温故而后知新——对ado.net中常用对象的一些解释

在使用ado.net连接数据库获取数据，一般的步骤是：

1、设置好web.config    //用来设置服务器数据库的地址以及登录名密码

2、创建Connection对象    //用来创建访问数据库的连接

3、创建Command对象    //用来发送命令(写sql语句)

4、用DataReader获取数据    //获取数据库数据

5、利用DataSet储存获取到的数据    //容器

6、关闭Connection对象和DataReader/DataAdapter的连接  //为了性能，不关闭可能会报错

 （其中3、4两步可以用DataAdapter对象一步代替）

一、设置web.config

打开web.config，直接在根节点加入：

<connectionStrings>
<add name="Try" connectionString="data source=数据库地址;initial catalog=数据库名称;persist security info=True;user id=登录名;password=密码;"/>
</connectionStrings>

变成

<configuration> //根节点
  <system.web>
    <compilation debug="true" targetFramework="4.5.1" />
    <httpRuntime targetFramework="4.5.1" />
  </system.web>
  <connectionStrings>
    <add name="Try" connectionString="data source=数据库地址;initial catalog=数据库名称;persist security info=True;user id=登录名;password=密码;"/>
  </connectionStrings>
</configuration>

这样就添加了一个连接字符串，名字叫做Try

 

二、Connection对象

现在要通过刚刚添加的连接字符串连接数据库了，获取web.config中的连接字符串后新建Connection对象：

using System.Configuration;
using System.Data.SqlClient;


string connectionString = ConfigurationManager.ConnectionStrings["Try"].ConnectionString; //取名字为Try的连接字符串
SqlConnection conn = new SqlConnection(connectionString); //用这个连接字符串新建Connection对象

 

 

三、Command对象

在使用Command对象之前，必须保证我们的Connection对象已经处于open状态：

conn.Open();

 

Commond对象的创建，无论用哪种形式，只要保证它与Connection对象的联系以及我们写的sql语句传进去了就行：

SqlCommand cmd = conn.CreateCommand(); //通过之前的Connection对象创建
cmd.CommandText = "select top 10 * from Project";  //写sql语句

//或者下面的直接构造函数的形式

SqlCommand sc = new SqlCommand("select top 10 * from Project", conn);

 

它有三个常用方法

1.ExecuteNonQuery();  该方法适用于非查询语句，返回受影响的行数(int)

cmd.CommandText = "update Project set Name='tom' where id=3";
int result = cmd.ExecuteNonQuery(); //返回1，即有一条数据被修改

 

2.ExecuteScalar(); 该方法返回查询结果的第一行第一列数据(object)

cmd.CommandText = "select count(*) from Project";
int result = int.Parse( cmd.ExecuteScalar() ); //返回Project表的总行数，由于返回类型是object，因此需要转换成int

 

3.ExecuteReader();   该方法返回一个DataReader对象，用于获取查询的结果，后面详解

 

 

 

四、DataReader对象和DataAdapter对象

1.DataReader的用法

　DataReader可以通过Command的ExecuteReader()方法返回，它的核心方法是Read()：

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())  //Read方法返回一个bool值，如果有值就返回true并前进到下一行，当整个表读取结束了就返回false
 {
        Response.Write(dr.GetInt32(0));  //这里假设表的第一列是int型,GetInt32()方法需要传一个int参数，从0开始的列的索引值
 }    

  这样把查询结果的第一列就读取出来的，它适合快速获取一些信息

  读取完之后记得关闭DataReader：

dr.Close();
dr.Dispose();

 

 

 

2.DataAdapter的用法

　DataAdapter对象也称之为数据适配器对象，用来填充一个DataSet容器：

DataSet a=new DataSet("ds1");
SqlDataAdapter ada = new SqlDataAdapter("select top 10 * from Project", conn);
ada.Fill(a);

 

注：

在用完后记得要关闭Connection和DataReader/DataAdapter：

ada.Dispose();
dr.Dispose();

conn.Close();
conn.Dispose();

这其实很麻烦，用完了还得关闭，为了图简便，可以使用using语法。

其完整的读取数据并返回DataSet示例如下：

static public DataSet FillDataSet(string sql,params SqlParameter[] paras)
{
    string connectionString = ConfigurationManager.ConnectionStrings["Try"].ConnectionString; //取名字为Try的连接字符串
    DataSet ds = new DataSet("ds1");
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
       using (SqlDataAdapter ada = new SqlDataAdapter(sql, conn))
         {
            ada.SelectCommand.Parameters.AddRange(paras);//传参
            ada.Fill(ds);
         }
    }
    return ds;
}

另附上完整的ExecuteNonQuery方法：

static public int ExecuteNonQuery(string sql, params SqlParameter[] paras)
{
     int result = 0;
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
          conn.Open();
          SqlCommand cmd = new SqlCommand(sql, conn);
          cmd.Parameters.AddRange(paras); //传参
          result = cmd.ExecuteNonQuery();
     }
     return result;
}

注：加了params的参数表示可以不写，即不需要传null，直接ExecuteNonQuery(string sql) 就行了

 

 

2014-12-29新增

近日感觉调用的时候有点麻烦，每次传参都需要new SqlParameter[]{new SqlParameter(){ParameterName = name, Value = val}} ; 于是将整个DBHelper类改写成如下形式：

public class DBHelper
    {
        static string connectionString = ConfigurationManager.ConnectionStrings["Try"].ConnectionString;
        static public DataSet FillDataSet(string sql, object paras)
        {
            DataSet a = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlDataAdapter ada = new SqlDataAdapter(sql, conn);
                ada.SelectCommand.Parameters.AddRange(GetParas(paras));
                ada.Fill(a);
            }
            return a;
        }
        static public List<T> FillList<T>(string sql, object paras)
        {
            DataSet a = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlDataAdapter ada = new SqlDataAdapter(sql, conn);
                ada.SelectCommand.Parameters.AddRange(GetParas(paras));
                ada.Fill(a);
            }
            return DataSetToList<T>(a, 0).ToList();
        }
        static public int ExecuteNonQuery(string sql, object paras)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(GetParas(paras));
                result = cmd.ExecuteNonQuery();
            }
            return result;
        }
        static public object ExecuteScalar(string sql, object paras)
        {
            object result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(GetParas(paras));
                result = cmd.ExecuteScalar();
            }
            return result;
        }

        /// <summary>
        /// DataSetToList
        /// </summary>
        /// <typeparam name="T">转换类型</typeparam>
        /// <param name="dataSet">数据源</param>
        /// <param name="tableIndex">需要转换表的索引</param>
        /// <returns></returns>
        private static IList<T> DataSetToList<T>(DataSet dataSet, int tableIndex)
        {
            //确认参数有效
            if (dataSet == null || dataSet.Tables.Count <= 0 || tableIndex < 0)
                return null;

            DataTable dt = dataSet.Tables[tableIndex];

            IList<T> list = new List<T>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                //获取对象所有属性
                PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    foreach (PropertyInfo info in propertyInfo)
                    {
                        //属性名称和列名相同时赋值
                        if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
                        {
                            if (dt.Rows[i][j] != DBNull.Value)
                            {
                                info.SetValue(_t, dt.Rows[i][j], null);
                            }
                            else
                            {
                                info.SetValue(_t, null, null);
                            }
                            break;
                        }
                    }
                }
                list.Add(_t);
            }
            return list;
        }
        private static SqlParameter[] GetParas(object paras)
        {
            if (paras != null)
            {
                List<SqlParameter> list = new List<SqlParameter>();
                Type t = paras.GetType();
                foreach (PropertyInfo pi in t.GetProperties())
                {

                    string name = pi.Name;
                    object val = pi.GetValue(paras, null);
                    list.Add(new SqlParameter() { ParameterName = name, Value = val });
                }
                return list.ToArray();
            }
            else
            {
                return new SqlParameter[0];
            }
        }
    }

 

调用的时候就不必再new一个SqlParameterp[]数组了，可以直接

int id = 20 , fid = 3;
string sql = "select * from tb where id=@userID and uId=@uId";
DataSet ds = DBHelper.FillDataSet(sql,new { userID = id , uId=fid });

 

传参就简单多了。缺陷是无法使用params关键字了，params关键字只能修饰一维数组。所以没有参数的时候也记得写个null，例：

string sql = "select * from tb";
DataSet ds = DBHelper.FillDataSet(sql,null);

 

 

2016-11-17新增

增加对事务的支持，整个类代码如下：

    public class DbHelper
    {
        #region 静态
        private static string ConnStr { get; set; }//静态类的连接字符串,后面的实例统一调用这个连接字符串
        /// <summary>
        /// 设置连接字符串
        /// </summary>
        /// <param name="connStr"></param>
        public static void SetConnectionString(string connStr)
        {
            DbHelper.ConnStr = connStr;
        }
        /// <summary>
        /// 将object转换成SqlParameter[]
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        private static SqlParameter[] ConvertToSqlParameter(object paras)
        {
            List<SqlParameter> list = new List<SqlParameter>();
            if (paras != null)
            {
                Type t = paras.GetType();
                foreach (PropertyInfo pi in t.GetProperties())
                {

                    string name = pi.Name;
                    object val = pi.GetValue(paras, null);
                    list.Add(new SqlParameter() { ParameterName = name, Value = val });
                }
            }
            return list.ToArray();
        }
        #endregion

        //*****************非静态*******************

        #region 基本属性
        private SqlConnection _conn;
        protected SqlConnection conn
        {
            get
            {
                if (_conn == null)
                {
                    _conn = new SqlConnection();
                }
                return _conn;
            }
        }
        private SqlCommand _cmd;
        protected SqlCommand cmd
        {
            get
            {
                if (_cmd == null)
                {
                    _cmd = new SqlCommand();
                    _cmd.Connection = conn;//设置cmd对象的conn
                }
                return _cmd;
            }
        }
        private SqlDataAdapter _adapter;
        protected SqlDataAdapter adapter
        {
            get
            {
                if (_adapter == null)
                {
                    _adapter = new SqlDataAdapter();
                }
                return _adapter;
            }
        }
        protected SqlTransaction trans { get; set; }
        private bool IsTrans { get; set; }//是否事务
        #endregion

        #region 构造函数
        public DbHelper()
        {

        }
        #endregion

        #region 打开或关闭连接对象
        /// <summary>
        /// 打开连接,如果已经打开则什么都不执行了
        /// </summary>
        private void OpenConnection()
        {
            if (this.conn.State != ConnectionState.Open)
            {
                this.conn.ConnectionString = DbHelper.ConnStr;
                this.conn.Open();
            }
        }
        /// <summary>
        /// 关闭连接,如果没有开始事务或连接打开时才关闭
        /// </summary>
        private void CloseConnect()
        {
            if (!this.IsTrans)
            {
                if (this.conn.State == ConnectionState.Open)
                {
                    this.conn.Close();
                    this.conn.Dispose();
                }
            }
        }
        /// <summary>
        /// 给当前DbCommand对象赋值,并且OpenConnection();
        /// </summary>
        private void OpenConnectAndSetCommand(string sqlText, CommandType cmdType, object param)
        {
            OpenConnection();
            this.cmd.Parameters.Clear();
            if (param != null)
            {
                this.cmd.Parameters.AddRange(DbHelper.ConvertToSqlParameter(param));
            }
            this.cmd.CommandText = sqlText;
            this.cmd.CommandType = cmdType;
        }
        #endregion

        #region 执行sql的基本方法
        /// <summary>
        /// 执行查询，并返回查询结果的第一行第一列
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="cmdType"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlText, object param = null, CommandType cmdType = CommandType.Text)
        {
            try
            {
                OpenConnectAndSetCommand(sqlText, cmdType, param);
                return this.cmd.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }
        }
        public object ExecuteScalar(string sqlText, CommandType cmdType)
        {
            return ExecuteScalar(sqlText, null, cmdType);
        }

        /// <summary>
        /// 执行非查询语句，并返回受影响的行数
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="cmdType"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlText, object param = null, CommandType cmdType = CommandType.Text)
        {
            try
            {
                OpenConnectAndSetCommand(sqlText, cmdType, param);
                return this.cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }
        }
        public int ExecuteNonQuery(string sqlText, CommandType cmdType)
        {
            return ExecuteNonQuery(sqlText, null, cmdType);
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="cmdType"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sqlText, object param = null, CommandType cmdType = CommandType.Text)
        {
            try
            {
                OpenConnectAndSetCommand(sqlText, cmdType, param);
                adapter.SelectCommand = this.cmd;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }
        }
        public DataSet GetDataSet(string sqlText, CommandType cmdType)
        {
            return GetDataSet(sqlText, null, cmdType);
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="cmdType"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sqlText, object param = null, CommandType cmdType = CommandType.Text)
        {
            try
            {
                OpenConnectAndSetCommand(sqlText, cmdType, param);
                adapter.SelectCommand = this.cmd;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }
        }
        public DataTable GetDataTable(string sqlText, CommandType cmdType)
        {
            return GetDataTable(sqlText, null, cmdType);
        }
        #endregion

        #region 关于事务的方法
        /// <summary>
        /// 开始执行事务
        /// </summary>
        public void TransBegin()
        {
            OpenConnection();
            this.IsTrans = true;
            this.trans = this.conn.BeginTransaction();
            cmd.Transaction = this.trans;
        }
        /// <summary>
        /// 事务提交
        /// </summary>
        public void TransCommit()
        {
            this.trans.Commit();
            this.IsTrans = false;
            CloseConnect();
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public void TransRollback()
        {
            this.trans.Rollback();
            this.IsTrans = false;
            CloseConnect();
        }
        #endregion

    }

 

调用方法：

            string connStr = ConfigurationManager.ConnectionStrings["MyDapper"].ConnectionString;
            DbHelper.SetConnectionString(connStr);//注册连接字符串

            DbHelper db = new DbHelper();

            db.TransBegin();

            string sql="select * from Tcar where id=1";
            var result = db.GetDataTable(sql);
           
            db.TransCommit();