C#: Update dataset without any primary key

Question

Is it possible in C# to use an OleDbAdapter and use its Update method for a dataset when a table has no primary key and how can I do this?

Answer 1

Here's an example in VB, which might give you a general idea.

Answer 2

You can always find a row using myTable.Select("myColumn Like 'unique value'");

Answer 3

Can you add a primary key?

Without some kind of unique ID (which should be the PK) then there is no way of knowing what row to update.

Answer 4

I got the same issue when I used a third-party database. They don't have primary keys in their database, but they do have a combination of fields that are unique.

First, create a datatable, and fill it with the result of the query using the unique condition.

DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(sqlstring, ACCESS_dbConnection);
adapter.Fill(dt);

Second, replace ds with your updated data by column.

foreach (DataColumn col in datarow.Columns)
{
    Debug.WriteLine(col.ColumnName);
    ds.Rows[0][col.ColumnName] = datarow.Rows[0][col.ColumnName];
}

Then, you can update.

OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.InsertCommand = cb.GetInsertCommand();
adapter.Update(ds);

This works for me.

Only do it when you are sure there is one at only one query data. If not, you may want to add some "if" checking