现在的位置: 首页 > 综合 > 正文

asp.net数据库连接,三层架构方法调用

2012年04月25日 ⁄ 综合 ⁄ 共 7548字 ⁄ 字号 评论关闭

http://www.cnblogs.com/dingjie08/archive/2009/03/05/1404103.html

 

asp.net数据库连接,三层架构方法调用

一、web.config配置
<connectionStrings/>
 <!-- 数据库连接关键字 -->
 <appSettings>
  <add key="ConnectionKey" value="Data Source=192.168.100.31;Initial Catalog=stock;User ID=sa;password=123;max pool size=10;min pool size=6"/>
 </appSettings>
 <system.web>

下面的是语言配置
<globalization culture="zh-CN" uiCulture="zh-CN" requestEncoding="gb2312" responseEncoding="gb2312"/>
注意他的位置,不能弄错了。
二、建立数据库的连接

public class GetConn
{
    
public GetConn()
    
{
        
//
        
// TODO: 在此处添加构造函数逻辑
        
//
    }

    
private static SqlConnection conn = null;
    
/// <summary>
    
/// 返回数据库的连接
    
/// </summary>

    public SqlConnection Conn
    
{
        
get
        
{
            
if (conn == null)//如果为空,则创建,否则直接返回对象,单键模式,减少对象的重复创建。
            {
                
string url = ConfigurationSettings.AppSettings["ConnectionKey"];
                conn 
= new SqlConnection(url);
            }

            
return conn;
        }

    }

}

三、数据库方法调用
假设数据库里有一个用户表,下面是关于用户表的方法,查询,删除,修改

public class User
{
    
public User()
    
{
        
//
        
// TODO: 在此处添加构造函数逻辑
        
//
    }

    GetConn conn 
= new GetConn();
    
/// <summary>
    
/// 根据条件获取用户信息
    
/// </summary>
    
/// <param name="UserName"></param>
    
/// <returns></returns>

    public DataTable SelectUsersDynamic(string WhereCondition, string OrderByExpression)
    
{
        DataTable dt 
= new DataTable();
        SqlCommand comm 
= new SqlCommand("SelectUsersDynamic", conn.Conn);
        comm.CommandType 
= CommandType.StoredProcedure;
        comm.Parameters.Add(
"@WhereCondition", SqlDbType.NVarChar, 500);
        comm.Parameters[
"@WhereCondition"].Value = WhereCondition;
        comm.Parameters[
"@WhereCondition"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@OrderByExpression", SqlDbType.NVarChar, 250);
        comm.Parameters[
"@OrderByExpression"].Value = OrderByExpression;
        comm.Parameters[
"@OrderByExpression"].Direction = System.Data.ParameterDirection.Input;
        SqlDataAdapter adapter 
= new SqlDataAdapter(comm);
        adapter.Fill(dt);
        
if (conn.Conn.State == ConnectionState.Open)
            conn.Conn.Close();
        
return dt;
    }

    
/// <summary>
    
/// 查询所有用户信息
    
/// </summary>
    
/// <returns></returns>

    public DataTable SelectUsersAll()
    
{
        DataTable dt 
= new DataTable();
        SqlCommand comm 
= new SqlCommand("SelectUsersAll", conn.Conn);
        comm.CommandType 
= CommandType.StoredProcedure;       
        SqlDataAdapter adapter 
= new SqlDataAdapter(comm);
        adapter.Fill(dt);
        
if (conn.Conn.State == ConnectionState.Open)
            conn.Conn.Close();
        
return dt;
    }

    
/// <summary>
    
/// 删除用户
    
/// </summary>
    
/// <param name="UserID"></param>
    
/// <returns></returns>

    public int DeleteUser(int UserID)
    
{
        SqlCommand comm 
= new SqlCommand("DeleteUser", conn.Conn);
        
if (conn.Conn.State == ConnectionState.Closed)
            conn.Conn.Open();
        comm.CommandType 
= CommandType.StoredProcedure;
        comm.Parameters.Add(
"@UserID", SqlDbType.Int, 4);
        comm.Parameters[
"@UserID"].Value = UserID;
        comm.Parameters[
"@UserID"].Direction = System.Data.ParameterDirection.Input;
        
int counts = comm.ExecuteNonQuery();
        
if (conn.Conn.State == ConnectionState.Open)
            conn.Conn.Close();
        
return counts;
    }

    
/// <summary>
    
/// 修改用户信息
    
/// </summary>
    
/// <param name="UserID"></param>  
    
/// <param name="IsSupplier"></param>
    
/// <param name="RoleID"></param>
    
/// <returns></returns>

    public int UpdateUser(int UserID,string UserName,int IsSupplier, int RoleID)
    
{
        SqlCommand comm 
= new SqlCommand("UpdateUser", conn.Conn);
        
if (conn.Conn.State == ConnectionState.Closed)
            conn.Conn.Open();
        comm.CommandType 
= CommandType.StoredProcedure;
        comm.Parameters.Add(
"@UserID", SqlDbType.Int,4);
        comm.Parameters[
"@UserID"].Value = UserID;
        comm.Parameters[
"@UserID"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@UserName", SqlDbType.VarChar,20);
        comm.Parameters[
"@UserName"].Value = UserName;
        comm.Parameters[
"@UserName"].Direction = System.Data.ParameterDirection.Input;      
        comm.Parameters.Add(
"@IsSupplier", SqlDbType.Int,4);
        comm.Parameters[
"@IsSupplier"].Value = IsSupplier;
        comm.Parameters[
"@IsSupplier"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@RoleID", SqlDbType.Int,4);
        comm.Parameters[
"@RoleID"].Value = RoleID;
        comm.Parameters[
"@RoleID"].Direction = System.Data.ParameterDirection.Input;
        
int counts = comm.ExecuteNonQuery();
        
if (conn.Conn.State == ConnectionState.Open)
            conn.Conn.Close();
        
return counts;
    }

    
/// <summary>
    
/// 设置密码
    
/// </summary>
    
/// <param name="UserName"></param>
    
/// <param name="Password"></param>
    
/// <returns></returns>

    public int UpdateUserPassword(string UserName, string Password)
    
{
        SqlCommand comm 
= new SqlCommand("UpdateUserPassword", conn.Conn);
        
if (conn.Conn.State == ConnectionState.Closed)
            conn.Conn.Open();
        comm.CommandType 
= CommandType.StoredProcedure;
        comm.Parameters.Add(
"@UserName", SqlDbType.VarChar,20);
        comm.Parameters[
"@UserName"].Value = UserName;
        comm.Parameters[
"@UserName"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@Password", SqlDbType.VarChar ,32);
        comm.Parameters[
"@Password"].Value = Password;
        comm.Parameters[
"@Password"].Direction = System.Data.ParameterDirection.Input;
        
int counts = comm.ExecuteNonQuery();
        
if (conn.Conn.State == ConnectionState.Open)
            conn.Conn.Close();
        
return counts;
    }

    
/// <summary>
    
/// 新增用户
    
/// </summary>
    
/// <param name="UserName"></param>
    
/// <param name="Password"></param>
    
/// <param name="IsSupplier"></param>
    
/// <param name="RoleID"></param>
    
/// <returns></returns>

    public int InsertUser(string UserName, string Password, int IsSupplier, int RoleID,string SupplierCode)
    
{
        SqlCommand comm 
= new SqlCommand("InsertUser", conn.Conn);
        
if (conn.Conn.State == ConnectionState.Closed)
            conn.Conn.Open();
        comm.CommandType 
= CommandType.StoredProcedure;        
        comm.Parameters.Add(
"@UserName", SqlDbType.NVarChar, 20);
        comm.Parameters[
"@UserName"].Value = UserName;
        comm.Parameters[
"@UserName"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@Password", SqlDbType.NVarChar, 32);
        comm.Parameters[
"@Password"].Value = Password;
        comm.Parameters[
"@Password"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@IsSupplier", SqlDbType.Int, 4);
        comm.Parameters[
"@IsSupplier"].Value = IsSupplier;
        comm.Parameters[
"@IsSupplier"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@RoleID", SqlDbType.Int, 4);
        comm.Parameters[
"@RoleID"].Value = RoleID;
        comm.Parameters[
"@RoleID"].Direction = System.Data.ParameterDirection.Input;
        comm.Parameters.Add(
"@SupplierCode", SqlDbType.VarChar,200);
        comm.Parameters[
"@SupplierCode"].Value = SupplierCode;
        comm.Parameters[
"@SupplierCode"].Direction = System.Data.ParameterDirection.Input;
        
int counts = comm.ExecuteNonQuery();
        
if (conn.Conn.State == ConnectionState.Open)
            conn.Conn.Close();
        
return counts;
    }

}

四、业务层的建立

public class UserBLL

抱歉!评论已关闭.