56,677
社区成员
发帖
与我相关
我的任务
分享
public bool UpdateDataSet(DataSet dsOri, List<string> listSql, UpdateMode mode = UpdateMode.Normal)
{
this.ErrInfo = "";
bool result = true;
DataSet dsDes = new DataSet();
try
{
DbConnection cnn = this.CurDBCnn;
OpenCnn();
DbCommand cmd = this.GetDBCmd(cnn);
DbTransaction trans = this.GetDBTrans(cnn);
cmd.Transaction = trans;
cmd.CommandText = string.Join(";", listSql);
DbDataAdapter adpt = this.GetDataAdpt(cmd);
adpt.SelectCommand = cmd;
DbCommandBuilder builder = this.GetDBCmdBuilder(adpt);//注意这个地方必须有,虽然没用到,要不会报错
adpt.MissingSchemaAction = MissingSchemaAction.AddWithKey;//带主键填充
adpt.Fill(dsDes);//填充数据
int i = 0;
try
{
foreach (DataTable dtOri in dsOri.Tables)
{
DataTable dtDes = dsDes.Tables[i];
if (dtDes.PrimaryKey.Length == 0)//无主键
{
this.ErrInfo = "SQL中必须带有主键列";
return false;
}
List<string> keyList = new List<string>();
foreach (DataColumn column in dtDes.PrimaryKey)
keyList.Add(column.ColumnName);
switch (mode)
{
case UpdateMode.Normal:
foreach (DataRow drOri in dtOri.Rows)
{
//获取主键
string strKeyCtrs = GetKeyCstrs(drOri, keyList);
if (drOri.RowState == DataRowState.Added)//插入
{
DataRow[] arrRowAdd = dtDes.Select(strKeyCtrs);
if (arrRowAdd.Length == 0)//找不到记录
{
DataRow drNew = dtDes.NewRow();
foreach (DataColumn column in dtDes.Columns)
{
string colName = column.ColumnName;
if (dtOri.Columns.Contains(colName))
drNew[colName] = drOri[colName];
}
dtDes.Rows.Add(drNew);
}
}
if (drOri.RowState == DataRowState.Modified)//更新
{
DataRow[] arrRowUpdate = dtDes.Select(strKeyCtrs);
if (arrRowUpdate.Length > 0)//能找到记录
{
DataRow _drDes = arrRowUpdate[0];
foreach (DataColumn column in dtDes.Columns)
{
string colName = column.ColumnName;
if (dtOri.Columns.Contains(colName))
_drDes[colName] = drOri[colName];
}
}
}
if (drOri.RowState == DataRowState.Deleted)//删除
{
DataRow[] arrRowDelete = dtDes.Select(strKeyCtrs);
if (arrRowDelete.Length > 0)//能找到记录
{
arrRowDelete[0].Delete();
}
}
}
break;
case UpdateMode.Incre://增量
foreach (DataRow drOri in dtOri.Rows)
{
//获取主键
string strKeyCtrs = GetKeyCstrs(drOri, keyList);
DataRow[] arrRow = dtDes.Select(strKeyCtrs);
if (arrRow.Length == 0)//找不到记录---增加
{
DataRow drNew = dtDes.NewRow();
foreach (DataColumn column in dtDes.Columns)
{
string colName = column.ColumnName;
if (dtOri.Columns.Contains(colName))
drNew[colName] = drOri[colName];
}
dtDes.Rows.Add(drNew);
}
else//更新
{
DataRow _drDes = arrRow[0];
foreach (DataColumn column in dtDes.Columns)
{
string colName = column.ColumnName;
if (dtOri.Columns.Contains(colName))
_drDes[colName] = drOri[colName];
}
}
}
break;
case UpdateMode.FIncre://全增量
foreach (DataRow drOri in dtOri.Rows)
{
DataRow drNew = dtDes.NewRow();
foreach (DataColumn column in dtDes.Columns)
{
string colName = column.ColumnName;
if (dtOri.Columns.Contains(colName))
drNew[colName] = drOri[colName];
}
dtDes.Rows.Add(drNew);
}
break;
}
i++;
}
adpt.Update(dsDes);//这个地方更新数据集后,关闭数据库连接 接着其它查询 就完蛋了
if (!this._UseTransaction)
trans.Commit();
}
catch (Exception ex)
{
if (!this._UseTransaction)
trans.Rollback();
result = false;
this.ErrInfo = ex.Message;
}
adpt.Dispose();
trans.Dispose();
builder.Dispose();
cmd.Dispose();
CloseCnn();
}
catch (Exception ex)
{
result = false;
this.ErrInfo = ex.Message;
}
return result;
}
string sql = "SELECT T.*,f_getTypeName(T.TYPE) TYPENAME FROM XM_BASIC T WHERE ID='" + xmid + "' AND ifnull(DELFLAG,'0')<>'1'";
DataTable dtBasic = dal.GetDataTable(sql);//getdatatable也是用完连接再关闭的