将大数据填充到c＃数据表

问题描述

亲爱的朋友，


我的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.

推荐答案

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＃数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！