Introduction: In this article I will
explain how to upload the image through file upload control and save the
image in website root folder and store the image path in Sql server database
and then retrieve the record along with image according to image path and
display in DataList data control in asp.net using both C# and VB
languages. - Upload image through asp.net
fileupload control and store that image in folder.
- Save uploaded image path in Sql server database.
- Read/retrieve the image from the image path stored in
database and show in DataList data control.
In this demo application, I will save Employee details in database with image. Then Retrieve the details along with image and show in DataList.
Create a table "EmployeeDetail" in Sql
server Database as shown below:
CREATE TABLE [dbo].[EmployeeDetail](
[EmpId]
[int] NULL,
[Name]
[varchar](50) NULL,
[Department]
[varchar](50) NULL,
[Designation]
[varchar](50) NULL,
[salary]
[decimal](18, 2) NULL,
[PicName]
[varchar](100) NULL,
[PicPath]
[varchar](500) NULL
)
Then
create the following stored procedure in Sql server database
Stored
procedure to save Employee record in Database
CREATE PROCEDURE [dbo].[InsertEmpDetails_Sp]
@Name VARCHAR(100),
@dept VARCHAR(100),
@Desig VARCHAR(100),
@Salary DECIMAL(18,2),
@PicName VARCHAR(100)=NULL,
@PicPath VARCHAR(200)=NULL
AS
BEGIN
SET
NOCOUNT ON;
INSERT
INTO EmployeeDetail(Name,Department,Designation,salary,PicName,PicPath)
VALUES (@Name,@dept,@Desig,@Salary,@PicName,@PicPath)
END
Stored
procedure to fetch the Employee record from Database
CREATE PROCEDURE [dbo].[GetEmpDetails_Sp]
AS
BEGIN
SET
NOCOUNT ON;
SELECT
* FROM
EmployeeDetail
END
Create
Connection String In WebConfig file:
<connectionStrings>
<add name="conStr" connectionString="Data
Source=localhost;Initial Catalog=Test;Integrated Security=True"/>
</connectionStrings>
Source Code:
Create a folder "Images" in the root directory of project
to upload Employee Images. 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 DataList.
Design
Section:
Create
the page as:
<fieldset style="width:550px;">
<legend>Save and
retrieve image from database</legend>
<table>
<tr><td>Name: </td><td><asp:TextBox ID="txtName"
runat="server"></asp:TextBox></td>
</tr>
<tr><td>Department: </td><td><asp:TextBox ID="txtDept"
runat="server"></asp:TextBox></td></tr>
<tr><td>Designation: </td><td><asp:TextBox ID="txtDesig"
runat="server"></asp:TextBox></td></tr>
<tr><td>Salary: </td><td><asp:TextBox ID="txtSalary"
runat="server"></asp:TextBox></td></tr>
<tr><td>Employee Pic:
</td><td>
<asp:FileUpload ID="flupEmpPic" runat="server" /></td></tr>
<tr><td></td><td>
<asp:Button ID="btnSave"
runat="server"
Text="Save"
onclick="btnSave_Click"
/>
<asp:Button ID="btnCancel"
runat="server"
onclick="btnCancel_Click"
Text="Cancel"
/>
</td></tr>
<tr><td> </td><td>
<asp:Label ID="lblStatus"
runat="server"></asp:Label>
</td></tr>
<tr><td colspan="2">
<asp:DataList ID="dlEmp" runat="server" RepeatColumns="2" RepeatDirection="Horizontal" >
<ItemTemplate>
<table>
<tr>
<td
align="center">
<asp:Image ID="ImgEmpPic"
runat="server"
Height="100px"
Width="100px"
/><br />
</td>
<td>
<b>Name:</b> <%#Eval("Name")%><br />
<b>department:</b> <%#Eval("Department")%><br />
<b>Designation:</b> <%#Eval("Designation")%><br />
<b>Salary:</b> <%#Eval("Salary")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</td></tr>
</table>
</fieldset>
Asp.Net C# Code to
store image and data and show in DataList
Write
the code as:
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
using System.IO;
public partial class Upload : System.Web.UI.Page
{
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
protected
void Page_Load(object
sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindDataList();
}
}
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("InsertEmpDetails_Sp",
con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name",
txtName.Text.Trim());
cmd.Parameters.AddWithValue("@Dept",
txtDept.Text.Trim());
cmd.Parameters.AddWithValue("@Desig",
txtDesig.Text.Trim());
cmd.Parameters.AddWithValue("@Salary",
Convert.ToDecimal(txtSalary.Text));
try
{
if (flupEmpPic.HasFile)
{
fileName =
flupEmpPic.FileName;
filePath =
Server.MapPath("Images/" + System.Guid.NewGuid() + fileName);
flupEmpPic.SaveAs(filePath);
cmd.Parameters.AddWithValue("@PicName", fileName);
int getPos = filePath.LastIndexOf("\\");
int len = filePath.Length;
getPath =
filePath.Substring(getPos, len - getPos);
pathToStore =
getPath.Remove(0, 1);
finalPathToStore = "~/Images/"
+ pathToStore;
cmd.Parameters.AddWithValue("@PicPath",
finalPathToStore);
}
con.Open();
cmd.ExecuteNonQuery();
lblStatus.Text = "Employee Record saved successfully";
lblStatus.ForeColor
= System.Drawing.Color.Green;
ClearControls();
BindDataList();
}
catch (Exception ex)
{
lblStatus.Text = "Employee 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 BindDataList()
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("GetEmpDetails_Sp",
con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
try
{
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
dlEmp.DataSource
= dt;
dlEmp.DataBind();
for (int i = 0; i
< dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["PicPath"])))
{
Image img = (Image)dlEmp.Controls[i].FindControl("ImgEmpPic");
img.ImageUrl = Convert.ToString(dt.Rows[i]["PicPath"]);
}
}
}
}
catch (Exception)
{
lblStatus.Text = "Employee 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()
{
txtDept.Text = string.Empty;
txtName.Text = string.Empty;
txtSalary.Text = string.Empty;
txtDesig.Text = string.Empty;
txtName.Focus();
}
}
Asp.Net VB Section:
Design the page as:
<fieldset style="width:550px;">
<legend>Save and
retrieve image from database</legend>
<table>
<tr><td>Name: </td><td><asp:TextBox ID="txtName"
runat="server"></asp:TextBox></td>
</tr>
<tr><td>Department: </td><td><asp:TextBox ID="txtDept"
runat="server"></asp:TextBox></td></tr>
<tr><td>Designation: </td><td><asp:TextBox ID="txtDesig"
runat="server"></asp:TextBox></td></tr>
<tr><td>Salary: </td><td><asp:TextBox ID="txtSalary"
runat="server"></asp:TextBox></td></tr>
<tr><td>Employee Pic:
</td><td>
<asp:FileUpload ID="flupEmpPic" runat="server" /></td></tr>
<tr><td></td><td>
<asp:Button ID="btnSave" runat="server" Text="Save" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" />
</td></tr>
<tr><td> </td><td>
<asp:Label ID="lblStatus"
runat="server"></asp:Label>
</td></tr>
<tr><td colspan="2">
<asp:DataList ID="dlEmp" runat="server">
<ItemTemplate>
<table>
<tr>
<td align="center">
<asp:Image ID="ImgEmpPic"
runat="server"
Height="100px"
Width="100px"
/><br />
</td>
<td>
<b>Employee Name:</b> <%#Eval("Name")%><br />
<b>department:</b> <%#Eval("Department")%><br />
<b>Designation:</b> <%#Eval("Designation")%><br />
<b>Salary:</b> <%#Eval("Salary")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</td></tr>
</table>
</fieldset>
Asp.Net (VB.NET) Code to
store image and data and show in DataList
Write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Partial Public Class Upload
Inherits
System.Web.UI.Page
Dim con As SqlConnection =
New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
protected
void Page_Load(Object sender, EventArgs e)
{
if (Not
Page.IsPostBack)
{
BindDataList()
}
}
protected
void btnSave_Click(Object sender, EventArgs e)
{
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 SqlCommand = New SqlCommand("InsertEmpDetails_Sp", con)
cmd.CommandType =
CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim())
cmd.Parameters.AddWithValue("@Dept",
txtDept.Text.Trim())
cmd.Parameters.AddWithValue("@Desig", txtDesig.Text.Trim())
cmd.Parameters.AddWithValue("@Salary",
Convert.ToDecimal(txtSalary.Text))
Try
if
(flupEmpPic.HasFile)
{
fileName = flupEmpPic.FileName
filePath =
Server.MapPath("Images/" + System.Guid.NewGuid() + fileName)
flupEmpPic.SaveAs(filePath)
cmd.Parameters.AddWithValue("@PicName", 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 =
"~/Images/" + pathToStore
cmd.Parameters.AddWithValue("@PicPath",
finalPathToStore)
}
con.Open()
cmd.ExecuteNonQuery()
lblStatus.Text = "Employee
Record saved successfully"
lblStatus.ForeColor =
System.Drawing.Color.Green
ClearControls()
BindDataList()
Catch
ex As Exception
lblStatus.Text = "Employee
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
}
private
void BindDataList()
{
Dim dt As DataTable = New DataTable()
Dim cmd As SqlCommand = New SqlCommand("GetEmpDetails_Sp", con)
cmd.CommandType =
CommandType.StoredProcedure
Dim adp As SqlDataAdapter
= New SqlDataAdapter(cmd)
Try
adp.Fill(dt)
if
(dt.Rows.Count > 0)
{
dlEmp.DataSource = dt
dlEmp.DataBind()
Dim i As Integer
For i = 0 To dt.Rows.Count- 1 Step i + 1
if
(Not String.IsNullOrEmpty(Convert.ToString(dt.Rows(i)("PicPath"))))
{
Dim img As Image = CType(dlEmp.Controls(i).FindControl("ImgEmpPic"), Image)
img.ImageUrl =
Convert.ToString(dt.Rows(i)("PicPath"))
}
Next
}
Catch
lblStatus.Text = "Employee
record could not be retrieved"
lblStatus.ForeColor =
System.Drawing.Color.Red
Finally
con.Close()
dt.Clear()
dt.Dispose()
cmd.Dispose()
End Try
}
protected
void btnCancel_Click(Object sender, EventArgs e)
{
ClearControls()
lblStatus.Text = String.Empty
}
private
void ClearControls()
{
txtDept.Text = String.Empty
txtName.Text = String.Empty
txtSalary.Text = String.Empty
txtDesig.Text = String.Empty
txtName.Focus()
}
End Class
0 comments:
Post a Comment