Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server C#4.0
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
Edited 11-Sep-12 20:37pm
v2
Comments
Karthik Harve at 12-Sep-12 2:30am
   
What are you going to do with this data ? are you binding this data to a gidview ?
D-Kishore at 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 at 12-Sep-12 2:37am
   
Absolutely right. 5'ed! :)
Mehdi Gholam at 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 at 12-Sep-12 2:39am
   
5'ed
_Amy at 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
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 at 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.

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 360
1 Abhinav S 260
2 Nirav Prabtani 252
3 Dave Kreskowiak 155
4 Pikoh 140
0 OriginalGriff 7,545
1 Sergey Alexandrovich Kryukov 6,757
2 Maciej Los 3,909
3 Peter Leow 3,693
4 CHill60 2,712


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 14 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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