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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
public GetConn()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
private static SqlConnection conn = null;
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 返回数据库的连接
/// </summary>
public SqlConnection Conn
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn == null)//如果为空,则创建,否则直接返回对象,单键模式,减少对象的重复创建。
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string url = ConfigurationSettings.AppSettings["ConnectionKey"];
conn = new SqlConnection(url);
}
return conn;
}
}
}
三、数据库方法调用
假设数据库里有一个用户表,下面是关于用户表的方法,查询,删除,修改
public
class
User
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
public User()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
GetConn conn = new GetConn();
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据条件获取用户信息
/// </summary>
/// <param name="UserName"></param>
/// <returns></returns>
public DataTable SelectUsersDynamic(string WhereCondition, string OrderByExpression)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 查询所有用户信息
/// </summary>
/// <returns></returns>
public DataTable SelectUsersAll()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 删除用户
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public int DeleteUser(int UserID)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 设置密码
/// </summary>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public int UpdateUserPassword(string UserName, string Password)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
public UserBLL()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
User user = new User();
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据用户名获取用户信息
/// </summary>
/// <param name="UserName"></param>
/// <returns></returns>
public DataTable SelectUsersDynamic(string WhereCondition, string OrderByExpression)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return user.SelectUsersDynamic(WhereCondition, OrderByExpression);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 查询所有用户信息
/// </summary>
/// <returns></returns>
public DataTable SelectUsersAll()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return user.SelectUsersAll();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 删除用户
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public int DeleteUser(int UserID)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return user.DeleteUser(UserID);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return user.UpdateUser(UserID,UserName,IsSupplier, RoleID);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 设置密码
/// </summary>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public int UpdateUserPassword(string UserName, string Password)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return user.UpdateUserPassword(UserName , new StockBaseBLL().MD5( Password,16));
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return user.InsertUser(UserName, Password, IsSupplier, RoleID,SupplierCode);
}
}
上面的调用感觉像是多余的,其实不是的,他可以隔离表示层和数据库层的方法。还有如果对数据集要处理的话,也可以在这里处理好,然后返回给表示层。
五、表示层调用
这一层的调用我想就不写了,因为很简单,就是创建对象,然后调用。