How to : Get Image from SQL database table's image filed in asp.net

Introduction

MS SQL has provided many new data types.Image data type is one of that. which store for example images in any format (.jpg,.bmp,.gif) or any document or file in binary format data. in this article I will show you how to store data into image data type and vice verse how to retrieve those.

For saving image into database we have aspx code as like below.

<asp:FileUpload ID="fl" runat="server" />
<asp:Button ID="btn" runat="server" Text="Save Image" />

Now we have handle button click event for save image.

C#.Net

protected void btn_Click(object sender, System.EventArgs e)
{
    SaveImage();
}

public void SaveImage()
{
    if (fl.HasFile) {
        byte[] productImage = fl.FileBytes;

        string strQuery = null;
        SqlConnection objConnDBConnection = null;
        SqlCommand objCommand = null;
       
        try {
            string connectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString;
            objConnDBConnection = new SqlConnection(connectionString);
            objConnDBConnection.Open();

            strQuery = "insert into customers_details(customer_id,customer_logo)values(@customer_id,@customer_logo)";
            objCommand = new SqlCommand(strQuery, objConnDBConnection);
            objCommand.CommandType = CommandType.Text;
            objCommand.Parameters.AddWithValue("@customer_id", 1);
            objCommand.Parameters.AddWithValue("@customer_logo", productImage);

            int i = objCommand.ExecuteNonQuery();

        } catch (Exception ex) {
        } finally {
            objCommand = null;
            objConnDBConnection.Close();
        }

    }
}

VB.Net

Protected Sub btn_Click(sender As Object, e As System.EventArgs)
    SaveImage()
End Sub

Public Sub SaveImage()
    If fl.HasFile Then
        Dim productImage As Byte() = fl.FileBytes

        Dim strQuery As String = Nothing
        Dim objConnDBConnection As SqlConnection = Nothing
        Dim objCommand As SqlCommand = Nothing
      
        Try
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
            objConnDBConnection = New SqlConnection(connectionString)
            objConnDBConnection.Open()

            strQuery = "insert into customers_details(customer_id,customer_logo)values(@customer_id,@customer_logo)"
            objCommand = New SqlCommand(strQuery, objConnDBConnection)
            objCommand.CommandType = CommandType.Text
            objCommand.Parameters.AddWithValue("@customer_id", 1)
            objCommand.Parameters.AddWithValue("@customer_logo", productImage)

            Dim i As Integer = objCommand.ExecuteNonQuery()
        Catch ex As Exception
        Finally
            objCommand = Nothing
            objConnDBConnection.Close()

        End Try
    End If
End Sub

Half the way we have done. now image is stored in binary format in database. now I m showing how to get that image from database and display in image control.


Here is the code for aspx page for image control

<asp:Image ID="img" runat="server" ImageUrl="ImgHandler.ashx?imgid=1" />

Here is some tricky part . As we know for display image normally we need physical file at location.  Over here we need to create handler for getting image binary data , so need to create ashx file and pass id for customer for which you need to display image. in ashx file you need to create below function.



C#.Net

public void GetImageFromDatabase(HttpContext context)
{
    SqlDataReader rdr = default(SqlDataReader);
    SqlConnection conn = default(SqlConnection);
    SqlCommand selcmd = default(SqlCommand);
    try {
        conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString);
        selcmd = new SqlCommand("select customer_logo from customers_details where customer_id=" + context.Request.QueryString("imgid"), conn);
        conn.Open();
        rdr = selcmd.ExecuteReader();
        while (rdr.Read()) {
            context.Response.ContentType = "image/jpg";
            context.Response.BinaryWrite((byte[])rdr("customer_logo"));
        }
        if (rdr != null) {
            rdr.Close();
        }
    } finally {
        if (conn != null) {
            conn.Close();
        }
    }
}

VB.Net

Public Sub GetImageFromDatabase(context As HttpContext)
    Dim rdr As SqlDataReader = Nothing
    Dim conn As SqlConnection = Nothing
    Dim selcmd As SqlCommand = Nothing
    Try
        conn = New System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        selcmd = New System.Data.SqlClient.SqlCommand("select customer_logo from customers_details where customer_id=" & context.Request.QueryString("imgid"), conn)
        conn.Open()
        rdr = selcmd.ExecuteReader()
        While rdr.Read()
            context.Response.ContentType = "image/jpg"
            context.Response.BinaryWrite(DirectCast(rdr("customer_logo"), Byte()))
        End While
        If rdr IsNot Nothing Then
            rdr.Close()
        End If
    Finally
        If conn IsNot Nothing Then
            conn.Close()
        End If
    End Try
End Sub

 I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff.