I have a object with a byte[] property, and I would like to convert this value to the correct value to can insert it into the database using T-SQL.
But I don't know how I could convert the byte[] to the correct value for T-SQL for the insert.
Thanks.
Create a Console Application project and try this code
// Sample Class
public class MyClass
{
public byte[] data;
}
// Main
static void Main(string[] args)
{
MyClass cls = new MyClass();
using (SqlConnection cn = new SqlConnection("CONNECTION STRING"))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand("insert into MyTable values (@data)", cn))
{
cmd.Parameters.AddWithValue("@data", cls.data);
cmd.ExecuteNonQuery();
}
}
}
Add you are calling is marked obsolete. You should use .AddWithValue("@data", cls.data); or .Add("@data", SqlDbType.VarBinary).Value = cls.data;
You want to convert to VarBinary.
See the following:
simple example (setting command parameter)
byte[] data;
command.Parameters.Add("@data", SqlDbType.VarBinary).Value = data;
T-SQL example of how to pass in varbinary
CREATE PROCEDURE YourStoredProc
@data varbinary(max)
AS
BEGIN
-- your code
END
Generating raw varbinary to insert to database (copy-paste case)
string ToVarbinary(byte[] data)
{
var sb = new StringBuilder((data.Length * 2) + 2);
sb.Append("0x");
for (int i = 0; i < data.Length; i++)
{
sb.Append(data[i].ToString("X2"));
}
return sb.ToString();
}
