Filling large data to c# Data Table

Why you are fetching all the data from table at a time. Although you are having all the data in sql server then why you are making your form unnecessarily slow. Take only that records. Otherwise one alternative "Take top 1000 records manipulate it and update it to database". Again do the same operation.

No need to have that many records in the datatable. If you are performing any search operation then you can go to the database and search the records there itself. That will be faster than what you have currently.

This was my suggestion. There is some other ways also to handle large data in datatable. Refer the links below for that:
Tips For Using DataTables with VERY Large Data Sets[^]
best way to use .net Datatable with a huge data[^]
Storing Large Amounts of Data in a DataTable[^]


All the best.
--Amit

Hi friends,

This is my final solution for this.

with this code we can export 50 lakhs records to csv file in lessthan 2 minutes.
instead of datatable here i used datareader.

private void button1_Click(object sender, EventArgs e)
        {

            Stopwatch swra = new Stopwatch();
            swra.Start();
            string NewconnectionString = "myCoonectionString";
            StreamWriter CsvfileWriter = new StreamWriter(@"D:\testfile.csv");
            string sqlselectQuery = "select * from Mytable";
            SqlCommand sqlcmd = new SqlCommand();

            SqlConnection spContentConn = new SqlConnection(NewconnectionString);
            sqlcmd.Connection = spContentConn;
            sqlcmd.CommandTimeout = 0;
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.CommandText = sqlselectQuery;
            spContentConn.Open();
            using (spContentConn)
            {
                using (SqlDataReader sdr = sqlcmd.ExecuteReader())
                using (CsvfileWriter)
                {
                    //For getting the Table Headers
                    DataTable Tablecolumns = new DataTable();

                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        Tablecolumns.Columns.Add(sdr.GetName(i));
                    }
                    CsvfileWriter.WriteLine(string.Join(",", Tablecolumns.Columns.Cast<datacolumn>().Select(csvfile => csvfile.ColumnName)));
                    //For table headers

                    while (sdr.Read())
                    //based on your columns
                        YourWriter.WriteLine(sdr[0].ToString() + "," + sdr[1].ToString() + "," + sdr[2].ToString() + "," + sdr[3].ToString() + "," + sdr[4].ToString() + "," + sdr[5].ToString() + "," + sdr[6].ToString() + "," + sdr[7].ToString() + "," + sdr[8].ToString() + "," + sdr[9].ToString() + "," + sdr[10].ToString() + "," + sdr[11].ToString() + ",");
                       
                }
            }
           swra.Stop();
Console.WriteLine(swra.ElapsedMilliseconds);
}</datacolumn>

Thanks for all.

Hi Kishore,

I guess this question already been solved.

check this[^].

Think about this as well....
Export all data in xml from DB and traverse xml the way you want.

Oracle Proc Ex-
delcare
ctx1 dbms_xmlgen.ctxHandle;
begin
ctx1 := dbms_xmlgen.newContext('SELECT * FROM table **** ');
o_eligible_data:=dbms_xmlgen.getXML(ctx1);
dbms_xmlgen.closeContext(ctx1);
end;

C# Code - get below code in xml doc or anyother suitable variable.
(!((Oracle.ManagedDataAccess.Types.OracleClob)(objCmd.Parameters["clobData"].Value)).IsNull)
? ((Oracle.ManagedDataAccess.Types.OracleClob)(objCmd.Parameters["clobData"].Value)).Value.ToString() : string.Empty;


C# code - get specific node from xml
resultList = (xElementBalance != null) ? xElementBalance.Elements("ROW").
Where(x => x.Element("CORE_ID").Value.Trim().Equals(_coreId)).ToList() : null;


C# code - to read XML file using Xmlreader and create chunks. this is very fast.

string sourcePath = filesSourcePath[0].ToString();
int count = 0;

using (XmlReader myReader = XmlReader.Create(sourcePath))
{
int fileCount = 0;
StringBuilder sb = new StringBuilder();
myReader.ReadToDescendant("ResidentialStatement");
myReader.ReadToDescendant("CORE_ID");
do
{
sb.Append(myReader.ReadOuterXml());
count++;
if (count%10000 == 0)
{
fileCount++;
string strXml = "<file"+ filecount="" +="" "="">"+ sb.ToString()+ "";

XmlDocument doc = new XmlDocument();
doc.LoadXml(strXml);
doc.Save(ConfigurationManager.AppSettings["SourcePathConsolicatedXmlResidentialStatement"].ToString() + fileCount + ".xml");
Console.WriteLine(count);
Console.WriteLine("File Create " + @"D:\InvStm1\POC\Chunks\" + fileCount + ".xml");
sb.Clear();
doc = null;
}
}
while (myReader.ReadToNextSibling("CORE_ID"));
}