熱點推薦：
您现在的位置： 電腦知識網 >> 編程 >> ASP編程 >> 正文

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

2022-06-13   來源: ASP編程 
    這兩天重溫經典對的東西稍微深入的了解了一下順便寫點代碼練練手全當是復習筆記吧
  一簡單說說的大常用對象
    既然說當然不能免俗地要提到大常用對象本文不會對的大對象和它們的關系進行過多闡釋不過我們應該對下面這張圖的結構有個了解

　　

 
    關於上圖圖示中的大對象經常做以數據為驅動的mis系統的童鞋應該不會陌生本文一筆帶過下面我們一步一步實現以為核心的數據訪問程序
 二數據訪問持久化層
    IDbOperation接口
    代碼
    using SystemCollectionsGeneric;
    using SystemData;
    using SystemDataCommon;
    namespace AdoNetDataaccessCoreContract
    {
    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();
    }
    }
    上面的接口包括增刪改查批量插入以及數據庫連接對象的連接和關閉等常用操作您可以根據命名和參數輕松理解函數的含義根據樓豬的開發經驗對於平時的數據庫操作上述方法差不多夠用了當然您也可以按照自己需要重寫組織添加其他函數


    針對一種數據源的數據操作實現
    底層的數據操作接口定義好後就要針對一種數據源具體實現上述的數據操作這裡樓豬選擇了Sql Server我們也可以實現其他數據源的數據訪問操作按照配置利用抽象工廠動態反射選擇是哪一種數據源的實現這裡按下不表有心的童鞋自己可以動手一試下面是具體的實現
    代碼
    using System;
    using SystemCollectionsGeneric;
    using SystemData;
    using SystemDataCommon;
    using SystemDataSqlClient;
    using SystemTransactions;
    namespace AdoNetDataAccessCoreImplement
    {
    using AdoNetDataAccessCoreContract;
    public class SqlServer : IDbOperation IDisposable
    {
    private int cmdTimeOut = ;
    private DbConnection sqlConn = null;
    private DbCommand cmd = null;
    private SqlServer()
    {
    }
    public SqlServer(string sqlConStr)
    {
    sqlConn = new SqlConnection(sqlConStr);
    cmdTimeOut = sqlConnConnectionTimeout;
    }
    public SqlServer(string sqlConStr int timeOut)
    {
    sqlConn = new SqlConnection(sqlConStr);
    if (timeOut < )
    {
    timeOut = sqlConnConnectionTimeout;
    }
    cmdTimeOut = timeOut;
    }
    #region contract method
    public DbCommand CreateDbCommd(DbConnection sqlConn DbTransaction transaction string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    DbCommand cmd = new SqlCommand();
    cmdConnection = sqlConn;
    cmdCommandText = sqlStr;
    cmdCommandType = cmdType;
    if (transaction != null)
    {
    cmdTransaction = transaction;
    }
    if (listParams != null && listParamsCount > )
    {
    cmdParametersAddRange(listParamsToArray());
    }
    cmdCommandTimeout = 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 = cmdExecuteReader();
    }
    catch (Exception ex)
    {
    throw ex;
    }
    return rdr;
    }
    public DataTable FillDataTable(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    OpenConnection();
    DbTransaction trans = sqlConnBeginTransaction();
    DbCommand cmd = CreateDbCommd(sqlConn trans sqlStr cmdType listParams);
    SqlDataAdapter sqlDataAdpter = new SqlDataAdapter(cmd as SqlCommand);
    DataTable dt = new DataTable();
    try
    {
    sqlDataAdpterFill(dt);
    transCommit();
    }
    catch (Exception e)
    {
    transRollback();
    throw new Exception(執行數據庫操作失敗 sql: + sqlStr e);
    }
    finally
    {
    sqlDataAdpterDispose();
    cmdDispose();
    transDispose();
    CloseConnection();
    }
    return dt;
    }
    public DataSet FillDataSet(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    OpenConnection();
    DbTransaction trans = sqlConnBeginTransaction();
    DbCommand cmd = CreateDbCommd(sqlConn trans sqlStr cmdType listParams);
    SqlDataAdapter sqlDataAdpter = new SqlDataAdapter(cmd as SqlCommand);
    DataSet ds = new DataSet();
    try
    {
    sqlDataAdpterFill(ds);
    transCommit();
    }
    catch (Exception e)
    {
    transRollback();
    throw new Exception(執行數據庫操作失敗 sql: + sqlStr e);
    }
    finally
    {
    sqlDataAdpterDispose();
    cmdDispose();
    transDispose();
    CloseConnection();
    }
    return ds;
    }
    public object ExecuteScalar(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    object result = null;
    OpenConnection();
    DbTransaction trans = sqlConnBeginTransaction();
    try
    {
    cmd = CreateDbCommd(sqlConn trans sqlStr cmdType listParams);
    result = cmdExecuteScalar();
    transCommit();
    }
    catch (Exception e)
    {
    transRollback();
    throw new Exception(執行數據庫操作失敗 sql: + sqlStr e);
    }
    finally
    {
    transDispose();
    CloseConnection();
    }
    return result;
    }
    public int ExecuteNonQuery(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    int result = ;
    OpenConnection();
    DbTransaction trans = sqlConnBeginTransaction();
    try
    {
    cmd = CreateDbCommd(sqlConn trans sqlStr cmdType listParams);
    result = cmdExecuteNonQuery();
    transCommit();
    }
    catch (Exception e)
    {
    transRollback();
    throw new Exception(執行數據庫操作失敗 sql: + sqlStr e);
    }
    finally
    {
    transDispose();
    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))
    {
    //服務器上目標表的名稱
    sbcDestinationTableName = tableName;
    sbcBatchSize = batchSize;
    sbcBulkCopyTimeout = copyTimeout;
    for (int i = ; i < dtColumnsCount; i++)
    {
    //列映射定義數據源中的列和目標表中的列之間的關系
    sbcColumnMappingsAdd(dtColumns[i]ColumnName dtColumns[i]ColumnName);
    }
    sbcWriteToServer(dt);
    flag = true;
    scopeComplete();//有效的事務
    }
    }
    }
    catch (Exception ex)
    {
    throw ex;
    }
    return flag;
    }
    public void OpenConnection()
    {
    if (sqlConnState == ConnectionStateBroken || sqlConnState == ConnectionStateClosed)
    sqlConnOpen();
    }
    public void CloseConnection()
    {
    sqlConnClose();
    }
    #endregion
    #region dispose method
    /// <summary>
    /// dispose接口方法
    /// </summary>
    public void Dispose()
    {
    }
    #endregion
    }
    }
    到這裡我們實現了SqlServer類裡的方法對Ms SqlServer數據庫我們就已經可以進行簡單的基礎的CRUD操作了


  三簡單直觀的對象實體轉換
    在第二步中我們已經實現了簡單的數據CRUD操作根據樓豬使用ORM的經驗和習慣我們也應該對一些查詢結果進行轉換因為以類的組織方式比直接呈現對象更容易讓人接受效率高低反在其次下面利用常見的反射原理簡單實現一個對象實體轉換器ModelConverter類
    代碼
    using System;
    using SystemCollections;
    using SystemCollectionsGeneric;
    using SystemData;
    using SystemDataCommon;
    using SystemReflection;
    using SystemThreading;
    namespace AdoNetDataAccessCoreObjModel
    {
    using AdoNetDataAccessCoreContract;
    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 = dbOperationExecuteReader(sqlStr cmdType listParams);
    IList<T> listModels = new List<T>();
    try
    {
    MonitorEnter(objSync);
    Hashtable ht = CreateHashColumnName(rdr);
    while (rdrRead())
    {
    Object obj = ActivatorCreateInstance(objType);
    PropertyInfo[] properties = objTypeGetProperties();
    foreach (PropertyInfo propInfo in properties)
    {
    string columnName = propInfoNameToUpper();
    if (htContainsKey(columnName) == false)
    {
    continue;
    }
    int index = rdrGetOrdinal(propInfoName);
    object columnValue = rdrGetValue(index);
    if (columnValue != SystemDBNullValue)
    {
    SetValue(propInfo obj columnValue);
    }
    }
    T model = default(T);
    model = obj as T;
    listModelsAdd(model);
    }
    }
    finally
    {
    rdrClose();
    rdrDispose();
    MonitorExit(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 = dbOperationExecuteReader(sqlStr cmdType listParams);
    IDictionary<K T> dictModels = new Dictionary<K T>();
    try
    {
    MonitorEnter(objSync);
    Hashtable ht = CreateHashColumnName(rdr);
    while (rdrRead())
    {
    Object obj = ActivatorCreateInstance(objType);
    PropertyInfo[] properties = objTypeGetProperties();
    object dictKey = null;
    foreach (PropertyInfo propInfo in properties)
    {
    string columnName = propInfoNameToUpper();
    if (htContainsKey(columnName) == false)
    {
    continue;
    }
    int index = rdrGetOrdinal(propInfoName);
    object columnValue = rdrGetValue(index);
    if (columnValue != SystemDBNullValue)
    {
    SetValue(propInfo obj columnValue);
    if (stringCompare(columnName keyToUpper()) == )
    {
    dictKey = columnValue;
    }
    }
    }
    T model = default(T);
    model = obj as T;
    K objKey = (K)dictKey;
    dictModelsAdd(objKey model);
    }
    }
    finally
    {
    rdrClose();
    rdrDispose();
    MonitorExit(objSync);
    }
    return dictModels;
    }
    #endregion
    #region internal util
    private static Hashtable CreateHashColumnName(IDataReader rdr)
    {
    int len = rdrFieldCount;
    Hashtable ht = new Hashtable(len);
    for (int i = ; i < len; i++)
    {
    string columnName = rdrGetName(i)ToUpper(); //不區分大小寫
    string columnRealName = rdrGetName(i);
    if (htContainsKey(columnName) == false)
    {
    htAdd(columnName columnRealName);
    }
    }
    return ht;
    }
    private static void SetValue(PropertyInfo propInfo Object obj object objValue)
    {
    try
    {
    propInfoSetValue(obj objValue null);
    }
    catch
    {
    object realValue = null;
    try
    {
    realValue = ConvertChangeType(objValue propInfoPropertyType);
    propInfoSetValue(obj realValue null);
    }
    catch (Exception ex)
    {
    string err = exMessage;
    //throw ex; //在數據庫數據有不符合規范的情況下應該及時拋出異常
    }
    }
    }
    #endregion
    }
    }
    到這裡簡單的數據訪問持久化層就實現了下面模仿樓豬使用的寫個偽SqlMapper改善一下調用形式豐富一下調用方法讓方法辨識度更高


四實現偽SqlMapper
    BaseMapper類
    代碼
    using System;
    using SystemCollectionsGeneric;
    using SystemData;
    using SystemDataCommon;
    using SystemDataSqlClient;
    namespace AdoNetDataAccessMapper
    {
    using AdoNetDataAccessCoreContract;
    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
    }
    }
    上面代碼中的方法您是不是很熟悉呢？ 呵呵使用 的童鞋應該會和樓豬產生更多的共鳴


    SqlMapper類
    代碼
    using System;
    using SystemCollectionsGeneric;
    using SystemData;
    using SystemDataCommon;
    namespace AdoNetDataAccessMapper
    {
    using AdoNetDataAccessCoreContract;
    using AdoNetDataAccessCoreObjModel;
    public class SqlMapper : BaseMapper
    {
    private SqlMapper()
    {
    }
    public SqlMapper(IDbOperation dbOperation)
    {
    thisCurrentDbOperation = dbOperation;
    }
    #region query for list
    public override IList<T> QueryForList<T>(string sqlStr)
    {
    return QueryForList<T>(sqlStr CommandTypeText null typeof(T));
    }
    public override IList<T> QueryForList<T>(string sqlStr Type objType)
    {
    return QueryForList<T>(sqlStr CommandTypeText 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 ModelConverterQueryForList<T>(sqlStr cmdType listParams objType thisCurrentDbOperation);
    }
    #endregion
    #region query for dictionary
    public override IDictionary<K T> QueryForDictionary<K T>(string key string sqlStr)
    {
    return QueryForDictionary<K T>(key sqlStr CommandTypeText null typeof(T));
    }
    public override IDictionary<K T> QueryForDictionary<K T>(string key string sqlStr Type objType)
    {
    return QueryForDictionary<K T>(key sqlStr CommandTypeText 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 ModelConverterQueryForDictionary<K T>(key sqlStr cmdType listParams objType thisCurrentDbOperation);
    }
    #endregion
    #region dataset datatable
    public override DataTable FillDataTable(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    return thisCurrentDbOperationFillDataTable(sqlStr cmdType listParams);
    }
    public override DataSet FillDataSet(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    return thisCurrentDbOperationFillDataSet(sqlStr cmdType listParams);
    }
    #endregion
    #region ExecuteScalar
    public override object ExecuteScalar(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    return thisCurrentDbOperationExecuteScalar(sqlStr cmdType listParams);
    }
    #endregion
    #region insert
    public override int Insert(string sqlStr)
    {
    object obj = ExecuteScalar(sqlStr CommandTypeText null);
    int id = obj == null ? : intParse(objToString());
    return id;
    }
    public override int Insert(string sqlStr CommandType cmdType List<DbParameter> listParams)
    {
    object obj = ExecuteScalar(sqlStr cmdType listParams);
    int id = obj == null ? : intParse(objToString());
    return id;
    }
    /// <summary>
    /// 批量插入
    /// </summary>
    /// <param name=tableName></param>
    /// <param name=batchSize></param>
    /// <param name=copyTimeout></param>
    /// <param name=dt></param>
    /// <returns></returns>
    public override bool BatchInsert(string tableName int batchSize int copyTimeout DataTable dt)
    {
    return thisCurrentDbOperationExecuteBatchInsert(tableName batchSize copyTimeout dt);
    }
    #endregion
    #region delete
    public override int Delete(string sqlStr)
    {
    return CommitSql(sqlStr CommandTypeText 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 CommandTypeText 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 thisCurrentDbOperationExecuteNonQuery(sqlStr cmdType listParams);
    }
    #endregion
    #region  dbparameter
    public DbParameter CreateParameter(string paraName object paramValue)
    {
    return thisCurrentDbOperationCreateDbPrameter(paraName paramValue);
    }
    public List<DbParameter> CreateParameterList(string[] paraNames object[] paramValues)
    {
    List<DbParameter> listParams = new List<DbParameter>();
    try
    {
    if (paraNamesLength != paramValuesLength)
    {
    throw new Exception(Param name and value is not equal);
    }
    for (int i = ; i < paraNamesLength; i++)
    {
    DbParameter param = CreateParameter(paraNames[i] paramValues[i]);
    listParamsAdd(param);
    }
    }
    catch (Exception ex)
    {
    throw ex;
    }
    return listParams;
    }
    #endregion
    }
    }
    上面的方法豐富實現了CRUD的常見操作其實主要還是調用了IDbOperation接口和方法


From:http://tw.wingwit.com/Article/program/ASP/201311/21746.html
    《ado.net快速上手實踐篇（一）》本文是由ASP編程免費提供，內容來源於互聯網,本文歸原作者所有。
  • 上一篇文章：

  • 下一篇文章：
    • 相關文章
    主板 CPU 硬盤 內存 顯卡 聲卡 顯示器 光驅 死機
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.