SQL Server and binary fields (images)

No matter how much I disagree on putting image files into SQL Server, I had to handle this on a project.

We had to provide update statements to insert binary data into production servers from the data we had on the test servers. The statement had to be written in T-SQL, period.

Everything would have been a fairy tail if Query Analyzer wouldn't have the 8K result limit. Most of the images were bigger than 8K. This limitation does not apply to the data you use on the query you're running but on the buffer that stores the results to show in QA.

We ended up writing a small console app with ADO.NET to retrieve the binary data, but guess what, you cannot insert this data as it is with a T-SQL statement. We shortly after discovered we could have used a third party tool such as SQL Bundle from red-gate to do this. Did I say before that I just LOVE SQL Compare?
The binary data has to be converted to its hexadecimal equivalent so it can be used in an update or insert statement, you can not insert whatever you get from the database into your T-SQL queries.

Code samples follow:

The ADO.NET part (pretty basic):

this.sqlConnection1.Open();
this.sqlCommand1.CommandText="SELECT [Image field] FROM table where ID=something";


byte[] barrImg=(byte[])sqlCommand1.ExecuteScalar();

string strfn=Convert.ToString("yourfilename.txt");
FileStream fs=new FileStream(strfn,FileMode.CreateNew);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();

The hexadecimal conversion part, basic too:

char[] hexDigits = {'0', '1', '2', '3', '4', '5', '6', '7','8', '9', 'A', 'B', 'C', 'D', 'E', 'F'};
char[] chars = new char[barrImg.Length * 2];
for (int i = 0; i <>
{
int b = barrImg[i];
chars[i * 2] = hexDigits[b >> 4];
chars[i * 2 + 1] = hexDigits[b & 0xF];
}

string results;
results=new string(chars);

StreamWriter strWr = new StreamWriter("yourhexdatafile.txt");
strWr.Write(results);
strWr.Flush();
strWr.Close();


Note: VB.NET has a Hex function for the hexadecimal conversion.