专为新手写的数据库连接使用类，诚挚邀请大家多提宝贵意见

Question

说明:这个是专为新手（也是为了自己）写的一个数据库连接使用类,是正在完善更新的一个类,现在放出来的目的不是马上让大家使用，所以也没有把注解写的非常的详细.声明:我写这个的目的主要为了锻炼自己,尽管我知道现在已经有很多类似的类了,但是我还是决定来写一个,诚望各位高手多提意见,多多指教,先谢了!好了,废话少说了,下面帖代码

Answer 1

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;namespace Stm_DbBuild
{
    public class DbBuilder
    {
        public DbBuilder()
        {
        }
        //分别定义一个SqlConnection及SqlCommand对象,用于对数据库的操作
        protected SqlConnection conn=new SqlConnection();
        protected SqlCommand comm=new SqlCommand();        /// <summary>
        /// 打开数据库连接1
        /// </summary>
        protected void OpenConnection()
        {
            CheckConnection();
        }
        /// <summary>
        /// 检查数据库连接是否打开
        /// </summary>
        /// <returns>bool</returns>
        public bool CheckConnection()
        {
            //如果数据连接是关闭的,则获取其连接字符串并打开
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    //获取数据库连接字符串
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                    comm.Connection = conn;
                    conn.Open();//打开连接
                }
                catch (Exception)
                {
                    return false;
                }                
            }  
            return true;          
        }
        /// <summary>
        /// 关闭当前数据库连接
        /// </summary>
        protected void CloseConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Dispose();
            comm.Dispose();
        }
        /// <summary>
        /// 执行SQL查询语句
        /// </summary>
        /// <param name="sqlText">传入的SQL语句</param>
        public void ExcuteSql(string sqlText)
        {
            try
            {
                OpenConnection();//打开数据库连接
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlText;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);//抛出连接的异常信息
            }
            finally
            {
                CloseConnection();//关闭连接
            }
        }
        public int ExcuteCmdSql(string sqlText)
        {
            try
            {
                OpenConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlText;
                return comm.ExecuteNonQuery();//执行查询,并返回一整数,如果大于0,表示查询成功,否则表示失败
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                CloseConnection();
            }
            return 1;
        }
        /// <summary>
        /// 返回指定SQL语句的DataTable
        /// </summary>
        /// <param name="sqlText">传入的SQL语句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string sqlText)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                OpenConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlText;
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception)
            {
            }
            finally
            {
                CloseConnection();
            }            return datatable;
        }
        /// <summary>
        /// 返回指定SQL语句的DataSet
        /// </summary>
        /// <param name="sqlText">传入的SQL语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sqlText)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet dataset = new DataSet();
            try
            {
                OpenConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlText;
                da.SelectCommand = comm;
                da.Fill(dataset);
            }
            catch (Exception)
            {
            }
            finally
            {
                da.Dispose();
                CloseConnection();//关闭连接
            }            return dataset;
        }        /// <summary>
        /// 建立数据库连接方法2
        /// 返回一个连接的对象
        /// </summary>
        /// <returns>SqlConnection</returns>
        public SqlConnection GetSqlConn()
        {         
            //获取连接到数据库的字符串
            string  connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            SqlConnection conn1 = new SqlConnection(connStr);
            conn1.Open();//打开数据库连接
            return conn1;//返回连接对象
        }        /// <summary>
        /// 实例化SqlCommand对象
        /// 返回一个SqlCommand对象
        /// </summary>
        /// <param name="sqlText">sql语句</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand GetSqlComm(string sqlText)
        {
            return new SqlCommand(sqlText, GetSqlConn());//返回对象成员
        }        /// <summary>
        /// 执行sql语句进行数据查询
        /// </summary>
        /// <param name="sqlText">sql语句</param>
        /// <returns></returns>
        public int cmd(string sqlText)
        {
            comm = GetSqlComm(sqlText);//实例化一个执行对象
            return comm.ExecuteNonQuery();//执行查询,并返回一整数,如果大于0,表示查询成功,否则表示失败
        }
        /// <summary>
        /// 关闭连接2
        /// </summary>
        /// <param name="comm"></param>
        public void CloseSqlCommand(SqlCommand comm)
        {
            comm.Connection.Close();
            comm.Connection.Dispose();
            comm.Dispose();
        }
    }
}

Answer 2

我觉得很一般，
只能执行sql语句，存储过程怎么办？

Answer 3

补上存储过程，还有事务
最好用using 来自动释放资源

Answer 4

还有，不要加那么多Web的命名空间。这种基础类应该是B/S和C/S系统都能用的。估计你是把它放在App_Code里了。

Answer 5

楼主什么时候出新版本啊？
比如一键切换为ACCESS,MSSQL,MYSQL,ORACLE，
再比如弄成类库，到时候大家就各自using就好了

Answer 6

下载个sqlhelper把不常用的东西去掉,就可以了.