C#数据库(MSSQL)帮助类

    /// <summary>
    /// 数据库帮助类
    /// <author>Devin</author>
    /// </summary>
    public sealed class DBHelper
    {
        private DBHelper() { }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string connStr = System.Configuration.ConfigurationManager.AppSettings["connStr"].ToString();


        /// <summary>
        /// 一个有效的数据库连接对象
        /// </summary>
        private static SqlConnection _MyConnection;
        public static SqlConnection MyConnection
        {
            get
            {
                if(_MyConnection == null)
                {
                    _MyConnection = new SqlConnection(connStr);
                }
                return _MyConnection;
            }
        }


        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">参数集</param>
        /// <returns></returns>
        public static int MyExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = MyConnection;
            try
            {
                PrepareCommand(cmd, conn, null, cmdText, commandParameters);
                result = cmd.ExecuteNonQuery();
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
            }
            return result;
        }



        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">参数集</param>
        /// <returns></returns>
        public static DataSet MyExecuteDataset(string cmdText, params SqlParameter[] commandParameters)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = MyConnection;
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, conn, null, cmdText, commandParameters);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                try
                {
                    da.Fill(ds);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
            return ds;
        }

        /// <summary>
        /// 返回DataReader (使用后请关闭DataReader)
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">参数集</param>
        /// <returns></returns>
        public static SqlDataReader MyExecuteReader(string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection conn = MyConnection;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd,conn,null,cmdText,commandParameters);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭DataReader的同时，Connection也将同时关闭
                return myReader;
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
            }
        }

        /// <summary>
        /// 返回首行首列的值
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">参数集</param>
        /// <returns></returns>
        public static object MyExecuteScalar(string cmdText,params SqlParameter[] commandParameters)
        {
            SqlConnection conn = MyConnection;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd,conn,null,cmdText,commandParameters);
                return cmd.ExecuteScalar();
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
            }
        }

        /// <summary>
        /// 设置SqlCommand
        /// </summary>
        /// <param name="cmd">执行对象</param>
        /// <param name="conn">连接对象</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="cmdParms">参数集</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }