将字节数组插入SQL Server [英] Inserting byte array into SQL Server

问题描述

我正在构造要在 Microsoft.ApplicationBlocks.Data.SqlHelper 中使用的 sql_insert_string ，其使用方式如下:

I am constructing a sql_insert_string to be used in Microsoft.ApplicationBlocks.Data.SqlHelper to be used as follows:

SqlHelper.ExecuteNonQuery(Transaction, CommandType.Text, sql_insert_string)

当我将鼠标悬停在SQL语句上时，它如下所示:

When I hover over the SQL statement it looks like below:

 string sql_insert_string = "Insert into images_table(image_id,     image_byte_array) values ('123', System.Byte[])

插入值之一是一个字节数组，如上所示.该变量在字节数组中具有值，例如byte [6738].但是，在构造 sql_insert_string 后，它以 System.Byte [] 的形式出现. image_byte_array 列类型为 varbinary(max).该数据库是SQL Server2008.因此，该数据库引发以下错误:

One of the insert value is a byte array as shown above. The variable has value in the byte array, say like byte[6738] . But after the sql_insert_string is constructed, it comes as System.Byte[]. The image_byte_array column type is varbinary(max). The database is SQL Server 2008. Because of this the database throws the following error:

对象或列名称丢失或为空.对于SELECT INTO语句，请验证每个列都有一个名称.对于其他语句，请查找空别名.不允许将别名定义为\"\"或[].将别名更改为有效名称.

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as \"\" or [] are not allowed. Change the alias to a valid name.

推荐答案

您可以像这样插入字节数组:

you can insert the byte array like so:

        private void FireSql(byte[] input)
        {
            const string sql_insert_string =
                "Insert into images_table(image_id, image_byte_array) values (@image_id, @image_byte_array)";

            SqlTransaction transaction = null; //wherever you get the transaction obj from.

            var imageIdParam = new SqlParameter("@image_id", SqlDbType.Int, 4)
            {
                Direction = ParameterDirection.Input,
                Value = 123
            }; //change the data type to whatever data type you are expecting

            var byteParam = new SqlParameter("@image_byte_array", SqlDbType.VarBinary)
            {
                Direction = ParameterDirection.Input,
                Size = input.Length,
                Value = input
            }; //change the data type to whatever data type you are expecting

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql_insert_string, imageIdParam, byteParam);
        }

我建议您查看ORM( https://en.wikipedia.org/wiki/像实体框架一样的对象关系映射()( http://www.asp.net/entity-framework)为您完成所有这些工作，同时提高安全性和将来的更改变得更加容易.

I would suggest looking at an ORM (https://en.wikipedia.org/wiki/Object-relational_mapping) like Entity Framework(http://www.asp.net/entity-framework) to do all of this for you while increasing security and future changes much easier.

这篇关于将字节数组插入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！