CODEWITHSUNDEEP
c#sql-server
Massey
Massey

Reputation: 1125

Inserting byte array into SQL Server

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)

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[])

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:

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.

Upvotes: 3

Views: 34828

Answers (3)

Deepak
Deepak

Reputation: 119

You should be using the Parameters while constructing the SQL Query which obviously will avoid SQL Injection attacks. How your queries are getting constructed is still unclear here. Something like this should do it for you.

SqlParameter sParam = new SqlParameter("@image_byte_array", SqlDbType.VarBinary)
{
 Value = image
};
SqlHelper.ExecuteNonQuery(Transaction, CommandType.Text, sql_insert_string, sParam)

Upvotes: 0

Sumit Maingi
Sumit Maingi

Reputation: 2263

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);
        }

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.

Upvotes: 5

ydoow
ydoow

Reputation: 3006

You may use 

string sql_insert_string = 
    String.Format("INSERT INTO images_table(image_id, image_byte_array) VALUES ('123', CAST('{0}' AS VARBINARY(MAX)))", System.Byte[].ToString());

And yes, as @marc_s commented, you shouldn't be constructing SQL statements as a security concern.

Upvotes: -4

Related Questions