[Mono-list] DbDataAdapter.Fill patch
Aleksey Demakov
avd@openlinksw.com
Wed, 26 Feb 2003 05:20:44 +0600
This is a multi-part message in MIME format.
--------------020102050909030809040405
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
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" <avd@openlinksw.com>
> To: "Alan Tam" <Tam@SiuLung.com>
> Cc: <mono-list@ximian.com>
> 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" <avd@openlinksw.com>
>>>To: <mono-list@ximian.com>
>>>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 - Mono-list@lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list
>
--------------020102050909030809040405
Content-Type: text/plain;
name="u.cs"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="u.cs"
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);
}
}
--------------020102050909030809040405
Content-Type: text/plain;
name="out"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="out"
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.
--------------020102050909030809040405--