Convert byte[] array TO sqldbtype.Varbinary using Dapper

Question

I've found a code snipped on the Internet that inserts a document as a byte array in the database. It is as follows:

    public void databaseFilePut(string varFilePath)
    {
        byte[] file;
        using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read))
        {
            using (var reader = new BinaryReader(stream))
            {
                file = reader.ReadBytes((int)stream.Length);
            }
        }

        //using (var varConnection = Locale.sqlConnectOneTime(Locale.slqDataConnectionDetails))
        using (SqlConnection connection = new SqlConnection(connectionString))
        using (var sqlWrite = new SqlCommand("INSERT INTO EXCEL_EM_BYTES (DOCUMENTO_BYTES) Values(@File)", connection))
        {
            sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
            connection.Open();
            sqlWrite.ExecuteNonQuery();
            connection.Close();
        }
    }

Now, I have to apply that to Dapper/Entity framework, but so far, without success. What I got so far is as follows:

      public void InsereRegistroEmail(string a, string b, string c, byte[] anexoBytes)
    {
        //var cn = _context.Database.Connection;

        //cn.Execute(string.Format(QueriesSAC.InsereRegistroEmailBanco, motivoEmail, nomeGestor, corpoEmail, anexoBytes));

        var cn = _context.Database.Connection;
        //var A = new SqlParameter("@A", SqlDbType.VarBinary, anexoBytes.Length);
        //A.Value = anexoBytes;

        var sql =(string.Format("INSERT INTO [LOG_EMAIL] ([GSTOR_DSTNA] ,[ASSNT],[DATA_ENVIO],[CORPO_EMAIL],[ANEXO]) VALUES('{0}','{1}', GETDATE(),'{2}', @A)", a, b, c));
        var A = new DynamicParameters();
        A.Add("@A", anexoBytes, dbType: DbType.Binary, direction: ParameterDirection.Input);
        A.Get<DbType>("@A");
        cn.Execute(sql);
    }

The key line here is:

         sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

As is the one that sets the data type to VarBinary. I really need some help here...

The file array is anexoBytes.

Not very familiar with Dapper, but don't you have to pass your parameters to Execute somehow? — Crowcoder
– Crowcoder, Commented Jan 17, 2017 at 15:41
Thing is, i'm not trying to exec a proc. It's just a simple query. I couldn't make the Dapper example fit in there — Longshadow
– Longshadow, Commented Jan 17, 2017 at 15:59
There are other example in the documentation of using parameters. What you are doing is basically like what would happen in your ADO.Net example if you created SqlParameters but never added them to the SqlCommand. — Crowcoder
– Crowcoder, Commented Jan 17, 2017 at 16:02
Just try this, see what happens. Looks like the Execute extension method takes an object that is the parameters in the second position: cn.Execute(sql, A); — Crowcoder
– Crowcoder, Commented Jan 17, 2017 at 16:04

G Davison · Accepted Answer · 2017-01-17 20:06:54Z

5

Dapper can handle the assignments through an anonymous type that will take the place of the parameter values, so in your example, you would just change your SQL to define some parameterised SQL, then pass in the new object that contains the values for those parameters. This will automatically map the values up to their parameters and avoid the need to manually define them or perform string replacement on your SQL.

_connection.Execute("INSERT INTO [LOG_EMAIL] ([GSTOR_DSTNA] ,[ASSNT],[DATA_ENVIO],[CORPO_EMAIL],[ANEXO]) VALUES(@DstNa, @Assnt, GETDATE(), @CorpEmail, @Anexo",
            new { DstNa = a, Assnt = b, CorpEmail = c, Anexo = anexoBytes });

answered Jan 17, 2017 at 20:06

G Davison

1,0991 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kiquenet Over a year ago

Valid for NVARCHAR(MAX), FILESTREAM and new features JSON in Sql Server 2016 ?

Collectives™ on Stack Overflow

Convert byte[] array TO sqldbtype.Varbinary using Dapper

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related