如何有效地写入从SQL DataReader的文件在C＃？ [英] How to efficiently write to file from SQL datareader in c#?

问题描述

我在C＃中的远程SQL连接需要执行一个查询，其结果保存到用户的本地硬盘上。有这个东西可以返回，所以需要想将其存储的有效方式相当大的数据量。我首先把整个结果到内存中之前已经阅读并然后写它是不是一个好主意，因此，如果有人可以帮助，就太棒了！

I have a remote sql connection in C# that needs to execute a query and save its results to the users's local hard disk. There is a fairly large amount of data this thing can return, so need to think of an efficient way of storing it. I've read before that first putting the whole result into memory and then writing it is not a good idea, so if someone could help, would be great!

我目前存储SQL结果数据转换成一个DataTable，但我想它可能是更好的做一些事情，而（myReader.Read（）{...}
下面是得到的结果代码：

I am currently storing the sql result data into a DataTable, although I am thinking it could be better doing something in while(myReader.Read(){...} Below is the code that gets the results:

          DataTable t = new DataTable();
            string myQuery = QueryLoader.ReadQueryFromFileWithBdateEdate(@"Resources\qrs\qryssysblo.q", newdate, newdate);
            using (SqlDataAdapter a = new SqlDataAdapter(myQuery, sqlconn.myConnection))
            {
                a.Fill(t);
            }

            var result = string.Empty;
    for(int i = 0; i < t.Rows.Count; i++)
    {
        for (int j = 0; j < t.Columns.Count; j++)
        {
            result += t.Rows[i][j] + ",";
        }


        result += "\r\n";
    }

所以现在我有这个巨大的结果字符串。和我有数据表。必须有这样做的更好的方法？

So now I have this huge result string. And I have the datatable. There has to be a much better way of doing it?

感谢。

推荐答案

您是在正确的轨道自己。使用循环与而（myReader.Read（）{...} 并写入每个​​记录循环内的文本文件。.NET Framework和操作系统将采取小心冲洗缓冲区写入磁盘以高效的方式。

You are on the right track yourself. Use a loop with while(myReader.Read(){...} and write each record to the text file inside the loop. The .NET framework and operating system will take care of flushing the buffers to disk in an efficient way.

using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = conn.CreateCommand())
{
  conn.Open();
  cmd.CommandText = QueryLoader.ReadQueryFromFileWithBdateEdate(
    @"Resources\qrs\qryssysblo.q", newdate, newdate);

  using(SqlDataReader reader = cmd.ExecuteReader())
  using(StreamWriter writer = new StreamWriter("c:\temp\file.txt"))
  {
    while(reader.Read())
    {
      // Using Name and Phone as example columns.
      writer.WriteLine("Name: {0}, Phone : {1}", 
        reader["Name"], reader["Phone"]);
    }
  }
}

这篇关于如何有效地写入从SQL DataReader的文件在C＃？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！