C#SQL客户端处理大文本数据通用接口
A uniform interface for large texts for a C# SQL client
By AlexS9999The article provides a simple interface for handling large text in SqlClient and C# file streams.
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.
译: 我们有可能要把SQL的数据存储到本地文件。所以我们建议策划出一个通用接口，并提供这两种实现方式：
C＃文本流和SQL Server数据库
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:
译: 也许分离出来“reader"和"writer"是个非常棒的主意，下面是Microsoft SqlClient实现:
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:
我们提供2个SQL命令，cmdReader用于读取文本和cmdWriter用于写入文本。
下面的代码展示SqlTextHandler类的输入参数.使用.WRITE条件更新T-SQL.
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)orvarchar(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, thePutCharsoperation 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
PutCharsoperation 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 abyte[]buffer, and instead of text streams, we would deal with C# binary streams.
总结:
1.两个处理程序可以合并为一个处理程序，其中通过PutChars()方法将数据写入两个逻辑流。
2.同样的机制可简单应用于二进制数据处理。处理一个byte[]缓冲区而不要处理字符数组缓冲区，
同理，用C＃二进制流处理而不要用文本流处理。
原文:http://www.codeproject.com/KB/database/CharsHandlerSQL.aspx
www.csframework.com 翻译
