Table of Contents
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.
[adinserter block=”2″]
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;
}
[adinserter block=”2″]
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;
}
}
[adinserter block=”2″]
Demo: Retrieve and Displace the image from the database
Happy coding!
Blog Hero