自用 SqlHelper浓缩版 增删改查 存储过程 事物

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace JYSH.DAL.SqlHelper
{
    /// <summary>
    /// SqlHelper浓缩版
    /// </summary>
    public class DbHelperSQL
    {
        #region 属性
        //public static string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        public static string ConnStr = string.Empty;//链接字符串，链接不同的数据库，配置于Web.Config中...
        private static SqlConnection conn = null;//数据库链接对象 
        private static SqlCommand cmd = null;//数据库操作
        private static SqlDataReader sdr = null;//数据集
        #endregion 属性

        #region 基本用法
        /// <summary>
        /// 链接数据库
        /// </summary>
        /// <returns></returns>
        private static SqlConnection GetConn()
        {
            conn = new SqlConnection(ConnStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 增删改 INSERT|DELETE|UPDATE
        /// SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@Name",DBNull.Value),}; 
        /// </summary>
        public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas = null)
        {
            cmd = new SqlCommand(sqlStr, GetConn());
            if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        } 

        /// <summary>
        /// 查 SELECT
        /// SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@Name",DBNull.Value),}; 
        /// </summary>
        public static DataTable ExecuteReader(string sqlStr, SqlParameter[] sqlparas = null)
        {
            using (cmd = new SqlCommand(sqlStr, GetConn()))
            {
                DataTable dt = new DataTable();
                if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
                conn.Close();
                return dt;
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 如：
        /// insert一条数据，返回自增的id值：insert into table([NAME])values ('名字');Select @@Identity;
        /// </summary>
        public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas = null)
        {
            cmd = new SqlCommand(sqlStr, GetConn());
            if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
            object res = cmd.ExecuteScalar();
            conn.Close();
            return res;
        }
        #endregion 基本用法

        #region 存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="proc">存储过程名称</param>
        /// <param name="spArr">参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery_ForProc(string proc, SqlParameter[] spArr = null)
        {
            cmd = new SqlCommand(proc, GetConn());
            cmd.CommandTimeout = 3600;
            cmd.CommandType = CommandType.StoredProcedure;
            if (spArr != null) cmd.Parameters.AddRange(spArr);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 执行存储过程-带输出参数(最后一个参数)
        /// </summary>
        /// <param name="proc">存储过程名称</param>
        /// <param name="spArr">参数</param>
        /// <returns>输出参数的值</returns>
        public static object ExecuteNonQuery_ForProc_Output(string ProcName, SqlParameter[] parm)
        {
            cmd = new SqlCommand(ProcName, GetConn());
            //最后一个参数为输出参数
            parm[parm.Length - 1].Direction = ParameterDirection.Output;
            cmd.CommandType = CommandType.StoredProcedure;
            if (parm != null)
            {
                cmd.Parameters.AddRange(parm);
            }
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            //返回输出的参数,看存储过程中定义的输出参数是什么类型,这里就转换成什么类型
            return parm[parm.Length - 1].Value;
        }
        #endregion

        #region 事物
        /// <summary>
        /// 事物-执行多条操作(INSERT|UPDATE|DELETE)语句
        /// </summary>
        /// <param name="Sqlstr">sql语句数组</param>
        /// <returns></returns>
        public int ExecTran(string[] Sqlstr)
        {
            conn = GetConn();
            SqlTransaction tran = conn.BeginTransaction();//开始事物
            cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = tran;
            try
            {
                int count = Sqlstr.Length;
                for (int i = 0; i < count; i++)
                {
                    cmd.CommandText = Sqlstr[i];
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();//提交事物
                return 1;
            }
            catch
            {
                tran.Rollback();//回滚事物
                return 0;
            }
            finally
            {
                conn.Close();
                tran.Dispose();
            }
        }
        #endregion

        #region 私有方法
        public static DataTable SELECT(string sqlStr, SqlParameter[] sqlparas = null)
        {
            return ExecuteReader(sqlStr, sqlparas);
        }
        public static DataTable GetData(string sqlStr, SqlParameter[] sqlparas = null)
        {
            return ExecuteReader(sqlStr, sqlparas);
        }
        public static int INSERT(string sqlStr, SqlParameter[] sqlparas = null)
        {
            return ExecuteNonQuery(sqlStr, sqlparas);
        }
        public static int UPDATE(string sqlStr, SqlParameter[] sqlparas = null)
        {
            return ExecuteNonQuery(sqlStr, sqlparas);
        }
        public static int DELETE(string sqlStr, SqlParameter[] sqlparas = null)
        {
            return ExecuteNonQuery(sqlStr, sqlparas);
        }
        #endregion

        #region 特殊方法 
        /// <summary>
        /// 简单单表查询
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Top"></param>
        /// <param name="fldName"></param>
        /// <param name="strWhere"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static DataTable GetData(string TableName, int Top = 0, string fldName = "", string strWhere = "", string orderBy = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            if (Top > 0)
            {
                strSql.AppendFormat(" TOP {0} ", Top);
            }
            if (!string.IsNullOrEmpty(fldName))
            {
                strSql.AppendFormat(fldName + " FROM {0}", TableName);
            }
            else
            {
                strSql.AppendFormat(" * FROM {0}", TableName);
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                strSql.Append(" ORDER BY " + orderBy);
            }

            DataTable dt = ExecuteReader(strSql.ToString());
            return dt;
        }
        /// <summary>
        /// SQL分页查询
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="fldName">显示字段</param>
        /// <param name="strWhere">where条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="PageSize">每页多少数据</param>
        /// <param name="PageIndex">当前第几页</param>
        /// <returns></returns>
        public static DataTable GetPageData(string TableName, string fldName, string strWhere, string orderBy, int PageSize = 10, int PageIndex = 1)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("DECLARE @PageSize INT;");
            strSql.AppendLine("DECLARE @PageIndex INT;");
            strSql.AppendLine("SET @PageSize=" + PageSize + ";");
            strSql.AppendLine("SET @PageIndex=" + PageIndex + ";");

            strSql.Append("SELECT ");
            if (!string.IsNullOrEmpty(fldName))
            {
                strSql.Append(fldName + " FROM (");
            }
            else
            {
                strSql.Append(" * FROM (");
            }
            strSql.AppendFormat(" SELECT ROW_NUMBER() OVER(ORDER BY {0}) rownum,", orderBy);
            if (!string.IsNullOrEmpty(fldName))
            {
                strSql.AppendFormat(fldName + " FROM {0}", TableName);
            }
            else
            {
                strSql.AppendFormat(" * FROM {0}", TableName);
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.AppendLine(" WHERE " + strWhere);
            }
            strSql.AppendLine(" )a");
            strSql.AppendLine(" WHERE rownum > @PageSize * (@PageIndex - 1) AND rownum <= @PageSize * @PageIndex ");
            if (!string.IsNullOrEmpty(orderBy))
            {
                strSql.Append(" ORDER BY " + orderBy);
            }

            DataTable dt = ExecuteReader(strSql.ToString());
            return dt;
        }
        /// <summary>
        /// DataTable分页查询
        /// </summary>
        /// <param name="data">DataTable</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">每页几行数据</param>
        /// <param name="allPage">一共有几页</param>
        /// <returns></returns>
        public static DataTable GetDataTablePage(DataTable data, int pageIndex, int pageSize, out int allPage)
        {
            allPage = data.Rows.Count / pageSize;
            allPage += data.Rows.Count % pageSize == 0 ? 0 : 1;
            DataTable Ntable = data.Clone();
            int startIndex = pageIndex * pageSize;
            int endIndex = startIndex + pageSize > data.Rows.Count ? data.Rows.Count : startIndex + pageSize;
            if (startIndex < endIndex)
                for (int i = startIndex; i < endIndex; i++)
                {
                    Ntable.ImportRow(data.Rows[i]);
                }
            return Ntable;
        }
        #endregion


    }
}