Does anyone know whether if its possible to insert binary data into an SQL field from c# without using a stored procedure?
For example - convert a byte array into base64 or something like that and then use a text command like the following...
String.Format("update A set B = {0} where C = D", Convert.ToBase64String(b));
where b is a byte array.
Thanks
Of course, you should use parameterized statements, but if you really need to, you can do it like this:
byte[] b = null; //(your input should be a byte[])
String.Format("update A set B = 0x{0} where C = D", BitConverter.ToString(b).Replace("-", "").ToLower());
SQL Server expects binary data in a hexadecimal format without hyphens and in lower case with a prefix of '0x'
Try this code, either the command.Parameters that is uncommented or the commented code should work. I use OracleDataClient at work, so I took this code almost completely from MSDN
string commandText= "update A set B = @BIN where C = D";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@BIN", SqlDbType.Binary, b.Length).Value = b;
// command.Parameters.AddWithValue("@BIN ", b);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
edit: this assumes b is already byte[]. I just looked at some old code and update the parameter to what worked for me (SQL Server 2005)
