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

五dal層數據訪問實現
    在這裡我們使用前一篇文章裡實現的數據持久化層和偽SqlMapper對象實現數據操作下面我們來看看Dal下核心的Dao如何實現
    還記得我們在下面的dao類是怎麼實現的嗎？沒錯我們根據一個基類BaseDAO和它的構造函數實現dao的配置加載但是樓豬的實現沒有那麼復雜和強大本文的實現其實就是通過BaseDAO和構造函數獲取數據庫連接對象的key初始化一個SqlMapper然後利用SqlMapper對象進行基本的CRUD等等數據操作那麼我們如何利用BaseDAO和構造函數就像以前在系列文章裡的提到的Dal層下那樣進行SqlMapper的初始化呢？
在AdoNetDataaccessMapper下我們定義公共的BaseDAO類
    代碼
    namespace AdoNetDataAccessMapper
    {
    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)
    {
    thisSqlMapper = MapperUtillGetMapper(mapperName);
    }
    #endregion
    }
    }

初始化SqlMapper的實用類
    代碼
    using System;
    using SystemCollectionsGeneric;
    using SystemConfiguration;
    namespace AdoNetDataAccessMapper
    {
    using AdoNetDataAccessCoreContract;
    using AdoNetDataAccessCoreImplement;
    public sealed class MapperUtill
    {
    #region fields
    public static string currentSqlKey = sqlConn;
    public static int cmdTimeOut = ;
    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 = intParse(ConfigurationManagerAppSettings[db_timeOut]);
    }
    catch
    {
    cmdTimeOut = ;
    }
    //實例化SqlDbMapper
    for (int i = ; i < ConfigurationManagerConnectionStringsCount; i++)
    {
    string key = ConfigurationManagerConnectionStrings[i]Name;
    string value = ConfigurationManagerConnectionStrings[i]ConnectionString;
    CreateMapper(key value cmdTimeOut);
    }
    }
    public static SqlMapper GetSqlMapper(string key)
    {
    return MapperUtillGetMapper(key);
    }
    public static SqlMapper GetCurrentSqlMapper()
    {
    return MapperUtillGetMapper(currentSqlKey);
    }
    public static void CreateMapper(string connKey string sqlConStr int connTimeOut)
    {
    IDbOperation operation = new SqlServer(sqlConStr connTimeOut);
    SqlMapper mapper = new SqlMapper(operation);
    dictMappersAdd(connKeyToUpper()Trim() mapper);//不區分大小寫
    }
    public static SqlMapper GetMapper(string sqlConKey)
    {
    if (stringIsNullOrEmpty(sqlConKey))
    {
    throw new Exception(數據庫連接字符串主鍵為空!);
    }
    sqlConKey = sqlConKeyToUpper();//不區分大小寫
    SqlMapper mapper = null;
    if (dictMappersContainsKey(sqlConKey))
    {
    mapper = dictMappers[sqlConKey];
    }
    else
    {
    throw new Exception(stringFormat(沒有{}所對應的數據庫連接 sqlConKey));
    }
    return mapper;
    }
    /// <summary>
    /// 釋放所有
    /// </summary>
    public void Release()
    {
    foreach (KeyValuePair<string SqlMapper> kv in dictMappers)
    {
    SqlMapper mapper = kvValue;
    if (mapper == null)
    {
    continue;
    }
    mapperCurrentDbOperationCloseConnection();
    }
    dictMappersClear();
    }
    #endregion
    }
    }
    這個實用類的重要作用就是初始化配置文件裡connectionStrings配置節點以獲取sql連接對象必須的連接字符串

PersonDao類
    下面就是針對具體的Person表的數據操作了
    代碼
    using SystemCollectionsGeneric;
    using SystemData;
    namespace AdoNetDataAccessDalDao
    {
    using AdoNetDataAccessDalModel;
    using AdoNetDataAccessDalUtility;
    using AdoNetDataAccessMapper;
    public class PersonDao : BaseDAO
    {
    public PersonDao()
    : base(sqlConn)//sqlConn是<connectionStrings>配置節點的一個name
    {
    }
    public int Insert(string sqlInsert)
    {
    int id = thisSqlMapperInsert(sqlInsert);
    //object obj = thisSqlMapperExecuteScalar(sqlInsert SystemDataCommandTypeText null);
    return id;
    }
    public bool BatchInsert(IList<Person> listModels)
    {
    int batchSize = ;
    int copyTimeOut = ;
    DataTable dt = DataTableHelperCreateTable<Person>(listModels);
    bool flag = thisSqlMapperBatchInsert(typeof(Person)Name batchSize copyTimeOut dt);
    return flag;
    }
    public int Update(string sqlUpdate)
    {
    int result = thisSqlMapperUpdate(sqlUpdate);
    return result;
    }
    public IList<Person> SelectPersons(string sqlSelect)
    {
    IList<Person> listPersons = thisSqlMapperQueryForList<Person>(sqlSelect);
    return listPersons;
    }
    public IDictionary<int Person> SelectDictPersons(string sqlSelect)
    {
    IDictionary<int Person> dictPersons = thisSqlMapperQueryForDictionary<int Person>(Id sqlSelect);
    return dictPersons;
    }
    public DataTable SelectPersonTable(string sqlSelect)
    {
    DataTable dt = thisSqlMapperFillDataTable(sqlSelect CommandTypeText null);
    return dt;
    }
    public DataSet SelectPersonDataSet(string sqlSelect)
    {
    DataSet ds = thisSqlMapperFillDataSet(sqlSelect CommandTypeText null);
    return ds;
    }
    public int Delete(string sqlDelete)
    {
    int result = thisSqlMapperDelete(sqlDelete);
    return result;
    }
    }
    }
    到這裡一個dao類操作就實現了然後我們按步就班實現對外調用的服務接口在表現層調用吧
六表現層的調用
  配置文件
    代碼
    <appSettings>
    <add key=db_timeOut value=/>
    </appSettings>
    <connectionStrings>
    <add name=sqlConn connectionString=Data Source=\sqlexpress; Initial Catalog=TestDb; User Id=sa; PassWord=;/>
    <add name=sqlConnStr connectionString=Data Source=\sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=;/>
    <add name=sqlConnStr connectionString=Data Source=\sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=;/>
    </connectionStrings>
    其中connectionString是必須的如果沒有我們無法加載調用可用的SqlMapper

CRUD操作測試
    代碼
    using System;
    using SystemCollections;
    using SystemCollectionsGeneric;
    using SystemData;
    namespace OOXXWebApp
    {
    using AdoNetDataAccessDal;
    using AdoNetDataAccessDalModel;
    public partial class _Default : SystemWebUIPage
    {
    protected void Page_Load(object sender EventArgs e)
    {
    if (!IsPostBack)
    {
    //增刪改查測試
    string sqlInsert = INSERT Person (FirstNameLastNameWeightHeight) VALUES( jeffwong) SELECT @@IDENTITY FROM Person(NOLOCK);
    string sqlUpdate = UPDATE Person SET Height= WHERE Id=;
    string sqlSelect = SELECT TOP * FROM Person(NOLOCK);
    string sqlDelete = DELETE Person  WHERE Id> AND Id<;
    IList<Person> listModels = new List<Person>();
    for (int i = ; i < ; i++)
    {
    Person model = new Person();
    modelFirstName = Jeff;
    modelLastName = Wong;
    modelWeight = ;
    modelHeight = ;
    listModelsAdd(model);
    }
    ResponseWrite(Test Beginning<br/>);
    int id = ServiceFactoryCreatePersonService()Add(sqlInsert);
    ResponseWrite(stringFormat(<br/>Insert and return id:{} id));
    bool flag = ServiceFactoryCreatePersonService()BatchInsert(listModels);
    ResponseWrite(stringFormat(<br/> Batch Insert {} flag ? succeed : failed));
    IList<Person> listPersons = ServiceFactoryCreatePersonService()GetPersons(sqlSelect);
    ResponseWrite(stringFormat(<br/>Select pesons and return persons:{} listPersonsCount));
    IDictionary<int Person> dictPersons = ServiceFactoryCreatePersonService()GetDictPersons(sqlSelect);
    ResponseWrite(stringFormat(<br/>Select pesons and return dictionary persons:{} dictPersonsCount));
    DataTable dt = ServiceFactoryCreatePersonService()GetPersonTable(sqlSelect);
    ResponseWrite(stringFormat(<br/>Select pesons and return persons:{} dtRowsCount));
    DataSet ds = ServiceFactoryCreatePersonService()GetPersonDataSet(sqlSelect);
    ResponseWrite(stringFormat(<br/>Select pesons and return persons:{} dsTables[]RowsCount));
    int affectNum = ServiceFactoryCreatePersonService()Modify(sqlUpdate);
    ResponseWrite(stringFormat(<br/>Update and affect rows :{} affectNum));
    affectNum = ;
    affectNum = ServiceFactoryCreatePersonService()Remove(sqlDelete);
    ResponseWrite(stringFormat(<br/>Delete and affect rows :{} affectNum));
    ResponseWrite(<br/><br/>Test End);
    }
    }
    }
    }
    這個就不用多說了吧表現層寫SQL語句調用寫好的服務就行了比較不舒服的地方就是SQL語句不得不寫在類裡面如果自動生成或者獨立放在xml下實現可配置的形式那就更好了當然sql語句不是我們討論的重點您有好的方法可以自己擴展實現更人性化的功能減少書寫SQLl語句的工作
七最後對demo工程文件結構進行簡單說明
    數據持久化層AdoNetDataAccessCore
    SqlMapper層AdoNetDataAccessMapper（引用AdoNetDataAccessCore）
    具體數據操作使用層AdoNetDataAccessDal（引用AdoNetDataAccessMapper）
    表現層AdoNetDataAccessWebApp（引用AdoNetDataAccessDal）
    可以看出工程裡的文件結構還是很清晰的需要學習的童鞋不妨下載使用試試看吧

From:http://tw.wingwit.com/Article/program/net/201311/11765.html