ADO.NET 通过updatetext 如何更新 ntext字段?困扰我两周了!
现有数据库是基于SQL SERVER 2000创建的,其中一张表有个字段是ntext类型,现在需要把二进制文件更新到此字段,我按照MSDN的方法,代码如下:
int BUFFER_LENGTH = 10240;
string strConnection = "server=localhost;database=test;uid=sa;pwd=123456";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open();
SqlCommand cmdGetPointer = new SqlCommand("SET NOCOUNT ON;UPDATE t_SaleActionEntry SET FContent_Tag = '' WHERE FEntryID=4;"
+ "SELECT @Pointer=TEXTPTR(FContent_Tag) FROM t_SaleActionEntry WHERE FEntryID=4", objConnection);
SqlParameter pointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
pointerOutParam.Direction = ParameterDirection.Output;
cmdGetPointer.ExecuteNonQuery();
SqlCommand cmdUpdateBinary = new SqlCommand("UPDATETEXT t_SaleActionEntry.FContent_Tag @Pointer @Offset @Delete @Bytes", objConnection);
SqlParameter pointerParam = cmdUpdateBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
SqlParameter offsetParam = cmdUpdateBinary.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter deleteParam = cmdUpdateBinary.Parameters.Add("@Delete", SqlDbType.Int);
deleteParam.Value = 0;
SqlParameter bytesParam = cmdUpdateBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH);
FileStream files = new FileStream("c:\\test.bat", FileMode.Open);
BinaryReader br = new BinaryReader(files);
int offset = 0;
offsetParam.Value = offset;
byte[] buffer = br.ReadBytes(BUFFER_LENGTH);
while (buffer.Length > 0)
{
pointerParam.Value = pointerOutParam.Value;
bytesParam.Value = buffer;
cmdUpdateBinary.ExecuteNonQuery();
deleteParam.Value = 0;
offset += BUFFER_LENGTH;
offsetParam.Value = offset;
buffer = br.ReadBytes(BUFFER_LENGTH);
}
br.Close();
files.Close();
执行一下就是报:“不能将数据类型 varbinary(max) 转换为 ntext”错误
因为更新字段为ntext,而@Bytes是一个Binary类型,执行确实会报错。
但我的需求就是要把二进制流写入NTEXT字段,能不能实现呢?