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.
SELECT * FROM table
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.
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);
As I seem to keep forgetting this most simple of SQL statements…
SELECT * INTO NewTable FROM existingdb.dbo.existingtable;
.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;
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