辰东全部小说,辰东完美世界有声小说

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

ADO.NET快速上手實(shí)踐總結(jié)

本文希望能給大家一些啟發(fā)。前言：這兩天重溫經(jīng)典，對(duì)ADO.NET的東西稍微深入的了解了一下，順便寫點(diǎn)代碼練練手，全當(dāng)是復(fù)習(xí)筆記吧。

創(chuàng)新互聯(lián)主要從事成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站、網(wǎng)頁設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)海西,十多年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108

一、簡單說說ADO.NET的5大常用對(duì)象

既然說ADO.NET，當(dāng)然不能免俗地要提到5大常用對(duì)象。本文不會(huì)對(duì)ADO.NET的5大對(duì)象和它們的關(guān)系進(jìn)行過多闡釋，不過我們應(yīng)該對(duì)下面這張圖的結(jié)構(gòu)有個(gè)了解：

關(guān)于上圖圖示中的5大對(duì)象，經(jīng)常做以數(shù)據(jù)為驅(qū)動(dòng)的mis系統(tǒng)的童鞋應(yīng)該不會(huì)陌生。本文一筆帶過。下面我們一步一步實(shí)現(xiàn)以ADO.NET為核心的數(shù)據(jù)訪問程序。

二、數(shù)據(jù)訪問持久化層

1、IDbOperation接口

  
 
 
    
  
  
  using System.Collections.Generic;     
  
  
  using System.Data;     
  
  
  using System.Data.Common;     
  
  
  namespace AdoNetDataAccess.Core.Contract     
  
  
  {     
  
  
      public interface IDbOperation     
  
  
      {     
  
  
          DbCommand CreateDbCommd(DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType cmdType, List listParams);     
  
  
          DbParameter CreateDbPrameter(string paramName, object paramValue);     
  
  
          DbDataReader ExecuteReader(string sqlStr, CommandType cmdType, List listParams);     
  
  
          DataTable FillDataTable(string sqlStr, CommandType cmdType, List listParams);     
  
  
          DataSet FillDataSet(string sqlStr, CommandType cmdType, List listParams);     
  
  
          object ExecuteScalar(string sqlStr, CommandType cmdType, List listParams);     
  
  
         int ExecuteNonQuery(string sqlStr, CommandType cmdType, List listParams);     
  
  
          ///      
  
  
          /// 批量插入     
  
  
          ///      
  
  
          /// 表名稱     
  
  
          /// 組裝好的要批量導(dǎo)入的datatable     
  
  
          ///      
  
  
          bool ExecuteBatchInsert(string tableName, int batchSize, int copyTimeout, DataTable dt);     
  
  
          void OpenConnection();     
  
  
          void CloseConnection();     
  
  
      }     
  
  
  }

上面的接口包括增刪改查，批量插入以及數(shù)據(jù)庫連接對(duì)象的連接和關(guān)閉等常用操作，您可以根據(jù)命名和參數(shù)輕松理解函數(shù)的含義。根據(jù)樓豬的開發(fā)經(jīng)驗(yàn)，對(duì)于平時(shí)的數(shù)據(jù)庫操作，上述方法差不多夠用了。當(dāng)然您也可以按照自己需要，重寫組織添加其他函數(shù)。

2、針對(duì)一種數(shù)據(jù)源的數(shù)據(jù)操作實(shí)現(xiàn)

底層的數(shù)據(jù)操作接口定義好后，就要針對(duì)一種數(shù)據(jù)源，具體實(shí)現(xiàn)上述的數(shù)據(jù)操作。這里樓豬選擇了Sql Server。我們也可以實(shí)現(xiàn)其他數(shù)據(jù)源的數(shù)據(jù)訪問操作，按照配置，利用抽象工廠動(dòng)態(tài)反射選擇是哪一種數(shù)據(jù)源的實(shí)現(xiàn)。這里按下不表，有心的童鞋自己可以動(dòng)手一試。下面是具體的實(shí)現(xiàn)：

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 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 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 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("執(zhí)行數(shù)據(jù)庫操作失敗, sql: " + sqlStr, e);
}
finally
{
sqlDataAdpter.Dispose();
cmd.Dispose();
trans.Dispose();
CloseConnection();
}
return dt;
}
public DataSet FillDataSet(string sqlStr, CommandType cmdType, List 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("執(zhí)行數(shù)據(jù)庫操作失敗, sql: " + sqlStr, e);
}
finally
{
sqlDataAdpter.Dispose();
cmd.Dispose();
trans.Dispose();
CloseConnection();
}
return ds;
}
public object ExecuteScalar(string sqlStr, CommandType cmdType, List 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("執(zhí)行數(shù)據(jù)庫操作失敗, sql: " + sqlStr, e);
}
finally
{
trans.Dispose();
CloseConnection();
}
return result;
}
public int ExecuteNonQuery(string sqlStr, CommandType cmdType, List 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("執(zhí)行數(shù)據(jù)庫操作失敗, sql: " + sqlStr, e);
}
finally
{
trans.Dispose();
CloseConnection();
}
return result;
}
///
/// 批量插入
///
///
///
///
///
///
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))
{
//服務(wù)器上目標(biāo)表的名稱
sbc.DestinationTableName = tableName;
sbc.BatchSize = batchSize;
sbc.BulkCopyTimeout = copyTimeout;
for (int i = 0; i < dt.Columns.Count; i++)
{
//列映射定義數(shù)據(jù)源中的列和目標(biāo)表中的列之間的關(guān)系
sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sbc.WriteToServer(dt);
flag = true;
scope.Complete();//有效的事務(wù)
}
}
}
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
///
/// dispose接口方法
///
public void Dispose()
{
}
#endregion
}
}

到這里，我們實(shí)現(xiàn)了SqlServer類里的方法，對(duì)Ms SqlServer數(shù)據(jù)庫我們就已經(jīng)可以進(jìn)行簡單的基礎(chǔ)的CRUD操作了。

三、簡單直觀的對(duì)象實(shí)體轉(zhuǎn)換

在第二步中，我們已經(jīng)實(shí)現(xiàn)了簡單的數(shù)據(jù)CRUD操作。根據(jù)樓豬使用ORM的經(jīng)驗(yàn)和習(xí)慣，我們也應(yīng)該對(duì)一些查詢結(jié)果進(jìn)行轉(zhuǎn)換，因?yàn)橐灶惖慕M織方式比直接呈現(xiàn)ADO.NET對(duì)象更容易讓人接受，效率高低反在其次。下面利用常見的反射原理，簡單實(shí)現(xiàn)一個(gè)對(duì)象實(shí)體轉(zhuǎn)換器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      
  
  
       
  
  
          ///       
  
  
          /// 查詢數(shù)據(jù)表項(xiàng)并轉(zhuǎn)換為對(duì)應(yīng)實(shí)體      
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          public static IList QueryForList(string sqlStr, CommandType cmdType, List listParams, Type objType, IDbOperation dbOperation)      
  
  
              where T : class, new()      
  
  
          {      
  
  
              IDataReader rdr = dbOperation.ExecuteReader(sqlStr, cmdType, listParams);      
  
  
              IList listModels = new List();      
  
  
              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      
  
  
       
  
  
          ///       
  
  
          /// 查詢數(shù)據(jù)表項(xiàng)并轉(zhuǎn)換為對(duì)應(yīng)實(shí)體      
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          /// 字典對(duì)應(yīng)key列名      
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          public static IDictionary QueryForDictionary    
  
  
  (string key, string sqlStr, CommandType cmdType, List listParams, Type objType, IDbOperation dbOperation)      
  
  
              where T : class, new()      
  
  
          {      
  
  
              IDataReader rdr = dbOperation.ExecuteReader(sqlStr, cmdType, listParams);      
  
  
              IDictionary dictModels = new Dictionary();      
  
  
              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(); //不區(qū)分大小寫      
  
  
                  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,                                                 

                                                文章標(biāo)題：ADO.NET快速上手實(shí)踐總結(jié)                                                

                                                URL鏈接：http://fisionsoft.com.cn/article/dhcsped.html

新聞中心

其他資訊