Exporting SqlDataReader results to array in C#

Question

I've written a function that that runs a SQL query, and have exposed it to Excel with ExcelDNA. The query itself uses a SqlDataAdapter and its corresponding .Fill() method to populate a DataTable.

I then iterate over the rows and columns of the DataTable to populate a 2D array that is defined as,

object[,] results = new object[dt.Rows.Count, dt.Columns.Count];

I can then directly return the results object to Excel, and everything is rendered correctly (strings as strings, and numbers as values).

However, I have run into a problem where certain SQL queries throw an "Out of Memory" exception when calling the .Fill() method.

I did a little bit of research and turns out that SqlDataReader might be more efficient as I'm interesting in only retrieving data into Excel and it will not load the result set into memory, but rather read them off row by row.

The problem I have is that there is no .Fill() method for SqlDataReader. I do have some working code where I can output the SQL results as a CSV file. Then I suppose I could write another function to import the CSV back into Excel. But this seems very circuitous.

Is there a more straightforward way to achieve this?

The full stack trace of the exception is included below.

System.Data.SQLite.SQLiteException (0x80004005): out of memory
out of memory
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
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 UtilXL.Utils.UtilsSQLite.RunQueryCSLite(String SQLStatement, String FilePath, Boolean IncludeHeaders) in h:\Projects\UtilXL\UtilXL\Utils\UtilsSQLite.cs:line 37

Line 37 in referenced above is the sda.Fill() call.

This is the stack trace when running SqlDataReader,

System.Data.SQLite.SQLiteException (0x80004005): out of memory out of memory
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteReader()
at UtilXL.Utils.UtilsSQLite.RunQueryCSReader(String SQLStatement, String FilePath, Boolean IncludeHeaders) in h:\Projects\UtilXL\UtilXL\Utils\UtilsSQLite.cs:line 111

Answer 1

I have no experience with ExcelDNA, but ...

I think you should try to understand why you're getting an OutOfMemoryException. It happens frequently in 32-bit applications that are using large object (in your case, probably strings).

It's true that using an SqlDataReader to process one row at a time will use less memory in the managed heap than loading all rows into a DataTable. But you would then need to load one row at a time into Excel - e.g. from an array declared as:

object[,] results = new object[1, reader.FieldCount];

This will likely be slower than loading the two-dimensional array on one call.

Answer 2

This is not an answer I think, but otherwise I don't know how to format the code. Grabbed your base code from your other tread, assuming you got IQToolkit from nuget, could you try something like this:

void Main()
{
  System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection( @"Data Source="+FilePath );
  SQLiteQueryProvider provider = new SQLiteQueryProvider(con, new ImplicitMapping(), QueryPolicy.Default);
    var data = provider.GetTable<MyTable>("MyTable")
    .Where(mt => mt.Level1 == "M_TO" && mt.AggCode == "C_DTA")
    .GroupBy(mt => new {mt.Date, mt.CompanyName})
    .Select(mt => new
     {
         Date = mt.Key.Date
         CompanyName = mt.Key.CompanyName,
         Sum = mt.Sum(t => t.Amount)
     }
    ).ToList();

// If it succeeds getting here then you are in good shape

  int numRows = data.Count() + (IncludeHeaders ?  1 : 0);
  object[,] ret = new object[numRows, 3];
  // ..
}

//// Entity Class
public class MyTable
{
    public string CompanyName { get; set; }
    public decimal Amount { get; set; }
    public DateTime? Date { get; set; } // varchar, really?
    public string AggCode { get; set; }
    public string Level1 { get; set; }
    //...
    public string Level5 { get; set; }
    // ...
    public string Level20 { get; set; }
}