ASP.NET: Insert and Retrieve images using C# and SQL Server Database

Saving and retrieving images have been a hot topic in today’s development world. Too many web applications allow the user to upload images for various reasons. A user can as well retrieve a saved image from the database when needed.

More like this:

ASP.Net: Filter a DropDownList items using JavaScript

This article will learn how to insert and retrieve images in an ASP.NET application backed by a SQL Server database, using the C# language.

Insert/Retrieve image from SQL DataBase using ASP.NET

To get started, we have to create a table of Images. Assume that you have an existing SQL Database; the code to create the table is shown below:

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Images
    (
    Id int NOT NULL IDENTITY (1, 1),
    Name nvarchar(50) NULL,
    Image image NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.ImagesADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    Id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Images SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Create the ASP.NET Application

Now let’s create the ASP.NET application by following the steps below.

1- Open visual studio (For this tutorial, I’m using Visual Studio 2019)

2- Create a new web form project, and give it a name.

3- Select a Web Forms


4- Now add the following control to the Default.aspx page

  • asp:FileUpload, ID =”fiImages”
  • asp:Button, ID =”btnUpload”
  • asp:GridView, ID =”gvImages”

After adding the controls above, your form should look like the image below.

Markup

<div class="jumbotron">
    <asp:FileUpload ID="fiImages" runat="server" /><asp:Button ID="btnUpload" runat="server" Text="Upload Image" />
    <br />
    <br />
    <asp:GridView CssClass="myGrid" ID="gvImages" runat="server">
    </asp:GridView>
</div>

CSS

      .myGrid{
            background-color: #ffffff;
            margin: 2px 0 5px 0;
            border: solid 1px #800000;
            border-collapse: collapse;
            font-family: 'Times New Roman';
            color: #000000;
        }
            .myGridtd {
                padding: 1px;
                border: solid 1px #blue;
            }
            .myGridth {
                padding: 5px 5px;
                color: #ffffff;
                background: #552525;
                border-left: solid 1px #red;
                font-size: 1.3em;
            }

Insert/Save the Image into SQL Database

Now, let create the function to insert an image into the database.

private void InsertImage()
{
    // Check if the user selected an image
    if (fiImages.HasFile)
    {
        // Get the length of the image
        int imageLength = fiImages.PostedFile.ContentLength;

        // create a variable to hold the image in a byte format
        byte[] imageArr = new byte[imageLength];
        HttpPostedFile image = fiImages.PostedFile;
        image.InputStream.Read(imageArr, 0, imageLength);
        
        // Declare and assign the SQL Server connection variable
        SqlConnection  connection = new SqlConnection("Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True");

        // Declare and assign the SQL command variable
        SqlCommand cmd = new SqlCommand("Insert into Images(Name, Image) values (@Name, @Image)", connection);
        
        // Open the connection
        connection.Open();

        //Add the parameters
        cmd.Parameters.AddWithValue("@Name", SqlDbType.VarChar).Value = fiImages.FileName;
        cmd.Parameters.AddWithValue("@Image", SqlDbType.Image).Value = imageArr;

        //Execute the command
        cmd.ExecuteNonQuery();

        // Bind the GridView 
      BindGridView();
    }
}

Now call this function from the BtnUpload click event

protected void btnUpload_Click(object sender, EventArgs e)
{
    try
    {
        InsertImage();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Bind the GridView

Next, we will need to show the records we have in the database table in the GridView. To do so, use the method below:

public void BindGridView()
{
    // Declare and assign the SQL Server connection variable
    SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=myDAtabase;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("Select id, Name, Image from Images", connection);

    // Open the connection
    connection.Open();

    //Defind a dataReader
    SqlDataReader dataReader = cmd.ExecuteReader();

    // Bind the result to the GridView
    gvImages.DataSource = dataReader;
    gvImages.DataBind();
}

Now, call the method above from the Page_Load event

protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        // Load the GridView on Page_Load event
        BindGridView();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Demo: Insert an Image into the database

Retrieve the image from the database

As you have already noticed, the grid we just created has a Display link. Upon clicking on that link, it will retrieve and display the image in a new window. To do so, first, we need to add a Generic Handler to the project.

Adding a Generic Handler to the project

1- Right Click on the project.

2- Select Add

3- Select New Item

4 – Select Generic Handler from the Web Tree Node.

Now, add the SqlClient namespace at the top of the newly added file:

using System.Data.SqlClient;

Then, add the code below to the ProcessRequest Function:

public void ProcessRequest(HttpContext context)
{
    try
    {
        string Id = context.Request.QueryString["Id"].ToString();
        byte[] image;
        //
        SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=myDatabase;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("select Image from Images where id=" + Id, connection);
        connection.Open();

        // Initialize a data reader
        SqlDataReader reader = cmd.ExecuteReader();
        reader.Read();
        image = (Byte[])reader[0];

        // Configure the Response
        context.Response.BinaryWrite(image);
        context.Response.ContentType = "image/jpg";
        context.Response.End();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Demo: Retrieve and Displace the image from the database

Happy coding!

Leave a Reply

Your email address will not be published. Required fields are marked *