ado.net快速上手實踐篇（一）

2019-11-17 03:48:07

字體：大中小

來源：轉載

供稿：網友

前言：這兩天重溫經典，對ado.net的東西稍微深入的了解了一下，順便寫點代碼練練手，全當是復習筆記吧。
一、簡單說說ado.net的5大常用對象

既然說ado.net，當然不能免俗地要提到5大常用對象。本文不會對ado.net的5大對象和它們的關系進行過多闡釋，不過我們應該對下面這張圖的結構有個了解：

關于上圖圖示中的5大對象，經常做以數據為驅動的mis系統的童鞋應該不會陌生。本文一筆帶過。下面我們一步一步實現以ado.net為核心的數據訪問程序。

【注意：下面的示例代碼和demo是樓豬本周六和周日兩天時間實現的，未經詳細測試，可能有重大bug，下載學習使用的童鞋務必注意】

二、數據訪問持久化層
1、IDbOperation接口

代碼
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace AdoNetData access.Core.Contract
{
    public interface IDbOperation
    {
        DbCommand CreateDbCommd(DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType cmdType, List<DbParameter> listParams);

DbParameter CreateDbPRameter(string paramName, object paramValue);

DbDataReader ExecuteReader(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

int ExecuteNonQuery(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="tableName">表名稱</param>
        /// <param name="dt">組裝好的要批量導入的datatable</param>
        /// <returns></returns>
        bool ExecuteBatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt);

void OpenConnection();

void CloseConnection();
}
}

上面的接口包括增刪改查，批量插入以及數據庫連接對象的連接和關閉等常用操作，您可以根據命名和參數輕松理解函數的含義。根據樓豬的開發經驗，對于平時的數據庫操作，上述方法差不多夠用了。當然您也可以按照自己需要，重寫組織添加其他函數。
2、針對一種數據源的數據操作實現
底層的數據操作接口定義好后，就要針對一種數據源，具體實現上述的數據操作。這里樓豬選擇了Sql Server。我們也可以實現其他數據源的數據訪問操作，按照配置，利用抽象工廠動態反射選擇是哪一種數據源的實現。這里按下不表，有心的童鞋自己可以動手一試。下面是具體的實現：

代碼
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Transactions;

namespace AdoNetDataAccess.Core.Implement
{
using AdoNetDataAccess.Core.Contract;

    public class SqlServer : IDbOperation, IDisposable
    {
        private int cmdTimeOut = 60;
        private DbConnection sqlConn = null;
        private DbCommand cmd = null;

private SqlServer()
{

}

        public SqlServer(string sqlConStr)
        {
            sqlConn = new SqlConnection(sqlConStr);
            cmdTimeOut = sqlConn.ConnectionTimeout;
        }

        public SqlServer(string sqlConStr, int timeOut)
        {
            sqlConn = new SqlConnection(sqlConStr);
            if (timeOut < 0)
            {
                timeOut = sqlConn.ConnectionTimeout;
            }
            cmdTimeOut = timeOut;
        }

#region contract method

        public DbCommand CreateDbCommd(DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            DbCommand cmd = new SqlCommand();
            cmd.Connection = sqlConn;
            cmd.CommandText = sqlStr;
            cmd.CommandType = cmdType;
            if (transaction != null)
            {
                cmd.Transaction = transaction;
            }
            if (listParams != null && listParams.Count > 0)
            {
                cmd.Parameters.AddRange(listParams.ToArray());
            }
            cmd.CommandTimeout = cmdTimeOut;
            OpenConnection();
            return cmd;
        }

        public DbParameter CreateDbPrameter(string paramName, object paramValue)
        {
            SqlParameter sp = new SqlParameter(paramName, paramValue);
            return sp;
        }

        public DbDataReader ExecuteReader(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            DbDataReader rdr = null;
            try
            {
                OpenConnection();
                cmd = CreateDbCommd(sqlConn, null, sqlStr, cmdType, listParams);
                rdr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rdr;
        }

        public DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            OpenConnection();
            DbTransaction trans = sqlConn.BeginTransaction();
            DbCommand cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);
            SqlDataAdapter sqlDataAdpter = new SqlDataAdapter(cmd as SqlCommand);
            DataTable dt = new DataTable();
            try
            {
                sqlDataAdpter.Fill(dt);
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception("執行數據庫操作失敗, sql: " + sqlStr, e);
            }
            finally
            {
                sqlDataAdpter.Dispose();
                cmd.Dispose();
                trans.Dispose();
                CloseConnection();
            }
            return dt;
        }

        public DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            OpenConnection();
            DbTransaction trans = sqlConn.BeginTransaction();
            DbCommand cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);
            SqlDataAdapter sqlDataAdpter = new SqlDataAdapter(cmd as SqlCommand);
            DataSet ds = new DataSet();
            try
            {
                sqlDataAdpter.Fill(ds);
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception("執行數據庫操作失敗, sql: " + sqlStr, e);
            }
            finally
            {
                sqlDataAdpter.Dispose();
                cmd.Dispose();
                trans.Dispose();
                CloseConnection();
            }
            return ds;
        }

        public object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            object result = null;
            OpenConnection();
            DbTransaction trans = sqlConn.BeginTransaction();
            try
            {
                cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);
                result = cmd.ExecuteScalar();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception("執行數據庫操作失敗, sql: " + sqlStr, e);
            }
            finally
            {
                trans.Dispose();
                CloseConnection();
            }
            return result;
        }

        public int ExecuteNonQuery(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            int result = -1;
            OpenConnection();
            DbTransaction trans = sqlConn.BeginTransaction();
            try
            {
                cmd = CreateDbCommd(sqlConn, trans, sqlStr, cmdType, listParams);
                result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception("執行數據庫操作失敗, sql: " + sqlStr, e);
            }
            finally
            {
                trans.Dispose();
                CloseConnection();
            }
            return result;
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="batchSize"></param>
        /// <param name="copyTimeout"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool ExecuteBatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt)
        {
            bool flag = false;
            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    OpenConnection();
                    using (SqlBulkCopy sbc = new SqlBulkCopy(sqlConn as SqlConnection))
                    {
                        //服務器上目標表的名稱
                        sbc.DestinationTableName = tableName;
                        sbc.BatchSize = batchSize;
                        sbc.BulkCopyTimeout = copyTimeout;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            //列映射定義數據源中的列和目標表中的列之間的關系
                            sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sbc.WriteToServer(dt);
                        flag = true;
                        scope.Complete();//有效的事務
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public void OpenConnection()
        {
            if (sqlConn.State == ConnectionState.Broken || sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
        }

        public void CloseConnection()
        {
            sqlConn.Close();
        }

#endregion

#region dispose method

        /// <summary>
        /// dispose接口方法
        /// </summary>
        public void Dispose()
        {

}

#endregion
}
}

到這里，我們實現了SqlServer類里的方法，對Ms SqlServer數據庫我們就已經可以進行簡單的基礎的CRUD操作了。

三、簡單直觀的對象實體轉換
在第二步中，我們已經實現了簡單的數據CRUD操作。根據樓豬使用ORM的經驗和習慣，我們也應該對一些查詢結果進行轉換，因為以類的組織方式比直接呈現ado.net對象更容易讓人接受，效率高低反在其次。下面利用常見的反射原理，簡單實現一個對象實體轉換器ModelConverter類：

代碼
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Threading;

namespace AdoNetDataAccess.Core.Obj2Model
{
using AdoNetDataAccess.Core.Contract;

    public sealed class ModelConverter
    {
        private static readonly object objSync = new object();

#region query for list

        /// <summary>
        /// 查詢數據表項并轉換為對應實體
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objType"></param>
        /// <param name="rdr"></param>
        /// <returns></returns>
        public static IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType, IDbOperation dbOperation)
            where T : class, new()
        {
            IDataReader rdr = dbOperation.ExecuteReader(sqlStr, cmdType, listParams);
            IList<T> listModels = new List<T>();
            try
            {
                Monitor.Enter(objSync);
                Hashtable ht = CreateHashColumnName(rdr);
                while (rdr.Read())
                {
                    Object obj = Activator.CreateInstance(objType);
                    PropertyInfo[] properties = objType.GetProperties();
                    foreach (PropertyInfo propInfo in properties)
                    {
                        string columnName = propInfo.Name.ToUpper();
                        if (ht.ContainsKey(columnName) == false)
                        {
                            continue;
                        }
                        int index = rdr.GetOrdinal(propInfo.Name);
                        object columnValue = rdr.GetValue(index);
                        if (columnValue != System.DBNull.Value)
                        {
                            SetValue(propInfo, obj, columnValue);
                        }
                    }
                    T model = default(T);
                    model = obj as T;
                    listModels.Add(model);
                }
            }
            finally
            {
                rdr.Close();
                rdr.Dispose();
                Monitor.Exit(objSync);
            }
            return listModels;
        }

#endregion

#region query for dictionary

        /// <summary>
        /// 查詢數據表項并轉換為對應實體
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">字典對應key列名</param>
        /// <param name="objType"></param>
        /// <param name="rdr"></param>
        /// <returns></returns>
        public static IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType, IDbOperation dbOperation)
            where T : class, new()
        {
            IDataReader rdr = dbOperation.ExecuteReader(sqlStr, cmdType, listParams);
            IDictionary<K, T> dictModels = new Dictionary<K, T>();
            try
            {
                Monitor.Enter(objSync);
                Hashtable ht = CreateHashColumnName(rdr);
                while (rdr.Read())
                {
                    Object obj = Activator.CreateInstance(objType);
                    PropertyInfo[] properties = objType.GetProperties();
                    object dictKey = null;
                    foreach (PropertyInfo propInfo in properties)
                    {
                        string columnName = propInfo.Name.ToUpper();
                        if (ht.ContainsKey(columnName) == false)
                        {
                            continue;
                        }
                        int index = rdr.GetOrdinal(propInfo.Name);
                        object columnValue = rdr.GetValue(index);
                        if (columnValue != System.DBNull.Value)
                        {
                            SetValue(propInfo, obj, columnValue);
                            if (string.Compare(columnName, key.ToUpper()) == 0)
                            {
                                dictKey = columnValue;
                            }
                        }
                    }
                    T model = default(T);
                    model = obj as T;
                    K objKey = (K)dictKey;
                    dictModels.Add(objKey, model);
                }
            }
            finally
            {
                rdr.Close();
                rdr.Dispose();
                Monitor.Exit(objSync);
            }
            return dictModels;
        }

#endregion

#region internal util

        private static Hashtable CreateHashColumnName(IDataReader rdr)
        {
            int len = rdr.FieldCount;
            Hashtable ht = new Hashtable(len);
            for (int i = 0; i < len; i++)
            {
                string columnName = rdr.GetName(i).ToUpper(); //不區分大小寫
                string columnRealName = rdr.GetName(i);
                if (ht.ContainsKey(columnName) == false)
                {
                    ht.Add(columnName, columnRealName);
                }
            }
            return ht;
        }

        private static void SetValue(PropertyInfo propInfo, Object obj, object objValue)
        {
            try
            {
                propInfo.SetValue(obj, objValue, null);
            }
            catch
            {
                object realValue = null;
                try
                {
                    realValue = Convert.ChangeType(objValue, propInfo.PropertyType);
                    propInfo.SetValue(obj, realValue, null);
                }
                catch (Exception ex)
                {
                    string err = ex.Message;
                    //throw ex; //在數據庫數據有不符合規范的情況下應該及時拋出異常
                }
            }
        }

#endregion
}
}

到這里，簡單的數據訪問持久化層就實現了。下面模仿樓豬使用的IBatis.net，寫個偽SqlMapper，改善一下調用形式，豐富一下調用方法，讓方法辨識度更高。
四、實現偽SqlMapper
1、BaseMapper類

代碼
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace AdoNetDataAccess.Mapper
{
using AdoNetDataAccess.Core.Contract;

    public abstract class BaseMapper
    {
        public IDbOperation CurrentDbOperation;

#region query for list

public abstract IList<T> QueryForList<T>(string sqlStr)
where T : class, new();

public abstract IList<T> QueryForList<T>(string sqlStr, Type objType)
where T : class, new();

public abstract IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
where T : class, new();

public abstract IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
where T : class, new();

#endregion

#region query for dictionary

public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr)
where T : class, new();

public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, Type objType)
where T : class, new();

public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, Type objType)
where T : class, new();

public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
where T : class, new();

#endregion

#region dataset datatable

public abstract DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

public abstract DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

#endregion

#region ExecuteScalar

public abstract object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

#endregion

#region insert

public abstract int Insert(string sqlStr);

public abstract int Insert(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

public abstract bool BatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt);

#endregion

#region delete

public abstract int Delete(string sqlStr);

public abstract int Delete(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

#endregion

#region update

public abstract int Update(string sqlStr);

public abstract int Update(string sqlStr, CommandType cmdType, List<DbParameter> listParams);

#endregion

}

上面代碼中的方法您是不是很熟悉呢？呵呵，使用IBatis.net 的童鞋應該會和樓豬產生更多的共鳴。
2、SqlMapper類

代碼
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace AdoNetDataAccess.Mapper
{
using AdoNetDataAccess.Core.Contract;
using AdoNetDataAccess.Core.Obj2Model;

    public class SqlMapper : BaseMapper
    {
        private SqlMapper()
        {

}

        public SqlMapper(IDbOperation dbOperation)
        {
            this.CurrentDbOperation = dbOperation;
        }

#region query for list

        public override IList<T> QueryForList<T>(string sqlStr)
        {
            return QueryForList<T>(sqlStr, CommandType.Text, null, typeof(T));
        }

        public override IList<T> QueryForList<T>(string sqlStr, Type objType)
        {
            return QueryForList<T>(sqlStr, CommandType.Text, null, objType);
        }

        public override IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return QueryForList<T>(sqlStr, cmdType, listParams, typeof(T));
        }

        public override IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
        {
            return ModelConverter.QueryForList<T>(sqlStr, cmdType, listParams, objType, this.CurrentDbOperation);
        }

#endregion

#region query for dictionary

        public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr)
        {
            return QueryForDictionary<K, T>(key, sqlStr, CommandType.Text, null, typeof(T));
        }

        public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, Type objType)
        {
            return QueryForDictionary<K, T>(key, sqlStr, CommandType.Text, null, objType);
        }

        public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, Type objType)
        {
            return QueryForDictionary<K, T>(key, sqlStr, cmdType, null, objType);
        }

        public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
        {
            return ModelConverter.QueryForDictionary<K, T>(key, sqlStr, cmdType, listParams, objType, this.CurrentDbOperation);
        }

#endregion

#region dataset datatable

        public override DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return this.CurrentDbOperation.FillDataTable(sqlStr, cmdType, listParams);
        }

        public override DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return this.CurrentDbOperation.FillDataSet(sqlStr, cmdType, listParams);
        }

#endregion

#region ExecuteScalar

        public override object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return this.CurrentDbOperation.ExecuteScalar(sqlStr, cmdType, listParams);
        }

#endregion

#region insert

        public override int Insert(string sqlStr)
        {
            object obj = ExecuteScalar(sqlStr, CommandType.Text, null);
            int id = obj == null ? 0 : int.Parse(obj.ToString());
            return id;
        }

        public override int Insert(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            object obj = ExecuteScalar(sqlStr, cmdType, listParams);
            int id = obj == null ? 0 : int.Parse(obj.ToString());
            return id;
        }

#endregion

#region delete

        public override int Delete(string sqlStr)
        {
            return CommitSql(sqlStr, CommandType.Text, null);
        }

        public override int Delete(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return CommitSql(sqlStr, cmdType, listParams);
        }

#endregion

#region update

        public override int Update(string sqlStr)
        {
            return CommitSql(sqlStr, CommandType.Text, null);
        }

        public override int Update(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return CommitSql(sqlStr, cmdType, listParams);
        }

#endregion

#region commit and execute sql

        private int CommitSql(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            return this.CurrentDbOperation.ExecuteNonQuery(sqlStr, cmdType, listParams);
        }

#endregion

#region dbparameter

        public DbParameter CreateParameter(string paraName, object paramValue)
        {
            return this.CurrentDbOperation.CreateDbPrameter(paraName, paramValue);
        }

        public List<DbParameter> CreateParameterList(string[] paraNames, object[] paramValues)
        {
            List<DbParameter> listParams = new List<DbParameter>();
            try
            {
                if (paraNames.Length != paramValues.Length)
                {
                    throw new Exception("Param name and value is not equal.");
                }
                for (int i = 0; i < paraNames.Length; i++)
                {
                    DbParameter param = CreateParameter(paraNames[i], paramValues[i]);
                    listParams.Add(param);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return listParams;
        }