public class SQLServerHelper
    {
        //连接字符串
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["SPEEDLINK"].ToString();
        private static SqlConnection conn = new SqlConnection(connectionString);

        /// <summary>
        /// 封装对数据库的增、删、改方法
        /// </summary>
        /// <param name="cmdText">SQL语句字符串</param>
        /// <param name="cmdType">命令类型：存储过程或SQL语句</param>
        /// <param name="cmdParams">传入的参数，这里是一个动态数组</param>
        /// <returns>返回数据影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
        {

                SqlCommand comm = new SqlCommand();

                //详细情况参见下面的方法存根
                PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
                try
                {
                    return comm.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally 
                {
                    //comm.Parameters.Clear();
                    conn.Close();
                }

        }

        /// <summary>
        /// 封装Command对象的ExecuteReader方法，用于数据查询
        /// </summary>
        /// <param name="cmdText">SQL语句字符串</param>
        /// <param name="cmdType">命令类型：存储过程或SQL语句</param>
        /// <param name="cmdParams">传入的参数，这里是一个动态数组</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams) 
        {
            SqlCommand comm = new SqlCommand();
            PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
            try
            {
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用于封装Command对象的ExecuteScalar方法，返回首行首列数据
        /// </summary>
        /// <param name="cmdText">SQL语句字符串</param>
        /// <param name="cmdType">命令类型：存储过程或SQL语句</param>
        /// <param name="cmdParams">传入的参数，这里是一个动态数组</param>
        /// <returns>返回object对象</returns>
        public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
        {
            SqlCommand comm = new SqlCommand();
            PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);
            try
            {
                return comm.ExecuteScalar();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 主要用于返回DataTable的查询数据
        /// </summary>
        /// <param name="cmdText">SQL语句字符串</param>
        /// <param name="cmdType">命令类型：存储过程或SQL语句</param>
        /// <param name="cmdParams">传入的参数，这里是一个动态数组</param>
        /// <returns>返回DataTable对象</returns>
        public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams)
        {
            SqlCommand comm = new SqlCommand();
            PrepareCommand(comm, conn, cmdText, cmdType, cmdParams);

            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = comm;
            DataSet ds = new DataSet();

            try
            {
                sda.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally 
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 用于对Command对象做初始化赋值工作
        /// </summary>
        /// <param name="comm">Command命令</param>
        /// <param name="conn">数据库连接</param>
        /// <param name="cmdText">指令语句</param>
        /// <param name="cmdType">指令类型</param>
        /// <param name="cmdParams">指令参数</param>
        private static void PrepareCommand(SqlCommand comm, SqlConnection conn, string cmdText, CommandType cmdType, SqlParameter[] cmdParams)
        {
            try
            {
                //检测数据库连接状态，如果关闭则开启数据库连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                    //comm.Parameters.Clear();
                }

                comm.Connection = conn;
                comm.CommandText = cmdText;
                comm.CommandType = cmdType;

                //如果命令参数不为空，则将参数全部引用
                if (cmdParams != null)
                {

                    for (int i = 0; i < cmdParams.Length; i++)
                    {
                        comm.Parameters.Add(cmdParams[i]);
                    }
                }
            }
            catch (Exception ex) 
            {
                throw ex;
            }
 
        }

    }