Click here to Skip to main content
12,815,616 members (40,407 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# 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";
                SqlCommand sqlCmd = new SqlCommand(sqlselectQuery, spContentConn);
                sqlCmd.CommandTimeout = 0;
                sqlCmd.CommandType = CommandType.Text;
                SqlDataAdapter adptr = new SqlDataAdapter(sqlCmd);
            catch (Exception ex)
                throw ex;
                if (spContentConn != null)
            return dtDataTablesList;

can you tell me which is the best way of doing.
Posted 11-Sep-12 21:22pm
Updated 11-Sep-12 21:37pm
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
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.
_Amy 12-Sep-12 2:37am
Absolutely right. 5'ed! :)
Mehdi Gholam 12-Sep-12 2:39am
Rate this: bad
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.
Mehdi Gholam 12-Sep-12 2:39am
_Amy 12-Sep-12 2:43am
Thanks Mehdi. :)
Rate this: bad
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();
            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;
            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++)
                    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() + ",");

Thanks for all.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Hi Kishore,

I guess this question already been solved.

check this[^].
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.

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 | Mobile
Web02 | 2.8.170308.1 | Last Updated 14 Sep 2012
Copyright © CodeProject, 1999-2017
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