重拾MVC——第一天：数据库连接与SqlDbHelper

这个 SqlDbHelper 是我参考网上的和以前用过的 SqlDbHelper 自己写的一个非常简单的东西，主要是记录自己的学习情况

首先在Web.config中配置数据库连接字符串:

  <connectionStrings>
    <add name="TestConnectionStrings" connectionString="Data Source=服务器地址;Initial Catalog=数据库名称;User ID=sa;Password=密码" providerName="System.Data.SqlClient" />
  </connectionStrings>

然后新建DbHelper文件夹，创建SqlDbHelper类：

    public class DbHelper
    {
        private string connectionString = "";
        protected SqlConnection conn = null;

        #region  //构造函数
        public DbHelper()
        {
            //this.connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
            this.connectionString = ConfigurationManager.ConnectionStrings["TestConnectionStrings"].ConnectionString;
            this.conn = new SqlConnection(connectionString);
        }
        #endregion

        #region //打开连接
        public void OpenDb()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
        #endregion

        #region //关闭连接
        public void CloseDb()
        {
            if (!object.Equals(conn, null) && (conn.State != ConnectionState.Closed))
            {
                conn.Close();
            }
        }
        #endregion

        #region //释放连接
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
        #endregion

        #region  //执行单条SQL(插入、更新、删除)
        /// <summary>
        /// 执行单条SQL(插入、更新、删除)
        /// </summary>
        /// <param name="sql_"></param>
        public void ExecuteNonQuery(string sql_)
        {
            try
            {
                OpenDb();
                SqlCommand cm = new SqlCommand(sql_, conn);
                cm.ExecuteNonQuery();
                cm.Dispose();
                cm = null;
                CloseDb();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql_);
            }
        }
        #endregion

        #region  //用于执行带参的SQL(插入、更新、删除)
        /// <summary>
        /// 用于执行带参的SQL(插入、更新、删除)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameter">参数化查询</param>
        /// <returns>有多少语句执行成功</returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
        {
            try
            {
                OpenDb();
                SqlCommand cmd = conn.CreateCommand();
                //SqlCommand cm = new SqlCommand(sql_, conn);
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);
                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql);
            }
        }
        #endregion

        #region  //执行查询语句，返回SqlDataReader
        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameter)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);
                //return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql);
            }
        }
        #endregion

        #region  //执行查询语句，返回DataTable
        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <param name="cmdParms"></param>
        /// <returns>DataSet</returns>
        public DataTable GetDataTable(string sql, params SqlParameter[] parameter)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);

                using (var da = new SqlDataAdapter(cmd))
                {
                    var dt = new DataTable();
                    var ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        if (ds.Tables.Count <= 0)
                            return null;
                        dt = ds.Tables[0];

                        cmd.Parameters.Clear();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql);
            }
        }
        #endregion

        #region  //执行查询语句，返回DataSet
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <param name="cmdParms"></param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sql, params SqlParameter[] parameter)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);

                using (var da = new SqlDataAdapter(cmd))
                {
                    var ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
        #endregion

    }

　　在这其中，先获得Web.config中的连接数据库的字符串：

        private string connectionString = "";
        protected SqlConnection conn = null;

        #region  //构造函数
        public DbHelper()
        {
            //this.connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
            this.connectionString = ConfigurationManager.ConnectionStrings["TestConnectionStrings"].ConnectionString;
            this.conn = new SqlConnection(connectionString);
        }
        #endregion

　　然后写连接打开/关闭/释放的方法

        #region //打开连接
        public void OpenDb()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
        #endregion

        #region //关闭连接
        public void CloseDb()
        {
            if (!object.Equals(conn, null) && (conn.State != ConnectionState.Closed))
            {
                conn.Close();
            }
        }
        #endregion

        #region //释放连接
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
        #endregion

　　下面写增删改的方法，我写了两种，带参的和不带参的

        #region  //执行单条SQL(插入、更新、删除)
        /// <summary>
        /// 执行单条SQL(插入、更新、删除)
        /// </summary>
        /// <param name="sql_"></param>
        public void ExecuteNonQuery(string sql_)
        {
            try
            {
                OpenDb();
                SqlCommand cm = new SqlCommand(sql_, conn);
                cm.ExecuteNonQuery();
                cm.Dispose();
                cm = null;
                CloseDb();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql_);
            }
        }
        #endregion

        #region  //用于执行带参的SQL(插入、更新、删除)
        /// <summary>
        /// 用于执行带参的SQL(插入、更新、删除)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameter">参数化查询</param>
        /// <returns>有多少语句执行成功</returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
        {
            try
            {
                OpenDb();
                SqlCommand cmd = conn.CreateCommand();
                //SqlCommand cm = new SqlCommand(sql_, conn);
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);
                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql);
            }
        }
        #endregion

　　然后是查询，查询我写了三种，第一中返回的 SqlDataReader :

        #region  //执行查询语句，返回SqlDataReader
        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameter)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);
                //return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql);
            }
        }
        #endregion

　　这里用完一定要对SqlDataReader进行Close

　　第二种返回的DateTable：

       #region  //执行查询语句，返回DataTable
        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <param name="cmdParms"></param>
        /// <returns>DataSet</returns>
        public DataTable GetDataTable(string sql, params SqlParameter[] parameter)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);

                using (var da = new SqlDataAdapter(cmd))
                {
                    var dt = new DataTable();
                    var ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        if (ds.Tables.Count <= 0)
                            return null;
                        dt = ds.Tables[0];

                        cmd.Parameters.Clear();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "  " + sql);
            }
        }
        #endregion

　　第二种返回的DateSet：

        #region  //执行查询语句，返回DataSet
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <param name="cmdParms"></param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sql, params SqlParameter[] parameter)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameter);

                using (var da = new SqlDataAdapter(cmd))
                {
                    var ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
        #endregion