How to insert data in database using 3-tier and stored procedure in asp.net? ~ Lab of Crazzy Buddies

Friends,in earlier article i have given code for simple insertion procedure in database using asp.net c#.How to insert data into database using asp.net c#.Now,i am going to post how to store data using stored procedure as well as 3-tier architecture.

The purpose of 3-tier architecture coding is to provide higher security of code, reusability and structural programming.

So,first of all i am going to create designing form for insertion.

Design.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Product Registration</title>

//Page validations start
<script language="javascript" type="text/javascript">
function validate() {
if (document.getElementById("<%=txtprid.ClientID%>").value == "") {
alert("Product id Field can not be blank");
document.getElementById("<%=txtprid.ClientID%>").focus();
return false;
}
if (document.getElementById("<%=txtprname.ClientID %>").value == "") {
alert("Product name can not be blank");
document.getElementById("<%=txtprname.ClientID %>").focus();
return false;
}
if (document.getElementById("<%=txtprprice.ClientID %>").value == "") {
alert("product price can not be blank");
document.getElementById("<%=txtprprice.ClientID %>").focus();
return false;
}
if (document.getElementById("<%=txtprdesc.ClientID %>").value == "") {
alert("product description can not be blank");
document.getElementById("<%=txtprdesc.ClientID %>").focus();
return false;
}
return true;
}
</script>
//Page validations end

</head>
<body style="text-align: left">
<form id="form1" runat="server">
<div>
<div style="text-align: center">
<br /><br />

<table align="center" border="1" style="height: 221px; width: 401px">

<tr>
<td style="text-align: center; font-size: x-large;" colspan="2">
PRODUCT ENTRY
</td>
</tr>

<tr>
<td style="text-align: left">
Product ID:-
</td>
<td style="width: 100px">
<asp:TextBox ID="txtprid" runat="server" Width="200px" autocomplete="off"></asp:TextBox>
</td>
</tr>

<tr>
<td style="text-align: left" class="style1">
Product Name:-
</td>
<td style="width: 100px">
<asp:TextBox ID="txtprname" runat="server" Width="200px" autocomplete="off"></asp:TextBox>
</td>
</tr>

<tr>
<td style="text-align: left">
Product Type:-
</td>
<td style="width: 100px">
<asp:DropDownList ID="ddprtype" runat="server" Width="200px">
<asp:ListItem Selected="True">Shampoo</asp:ListItem>
<asp:ListItem>Soap</asp:ListItem>
<asp:ListItem>Spray</asp:ListItem>
</asp:DropDownList>
</td>
</tr>

<tr>
<td style="text-align: left">
Verification:-
</td>
<td align="left">
<asp:RadioButton ID="RadioButton1" runat="server" GroupName="ver" Text="Yes" />
   
<asp:RadioButton ID="RadioButton2" runat="server" GroupName="ver" Text="No" />
</td>
</tr>

<tr>
<td style="text-align: left" class="style2">
Description:-
</td>
<td>
<asp:TextBox ID="txtprdesc" runat="server" TextMode="MultiLine" Width="200px"
autocomplete="off"></asp:TextBox>
</td>
</tr>

<tr>
<td style="text-align: left">
Price:-
</td>
<td>
<asp:TextBox ID="txtprprice" runat="server" Width="200px" autocomplete="off"></asp:TextBox>
</td>
</tr>

<tr>
<td colspan="2" style="text-align: center">
<asp:Button ID="btnsave" runat="server" Font-Bold="True" OnClientClick=" return validate()"
Text="SAVE" onclick="btnsave_Click" />
</td>
</tr>

<tr>
<td colspan="2" style="text-align: center">
<asp:Label ID="Label1" runat="server" Font-Bold="True" Text="Label"
Visible="False"></asp:Label>
</td>
</tr>

</table>
</div>
</div>
</form>
</body>
</html>

Output will be like below.

Now,i am going to create 3-tier architecture classes to connect with database and insert data.We will create database and product table.

product table:-

Now, we will create stored procedure to insert data in database.To create a stored procedure follow steps.

Server Explorer->Database.mdf(select your database)->Right click on Stored Procedure->Add New Stored Procedure->then write your stored procedure and save it.

insertproduct stored procedure

CREATE PROCEDURE insertproduct
@pid int,
@pname varchar(50),
@ptype varchar(50),
@pver varchar(10),
@pdesc varchar(50),
@pprice int
AS
insert into product values(@pid,@pname,@ptype,@pver,@pdesc,@pprice);

Now we will create class.How to add new class?Follow steps.

Go to Solution Explorer->Select your site name->Right click on App_Code folder->Click on the Add New Item->Select class from list and give it to name and save it.

1) PROPERTYPRODUCT:- It contains all variables which are used in this program.

2) BUSINESSPRODUCT:- It contains function to connect PROPERTY and DATA classes and business logic of program.

3) DATAPRODUCT:- It contains actual coding,connection and queries of database.

PROPERTYPRODUCT class:-

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for PROPERTYEMP
/// </summary>
public class PROPERTYPRODUCT
{
public int prid;
public string prname;
public string prtype;
public string prver;
public string prdesc;
public int prprice;
}

BUSINESSPRODUCT class:-

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

/// <summary>

/// Summary description for BUSINESSEMP

/// </summary>

public class BUSINESSPRODUCT

{

public void intersave(PROPERTYPRODUCT p1)

{

DATAPRODUCT d1 = new DATAPRODUCT();

d1.insertdata(p1);

}

DATAPRODUCT class:-

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

/// <summary>

/// Summary description for DATAEMP

/// </summary>

public class DATAPRODUCT

{

public void exesql(SqlCommand cmd, string proc)

{

SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");

//SqlConnection cn=new SqlConnection=("Connection String");

cn.Open();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = proc;

cmd.ExecuteNonQuery();

}

public void insertdata(PROPERTYPRODUCT p1)

{

SqlCommand cmd = new SqlCommand();

cmd.Parameters.AddWithValue("@pid", p1.prid);

cmd.Parameters.AddWithValue("@pname", p1.prname);

cmd.Parameters.AddWithValue("@ptype", p1.prtype);

cmd.Parameters.AddWithValue("@pver", p1.prver);

cmd.Parameters.AddWithValue("@pdesc", p1.prdesc);

cmd.Parameters.AddWithValue("@pprice", p1.prprice);

exesql(cmd, "insertproduct");

}

Now,we will write code in the coding file of aspx page which is Design.aspx.cs.

Design.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
btnsave.Attributes.Add("onclick", "return validate()");
incr();
}
protected void Button1_Click(object sender, EventArgs e)
{

}
protected void btnsave_Click(object sender, EventArgs e)
{
PROPERTYPRODUCT p1 = new PROPERTYPRODUCT();

p1.prid = Convert.ToInt32(txtprid.Text);
p1.prname = txtprname.Text;
p1.prtype = ddprtype.SelectedItem.Text;
if (RadioButton1.Checked == true)
{
p1.prver = RadioButton1.Text;
}
else
{
p1.prver = RadioButton2.Text;
}
p1.prdesc = txtprdesc.Text;
p1.prprice = Convert.ToInt32(txtprprice.Text);

BUSINESSPRODUCT b1 = new BUSINESSPRODUCT();
b1.intersave(p1);

Label1.Visible = true;
Label1.Text = "Data Stored";

cleartext();

}
private void cleartext()
{
txtprid.Text = "";
txtprname.Text = "";
txtprdesc.Text = "";
txtprprice.Text = "";
txtprid.Focus();
incr();
}
private void incr()
{
int a;
SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
if (txtprid.Text == "")
{
cn.Open();
string sqlstr = "select * from product";
SqlDataAdapter sda = new SqlDataAdapter(sqlstr, cn);
DataSet dst = new DataSet();
sda.Fill(dst);
if (dst.Tables[0].Rows.Count != 0)
{
a = dst.Tables[0].Rows.Count;
a = a + 1;
txtprid.Text = Convert.ToString(a);
txtprname.Focus();
}
else
{
txtprid.Text = "1";
txtprname.Focus();
}
cn.Close();
}
}
}

The output of complete program which looks like below figure.

If you want to download demo code of this output then it is available.Click on the following download button.

Lab of Crazzy Buddies

Sunday, 21 December 2014