SqlServerHelper,支持批量插入，事务，DataTable转实体，转实体类要用到的类，请查看往期随笔 c# +泛型+自定义属性 实现 DataTable转换成实体类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
namespace XXX.XXX
{
    /// <summary>
    /// 页面名  ：数据库操作类<br/>
    /// 说明    ：<br/>
    /// 作者    ：niu<br/>
    /// 创建时间：2011-12-13<br/>
    /// 最后修改：<br/>
    /// </summary>
    public class SqlServerHelper
    {
        private static readonly string connectionString = "server=.;uid=sa;pwd=******;database=XXX";
        #region AddInParameter 添加输入参数
        /// <summary>
        /// 添加In参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="value">值</param>
        /// <returns>返回一个SqlParameter对象</returns>
        public static SqlParameter AddInParameter(string paramName, object value)
        {
            SqlParameter param = new SqlParameter(paramName, value);
            return param;
        }

        /// <summary>
        /// 添加In参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">数据类型</param>
        /// <param name="value">值</param>
        /// <returns>返回一个SqlParameter对象</returns>
        public static SqlParameter AddInParameter(string paramName, SqlDbType dbType, object value)
        {
            return AddInParameter(paramName, dbType, 0, value);
        }
        /// <summary>
        /// 添加In参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">数据类型</param>
        /// <param name="size">字段大小</param>
        /// <param name="value">值</param>
        /// <returns>返回一个SqlParameter对象</returns>
        public static SqlParameter AddInParameter(string paramName, SqlDbType dbType, int size, object value)
        {
            SqlParameter param;
            if (size > 0)
                param = new SqlParameter(paramName, dbType, size);
            else
                param = new SqlParameter(paramName, dbType);
            param.Value = value;

            return param;
        }
        #endregion

        #region AddOutParameter 添加输出参数
        /// <summary>
        /// 添加Out参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">数据类型</param>
        /// <returns>返回一个SqlParameter对象</returns>
        public static SqlParameter AddOutParameter(string paramName, SqlDbType dbType)
        {
            return AddOutParameter(paramName, dbType, 0, null);
        }

        /// <summary>
        /// 添加Out参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">数据类型</param>
        /// <param name="size">字段大小</param>
        /// <returns>返回一个SqlParameter对象</returns>
        public static SqlParameter AddOutParameter(string paramName, SqlDbType dbType, int size)
        {
            return AddOutParameter(paramName, dbType, size, null);
        }
        public static SqlParameter AddOutParameter(string paramName, SqlDbType dbType, int size, object value)
        {
            SqlParameter param;
            if (size > 0)
            {
                param = new SqlParameter(paramName, dbType, size);
            }
            else
            {
                param = new SqlParameter(paramName, dbType);
            }
            if (value != null)
            {
                param.Value = value;
            }
            param.Direction = ParameterDirection.Output;

            return param;
        }
        #endregion

        #region PrepareCommand 
        private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, CommandType cmdType, string cmdText, int timeout, SqlParameter[] cmdParms)
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (timeout > 30) cmd.CommandTimeout = timeout;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && (parm.Value == null))
                    {
                        parm.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(parm);
                }
            }
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch { }
            }
          
        }
        private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, CommandType cmdType, string cmdText, int timeout, List<SqlParameter> cmdParms)
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (timeout > 30) cmd.CommandTimeout = timeout;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && (parm.Value == null))
                    {
                        parm.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }
            }
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch { }
            }
           
        }
        #endregion

        #region ConnClose 关闭数据库连接
        private static void ConnClose(SqlConnection conn)
        {
            if (conn!=null && conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
        #endregion

        #region 直接返回Model

        public static T ExecuteModel<T>(string cmdText, params  SqlParameter[] cmdParms) where T : new()
        {
            return ExecuteModel<T>(cmdText, CommandType.Text, 30, cmdParms);
        }
        public static T ExecuteModel<T>(string cmdText, List<SqlParameter> cmdParms) where T : new()
        {
            return ExecuteModel<T>(cmdText, CommandType.Text, 30, cmdParms);
        }
        public static T ExecuteModel<T>(string cmdText) where T : new()
        {
            return ExecuteModel<T>(cmdText, CommandType.Text, 30, new List<SqlParameter>());
        }

        public static T ExecuteModel<T>(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms) where T : new()
        {
            try
            {
                DataTable t = ExecuteDataTable(cmdText, cmdType, timeOut, cmdParms);
                if (t.Rows.Count > 0)
                {
                    return DataConvert.DataRowToModel<T>(t.Rows[0]);
                }
                else
                {
                    return default(T);
                }
            }
            catch (Exception ex)
            {
                
               
                /*使用 default 关键字，此关键字对于引用类型会返回空，对于数值类型会返回零。对于结构，
                 * 此关键字将返回初始化为零或空的每个结构成员，具体取决于这些结构是值类型还是引用类型*/
                return default(T);
            }
        }

        public static T ExecuteModel<T>(string cmdText, CommandType cmdType, int timeOut, List<SqlParameter> cmdParms) where T : new()
        {
            try
            {
                DataTable t = ExecuteDataTable(cmdText, cmdType, timeOut, cmdParms);
                if (t.Rows.Count > 0)
                {
                    return DataConvert.DataRowToModel<T>(t.Rows[0]);
                }
                else
                {
                    return default(T);
                }
            }
            catch (Exception ex)
            {

             
                return default(T);
            }
        }
        #endregion

        #region 直接返回IList

        public static IList<T> ExecuteIList<T>(string cmdText, params  SqlParameter[] cmdParms) where T : new()
        {
            return ExecuteIList<T>(cmdText, CommandType.Text, 30, cmdParms);
        }
        public static IList<T> ExecuteIList<T>(string cmdText, List<SqlParameter> cmdParms) where T : new()
        {
            return ExecuteIList<T>(cmdText, CommandType.Text, 30, cmdParms);
        }
        public static IList<T> ExecuteIList<T>(string cmdText) where T : new()
        {
            return ExecuteIList<T>(cmdText, CommandType.Text, 30, new List<SqlParameter>());
        }

        public static IList<T> ExecuteIList<T>(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms) where T : new()
        {
            try
            {
                DataTable t = ExecuteDataTable(cmdText, cmdType, timeOut, cmdParms);
                if (t.Rows.Count > 0)
                {
                    return DataConvert.DataTableToList<T>(t);
                }
                else
                {
                    return new List<T>();
                }
            }
            catch (Exception ex)
            {
             
                return new List<T>();
            }
        }

        public static IList<T> ExecuteIList<T>(string cmdText, CommandType cmdType, int timeOut, List<SqlParameter> cmdParms) where T : new()
        {
            try
            {
                DataTable t = ExecuteDataTable(cmdText, cmdType, timeOut, cmdParms);
                if (t.Rows.Count > 0)
                {
                    return DataConvert.DataTableToList<T>(t);
                }
                else
                {
                    return new List<T>();
                }
            }
            catch (Exception ex)
            {
              
                return new List<T>();
            }
        }

        #endregion

        #region 返回DataTable,DataSet,执行更新
      
        /// <summary>
        /// 标量查询，返回查询结果集中第一行的第一列。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回值</returns>
        public static object ExecuteScalar(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    object retval = null;
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                        retval = myCmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                       
                        return null;
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        ConnClose(myConn);
                    }
                    return retval;
                }
            }
        }
        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    SqlDataAdapter myda = null;
                    DataTable dt = new DataTable();
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                        myda = new SqlDataAdapter(myCmd);
                        myda.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        
                       
                        return new DataTable();
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        myda.Dispose();
                        ConnClose(myConn);
                    }
                    return dt;
                }
            }
        }
        /// <summary>
        /// 创建DataSet
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataSet 对象。</returns>
        public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    SqlDataAdapter myda = null;
                    DataSet ds = new DataSet();
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                        myda = new SqlDataAdapter(myCmd);
                        myda.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        
                        return new DataSet();
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        myda.Dispose();
                        ConnClose(myConn);
                    }
                    return ds;
                }
            }
        }
        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, int timeOut, List<SqlParameter> cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    SqlDataAdapter myda = null;
                    DataTable dt = new DataTable();
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                        myda = new SqlDataAdapter(myCmd);
                        myda.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                       
                        return new DataTable();
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        myda.Dispose();
                        ConnClose(myConn);
                    }
                    return dt;
                }
            }
        }
        
        #endregion

        #region 快捷方法

        #region ExecuteDataTable 创建DataTable
        public static DataTable ExecuteDataTable(string cmdText)
        {
            return ExecuteDataTable(cmdText, CommandType.Text, 30, new List<SqlParameter>());
        }
        public static DataTable ExecuteDataTable(string cmdText, List<SqlParameter> cmdParms)
        {
            return ExecuteDataTable(cmdText, CommandType.Text, 30, cmdParms);
        }

        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataTable(cmdText, CommandType.Text, 60, cmdParms);
        }
        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns> DataTable 对象。</returns>
        public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteDataTable(cmdText, cmdType, 60, cmdParms);
        }


        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="ConnectionString">数据库连接字符串</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataTable ExecuteDataTable(string ConnectionString, string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataTable(ConnectionString, cmdText, CommandType.Text, cmdParms);
        }

        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="ConnectionString">数据库连接字符串</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataTable ExecuteDataTable(string ConnectionString, string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    SqlDataAdapter myda = null;
                    DataTable dt = new DataTable();
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, 60, cmdParms);
                        myda = new SqlDataAdapter(myCmd);
                        myda.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        myda.Dispose();
                        ConnClose(myConn);
                    }
                    return dt;
                }
            }
        }
        #endregion

        #region ExecuteDataRow 返回一行数据
        public static DataRow ExecuteDataRow(string cmdText)
        {
            return ExecuteDataRow(cmdText, CommandType.Text, 30, new List<SqlParameter>());
        }
        public static DataRow ExecuteDataRow(string cmdText, List<SqlParameter> cmdParms)
        {
            return ExecuteDataRow(cmdText, CommandType.Text, 30, cmdParms);
        }

        /// <summary>
        /// DataRow
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataRow ExecuteDataRow(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataRow(cmdText, CommandType.Text, 60, cmdParms);
        }
        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns> DataTable 对象。</returns>
        public static DataRow ExecuteDataRow(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteDataRow(cmdText, cmdType, 60, cmdParms);
        }


        /// <summary>
        /// DataRow
        /// </summary>
        /// <param name="ConnectionString">数据库连接字符串</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataRow ExecuteDataRow(string ConnectionString, string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataRow(ConnectionString, cmdText, CommandType.Text, cmdParms);
        }

        /// <summary>
        /// DataRow
        /// </summary>
        /// <param name="ConnectionString">数据库连接字符串</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataRow ExecuteDataRow(string ConnectionString, string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            DataTable table = ExecuteDataTable(ConnectionString, cmdText, cmdType, cmdParms);
            if (table != null) {
                if (table.Rows.Count > 0)
                {
                    return table.Rows[0];
                }
            }
            return null;
        }

        /// <summary>
        /// 返回DataRow
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataRow ExecuteDataRow(string cmdText, CommandType cmdType, int timeOut, List<SqlParameter> cmdParms)
        {
            DataTable table = ExecuteDataTable( cmdText,  cmdType,  timeOut, cmdParms);
            if (table != null)
            {
                if (table.Rows.Count > 0)
                {
                    return table.Rows[0];
                }
            }
            return null;
        }
        /// <summary>
        /// DataRow
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>DataTable 对象。</returns>
        public static DataRow ExecuteDataRow(string cmdText, CommandType cmdType, int timeOut, params SqlParameter[] cmdParms)
        {
            DataTable table = ExecuteDataTable(cmdText, cmdType, timeOut, cmdParms);
            if (table != null)
            {
                if (table.Rows.Count > 0)
                {
                    return table.Rows[0];
                }
            }
            return null;
        }
        #endregion

        #region ExecuteNonQuery
        public static int ExecuteNonQueryStoredProcedure(string cmdText, params  SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, 30, cmdParms);
        }

        public static int ExecuteNonQuery(string cmdText, List<SqlParameter> cmdParms)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, 30, cmdParms);
        }
        public static int ExecuteNonQuery(string cmdText)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, 30, new List<SqlParameter>());
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, 60, cmdParms);
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(cmdText, cmdType, 60, cmdParms);
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, int timeOut, List<SqlParameter> cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    int retval = 0;
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                        retval = myCmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {

                        return 0;
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        ConnClose(myConn);
                    }
                    return retval;
                }
            }
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                using (SqlCommand myCmd = new SqlCommand())
                {
                    int retval = 0;
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                        retval = myCmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;

                        return 0;
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        myCmd.Dispose();
                        ConnClose(myConn);
                    }
                    return retval;
                }
            }
        }
        #endregion

        #region ExecuteDataSet 创建DataSet
        /// <summary>
        /// 创建DataSet
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns> DataSet 对象。</returns>
        public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataSet(cmdText, CommandType.Text, 60, cmdParms);
        }

        /// <summary>
        /// 创建DataSet
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">设Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns> DataSet 对象。</returns>
        public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteDataSet(cmdText, cmdType, 60, cmdParms);
        }
        #endregion

        #region ExecuteDataReader 创建SqlDataReader
        /// <summary>
        /// 创建 SqlDataReader。
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns> SqlDataReader 对象。</returns>
        public static SqlDataReader ExecuteDataReader(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataReader(cmdText, CommandType.Text, 60, cmdParms);
        }
        /// <summary>
        /// 创建 SqlDataReader。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>一个 SqlDataReader 对象。</returns>
        public static SqlDataReader ExecuteDataReader(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteDataReader(cmdText, cmdType, 60, cmdParms);
        }
        /// <summary>
        /// 创建 SqlDataReader。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>一个 SqlDataReader 对象。</returns>
        public static SqlDataReader ExecuteDataReader(string cmdText, CommandType cmdType, int timeOut, params SqlParameter[] cmdParms)
        {
            SqlConnection myConn = new SqlConnection(connectionString);
            SqlCommand myCmd = new SqlCommand();
            SqlDataReader dr = null;
            try
            {
                PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                ConnClose(myConn);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmdParms != null)
                {
                    myCmd.Parameters.Clear();
                }
            }
            return dr;
        }
        #endregion

        #region  ExecuteNonQueryTran 执行带事务的批量SQL语句
        public static int ExecuteNonQueryTranStoredProcedure(string cmdText, params  SqlParameter[] cmdParms)
        {
            return ExecuteNonQueryTran(cmdText, CommandType.StoredProcedure, 30, cmdParms);
        }

        public static int ExecuteNonQueryTran(string cmdText, List<SqlParameter> cmdParms)
        {
            return ExecuteNonQueryTran(cmdText, CommandType.Text, 30, cmdParms);
        }
        public static int ExecuteNonQueryTran(string cmdText)
        {
            return ExecuteNonQueryTran(cmdText, CommandType.Text, 30, new List<SqlParameter>());
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQueryTran(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQueryTran(cmdText, CommandType.Text, 60, cmdParms);
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQueryTran(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQueryTran(cmdText, cmdType, 60, cmdParms);
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQueryTran(string cmdText, CommandType cmdType, int timeOut, List<SqlParameter> cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                myConn.Open();
                SqlTransaction tran = myConn.BeginTransaction();
                using (SqlCommand myCmd = new SqlCommand())
                {
                    int retval = 0;
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);
                      
                        myCmd.Transaction = tran;
                        retval = myCmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return 0;
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        tran.Dispose();
                        myCmd.Dispose();
                        ConnClose(myConn);
                    }
                    return retval;
                }
            }
        }
        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="timeOut">超时时间</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回受影响的行数。</returns>
        public static int ExecuteNonQueryTran(string cmdText, CommandType cmdType, int timeOut, params  SqlParameter[] cmdParms)
        {
            using (SqlConnection myConn = new SqlConnection(connectionString))
            {
                myConn.Open();
                SqlTransaction tran = myConn.BeginTransaction();
                using (SqlCommand myCmd = new SqlCommand())
                {
                    int retval = 0;
                    try
                    {
                        PrepareCommand(myConn, myCmd, cmdType, cmdText, timeOut, cmdParms);

                        myCmd.Transaction = tran;
                        retval = myCmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return 0;
                    }
                    finally
                    {
                        if (cmdParms != null)
                        {
                            myCmd.Parameters.Clear();
                        }
                        tran.Dispose();
                        myCmd.Dispose();
                        ConnClose(myConn);

                    }
                    return retval;
                }
            }
        }

        #endregion

        #region ExecuteScalar 执行标量查询
        /// <summary>
        /// 标量查询，返回查询结果集中第一行的第一列。
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回值</returns>
        public static Object ExecuteScalar(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteScalar(cmdText, CommandType.Text, 60, cmdParms);
        }
        /// <summary>
        /// 标量查询，返回查询结果集中第一行的第一列。
        /// </summary>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回值</returns>
        public static int ExecuteScalar<Int32>(string cmdText, params SqlParameter[] cmdParms) 
        { 
          
            object obj= ExecuteScalar(cmdText, CommandType.Text, 60, cmdParms);
            int result = -1;
            if (obj != null) {

                int.TryParse(obj.ToString(), out result);
            }
            return result;
        }

        /// <summary>
        /// 标量查询，返回查询结果集中第一行的第一列。
        /// </summary>
        /// <param name="cmdType">该值指示如何解释 CommandText 属性</param>
        /// <param name="cmdText">Transact-SQL 语句或存储过程。</param>
        /// <param name="cmdParms">参数列表，params变长数组的形式</param>
        /// <returns>返回值</returns>
        public static Object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            return ExecuteScalar(cmdText, cmdType, 60, cmdParms);
        }
        #endregion

        #region ExecuteBulkInsert 批量插入数据
        public static int ExecuteBulkInsert(DataTable t)
        {
            try
            {
                SqlBulkCopy bulk = new SqlBulkCopy(connectionString);
                bulk.BatchSize = t.Rows.Count;
                bulk.DestinationTableName = t.TableName;
                bulk.WriteToServer(t);
                return t.Rows.Count;
            }
            catch {
                return 0;
            }
        }
      
        #endregion

        #region getMaxID
        public static int getMaxID(string fieldName, string tableName)
        {
            string sql = "select max("+fieldName+") from "+tableName;
            object obj=  ExecuteScalar(sql);
            int maxId = -1;
            if (obj != null) {
                int.TryParse(obj.ToString(), out maxId);
            }
            return maxId;
        }
        #endregion

        #endregion

        #region 返回分页列表
        public static DataTable getList(TableEnum tableName, string getFields, string OrderField, string whereCondition, int pageSize, int pageIndex, ref int recordCount)
        {
            try
            {
                int startIndex = pageSize * (pageIndex - 1);
                string sql = "select " + getFields + " from [" + Enum.GetName(typeof(TableEnum), tableName)+"]";
                if (!string.IsNullOrEmpty(whereCondition)) {
                    sql += " where " + whereCondition;
                }
              string sqlCount = "select count(1) from (" + sql + ") T";
              sql += " order by " + OrderField;// +" limit " + startIndex + "," + pageSize;//跳过多少条，选择多少条,
              /*
               如果我要去11-20的Account表的数据 Select * From Account Limit 9 Offset 10; 
               * 以上语句表示从Account表获取数据，跳过10行，取9行嗯，
               * 也可以这样写 select * from account limit 10,9和上面的的效果一样。 通用公试: 
               * sql = "select * from FlyCrocodile where "+条件+" order by "+排序+" limit "+要显示多少条记录+" offset "+跳过多少条记录;
               * 如: select * from flycrocodile limit 15 offset 20  
               * 意思是说:   从flycrocodile表跳过20条记录选出15条记录
               */
              object obj = ExecuteScalar(sqlCount);
              DataTable table = new DataTable();
              int total = 0;
              if (obj != null)
              {
                  if (int.TryParse(obj.ToString(), out total))
                  {
                      table= ExecuteDataTable(sql);
                  }
              }
              recordCount = total;
              return table;
            }
            catch (Exception ex)
            {
                
               
                recordCount = 0;
                return new DataTable();
            }
        }
        public static IList<T> ExecuteIList<T>(TableEnum tableName, string getFields, string OrderField, string whereCondition, int pageSize, int pageIndex, ref int recordCount) where T : new()
        {
            DataTable table=getList( tableName,  getFields,  OrderField,  whereCondition,  pageSize,  pageIndex, ref  recordCount);
            return DataConvert.DataTableToList<T>(table);
        }
        public static DataTable getList(string cmdText, int pageSize, int pageIndex, ref int recordCount)
        {
            try
            {
                int startIndex = pageSize * (pageIndex - 1);
                string sqlCount = "select count(1) from (" + cmdText + ")";
                object obj = ExecuteScalar(sqlCount);
                DataTable table = new DataTable();
                int total = 0;
                if (obj != null)
                {
                    if (int.TryParse(obj.ToString(), out total))
                    {
                        table = ExecuteDataTable(cmdText);
                    }
                }
                recordCount = total;
                return table;
            }
            catch (Exception ex)
            {

               
                recordCount = 0;
                return new DataTable();
            }
        }
        public static DataTable FY(string cmdText, int pageSize, int pageIndex,string OrderField, ref int recordCount)
        {
            try
            {
                string sqlCount = "select count(1) from (" + cmdText + ")";
                cmdText = "select * from (select top " + pageSize + " * from (select top " + pageSize * pageIndex + " * from (" + cmdText + ") tmp order by " + OrderField + " desc) order by " + OrderField + ") order by " + OrderField + " desc";
                object obj = ExecuteScalar(sqlCount);
                DataTable table = new DataTable();
                int total = 0;
                if (obj != null)
                {
                    if (int.TryParse(obj.ToString(), out total))
                    {
                        table = ExecuteDataTable(cmdText);
                    }
                }
                recordCount = total;
                return table;
            }
            catch (Exception ex)
            {

              
                recordCount = 0;
                return new DataTable();
            }
        }
      
        #endregion

    }
    public enum TableEnum { }

}