我們接著上文繼續完成我們的ado.net簡單實踐。

五、dal層數據訪問實現
在這里我們使用前一篇文章里實現的數據持久化層和偽SqlMapper對象，實現數據操作。下面我們來看看Dal下核心的Dao如何實現：
還記得我們在IBatis.net下面的dao類是怎么實現的嗎？沒錯，我們根據一個基類BaseDAO和它的構造函數，實現dao的配置加載。但是樓豬的實現沒有那么復雜和強大，本文的實現其實就是通過BaseDAO和構造函數獲取數據庫連接對象的key，初始化一個SqlMapper，然后利用SqlMapper對象進行基本的CRUD等等數據操作。那么我們如何利用BaseDAO和構造函數就像以前在IBatis.net系列文章里的提到的Dal層下那樣進行SqlMapper的初始化呢？
1、在AdoNetDataaccess.Mapper下我們定義公共的BaseDAO類

代碼

namespace AdoNetDataAccess.Mapper
{
    public abstract class BaseDAO
    {
        #region PRoperties

        public SqlMapper SqlMapper { get; set; }

        #endregion

        #region Constructor

        private BaseDAO()
        {
        }

        /// <summary>
        /// SqlMapper屬性適用
        /// </summary>
        /// <param name="mapperName"></param>
        public BaseDAO(string mapperName)
        {
            this.SqlMapper = MapperUtill.GetMapper(mapperName);
        }

        #endregion
    }
}

2、初始化SqlMapper的實用類

代碼
using System;
using System.Collections.Generic;
using System.Configuration;

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

    public sealed class MapperUtill
    {
        #region fields

        public static string currentSqlKey = "sqlConn";

        public static int cmdTimeOut = 15;

        private static readonly object objSync = new object();

        private static readonly IDictionary<string, SqlMapper> dictMappers = new Dictionary<string, SqlMapper>();

        #endregion

        #region constructor and methods

        private MapperUtill()
        {

        }

        static MapperUtill()
        {
            try
            {
                cmdTimeOut = int.Parse(ConfigurationManager.AppSettings["db_timeOut"]);
            }
            catch
            {
                cmdTimeOut = 15;
            }
            //實例化SqlDbMapper
            for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++)
            {
                string key = ConfigurationManager.ConnectionStrings[i].Name;
                string value = ConfigurationManager.ConnectionStrings[i].ConnectionString;
                CreateMapper(key, value, cmdTimeOut);
            }
        }

        public static SqlMapper GetSqlMapper(string key)
        {
            return MapperUtill.GetMapper(key);
        }

        public static SqlMapper GetCurrentSqlMapper()
        {
            return MapperUtill.GetMapper(currentSqlKey);
        }

        public static void CreateMapper(string connKey, string sqlConStr, int connTimeOut)
        {
            IDbOperation operation = new SqlServer(sqlConStr, connTimeOut);
            SqlMapper mapper = new SqlMapper(operation);
            dictMappers.Add(connKey.ToUpper().Trim(), mapper);//不區分大小寫
        }

        public static SqlMapper GetMapper(string sqlConKey)
        {
            if (string.IsNullOrEmpty(sqlConKey))
            {
                throw new Exception("數據庫連接字符串主鍵為空!");
            }
            sqlConKey = sqlConKey.ToUpper();//不區分大小寫
            SqlMapper mapper = null;
            if (dictMappers.ContainsKey(sqlConKey))
            {
                mapper = dictMappers[sqlConKey];
            }
            else
            {
                throw new Exception(string.Format("沒有{0}所對應的數據庫連接", sqlConKey));
            }
            return mapper;
        }

        /// <summary>
        /// 釋放所有
        /// </summary>
        public void Release()
        {
            foreach (KeyValuePair<string, SqlMapper> kv in dictMappers)
            {
                SqlMapper mapper = kv.Value;
                if (mapper == null)
                {
                    continue;
                }
                mapper.CurrentDbOperation.CloseConnection();
            }
            dictMappers.Clear();
        }

        #endregion

    }
}

這個實用類的重要作用就是初始化配置文件里connectionStrings配置節點，以獲取sql連接對象必須的連接字符串。
3、PersonDao類
下面就是針對具體的Person表的數據操作了：

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

namespace AdoNetDataAccess.Dal.Dao
{
    using AdoNetDataAccess.Dal.Model;
    using AdoNetDataAccess.Dal.Utility;
    using AdoNetDataAccess.Mapper;

    public class PersonDao : BaseDAO
    {
        public PersonDao()
            : base("sqlConn")//sqlConn是<connectionStrings>配置節點的一個name
        {
        }

        public int Insert(string sqlInsert)
        {
            int id = this.SqlMapper.Insert(sqlInsert);
            //object obj = this.SqlMapper.ExecuteScalar(sqlInsert, System.Data.CommandType.Text, null);
            return id;
        }

        public bool BatchInsert(IList<Person> listModels)
        {
            int batchSize = 50000;
            int copyTimeOut = 60;
            DataTable dt = DataTableHelper.CreateTable<Person>(listModels);
            bool flag = this.SqlMapper.BatchInsert(typeof(Person).Name, batchSize, copyTimeOut, dt);
            return flag;
        }

        public int Update(string sqlUpdate)
        {
            int result = this.SqlMapper.Update(sqlUpdate);
            return result;
        }

        public IList<Person> SelectPersons(string sqlSelect)
        {
            IList<Person> listPersons = this.SqlMapper.QueryForList<Person>(sqlSelect);
            return listPersons;
        }

        public IDictionary<int, Person> SelectDictPersons(string sqlSelect)
        {
            IDictionary<int, Person> dictPersons = this.SqlMapper.QueryForDictionary<int, Person>("Id", sqlSelect);
            return dictPersons;
        }

        public DataTable SelectPersonTable(string sqlSelect)
        {
            DataTable dt = this.SqlMapper.FillDataTable(sqlSelect, CommandType.Text, null);
            return dt;
        }

        public DataSet SelectPersonDataSet(string sqlSelect)
        {
            DataSet ds = this.SqlMapper.FillDataSet(sqlSelect, CommandType.Text, null);
            return ds;
        }

        public int Delete(string sqlDelete)
        {
            int result = this.SqlMapper.Delete(sqlDelete);
            return result;
        }

    }
}

到這里，一個dao類操作就實現了。然后我們按步就班實現對外調用的服務接口。在表現層調用吧。
六、表現層的調用
1、配置文件

代碼
  <appSettings>
    <add key="db_timeOut" value="5000"/>
  </appSettings>
  <connectionStrings>
    <add name="sqlConn" connectionString="Data Source=./sqlexpress; Initial Catalog=TestDb; User Id=sa; PassWord=123456;"/>
    <add name="sqlConnStr1" connectionString="Data Source=./sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=123456;"/>
    <add name="sqlConnStr2" connectionString="Data Source=./sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=123456;"/>
  </connectionStrings>
其中，connectionString是必須的，如果沒有，我們無法加載調用可用的SqlMapper。
2、CRUD操作測試

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

namespace OOXXWebApp
{
    using AdoNetDataAccess.Dal;
    using AdoNetDataAccess.Dal.Model;

    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //增刪改查測試
                string sqlInsert = "INSERT Person (FirstName,LastName,Weight,Height) VALUES( 'jeff','wong',70,180) SELECT @@IDENTITY FROM Person(NOLOCK)";
                string sqlUpdate = "UPDATE Person SET Height=178 WHERE Id=1";
                string sqlSelect = "SELECT TOP 100 * FROM Person(NOLOCK)";
                string sqlDelete = "DELETE Person  WHERE Id>10 AND Id<100";

                IList<Person> listModels = new List<Person>();
                for (int i = 0; i < 500000; i++)
                {
                    Person model = new Person();
                    model.FirstName = "Jeff";
                    model.LastName = "Wong";
                    model.Weight = 70;
                    model.Height = 180;
                    listModels.Add(model);
                }

                Response.Write("Test Beginning......<br/>");

                int id = ServiceFactory.CreatePersonService().Add(sqlInsert);
                Response.Write(string.Format("<br/>Insert and return id:{0}", id));

                bool flag = ServiceFactory.CreatePersonService().BatchInsert(listModels);
                Response.Write(string.Format("<br/> Batch Insert {0}", flag ? "succeed" : "failed"));

                IList<Person> listPersons = ServiceFactory.CreatePersonService().GetPersons(sqlSelect);
                Response.Write(string.Format("<br/>Select pesons and return persons:{0}", listPersons.Count));

                IDictionary<int, Person> dictPersons = ServiceFactory.CreatePersonService().GetDictPersons(sqlSelect);
                Response.Write(string.Format("<br/>Select pesons and return dictionary persons:{0}", dictPersons.Count));

                DataTable dt = ServiceFactory.CreatePersonService().GetPersonTable(sqlSelect);
                Response.Write(string.Format("<br/>Select pesons and return persons:{0}", dt.Rows.Count));

                DataSet ds = ServiceFactory.CreatePersonService().GetPersonDataSet(sqlSelect);
                Response.Write(string.Format("<br/>Select pesons and return persons:{0}", ds.Tables[0].Rows.Count));

                int affectNum = ServiceFactory.CreatePersonService().Modify(sqlUpdate);
                Response.Write(string.Format("<br/>Update and affect rows :{0}", affectNum));

                affectNum = 0;
                affectNum = ServiceFactory.CreatePersonService().Remove(sqlDelete);
                Response.Write(string.Format("<br/>Delete and affect rows :{0}", affectNum));

                Response.Write("<br/><br/>Test End.");
            }
        }
    }
}

這個就不用多說了吧，表現層寫SQL語句調用寫好的服務就行了。比較不舒服的地方就是SQL語句不得不寫在類里面，如果自動生成或者獨立放在xml下實現可配置的形式那就更好了，當然sql語句不是我們討論的重點，您有好的方法可以自己擴展實現更人性化的功能，減少書寫SQLl語句的工作。

七、最后，對demo工程文件結構進行簡單說明。
1、數據持久化層AdoNetDataAccess.Core
2、SqlMapper層AdoNetDataAccess.Mapper（引用AdoNetDataAccess.Core）
3、具體數據操作使用層AdoNetDataAccess.Dal（引用AdoNetDataAccess.Mapper）
4、表現層AdoNetDataAccessWebApp（引用AdoNetDataAccess.Dal）
可以看出，工程里的文件結構還是很清晰的，需要學習的童鞋不妨下載使用試試看吧。

demo下載：demo

作者：Jeff Wong
出處：http://m.survivalescaperooms.com/jeffwongishandsome/