将大数据填充到c＃数据表 [英] Filling large data to c# Data Table

问题描述

亲爱的朋友，


我的c＃windows应用程序项目中的
，



我们需要获取来自sqlquery的数据然后填入C＃datatable。

这里我们要填充数据表超过50 Lakhs行和12列。



如果数据已经超过10万卢比申请需要3分钟的时间。

如果是> 10万卢比系统被绞死。



这是我正在使用的代码。

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.

推荐答案

首先，您的客户端可能不需要那么多行，因为您的用户将被淹没，显然它会扼杀您的网络。



尝试以1000行为单位将数据分页到客户端。



如果需要处理大量行（例如解析）尝试查看服务器端分析软件和查询处理。

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.

为什么一次从表中获取所有数据。虽然你在sql server中拥有所有数据，但为什么你的表单不必要地变慢。只记录那些记录。否则，可以选择获取前1000条记录来操纵它并将其更新到数据库。再次做同样的操作。



无需在数据表中包含那么多记录。如果您正在执行任何搜索操作，那么您可以转到数据库并在那里搜索记录。这比你现在的要快。



这是我的建议。还有一些其他方法可以处理数据表中的大数据。请参阅以下链接：

使用具有非常大数据集的DataTable的提示 [ ^ ]

使用.net Datatable的最佳方式，包含大量数据 [ ^ ]

在DataTable中存储大量数据 [ ^ ]





一切顺利。

--Amit

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

嗨朋友们，



这个这是我的最终解决方案。



使用此代码，我们可以在2分钟内将50万条记录导出到csv文件。

而不是数据表在这里我使用了datareader。

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.

这篇关于将大数据填充到c＃数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！