+91-90427 10472

Dot net training in Chennai

CRUD in ASP.net web application using Stored procedure

Document by Maria Academy – mariatrainingacademy@gmail.com – +919042710472

Create Database in SQL server as below,

Create database StudentDB

Create StudentDetails table using the below script,

CREATE TABLE [dbo].[StudentDetails](
[StudentId] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [varchar](100) NULL,
[Age] [int] NULL
) ON [PRIMARY]

Run the below Stored Procedures in SQL Server,

create Proc [dbo].[Ins_StudentDetails]
@StudentName varchar(100),
@Age int
as
begin
INSERT INTO [StudentDetails]
([StudentName]
,[Age])
VALUES
(@StudentName
,@Age)

create Proc [dbo].[Select_StudentDetails]
as
begin
select * from StudentDetails
end

create Proc [dbo].[Update_StudentDetails]
@StudentId int,
@StudentName varchar(100),
@Age int
as
begin
UPDATE [StudentDetails]
SET [StudentName] = @StudentName
,[Age] = @Age
WHERE StudentId=@StudentId
end

Create a new Asp.net web application CRUDinProcedure as below,
File -> New Project

Click OK.
Solution Explorer as below,

Add new Page AddStudentDetails.aspx
Put / Replace the below code in AddStudentDetails.aspx

Replace the below code in AddStudentDetails.aspx.cs,

public partial class AddStudentDetails : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“DefaultConnection”].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
btnSubmit.Visible = true;
btnUpdate.Visible = false;
SqlCommand cmd = new SqlCommand(“Select_StudentDetails”, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter ada = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ada.Fill(ds);
grvStudentDetails.DataSource = ds.Tables[0];
grvStudentDetails.DataBind();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“Ins_StudentDetails”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@StudentName”, SqlDbType.VarChar).Value = txtStudentName.Text;
cmd.Parameters.Add(“@Age”, SqlDbType.Int).Value = txtAge.Text;
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
Response.Write(“Inserted Successfully”);
}
con.Close();
BindGrid();
btnClear_Click(null, null);
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
//Get the row that contains this button
GridViewRow gvr = (GridViewRow)btn.NamingContainer;
hfId.Value = gvr.Cells[0].Text;
txtStudentName.Text = gvr.Cells[1].Text;
txtAge.Text = gvr.Cells[2].Text;
btnSubmit.Visible = false;
btnUpdate.Visible = true;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“Update_StudentDetails”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@StudentId”, SqlDbType.Int).Value = hfId.Value;
cmd.Parameters.Add(“@StudentName”, SqlDbType.VarChar).Value = txtStudentName.Text;
cmd.Parameters.Add(“@Age”, SqlDbType.Int).Value = txtAge.Text;
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
Response.Write(“Updated Successfully”);
}
con.Close();
BindGrid();
btnClear_Click(null, null);
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtStudentName.Text = string.Empty;
txtAge.Text = string.Empty;
hfId.Value = string.Empty;
}
}

Screenshot as below,

The Output will be as below,

Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6of7HjHOGOkB8IMXA

Asp.net Validation Controls

Document by Ganesan -ganesanva@hotmail.com – + 919600370429
List of Asp.net Validation available are as follows,

1.Required Field validator
2.Regular Expression validator
3.Range Validator
4.Custom Validator
5.Compare Validator

Create a new Asp.net Web application
File -> New Project
b2-1
Right click on the solution and Add New web form page AddStudentDetails.aspx as below,
b2-2
Replace the HTML Tag in AddStudentDetails.aspx with below snippet,

<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title></title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<table border=”0″ cellspacing=”2″ cellpadding=”2″>
<tr>
<td>Student Name
</td>
<td>
<asp:TextBox ID=”txtStudentName” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RQStudentName” runat=”server” ControlToValidate=”txtStudentName” ErrorMessage=”Please enter Student Name” ForeColor=”Red”>
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td> Date of Birth
</td>
<td>
<asp:TextBox ID=”txtDOB” runat=”server”></asp:TextBox>
<asp:RangeValidator ID=”Rvalid” runat=”server”
ControlToValidate=”txtDOB” ErrorMessage=”Date of Birth should not be greater than current date” Type=”Date” ForeColor=”Red”></asp:RangeValidator>
</td>
</tr>
<tr>
<td> Age
</td>
<td>
<asp:TextBox ID=”txtAge” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RqAge” runat=”server” ControlToValidate=”txtAge” ErrorMessage=”Please enter Student Age” ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:RangeValidator ID=”RgAge” runat=”server” ControlToValidate=”txtAge” ErrorMessage=”Age should be greater than 18″ MinimumValue=”18″ ForeColor=”Red”
MaximumValue=”2000″ Type=”Integer”></asp:RangeValidator>
</td>
</tr>
<tr>
<td> City
</td>
<td>
<asp:DropDownList ID=”ddlCity” runat=”server”>
<asp:ListItem Text=”–Select–” Value=”–Select–“></asp:ListItem>
<asp:ListItem Text=”Chennai” Value=”Chennai”></asp:ListItem>
<asp:ListItem Text=”Madurai” Value=”Madurai”></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID=”RqCity” runat=”server” InitialValue=”–Select–” ControlToValidate=”ddlCity”
ErrorMessage=”Please enter Student City” ForeColor=”Red”>
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td> Email Id
</td>
<td>
<asp:TextBox ID=”txtEmailId” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RqEmailId” runat=”server” ControlToValidate=”txtEmailId” ErrorMessage=”Please enter Email ID” ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID=”REVEmailId” runat=”server” ErrorMessage=”please enter correct email id” ControlToValidate=”txtEmailId”
ValidationExpression=”\w+([-+.’]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*” ForeColor=”Red”></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td> User Name
</td>
<td>
<asp:TextBox ID=”txtUserName” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RequiredFieldValidator1″ runat=”server” ControlToValidate=”txtUserName” ErrorMessage=”Please enter UserName”
ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:CustomValidator ID=”CVUserName” runat=”server” ControlToValidate=”txtUserName” OnServerValidate=”CVUserName_ServerValidate”
ErrorMessage=”UserName should not be greater than 15 digits” ForeColor=”Red”></asp:CustomValidator>
</td>
</tr>
<tr>
<td> Password
</td>
<td>
<asp:TextBox ID=”txtPassword” runat=”server” TextMode=”Password”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RequiredFieldValidator2″ runat=”server” ControlToValidate=”txtPassword” ErrorMessage=”Please enter Password” ForeColor=”Red”>
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td> Retype Password
</td>
<td>
<asp:TextBox ID=”txtRetypePassword” runat=”server” TextMode=”Password”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RequiredFieldValidator3″ runat=”server” ControlToValidate=”txtRetypePassword” ErrorMessage=”Please enter Retype Password” ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:CompareValidator ID=”cmpPasswordmatch” runat=”server” ControlToCompare=”txtPassword” ControlToValidate=”txtRetypePassword”
ErrorMessage=”Password and Retype Password should not match” ForeColor=”Red”>
</asp:CompareValidator>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID=”btnSave” runat=”server” Text=”Save” CausesValidation=”true” OnClick=”btnSave_Click” />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Replace the AddStudentDetails.cs class with the below snippet,

public partial class AddStudentDetails : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Rvalid.MinimumValue = DateTime.MinValue.ToString(“dd-MM-yyyy”);
Rvalid.MaximumValue = DateTime.Now.ToString(“dd-MM-yyyy”);
}
protected void CVUserName_ServerValidate(object source, ServerValidateEventArgs args)
{
args.IsValid = (args.Value.Length < 15);
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
Response.Write(“Saved”);
}
}
}

The Output as below,
b2-3
b2-4
b2-5
Custom Validation on server event,
b2-6
Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6kmic1tyrHNsIbXqA

Asp.net CRUD operation with Ado.net

Document by Ganesan – Ganesanva@hotmail.com – + 919600370429

– Create Database in SQL as “StudentDB”
– Create Table “StudentDetails” with the below snippet,

CREATE TABLE [dbo].[StudentDetails] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[StudentName] VARCHAR (50) NULL,
[Age] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

You can see the DB created in local SQL express as below,

– Create a new Asp.net Web forms Application as below
File –> New Project

Add the Connection string in Web.config as below snippet,

Add new Page as AddStudentDetails.aspx as below,

Replace the Below HTML code in AddStudentDetails.aspx file as below,

Replace the AddStudentDetails.aspx.cs file as below,

public partial class AddStudentDetails : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“DefaultConnection”].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
btnSubmit.Visible = true;
btnUpdate.Visible = false;
SqlCommand cmd = new SqlCommand(“Select * from StudentDetails”, con);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ada.Fill(ds);
grvStudentDetails.DataSource = ds.Tables[0];
grvStudentDetails.DataBind();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“insert into StudentDetails values(‘”+txtStudentName.Text+”‘,”+txtAge.Text+”)”, con);
int result=cmd.ExecuteNonQuery();
if (result > 0)
{
Response.Write(“Inserted Successfully”);
}
con.Close();
BindGrid();
btnClear_Click(null, null);
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
//Get the row that contains this button
GridViewRow gvr = (GridViewRow)btn.NamingContainer;
hfId.Value = gvr.Cells[0].Text;
txtStudentName.Text = gvr.Cells[1].Text;
txtAge.Text = gvr.Cells[2].Text;
btnSubmit.Visible = false;
btnUpdate.Visible = true;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“Update StudentDetails set StudentName='” + txtStudentName.Text + “‘,Age=” + txtAge.Text + ” where Id=”+hfId.Value, con);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
Response.Write(“Updated Successfully”);
}
con.Close();
BindGrid();
btnClear_Click(null, null);
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtStudentName.Text = string.Empty;
txtAge.Text = string.Empty;
hfId.Value = string.Empty;
}
}

Run the solution.
The Output will be below,

Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6h98mZAX_-Zv-6reQ

Maria academy

CRUD in ASP.net web application using Stored procedure

Document by Maria Academy – mariatrainingacademy@gmail.com – +919042710472

Asp.net Validation Controls

Asp.net CRUD operation with Ado.net

Document by Ganesan – Ganesanva@hotmail.com – + 919600370429

Recent Posts

Categories