ADO.NET快速上手实践总结-内江站浩晨众云

新闻中心

这里有您想知道的互联网营销解决方案

ADO.NET快速上手实践总结

本文希望能给大家一些启发。前言：这两天重温经典，对ADO.NET的东西稍微深入的了解了一下，顺便写点代码练练手，全当是复习笔记吧。

创新互联主要从事成都网站设计、做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务海西,十多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18982081108

一、简单说说ADO.NET的5大常用对象

既然说ADO.NET，当然不能免俗地要提到5大常用对象。本文不会对ADO.NET的5大对象和它们的关系进行过多阐释，不过我们应该对下面这张图的结构有个了解：

关于上图图示中的5大对象，经常做以数据为驱动的mis系统的童鞋应该不会陌生。本文一笔带过。下面我们一步一步实现以ADO.NET为核心的数据访问程序。

二、数据访问持久化层

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);     
  
  
          ///      
  
  
          /// 批量插入     
  
  
          ///      
  
  
          /// 表名称     
  
  
          /// 组装好的要批量导入的datatable     
  
  
          ///      
  
  
          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 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("执行数据库操作失败, 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("执行数据库操作失败, 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("执行数据库操作失败, 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("执行数据库操作失败, 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))
{
//服务器上目标表的名称
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
///
/// dispose接口方法
///
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      
  
  
       
  
  
          ///       
  
  
          /// 查询数据表项并转换为对应实体      
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          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      
  
  
       
  
  
          ///       
  
  
          /// 查询数据表项并转换为对应实体      
  
  
          ///       
  
  
          ///       
  
  
          ///       
  
  
          /// 字典对应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(); //不区分大小写      
  
  
                  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,                                                 

                                                新闻名称：ADO.NET快速上手实践总结                                                

                                                当前链接：http://www.jxjierui.cn/article/dhcsped.html

新闻中心

其他资讯