110,568
社区成员
发帖
与我相关
我的任务
分享
我使用Adapter.Update直接把DataTable里的记录插入到数据库里,但如果DataTable里的主键和数据库原来的记录主键重复,就会出错,后面所有的记录都不能插入了,能不能做到:
排除主键重复的记录,其他不重复的记录仍然可以插入到数据库里
/// <summary>
/// 大批量添加数据ds.Tables[0] 表名strTblName(数据库表名称)
/// </summary>
/// <param name="ds"></param>
/// <param name="strTblName"></param>
/// <returns>受影响的行数</returns>
public int BulkInsert(DataTable dt, string strTblName)
{
int affect = 0;
try
{
using (OleDbConnection conn = new OleDbConnection(AccessHelper.ConnectionStringLocalTransaction))
{
OleDbCommand myCommand = new OleDbCommand("select top 1 * from " + strTblName, conn);
OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter);
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
//foreach (DataRow dr in dt.Rows)
//{
// if (dr.RowState != DataRowState.Added)
// dr.SetAdded();
//}
conn.Open();
affect = myAdapter.Update(dt);
}
}
catch
{
return 0;
}
return affect;
}
foreach (DataRow dr in dt.Rows)
{
if (dr.RowState != DataRowState.Added)
try
{
dr.SetAdded();
}
catch
{
}
}