自定义操作数据库的类

为了提高操作数据库的效率，可以将与数据库操作相关的方法封装在一个类中，供以后调用。

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();
            }
        }
    }
}