曾做过几个ASP.NET的项目,其中大量用到数据库操作,因此单独写了个类,方便使用。源代码如下:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// CommSql 的摘要说明
/// </summary>
public class CommSql
{
//获取Web.Config数据库连接字符串
private string SqlConnectionString;
private SqlConnection cn; //创建SQL连接
private SqlCommand cmd; //创建SQL命令对象
public CommSql(string connectionString)
{
SqlConnectionString = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
#region
cn = new SqlConnection(SqlConnectionString);
cn.Open();
#endregion
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
#region
if (cn != null)
{
cn.Close();
cn.Dispose();
}
#endregion
}
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="strSql"></param>
public Boolean RunSql(string strSql)
{
#region
Open();
try
{
cmd = new SqlCommand(strSql, cn);
cmd.ExecuteNonQuery();
Close();
return true;
}
catch {
Close();
return false;
}
#endregion
}
/// <summary>
/// 执行SQL语句,并返回第一行第一列结果
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public string RunSqlReturn(string strSql)
{
#region
string strReturn = "";
Open();
try
{
cmd = new SqlCommand(strSql, cn);
strReturn = cmd.ExecuteScalar().ToString();
}
catch { }
Close();
return strReturn;
#endregion
}
/// <summary>
/// 返回需要的数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public DataSet GetResultAsDataSet(string strSql)
{
#region
Open();
SqlDataAdapter da = new SqlDataAdapter(strSql, cn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch
{
ds = null;
}
Close();
return ds;
#endregion
}
}
使用方法,在web.config中可以设置数据库的连接,即下面代码:
<connectionStrings>
<add name="A" connectionString="Data Source= ;Initial Catalog= ; UID= ; PWD=; Trusted_Connection=False;" providerName="System.Data.SqlClient"/>
</connectionStrings>
只需定义个CommSql的对象,实参为name的值,以上面为例,就是字符串“A"。
这个类只封装了数据库的几个操作,可以根据需要进一步扩展。