title: Retry DbDataAdapter.Fill after reconnection
body: <p>I have a code to fill a DataTable from db using a SqlCommand with select sql and DbDataAdapter. During filling rows to DataTable there is sometimes a loss of DbConnection, which is handled and second attempt of adapter.Fill() is called:</p>
  
  <pre><code>int FillSafe(DataTable dataTable)
  {
      try
      {
          return adapter.Fill(dataTable);
      }
      catch (Exception ex)
      {
          if (connectionErrorManager.HandleDisconnectionAndSecurityErrors(ex))
          {
              // Re-try fill function if error was properly handled
              return adapter.Fill(dataTable);
          }
  
          throw;
      }
  }
  </code></pre>
  
  <p>adapter is local field initialized with command:</p>
  
  <pre><code>adapter = new SqlDataAdapter((SqlCommand)selectCommand);
  </code></pre>
  
  <p>And connectionErrorManager.HandleDisconnectionAndSecurityErrors() basically closes and reopens SqlConnection object (same instance).</p>
  
  <p>It seems to be working fine, but there is a problem for tables without primary key - some data rows are become duplicated in DataTable.
  I.e. following test fails with 5 rows instead of 3:</p>
  
  <pre><code>public void TestFillDoesnotProduceDuplicateRowsAfterDisconnect()
  {
      const string Query = @"
  SELECT 'A'
  UNION ALL
  SELECT 'B'
  UNION ALL
  SELECT 'C'";
  
      var table = new DataTable();
      table.RowChanged += (o, e) =&gt;
      {
          if (table.Rows.Count == 2)
          {
              Db.Connection.CloseConnection();
          }
      };
  
      using (var command = Db.Connection.Command(Query))
      using (var adapter = command.NewDataAdapter())
      {
          FillSafe(table);
      }
  
      AssertEquals(3, table.Rows.Count);
  }
  </code></pre>
  
  <p>If I try to clear DataTable before refilling it</p>
  
  <pre><code>table.Rows.Clear();
  </code></pre>
  
  <p>test fails with exception about closed DataReader:</p>
  
  <pre><code>Exception Type: System.InvalidOperationException
  Message: Invalid attempt to call Read when reader is closed.
  StackTrace:
  at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean&amp; more)
  at System.Data.SqlClient.SqlDataReader.Read()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
  at className.FillSafe[T](DataTable dataTable)
  at className.TestFillDoesnotProduceDuplicateRowsAfterDisconnect()
  </code></pre>
  
  <p>So I have 2 questions:</p>
  
  <ol>
  <li><p>Why table.Rows.Clear() causes such difference in behaviour? How does it closes DataReader?</p></li>
  <li><p>How to solve it? I need to clear already loaded rows and refill table again.</p></li>
  </ol>