ASP.NET store Image in SQL and retrieve for Asp:Image

I am looking to fileupload a picture jpeg,gif,etc into an SQL database on an updateprofilepicture page. Then on the profile page, I want to retrieve the image from an sql database and have it show up in an Asp:Image control. I have much code trying to do this and it doesn't work. The table contains a column of type Image.

Answers


As Joel mentioned you should use an HttpHandler or a page to display the image. Here is a sample code to output image (Image.ashx) :

// ProcessRequest method of Image.ashx
long imageId = Convert.ToInt64(Request.QueryString["ImageId"]);

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand(
    "SELECT ImageFile FROM ImageTable WHERE ImageId = @ImageID", conn))
{
    command.Parameters.Add("@ImageID", SqlDbType.Int).Value = imageId;
    conn.Open();

    Response.ContentType = "image/gif";
    Response.BinaryWrite((byte[]) command.ExecuteScalar());
}

and then use image in your page as :

  <asp:Image id="Image1" runat="server" ImageUrl="Image.ashx?ImageID=12"/>

The important thing to remember here is that you shouldn't try to transmit the image data with the profile page itself. Instead, you want your profile page to generate HTML markup for the browser that looks something like this:

<img src="~/MyImageHandler.ashx?UserID=1234" alt="User 1234 avatar" width="100px" height="150px" />

That is the ultimate result of your <asp:Image .../> control. Then the browser will send a completely separate Http request to retrieve the image. That's how pictures on web sites work. You then need to be able to handle that additional request. To do that, create an Http handler (*.ashx file) and use it to retrieve the appropriate image data from the database and send it to the browser.


If you're using SQL 2005 or greater you should not use the data type Image because it's now deprecated. Instead you want to use the new Varbinary(MAX) type if possible. Once you have it stored all you need to do is retrieve it via ADO.Net call and cast the cell value into type Byte[] and then call Response.BinaryWrite like in ScarletGarden's example above.


After a few hundred gigabytes of images, I believe you'll find yourself thinking that the operating systems' file system and static file http servers is better suited than the database, which is busy which a lot of other details, for storing images. It also allows you to use thousands of existing free tools to work with, move, host, etc the images.


Instead of storing images in the database, store the path and/or filename for the image. Images will fill up the database and make it slow.


protected void Page_Load(object sender, EventArgs e) {
    GridView1.DataSourceID = "";
    GridView1.DataSource = SqlDataSource1;
    GridView1.DataBind();
}

protected void btnSubmit_Click(object sender, EventArgs e) {
    string strImageName = txtImageName.Text.ToString();

    if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "") {
        byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
        HttpPostedFile uploadedImage = FileUpload1.PostedFile;
        uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);

        // Create SQL Connection
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=RND3" + "\\" + "SQLEXPRESS;Initial Catalog=SSSolutionFiles;Integrated Security=True";

        // Create SQL Command 
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "INSERT INTO Imagess(ImageName,Image)" + " VALUES (@ImageName,@Image)";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;

        SqlParameter ImageName = new SqlParameter("@ImageName", SqlDbType.VarChar, 50);
        ImageName.Value = strImageName.ToString();
        cmd.Parameters.Add(ImageName);

        SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
        UploadedImage.Value = imageSize;
        cmd.Parameters.Add(UploadedImage);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        Label1.Text = "File Uploaded";

        GridView1.DataSourceID = "";
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
        con.Close();
    }
}

Try these links it might help you..

you can also try by storing the image files on the server and store the paths on the Sql table.. by these links

http://pratikataspdotnet.blogspot.in/2014/11/retrieve-images-from-path-stored-in.html


Need Your Help

Why isn't CTRL-D signaling EOF with this program?

c sockets unix stdin eof

When I run this program I enter in an ip address and the server port as input. However, after this the program asks me to "Please enter msg: ". However, after typing my message pressing ctrl-D do...

Unknown server tag 'CR:CrystalReportViewer'

c# asp.net .net-4.0 crystal-reports

I am working on VS2010, Crystal Reports. While compiling my code I am getting below error: