ado.net快速上手实践篇（一）
这两天重温经典，对ado.net的东西稍微深入的了解了一下，顺便写点代码练练手，全当是复习笔记吧。
一、简单说说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 inte易做图ce 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();
}
&nbs
补充：Web开发 , ASP.Net ,
