从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语句放进读取循环中，因为那样会为每条记录打开和关闭文件连接。有没有什么办法可以使我的位置保持在读取器中，并且只在需要时打开和关闭文件呢？

- adam

---

就在我发布这篇文章的时候，我有了一个想法。我能否不使用writer block，而是自己管理StreamWriter呢？比如StreamWriter writer = new StreamWriter(stream.Open())，然后使用try finally块来管理连接。 - adam

为什么需要同时使用 FileStream 和 StreamWriter。在新的 StreamWriter 中放置一个 stream.Open() 会同时打开两个文件，这在尝试关闭和重新打开时会导致问题。 - jdweng

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();
            }
        }
    }
}

- David

---

非常感谢您。我正在根据您的建议重构我的代码，并会告诉您进展如何。 - adam

Answer 2

个人而言，我会使用自定义 TextWriter 的覆盖来将文件名滚动逻辑从代码中解耦。类似这样的代码完全未经测试，可能不是线程安全的，效率低下（它目前只会一个字符一个字符地写入！）而且可能有 bug：

public class RollingFileWriter : TextWriter
{
    private readonly string _filenamePrefix;
    private readonly string _fileNameSuffix;
    private readonly int _maxRecordCount;
    private Stream _innerStream;
    private int _recordCount = 0;
    private int _fileCounter = 0;

    public RollingFileWriter( string filenamePrefix, string fileNameSuffix = ".txt", int maxRecordCount = 500000 )
    {
        _filenamePrefix = filenamePrefix;
        _fileNameSuffix = fileNameSuffix;
        _maxRecordCount = maxRecordCount;

        _innerStream = new FileStream(
            _filenamePrefix + "_" + _fileCounter.ToString() + _fileNameSuffix,
            FileMode.Create );
    }

    public override Encoding Encoding
    {
        get { return Encoding.UTF8; }
    }

    public override void Write( char value )
    {
        _innerStream.Write( Encoding.GetBytes( new[] { value } ), 0, 1 );
    }

    public override void WriteLine( string value )
    {
        if ( ++_recordCount == _maxRecordCount )
        {
            SwitchStreams();
        }
        base.WriteLine( value );
    }

    private void SwitchStreams()
    {
        _innerStream.Close();
        _innerStream.Dispose();

        _innerStream = new FileStream(
            _filenamePrefix + "_" + ( ++_fileCounter ).ToString() + _fileNameSuffix,
            FileMode.Create );
        _recordCount = 0;
    }

    protected override void Dispose( bool disposing )
    {
        if ( disposing )
        {
            _innerStream.Dispose();
        }
    }
}

然后您可以摆脱外部的FileStream，并将内部的StreamWriter替换为RollingFileWriter，并从循环中删除所有其他逻辑。

- Steve Czetty