In this article i have explain insert ,delete, update and retrieve data from database using asp.net and c#. To implementing this process i have take the product table for performing the create product, delete particular product, retrieve all product and update product details operations.
1. Create product table :
create table tblProduct(
id int identity(1,1) primary key,
title nvarchar(200),
code nvarchar(100),
quantity int ,
price int ,
decription nvarchar(500),
image nvarchar(200)
);
2. Create new project in visual studio and Create the product class and define properties of product in your project.
public class Product
{
public int id { get; set; }
public string title { get; set; }
public string code { get; set; }
public int quantity { get; set; }
public int price { get; set; }
public string decription { get; set; }
public string image { get; set; }
}
3. Add connection string into web.config file
<appSettings>
<add key="ConnectionString" value="Data Source=LAPTOP-ISNBQ76D;Initial Catalog=tutorial;Integrated Security=True"/>
<add key="FolderPath" value="uploads"/>
</appSettings>
4. Create the product business class ProductBL for performing insert,delete,update and retrieve operation.
public class ProductBL
{
public static string StringCon = null;
SqlConnection conn = null;
public ProductBL()
{
StringCon = ConfigurationSettings.AppSettings["ConnectionString"];
conn = new SqlConnection(StringCon);
}
public bool insertProduct(Product p)
{
try
{
conn.Open();
string sql = "insert into tblProduct(" +
"title," +
"code," +
"quantity," +
"price," +
"decription," +
"image" +
") values(" +
"@title," +
"@code," +
"@quantity," +
"@price," +
"@decription," +
"@image" +
")";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@code", p.code);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@decription", p.decription);
cmd.Parameters.AddWithValue("@image", p.image);
int i = cmd.ExecuteNonQuery();
if(i>0)
{
return true;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return false;
}
public bool updateProduct(Product p)
{
try
{
conn.Open();
string sql = "update tblProduct set " +
"title=@title," +
"code=@code," +
"quantity=@quantity," +
"price=@price," +
"decription=@decription," +
"image=@image where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@code", p.code);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@decription", p.decription);
cmd.Parameters.AddWithValue("@image", p.image);
cmd.Parameters.AddWithValue("@id", p.id);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
return true;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return false;
}
public List<Product> GetProducts()
{
List<Product> pList = new List<Product>();
try
{
conn.Open();
string sql = "select * from tblProduct";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sRd = cmd.ExecuteReader();
if (sRd.HasRows)
{
while(sRd.Read())
{
Product p = new Product();
p.id = (int)sRd["id"];
p.title = sRd["title"].ToString();
p.code = sRd["code"].ToString();
p.quantity =(int)sRd["quantity"];
p.price =(int)sRd["price"];
p.decription = sRd["decription"].ToString();
p.image = sRd["image"].ToString();
pList.Add(p);
}
return pList;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return pList;
}
public Product productDetails(int pId)
{
Product p = new Product();
try
{
conn.Open();
string sql = "select * from tblProduct";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sRd = cmd.ExecuteReader();
if (sRd.HasRows)
{
sRd.Read();
p.id = (int)sRd["id"];
p.title = sRd["title"].ToString();
p.code = sRd["code"].ToString();
p.quantity = (int)sRd["quantity"];
p.price = (int)sRd["price"];
p.decription = sRd["decription"].ToString();
p.image = sRd["image"].ToString();
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return p;
}
public bool deleteProduct(int pId)
{
try
{
conn.Open();
string sql = "delete from tblProduct where id="+pId+"";
SqlCommand cmd = new SqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
if(i>0)
{
return true;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return false;
}
}
5. Create the web form and add the following code to web form
For creating the new product add following code
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CURD Operation In Asp.net</title>
<link rel="stylesheet" href="css/bootstrap.min.css" />
<style>
.input-box{
width: 100%;
padding: 1rem;
border-radius: 5px;
margin: .5rem;
border:1px solid #ddd;
}
.btn_{
padding: 1rem 3rem;
border: 1px solid;
border-radius: 5px;
margin: .5rem .5rem;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div class="row">
<div class="col-lg-12">
<div class="panel-heading">
<h2>Add Product Details</h2>
<asp:Label ID="lblWarning" runat="server" style="color:red;"></asp:Label>
</div>
</div>
</div>
<div class="row">
<div class="col-lg-6">
<input type="text" id="txtTitle" runat="server" placeholder="Title" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtCode" runat="server" placeholder="Code" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtQty" runat="server" placeholder="Quantity" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtPrice" runat="server" placeholder="Price" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtDescription" runat="server" placeholder="Description" class="input-box"/>
</div>
<div class="col-lg-6">
Image : <asp:FileUpload ID="productImage" runat="server" />
</div>
</div>
<div class="row">
<div class="col-lg-6">
<asp:Button ID="btnSave" runat="server" class="btn_ btn-primary" Text="Save" OnClick="btnSave_Click" />
</div>
<div class="col-lg-6">
<asp:Button ID="btnReset" runat="server" class="btn_ btn-danger" Text="Reset" />
</div>
</div>
</div>
</form>
</body>
</html>
1. Create product table :
create table tblProduct(
id int identity(1,1) primary key,
title nvarchar(200),
code nvarchar(100),
quantity int ,
price int ,
decription nvarchar(500),
image nvarchar(200)
);
2. Create new project in visual studio and Create the product class and define properties of product in your project.
public class Product
{
public int id { get; set; }
public string title { get; set; }
public string code { get; set; }
public int quantity { get; set; }
public int price { get; set; }
public string decription { get; set; }
public string image { get; set; }
}
3. Add connection string into web.config file
<appSettings>
<add key="ConnectionString" value="Data Source=LAPTOP-ISNBQ76D;Initial Catalog=tutorial;Integrated Security=True"/>
<add key="FolderPath" value="uploads"/>
</appSettings>
4. Create the product business class ProductBL for performing insert,delete,update and retrieve operation.
public class ProductBL
{
public static string StringCon = null;
SqlConnection conn = null;
public ProductBL()
{
StringCon = ConfigurationSettings.AppSettings["ConnectionString"];
conn = new SqlConnection(StringCon);
}
public bool insertProduct(Product p)
{
try
{
conn.Open();
string sql = "insert into tblProduct(" +
"title," +
"code," +
"quantity," +
"price," +
"decription," +
"image" +
") values(" +
"@title," +
"@code," +
"@quantity," +
"@price," +
"@decription," +
"@image" +
")";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@code", p.code);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@decription", p.decription);
cmd.Parameters.AddWithValue("@image", p.image);
int i = cmd.ExecuteNonQuery();
if(i>0)
{
return true;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return false;
}
public bool updateProduct(Product p)
{
try
{
conn.Open();
string sql = "update tblProduct set " +
"title=@title," +
"code=@code," +
"quantity=@quantity," +
"price=@price," +
"decription=@decription," +
"image=@image where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@code", p.code);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@decription", p.decription);
cmd.Parameters.AddWithValue("@image", p.image);
cmd.Parameters.AddWithValue("@id", p.id);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
return true;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return false;
}
public List<Product> GetProducts()
{
List<Product> pList = new List<Product>();
try
{
conn.Open();
string sql = "select * from tblProduct";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sRd = cmd.ExecuteReader();
if (sRd.HasRows)
{
while(sRd.Read())
{
Product p = new Product();
p.id = (int)sRd["id"];
p.title = sRd["title"].ToString();
p.code = sRd["code"].ToString();
p.quantity =(int)sRd["quantity"];
p.price =(int)sRd["price"];
p.decription = sRd["decription"].ToString();
p.image = sRd["image"].ToString();
pList.Add(p);
}
return pList;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return pList;
}
public Product productDetails(int pId)
{
Product p = new Product();
try
{
conn.Open();
string sql = "select * from tblProduct";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sRd = cmd.ExecuteReader();
if (sRd.HasRows)
{
sRd.Read();
p.id = (int)sRd["id"];
p.title = sRd["title"].ToString();
p.code = sRd["code"].ToString();
p.quantity = (int)sRd["quantity"];
p.price = (int)sRd["price"];
p.decription = sRd["decription"].ToString();
p.image = sRd["image"].ToString();
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return p;
}
public bool deleteProduct(int pId)
{
try
{
conn.Open();
string sql = "delete from tblProduct where id="+pId+"";
SqlCommand cmd = new SqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
if(i>0)
{
return true;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return false;
}
}
5. Create the web form and add the following code to web form
For creating the new product add following code
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CURD Operation In Asp.net</title>
<link rel="stylesheet" href="css/bootstrap.min.css" />
<style>
.input-box{
width: 100%;
padding: 1rem;
border-radius: 5px;
margin: .5rem;
border:1px solid #ddd;
}
.btn_{
padding: 1rem 3rem;
border: 1px solid;
border-radius: 5px;
margin: .5rem .5rem;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div class="row">
<div class="col-lg-12">
<div class="panel-heading">
<h2>Add Product Details</h2>
<asp:Label ID="lblWarning" runat="server" style="color:red;"></asp:Label>
</div>
</div>
</div>
<div class="row">
<div class="col-lg-6">
<input type="text" id="txtTitle" runat="server" placeholder="Title" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtCode" runat="server" placeholder="Code" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtQty" runat="server" placeholder="Quantity" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtPrice" runat="server" placeholder="Price" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtDescription" runat="server" placeholder="Description" class="input-box"/>
</div>
<div class="col-lg-6">
Image : <asp:FileUpload ID="productImage" runat="server" />
</div>
</div>
<div class="row">
<div class="col-lg-6">
<asp:Button ID="btnSave" runat="server" class="btn_ btn-primary" Text="Save" OnClick="btnSave_Click" />
</div>
<div class="col-lg-6">
<asp:Button ID="btnReset" runat="server" class="btn_ btn-danger" Text="Reset" />
</div>
</div>
</div>
</form>
</body>
</html>
6. Add the following c# code to code behind c# class of web form
public partial class CURD : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSave_Click(object sender, EventArgs e)
{
Product p = new Product();
p.title = txtTitle.Value.Trim();
p.code = txtCode.Value.Trim();
p.quantity = Int32.Parse(txtQty.Value.Trim());
p.price = Int32.Parse(txtPrice.Value.Trim());
p.decription = txtDescription.Value.Trim();
p.image = saveImage(productImage);
ProductBL pBL = new ProductBL();
if (pBL.insertProduct(p))
{
Response.Redirect("products.aspx");
} else
{
lblWarning.Text = "Error Occor !";
}
}
protected string saveImage(FileUpload imageFile)
{
string FolderPath = "";
if (imageFile.HasFile)
{
//Get the name of image
string FileName = imageFile.PostedFile.FileName;
//Get the Extension of image
string Extension = Path.GetExtension(imageFile.PostedFile.FileName);
//Get The folder path from web.config file
FolderPath = ConfigurationManager.AppSettings["FolderPath"];
FolderPath += "/" + System.DateTime.Now.Second + FileName;
//Get the server path
string FilePath = Server.MapPath(FolderPath);
//Save image to uploader
imageFile.SaveAs(FilePath);
}
return FolderPath;
}
}
The product details save into database.
7. Display the all products details.
For display the product list to add the another web form and add the following code on it .
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CURD Operation In Asp.net</title>
<link rel="stylesheet" href="css/bootstrap.min.css" />
<style>
table{
width:100%;
}
img{
width:100px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div class="row">
<div class="col-lg-12">
<h2>Products List</h2>
<a href="CURD.aspx" >Add New Product</a>
<asp:Panel ID="pnlProducts" runat="server"></asp:Panel>
</div>
</div>
</div>
</form>
</body>
</html>
Now add the c# code into web form cs file
public partial class Products : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
setProductToPnl();
}
protected void setProductToPnl()
{
ProductBL pBL = new ProductBL();
List<Product> pList = pBL.GetProducts();
string html = "";
if (pList.Count>0)
{
html = "<table>" +
"<tr>" +
"<th>Sr. No</th>" +
"<th>Image</th>" +
"<th>Title</th>" +
"<th>Quantity</th>" +
"<th>Price</th>" +
"<th>Action</th>" +
"</tr>";
int c = 0;
foreach (Product p in pList)
{
html += "<tr>" +
"<td>" + (++c) + "</td>" +
"<td><img src='"+p.image+"'></td>" +
"<td>"+p.title+"</td>" +
"<td>" + p.quantity + "</td>" +
"<td>" + p.price + "</td>" +
"<td><a href='edit-product.aspx?id=" + p.id + "' title='Delete Product'> Edit </a> | <a href='delete-product.aspx?id=" + p.id+"' title='Delete Product'> Delete </a> </td>" +
"</tr>";
}
html += "</table>";
} else
{
html = "<h2>Record Not Found !</h2>";
}
pnlProducts.Controls.Add(new LiteralControl(html));
}
}
8. Update the product details
For update the product details add the new web form edit-product.aspx page and add the following code into web form.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CURD Operation In Asp.net</title>
<link rel="stylesheet" href="css/bootstrap.min.css" />
<style>
.input-box{
width: 100%;
padding: 1rem;
border-radius: 5px;
margin: .5rem;
border:1px solid #ddd;
}
.btn_{
padding: 1rem 3rem;
border: 1px solid;
border-radius: 5px;
margin: .5rem .5rem;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div class="row">
<div class="col-lg-12">
<div class="panel-heading">
<h2>Edit Product Details</h2>
<asp:Label ID="lblWarning" runat="server" style="color:red;"></asp:Label>
</div>
</div>
</div>
<div class="row">
<div class="col-lg-6">
<input type="text" id="txtTitle" runat="server" placeholder="Title" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtCode" runat="server" placeholder="Code" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtQty" runat="server" placeholder="Quantity" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtPrice" runat="server" placeholder="Price" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtDescription" runat="server" placeholder="Description" class="input-box"/>
</div>
<div class="col-lg-6">
Image : <asp:FileUpload ID="productImage" runat="server" />
</div>
</div>
<div class="row">
<div class="col-lg-6">
<asp:Button ID="btnEdit" runat="server" class="btn_ btn-primary" Text="Update" OnClick="btnEdit_Click" />
</div>
<div class="col-lg-6">
<asp:Button ID="btnReset" runat="server" class="btn_ btn-danger" Text="Reset" />
</div>
</div>
</div>
</form>
</body>
</html>
Now add the following c# code into web from cs file
public partial class edit_product : System.Web.UI.Page
{
int pId;
static string imgpath = "";
ProductBL pBL = new ProductBL();
protected void Page_Load(object sender, EventArgs e)
{
pId = Int32.Parse(Request["id"]);
if(!IsPostBack)
{
setData();
}
}
protected void setData()
{
Product p = pBL.productDetails(pId);
txtTitle.Value = p.title;
txtCode.Value = p.code;
txtQty.Value = p.quantity.ToString();
txtPrice.Value = p.price.ToString();
txtDescription.Value = p.decription;
imgpath = p.image;
}
protected string saveImage(FileUpload imageFile)
{
string FolderPath = "";
if (imageFile.HasFile)
{
//Get the name of image
string FileName = imageFile.PostedFile.FileName;
//Get the Extension of image
string Extension = Path.GetExtension(imageFile.PostedFile.FileName);
//Get The folder path from web.config file
FolderPath = ConfigurationManager.AppSettings["FolderPath"];
FolderPath += "/" + System.DateTime.Now.Second + FileName;
//Get the server path
string FilePath = Server.MapPath(FolderPath);
//Save image to uploader
imageFile.SaveAs(FilePath);
}
return FolderPath;
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Product p = new Product();
p.id = pId;
p.title = txtTitle.Value.Trim();
p.code = txtCode.Value.Trim();
p.quantity = Int32.Parse(txtQty.Value.Trim());
p.price = Int32.Parse(txtPrice.Value.Trim());
p.decription = txtDescription.Value.Trim();
if (saveImage(productImage) != "")
{
p.image = saveImage(productImage);
}
else
{
p.image = imgpath;
}
if (pBL.updateProduct(p))
{
Response.Redirect("products.aspx");
}
else
{
lblWarning.Text = "Error Occor !";
}
}
}
Now your product details are updated !
9. Delete the product details from database
For delete the product details add the new web form delete-product.aspx and add the following code into web form .
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblWarning" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
Now add the following c# code into web form cs file
public partial class delete_product : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int pId = Int32.Parse(Request["id"]);
ProductBL pBL = new ProductBL();
if (pBL.deleteProduct(pId))
{
Response.Redirect("Products.aspx");
}
else
{
lblWarning.Text = "Error Occur !";
}
}
}
