Tuesday, July 7, 2009

Inserting and Retrieving Images from SQL Database using LINQ

I was trying out to find a way to do this the other day and got it to work after a few tweeks. I have SQL 2005 installed and wanted to save the Uploaded Company Logos into the Database. First make sure that the table has a column of "image" type. the Upload control id is "flUploadImage". I will limit the code to that specific Image Upload, while removing the rest of the table fields/properties.

Inserting the Record:


article_sponsorship sponsorship = db.article_sponsorships.Single(artsp => artsp.id == ddlCampaigns.SelectedValue.ToInt16());

if (flUploadImage.HasFile && flUploadImage.PostedFile.ContentLength > 0)
{
byte[] img = null;
img = new byte[flUploadImage.PostedFile.ContentLength];
flUploadImage.PostedFile.InputStream.Read(img, 0, img.Length);
System.Data.Linq.Binary postedimage = new System.Data.Linq.Binary(img);
sponsorship.Logo = postedimage;
}

Fetching the Record:

To Display the Image, I had a seperate page built for rendering the image. The Image was queried on the basis of the querystring parameter that was passed on the page.

So my Image control named "imgLogo" was assigned this way


imgLogo.ImageUrl = "~/sponsorshipimage.aspx?imageid=" + sponsorship.id;

On the sponsorshipimage.aspx page, I had the code to fetch the image and put it in the OutPutStream. Here's the code:

sponsorshipimage.aspx
protected void Page_Load(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(Request.QueryString["imageid"]))
{
DataClassesDataContext db2 = new DataClassesDataContext();
article_sponsorship sponsorship = db2.article_sponsorships.Single(sp => sp.id == Request.QueryString["imageid"].ToInt16());

Response.ContentType = "image/jpeg";
Response.BinaryWrite(sponsorship.Logo.ToArray());

db2.Dispose();
db2 = null;
}
}

The ToArray() method was a pretty simple way to convert the System.Linq.Binary to an Array of bytes, which initially had made me little crazy figuring out how to do that, until I found out this Method. The image appeared without a doubt! Perfect!

Update : 03/01/2011

If you would like to post a default image if no image is present, here's how you go about it.

FileStream fileStream = new FileStream(Server.MapPath("~/images/noimage.png"), FileMode.Open, FileAccess.Read);
byte[] img = new byte[fileStream.Length];
fileStream.Read(img, 0, (int)fileStream.Length);
fileStream.Close();
System.Data.Linq.Binary postedimage = new System.Data.Linq.Binary(img);
sponsorship.Logo = postedimage;

No comments:

There was an error in this gadget