这两天重温经典
对的东西稍微深入的了解了一下
顺便写点代码练练手
全当是复习笔记吧
一简单说说的大常用对象 既然说当然不能免俗地要提到大常用对象本文不会对的大对象和它们的关系进行过多阐释不过我们应该对下面这张图的结构有个了解
关于上图图示中的大对象经常做以数据为驱动的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接口和方法
