Hi,
I've been using the oracle provider and I discovered that when I use the CommandBuilder with an Adapter that contains a select statement like this:
Code: Select all
Select COLUMN2 From TABLE1 where COLUMN1 = Value
Where COLUMN1 is the Primary Key of the Table, and the Column2 is just another column in the table (Not a Primary key or a Foreign Key), then the Generated UpdateCommand is something like this:
Code: Select all
UPDATE TABLE1
SET COLUMN2 = :p1
WHERE ((COLUMN2 = :p2))
But as you can see this is not the command I was expecting, because it is going to update all the records that have the same value that the record I was trying to update. For example if the COLUMN2 in the record I'm trying to update has the value 1, and I'm going to change it 2, then all the records wit a 1 value in the COLUMN2 is going to be changed to 2.
This is not a desired behavior at all and it is dangerous because it can be updating thousands of records without any warning. This is wrong, and might be causing several bugs that anyone will notice.
You can easily replicate the problem with the folowing code:
Code: Select all
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
DataSet ds = new DataSet();
OracleCommand command = new OracleCommand("Select COLUMN2 From TABLE1 where COLUMN1 = Value", connection);
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
OracleCommand UpdateCommand = builder.GetUpdateCommand();
}
We are using at this moment the .NET's Oracle Provider and we are trying to move to this one. If I use the .NET Oracle provider when I call the GetUpdateCommand with that Select Statment, then I get an exception ("Dynamic SQL generation failed. No key information found"), and it doesn't matter, because we have code to manage that. The problem is that we have thousands of queries and potentially this is happening in some of them, this is a hard issue to track and for this getting the exception is the right behavior.