DBHelper SQL Server数据库操作类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace WebApplication1
{
    public partial class WebForm3 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string sqlStr = "select * from userinfo";
            SqlDataReader sdr = ExecuteDataReaderSQL(sqlStr);
            while (sdr.Read())
            {
                Response.Write(sdr["id"]+"<br />");
            }
            //GridView2.DataSource = sdr;
            //GridView2.DataBind();
        }
        
        //连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["aa"].ConnectionString;
        /// <summary>
        /// 返回DataTable，执行SQL语句
        /// </summary>
        /// <param name="sqlStr">Sql命令语句</param>
        /// <param name="param">参数化</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTableSQL(string sqlStr, params SqlParameter[] param)
        {
            return ExecuteDataTable(sqlStr, CommandType.Text, param.ToArray());
        }
        /// <summary>
        /// 返回DataTable，执行存储过程
        /// </summary>
        /// <param name="sqlStr">存储过程名</param>
        /// <param name="param">参数化</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTableSP(string sqlStr, params SqlParameter[] param)
        {
            return ExecuteDataTable(sqlStr, CommandType.StoredProcedure, param.ToArray());
        }
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sqlStr">Sql命令语句或存储过程名</param>
        /// <param name="type">CommandType</param>
        /// <param name="param">参数化</param>
        /// <returns>DataTable</returns>
        private static DataTable ExecuteDataTable(string sqlStr, CommandType type, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    cmd.CommandType = type;
                    cmd.Parameters.AddRange(param);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        sda.Fill(dt);
                    }
                }
                return dt;
            }
        }
        /// <summary>
        /// 返回受影响的行数,SQL语句
        /// </summary>
        /// <param name="sqlStr">Sql命令语句</param>
        /// <param name="param">参数化</param>
        /// <returns>int 受影响的行数</returns>
        public static int ExecuteNonQuerySQL(string sqlStr, params SqlParameter[] param)
        {
            return ExecuteNonQuery(sqlStr, CommandType.Text, param.ToArray());
        }
        /// <summary>
        /// 返回受影响的行数,执行存储过程名
        /// </summary>
        /// <param name="sqlStr">存储过程名</param>
        /// <param name="param">参数化</param>
        /// <returns>int 受影响的行数</returns>
        public static int ExecuteNonQuerySP(string sqlStr, params SqlParameter[] param)
        {
            return ExecuteNonQuery(sqlStr, CommandType.StoredProcedure, param.ToArray());
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sqlStr">Sql命令语句或存储过程名</param>
        /// <param name="type">CommandType</param>
        /// <param name="param">参数化</param>
        /// <returns>int 受影响的行数</returns>
        private static int ExecuteNonQuery(string sqlStr, CommandType type, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    cmd.CommandType = type;
                    cmd.Parameters.AddRange(param);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 只读只进方式返回数据，执行存储过程
        /// </summary>
        /// <param name="sqlStr">存储过程名</param>
        /// <param name="param">参数化</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteDataReaderSP(string sqlStr, params SqlParameter[] param)
        {
            return ExecuteDataReader(sqlStr, CommandType.StoredProcedure, param);
        }
        /// <summary>
        /// 只读只进方式返回数据,SQL语句
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteDataReaderSQL(string sqlStr, params SqlParameter[] param)
        {
            return ExecuteDataReader(sqlStr, CommandType.Text, param);
        }
        /// <summary>
        /// 只读只进方式返回数据
        /// </summary>
        /// <param name="sqlStr">Sql命令语句或存储过程名</param>
        /// <param name="type">CommandType</param>
        /// <param name="param">参数化</param>
        /// <returns>SqlDataReader</returns>
        private static SqlDataReader ExecuteDataReader(string sqlStr,CommandType type, params SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            try
            {
                cmd.CommandType = type;
                cmd.Parameters.AddRange(param);
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 返回查询结果中第一行中的第一列，忽略其它行和列,执行SQL语句
        /// </summary>
        /// <param name="sqlStr">Sql命令语句</param>
        /// <param name="param">参数化</param>
        /// <returns>object</returns>
        public static object ExecuteDataScalerSQL(string sqlStr, CommandType type, params SqlParameter[] param)
        {
            return ExecuteDataScaler(sqlStr, CommandType.Text, param);
        }
        /// <summary>
        /// 返回查询结果中第一行中的第一列，忽略其它行和列,执行存储过程
        /// </summary>
        /// <param name="sqlStr">存储过程名</param>
        /// <param name="param">参数化</param>
        /// <returns>object</returns>
        public static object ExecuteDataScalerSP(string sqlStr, CommandType type, params SqlParameter[] param)
        {
            return ExecuteDataScaler(sqlStr, CommandType.StoredProcedure, param);
        }
        /// <summary>
        /// 返回查询结果中第一行中的第一列，忽略其它行和列
        /// </summary>
        /// <param name="sqlStr">Sql命令语句或存储过程名</param>
        /// <param name="type">CommandType</param>
        /// <param name="param">参数化</param>
        /// <returns>object</returns>
        public static object ExecuteDataScaler(string sqlStr, CommandType type, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sqlStr,conn))
                {
                    cmd.CommandType = type;
                    cmd.Parameters.AddRange(param);
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
    }
}