这个是批量上传一个DataTable的方法,如果导入的数据存在就更新,如果不存在就是插入,但是如果我传入的DataTable与查询出来的数据集合比较不存在如何删除呢?
例如我传入的DataTable里面有3条数据.而数据库查询出来的有4条,那么多出的那条如何删除呢?
这里写了dbAdapter.DeleteCommand = autoCmds.GetDeleteCommand();如何让他使用起来public bool UpdateDataTable(string strSql, DataTable dataTable)
{
DataTable dtTemp = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
SqlDataAdapter dbAdapter = new SqlDataAdapter(strSql, connection);
dbAdapter.FillSchema(dtTemp, SchemaType.Mapped); dbAdapter.Fill(tempTable);
dtTemp.Merge(dataTable);
SqlCommandBuilder autoCmds = new SqlCommandBuilder(dbAdapter);
dbAdapter.InsertCommand = autoCmds.GetInsertCommand();
dbAdapter.UpdateCommand = autoCmds.GetUpdateCommand();
dbAdapter.DeleteCommand = autoCmds.GetDeleteCommand(); dbAdapter.Update(tempTable);
return true;
}
catch (Exception e)
{
e.ToString();
}
finally
{
conn.Close();
}
}
}
例如我传入的DataTable里面有3条数据.而数据库查询出来的有4条,那么多出的那条如何删除呢?
这里写了dbAdapter.DeleteCommand = autoCmds.GetDeleteCommand();如何让他使用起来public bool UpdateDataTable(string strSql, DataTable dataTable)
{
DataTable dtTemp = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
SqlDataAdapter dbAdapter = new SqlDataAdapter(strSql, connection);
dbAdapter.FillSchema(dtTemp, SchemaType.Mapped); dbAdapter.Fill(tempTable);
dtTemp.Merge(dataTable);
SqlCommandBuilder autoCmds = new SqlCommandBuilder(dbAdapter);
dbAdapter.InsertCommand = autoCmds.GetInsertCommand();
dbAdapter.UpdateCommand = autoCmds.GetUpdateCommand();
dbAdapter.DeleteCommand = autoCmds.GetDeleteCommand(); dbAdapter.Update(tempTable);
return true;
}
catch (Exception e)
{
e.ToString();
}
finally
{
conn.Close();
}
}
}
一个存储过程搞定,参数为XML
将table里面的数据拼成XML
例如 参数:
<Root>
<Pri StartDate=\"{0}\" ExpirationDate=\"{1}\" />
<Pri StartDate=\"{0}\" ExpirationDate=\"{1}\" />
</Root>存储过程
解析XML
将数据插入到 新建临时表,然后再操作临时表想怎么操作怎么操作。