Click here to Skip to main content
13,596,086 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Dear Friends,

in my c# windows application project,

we need to fetch the data from the sqlquery then filling into C# datatable.
Here we have to fill datatable morethan 50 Lakhs rows and 12 columns.

if the data has been <10 lakhs the application takes 3 minutes of time.
if it is >10 lakhs system getting hanged.

This is code i am using.

public DataTable GetTheData()
        {
            DataTable dtDataTablesList = new DataTable();
            string NewconnectionString ="Mycooectionstring";
            SqlConnection spContentConn = new SqlConnection(NewconnectionString);
            string sqlselectQuery = "select * from table";
            try
            {
                spContentConn.Open();
                SqlCommand sqlCmd = new SqlCommand(sqlselectQuery, spContentConn);
                sqlCmd.CommandTimeout = 0;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.ExecuteNonQuery();
                SqlDataAdapter adptr = new SqlDataAdapter(sqlCmd);
                adptr.Fill(dtDataTablesList);
                spContentConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (spContentConn != null)
                    spContentConn.Dispose();
            }
            return dtDataTablesList;
        }


can you tell me which is the best way of doing.
Posted 11-Sep-12 20:22pm
Updated 27-Apr-18 10:55am
v2
Comments
Karthik Harve 12-Sep-12 2:30am
   
What are you going to do with this data ? are you binding this data to a gidview ?
D-Kishore 12-Sep-12 2:34am
   
we need to export these datatable data to .csv file
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

First you probably don't need that many rows in your client as your user will be overwhelmed and clearly it chokes your network.

Try paging your data to the client in blocks of say 1000 rows.

If you need to process a lot of rows (analytic for example) try looking into server side analytics software and query processing.
  Permalink  
Comments
_Amy 12-Sep-12 2:37am
   
Absolutely right. 5'ed! :)
Mehdi Gholam 12-Sep-12 2:39am
   
Cheers!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Comments
Mehdi Gholam 12-Sep-12 2:39am
   
5'ed
_Amy 12-Sep-12 2:43am
   
Thanks Mehdi. :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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.
  Permalink  
v2
Comments
Nilesh Avhad 11-Jul-17 10:15am
   
Hey Kishore,
I am using your code in my project. But its showing error on YourWriter.WriteLine and
line. Could you please help.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi Kishore,

I guess this question already been solved.

check this[^].
  Permalink  
Comments
D-Kishore 13-Sep-12 2:36am
   
We need to purchage that component, so i am trying in different way,
That's why i posted this question.

Do you have any other solution.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

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()+ "</file"+>";

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"));
}
  Permalink  
Comments
Richard Deeming 16-Jun-17 11:14am
   
This question was asked, answered, and solved FIVE YEARS AGO!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03-2016 | 2.8.180621.3 | Last Updated 27 Apr 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100