CODEWITHSUNDEEP
c#.netsql-serverado.net
Sadiq
Sadiq

Reputation: 838

SqlDataAdapter not updating rows in Database

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

Answers (2)

Andrey Korneyev
Andrey Korneyev

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

mnieto
mnieto

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:

  • Autoincrement: true
  • AutoIncrementSeed: -1

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

Related Questions