Insert byte array into SQL Server from C# and how to retrieve it

Question

I'm trying to insert this byte array into a SQL Server database, the column data type is varbinary and this is my code in C#

SqlParameter param = new SqlParameter("@buffer", SqlDbType.VarBinary, 8000);
param.Value = buffer;

string _query = "INSERT INTO [dbo].[Files] (FileID, Data, Length) VALUES ('" + uID + "','" + buffer + "','" + buffer.Length + "')";

using (SqlCommand comm = new SqlCommand(_query, conn))
{
        comm.Parameters.Add(param);
        try
        {
            conn.Open();

            comm.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            return Request.CreateResponse(HttpStatusCode.OK, "Something went wrong : " + ex.Message);
        }
        finally
        {
            conn.Close();
        }
}

I also tried it with @buffer inside the _query string instead of buffer but I keep getting the error :

Converting from varchar to varbinary is not allowed use the CONVERT command to execute this query

and I used Convert and it is saved successfully, but when I retrieve it, it retrieves the first 14 bytes only,

byte[] bytearr = (byte[])row["Data"];

I've been looking and found nothing. Can you please help me in storing the bytes and retrieving it?

Answer 1

You send your values as literals in your SQL query. This is a bad idea, but first the problem with your query:

A varbinary literal in SQL Server is not a string (enclosed in quotes), but a very big hex number, looking something like this example: SET @binary = 0x1145A5B9C98.

By the way, I find it strange that you enclose your ID and your Length in quotes as well. I assume they are integers, so they should be specified without quotes: SET @i = 2. It may still work in your case, because the string is going to be converted to integer by SQL Server on the fly. It's just confusing and less efficient.

Now, please never do SQL requests by concatenating literals like that. Use SQL parameters instead. Something like that:

cmd.CommandText = "INSERT INTO Files (FileId, Data, Length) VALUES (@id, @data, @length)";
cmd.Parameters.AddWithValue("id", 3);
cmd.Parameters.AddWithValue("data", someByteArray);
cmd.Parameters.AddWithValue("length", someByteArray.Length);

If you want to make even simpler, look into some helper. I recommend Dapper.

Lastly, I note that you are storing both a varbinary and its length. That's not required, you can always get the length of a varbinary stored in SQL Server like this: SELECT LEN(Data) FROM Files

Answer 2

This is how I am doing it from one of my projects. Also to add to one of the comments someone made about sql injection attacks, it is good practice to not concat strings together to make up a sql string. Instead use command parameters like I am doing below with the name param. This prevents most kinds of sql injection attacks.

Loading:

        const string sql = "select data from files where name = @name";
        using (var cn = _db.CreateConnection())
        using (var cm = cn.CreateTextCommand(sql))
        {
            cm.AddInParam("name", DbType.String, name);
            cn.Open();
            return cm.ExecuteScalar() as byte[];
        }

Saving:

        const string sql = "insert into files set data = @data where Name = @name";
        using (var cn = _db.CreateConnection())
        using (var cm = cn.CreateTextCommand(sql))
        {
            cm.AddInParam("name", DbType.String, name);
            cm.AddInParam("data", DbType.Binary, data);
            cm.ExecuteNonQuery();
        }