Inserting byte array into SQL Server

Question

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.

Answer 1

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.