【编者按】网学网ASP.net频道为大家收集整理了“设计模式开发通用数据库操作器（2）“提供大家参考，希望对大家有所帮助!
/// <summary>
/// 获取指定数据库的类型
/// </summary>
/// <param name="dbName">指定的数据库名</param>
/// <returns>指定数据库的类型</returns>
public static DBType GetDBType(string dbName)
{
string dbType = null;
dbType = AppConfig.GetAppSetting(dbName + DBTypeEnds);
if (dbType.ToLower() == DBType.Oracle.ToString().ToLower())
{
return DBType.Oracle;
}
if (dbType.ToLower() == DBType.Odbc.ToString().ToLower())
{
return DBType.Odbc;
}
if (dbType.ToLower() == DBType.OleDb.ToString().ToLower())
{
return DBType.OleDb;
}
else
{
return DBType.SQLServer;
}
}
/// <summary>
/// 保存指定数据库的类型
/// </summary>
/// <param name="dbType">指定数据库的类型</param>
/// <param name="dbName">指定的数据库名</param>
public static void SaveDBType(DBType dbType,string dbName)
{
AppConfig.SaveAppSetting(dbName + DBTypeEnds,dbType.ToString());
}
/// <summary>
/// 获取指定数据库的连接字符串
/// </summary>
/// <param name="dbName">指定的数据库名</param>
/// <returns>指定数据库的连接字符串</returns>
public static string GetDBConnectionString(string dbName)
{
return AppConfig.GetAppSetting(dbName + DBConnectionEnds);
}
/// <summary>
/// 保存指定数据库的连接字符串
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="dbName">指定的数据库名</param>
public static void SaveDBConnectionString(string connectionString, string dbName)
{
AppConfig.SaveAppSetting(dbName + DBConnectionEnds,connectionString);
}
}
接着为每一种数据库写一个类来执行针对该数据库的操作，例如针对SQL Server：
/// <summary>
/// 用于SQL数据源操作的类
/// </summary>
public class SQLExec
{
/// <summary>
/// 获取数据库连接，读取由Storm.AppSetting的配置文件中dbName + "DBCon"的设置(如针对数据库Test的配置键是“TestDBCon”),若没有，则抛出异常
/// </summary>
/// <param name="dbName">要获取数据连接的数据库名</param>
/// <returns>得到的数据库连接</returns>
public static SqlConnection GetDBConnection(string dbName)
{
return new SqlConnection(DBSetting.GetDBConnectionString());
}
private void ModifyDataBase(DataTableExtend[] dts, string dbName)
{
//打开连接
SqlConnection sqlCon = GetDBConnection(dbName);
sqlCon.Open();
//根据数据表的多少生成多个数据适配器并分别生成SQL语句
int length = dts.Length;
SqlDataAdapter[] myDataAdapters = new SqlDataAdapter[length];
for (int i = 0; i < length; i++)
{
string selectText = GetSelectCommand(dts[i].dataTableName);
myDataAdapters[i] = new SqlDataAdapter(selectText, sqlCon);
SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapters[i]);
myDataAdapters[i].InsertCommand = cb.GetInsertCommand();
myDataAdapters[i].UpdateCommand = cb.GetUpdateCommand();
myDataAdapters[i].DeleteCommand = cb.GetDeleteCommand();
}
//配置事务
SqlTransaction myTrans;
myTrans = sqlCon.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
for (int i = 0; i < length; i++)
{
myDataAdapters[i].SelectCommand.Transaction = myTrans;
myDataAdapters[i].InsertCommand.Transaction = myTrans;
myDataAdapters[i].UpdateCommand.Transaction = myTrans;
myDataAdapters[i].DeleteCommand.Transaction = myTrans;
//更新数据库
myDataAdapters[i].Update(dts[i].dataTable);
}
myTrans.Commit();
sqlCon.Close();
for(int i = 0; i < length ; i++)
{
dts[i].dataTable.AcceptChanges();
}
}
//如果失败，则自动回滚
catch(Exception ee)
{
myTrans.Rollback();
sqlCon.Close();
for(int i = 0; i < length ; i++)
{
dts[i].dataTable.RejectChanges();
}
throw ee;
}
}
/// <summary>
/// 从数据库中读取数据
/// </summary>
/// <param name="dt">要承载数据的数据表</param>
/// <param name="selectString">查询语句</param>
public void GetData(DataTable dt, string selectString, string dbName)
{
SqlDataAdapter myDataAdapter = new SqlDataAdapter(selectString,SQLConfig.GetDBConnection(dbName));
myDataAdapter.Fill(dt);
}
//自动生成查询语句
private static string GetSelectCommand(string dataTableName)
{
string strGet = "SELECT * FROM " +dataTableName;
return strGet;
}
}
然后就是写一个类来根据实际情况调用这些东东了：
public class DatabaseExecute
{
private string dbName;
/// <summary>
/// 目标数据库
/// </summary>
public string DBName
{
get{ return dbName; }
set{ dbName = value; }
}
/// <summary>
/// 生成DatabaseExecute的实例
/// </summary>
public DatabaseExecute()
{
dbName = null;
}
/// <summary>
/// 用指定的目标数据库生成DatabaseModifier的实例
/// </summary>
/// <param name="dbName"></param>
public DatabaseExecute(string dbName)
{
this.dbName = dbName;
}
/// <summary>
/// 从数据库中读取数据
/// </summary>
/// <param name="dt">要承载数据的数据表</param>
/// <param name="selectString">查询语句</param>
public void GetData(DataTable dt, string selectString)
{
//操作指定数据库
if (DBName != null)
{
if (DBSetting.GetDBType(dbName) == DBType.SQLServer)
{
SQLExec mySQLExec = new SQLExec();
mySQLExec. GetData(dt, selectString, DBName);
}
else if (DBSetting.GetDBType(dbName) == DBType.Odbc)
{
OdbcExec myOdbcExec = new OdbcExec();
myOdbcExec. GetData(dt, selectString, DBName);
}
else if (DBSetting.GetDBType(dbName) == DBType.OleDb)
{
OleDbExec myOleDbExec = new OleDbExec();
mySQLExec. GetData(dt, selectString, DBName);
}
else
{
OracleExec myOracleExec = new OracleExec();
myOracleExec. GetData(dt, selectString, DBName);
}
}
//操作默认数据库
else
{
if (DBSetting.GetDBType(“”) == DBType.SQLServer)
{
SQLExec mySQLExec = new SQLExec();
mySQLExec. GetData(dt, selectString, “”);
}
else if (DBSetting.GetDBType(“”) == DBType.Odbc)
{
OdbcExec myOdbcExec = new OdbcExec();
myOdbcExec. GetData(dt, selectString, “”);
}
else if (DBSetting.GetDBType(dbName) == DBType.OleDb)
{
OleDbExec myOleDbExec = new OleDbExec();
mySQLExec. GetData(dt, selectString, “”);
}
else
{
OracleExec myOracleExec = new OracleExec();
myOracleExec. GetData(dt, selectString, “”);
}
}
}
/// <summary>
/// 根据数据表组更新数据库
/// </summary>
/// <param name="dts">要更新的数据表组</param>
public void ModifyDataBase(DataTableExtend[] dts)
{
//操作指定数据库
if (dbName != null)
{
if (DBSetting.GetDBType(dbName) == DBType.SQLServer)
{
SQLExec mySQLExec = new SQLExec();
mySQLExec ModifyDataBase(dts,dbName);
}
else if (DBSetting.GetDBType(dbName) == DBType.Odbc)
{
OdbcExec mySQLExec = new OdbcExec();
myOdbcExec ModifyDataBase(dts,dbName);
}
else if (DBSetting.GetDBType(dbName) == DBType.OleDb)
{
OleDbExec mySQLExec = new OleDbExec();
myOleDbExec ModifyDataBase(dts,dbName);
}
else
{
OracleExec mySQLExec = new OracleExec();
myOracleExec ModifyDataBase(dts,dbName);
}
}
//操作默认数据库
else
{
if (DBSetting.GetDBType(“”) == DBType.SQLServer)
{
SQLExec mySQLExec = new SQLExec();
mySQLExec ModifyDataBase(dts, “”);
}
else if (DBSetting.GetDBType(dbName) == DBType.Odbc)
{
OdbcExec mySQLExec = new OdbcExec();
myOdbcExec ModifyDataBase(dts, “”);
}
else if (DBSetting.GetDBType(dbName) == DBType.OleDb)
{
OleDbExec mySQLExec = new OleDbExec();
myOleDbExec ModifyDataBase(dts, “”);
}
else
{
OracleExec mySQLExec = new OracleExec();
myOracleExec ModifyDataBase(dts, “”);
}
}
}
这样，在项目中只要引用这个DatabaseExecute类就可以了。
最后，要注意的几点：
1． 对于多表操作而言，因为表间有关联，所以操作的顺序很重要，本构件操作的顺序是从数据表数组的前向后处理，请千万注意表处理的顺序！
2． 默认数据库连接由配置文件中“DBCon”的设置决定，非默认数据库连接由配置文件中“*DBCon”的设置决定，其中星号代表数据库标识
3． 默认数据库类型由配置文件中“DBCon”的设置决定，非默认数据库类型由配置文件中“*DBCon”的设置决定，其中星号代表数据库标识
4． 针对每一个数据库都有两个配置，分别是数据库连接和数据库类型。
