none
Can I make SqlDataAdapter.Fill immediately return after retrieving maxRecords from the database? RRS feed

  • Question

  • I'm looking to write some code that has a querying behavior similar to BIDS's Preview Query capability, where it returns the first N rows from a query and then appears to stop executing it.

    I was hoping to use SqlDataAdapter.Fill for this purpose, as it offers a maxRecords parameter. However, it appears that while it will only fill a DataTable with maxRecords, the query still runs to completion. For example, if I use a long running query liike:

    select * from AdventureWorks.Sales.SalesOrderDetail a

    CROSS JOIN AdventureWorks.Sales.SalesOrderDetail b

    CROSS JOIN AdventureWorks.Sales.SalesOrderDetail c

    CROSS JOIN AdventureWorks.Sales.SalesOrderDetail d

    CROSS JOIN AdventureWorks.Sales.SalesOrderDetail e

    the Fill method will just keep running, despite the 1st 100 hundred rows being retrieved nearly instantly. Additionally, when executing this in BIDS, I do get the behavior I'm looking for; the 1st 200 rows appear almost instantly and that's it. Is there a way to get SqlDataAdapter to stop executing the query once maxRecords is reached. If not, is there some other way to do it? (perhaps even point me to an example as I've been searching online but can't seem to find another approach that works)

    Thanks,

    -Craig

    • Moved by VMazurModerator Wednesday, December 1, 2010 11:30 AM (From:ADO.NET DataSet)
    Wednesday, November 24, 2010 10:13 PM

Answers

  • Hi Roahn and Giorgi,

    First off, thanks Giorgi for the code snippet.

    I came up with an approach (code below) that I think solves it. The key points are:

    1.     When clicking on the preview query button, we use the Task factory to start an asynchronous task that calls GetResultsSetDataTable(). This keeps the UI thread unblocked.

    2.     In GetResultsSetDataTable(), I kick off a SqlCommand, and use the SqlDataReader to read rows until we either run out of rows to read or we reach the maximum number of rows we want.

    3.     BuildColumnsList() is used to populate the DataTable’s columns list. Normally, this would be handled by the DataAdapter but now we’re doing it ourselves. Additionally, we can’t load rows into the DataTable until it has a columns list.

     

    This approach appears to work as desired. One key question though:

     

    Do you happen to know an API that will build the DataColumns list for me, so I can add it to the DataTable? I’d prefer to use a less fragile approach that doesn't rely on reflection, if possible. Alternatively, do I need all the properties from the DataColumns for the DataTable, or is it sufficient to just copy the DataColumn’s name?

     

    Thanks again,

     

    -Craig

     

      private void previewQueryButton_Click(object sender, RoutedEventArgs e)
      {
       SetCurrentValue(IsQueryRunningProperty, true);
       _taskCompletionSource = new TaskCompletionSource<int>();
    
       var oleDbConnectionNode = Connection as AstOleDbConnectionNode;
       if (oleDbConnectionNode != null)
       {
        _sqlCommand = new SqlCommand(Query, new SqlConnection(oleDbConnectionNode.SqlConnectionString));
       }
    
       Task.Factory.StartNew(
        (Action)GetResultsSetDataTable).ContinueWith(
        (Action<Task>)UpdateControlUi,
        TaskScheduler.FromCurrentSynchronizationContext());
      }
    
      private void GetResultsSetDataTable()
      {
       _resultsSetDataTable = new DataTable();
       _resultsSetErrorMessage = string.Empty;
       SqlDataReader sqlDataReader = null;
    
       try
       {
        // 1. Execute the command.
        _sqlCommand.Connection.Open();
        sqlDataReader = _sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
    
        // 2. Use the SqlDataReader to populate the DataTable's Columns list.
        BuildColumnsList(sqlDataReader);
    
        // 3. Load the rows into the DataTable.
        var numRows = 0;
        _resultsSetDataTable.BeginLoadData();
    
        while (sqlDataReader.Read() && numRows < MaximumRows)
        {
         numRows++;
    
         var objectArray = new object[sqlDataReader.FieldCount];
         sqlDataReader.GetValues(objectArray);
    
         _resultsSetDataTable.LoadDataRow(objectArray, true);
        }
    
        _resultsSetDataTable.EndLoadData();
       }
       catch (Exception ex)
       {
        _resultsSetErrorMessage = ex.Message;
       }
       finally
       {
        // 4. Clean up.
        _sqlCommand.Cancel();
    
        if (sqlDataReader != null && !sqlDataReader.IsClosed)
        {
         sqlDataReader.Close();
        }
       }
      }
    
      private void BuildColumnsList(SqlDataReader sqlDataReader)
      {
       var schemaTable = sqlDataReader.GetSchemaTable();
       if (schemaTable != null)
       {
        foreach (DataRow dataRow in schemaTable.Rows)
        {
         // Each SchemaTable row represents a DataColumn.
         // Each SchemaTable column represents a property for the DataColumn.
         // When we iterate over the SchemaTable's Columns collection, we'll populate the DataColumn's properties.
         var dc = new DataColumn();
    
         foreach (DataColumn dataColumn in schemaTable.Columns)
         {
          var dataColumnValue = dataRow.ItemArray[dataColumn.Ordinal];
    
          var propertyInfo = dc.GetType().GetProperty(dataColumn.ColumnName);
          if (propertyInfo != null)
          {
           propertyInfo.SetValue(dc, dataColumnValue, null);
          }
         }
    
         // Must avoid duplicate column names
         var tempName = dc.ColumnName;
         var dupCount = 0;
    
         while (_resultsSetDataTable.Columns.Contains(tempName))
         {
          dupCount++;
          tempName = dc.ColumnName + dupCount;
         }
    
         dc.ColumnName = tempName;
    
         _resultsSetDataTable.Columns.Add(dc);
        }
       }
      }
    
    
    Monday, November 29, 2010 7:37 PM

All replies

  • You can limit number of records by using top keyword. Select top 100 * from AdventureWorks.Sales.SalesOrderDetail a. If you need to you can make the top clause use a parameter instead of hard coding it.
    MCTS, CodeProject MVP 2008
    Thursday, November 25, 2010 7:32 AM
  • Hello,

    It is recommended to avoid the use of the DataAdapter.Fill overload that takes startRecord and maxRecords values.

    A technique used for returning only one page of records at a time is creating a sql statement that combines a WHERE clause and an ORDER BY clause, with the TOP predicate.

    Another technique for returning only one page of records is to create a sql statement that combines the use of the TOP predicate and embedded SELECT statements. For example,
    SELECT TOP 10 * FROM
      (SELECT TOP 30 * FROM Customers ORDER BY Id ASC) AS Table1
    ORDER BY Id DESC

    Please take a look at the following article on best practice for using ADO.NET:
    http://msdn.microsoft.com/en-us/library/ms971481.aspx

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Thursday, November 25, 2010 9:57 AM
  • Hi Roahn,

    I'm aware of the TOP predicate but my problem is that for my situation, the user may enter anything, not just SELECT statements. So to be clear, I won't have control over the user's SQL statement. Furthermore, they may enter statements that can't be subclauses.

    I've been trying to use the DataReader that's returned from the SqlCommand's ExecuteReader function to handle this. The only problem I have is that while I can use a while loop to read each row from the DataReader (thus ensuring I only read what I need), I don't see a function that conveinently converts each row from the reader into a DataRow object that I can add to my DataTable. Is there such a function?

    Thanks again,

    -Craig

    Saturday, November 27, 2010 7:34 AM
  • You can use the following code to copy datareader to datarow:

     

    object[] RowData = new object[theDataReader.FieldCount];
    
    theDataReader.GetValues(RowData);  
    
    theDataTable.Rows.Add(RowData);
    


    MCTS, CodeProject MVP 2008
    Saturday, November 27, 2010 1:51 PM
  • Hello Craig,

    Is there any update? Please let me know if it does not work!


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Monday, November 29, 2010 8:37 AM
  • Hi Roahn and Giorgi,

    First off, thanks Giorgi for the code snippet.

    I came up with an approach (code below) that I think solves it. The key points are:

    1.     When clicking on the preview query button, we use the Task factory to start an asynchronous task that calls GetResultsSetDataTable(). This keeps the UI thread unblocked.

    2.     In GetResultsSetDataTable(), I kick off a SqlCommand, and use the SqlDataReader to read rows until we either run out of rows to read or we reach the maximum number of rows we want.

    3.     BuildColumnsList() is used to populate the DataTable’s columns list. Normally, this would be handled by the DataAdapter but now we’re doing it ourselves. Additionally, we can’t load rows into the DataTable until it has a columns list.

     

    This approach appears to work as desired. One key question though:

     

    Do you happen to know an API that will build the DataColumns list for me, so I can add it to the DataTable? I’d prefer to use a less fragile approach that doesn't rely on reflection, if possible. Alternatively, do I need all the properties from the DataColumns for the DataTable, or is it sufficient to just copy the DataColumn’s name?

     

    Thanks again,

     

    -Craig

     

      private void previewQueryButton_Click(object sender, RoutedEventArgs e)
      {
       SetCurrentValue(IsQueryRunningProperty, true);
       _taskCompletionSource = new TaskCompletionSource<int>();
    
       var oleDbConnectionNode = Connection as AstOleDbConnectionNode;
       if (oleDbConnectionNode != null)
       {
        _sqlCommand = new SqlCommand(Query, new SqlConnection(oleDbConnectionNode.SqlConnectionString));
       }
    
       Task.Factory.StartNew(
        (Action)GetResultsSetDataTable).ContinueWith(
        (Action<Task>)UpdateControlUi,
        TaskScheduler.FromCurrentSynchronizationContext());
      }
    
      private void GetResultsSetDataTable()
      {
       _resultsSetDataTable = new DataTable();
       _resultsSetErrorMessage = string.Empty;
       SqlDataReader sqlDataReader = null;
    
       try
       {
        // 1. Execute the command.
        _sqlCommand.Connection.Open();
        sqlDataReader = _sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
    
        // 2. Use the SqlDataReader to populate the DataTable's Columns list.
        BuildColumnsList(sqlDataReader);
    
        // 3. Load the rows into the DataTable.
        var numRows = 0;
        _resultsSetDataTable.BeginLoadData();
    
        while (sqlDataReader.Read() && numRows < MaximumRows)
        {
         numRows++;
    
         var objectArray = new object[sqlDataReader.FieldCount];
         sqlDataReader.GetValues(objectArray);
    
         _resultsSetDataTable.LoadDataRow(objectArray, true);
        }
    
        _resultsSetDataTable.EndLoadData();
       }
       catch (Exception ex)
       {
        _resultsSetErrorMessage = ex.Message;
       }
       finally
       {
        // 4. Clean up.
        _sqlCommand.Cancel();
    
        if (sqlDataReader != null && !sqlDataReader.IsClosed)
        {
         sqlDataReader.Close();
        }
       }
      }
    
      private void BuildColumnsList(SqlDataReader sqlDataReader)
      {
       var schemaTable = sqlDataReader.GetSchemaTable();
       if (schemaTable != null)
       {
        foreach (DataRow dataRow in schemaTable.Rows)
        {
         // Each SchemaTable row represents a DataColumn.
         // Each SchemaTable column represents a property for the DataColumn.
         // When we iterate over the SchemaTable's Columns collection, we'll populate the DataColumn's properties.
         var dc = new DataColumn();
    
         foreach (DataColumn dataColumn in schemaTable.Columns)
         {
          var dataColumnValue = dataRow.ItemArray[dataColumn.Ordinal];
    
          var propertyInfo = dc.GetType().GetProperty(dataColumn.ColumnName);
          if (propertyInfo != null)
          {
           propertyInfo.SetValue(dc, dataColumnValue, null);
          }
         }
    
         // Must avoid duplicate column names
         var tempName = dc.ColumnName;
         var dupCount = 0;
    
         while (_resultsSetDataTable.Columns.Contains(tempName))
         {
          dupCount++;
          tempName = dc.ColumnName + dupCount;
         }
    
         dc.ColumnName = tempName;
    
         _resultsSetDataTable.Columns.Add(dc);
        }
       }
      }
    
    
    Monday, November 29, 2010 7:37 PM