Insert byte array in SQL Server from C # and how to get it

I am trying to insert this byte array into a SQL Server database, column data type 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 an @bufferinside line _queryinstead of a buffer, but I keep getting an error:

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

and I used Convertand it is successfully saved, but when I extract it, it only extracts the first 14 bytes,

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

I searched and found nothing. Could you help me in storing bytes and retrieving it?

+4
source share
2

SQL-. , :

SQL Server ( ), , : SET @binary = 0x1145A5B9C98.

, , . , , : SET @i = 2. - , integer SQL Server . .

, SQL-, . SQL. - :

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

, . Dapper.

, , varbinary, . , varbinary, SQL Server, : SELECT LEN(Data) FROM Files

+4

. , -, SQL-, - , sql. , , param. SQL-.

:

        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[];
        }

:

        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();
        }
+3

Source: https://habr.com/ru/post/1534320/


All Articles