Well, I found .NET docs quite unclear on this issue. The docs even provide code samples which I believe won't work.
Using MSDN (from January 2003) I found the following.
The docs for System.Data.IDataAdapter.Update and System.Data.Common.DataAdapter.Update both say:
> Calls the respective INSERT, UPDATE, or DELETE statements for each > inserted, updated, or deleted row in the specified DataSet from a > DataTable named "Table".
However the docs for System.Data.Common.DataAdapter.Update (DataSet) says:
> Calls the respective INSERT, UPDATE, or DELETE statements for each > inserted, updated, or deleted row in the specified DataSet.
As you can see in this case the "from a DataTable named "Table"" part is missing. I think that this is simply a documentation bug in the MS docs.
We can use some elementary logic to make this point. A DataAdapter
can actually perform an update only if it has its UpdateCommand,
DeleteCommand, and InsertCommand properties set to some reasonable
SQL statements that will do the necessary actions. Alternatively
it can be associated with a CommandBuilder object. I think it's
clear that an SQL update statement (like
"update t set s = ? where id = ?") can update only one table.
If you execute such command on a DataTable filled with different DataAdapter from a different database table the result will be
unpredictable.
If this is not enough I wrote a simple program that shows how this works. I've tested it only with MS .NET framework though, not with Mono. It's source and output is attached.
Regards, Aleksey
Alan Tam wrote:
May I know where is it stated that one DataAdapter updates only one table? I also believe so, but I found the code very strange, including trying to find the DataTable associated to each schemaRow. I'm totally confused.
Regards, Alan
----- Original Message ----- From: "Aleksey Demakov" <[EMAIL PROTECTED]> To: "Alan Tam" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, February 25, 2003 5:24 PM Subject: Re: [Mono-list] DbDataAdapter.Fill patch
So what's wrong with it? I think that a DataAdapter at one time should update only one table. It's absolutely wrong to iterate through all the tables in the DataSet because the DataAdapter contains only one set of update commands while different tables require different commands.
Regards, Aleksey
Alan Tam wrote:
The bottom half of the patch has been applied. Thank you.
For the upper half, we may need more discussion. As far as I've observed,
the
-----------------------------------------------------------------------------patch changes the code to simulate Microsoft behavior, which seems to be a wrong behavior. I wonder if we should follow suit.
Regards, Alan
----- Original Message ----- From: "Aleksey Demakov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 4:37 AM Subject: [Mono-list] DbDataAdapter.Fill patch
Hi all,
I've found that the DbDataAdapter.Update (DataTable dataTable) and Update (DataSet dataSet, string sourceTable) methods iterate through all tables of the given dataSet and try to update them with this DataAdapter. I believe that this is incorrect.
The dataSet can contain multiple DataTables which are Filled using different DataAdapters with different select/insert/delete/update commans. Consequently one DataAdapter cannot be be able to perform all the needed updates.
Unfortunately, the .NET docs are silent about this issue. But I believe that DbDataAdapter.Update methods should be symmetric to Fill methods. So as Fill (DataSet) method fills only one DataSet table with default name "Table", the Update (DataSet) method should only update default table. And Update (DataSet, string) method should only update the specified table.
The attached patch fixes also another problem. The original code might pass a null DataTableMapping value which is then used to create a RowUpdatingEventArgs instance. So RowUpdatingEvent handler (for instance CommandBuilder) could get null DataTableMapping which might be unexpected. The patch makes sure that a non-null DataTableMapping is passed.
Regards, Aleksey
--
-
Index: DbDataAdapter.cs =================================================================== RCS file: /mono/mcs/class/System.Data/System.Data.Common/DbDataAdapter.cs,v retrieving revision 1.21 diff -u -r1.21 DbDataAdapter.cs --- DbDataAdapter.cs 12 Nov 2002 13:47:37 -0000 1.21 +++ DbDataAdapter.cs 21 Jan 2003 10:05:50 -0000 @@ -356,10 +356,7 @@
public override int Update (DataSet dataSet) { - int result = 0; - foreach (DataTable table in dataSet.Tables) - result += Update (table); - return result; + return Update (dataSet, "Table"); }
public int Update (DataTable dataTable) @@ -447,11 +444,16 @@
public int Update (DataSet dataSet, string sourceTable) { - int result = 0; - DataTableMapping tableMapping = TableMappings [sourceTable]; - foreach (DataTable dataTable in dataSet.Tables) - result += Update (dataTable, tableMapping); - return result; + MissingMappingAction mappingAction = MissingMappingAction; + if (mappingAction == MissingMappingAction.Ignore) + mappingAction = MissingMappingAction.Error; + DataTableMapping tableMapping =
DataTableMappingCollection.GetTableMappingBySchemaAction (TableMappings, sourceTable, sourceTable, mappingAction);
+ + DataTable dataTable = dataSet.Tables[tableMapping.DataSetTable]; + if (dataTable == null) + throw new ArgumentException ("sourceTable"); + + return Update (dataTable, tableMapping); }
protected virtual void OnFillError (FillErrorEventArgs value)
_______________________________________________ Mono-list maillist - [EMAIL PROTECTED] http://lists.ximian.com/mailman/listinfo/mono-list
using System; using System.Data; using System.Data.SqlClient;
public class Test
{
static SqlConnection conn = new
SqlConnection("server=(local);Trusted_Connection=yes;database=northwind");
static SqlDataAdapter da1 = new SqlDataAdapter("select * from foo", conn);
static SqlDataAdapter da2 = new SqlDataAdapter("select * from bar", conn);
static SqlDataAdapter da3 = new SqlDataAdapter("select * from baz", conn);
static DataSet ds = new DataSet();
public static void Main ()
{
conn.Open ();
Create ();
SqlCommandBuilder b1 = new SqlCommandBuilder (da1);
da1.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
da1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da1.Fill (ds, "Foo");
ds.Tables["Foo"].Rows[0]["FooData"] = "foo2";
SqlCommandBuilder b2 = new SqlCommandBuilder (da2);
da2.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
da2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da2.Fill (ds, "Bar");
ds.Tables["Bar"].Rows[0]["BarData"] = "bar2";
try {
Console.WriteLine ("Calling da1.Update() without table
parameter.");
da1.Update (ds);
Console.WriteLine ("Ok.");
} catch (Exception e) {
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
try
{
Console.WriteLine ("Calling da2.Update() without table
parameter.");
da2.Update (ds);
Console.WriteLine ("Ok.");
}
catch (Exception e)
{
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
try
{
Console.WriteLine ("Calling da1.Update() with table name
parameter.");
da1.Update (ds, "Foo");
Console.WriteLine ("Ok.");
} catch (Exception e) {
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
try
{
Console.WriteLine ("Calling da2.Update() with table name
parameter.");
da2.Update (ds, "Bar");
Console.WriteLine ("Ok.");
} catch (Exception e) {
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
Console.WriteLine ("Once again. Fill a table with the default name.");
SqlCommandBuilder b3 = new SqlCommandBuilder (da3);
da3.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
da3.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da3.Fill (ds);
ds.Tables["Table"].Rows[0]["BazData"] = "baz2";
try {
Console.WriteLine ("Calling da1.Update() without table
parameter.");
da1.Update (ds);
Console.WriteLine ("Ok.");
} catch (Exception e) {
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
try
{
Console.WriteLine ("Calling da2.Update() without table
parameter.");
da2.Update (ds);
Console.WriteLine ("Ok.");
} catch (Exception e) {
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
try
{
Console.WriteLine ("Calling da3.Update() without table
parameter.");
da2.Update (ds);
Console.WriteLine ("Ok.");
} catch (Exception e) {
Console.WriteLine ("Caught an exception: ");
Console.WriteLine (e);
}
}
private static void Create ()
{
SqlCommand cmd = new SqlCommand ();
cmd.Connection = conn;
try
{
cmd.CommandText = "drop table foo";
cmd.ExecuteNonQuery ();
}
catch (Exception)
{
}
try
{
cmd.CommandText = "drop table bar";
cmd.ExecuteNonQuery ();
}
catch (Exception)
{
}
try
{
cmd.CommandText = "drop table baz";
cmd.ExecuteNonQuery ();
}
catch (Exception)
{
}
cmd.CommandText = "create table foo (FooId int primary key, FooData
varchar (100))";
cmd.ExecuteNonQuery ();
cmd.CommandText = "insert into foo values (1, 'foo1')";
cmd.ExecuteNonQuery ();
cmd.CommandText = "create table bar (BarId int primary key, BarData
varchar (100))";
cmd.ExecuteNonQuery ();
cmd.CommandText = "insert into bar values (1, 'bar1')";
cmd.ExecuteNonQuery ();
cmd.CommandText = "create table baz (BazId int primary key, BazData
varchar (100))";
cmd.ExecuteNonQuery ();
cmd.CommandText = "insert into baz values (1, 'baz1')";
cmd.ExecuteNonQuery ();
cmd.Dispose ();
}
private static void RowUpdatingHandler (object sender, SqlRowUpdatingEventArgs
e)
{
Console.WriteLine ("Updating with command: {0}",
e.Command.CommandText);
}
}
Calling da1.Update() without table parameter. Caught an exception: System.InvalidOperationException: Update unable to find TableMapping['Table'] or DataTable 'Table'. at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at Test.Main() Calling da2.Update() without table parameter. Caught an exception: System.InvalidOperationException: Update unable to find TableMapping['Table'] or DataTable 'Table'. at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at Test.Main() Calling da1.Update() with table name parameter. Updating with command: UPDATE foo SET FooData = @p1 WHERE ( (FooId = @p2) AND ((FooData IS NULL AND @p3 IS NULL) OR (FooData = @p4)) ) Ok. Calling da2.Update() with table name parameter. Updating with command: UPDATE bar SET BarData = @p1 WHERE ( (BarId = @p2) AND ((BarData IS NULL AND @p3 IS NULL) OR (BarData = @p4)) ) Ok.
