[Mono-bugs] [Bug 69887][Wis] New - DbDataAdapter.Update needs to check for multiple results

[bugzilla-daemon@bugzilla.ximian.com]

Please do not reply to this email- if you want to comment on the bug, go to the
URL shown below and enter your comments there.

Changed by rscaletta@augustmack.com.

http://bugzilla.ximian.com/show_bug.cgi?id=69887

--- shadow/69887	2004-11-24 15:06:57.000000000 -0500
+++ shadow/69887.tmp.2520	2004-11-24 15:06:57.000000000 -0500
@@ -0,0 +1,102 @@
+Bug#: 69887
+Product: Mono: Class Libraries
+Version: 1.0
+OS: All
+OS Details: 
+Status: NEW   
+Resolution: 
+Severity: 
+Priority: Wishlist
+Component: Sys.Data
+AssignedTo: mono-bugs@ximian.com                            
+ReportedBy: rscaletta@augustmack.com               
+QAContact: mono-bugs@ximian.com
+TargetMilestone: ---
+URL: 
+Cc: 
+Summary: DbDataAdapter.Update needs to check for multiple results
+
+Please fill in this template when reporting a bug, unless you know what you
+are doing.
+Description of Problem:
+
+Some database commands will return multiple result sets.  This are most
+often used to retrieve automatic values generated by the database server. 
+DbDataAdapter.Update() should get multiple results and store any values
+back into the row being updated.
+
+Microsoft's implementation appears to be hard-coded to only check for 2
+results, so that will be sufficient.
+
+
+Steps to reproduce the problem:
+// Create a database with schema "CREATE TABLE t (id INTEGER PRIMARY KEY, 
+//   a TEXT, b TEXT)";
+// When used with SQLite, this means id will be autoincremented by the 
+// database.
+
+SQLiteCommand cmd = new SQLiteCommand("INSERT INTO t (a, b) VALUES (@a,
+@b); SELECT id, a, b FROM t WHERE (id=last_insert_rowid())");
+DataSet ds;
+// set the schema on ds
+...
+// Insert a new row
+DataRow r = ds.Tables[0].NewRow();
+r["a"] = "val";
+r["b"] = "val2";
+ds.Tables[0].Rows.Add(r);
+
+SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT id, a, b FROM t",
+_connection);
+adapter.InsertCommand = cmd;
+adapter.Update(ds);
+
+// OK, here's the test... it should have pulled in the id and it should be
+// and integer
+Debug.Assert(r["id"] != DbNull.Value && 
+  Int32.Parse(r["id"].ToString()) > 0);
+
+
+Actual Results:
+r["id"] is DbNull.Value
+
+Expected Results:
+r["id"] should be the autogenerated integer.
+
+How often does this happen? 
+All the time.
+
+Additional Information:
+Here's a proposed solution:
+Don't know how to get a proper patch, so I'll show you:
+in System.Data.Common/DbDataAdapter.cs :
+protected virtual int Update(DataRow[] dataRows, DataTableMapping tableMapping)
+{
+    // Skip ahead a bit, nothing has changed
+    ...
+    try
+    {
+	// use ExecuteReader because we want to use the commandbehavior parameter.
+	// so the connection will be closed if needed.
+	reader = command.ExecuteReader (commandBehavior);
+	int tmp = reader.RecordsAffected;
+        // CHANGE: Pull in next command if it's a batch command
+        if (reader.NextResult() {
+             // Does it return values? If so, store them back
+             if (reader.FieldCount > 0) {
+                 object[] values = new object[reader.FieldCount];
+                 // Assume it will only return one row, call Read only once
+                 Read();
+                 reader.GetValues(values);
+                 row.ItemArray = values;
+             }
+        }
+        // DONE CHANGING
+	// if the execute does not effect any rows we throw an exception.
+	if (tmp == 0)
+	throw new DBConcurrencyException("Concurrency violation: the " +
+commandName +"Command affected 0 records.");
+	updateCount += tmp;
+
+        // The rest is good.
+        ...