0

My requirement is For a DataSet, Create multiple Datables dynamically and add them to Dataset dynamically in C#. I need to store more than 30 million dataset in memory that consist of a sql table of columns and 30 million+ rows.

I will get the data from DB to datareader if the and I add that data to new row and add to datatable if the rowCount==5000 I have to create a new datatable dynamically and add that datatable to the dataset dynamically.

In case if you dont find my approach is healthy please suggest a new one.

SqlDataReader reader = objCmd.ExecuteReader();
tmp = new DataSet();
while (reader.Read())
{
    if(counter == 0)
    {
        string tableName = "Table_" + tableCount;
        output = new DataTable(tableName);
        tmp.Tables.Add(output);
        output = tmp.Tables[tableCount];
        
        output.Columns.Add("col1", typeof(string));
        output.Columns.Add("col2", typeof(string));
        output.Columns.Add("col3", typeof(string));
        output.Columns.Add("col4", typeof(string));
        output.Columns.Add("col5", typeof(string));

        output.Clear();
        output.Dispose();
    }
    output.Rows.Add(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4));
    counter++;
    while (counter == 5000)
     {
        tableCount++;
        counter = 0;
    }
}

My problem is it is not adding rows. It is adding empty table.

James Z
  • 12,209
  • 10
  • 24
  • 44
iamTheLearner
  • 1
  • 1
  • Creating multiple `DataTables` won't help because the issue is the volume of data you're apparently trying to process and that won't change just because it's in multiple `DataTables`. What are you doing with the data that you need 30 million records all in one place at the same time? – jmcilhinney Oct 07 '22 at 06:54
  • You could adopt one of [these](https://stackoverflow.com/questions/13220743/implement-paging-skip-take-functionality-with-this-query) solutions, set your SELECT cmd in a loop, `OFFSET n ROWS` and `FETCH NEXT n ROWS ONLY` to fill new data tables and insert them into a DataSet. Break when the reader has no rows and return the data set. – dr.null Oct 07 '22 at 06:58
  • @dr.null I'm almost doing what you said but after inserting few number of dataset i'm gettin g memoryExecption – iamTheLearner Oct 07 '22 at 10:24
  • If you have millions of rows in the DB to load, then loading that size of data in memory is a very bad idea. Maybe we could suggest you alternatives if you tell us why you are doing this. The main and final goal. – dr.null Oct 07 '22 at 10:48
  • 2
    Please avoid using words like *crore* that are not globally understood – James Z Oct 07 '22 at 15:06

0 Answers0