I am storing an ammount of files, of different types, as BLOBs in my Sql Server 2000 database.
Some of these are XML files, inside of which are some addresses I'd like to read.
However, I don't want to download a 2MB JPG image from the db, just to find out it isn't an XML file!
My best solution after playing around was to extract the first 5 bytes from the data, to determine if I should go ahead and use the whole thing:
// Get the 'Image' SQL Type
SqlDataReader sqlDataReader = selectDataCommand.ExecuteReader(CommandBehavior.SequentialAccess);
// If successful
if(sqlDataReader.Read())
{
int bufferSize = 5;
byte[] outbyte = new byte[bufferSize];
long startIndex = 0;
// Read the first 5 bytes
long retval = sqlDataReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
// Translate bytes to string
string test = FromASCIIByteArray(outbyte);
// Do the first 5 characters match an XML header?
if(test.CompareTo("<?xml") == 0)
{
// Get entire file here
}
}
This is quicker, but I know it can't be the quickest way. I am trying to find out how to write a stored prodecure that will do the same on the SQL Server side.
And yes, I could just add an extra byte column to my table to designate the file as XML, but I am interested in how to parse Image data types on the SQL Side.
I'll update when I find out how :)