一个很好用的DBHelper类(包括使用SQL语句 存储过程 事务 做相关操作) 入门级

话不多少 直接贴代码

 

需引入的命名空间

 

定义连接属性

private  SqlConnection conn;
       public  SqlConnection Conn 
       {
           get 
           {
               if (conn == null)
               {
                   string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                    conn = new SqlConnection(connStr);
                }
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
                return conn; 
            }
        }

做查询(包括sql语句和带参数的SQL语句)

//查询:DataReader
        public  SqlDataReader GetReader(string sql)
        {
           SqlCommand cmd = new SqlCommand(sql, Conn);
           return cmd.ExecuteReader(CommandBehavior.CloseConnection);
         }
         public  SqlDataReader GetReader(string sql,SqlParameter[] paras)
         {
             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.Parameters.AddRange(paras);
             return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        //查询:DataTable
        public DataTable GetTable(string sql)
        {
            SqlDataAdapter dap = new SqlDataAdapter(sql, Conn);
            DataTable dt = new DataTable();
            dap.Fill(dt);
            conn.Close();
            return dt;
        }
        public DataTable GetTable(string sql, SqlParameter[] paras)
        {
            SqlCommand cmd = new SqlCommand(sql, Conn);
            cmd.Parameters.AddRange(paras);
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            dap.Fill(dt);
            conn.Close();
            return dt;

        }

做增删改(包括sql语句和带参数的SQL语句)

//增改删
        public bool ExecuteNoQuery(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Conn);
            int result = cmd.ExecuteNonQuery();
            this.conn.Close();
            return result > 0;
        }
        public bool ExecuteNoQuery(string sql, SqlParameter[] paras)
        {
            SqlCommand cmd = new SqlCommand(sql, Conn);
            cmd.Parameters.AddRange(paras);
            int result = cmd.ExecuteNonQuery();
            this.conn.Close();
            return result > 0;
        }

执行聚合函数(包括sql语句和带参数的SQL语句)

   //执行聚合函数
         public object ExecuteScalar(string sql)
         {
             SqlCommand cmd = new SqlCommand(sql, Conn);
             object result = cmd.ExecuteScalar();
             this.conn.Close();
             return result;
         }
         public object ExecuteScalar(string sql, SqlParameter[] paras)
         {
             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.Parameters.AddRange(paras);
             object result = cmd.ExecuteScalar();
             this.conn.Close();
             return result;
         }

 执行存储过程获取数据集(包括sql语句和带参数的SQL语句)
 <img alt="" src="http://www.itdaan.com/imgs/0/7/4/7/81/cdec0645add3fc3c328197dda5c76203.jpe" class="code_img_closed" style="border: 0px; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 25.1875px;" /><span class="cnblogs_code_collapse" style="font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 25.1875px;">执行存储过程获取数据集(查询)</span>
//执行存储过程获取数据集(查询)
        public DataTable ExecuteProcSelect(string ProcName, SqlParameter[] paras)
         {
             SqlCommand cmd = new SqlCommand(ProcName, Conn);
             cmd.Parameters.AddRange(paras);
             cmd.CommandType = CommandType.StoredProcedure;
             SqlDataAdapter dap = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             dap.Fill(dt);
             this.conn.Close();
             return dt;       
         }

执行存储过程做增删改

//执行存储过程做增删改
  public bool ExecuteProcUpdate(string ProcName, SqlParameter[] paras)
       {
           SqlCommand cmd = new SqlCommand(ProcName, Conn);
           cmd.Parameters.AddRange(paras);
           cmd.CommandType = CommandType.StoredProcedure;
           int result = cmd.ExecuteNonQuery();
           this.conn.Close();
           return result > 0;
       }

执行事物(ADO.NET)

//执行事物(ADO.NET)
          public bool ExecuteTrasaction(string sqlStr, string TranName)
        {
            bool result = true;
            SqlTransaction tran = null;
            try
            {
                tran = Conn.BeginTransaction(TranName);
                SqlCommand cmd = new SqlCommand(sqlStr, Conn, tran);
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                {
                    tran.Commit();
                    result = true;
                }
                else
                {
                    tran.Rollback();
                    result = false;
                }
            }
            catch
            {
                tran.Rollback();
                result = false;
            }
            return result;
        }

执行存储过程做增删改

//执行存储过程做增删改
  public bool ExecuteProcUpdate(string ProcName, SqlParameter[] paras)
        {
            SqlCommand cmd = new SqlCommand(ProcName, Conn);
            cmd.Parameters.AddRange(paras);
            cmd.CommandType = CommandType.StoredProcedure;
            int result = cmd.ExecuteNonQuery();
            this.conn.Close();
            return result > 0;
        }

执行存储过程做增删改