If I use a command that contains field name aliases, I get a different
result for _cmdBldr.GetUpdateCommand().CommandText, depending on
whether I am connected to SQL Server 2005 or Oracle 10g.
Here is the code in question:
string _sqlString = "SELECT ID, PLANGROUP AS PG, DESC_R FROM TABLEA
WHERE ID = 100002";
OdbcConnection _dc = new OdbcConnection();
_dc.ConnectionString = "<connectionstring>";
_dc.Open();
OdbcDataAdapter _da = new OdbcDataAdapter(_sqlString, _dc);
DataTable _dt = new DataTable();
_da.Fill(_dt);
_dt.Rows[0].BeginEdit();
_dt.Rows[0]["DESC_R"] = "ABC";
_dt.Rows[0].EndEdit();
OdbcCommandBuilder _cmdBldr = new OdbcCommandBuilder(_da);
MessageBox.Show(_cmdBldr.GetUpdateCommand().CommandText);
try
{
_da.Update(_dt);
}
catch(Exception ex)
{
}
When I run this for a SQL Server connection, the _da.Update(_dt);
command executes without an exception and
_cmdBldr.GetUpdateCommand().CommandText returns:
"UPDATE TABLEA SET ID = ?, PLANGROUP = ?, DESC_R = ? WHERE ((ID = ?)
AND ((? = 1 AND PLANGROUP IS NULL) OR (PLANGROUP = ?)) AND ((? = 1 AND
DESC_R IS NULL) OR (DESC_R = ?)))"
When I run this for an Oracle connection the _da.Update(_dt); command
generates an exception:
"ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "PG": invalid identifier"
and _cmdBldr.GetUpdateCommand().CommandText returns:
"UPDATE TABLEA SET ID = ?, PG = ?, DESC_R = ? WHERE ((ID = ?) AND ((?
= 1 AND PG IS NULL) OR (PG = ?)) AND ((? = 1 AND DESC_R IS NULL) OR
(DESC_R = ?)))"
Note that in SQL Server, the original field name "PLANGROUP" is used
in the UpdateCommand. In Oracle, the field name alias "PG" is used.
Is there a way to force the command builder to create an update
command with the orginal field name when using Oracle? Is there
another approach I should be using, other than always creating my own
update command from scratch for Oracle?
Thanks.