SQL – string inside a string

Handy little nugget to check if a string exists within another string. Particularly useful if you’re working with a delimited string as I was, as it makes dealing with the delimiters irrelevant.

WHERE CHARINDEX('string1', 'string2') != 0

In this instance ‘string1’ is the string you’re searching for in ‘string2’. Obviously these could be substituted for columns in a table or whatever.

Save an Image to a Filestream

In order to save an image to a database, you should first convert it to a byte array using a filestream. Once completed this byte array can be written to your database, usually being stored as a VarBinary DataType. In order to work with filestreams you must first add a reference to System.IO at the top of your project, the following code will then take care of the rest:

string strBLOBFilePath = yourImageFilePath;
FileStream fsBLOBFile = new FileStream(strBLOBFilePath, FileMode.Open, FileAccess.Read);
Byte[] bytBLOBData = new Byte[fsBLOBFile.Length];

fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length);

Storing Colours in Database

.NET allows you to build colours from their ARGB values, so it is possible to use this value to save and retrieve a colour to/from a database as an integer. The code snippet below uses the ToARGB and FromARGB methods to demonstrate this.

int myInt = colorDialog1.Color.ToArgb();
Color myColor = Color.FromArgb(myInt);
pictureBox1.Backcolor = myColor;

SQL Error Codes

When handling SQL Exceptions, it’s pretty handy to know which error codes relate to which errors…or at least it is for me as I’m currently writing some exception handling routines that work off the error code. So to get a list of all SQL error codes, along with descriptions, simply perform the following SELECT statement on the master database:

SELECT * FROM sysmessages