A uniform interface for large texts for a C# SQL client

Introduction

I have got a problem: we have texts as files and texts as blobs. Files are treated in C# via TextReader and TextWriter. The contents of files should also be stored in a SQL Server database. We also have the reverse task: read from SQL and put into a file. The main idea of moving data is simple: get a chunk of text as char[BUFFER_SIZE] from the source and put it to the target. Both SQL and C# interfaces support this (see the picture below).

We also might want to change the implementation from SQL storage to file storage. So, we suggested a uniform interface and provided two implementations of it: one for C# text streams and one for the SQL Server database.

The interface itself is:

public interface ICharsHandler { 
      char[]  GetChars(long offset, int length);  // read data chunk 
      void PutChars(long offset, char[] buffer); // put data chunk
      void Close(); // release the resources (recordset, connection or stream)
}

The implementation for the text writers follows:

public class StreamTextHandler : ICharsHandler
{
    TextReader reader;
    TextWriter writer;
    char[] buffer;

    public StreamTextHandler(TextWriter wr, TextReader rd)
    {
        reader = rd;
        writer = wr;
    }

    #region ICharsHandler Members

    public char[] GetChars(long offset, int length)
    {
        if (reader == null)
            throw new InvalidOperationException("Can't read data");
        if (buffer == null || buffer.Length != length)
            buffer = new char[length];
        int cnt = reader.Read(buffer, (int) offset, length);
        if (cnt < length)
        {
            char[] nv = new char[cnt];
            Array.Copy(buffer, nv, cnt);
            return nv;
        }
        return buffer;
    }

    public void PutChars(long offset, char[] buffer)
    {
        if (writer == null)
            throw new InvalidOperationException("Can't write data");
        writer.Write(buffer, (int) offset, buffer.Length);
    }

    public void Close()
    {
        if (reader != null) reader.Close();
        if (writer != null) writer.Close();
    }

    #endregion
}

Maybe, it would be a good idea to split this interface to a "reader" and a "writer".

The Microsoft SqlClient implementation follows:

class SqlTextHandler : ICharsHandler
{
    SqlCommand readCommand;
    SqlCommand writeCommand;
    int column;
    SqlDataReader rd;
    bool previousConn = false;
    
    public SqlTextHandler(SqlCommand cmd, SqlCommand wr, int _column)
    {
        readCommand = cmd;
        writeCommand = wr;
        column = _column;
        previousConn = (wr != null) ? 
        wr.Connection.State == ConnectionState.Open: 
             cmd.Connection.State == ConnectionState.Open;
    }

    protected void OpenReader()
    {
        readCommand.Connection.Open();
        rd = readCommand.ExecuteReader(CommandBehavior.SequentialAccess | 
                                       CommandBehavior.SingleRow);
        rd.Read();
    }
    // We assume that the input command
    // contain variables: @Value, @Offset and @Length
    protected void OpenWriter()
    {
        SqlParameter Out = 
          writeCommand.Parameters.Add("@Value", SqlDbType.NVarChar);
        SqlParameter OffsetParam = 
          writeCommand.Parameters.Add("@Offset", SqlDbType.BigInt);
        SqlParameter LengthParam = 
          writeCommand.Parameters.Add("@Length", SqlDbType.Int);
        writeCommand.Connection.Open();
    }

    char[] buffer;

    #region ICharsHandler Members

    public char[] GetChars(long offset, int length)
    {
        if (rd == null) OpenReader();
        if (buffer == null || buffer.Length != length)
        {
            buffer = new char[length];
        }
        long cnt = rd.GetChars(column, offset, buffer, 0, length);
        if (cnt < length)
        {
            char[] nv = new char[cnt];
            Array.Copy(buffer, nv, cnt);
            return nv;
        }
        return buffer;
    }

    public void PutChars(long offset, char[] buffer)
    {
        if (writeCommand.Parameters.Count < 4) OpenWriter();
        writeCommand.Parameters["@Length"].Value = buffer.Length;
        writeCommand.Parameters["@Value"].Value = buffer;
        writeCommand.Parameters["@Offset"].Value = offset;
        writeCommand.ExecuteNonQuery();
    }

    public void Close()
    {
        if (rd != null) rd.Close();
        if (!previousConn)
        {
            if (readCommand != null) readCommand.Connection.Close();
            if (writeCommand != null) writeCommand.Connection.Close();
        }
    }

    #endregion
}

We provide two SQL commands, the cmdReader for reading text and cmdWriter for writing text.

The code below shows a sample of input parameters for SqlTextHandler. The update T-SQL Command uses the .WRITE clause. Both SQL statements have been made bold in the sample below:

public ICharsHandler GetTextHandler(long id)
{
    SqlConnection _connection = new System.Data.SqlClient.SqlConnection();
    _connection.ConnectionString = 
       MyApp.Properties.Settings.Default.MyAppConnectionString;

    SqlCommand cmdWriter = new SqlCommand("UPDATE dbo.MessageUnit" + 
         " SET plainText .WRITE (@Value, @Offset, @Length) WHERE id = @id ", 
         _connection);
    cmdWriter.Parameters.Add(new SqlParameter("@id", id));
    SqlCommand cmdReader = new SqlCommand(
         "SELECT  plainText FROM dbo.MessageUnit WHERE id = @id",
         _connection);
    cmdReader.Parameters.Add(new SqlParameter("@id", id));
    return new SqlTextHandler(cmdReader, cmdWriter, 0);
}

An alternative implementation can be based on the UPDATETEXT SQL command, but it has been announced obsolete in the future versions of SQL server.

Two possible requirements should be mentioned:

• Use the proper SQL table column type nvarchar(MAX) or varchar(MAX). Otherwise, SQL Server reports an error operation.
• The value of the column should be initialized (as an empty string). If the initial value is null, the PutChars operation fails too.

A usage sample code may look like:

void MoveText(ICharHandler source, ICharHandler target)
{
    long offset = 0;
    for (; ; )
    {
        char[] buffer = source.GetChars(offset, BUFFER_SIZE);
        ptext.PutChars(offset, buffer);
        if (buffer.Length < BUFFER_SIZE) break;
        offset += BUFFER_SIZE;
    }
}

The conclusive notes are:

• Once we have two handlers, we can combine them into one handler, such that one PutChars operation will write into two logical streams.
• The same idea can be easily applied to binary data. So far, instead of the char[] buffer, we would deal with a byte[] buffer, and instead of text streams, we would deal with C# binary streams.