DBhelp公共类

在asp.net中使用公共类可以节省很多代码，DBHelp这个类我们经常用，现在来简单介绍一下：

 

连接字符串从配置文件中读取
       private readonly static string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

       执行增删改操作方法
       public static void GetExecuteNonQuery(string sqlstr,params SqlParameter[] paras)
       {
         using(SqlConnection conn=new SqlConnection(CONNSTRING))
         {

            try{
             SqlCommand cmd = new SqlCommand(sqlstr,conn);
             cmd.Parameters.AddRange(paras);
             conn.Open();
             cmd.ExecuteNonQuery();

             }catch(OleDbException e){

                 throw new Exception(e.Message);
             }
         }
      
       }

       执行检索查询方法
       public static SqlDataReader GetReader(string sqlstr, params  SqlParameter[] paras)
       {

        try{
           SqlConnection conn = new SqlConnection(CONNSTRING);
           SqlCommand cmd = new SqlCommand(sqlstr, conn);
           cmd.Parameters.AddRange(paras);
           conn.Open();
           return cmd.ExecuteReader(CommandBehavior.CloseConnection);

          }catch(OleDbException e){

                 throw new Exception(e.Message);
             }
       }

      注：CommandBehavior.CloseConnection表示关闭SqlDataReader的同时把连接也关掉

       

     返回第一行第一列的方法 
       public static object GetExecuteScalar(string sqlstr, params  SqlParameter[] paras)
       {
           using (SqlConnection conn = new SqlConnection(CONNSTRING))
           {  

               try

               {
               SqlCommand cmd = new SqlCommand(sqlstr, conn);
               cmd.Parameters.AddRange(paras);
               conn.Open();
               return cmd.ExecuteScalar();

           } catch(OleDbException e){

                 throw new Exception(e.Message);
             }
           }
       }

      
       返回DataTable对象
       public static DataTable GetDataSet(string sql, SqlParameter[] paramters)
       {
           using (SqlConnection conn = new SqlConnection(CONNSTRING))
           {

               try{
               DataSet ds = new DataSet();
               SqlCommand command = new SqlCommand(sql, conn);
               command.Parameters.AddRange(paramters);
               SqlDataAdapter adapter = new SqlDataAdapter(command);
               adapter.Fill(ds);
               return ds.Tables[0];

              }catch(OleDbException e){

                 throw new Exception(e.Message);
             }
           }
       }

 返回DataSet

 public static DataSet Query(string sqlstr, params OleDbParameter[] paras)
      {
         using(OleDbConnection conn=new OleDbConnection(CONNSTR))
         {
             try
             {
                 DataSet ds = new DataSet();
                 OleDbCommand cmd = new OleDbCommand(sqlstr, conn);
                 cmd.Parameters.AddRange(paras);
                 OleDbDataAdapter da = new OleDbDataAdapter();
                 da.SelectCommand = cmd;
                 da.Fill(ds, "ds");
             
                 return ds;
             }
             catch (OleDbException e)
             {

                 throw new Exception(e.Message);
             }
         }
      }

 

public static DataSet Query(string sqlstr)
      {
         using(OleDbConnection conn=new OleDbConnection(CONNSTR))
         {
             try
             {
                 DataSet ds = new DataSet();
                 OleDbCommand cmd = new OleDbCommand(sqlstr, conn);
               
                 OleDbDataAdapter da = new OleDbDataAdapter();

                 da.Fill(ds, "ds");
                 return ds;
             }
             catch (OleDbException e)
             {

                 throw new Exception(e.Message);
             }
         }
      }
执行多条SQL语句，实现数据库事务

  public static void ExecuteSqlTran(ArrayList SQLStringList)
      {

          int fig=0;//用来判断是否执行成功
          using (OleDbConnection conn = new OleDbConnection(CONNSTR))
          {
              conn.Open();
              OleDbCommand cmd = new OleDbCommand();
              cmd.Connection = conn;
             
              OleDbTransaction tx = conn.BeginTransaction();
              cmd.Transaction = tx;
              try
              {
                  for (int n = 0; n < SQLStringList.Count; n++)
                  {
                      string strsql = SQLStringList[n].ToString();
                      if (strsql.Trim().Length > 1)
                      {
                          cmd.CommandText = strsql;
                 

                         if(cmd.ExecuteNonQuery()>0)

                         {

                             fig++;

                          }
                      }
                  }
                  tx.Commit();
              }
              catch (System.Data.OleDb.OleDbException E)
              {

                  fig=-1;
                  tx.Rollback();
                  throw new Exception(E.Message);

              }
          }
      }

    其中SqlParameter[] paramters在返回范性集合的时候可以写参数或者没有参数也可以不写，但是返回dataTable或者DataSet的时候要写！

   获得新增数据的id(access)

    public void ExecuteSql(string cmdText, IDataParameter[] cmdParameters, out DataTable dt)
        {
            try
            {
                this._DbCommand.CommandText = cmdText;
                foreach (IDataParameter parameter in cmdParameters)
                {
                    this._DbCommand.Parameters.Add(parameter);
                }
                this._DbDataAdapter.SelectCommand = this._DbCommand;
                DataSet dataSet = new DataSet();
                this._DbDataAdapter.Fill(dataSet);
                dt = dataSet.Tables[0];
            }
            catch (Exception exception)
            {
                this.CloseConnection();
                throw new Exception(exception.Message);
            }
        }