MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Vladislav Vaintroub Date:July 12 2010 2:38pm
Subject:bzr commit into connector-net-6.0 branch (vvaintroub:824) Bug#38411
View as plain text  
#At file:///H:/connector_net/6.0/ based on revid:vvaintroub@stripped

  824 Vladislav Vaintroub	2010-07-12
      Bug#38411:
      
      When batching is used in MySqlDataAdapter, connection was not opened automatically in 
      MySqlDataAdapter.Update(). 
      
      This resulted into InvalidOperationException (exception text "connection must be valid and open")
      Fix to behave more like SQL Server :if connection is closed, open it  for the duration of update operation.

    modified:
      CHANGES
      MySql.Data/Provider/Source/dataadapter.cs
      MySql.Data/Tests/Source/DataAdapterTests.cs
=== modified file 'CHANGES'
--- a/CHANGES	2010-07-08 20:21:58 +0000
+++ b/CHANGES	2010-07-12 14:38:02 +0000
@@ -1,3 +1,5 @@
+- Fix "Connection must be valid and open" exception When UpdateBatchSize > 1 
+  and MySqlDataAdapter is not using an open connection (Bug #38411)
 - Make sure MySqlDataAdapter.Update() works for custom stored procedure driven update commands 
   that make use of UpdateRowSource.FirstReturnedRecord (Bug#54895)
 - Call DataTable.AcceptChanges() for affected tables at the end of MySqlAdapter.Update, 

=== modified file 'MySql.Data/Provider/Source/dataadapter.cs'
--- a/MySql.Data/Provider/Source/dataadapter.cs	2010-07-01 16:04:18 +0000
+++ b/MySql.Data/Provider/Source/dataadapter.cs	2010-07-12 14:38:02 +0000
@@ -125,18 +125,74 @@ namespace MySql.Data.MySqlClient
 
 		#endregion
 
+        /// <summary>
+        /// Open connection if it was closed.
+        /// Necessary to workaround "connection must be open and valid" error
+        /// with batched updates.
+        /// </summary>
+        /// <param name="state">Row state</param>
+        /// <param name="openedConnections"> list of opened connections 
+        /// If connection is opened by this function, the list is updated
+        /// </param>
+        /// <returns>true if connection was opened</returns>
+        private void OpenConnectionIfClosed(DataRowState state,
+            List<MySqlConnection> openedConnections)
+        {
+            MySqlCommand cmd = null;
+            switch (state)
+            {
+                case DataRowState.Added:
+                    cmd = InsertCommand;
+                    break;
+                case DataRowState.Deleted:
+                    cmd = DeleteCommand;
+                    break;
+                case DataRowState.Modified:
+                    cmd = UpdateCommand;
+                    break;
+                default:
+                    return;
+            }
+
+            if (cmd != null && cmd.Connection != null &&
+                cmd.Connection.connectionState == ConnectionState.Closed)
+            {
+                cmd.Connection.Open();
+                openedConnections.Add(cmd.Connection);
+            }
+        }
+
+
         protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping)
         {
-            int ret = base.Update(dataRows, tableMapping);
-            // DbDataAdapter does automatically calls AcceptChanges on table.
-            // (even if  documentation states otherwise).
-            // Do AcceptsChanges() here, for SQL Server compatible behavior
-            // (see also Bug#5463)
-            foreach (DataRow row in dataRows)
+            List<MySqlConnection> connectionsOpened = new List<MySqlConnection>();
+
+            try
             {
-                row.Table.AcceptChanges();
+                foreach(DataRow row in dataRows)
+                {
+                    OpenConnectionIfClosed(row.RowState, connectionsOpened);
+                }
+
+                int ret = base.Update(dataRows, tableMapping);
+
+                // DbDataAdapter does automatically calls AcceptChanges on table.
+                // (even if  documentation states otherwise).
+                // Do AcceptsChanges() here, for SQL Server compatible behavior
+                // (see also Bug#5463)
+                foreach (DataRow row in dataRows)
+                {
+                    row.Table.AcceptChanges();
+                }
+                return ret;
+
             }
-            return ret;
+            finally 
+            {
+                foreach(MySqlConnection c in connectionsOpened)
+                    c.Close();
+            }
+
         }
 
         #region Batching Support

=== modified file 'MySql.Data/Tests/Source/DataAdapterTests.cs'
--- a/MySql.Data/Tests/Source/DataAdapterTests.cs	2010-07-08 20:21:58 +0000
+++ b/MySql.Data/Tests/Source/DataAdapterTests.cs	2010-07-12 14:38:02 +0000
@@ -898,6 +898,75 @@ namespace MySql.Data.MySqlClient.Tests
         }
 
         /// <summary>
+        /// Bug #38411, using closed connection with data adapter.
+        /// </summary>
+        [Test]
+        public void BatchingConnectionClosed()
+        {
+            execSQL("CREATE TABLE Test (id INT, name VARCHAR(20), PRIMARY KEY(id))");
+
+            MySqlConnection c = new MySqlConnection(GetConnectionString(true));
+            MySqlConnection c2 = new MySqlConnection(GetConnectionString(true));
+            MySqlConnection c3 = new MySqlConnection(GetConnectionString(true));
+
+            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
+            MySqlCommand ins = new MySqlCommand("INSERT INTO test (id, name) VALUES (?p1, ?p2)", c);
+            da.InsertCommand = ins;
+            ins.UpdatedRowSource = UpdateRowSource.None;
+            ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
+            ins.Parameters.Add("?p2", MySqlDbType.VarChar, 20).SourceColumn = "name";
+
+            MySqlCommand del = new MySqlCommand("delete from test where id=?p1", c2);
+            da.DeleteCommand = del;
+            del.UpdatedRowSource = UpdateRowSource.None;
+            del.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
+
+
+            MySqlCommand upd = new MySqlCommand("update test set id=?p1, name=?p2  where id=?p1", c3);
+            da.UpdateCommand = upd;
+            upd.UpdatedRowSource = UpdateRowSource.None;
+            upd.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
+            upd.Parameters.Add("?p2", MySqlDbType.VarChar, 20).SourceColumn = "name";
+
+            DataTable dt = new DataTable();
+            da.Fill(dt);
+
+            for (int i = 1; i <= 100; i++)
+            {
+                DataRow row = dt.NewRow();
+                row["id"] = i;
+                row["name"] = "name " + i;
+                dt.Rows.Add(row);
+            }
+
+            da.UpdateBatchSize = 10;
+            da.Update(dt);
+
+            dt.Rows.Clear();
+            da.Fill(dt);
+            Assert.AreEqual(100, dt.Rows.Count);
+            for (int i = 0; i < 100; i++)
+            {
+                Assert.AreEqual(i + 1, dt.Rows[i]["id"]);
+                Assert.AreEqual("name " + (i + 1), dt.Rows[i]["name"]);
+            }
+
+            foreach (DataRow row in dt.Rows)
+            {
+                row["name"] = row["name"] + "_xxx";
+            }
+            da.Update(dt);
+            for (int i = 0; i < 100; i++)
+            {
+                dt.Rows[i].Delete();
+            }
+            da.Update(dt);
+            dt.Rows.Clear();
+            da.Fill(dt);
+            Assert.AreEqual(dt.Rows.Count, 0);
+
+        }
+        /// <summary>
         /// Bug#54895
         /// ConcurrencyException when trying to use UpdateRowSource.FirstReturnedRecord 
         /// with UpdateCommand and stored procedure.


Attachment: [text/bzr-bundle] bzr/vvaintroub@mysql.com-20100712143802-hlvbuu94fniz1999.bundle
Thread
bzr commit into connector-net-6.0 branch (vvaintroub:824) Bug#38411Vladislav Vaintroub12 Jul