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
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));
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,
at System.Drawing.Image.Save(MemoryStream stream)
SerializationInfo si, StreamingContext context)
XmlWriterDelegator xmlWriter, ISerializable obj)
at WriteBitmapToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext ,
. . .