Convert byte[] to SqlBytes

Question

I want to do some testing of a SQL CLR procedure and I'm trying to replicate it in a local C# project for better debugging.

I need to convert the data returned from the database to SqlBytes in order for my C# function to accept it but it tells me that I can't implicitly convert it. Note that the SQL column is of type image.

Code:

        SqlConnection con = new SqlConnection(connString);
        SqlDataAdapter da = new SqlDataAdapter(query, con); //query gets a field from the database of type 'image'
        SqlCommandBuilder MyCB = new SqlCommandBuilder(da);
        DataSet ds = new DataSet("Image");
        byte[] MyData = new byte[0];
        da.Fill(ds, "Image");
        DataRow myRow;
        myRow = ds.Tables["Image"].Rows[0];

        MyData = (byte[])myRow["econtents"];
        SqlBytes SQL = (SqlBytes)MyData; //Cannot implicitly convert type 'byte[]' to 'System.Data.SqlTypes.SqlBytes'

I've tried a few different options/syntaxes for the last line and also just passing MyData into the method directly.

I've been following this Microsoft article but it's not quite the same as my scenario. For reference I want to pass the data returned from the SQL query to: public static SqlString WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append)

How do I perform this conversion, or is it only possible to pass in SqlBytes when using a CLR function from the SQL side?

The image datatype has been deprecated in favor of varbinary (max) for more than a decade now. — Sean Lange
– Sean Lange, Commented Mar 12, 2018 at 16:34

Stephen Kennedy · Accepted Answer · 2019-08-29 16:26:44Z

4

It's quite simple - you pass your byte array to the overloaded SqlBytes constructor:

var bytes = new byte[] { 1, 2, 3 };
var sqlBytes = new SqlBytes(bytes);

This constructor "initializes a new instance of the SqlBytes class based on the specified byte array".

edited Aug 29, 2019 at 16:26

answered Mar 12, 2018 at 16:49

Stephen Kennedy

21.8k24 gold badges99 silver badges114 bronze badges

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

Collectives™ on Stack Overflow

Convert byte[] to SqlBytes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related