自己写简单的SqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public static class SqlHelper
    {
        #region 连接字符串 connStr
        /// <summary>
        /// 创建连接字符串
        /// </summary>
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["sqlStr"].ConnectionString;
        #endregion

        #region 执行增、删、改操作 ExecuteNonQuery(string sql,CommandType cmdType, params SqlParameter [] param)
        /// <summary>
        /// 执行增、删、改操作
        /// </summary>
        /// <param name="sql">要执行sql语句或存储过程名字</param>
        /// <param name="cmdType">要执行的类型是存储过程还是sql语句</param>
        /// <param name="param">要执行的sql语句的参数，params可选参数，参数可以为0个或多个,可选参数只能放在最后</param>
        /// <returns>返回bool值，true为执行成功，false为执行失败</returns>
        public static int ExecuteNonQuery(string sql,CommandType cmdType, params SqlParameter [] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using(SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    cmd.CommandType = cmdType;
                    if(param != null && param.Length > 0)
                        cmd.Parameters.AddRange(param);     //取出参数
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion

        #region 执行查询操作 ExecuteScalar(string sql,CommandType cmdType, params SqlParameter [] param)
        /// <summary>
        /// 执行查询操作
        /// </summary>
        /// <param name="sql">要执行sql语句或存储过程名字</param>
        /// <param name="cmdType">要执行的类型是存储过程还是sql语句</param>
        /// <param name="param">要执行的sql语句的参数，params可选参数，参数可以为0个或多个,可选参数只能放在最后</param>
        /// <returns>返回object对象</returns>
        public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = cmdType;
                    if (param != null && param.Length != 0)
                        cmd.Parameters.AddRange(param);     //取出参数
                    return cmd.ExecuteScalar();
                }
            }
        }
        #endregion

        #region 读取DataReader ExecuteDataReader(string sql,CommandType cmdType, params SqlParameter[] param)
        /// <summary>
        /// 读取DataReader
        /// </summary>
        /// <param name="sql">要执行sql语句</param>
        /// <param name="cmdType">要执行的类型是存储过程还是sql语句</param>
        /// <param name="param">要执行的sql语句的参数，params可选参数，参数可以为0个或多个</param>
        /// <returns>返回DataReader对象</returns>
        public static SqlDataReader ExecuteDataReader(string sql,CommandType cmdType, params SqlParameter[] param)
        { //不能用using
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = cmdType;
            conn.Open();
            if (param != null && param.Length != 0)
                cmd.Parameters.AddRange(param);
            SqlDataReader dr = cmd.ExecuteReader();
            conn.Close();
            return dr;
        }
        #endregion

        #region 获得DataTable getDataTable(string sql,CommandType cmdType,params SqlParameter[] param)
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="sql">要执行sql语句</param>
        /// <param name="cmdType">要执行的类型是存储过程还是sql语句</param>
        /// <param name="param">要执行的sql语句的参数，params可选参数，参数可以为0个或多个</param>
        /// <returns>返回DataTable数据表</returns>
        public static DataTable getDataTable(string sql,CommandType cmdType,params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    cmd.CommandType = cmdType;
                    if(param != null && param.Length != 0)
                        cmd.Parameters.AddRange(param);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable tb = new DataTable();
                    adapter.Fill(tb);
                    return tb;
                }
            }
        }
        #endregion

        #region 获得DataSet getDataSet(string sql,CommandType cmdType, params SqlParameter[] param)
        /// <summary>
        /// 获得DataSet
        /// </summary>
        /// <param name="sql">要执行sql语句</param>
        /// <param name="cmdType">要执行的类型是存储过程还是sql语句</param>
        /// <param name="param">要执行的sql语句的参数，params可选参数，参数可以为0个或多个</param>
        /// <returns>返回DataSet数据集合</returns>
        public static DataSet getDataSet(string sql,CommandType cmdType, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = cmdType;
                    if (param != null && param.Length != 0)
                        cmd.Parameters.AddRange(param);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet set = new DataSet();
                    adapter.Fill(set);
                    return set;
                }
            }
        }
        #endregion
    }
}