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);
void PutChars(long offset, char[] buffer);
void Close();
}
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();
}
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.