[轉載]Reading part of the ‘Image’ Sql Server BLOB

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

From：http://dotnetjunkies.com/WebLog/andrewwhitten/archive/2004/03/20/9569.aspx