Sql Server，插入和/或选择二进制数据问题

I'm having a problem either inserting or selecting data from a varbinary(max) column in my database (it is a localDB) and I can't seem to figure out why.

我在插入或从我的数据库中的varbinary(max)列中选择数据时遇到问题(它是一个localDB),我似乎无法弄清楚原因。

I'm adding the binary data from a C# application the following way.

我正在通过以下方式从C#应用程序添加二进制数据。

SqlConnection con = new SqlConnection("<connection string...>");            
SqlCommand cmd = new SqlCommand("sp_test_varbinary", con);

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@content", SqlDbType.VarBinary, -1).Value = Convert.FromBase64String("<base64 string>");

con.Open();
cmd.ExecuteNonQuery();
con.Close();

The sp_test_varbinary stored procedure takes just the one parameter @content and inserts it into a varbinary(max) column - so far all seems to work as expected. I can't tell what is in the varbinary(max) column, since it just says binary data, but something is there at least.

sp_test_varbinary存储过程只接受一个参数@content并将其插入varbinary(max)列 - 到目前为止,所有这些似乎都按预期工作。我无法分辨varbinary(max)列中的内容,因为它只是说二进制数据,但至少存在一些东西。

Then when I try to select the row I just inserted and attempt to get back the base64 string i originally had, something goes wrong or the data in the field is wrong.

然后,当我尝试选择刚插入的行并尝试返回我原来拥有的base64字符串时,出现问题或字段中的数据错误。

I'm doing the following (file_id is the ID of the row I added).

我正在执行以下操作(file_id是我添加的行的ID)。

SqlDataAdapter da = new SqlDataAdapter("SELECT [content] FROM [BaseFiles] WHERE ([file_id] = '" + file_id + "');", con);
DataSet ds = new DataSet();

da.Fill(ds);

string base64 = Convert.ToBase64String((byte[])ds.Tables[0].Rows["content"]);

At this point however, the base64 variable only contains the value:

但是,此时,base64变量仅包含值:

7w==

Any idea what I'm missing to make this work?

知道我缺少什么使这项工作?

1 个解决方案

#1

Looking at your result you more than likely did not specify the length of the VARBINARY in either your stored procedure, table or code within the stored procedure.

查看结果很可能没有在存储过程中的存储过程,表或代码中指定VARBINARY的长度。

Make sure that when you declare a VARBINARY variable that you included the MAX definition in your declaration. Example your stored procedure should be declared as follow:

确保在声明VARBINARY变量时,在声明中包含MAX定义。存储过程的示例应声明如下:

CREATE PROCEDURE [dbo].[sp_test_varbinary]
    @content AS VARBINARY(MAX)
AS
BEGIN

    SET NOCOUNT ON;

    -- YOUR INSERT STATEMENT HERE

END

And not declared as follow:

并未声明如下:

CREATE PROCEDURE [dbo].[sp_test_varbinary]
    @content AS VARBINARY
AS
BEGIN

    SET NOCOUNT ON;

    -- YOUR INSERT STATEMENT HERE

END

Similar make sure that if you use a local VARBINARY variables within your stored procedure that their lengths is also defined. Final make sure that the length of the VARBINARY is defined on the [content] column within your table.

类似地确保如果在存储过程中使用局部VARBINARY变量,也定义了它们的长度。最后确保在表中的[content]列上定义了VARBINARY的长度。

#1