如何读取TEXT列并将其视为字节数组

We have an ancient foxpro application with a function which has to be converted to C#.

我们有一个古老的foxpro应用程序,其功能必须转换为C#。

Basically, all it does is to read a XLS file from a SQL Server database table and save it on the local disc. This file serves as an Excel worksheet template.

基本上,它只是从SQL Server数据库表中读取XLS文件并将其保存在本地磁盘上。此文件用作Excel工作表模板。

The binary XLS file information is stored in a TEXT column (which makes me nervous) and is extracted via FoxPro with this single line:

二进制XLS文件信息存储在TEXT列中(这让我感到紧张)并通过FoxPro使用以下单行提取:

lnResult = SQLEXEC(THISFORM.Hconn, "select XExcell from MyTable", "xy")
SELECT xy
IF !EMPTY(xy.xExcell) AND !ISNULL(xy.xExcell)
  COPY  memo xy.xExcell to (SomeFile)

The problem is that C# (or at least the ADO command) treat the column as a string. Well, that is what the reader comes up with when I execute a

问题是C#(或至少ADO命令)将列视为字符串。嗯,这就是读者在执行时所提出的

MyADOCommand.ExecuteScalar();

Edit: Which is an implicit string and cannot be casted to a byte array.

编辑:哪个是隐式字符串,不能转换为字节数组。

So, here's my problem: The data is in some binary form in the SQL Server and has to be pulled and treated as a raw byte array (and finally written to disc, giving it an extension of .XLS and pray that Excel is able to read the file).

所以,这是我的问题:数据在SQL Server中以某种二进制形式存在,必须被拉出并作为原始字节数组处理(最后写入光盘,给它扩展.XLS并祈祷Excel能够读取文件)。

Any idea how to achieve this? The following code obviously does not work

知道怎么做到这一点?以下代码显然不起作用

var s=(string)cmd.ExecuteScalar();
var enc = new System.Text.ASCIIEncoding(); 
var template= enc.GetBytes(s);
var fs = new FileStream(excelTemplateFile, FileMode.OpenOrCreate, FileAccess.Write);
var bw = new BinaryWriter(fs);
bw.Write(template);
bw.Flush();
bw.Close();
fs.Close();

I also tried to cast the TEXT column as an IMAGE, but that does not work either (SQL Server complains that it cannot convert). I know the problem is the fact that a text (a string of characters) is not a stream of bytes, but if FoxPro can do it, C# should be able as well, no?

我还尝试将TEXT列转换为IMAGE,但这也不起作用(SQL Server抱怨它无法转换)。我知道问题是文本(一串字符)不是字节流,但如果FoxPro可以做到,C#应该也能,不是吗?

Edit: The contents is approx 3MB big.

编辑:内容大约3MB。

1 个解决方案

#1

Change the SQL query to cast the column to varbinary as follows:

更改SQL查询以将列强制转换为varbinary,如下所示:

select cast(cast(XExcell as varchar(max)) as varbinary(max)) from MyTable

Then:

var bytes = (byte[])cmd.ExecuteScalar();

using (var writer = new BinaryWriter(File.Open("your file name", FileMode.Create))) 
{
   writer.Write(bytes);
}

EDIT: Double casting TEXT -> varchar(max) -> varbinary(max), because direct casting from TEXT -> varbinary(max) is not allowed

编辑:双重转换TEXT - > varchar(max) - > varbinary(max),因为不允许从TEXT直接转换 - > varbinary(max)

#1