C#SqlDBHelper

#region [ 引入相关名命令空间 ]
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;// 添加程序集引用（在你的项目找到引用，鼠标右键点击打开，查找到Configuration。然后确定添加OK！）
#endregion

#region [ SqlHelper ]
public static class SqlHelper
{
    #region [ connectionString ]
    // 连接字符串
    //public static string connectionString = ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString;
    public static string connectionString = "Server=localhost;DataBase=MyDB;Uid=Sa;Pwd=123;";
    #endregion

    #region [ PrepareCommand ]
    // 准备命令
    public static void PrepareCommand(string cmdText, SqlConnection conn, SqlCommand cmd, CommandType cmdType, params SqlParameter[] commandParameters)
    {
        // 判断连接是否打开
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        // 设置连接对象
        cmd.Connection = conn;
        // 设置命令文本
        cmd.CommandText = cmdText;
        // 设置命令类型
        cmd.CommandType = cmdType;
        // 判断参数是否为空
        if (commandParameters != null)
        {
            cmd.Parameters.AddRange(commandParameters);
        }
    }
    #endregion

    #region [ ExecuteNonQuery ]
    // 增删改
    public static bool ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
    {
        int count;
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmdText, conn, cmd, cmdType, commandParameters);
            count = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
        }
        catch (Exception ex)
        {
            conn.Close();
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return count > 0 ? true : false;
    }
    // 执行Sql语句，增删改
    public static bool ExecuteNonQueryByText(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);
    }
    // 执行Proc，增删改
    public static bool ExecuteNonQueryByProc(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);
    }
    #endregion

    #region [ ExecuteReader ]
    // 返回SqlDataReader对象
    public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmdText, conn, cmd, cmdType, commandParameters);
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return dr;
        }
        catch (Exception ex)
        {
            conn.Close();
            throw new Exception(ex.Message);
        }
    }
    // 执行Sql语句，返回SqlDataReader对象
    public static SqlDataReader ExecuteReaderByText(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteReader(cmdText, CommandType.Text, commandParameters);
    }
    // 执行Proc，返回SqlDataReader对象
    #endregion

    #region [ ExecuteReaderByProc ]
    public static SqlDataReader ExecuteReaderByProc(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);
    }
    // 获取第一行，第一列值
    public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmdText, conn, cmd, cmdType, commandParameters);
            object obj = cmd.ExecuteScalar();
            return obj;
        }
        catch (Exception ex)
        {
            conn.Close();
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
    // 执行Sql语句，获取第一行，第一列值
    public static object ExecuteScalarByText(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteScalar(cmdText, CommandType.Text, commandParameters);
    }
    // 执行Proc，获取第一行，第一列值
    public static object ExecuteScalarByProc(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);
    }
    #endregion

    #region [ ExecuteDataSet ] 
    // 获取DataSet
    public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmdText, conn, cmd, cmdType, commandParameters);
            DataSet ds = new DataSet();
            SqlDataAdapter dr = new SqlDataAdapter(cmd);
            dr.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            conn.Close();
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
    // 执行Sql语句，获取DataSet
    public static DataSet ExecuteDataSetByText(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);
    }
    // 执行Proc，获取DataSet
    public static DataSet ExecuteDataSetByProc(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteDataSet(cmdText, CommandType.StoredProcedure, commandParameters);
    }
    #endregion

    #region [ ExecuteDataTable ]
    // 获取DataTable
    public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
    {
        try
        {
            DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
    // 执行Sql语句，获取DataTable
    public static DataTable ExecuteDataTableByText(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteDataTable(cmdText, CommandType.Text, commandParameters);
    }
    // 执行Proc，获取DataTable
    public static DataTable ExecuteDataTableByProc(string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);
    }
    #endregion  
}
#endregion