I have installed SQL server 2012 and I'm storing google-map-like tiles into it as a BLOB. I'd like to show these tiles on user's web browser. How can I do this using SQL server?
EDIT: I'm using C# and ASP.NET (.NET 4 framework) with LINQ to SQL.
You need to retrieve the image in to a byte array and stream it to the web form. Below is a sample code which shows you how to achieve it. Sample contains two page one page to get the image and store it on a byte array and the other page is to display it to the user.
getImage.aspx.cs
stringimageID = Request["imageid"];
string DBconnstr = "server=server;uid=sa;pwd=;database=database";
SqlConnection conn = new SqlConnection(DBconnstr);
SqlCommand cmd = new SqlCommand("spGetImage", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter imageid = new SqlParameter("@imageid", SqlDbType.Int, 4);
imageid.Value = Convert.ToInt32(imageID);
imageid.Direction = ParameterDirection.Input;
cmd.Parameters.Add(imageid);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
byte[] buffer = (byte[])dr[0];
Response.ContentType = "image/jpeg";
Response.BinaryWrite(buffer);
Response.Flush();
}
“spGetImage” is a stored procedure and can be written as below
Create procedure spGetImage
@imageid int
Begin
Select Image –BLOB image
From tblTest
Where id=@imageid
end
DisplayImage.aspx
Then on the web form you can bind the image to an ASP.NET Image control as below
<asp:Image ImageAlign =”Middle” ImageUrl =’getImage.aspx?imageid=1 ' Runat =server />
