在应用程序的设计中，数据库的访问是非常重要的，我们通常需要将对数据库的访问集中起来，以保证良好的封装性和可维护性。在.Net中，数据库的访问，对于微软自家的SqlServer和其他数据库（支持OleDb），采用不同的访问方法，这些类分别分布于System.Data.SqlClient和System.Data.OleDb名称空间中。微软后来又推出了专门用于访问Oracle数据库的类库。我们希望在编写应用系统的时候，不因这么多类的不同而受到影响，能够尽量做到数据库无关，当后台数据库发生变更的时候，不需要更改客户端的代码。
有的时候，为了性能和其他原因，我们也希望提供对数据库访问的缓存，特别是数据库连接的缓存。虽然微软给我们内置了数据库缓存，但是，自己控制缓存，无疑可以提供更大的灵活性和效率。
public class BaseDataAccess : IDisposable
{
/// 数据适配器
private SqlDataAdapter dsAdapter;
/// 数据更新事务句柄
SqlTransaction dsTransaction;
private SqlCommand selectCommand;
private SqlCommand updateCommand;
private SqlCommand insertCommand;
private SqlCommand deleteCommand;
// 数据库连接
private SqlConnection dbConnection;
private DBConfigration dbConfigration = new DBConfigration();
public BaseDataAccess()
{
/// 创建数据适配器对象“OldDbDataAdapter”
dsAdapter = new SqlDataAdapter();
/// 获取数据操纵连接
dbConnection = new SqlConnection(DBConfigration.BitSoftConnectionString);
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}
protected virtual void Dispose(bool disposing)
{
if (! disposing)
return;
if (dsAdapter != null )
{
if (dsAdapter.SelectCommand != null)
{
if( dsAdapter.SelectCommand.Connection != null)
dsAdapter.SelectCommand.Connection.Dispose();
dsAdapter.SelectCommand.Dispose();
}
dsAdapter.Dispose();
dsAdapter = null;
}
}
/// <summary>
/// 检查传入的单个数据表单（DataTable）字段域,自动获得该数据表单的“Update”命令。
/// </summary>
/// <param name="DataTable">单个数据表单</param>
/// <returns>SqlCommand</returns>
private SqlCommand GetUpdateByIdCommand(DataTable oneTable)
{
string sqlFirst = " UPDATE ";
string sqlSecond = " WHERE ";
sqlFirst = sqlFirst + oneTable.TableName + " SET ";
/// 创建 SqlCommand 对象
if ( updateCommand == null )
{
updateCommand = new SqlCommand(sqlFirst, dbConnection);
}
/// Update 语句的参数表集合
SqlParameterCollection updateParams = updateCommand.Parameters;
updateCommand.Parameters.Clear();
/// 遍历 DataTable 字段，生成 SQL UPDATE 语句
bool isFirstColumn = true;
bool isFirstKeyColumn = true;
foreach (BaseDataColumn dc in oneTable.Columns)
{
/// 忽略无用字段
if ( dc.IsValidColumn() )
{
/// 获取关键字字段
if ( dc.IsKeyColumn() )
{
if ( isFirstKeyColumn )
{
sqlSecond = sqlSecond + dc.ColumnName + " = @"+dc.ColumnName;
isFirstKeyColumn = false;
}
else
sqlSecond = sqlSecond + " AND " + dc.ColumnName + " = @"+dc.ColumnName;
}
else
{
if ( isFirstColumn )
{
sqlFirst = sqlFirst + dc.ColumnName + " = @"+dc.ColumnName;
isFirstColumn = false;
}
else
sqlFirst = sqlFirst + ", " + dc.ColumnName + " = @"+dc.ColumnName;
updateParams.Add(new sqlParameter("@"+dc.ColumnName,dc.GetColumnSqlDbType()));
updateParams["@"+dc.ColumnName].SourceColumn = dc.ColumnName;
}
}
}
foreach (BaseDataColumn dc in oneTable.Columns)
{
/// 忽略无用字段
if ( dc.IsValidColumn() )
{
/// 获取关键字字段
if ( dc.IsKeyColumn() )
{
updateParams.Add(new SqlParameter("@"+dc.ColumnName, dc.GetColumnSqlDbType()));
updateParams["@"+dc.ColumnName].SourceColumn = dc.ColumnName;
}
}
}
/// 设置 Command 类型和完整的 UPDATE 语句
updateCommand.CommandType = CommandType.Text;
updateCommand.CommandText = sqlFirst + sqlSecond;
return updateCommand;
}
/// <summary>
/// 检查传入的单个数据表单（DataTable）字段域,自动获得该数据表单的“Insert”命令。
/// </summary>
/// <param name="oneTable">单个数据表单</param>
/// <returns>SqlCommand</returns>
private SqlCommand GetInsertCommand(DataTable oneTable)
{
string sqlFirst = " INSERT INTO ";
string sqlSecond = " VALUES ( ";
/// 拼接表名
sqlFirst = sqlFirst + oneTable.TableName ;
/// 创建 SqlCommand 对象
if ( insertCommand == null )
{
insertCommand = new SqlCommand(sqlFirst, dbConnection);
}
/// Insert 语句的参数表集合
SqlParameterCollection insertParams = insertCommand.Parameters;
insertCommand.Parameters.Clear();
/// 遍历 DataTable 字段，生成 SQL INSERT 语句
bool isFirstColumn = true;
foreach (BaseDataColumn dc in oneTable.Columns)
{
/// 忽略无用字段
if ( dc.IsValidColumn() && !dc.AutoIncrement)
{
if ( isFirstColumn )
{
sqlFirst = sqlFirst + " (" + dc.ColumnName;
isFirstColumn = false;
sqlSecond = sqlSecond +"@"+dc.ColumnName;
}
else
{
sqlFirst = sqlFirst + ", " + dc.ColumnName;
sqlSecond = sqlSecond + "," +"@"+dc.ColumnName;
}
/// 生成 Insert 参数表集合
insertParams.Add(new SqlParameter("@"+dc.ColumnName, dc.GetColumnSqlDbType()));
insertParams["@"+dc.ColumnName].SourceColumn = dc.ColumnName;
}
}
/// 设置 Command 类型和完整的 UPDATE 语句
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = sqlFirst + ") " + sqlSecond + " )";
return insertCommand;
}
/// <summary>
/// 检查传入的单个数据表单（DataTable）字段域,自动获得该数据表单的“Delete”命令。
/// </summary>
/// <param name="oneTable">单个数据表单</param>
/// <returns>SqlCommand</returns>
private SqlCommand GetDeleteByIdCommand(DataTable oneTable)
{
string sqlFirst = " DELETE FROM ";
sqlFirst = sqlFirst + oneTable.TableName + " WHERE ";
/// 创建 SqlCommand 对象
if ( deleteCommand == null )
{
deleteCommand = new SqlCommand(sqlFirst, dbConnection);
}
/// Delete 语句的参数表集合
SqlParameterCollection deleteParams = deleteCommand.Parameters;
deleteCommand.Parameters.Clear();
/// 遍历 DataTable 字段，生成 SQL DELETE 语句
bool isFirstKeyColumn = true;
foreach (BaseDataColumn dc in oneTable.Columns)
{
/// 忽略无用字段
if ( dc.IsValidColumn() )
{
/// 获取关键字字段
if ( dc.IsKeyColumn() )
{
if ( isFirstKeyColumn )
{
sqlFirst = sqlFirst + dc.ColumnName + " = @"+dc.ColumnName;
isFirstKeyColumn = false;
}
else
sqlFirst = sqlFirst + " AND " + dc.ColumnName + " = @"+dc.ColumnName;
/// 生成 Delete 参数表集合
deleteParams.Add(new SqlParameter("@"+dc.ColumnName, dc.GetColumnSqlDbType()));
deleteParams["@"+dc.ColumnName].SourceColumn = dc.ColumnName;
}
}
}
/// 设置 Command 类型和完整的 DELETE 语句
deleteCommand.CommandType = CommandType.Text;
deleteCommand.CommandText = sqlFirst;
return deleteCommand;
}
/// <summary>
/// 根据指定的对象数据集，更新相应的数据库。
/// </summary>
/// <param name="dataSet">对象数据集</param>
/// <returns>返回是否成功的布尔值</returns>
public bool UpdateObjectData(BaseDataSet myDataSet)
{
if ( dsAdapter == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
try
{
dbConnection.Open();
try
{
dsTransaction = dbConnection.BeginTransaction();
}
catch
{
dbConnection.Close();
return false;
}
/// 遍历 DataSet 中的每一个表单进行更新处理
foreach (DataTable dt in myDataSet.Tables)
{
dsAdapter.UpdateCommand = GetUpdateByIdCommand(dt);
/// 设置记录更新的事务句柄
dsAdapter.UpdateCommand.Transaction = dsTransaction;
/// 根据传入DataSet的表和表中的内容更新相应的数据库
dsAdapter.Update( myDataSet, dt.TableName);
}
try
{
dsTransaction.Commit();
}
catch
{
dsTransaction.Rollback();
return false;
}
finally
{
/// 无论更新是否成功，及时释放数据库连接
dbConnection.Close();
}
/// 刷新 DataSet 更新标志
myDataSet.AcceptChanges();
return true;
}
catch(Exception e)
{
string a=e.Message;
return false;
}
}
/// <summary>
/// 根据指定的对象数据集，新增插入相应的数据库。
/// </summary>
/// <param name="myDataSet">对象数据集</param>
/// <returns>返回是否成功的布尔值</returns>
public bool InsertObjectData(BaseDataSet myDataSet)
{
if ( dsAdapter == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
try
{
dbConnection.Open();
/// 启动事务处理
try
{
dsTransaction = dbConnection.BeginTransaction();
}
catch
{
dbConnection.Close();
return false;
}
/// 遍历 DataSet 中的每一个表单进行更新处理
try
{
/// 调用自动产生关键字ID的方法。
if ( AutoCreatID(myDataSet,dsTransaction) )
{
foreach (DataTable dt in myDataSet.Tables)
{
dsAdapter.InsertCommand = GetInsertCommand(dt);
/// 设置记录更新的事务句柄
dsAdapter.InsertCommand.Transaction = dsTransaction;
/// 根据传入DataSet的表和表中的内容更新相应的数据库
dsAdapter.Update( myDataSet, dt.TableName);
}
/// 提交事务
dsTransaction.Commit();
}
else
{
/// 回滚事务
dsTransaction.Rollback();
return false;
}
}
catch(Exception e)
{
/// 回滚事务
string a=e.Message;
dsTransaction.Rollback();
return false;
}
finally
{
/// 无论更新是否成功，及时释放数据库连接
dbConnection.Close();
}
/// 刷新 DataSet 更新标志
myDataSet.AcceptChanges();
return true;
}
catch(Exception e)
{
string a=e.Message;
return false;
}
}
/// <summary>
/// 根据指定的对象数据集，删除相应的数据库记录。
/// </summary>
/// <param name="myDataSet">对象数据集</param>
/// <returns>返回是否成功的布尔值</returns>
public bool DeleteObjectData(BaseDataSet myDataSet)
{
if ( dsAdapter == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
try
{
dbConnection.Open();
try
{
dsTransaction = dbConnection.BeginTransaction();
}
catch
{
dbConnection.Close();
return false;
}
/// 遍历 DataSet 中的每一个表单进行更新处理
try
{
foreach (DataTable dt in myDataSet.Tables)
{
dsAdapter.DeleteCommand = GetDeleteByIdCommand(dt);
/// 设置记录更新的事务句柄
dsAdapter.DeleteCommand.Transaction = dsTransaction;
/// 根据传入DataSet的表和表中的内容更新相应的数据库
dsAdapter.Update( myDataSet, dt.TableName);
}
dsTransaction.Commit();
}
catch(Exception e)
{
string a=e.Message;
dsTransaction.Rollback();
return false;
}
finally
{
/// 无论更新是否成功，及时释放数据库连接
dbConnection.Close();
}
/// 刷新 DataSet 更新标志
myDataSet.AcceptChanges();
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 根据指定的对象数据集，同时删除、修改、添加相应的数据库记录。
/// </summary>
/// <param name="myDataSet">对象数据集</param>
/// <returns>返回是否成功的布尔值</returns>
public bool SynchronizeObjectData(BaseDataSet dataSet)
{
if ( dsAdapter == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
try
{
dbConnection.Open();
try
{
dsTransaction = dbConnection.BeginTransaction();
}
catch
{
dbConnection.Close();
return false;
}
/// 遍历 DataSet 中的每一个表单进行更新处理
try
{
/// 调用自动产生关键字ID的方法。
if ( AutoCreatID(dataSet,dsTransaction) )
{
foreach (DataTable dt in dataSet.Tables)
{
/// 删除相应记录。
dsAdapter.DeleteCommand = GetDeleteByIdCommand(dt);
/// 设置记录删除的事务句柄
dsAdapter.DeleteCommand.Transaction = dsTransaction;
/// 修改相应记录。
dsAdapter.UpdateCommand = GetUpdateByIdCommand(dt);
/// 设置记录更新的事务句柄
dsAdapter.UpdateCommand.Transaction = dsTransaction;
/// 新增相应记录。
dsAdapter.InsertCommand = GetInsertCommand(dt);
/// 设置记录更新的事务句柄
dsAdapter.InsertCommand.Transaction = dsTransaction;
/// 根据传入DataSet的表和表中的内容删除/更新/新增相应的数据库
dsAdapter.Update( dataSet, dt.TableName);
}
/// 提交事务
dsTransaction.Commit();
}
else
{
/// 回滚事务
dsTransaction.Rollback();
return false;
}
}
catch
{
dsTransaction.Rollback();
return false;
}
finally
{
/// 无论更新是否成功，及时释放数据库连接
dbConnection.Close();
}
/// 刷新 DataSet 更新标志
dataSet.AcceptChanges();
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 根据传入关键字值列表，填充相应的对象数据集。
/// 注意：关键字值列表为“@字段名，关键字值”格式的参数表。
/// </summary>
/// <param name="dataSet">用于装配的数据集对象</param>
/// <param name="keyTable">对象的关键字（可能多个）和值参数表</param>
/// <returns>返回是否成功的布尔值</returns>
public BaseDataSet FillDataSetByArray(BaseDataSet dataSet, ParmArrayWithOP keyArray)
{
if ( dsAdapter == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
try
{
foreach (DataTable dt in dataSet.Tables)
{
dsAdapter.SelectCommand = GetSelectBySqlOP(dt,keyArray);
// 设置关键字的值
foreach (SqlParameter pt in dsAdapter.SelectCommand.Parameters)
{
dsAdapter.SelectCommand.Parameters[pt.ParameterName].Value = keyArray.GetParmValue(keyArray.IndexOf(pt.ParameterName));
}
dsAdapter.Fill(dataSet,dt.TableName);
}
}
catch
{
/// 写入例外处理错误消息。
//MessagerBulletin.WriteMsg(e.Message);
return dataSet;
}
return dataSet;
}
/// <summary>
///说明： 该方法将取得带有分页显示功能的SELECT语句。目的是一次只返回一页的记录，而不是整个表记录。
/// 该方法有两种重载形式，分别为为带条件的查询和不带条件的查询。
///参数： intPageNum：每页的显示的记录数
/// intPageIndex：当前页号
/// strTableName：表名
/// strKey"：主键
/// strParam[]：参数数组。此值仅当blnCondition为true时有效。
/// objValues[]：值数组。此值仅当blnCondition为ture时有效。
/// strOrderField：排序的字段名
/// strOrderType：排序类型，是升序还是降序。其值为：ASC-升序，DESC-降序
///返回：select语句，其中输出参数返回取得记录总数的SQL语句，方法本身返回查询SQL语句
/// </summary>
public string GetPageSelectByID(int intPageNum,int intPageIndex,string strTableName,string strKey,string[] strParams,object[] objValues,string strOrderField,string strOrderType,out string strSqlCount)
{
string strSql;
string strTemp = " "; //存放条件字句
//构造select语句
strSql = "select Top " + intPageNum.ToString() + " * from " + strTableName + " where "
+ strKey + " not in (select top " + ((intPageIndex - 1) * intPageNum).ToString() + " " + strKey + " from " + strTableName;
//在生成的select语句上加上where条件子句
if ((strParams.Length == 0) | (strParams.Length != objValues.Length))
{
throw (new ParamValueNotMatchException("缺少参数值或参数与值不对应"));
}
for (int i=0;i <= strParams.Length - 1;i++)
{
strTemp = strTemp + " and " + strParams[i] + "=" + "'" + objValues[i].ToString() + "'";
}
char[] and = new char[] {'a','n','d'};
strTemp = (strTemp.TrimStart()).TrimStart(and); //去除头部多余的"and"
strSql = strSql + " where " + strTemp + " order by " + strOrderField + " " + strOrderType + ")"
+ " and " + strTemp + " order by " + strOrderField + " " + strOrderType ;
//得到查询总记录数的语句
strSqlCount = "select count(*) from " + strTableName + " where " + strTemp;
return strSql;
}
