reading writing a varbinary

Question

i'm trying to do this :

string path = C:/blablabla/file.png
byte[] byteFile = File.ReadAllBytes(path);
string s = Encoding.ASCII.GetString(byteFile, 0, byteFile.Length);
string SQL = @"INSERT INTO UserIcon (image) VALUES('" + s + "')";

i want to put the file in a sql varbinary(max) the request looks like ok, but i'm not sure

To get back the file i'm trying to do this :

string SQL2 = @"SELECT image FROM UserIcon WHERE id =xx";
DataSet mydataset = connect.ExecuteSelectQuery(SQL2);
DataTable myDataTable = mydataset.Tables[0];

connect is a custom connector to the database that puts data read in a DataSet i can ONLY give him a string

Then i'm quite lost, i can't get the file back. i tried things like that :

foreach (DataRow dr in myDataTable.Rows)
{
// doesn't work
(string)dr["image"]; 
//give me a corrupted file
File.WriteAllBytes(@"C:\Users\MyName\Desktop\test\file.png", (byte[])dr["Image"]); 
}

What do you think i should do to make that work?

No need to tell me " don't put big images in a database", my boss told me to do that and we already tried and it's not my decision.

Answer 1

You can't get the file back, because you broke it.

The GetString method is used to convert data back from binary to a string after converting it from a string to binary in the first place. If you do that on any arbitrary data, any values that doesn't map to a character will be replaced with a question mark.

To store the binary value you would use a parameterised query. Example:

string sql = "insert into UserIcon (image) values (@Image)";
using (SqlCommand cmd = new SqlCommand(sql, connection) {
  cmd.Parameters.AddWithValue("@Image", byteFile);
  cmd.ExecuteNonQuery();
}

Once you have stored the data correctly, you can read it and get the data back using (byte[])dr["Image"].

Edit:

To make a binary work in a string, you need to format it as a binary literal:

string bin = "0x" + BitConverter.ToString(byteFile).Replace("-", String.Empty);
string SQL = "insert into UserIcon (image) values (" + bin + ")";