Retrieving images stored in SQL Server using C#

Question

I have the image data stored in a SQL Server database table. This is my table structure

create table userimages
(ID integer identity(1,1),
boardingpass varbinary(max));
go

I have created a stored procedure which returns back the image using the out parameter after checking if the user is a valid user or not

alter procedure return_userimage(@firstname char(100) , @lastname char(100), @imagedata varbinary(max) out)
 as
 begin
    declare @result int

    exec @result = usp_validatuser @firstname, @lastname

    if(@result = 1)
    begin
       declare @userid int

       select @userid = ID 
       from tbl_UserInformation 
       where FirstName = @firstname and LastName = @lastname

       select @imagedata = boardingpass 
       from userimages 
       where ID = @userid 
    end
  else
  begin
     return 0
  end
end

I want to retrieve back the image and display it using ASP image control . Please guide with the code required to call the stored procedure and display the image using c#,ASP.

Thanks

Are you able to successfully execute the stored proc from code and see the value for @imagedata? — RQDQ, Jul 10 '13 at 15:19
@RQDQ : Hi , I am able to execute the procedure in SQL server data is shown in following format '0xFFD8FFE000104A46494600010101009600960000....' — Sukhdeep Singh Saini, Jul 10 '13 at 15:22
So really, this is two questions (how to call a stored procedure with an out parameter in sql server) and (how to display an image in ASP.NET). These have both been answered on this site before. Also, the example you provided could have been simplified to eliminate @@firstname, @@lastname, etc as they don't have anything to do with the answer. More information at http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — RQDQ, Jul 10 '13 at 15:42
While not exactly an answer to your question, may I suggest that storing images in the database is usually not a good idea. Storing images files on regular file systems has been much less trouble in my experience. Use the database only to store the path to those files. This simplifies the logic of accessing the images (websites, for example, just link to the path) and can improve performance through file system optimizations (e.g. sendfile) and simply because the database is tuned for lots of random IO, whereas image serving is linear IO. — Charles Burns, Jul 10 '13 at 15:52
@CharlesBurns: I modified the database structure and now I am storing only location of images in database and the code is working fine for me. Thanks for your help :) — Sukhdeep Singh Saini, Jul 11 '13 at 01:45
@RQDQ : thanks, I have modified the DB structure by referring the previous posts and its working for me now. — Sukhdeep Singh Saini, Jul 11 '13 at 01:47

score 2 · Accepted Answer · edited Jul 10 '13 at 15:38

There are few things you should consider first - storing the image type (is it png, bmp, tiff). I just assumed everything will be jpeg. And perhaps learning something about ASP.NET basics - I sincerely hope you know how to open connection to database for example.

You'll have to create HttpHandler - let's call it ImageHandler.ashx in the root of your web application.

Code will be:

public class ImageHandler : IHttpHandler {
  public bool IsReusable {
    get { return false; }
  }

  public void ProcessRequest(HttpContext context) {
    string firstName = context.Request.QueryString["FirstName"];
    string lastName = context.Request.QueryString["LastName"];
    context.Response.ContentType = "image/jpeg";

    using (var conn = new SqlConnection(@"SERVER=.\SQL2008;Database=Test;Integrated Security=True")) 
    {
      using(var cmd = new SqlCommand("return_userimage", conn))
      {
        cmd.Parameters.Add("@firstname", SqlDbType.Char, 100).Value = firstName;
        cmd.Parameters.Add("@lastname", SqlDbType.Char, 100).Value = lastName;
        var paramImage = cmd.Parameters.Add("@imagedata", SqlDbType.VarBinary);
        paramImage.Direction = ParameterDirection.Output;
        conn.Open();
        cmd.ExecuteNonQuery();
        if (paramImage.Value != null && paramImage.Value != DBNull.Value) {
          byte[] buffer = (byte[])paramImage.Value;
          context.Response.OutputStream.Write(buffer, 0, buffer.Length);
        }
      }
    }
  }
}

This will serve images. Then in your .aspx or .ascx page you can put it like this:

<asp:Image runat="server" 
  ImageUrl="~/ImageHandler.ashx?FirstName=John&LastName=Smith" />

This will call the image handler with first name "John" and last name "Smith". The image handler will serve bytes that represent the image.

Retrieving images stored in SQL Server using C#

1 Answers1