using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Configuration;using System.Data;using System.Data.SqlClient;namespace SqlHelper{    public static class SqlHelper    {        /// 
        /// 数据库连接字符串字段，默认取值配置文件        /// 
        private static string strConn = ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings["DCS"]].ConnectionString;        /// 
        /// 根据默认配置创建一个SqlConnection对象        /// 
        private static SqlConnection conn = new SqlConnection(strConn);        private static SqlCommand cmd = new SqlCommand();        private static SqlTransaction tran = null;        /// 
        /// 数据库连接字符串属性，可重新设置数据库连接字符串        /// 
        public static string ConnectionString        {            get            {                return strConn;            }            set            {                strConn = value;                // 数据库连接字符串更改时，同时更改SqlConnection的ConnectionString属性                conn.ConnectionString = strConn;            }        }        /// 
        /// 获取SqlConnection对象        /// 
        public static SqlConnection Connection        {            get            {                return conn;            }        }        static SqlHelper()        {            if (conn.State != ConnectionState.Open)            {                conn.Open();            }        }        #region 读取数据        public static T ExecuteScalar
     
      (CommandType commandType, string commandText, params SqlParameter[] parms)        {            PrepareCommand(cmd, conn, null, commandType, commandText, parms);            object result = cmd.ExecuteScalar();            Close();            if (result != null)            {                return (T)Convert.ChangeType(result, typeof(T));            }            return default(T);        }        public static SqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params SqlParameter[] parms)        {            PrepareCommand(cmd, conn, null, commandType, commandText, parms);            return cmd.ExecuteReader(CommandBehavior.CloseConnection);        }        public static DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] parms)        {            PrepareCommand(cmd, conn, null, commandType, commandText, parms);            SqlDataAdapter da = new SqlDataAdapter(cmd);            DataSet ds = new DataSet();            da.Fill(ds);            Close();            return ds;        }        public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] parms)        {            return ExecuteDataSet(commandType, commandText, parms).Tables[0];        }        #endregion        #region 增、删、改（无事务处理）        public static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parms)        {            PrepareCommand(cmd, conn, null, commandType, commandText, parms);            int count = cmd.ExecuteNonQuery();            Close();            return count;        }        #endregion        #region 增、删、改（带事务处理）        public static void BeginTransaction()        {            if(conn.State==ConnectionState.Closed)            {                conn.Open();            }            tran = conn.BeginTransaction();        }               public static void CommitTransaction()        {            tran.Commit();            Close();        }        public static void RollbackTransaction()        {            tran.Rollback();            Close();        }        public static int ExecuteNonQueryWithTransaction(CommandType commandType, string commandText, params SqlParameter[] parms)        {            PrepareCommand(cmd, conn, tran, commandType, commandText, parms);            int count = cmd.ExecuteNonQuery();            return count;        }        #endregion        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parms)        {            if (connection.State != ConnectionState.Open)            {                connection.Open();            }                      // 设置数据库连接            command.Connection = connection;            // 设置命令文本(存储过程名或SQL语句)            command.CommandText = commandText;            // 设置命令类型.            command.CommandType = commandType;            // 分配事务            if (transaction != null)            {                command.Transaction = transaction;            }            // 清除SqlCommand的参数            command.Parameters.Clear();            if (parms != null && parms.Length > 0)            {                // 预处理SqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;                foreach (SqlParameter parameter in parms)                {                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))                    {                        parameter.Value = DBNull.Value;                    }                }                command.Parameters.AddRange(parms);            }        }        /// 
      
        /// 关闭连接        /// 
        private static void Close()        {            if(conn.State== ConnectionState.Open)            {                conn.Close();            }                    }    }}