如何使用 TableAdapter 更新数据库数据集
本文关键字：数据库 数据集 更新 TableAdapter 何使用 | 更新日期: 2023-09-27 18:30:49
我正在使用Visual C# Studio 2010 express，并且一直在尝试批量更新我的表。每次我尝试更新 1 条记录时，我都会收到"并发冲突：UpdateCommand 影响了预期的 1 条记录中的 0 条"。 我在 dream in code 上问了这个问题，但我对一些诚实问题的回答有点失望。链接到我在D.I.C的问题
我采纳了最后的建议并更改了我的代码以影响数据集。或者我想是这样，但我仍然遇到并发问题。这是我更新的代码。
private void SendNewPotentialsToZoho()
{
Console.WriteLine("Trying to send potentials to Zoho");
var newZoho = potentialDatabaseDataSet.Potential.DefaultView;
var poster = new ZohoPoster(ZohoPoster.Fields.Potentials, ZohoPoster.Calls.insertRecords);
var count = 0;
//newZoho.RowFilter = "IsNull(ZohoID,'zoho') = 'zoho'";
newZoho.RowFilter = "Soid = 1234";
poster.Debugging = !UseZoho;
for (int i = 0; i < 1; i++)//newZoho.Count; i++)
{
var xmlString = Potential.GetPotentialXML(newZoho[i][1], newZoho[i][2], newZoho[i][4], newZoho[i][3], newZoho[i][5], newZoho[i][7], newZoho[i][0]);
Console.WriteLine("Sending New Records to Zoho: {0}", xmlString);
poster.PostItem.Set("xmlData", xmlString);
var result = poster.Post(3);
Console.WriteLine(result);
if (!string.IsNullOrEmpty(result))
{
try
{
var rowLength = newZoho[i].Row.ItemArray.Length;
var rowOffset = Math.Abs((int)newZoho[i][rowLength - 1])-1;
potentialDatabaseDataSet.Potential.Rows[rowOffset]["ZohoID"] = ReadResult(result);
potentialTableAdapter.Update(potentialDatabaseDataSet.Potential.Rows[rowOffset]);
}
catch (Exception ex)
{
Console.WriteLine("Failed to update: {0}", ex.Message);
}
}
}
}
称为海报的位变量效果很好。 它返回一个类似 xml 的结果，其中包含我的 zohoID，我解析并尝试存储该结果。出于测试目的，我尝试只更新一条记录。我收到 potentialTableAdapter.Update（potentialDatabaseDataSet） 的错误。对我来说奇怪的是，我使用非常相似的代码来制作一张全新的唱片，而且效果很好。事实上，这就是我使用 Soid = 1234 进行行的方式。我是唯一可以访问该程序的人，据我所知它不是多线程的，所以我只是不明白为什么它有并发问题。请帮忙:)
编辑
好的，所以我在玩弄它，发现如果我向其添加 EndEdit，我不会遇到并发问题。另一方面，虽然我的绑定数据网格视图显示了更新的数据，但数据实际上并没有更新。所以这并不是说我回到了原点，我认为我实际上已经相当接近了。我将从内存中获取这一小段代码，所以如果它不正确，请不要讨厌。这主要是为了了解我在说什么
for (int i = 0; i < 5; i++) //emailRecord.Count; i++)
{
if (ZohoEmail.EmailExpectedShipping(emailRecord[i], "12/10/2012"))
{//true means that the email went through just fine
try
{
var rowLengh = emailRecord[i].Row.ItemArray.Length;
var rowOffset = Math.Abs((int)emailRecord[i][rowLengh - 1]) - 1;
potentialDatabaseDataSet.Potential.Rows[rowOffset][17] = true; //17 is Expected Email
potentialDatabaseDataSet.Potential.Rows[rowOffset].AcceptChanges();
}
catch (Exception ex)
{
Console.WriteLine("Failed to update: {0}", ex.Message);
}
}
potentialTableAdapter.Update(potentialDatabaseDataSet.Potential);
}
}
不能只更新一行。数据适配器允许更新表，而不是行。所以而不是
potentialTableAdapter.Update(potentialDatabaseDataSet.Potential.Rows[rowOffset]);
用
potentialTableAdapter.Update(potentialDatabaseDataSet.Potential);
编辑
我不太确定你在这里要实现什么：
var rowLength = newZoho[i].Row.ItemArray.Length;
var rowOffset = Math.Abs((int)newZoho[i][rowLength - 1])-1;
看起来您首先获得列数，最好是：
var rowLength = potentialDatabaseDataSet.Potential.Columns.Count;
然后，根据最后一列中的值计算行号，并在值之前获取第一行。这就是我不明白为什么？此外，您还根据视图计算行偏移量。
为什么你只是不使用：
newZoho[i].Row["ZohoID"] = ReadResult(result);
potentialTableAdapter.Update(potentialDatabaseDataSet.Potential);
而不是计算您应该更新哪一行？
我相信 ZohoID 是更新前存在于数据库中的外键？
编辑 2
我认为问题出在您的自动表格适配器上。首先尝试以强类型方式更新行：
((PotentialRow)newZoho[i].Row).ZohoID = ReadResult(result);
并尝试修改数据集适配器的更新查询。自动数据适配器生成非常丑陋的查询。将更新查询中的 where 子句更改为仅比较行主键 （ID） 现在所有值。
嗯，
我想我想通了。非常感谢Marcin在我的这个问题上帮助了我这么多。您对我生成的表适配器中的问题是正确的。我最终根据自动生成的代码制作了自己的代码。这是我的代码，以防其他人在使用自动生成的代码时遇到问题。
using System;
using System.Data.SqlServerCe;
using System.Data.Common;
using System.Data;
namespace Zoho
{
public partial class PotentialTableAdapter
{
SqlCeDataAdapter _adapter;
SqlCeConnection _connection;
SqlCeTransaction _transaction;
SqlCeCommand[] _commandCollection;
DataTable _table;
public PotentialTableAdapter()
{
ClearBeforeFill = true;
InitConnection();
InitAdapter();
InitCommandCollection();
FillTable();
}
public bool ClearBeforeFill {get; set;}
public SqlCeDataAdapter Adapter
{
get
{
if ((this._adapter == null))
{
this.InitAdapter();
}
return this._adapter;
}
}
public SqlCeConnection Connection
{
get
{
if (_connection == null)
InitConnection();
return _connection;
}
}
public SqlCeTransaction Transaction
{
get
{
return _transaction;
}
set
{
_transaction = value;
for (int i = 0; (i < CommandCollection.Length); i = (i + 1))
{
CommandCollection[i].Transaction = value;
}
Adapter.DeleteCommand.Transaction = value;
Adapter.InsertCommand.Transaction = value;
Adapter.UpdateCommand.Transaction = value;
}
}
public SqlCeCommand[] CommandCollection
{
get
{
if ((this._commandCollection == null))
{
InitCommandCollection();
}
return this._commandCollection;
}
}
public DataTable Table
{
get
{
if (_table == null)
FillTable();
return _table;
}
}
}
}
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data;
namespace Zoho
{
partial class PotentialTableAdapter
{
private void InitAdapter()
{
this._adapter = new SqlCeDataAdapter();
this._adapter.TableMappings.Add(GetTableMapping());
this._adapter.SelectCommand = new SqlCeCommand("SELECT * FROM Potential", Connection);
this._adapter.InsertCommand = GetCommand(@"INSERT INTO [Potential] ([Soid], [SalesRep], [Account], [ClosingDate], [Amount], [Stage], [SourceId], [Product], [FirstName], [Email], [CustomerPO], [ZohoID], [WorkOrder], [ExpectedShip], [TrackingNumber], [DependencyID], [ProcessEmail], [ExpectedEmail], [ShippedEmail]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19)");
this._adapter.UpdateCommand = GetCommand(@"UPDATE [Potential] SET [Soid] = @p1, [SalesRep] = @p2, [Account] = @p3, [ClosingDate] = @p4, [Amount] = @p5, [Stage] = @p6, [SourceId] = @p7, [Product] = @p8, [FirstName] = @p9, [Email] = @p10, [CustomerPO] = @p11, [ZohoID] = @p12, [WorkOrder] = @p13, [ExpectedShip] = @p14, [TrackingNumber] = @p15, [DependencyID] = @p16, [ProcessEmail] = @p17, [ExpectedEmail] = @p18, [ShippedEmail] = @p19 WHERE (([NCPotentialKey] = @p20))");
}
private void InitConnection()
{
this._connection = new SqlCeConnection(Zoho.Properties.Settings.Default.PotentialDatabaseConnectionString);
}
private void InitCommandCollection()
{
_commandCollection = new SqlCeCommand[1];
_commandCollection[0] = new SqlCeCommand("SELECT * FROM Potential", Connection);
}
private void FillTable()
{
_table = new DataTable();
Adapter.Fill(_table);
}
private DataTableMapping GetTableMapping()
{
var tableMapping = new DataTableMapping();
tableMapping.SourceTable = "Table";
tableMapping.DataSetTable = "Potential";
tableMapping.ColumnMappings.Add("Soid", "Soid");
tableMapping.ColumnMappings.Add("SalesRep", "SalesRep");
tableMapping.ColumnMappings.Add("Account", "Account");
tableMapping.ColumnMappings.Add("ClosingDate", "ClosingDate");
tableMapping.ColumnMappings.Add("Amount", "Amount");
tableMapping.ColumnMappings.Add("Stage", "Stage");
tableMapping.ColumnMappings.Add("SourceId", "SourceId");
tableMapping.ColumnMappings.Add("Product", "Product");
tableMapping.ColumnMappings.Add("FirstName", "FirstName");
tableMapping.ColumnMappings.Add("Email", "Email");
tableMapping.ColumnMappings.Add("CustomerPO", "CustomerPO");
tableMapping.ColumnMappings.Add("ZohoID", "ZohoID");
tableMapping.ColumnMappings.Add("WorkOrder", "WorkOrder");
tableMapping.ColumnMappings.Add("ExpectedShip", "ExpectedShip");
tableMapping.ColumnMappings.Add("TrackingNumber", "TrackingNumber");
tableMapping.ColumnMappings.Add("DependencyID", "DependencyID");
tableMapping.ColumnMappings.Add("ProcessEmail", "ProcessEmail");
tableMapping.ColumnMappings.Add("ExpectedEmail", "ExpectedEmail");
tableMapping.ColumnMappings.Add("ShippedEmail", "ShippedEmail");
tableMapping.ColumnMappings.Add("NCPotentialKey", "NCPotentialKey1");
return tableMapping;
}
private SqlCeCommand GetDeleteCommand()
{
var deleteCommand = new SqlCeCommand();
deleteCommand.Connection = this.Connection;
deleteCommand.CommandText = "DELETE FROM [Potential] WHERE (([NCPotentialKey] = @p1))";
deleteCommand.CommandType = CommandType.Text;
var parameter = new SqlCeParameter("@p1", SqlDbType.Int, 0, ParameterDirection.Input, false, 0, 0, "NCPotentialKey", DataRowVersion.Original, null);
deleteCommand.Parameters.Add(parameter);
return deleteCommand;
}
private SqlCeCommand GetCommand(string text)
{
var command = new SqlCeCommand(text);
command.Connection = this.Connection;
command.Parameters.Add(new SqlCeParameter("@p1", SqlDbType.Int, 0, ParameterDirection.Input, true, 0, 0, "Soid", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p2", SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "SalesRep", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p3", SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "Account", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p4", SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "ClosingDate", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p5", SqlDbType.Money, 0, ParameterDirection.Input, true, 0, 0, "Amount", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p6", SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "Stage", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p7", SqlDbType.Int, 0, ParameterDirection.Input, true, 0, 0, "SourceId", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p8", SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "Product", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p9", SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "FirstName", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p10",SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "Email", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p11",SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "CustomerPO", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p12",SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "ZohoID", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p13",SqlDbType.Int, 0, ParameterDirection.Input, true, 0, 0, "WorkOrder", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p14",SqlDbType.DateTime, 0, ParameterDirection.Input, true, 0, 0, "ExpectedShip", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p15",SqlDbType.NVarChar, 0, ParameterDirection.Input, true, 0, 0, "TrackingNumber",DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p16",SqlDbType.Int, 0, ParameterDirection.Input, true, 0, 0, "DependencyID", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p17",SqlDbType.Bit, 0, ParameterDirection.Input, true, 0, 0, "ProcessEmail", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p18",SqlDbType.Bit, 0, ParameterDirection.Input, true, 0, 0, "ExpectedEmail", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p19",SqlDbType.Bit, 0, ParameterDirection.Input, true, 0, 0, "ShippedEmail", DataRowVersion.Current, null));
command.Parameters.Add(new SqlCeParameter("@p20",SqlDbType.Int, 0, ParameterDirection.Input, true, 0, 0, "NCPotentialKey",DataRowVersion.Original, null));
return command;
}
}
}
现在是我的包装器类。我发现这样做非常有用。 使用系统; 使用 System.Data.SqlServerCe; 使用系统数据;
namespace Zoho
{
public class DatabaseConnection
{
PotentialTableAdapter table;
public DatabaseConnection()
{
table = new PotentialTableAdapter();
}
public DataTable Table
{
get
{
return table.Table;
}
}
public void Update()
{
try
{
Console.Write("Attemping to update database: ");
table.Adapter.Update(Table);
Console.WriteLine("Success");
}
catch (Exception ex)
{
Console.WriteLine("Fail: {0}", ex.Message);
}
}
}
}
最后是真正重要的代码片段。马尔钦帮我弄清楚的那个。实际上这非常容易。
DatabaseConnection AllPotentials = new DatabaseConnection();
var newZoho = AllPotentials.Table.DefaultView;
var poster = new ZohoPoster(ZohoPoster.Fields.Potentials, zohoPoster.Calls.insertRecords);
newZoho.RowFilter = "IsNull(ZohoID,'zoho') = 'zoho'";
poster.Debugging = !UseZoho; //UseZoho= false which turns debugging on
for (int i = 0; i < 1; i++)//newZoho.Count; i++)
{
var xmlString = Potential.GetPotentialXML(newZoho[i]);
Console.WriteLine("Sending New Records to Zoho: {0}", xmlString);
poster.PostItem.Set("xmlData", xmlString);
var result = poster.Post(3);
Console.WriteLine(result);
if (!string.IsNullOrEmpty(result))
{
try
{
newZoho[i].Row["ZohoID"] = ReadResult(result);
}
catch (Exception ex)
{
Console.WriteLine("Failed to update: {0}", ex.Message);
}
}
}
try
{
Console.Write("Trying to update the database after getting zoho: ");
AllPotentials.Update();
Console.WriteLine("Success");
}
catch (Exception ex)
{
Console.WriteLine("Failed: {0}", ex.Message);
}
我试了一下，它说成功了，所以我在不同的程序中打开了数据库，然后低头看我的假数据中有 zoho id，:)我太兴奋了。现在我实际上可以启动并运行这个程序。我现在遇到的唯一问题是，在我的 datagridview 中，它只显示 4 或 5 条记录而不是 201 条记录（我只是想了想，我敢打赌过滤器与它有关！现在是时候完成我的程序了！:)再次感谢马尔钦的耐心和帮助我。既然你告诉我问题出在我的表适配器上，我将把问题标记为你回答。