Click here to Skip to main content
Click here to Skip to main content

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

, 26 Jul 2009
Rate this:
Please Sign up or sign in to vote.
The article provides a simple interface for handling large text in SqlClient and C# file streams.

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).

CharsHandler1.png

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

AlexS9999
Web Developer Zelax, inc
Russian Federation Russian Federation
Software Architect, Senior programmer since 1995.
In 1997-2000 worked for Basis Software Inc, year designed the Visual LISP (AutoLISP) UI, debugger environment and project system for AutoCAD. Current experience in java, C#, IBM Websphere, MS SQL.

Comments and Discussions

 
GeneralSome tips PingroupMd. Marufuzzaman26-Jul-09 11:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 26 Jul 2009
Article Copyright 2009 by AlexS9999
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid