Reputation: 838
I am using SqlDataAdapter to update my dataTable which I had queried from Database in another method and in that method there were columns, Status and Is_processed which were updated.
Now, I want these changes to be persisted (updated) in Database. Following is what have done to achieve:
Code
batchSize = 10;
string cmd;
int updatedRows;
try
{
string connString = db.ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
cmd = "UPDATE IMPORTED_ACCOUNTS SET STATUS = @Status , IS_PROCESSED = @IsProcessed " +
", CREATED_ON = @CreatedOn , CREATED_BY = @CreatedBy , UPDATED_ON = @UpdatedOn , UPDATED_BY = @UpdatedBy " +
"WHERE CONVENTIONAL_ACCOUNT = @ConAcct";
adapter.UpdateCommand = new SqlCommand(cmd, conn);
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.UpdateCommand.Parameters.AddWithValue("@Status", "Status");
adapter.UpdateCommand.Parameters.Add("@IsProcessed", SqlDbType.Bit, 1, dTable.Columns["IsProcessed"].ColumnName);
adapter.UpdateCommand.Parameters.Add("@CreatedOn",SqlDbType.DateTime,30, dTable.Columns["CreatedOn"].ColumnName);
adapter.UpdateCommand.Parameters.AddWithValue("@CreatedBy", dTable.Columns["CreatedBy"].ColumnName);
adapter.UpdateCommand.Parameters.Add("@UpdatedOn",SqlDbType.DateTime,30, dTable.Columns["UpdatedOn"].ColumnName);
adapter.UpdateCommand.Parameters.AddWithValue("@UpdatedBy", dTable.Columns["UpdatedBy"].ColumnName);
adapter.UpdateCommand.Parameters.AddWithValue("@ConAcct", dTable.Columns["ConventionalAccount"].ColumnName);
cmd = "INSERT INTO IMPORTED_ACCOUNTS ([STATUS],[IS_PROCESSED],[CREATED_ON],[CREATED_BY],[UPDATED_ON],[UPDATED_BY], CONVENTIONAL_ACCOUNT) " +
"VALUES (@Status , @IsProcessed, @CreatedOn, @CreatedBy, @UpdatedOn, @UpdatedBy, @ConAcct) ";
adapter.InsertCommand = new SqlCommand(cmd, conn);
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.InsertCommand.Parameters.AddWithValue("@Status", dTable.Columns["Status"].ColumnName);
adapter.InsertCommand.Parameters.Add("@IsProcessed", SqlDbType.Bit, 1, dTable.Columns["IsProcessed"].ColumnName);
adapter.InsertCommand.Parameters.Add("@CreatedOn", SqlDbType.DateTime, 30, dTable.Columns["CreatedOn"].ColumnName);
adapter.InsertCommand.Parameters.AddWithValue("@CreatedBy", dTable.Columns["CreatedBy"].ColumnName);
adapter.InsertCommand.Parameters.Add("@UpdatedOn", SqlDbType.DateTime, 30, dTable.Columns["UpdatedOn"].ColumnName);
adapter.InsertCommand.Parameters.AddWithValue("@UpdatedBy", dTable.Columns["UpdatedBy"].ColumnName);
adapter.InsertCommand.Parameters.Add("@ConAcct", SqlDbType.VarChar, 100, dTable.Columns["ConventionalAccount"].ColumnName);
cmd = "DELETE FROM IMPORTED_ACCOUNTS WHERE CONVENTIONAL_ACCOUNT = @ConAcct";
adapter.DeleteCommand = new SqlCommand(cmd, conn);
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.DeleteCommand.Parameters.AddWithValue("@ConAcct", dTable.Columns["ConventionalAccount"].ColumnName);
adapter.UpdateBatchSize = batchSize;
updatedRows = adapter.Update(dTable); // point where code breaks
}
return updatedRows;
}
catch (Exception ex)
{
return 0;
}
Error
It ends up with the following error in catch block:
Violation of PRIMARY KEY constraint 'PK_ACCTS'. Cannot insert duplicate key in object 'dbo.IMPORTED_ACCOUNTS'.
Comment Why is it trying to insert rows in database when they should be updated as they already exists in the Database? What are the conditions for adapter.Update(dTable) method, when it triggers Update rather Insert?
Can't figure out... Help is indeed appreciated!
Upvotes: 0
Views: 2074
Reputation: 26886
In .NET DataAdapter
makes database changes according to DataRow.RowState
property.
In the case when it has Added
value - dataadapter will try to insert records.
If you need to update records - then this property should has Modified
value.
You can't directly set values od RowState
. For example, it has been set to Added
automatically when you're adding rows to your dataTable.
Hovewer, you still can indirectly modify this value. To do this, you should call DataRow.AcceptChanges
for thouse datarows you need to update, this will set RowState
to Unchanged
. Then you should modify anything in these datarows, and in this case they will have RowState = Modified
.
Upvotes: 3
Reputation: 3874
Adapter will decide when insert, update or delete based on DataRow.RowState
of each row in the table.
See https://msdn.microsoft.com/es-es/library/system.data.datarowstate(v=vs.110).aspx for possible states.
After filling the DataTable you can call DataTable.AcceptChanges method to mark all rows as unchanged.
Also, if you have autonumeric PK, is a best practice setting that field in DataTable with these properties:
The last one ensure that if you insert new values in your datatable, the new ID values will not conflict with existing ones
Upvotes: 1