C# SqlHelper

每当小编在做一个新项目的时候都不可避免的会接触到与数据库的交互，小编准备一份，分享给大家，小编不觉得自己的这份SqlHelper多好，因为与部门里的老师傅们相比，我简直是逊毙了，不过平常自己练习项目的时候这个还是可以减少大家很多时间的

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Jipiao
{
    public class SqlHelper
    {

        /// <summary>
        /// 数据库连接字符串
        /// Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=用户名;Password=密码
        /// </summary>
        //private string _SqlConnectionStr = "server=;database=;uid=;pwd=";
        string _SqlConnectionStr = "data source=;initial catalog=;uid=;pwd=";
        //public string SqlConnectionStr { get { return _SqlConnectionStr; } }
        //public SqlHelper(string connStr)
        //{
        //    this._SqlConnectionStr = connStr;
        //}
        #region 单值查询
        public string GetSingle(string sqlStr)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    try
                    {
                        conn.Open();
                        return String.Format("{0}", cmd.ExecuteScalar());
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        public string GetSingle(string sqlStr, SqlParameter[] cmdParams)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sqlStr;
                        cmd.Parameters.AddRange(cmdParams);
                        return String.Format("{0}", cmd.ExecuteScalar());
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 查询数据集
        public DataSet Query(string sqlStr)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlDataAdapter ada = new SqlDataAdapter(sqlStr, conn))
                {
                    try
                    {
                        conn.Open();
                        DataSet ds = new DataSet();
                        ada.Fill(ds);
                        return ds;
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        public DataSet Query(string sqlStr, SqlParameter[] cmdParams)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
                    {
                        try
                        {
                            conn.Open();
                            cmd.Connection = conn;
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sqlStr;
                            cmd.Parameters.AddRange(cmdParams);

                            DataSet ds = new DataSet();
                            ada.Fill(ds);
                            return ds;
                        }
                        catch (SqlException e)
                        {
                            throw e;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        public DataSet RunProcedure(string procName, SqlParameter[] cmdParams)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
                    {
                        try
                        {
                            conn.Open();
                            cmd.Connection = conn;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = procName;
                            cmd.Parameters.AddRange(cmdParams);

                            DataSet ds = new DataSet();
                            ada.Fill(ds);
                            return ds;
                        }
                        catch (SqlException e)
                        {
                            throw e;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        #endregion

        #region 单表查询
        public DataTable GetQueryData(string sqlStr)
        {
            DataSet ds = Query(sqlStr);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }
        public DataTable GetQueryData(string sqlStr, SqlParameter[] cmdParams)
        {
            DataSet ds = Query(sqlStr, cmdParams);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }
        public DataTable GetProcData(string procName, SqlParameter[] cmdParams)
        {
            DataSet ds = RunProcedure(procName, cmdParams);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }
        #endregion

        #region 单行查询
        public DataRow GetQueryRecord(string sqlStr)
        {
            DataTable dt = GetQueryData(sqlStr);
            if (dt != null && dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }
        public DataRow GetQueryRecord(string sqlStr, SqlParameter[] cmdParams)
        {
            DataTable dt = GetQueryData(sqlStr, cmdParams);
            if (dt != null && dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }
        public DataRow GetProcRecord(string procName, SqlParameter[] cmdParams)
        {
            DataTable dt = GetProcData(procName, cmdParams);
            if (dt != null && dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }
        #endregion

        #region 使用完应关闭Reader
        public SqlDataReader ExecuteReader(string sqlStr)
        {
            SqlConnection conn = new SqlConnection(this._SqlConnectionStr);
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException e)
            {
                throw e;
            }
        }
        public SqlDataReader ExecuteReeder(string sqlStr, SqlParameter[] cmdParams)
        {
            SqlConnection conn = new SqlConnection(this._SqlConnectionStr);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlStr;
                cmd.Parameters.AddRange(cmdParams);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException e)
            {
                throw e;
            }
        }
        #endregion

        #region 执行sql语句
        public int ExecuteSql(string sqlStr)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        public int ExecuteSql(string sqlStr, SqlParameter[] cmdParams)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sqlStr;
                        cmd.Parameters.AddRange(cmdParams);
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        public object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteScalar();
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        #region 执行事务
        public int ExecuteSqlTran(List<string> sqlStrList)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlTransaction tran = conn.BeginTransaction())
                    {
                        try
                        {
                            cmd.Connection = conn;
                            cmd.CommandType = CommandType.Text;
                            cmd.Transaction = tran;
                            conn.Open();
                            int count = 0;
                            foreach (string sql in sqlStrList)
                            {
                                cmd.CommandText = sql;
                                count += cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                            return count;
                        }
                        catch (SqlException e)
                        {
                            tran.Rollback();
                            throw e;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        public int ExecuteSqlTran(List<KeyValuePair<string, SqlParameter[]>> sqlStrList)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlTransaction tran = conn.BeginTransaction())
                    {
                        try
                        {
                            cmd.Connection = conn;
                            cmd.CommandType = CommandType.Text;
                            cmd.Transaction = tran;
                            conn.Open();
                            int count = 0;
                            foreach (var item in sqlStrList)
                            {
                                cmd.CommandText = item.Key;
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddRange(item.Value);
                                count += cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                            return count;
                        }
                        catch (SqlException e)
                        {
                            tran.Rollback();
                            throw e;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        public int ExecuteProc(string procName, SqlParameter[] cmdParams)
        {
            using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = procName;
                        cmd.Parameters.AddRange(cmdParams);
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        从配置文件中读取连接字符串
        //private static string connStr = "server=120.236.158.136,9890;database=HlyTravel;uid=haolaiyun;pwd=haolaiyun321";
        //public static string GetSqlConnectionString()
        //{
        //    return connStr.ToString();
        //}
        适合增删改操作，返回影响条数
        //public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        //{
        //    using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
        //    {
        //        using (SqlCommand comm = conn.CreateCommand())
        //        {
        //            try
        //            {
        //                conn.Open();
        //                comm.CommandText = sql;
        //                comm.Parameters.AddRange(parameters);
        //                return comm.ExecuteNonQuery();
        //            }
        //            catch (Exception ex)
        //            {
        //                throw new Exception(ex.Message);
        //            }
        //            finally
        //            {
        //                if (conn != null && conn.State != ConnectionState.Closed)
        //                    conn.Close();
        //            }

        //        }
        //    }
        //}

        查询操作，返回查询结果中的第一行第一列的值
        //public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        //{
        //    using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
        //    {
        //        using (SqlCommand comm = conn.CreateCommand())
        //        {
        //            try
        //            {
        //                conn.Open();
        //                comm.CommandText = sql;
        //                comm.Parameters.AddRange(parameters);
        //                return comm.ExecuteScalar();
        //            }
        //            catch (Exception ex)
        //            {
        //                throw new Exception(ex.Message);
        //            }
        //            finally
        //            {
        //                if (conn != null && conn.State != ConnectionState.Closed)
        //                    conn.Close();
        //            }
        //        }
        //    }
        //}

        Adapter调整，查询操作，返回DataTable
        //public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        //{
        //    using (SqlDataAdapter adapter = new SqlDataAdapter(sql, GetSqlConnectionString()))
        //    {
        //        DataTable dt = new DataTable();
        //        adapter.SelectCommand.Parameters.AddRange(parameters);
        //        adapter.Fill(dt);
        //        return dt;
        //    }
        //}

        //public static SqlDataReader ExecuteReader(string sqlText, params SqlParameter[] parameters)
        //{
        //    //SqlDataReader要求，它读取数据的时候有，它独占它的SqlConnection对象，而且SqlConnection必须是Open状态
        //    SqlConnection conn = new SqlConnection(GetSqlConnectionString());//不要释放连接，因为后面还需要连接打开状态
        //    SqlCommand cmd = conn.CreateCommand();
        //    conn.Open();
        //    cmd.CommandText = sqlText;
        //    cmd.Parameters.AddRange(parameters);
        //    //CommandBehavior.CloseConnection当SqlDataReader释放的时候，顺便把SqlConnection对象也释放掉
        //    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //}
    }
}