ado.net快速上手实践篇(一)

前言:这两天重温经典,对ado.net的东西稍微深入的了解了一下,顺便写点代码练练手,全当是复习笔记吧。
一、简单说说ado.net的5大常用对象

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

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

注意:下面的示例代码和demo是楼猪本周六和周日两天时间实现的,未经详细测试,可能有重大bug,下载学习使用的童鞋务必注意】

二、数据访问持久化层
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<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();
    }
}

上面的接口包括增删改查,批量插入以及数据库连接对象的连接和关闭等常用操作,您可以根据命名和参数轻松理解函数的含义。根据楼猪的开发经验,对于平时的数据库操作,上述方法差不多够用了。当然您也可以按照自己需要,重写组织添加其他函数。
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<DbParameter> 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<DbParameter> 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<DbParameter> 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<DbParameter> 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<DbParameter> 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<DbParameter> 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;
        }

        
/// <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))
                    {
                        
//服务器上目标表的名称
                        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

        
/// <summary>
        
/// dispose接口方法
        
/// </summary>
        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

        
/// <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 : classnew()
        {
            IDataReader rdr 
= dbOperation.ExecuteReader(sqlStr, cmdType, listParams);
            IList
<T> listModels = new List<T>();
            
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

        
/// <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 : classnew()
        {
            IDataReader rdr 
= dbOperation.ExecuteReader(sqlStr, cmdType, listParams);
            IDictionary
<K, T> dictModels = new Dictionary<K, T>();
            
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, 
null);
            }
            
catch
            {
                
object realValue = null;
                
try
                {
                    realValue 
= Convert.ChangeType(objValue, propInfo.PropertyType);
                    propInfo.SetValue(obj, realValue, 
null);
                }
                
catch (Exception ex)
                {
                    
string err = ex.Message;
                    
//throw ex; //在数据库数据有不符合规范的情况下应该及时抛出异常
                }
            }
        }

        
#endregion
    }
}


到这里,简单的数据访问持久化层就实现了。下面模仿楼猪使用的IBatis.net,写个伪SqlMapper,改善一下调用形式,丰富一下调用方法,让方法辨识度更高。
四、实现伪SqlMapper
1、BaseMapper类

代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

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

    
public abstract class BaseMapper
    {
        
public IDbOperation CurrentDbOperation;

        
#region query for list

        
public abstract IList<T> QueryForList<T>(string sqlStr)
  
where T : classnew();

        
public abstract IList<T> QueryForList<T>(string sqlStr, Type objType)
where T : classnew();

        
public abstract IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
       
where T : classnew();

        
public abstract IList<T> QueryForList<T>(string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
       
where T : classnew();


        
#endregion

        
#region query for dictionary

        
public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr)
            
where T : classnew();

        
public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, Type objType)
    
where T : classnew();

        
public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, Type objType)
            
where T : classnew();

        
public abstract IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, CommandType cmdType, List<DbParameter> listParams, Type objType)
                    
where T : classnew();

        
#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

    }

}

上面代码中的方法您是不是很熟悉呢? 呵呵,使用IBatis.net 的童鞋应该会和楼猪产生更多的共鸣。
2、SqlMapper类

代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

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

    
public class SqlMapper : BaseMapper
    {
        
private SqlMapper()
        {

        }

        
public SqlMapper(IDbOperation dbOperation)
        {
            
this.CurrentDbOperation = dbOperation;
        }

        
#region query for list

        
public override IList<T> QueryForList<T>(string sqlStr)
        {
            
return QueryForList<T>(sqlStr, CommandType.Text, nulltypeof(T));
        }

        
public override IList<T> QueryForList<T>(string sqlStr, Type objType)
        {
            
return QueryForList<T>(sqlStr, CommandType.Text, 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 ModelConverter.QueryForList<T>(sqlStr, cmdType, listParams, objType, this.CurrentDbOperation);
        }

        
#endregion

        
#region query for dictionary

        
public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr)
        {
            
return QueryForDictionary<K, T>(key, sqlStr, CommandType.Text, nulltypeof(T));
        }

        
public override IDictionary<K, T> QueryForDictionary<K, T>(string key, string sqlStr, Type objType)
        {
            
return QueryForDictionary<K, T>(key, sqlStr, CommandType.Text, 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 ModelConverter.QueryForDictionary<K, T>(key, sqlStr, cmdType, listParams, objType, this.CurrentDbOperation);
        }

        
#endregion

        
#region dataset datatable

        
public override DataTable FillDataTable(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            
return this.CurrentDbOperation.FillDataTable(sqlStr, cmdType, listParams);
        }

        
public override DataSet FillDataSet(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            
return this.CurrentDbOperation.FillDataSet(sqlStr, cmdType, listParams);
        }

        
#endregion

        
#region ExecuteScalar

        
public override object ExecuteScalar(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            
return this.CurrentDbOperation.ExecuteScalar(sqlStr, cmdType, listParams);
        }

        
#endregion

        
#region insert

        
public override int Insert(string sqlStr)
        {
            
object obj = ExecuteScalar(sqlStr, CommandType.Text, null);
            
int id = obj == null ? 0 : int.Parse(obj.ToString());
            
return id;
        }

        
public override int Insert(string sqlStr, CommandType cmdType, List<DbParameter> listParams)
        {
            
object obj = ExecuteScalar(sqlStr, cmdType, listParams);
            
int id = obj == null ? 0 : int.Parse(obj.ToString());
            
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 this.CurrentDbOperation.ExecuteBatchInsert(tableName, batchSize, copyTimeout, dt);
        }

        
#endregion

        
#region delete

        
public override int Delete(string sqlStr)
        {
            
return CommitSql(sqlStr, CommandType.Text, 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, CommandType.Text, 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 this.CurrentDbOperation.ExecuteNonQuery(sqlStr, cmdType, listParams);
        }

        
#endregion

        
#region  dbparameter

        
public DbParameter CreateParameter(string paraName, object paramValue)
        {
            
return this.CurrentDbOperation.CreateDbPrameter(paraName, paramValue);
        }

        
public List<DbParameter> CreateParameterList(string[] paraNames, object[] paramValues)
        {
            List
<DbParameter> listParams = new List<DbParameter>();
            
try
            {
                
if (paraNames.Length != paramValues.Length)
                {
                    
throw new Exception("Param name and value is not equal.");
                }
                
for (int i = 0; i < paraNames.Length; i++)
                {
                    DbParameter param 
= CreateParameter(paraNames[i], paramValues[i]);
                    listParams.Add(param);
                }
            }
            
catch (Exception ex)
            {
                
throw ex;
            }
            
return listParams;
        }

        
#endregion

    }

}

上面的方法丰富实现了CRUD的常见操作,其实主要还是调用了IDbOperation接口和方法。
未完,待续。

demo下载:demo

 

posted on 2010-05-23 14:03  JeffWong  阅读(3884)  评论(5编辑  收藏