对于这种情况,DataSet是一个很好的选择,在效率上可能会有些影响,只要数据量不是海量,我想在效率上不是什么大问题。通过扩展DataSet,我们可以往DataSet中添加几个表,对数据进行修改之后,一次上传到数据库
例如:扩展的DataSet为:CustomDataSet
DS = new CustomDataSet("table1","");
DS.AddDataTable("table2","");
DataRow dr = DS.GetNewRow("table1",true);
dr[1] = "ha";
dr = DS.GetNewRow("table2",true);
dr[1] = "ha";
DS.UpData();
扩展的DataSet如下:
///
/// 自定义DataSet,封装对数据库的操作
///
public class CustomDataSet:DataSet
{
private SQLServer sqlServer;
private ArrayList tableNames;
private ArrayList sqlDataAdapters;
public CustomDataSet()
{
Init();
}
///
/// 对象实例化
///
/// 表名
/// 条件
public CustomDataSet(string TableName,string Condition)
{
Init();
AddDataTable(TableName,Condition);
}
private void Init()
{
tableNames = new ArrayList();
sqlDataAdapters = new ArrayList();
sqlServer = SQLServer.Instance();
}
///
/// 添加一个数据表
///
/// 表名
/// 条件
public void AddDataTable(string TableName,string Condition)
{
string SQLString = "SELECT * FROM " + TableName ;
if(Condition != null && Condition != "" && Condition.Trim() != "")
SQLString += " WHERE " + Condition;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(SQLString,sqlServer.Connection);
SqlCommandBuilder custCB = new SqlCommandBuilder(sqlDataAdapter);
sqlServer.Connection.Open();
try
{
sqlDataAdapter.FillSchema(this,SchemaType.Source,TableName);
sqlDataAdapter.Fill(this,TableName);
}
finally
{
sqlServer.Connection.Close();
}
tableNames.Add(TableName);
sqlDataAdapters.Add(sqlDataAdapter);
}
///
/// 提交
///
public void UpData()
{
UpDataToDataBase(false);
}
///
/// 提交
///
public void UpData(bool FlagOrder)
{
UpDataToDataBase(FlagOrder);
}
private void UpDataToDataBase(bool FlagOrder)
{
try
{
sqlServer.Connection.Open();
sqlServer.BeginTransaction();
if(FlagOrder)
{
for(int i = tableNames.Count-1;i>=0;i--)
{
SqlDataAdapter sqlDataAdapter = (SqlDataAdapter)sqlDataAdapters[i];
sqlDataAdapter.SelectCommand.Transaction = sqlServer.SqlTran;
sqlDataAdapter.Update(this.Tables[i]);
}
}
else
{
for(int i = 0;i
{
SqlDataAdapter sqlDataAdapter = (SqlDataAdapter)sqlDataAdapters[i];
sqlDataAdapter.SelectCommand.Transaction = sqlServer.SqlTran;
sqlDataAdapter.Update(this.Tables[i]);
}
}
sqlServer.CommitTransaction();
}
catch
{
sqlServer.RollbackTransaction();
throw;
}
finally
{
sqlServer.Connection.Close();
}
}
///
/// 获取新行
///
/// 表名
/// 标志位,True表示新行加入到表中,False反之
/// 新行的引用
public DataRow GetNewRow(string TableName,bool FlagAdd)
{
DataRow dr = this.Tables[TableName].NewRow();
if(FlagAdd)
this.Tables[TableName].Rows.Add(dr);
return dr;
}
///
/// 获取新行
///
/// 表索引
/// 标志位,True表示新行加入到表中,False反之
/// 新行的引用
public DataRow GetNewRow(int Index,bool FlagAdd)
{
DataRow dr = this.Tables[Index].NewRow();
if(FlagAdd)
this.Tables[Index].Rows.Add(dr);
return dr;
}
///
/// 添加新行
///
/// 表名
/// 新行
public void AddNewRow(string TableName,DataRow dr)
{
this.Tables[TableName].Rows.Add(dr);
}
///
/// 添加新行
///
/// 表索引
/// 新行
public void AddNewRow(int Index,DataRow dr)
{
this.Tables[Index].Rows.Add(dr);
}
///
/// 获取一行
///
/// 表名
/// 索引
/// 行的引用
public DataRow GetRow(string TableName,int Index)
{
return this.Tables[TableName].Rows[Index];
}
///
/// 获取一行
///
/// 表索引
/// 行索引
/// 行的引用
public DataRow GetRow(int TIndex,int RIndex)
{
return this.Tables[TIndex].Rows[RIndex];
}
///
/// 获取行集
///
/// 表名
/// 条件
/// 行集
public DataRow[] GetRows(string TableName,string Condition)
{
return this.Tables[TableName].Select(Condition,null,DataViewRowState.CurrentRows);
}
///
/// 获取行集
///
/// 表索引
/// 条件
/// 行集
public DataRow[] GetRows(int Index,string Condition)
{
return this.Tables[Index].Select(Condition,null,DataViewRowState.CurrentRows);
}
///
/// 删除一行
///
/// 表名
/// 索引
public void DeleteRow(string TableName,int Index)
{
this.Tables[TableName].Rows[Index].Delete();
}
///
/// 删除一行
///
/// 表索引
/// 行索引
public void DeleteRow(int TIndex,int RIndex)
{
this.Tables[TIndex].Rows[RIndex].Delete();
}
///
/// 删除行集
///
/// 表名
/// 条件
public void DeleteRows(string TableName,string Condition)
{
foreach(DataRow dr in GetRows(TableName,Condition))
dr.Delete();
}
///
/// 删除行集
///
/// 表索引
/// 条件
public void DeleteRows(int Index,string Condition)
{
foreach(DataRow dr in GetRows(Index,Condition))
dr.Delete();
}
///
/// 删除表中所有记录
///
/// 表名
public void DeleteDataTable(string TableName)
{
foreach(DataRow dr in this.Tables[TableName].Select(null,null,DataViewRowState.CurrentRows))
dr.Delete();
}
///
/// 删除表中所有记录
///
/// 表索引
public void DeleteDataTable(int Index)
{
foreach(DataRow dr in this.Tables[Index].Select(null,null,DataViewRowState.CurrentRows))
dr.Delete();
}
///
/// 删除所有表
///
public void DeleteAll()
{
for(int i=0;i
{
foreach(DataRow dr in this.Tables[i].Select(null,null,DataViewRowState.CurrentRows))
dr.Delete();
}
}
///
/// 设置主键
///
/// 表名
/// 键
public void SetPrimaryKey(string TableName,params string[] Keys)
{
DataColumn [] dc = new DataColumn[Keys.Length];
for(int i=0;i
{
dc[i] = this.Tables[TableName].Columns[Keys[i]];
}
this.Tables[TableName].PrimaryKey = dc;
}
///
/// 设置主键
///
/// 表索引
/// 键
public void SetPrimaryKey(int Index,params string[] Keys)
{
DataColumn [] dc = new DataColumn[Keys.Length];
for(int i=0;i
{
dc[i] = this.Tables[Index].Columns[Keys[i]];
}
this.Tables[Index].PrimaryKey = dc;
}
}