Dynamic SQL generation - How do I set the key column information manually?

[Rune B]

I use in my datalayer the following methods to fill and update data, and it
works like a charm.

But recently I had to add a dimension to the SqlServer table, - a new field
that allowed more than one instance of the same customer (same customerID)
in the table. And of cause that meant I had to remove the primarykey from
the "CustomerID" column at SqlTable.

So when I filled the table, the dimension "AppID" was always applied,
"SELECT CustomerID, CompanyName FROM Customers WHERE (AppID = 2)";
so in fact - in every Query the CustomerID was still unique, allowing me to
use the below code... to fill the table anyway.

When I try to update using the below UpdateCustomersData() with "WHERE
(AppID = 2)" added to the CommandText I get the error:

"Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information."


- The Question:
How do I manually specify to the key column information the DataAdapter or
CommandBuilder needs?




//---------------- code -----------------

private DbProviderFactory _providerFactory;
private DbConnection _connection;

public void FillCustomersData(DataTable customersTable)
{
DbCommand command = _providerFactory.CreateCommand();
command.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
command.Connection = this._connection;

DbDataAdapter adapter = _providerFactory.CreateDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(customersTable);
customersTable.PrimaryKey = new DataColumn[1] {
customersTable.Columns["CustomerID"] }; ;
}

public void UpdateCustomersData(DataTable customersTable)
{

DbCommand command = _providerFactory.CreateCommand();
command.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
command.Connection = this._connection;

DbDataAdapter adapter = _providerFactory.CreateDataAdapter();
adapter.SelectCommand = command;

DbCommandBuilder commandBuilder = _providerFactory.CreateCommandBuilder();
commandBuilder.DataAdapter = adapter;
commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
commandBuilder.SetAllValues = false;

adapter.Update(customersTable);

}

//-----------------------------------

 

[Guest]

Rune,

Your Customers table needs a primary key.

You need to identify the column or columns that make each row unique and
mark that column or combination of columns as the primary key.

If you have a table without a primary key in a relational database you have
much bigger problems than not being able to update the table through ADO.Net.

Kerry Moorman

 

[Rune B]

You need to identify the column or columns that make each row unique and

mark that column or combination of columns as the primary key.

[plonk] ... *combination of columns* ... I've been coding datadriven apps
for the last 8 years, and I never realized you could define a primary key as
a combination of columns.

Problem solved - thanks a bunch.

R-)

That's kind of scary ... how much other quite obvious stuff do I still not
know ;-)