Upload image in folder and save path in Sql server database and read, bind in Gridview in Asp.Net C#, VB ~ Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

upload and save image path in database and display in gridview

Click on image to enlarge

Introduction: In this article i am going to share the code to upload the image in website root folder through file upload control and save/store the image path in Sql server database and then read/retrieve the record along with image from image path and display in GridView data control in asp.net using both C# and VB languages.

Description: Basically you will learn the following through this article.

How to upload image through asp.net fileupload control in folder?
How to store uploaded image path in Sql server database?
How to read/retrieve the image from the folder by reading the image path stored in sql server database and show in GridView data control?

In previous articles i explained How to Upload and store image in binary format in Sql server and bind to Gridview and Save image in folder and path in Sql and retrieve and bind to Repeater and Save image in binary format in Sql server database and bind to DataList and Bind,upload,download,delete image files from the GridView and jQuery to validate file extension and upload image file and Drag & drop to upload multiple files using AjaxFileUpload like Facebook and Maintain image on PostBack in FileUpload control

I have created a demo web page from where i will insert book record like Book name, its author, publisher, price and Book picture. As soon as a new record will be inserted it will get binded to the GridView control as show in the sample image shown above.

Implementation: Let's create a sample website page to insert data including image and retrieve that data and fill in Grid View data controls.

First of all create a table "BookDetails" in Sql server Database with the columns and data type as shown in below:

Column Name	Data Type
BookId	Int (Primary key. Set Is Identity=true)
BookName	varchar(100)
Author	varchar(100)
Publisher	varchar(100)
Price	decimal(18, 2)
BookPicName	varchar(100)
BookPicPath	varchar(200)

Then create the following stored procedure in Sql server database

Stored procedure to save Book record in Database

CREATE PROCEDURE [dbo].[InsertBookDetails_Sp]

@BookName VARCHAR(100),

@Author VARCHAR(100),

@Publisher VARCHAR(100),

@Price DECIMAL(18,2),

@BookPicName VARCHAR(100)=NULL,

@BookPicPath VARCHAR(200)=NULL

BEGIN

SET NOCOUNT ON;

INSERT INTO BookDetails(BookName,Author,Publisher,Price,,BookPicName,BookPicPath)

VALUES (@BookName,@Author,@Publisher,@Price,@BookPicName,@BookPicPath)

END

Stored procedure to fetch the Book record from Database

CREATE PROCEDURE [dbo].[GetBookDetails_Sp]

BEGIN

SET NOCOUNT ON;

SELECT * FROM BookDetails

END

In the web.config file create the connection string to connect our asp.net website to Sql server database as:

</connectionStrings>

Note: Replace the Data Source and Initial catalog as per your application

Asp.Net C# Section:

Create a folder in the root directory of the project and name it "BookPictures". Uploaded Book images will be stored in this folder and the path will be saved into the database so that we can read the image path and get the image from the folder to display in gridview.

In the <Form> tag of the Asp.Net design page (Default.aspx) create the page as:

<legend>Save and retrieve image from database</legend>

<table>

</tr>

<tr><td>Author: </td><td><asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td></tr>

<tr><td>Publisher: </td><td><asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td></tr>

<tr><td>Price: </td><td><asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td></tr>

<tr><td>Book Picture: </td><td>

<asp:FileUpload ID="flupBookPic" runat="server" /></td></tr>

<asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />

<asp:Button ID="btnCancel" runat="server" onclick="btnCancel_Click"

Text="Cancel" />

</td></tr>

<asp:Label ID="lblStatus" runat="server"></asp:Label>

</td></tr>

<asp:GridView ID="grdBooks" runat="server" AutoGenerateColumns="false">

<asp:TemplateField>

<asp:Label ID="lblBookPicName" runat="server" Text='<%#Eval("BookPicName") %>'></asp:Label>

</center>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="BookName" HeaderText="Book Name" ItemStyle-HorizontalAlign="Center"/>

<asp:BoundField DataField="Author" HeaderText="Author" ItemStyle-HorizontalAlign="Center" />

<asp:BoundField DataField="Publisher" HeaderText="Publisher" ItemStyle-HorizontalAlign="Center" />

<asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-HorizontalAlign="Center" />

</Columns>

</asp:GridView>

</td></tr>

</table>

</fieldset>

Asp.Net C# Code to store image path in database, then retrieve and show in Gridview

In the code behind file (default.aspx.cs) write the code as:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.IO;

public partial class default : System.Web.UI.Page

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindGridView();

}

protected void btnSave_Click(object sender, EventArgs e)

{

string fileName = string.Empty;

string filePath = string.Empty;

string getPath = string.Empty;

string pathToStore = string.Empty;

string finalPathToStore = string.Empty;

SqlCommand cmd = new SqlCommand("InsertBookDetails_Sp", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@BookName", txtBookName.Text.Trim());

cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim());

cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim());

cmd.Parameters.AddWithValue("@Price", Convert.ToDecimal(txtPrice.Text));

try

{

if (flupBookPic.HasFile)

{

fileName = flupBookPic.FileName;

filePath = Server.MapPath("BookPictures/" + System.Guid.NewGuid() + fileName);

flupBookPic.SaveAs(filePath);

cmd.Parameters.AddWithValue("@BookPicName", fileName);

int getPos = filePath.LastIndexOf("\\");

int len = filePath.Length;

getPath = filePath.Substring(getPos, len - getPos);

pathToStore = getPath.Remove(0, 1);

finalPathToStore = "~/BookPictures/" + pathToStore;

cmd.Parameters.AddWithValue("@BookPicPath", finalPathToStore);

}

con.Open();

cmd.ExecuteNonQuery();

lblStatus.Text = "Book Record saved successfully";

lblStatus.ForeColor = System.Drawing.Color.Green;

ClearControls();

BindGridView();

}

catch (Exception ex)

{

lblStatus.Text = "Book Record could not be saved";

lblStatus.ForeColor = System.Drawing.Color.Red;

}

finally

{

con.Close();

cmd.Dispose();

fileName = null;

filePath = null;

getPath = null;

pathToStore = null;

finalPathToStore = null;

}

private void BindGridView()

{

DataTable dt = new DataTable();

SqlCommand cmd = new SqlCommand("GetBookDetails_Sp", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adp = new SqlDataAdapter(cmd);

try

{

adp.Fill(dt);

if (dt.Rows.Count > 0)

{

grdBooks.DataSource = dt;

grdBooks.DataBind();

for (int i = 0; i < dt.Rows.Count; i++)

{

if (!string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["BookPicPath"])))

{

Image img = (Image)grdBooks.Rows[i].FindControl("ImgBookPic");

img.ImageUrl=Convert.ToString(dt.Rows[i]["BookPicPath"]);

}

catch (Exception)

{

lblStatus.Text = "Book record could not be retrieved";

lblStatus.ForeColor = System.Drawing.Color.Red;

}

finally

{

con.Close();

dt.Clear();

dt.Dispose();

cmd.Dispose();

}

protected void btnCancel_Click(object sender, EventArgs e)

{

ClearControls();

lblStatus.Text = string.Empty;

}

private void ClearControls()

{

txtAuthor.Text = string.Empty;

txtBookName.Text = string.Empty;

txtPrice.Text = string.Empty;

txtPublisher.Text = string.Empty;

txtBookName.Focus();

}

Asp.Net VB Code to store image path in database, then retrieve the image and show in Gridview

In the code behind file(default.aspx.vb) write the code as:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.IO

Partial Class default

Inherits System.Web.UI.Page

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

If Not Page.IsPostBack Then

BindGridView()

End If

End Sub

Protected Sub btnSave_Click(sender As Object, e As EventArgs)

Dim fileName As String = String.Empty

Dim filePath As String = String.Empty

Dim getPath As String = String.Empty

Dim pathToStore As String = String.Empty

Dim finalPathToStore As String = String.Empty

Dim cmd As New SqlCommand("InsertBookDetails_Sp", con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@BookName", txtBookName.Text.Trim())

cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim())

cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim())

cmd.Parameters.AddWithValue("@Price", Convert.ToDecimal(txtPrice.Text))

Try

If flupBookPic.HasFile Then

fileName = flupBookPic.FileName

filePath = Server.MapPath("BookPictures/" & Convert.ToString(System.Guid.NewGuid()) & fileName)

flupBookPic.SaveAs(filePath)

cmd.Parameters.AddWithValue("@BookPicName", fileName)

Dim getPos As Integer = filePath.LastIndexOf("\")

Dim len As Integer = filePath.Length

getPath = filePath.Substring(getPos, len - getPos)

pathToStore = getPath.Remove(0, 1)

finalPathToStore = "~/BookPictures/" & pathToStore

cmd.Parameters.AddWithValue("@BookPicPath", finalPathToStore)

End If

con.Open()

cmd.ExecuteNonQuery()

lblStatus.Text = "Book Record saved successfully"

lblStatus.ForeColor = System.Drawing.Color.Green

ClearControls()

BindGridView()

Catch ex As Exception

lblStatus.Text = "Book Record could not be saved"

lblStatus.ForeColor = System.Drawing.Color.Red

Finally

con.Close()

cmd.Dispose()

fileName = Nothing

filePath = Nothing

getPath = Nothing

pathToStore = Nothing

finalPathToStore = Nothing

End Try

End Sub

Private Sub BindGridView()

Dim dt As New DataTable()

Dim cmd As New SqlCommand("GetBookDetails_Sp", con)

cmd.CommandType = CommandType.StoredProcedure

Dim adp As New SqlDataAdapter(cmd)

Try

adp.Fill(dt)

If dt.Rows.Count > 0 Then

grdBooks.DataSource = dt

grdBooks.DataBind()

For i As Integer = 0 To dt.Rows.Count - 1

If Not String.IsNullOrEmpty(Convert.ToString(dt.Rows(i)("BookPicPath"))) Then

Dim img As Image = DirectCast(grdBooks.Rows(i).FindControl("ImgBookPic"), Image)

img.ImageUrl = Convert.ToString(dt.Rows(i)("BookPicPath"))

End If

Catch ex As Exception

lblStatus.Text = "Book record could not be retrieved"

lblStatus.ForeColor = System.Drawing.Color.Red

Finally

con.Close()

dt.Clear()

dt.Dispose()

cmd.Dispose()

End Try

End Sub

Protected Sub btnCancel_Click(sender As Object, e As EventArgs)

ClearControls()

lblStatus.Text = String.Empty

End Sub

Private Sub ClearControls()

txtAuthor.Text = String.Empty

txtBookName.Text = String.Empty

txtPrice.Text = String.Empty

txtPublisher.Text = String.Empty

txtBookName.Focus()

End Sub

End Class

Now over to you:

" I hope you have got the way to upload image in folder , store image path in database and retrieve the image path from sql server database and display in GridView using asp.net and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..

Upload image in folder and save path in Sql server database and read, bind in Gridview in Asp.Net C#, VB

Related Post