c# - 从 SqlDataReader 写入多个文件

Question

我已经创建了一个数据读取器流，并且正在尝试将结果写入一个文件。由于此表可能返回数百万条记录，因此我想写入多个文件，以便我可以在文本编辑器中打开它们而不会出现问题，即。由于文件太大，文本编辑器崩溃了。这大致是我现在所拥有的。

using (var connection = new SqlConnection(connectionString))
using (var stream = new FileStream("directoryLocation", FileMode.Create))
{
    SqlCommand command = connection.CreateCommand();
    command.CommandText = "Select * from tblTemp";
    connection.Open();

    using(SqlDataReader reader = command.ExecuteReader())
    {
        var tableName = "tblTemp";
        var fileName = tableName + ".txt";
        var recordCount = 0;
        var fileCount = 0;

        using (StreamWriter writer = new StreamWriter(stream.Open()))
        {
            while(reader.Read())
            {
                if(recordCount == 500000)
                {
                    // Right here. Need to figure out how to close old file start new
                    recordCount = 0;
                    writer.Close();

                    fileName = tableName + "_" + (++fileCount).ToString() + ".txt";
                    writer = new StreamWriter(fileName); // I know this doesn't work. Just sudo code
                }

                recordCount++;
                writer.WriterLine(recordInfo); // recordInfo is sudo code as well
            }
        }
    }
}

我不想将 using writer 语句移动到 reader 循环中，因为这会为每条记录打开和关闭与文件的连接。关于如何保持我在阅读器中的位置以及仅在需要时打开和关闭文件有什么想法吗？

Answer 1

你很接近。您不需要单独的 FileStream。可以在循环中间创建一个新的 StreamWriter，只要您根据需要处置前一个 StreamWriter。

using (var connection = new SqlConnection(connectionString))
{
    SqlCommand command = connection.CreateCommand();
    command.CommandText = "Select * from tblTemp";
    connection.Open();

    using(SqlDataReader reader = command.ExecuteReader())
    {
        var tableName = "tblTemp";
        var fileName = tableName + ".txt";
        var recordCount = 0;
        var fileCount = 0;

        StreamWriter writer = null;
        try
        {
            while (reader.Read())
            {
                if (writer == null || recordCount == 500000)
                {
                    recordCount = 0;

                    // Close the previous file if it is open...
                    if (writer != null)
                    {
                        writer.Close();
                        writer.Dispose();
                    }

                    fileName = tableName + "_" + (++fileCount).ToString() + ".txt";

                    // Open the new file...
                    writer = new StreamWriter(fileName);
                }

                recordCount++;
                writer.WriterLine(recordInfo); // recordInfo is sudo code as well
            }
        }
        finally
        {
            // Make sure the file gets closed...
            if (writer != null)
            {
                writer.Dispose();
            }
        }
    }
}