Serving Google map tiles from local SQL Server

Asked By 60 points N/A Posted on -
qa-featured

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.

SHARE
Answered By 5 points N/A #137179

Serving Google map tiles from local SQL Server

qa-featured

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 />

Related Questions