ado.net快速上手实践篇（一）
发布时间：2020-12-12 15:19:59 所属栏目：MsSql教程 来源：网络整理
导读：? 文章来源：IT工程信息网? http://www.systhinker.com/?viewnews-11675 前言：这两天重温经典，对ado.net的东西稍微深入的了解了一下，顺便写点代码练练手，全当是复习笔记吧。 一、简单说说ado.net的5大常用对象 既然说ado.net，当然不能免俗地要提到5大常
|
? 文章来源：IT工程信息网? http://www.systhinker.com/?viewnews-11675前言：这两天重温经典，对ado.net的东西稍微深入的了解了一下，顺便写点代码练练手，全当是复习笔记吧。 既然说ado.net，当然不能免俗地要提到5大常用对象。本文不会对ado.net的5大对象和它们的关系进行过多阐释，不过我们应该对下面这张图的结构有个了解：
关于上图图示中的5大对象，经常做以数据为驱动的mis系统的童鞋应该不会陌生。本文一笔带过。下面我们一步一步实现以ado.net为核心的数据访问程序。 【注意：下面的示例代码和demo是楼猪本周六和周日两天时间实现的，未经详细测试，可能有重大bug，下载学习使用的童鞋务必注意】 二、数据访问持久化层 1、IDbOperation接口
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,?List < DbParameter > ?listParams); ????????DataTable?FillDataTable( string ?sqlStr,?List < DbParameter > ?listParams); ????????DataSet?FillDataSet( string ?sqlStr,?List < DbParameter > ?listParams); ???????? object ?ExecuteScalar( string ?sqlStr,?List < DbParameter > ?listParams); ???????? int ?ExecuteNonQuery( string ?sqlStr,?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(); ????} } 上面的接口包括增删改查，批量插入以及数据库连接对象的连接和关闭等常用操作，您可以根据命名和参数轻松理解函数的含义。根据楼猪的开发经验，对于平时的数据库操作，上述方法差不多够用了。当然您也可以按照自己需要，重写组织添加其他函数。
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,?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,?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,?List < DbParameter > ?listParams) ????????{ ????????????OpenConnection(); ????????????DbTransaction?trans? = ?sqlConn.BeginTransaction(); ????????????DbCommand?cmd? = ?CreateDbCommd(sqlConn,?trans,?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,?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( " 执行数据库操作失败,?e); ????????????} ???????????? finally ????????????{ ????????????????sqlDataAdpter.Dispose(); ????????????????cmd.Dispose(); ????????????????trans.Dispose(); ????????????????CloseConnection(); ????????????} ???????????? return ?ds; ????????} ???????? public ? object ?ExecuteScalar( string ?sqlStr,?List < DbParameter > ?listParams) ????????{ ???????????? object ?result? = ? null ; ????????????OpenConnection(); ????????????DbTransaction?trans? = ?sqlConn.BeginTransaction(); ???????????? try ????????????{ ????????????????cmd? = ?CreateDbCommd(sqlConn,?listParams); ????????????????result? = ?cmd.ExecuteScalar(); ????????????????trans.Commit(); ????????????} ???????????? catch ?(Exception?e) ????????????{ ????????????????trans.Rollback(); ???????????????? throw ? new ?Exception( " 执行数据库操作失败,?e); ????????????} ???????????? finally ????????????{ ????????????????trans.Dispose(); ????????????????CloseConnection(); ????????????} ???????????? return ?result; ????????} ???????? public ? int ?ExecuteNonQuery( string ?sqlStr,?List < DbParameter > ?listParams) ????????{ ???????????? int ?result? = ? - 1 ; ????????????OpenConnection(); ????????????DbTransaction?trans? = ?sqlConn.BeginTransaction(); ???????????? try ????????????{ ????????????????cmd? = ?CreateDbCommd(sqlConn,?listParams); ????????????????result? = ?cmd.ExecuteNonQuery(); ????????????????trans.Commit(); ????????????} ???????????? catch ?(Exception?e) ????????????{ ????????????????trans.Rollback(); ???????????????? throw ? new ?Exception( " 执行数据库操作失败,?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,?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操作了。
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,?List < DbParameter > ?listParams,?Type?objType,?IDbOperation?dbOperation) ???????????? where ?T?:? class ,? new () ????????{ ????????????IDataReader?rdr? = ?dbOperation.ExecuteReader(sqlStr,?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 ? ? 文章来源：IT工程信息网? http://www.systhinker.com/?viewnews-11675 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
