为了提高操作数据库的效率,可以将与数据库操作相关的方法封装在一个类中,供以后调用。
1 在web.config中配置连接字符串,这样可心方便以后修改
<appSettings>
<add key ="ConnectionString" value ="Data Source=.;Initial Catalog=counter;Integrated Security=True"/>
</appSettings >
2在App_Code中增加一个SQLconn类,用来封装数据库的相关操作
namespace counter
{
/// <summary>
///SQLConn 数据库连接类
/// </summary>
public class SQLConn
{
public SQLConn()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
//定义获取连接字符串的方法
private static string GetSqlConnString()
{
//定义变量保存读取出来的数据库连接字符串
string strSqlConnstring = ConfigurationSettings.AppSettings["ConnectionString"];
return strSqlConnstring;
}
//定义数据库的写入、更新和删除操作
public static int ExecuteSql(string strSQL)
{
SqlConnection MyConn = new SqlConnection(GetSqlConnString());
SqlCommand MyComm = new SqlCommand(strSQL, MyConn);
try
{
MyConn.Open();
MyComm.ExecuteNonQuery();//执行数据库操作
return 0; //返回值
}
catch (SqlException ex)
{
throw new Exception(ex.Message);//抛出异常
}
finally
{
MyComm.Dispose();
MyConn.Close();
}
}
//定义返回查询记录集的方法
public static DataSet ExecuteSqlForDataSet(string strSql)
{
SqlConnection MyConn = new SqlConnection(GetSqlConnString());
try
{
MyConn.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(strSql, MyConn);
DataSet myds = new DataSet();
myAdapter.Fill(myds); //填充数据集
return myds;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
MyConn.Close();
}
}
//查询指定范围内的记录
public static DataSet ExecuteSqlForDataSetPageing(string strSql, int startindex,
int pagesize, string tablename)
{
SqlConnection myConn = new SqlConnection(GetSqlConnString());
try
{
myConn.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(strSql, myConn);
DataSet myds = new DataSet();
myAdapter.Fill(myds, startindex, pagesize, tablename);//定义查询记录
return myds;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
myConn.Close();
}
}
}
}