Images in SQL

First you gotta get those suckers in there. It's someone else's job to write the admin app, and we aren't at that stage of development yet.

To insert an image file straight into an image or varbinary field:

INSERT Users (UserName, Photo) 
SELECT 'Joe', * FROM OPENROWSET(BULK 'C:\temp\studbil.jpg', SINGLE_BLOB) AS X

Or to update an existing field:

UPDATE Users SET Photo = (
SELECT * FROM OPENROWSET(BULK 'C:\temp\studbil.jpg', SINGLE_BLOB) AS X
)
where ID = 32

Now you might want to check the size of the binary field to see if it matches the file:

SELECT DATALENGTH(Photo) FROM Users

To get the photo out of the database, in C#:

if (row["Photo"] != DBNull.Value)
{
    MemoryStream imgStream = new MemoryStream((byte[])row["Photo"]);

    // According to MSDN, we're supposed to keep the stream open for the lifetime
    // of the image, which we can't do. So copy the original image by constructing
    // a new one.
    user.Photo = new Bitmap(Bitmap.FromStream(imgStream));

    imgStream.Close();
}

Many people forget the "keeping the stream open" issue when loading images, and in most cases it won't bite you. But in the case of my WCF service, I get this error if I close the stream and then try to serialize the image:

System.Runtime.InteropServices.ExternalException: A generic error occurred in GDI+. 
 at System.Drawing.Image.Save(Stream stream, ImageCodecInfo encoder, 
EncoderParameters encoderParams) 
 at System.Drawing.Image.Save(MemoryStream stream) 
 at System.Drawing.Image.System.Runtime.Serialization.ISerializable.GetObjectData(
SerializationInfo si, StreamingContext context) 
 at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteISerializable(
XmlWriterDelegator xmlWriter, ISerializable obj) 
 at WriteBitmapToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , 
ClassDataContract ) 
. . .