asp.net fresher Machine test CRUD and Stored procedure program want

Copy paste and Run

1.Aspx page(design)

"server" ID="gvEmployee" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataKeyNames="Id" OnRowCommand="gvEmployee_RowCommand" ShowFooter="True">
"lbEdit" CommandArgument='<%# Eval("Id") %>' CommandName="EditRow" ForeColor="#8C4510" runat="server">Edit
"lbDelete" CommandArgument='<%# Eval("Id") %>' CommandName="DeleteRow" ForeColor="#8C4510" runat="server" CausesValidation="false">Delete
"lbUpdate" CommandArgument='<%# Eval("Id") %>' CommandName="UpdateRow" ForeColor="#8C4510" runat="server">Update
"lbCancel" CommandArgument='<%# Eval("Id") %>' CommandName="CancelUpdate" ForeColor="#8C4510" runat="server" CausesValidation="false">Cancel
"Id" InsertVisible="False" SortExpression="Id">
"Label1" runat="server" Text='<%# Eval("Id") %>'>
"LblId" runat="server" Text='<%# Bind("Id") %>'>
"lbAdd" CommandName="Add" ForeColor="#8C4510" runat="server">Add
"Name" SortExpression="Name">
"TextBox1" runat="server" Text='<%# Bind("Name") %>'>
"Label2" runat="server" Text='<%# Bind("Name") %>'>
"txtName" runat="server">
"DOB" SortExpression="DOB">
<%--
"Calendar1" runat="server" SelectedDate='<%# Bind("DOB").ToString(("dd/M/yyyy", CultureInfo.InvariantCulture)) %>'>
--%>
"Label3" runat="server" Text='<%# Bind("DOB") %>'>
"calDOB" runat="server" >
"Salary" SortExpression="Salary">
"TextBox3" runat="server" Text='<%# Bind("Salary") %>'>
"Label4" runat="server" Text='<%# Bind("Salary") %>'>
"txtSalary" runat="server">
"Gender" SortExpression="Gender">
"chkGender" runat="server" SelectedValue='<%# Bind("Gender") %>'>
"Male" Value="Male">
"Female" Value="Female">
"lblGender" runat="server" Text='<%# Bind("Gender") %>'>
"rdoGender" runat="server">
"Male" Value="Male">
"Female" Value="Female">
"#F7DFB5" ForeColor="#8C4510" />
"#A55129" Font-Bold="True" ForeColor="White" />
"#8C4510" HorizontalAlign="Center" />
"#FFF7E7" ForeColor="#8C4510" />
"#738A9C" Font-Bold="True" ForeColor="White" />
"#FFF1D4" />
"#B95C30" />
"#F1E5CE" />
"#93451F" />

Aspx.cs(code behind)

public partial class Home : System.Web.UI.Page
{
string cs = ConfigurationManager.ConnectionStrings["KendoGridConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadGrid();
}
}
public void LoadGrid()
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spEmployeeData", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Type", "Select");
con.Open();
gvEmployee.DataSource = cmd.ExecuteReader();
gvEmployee.DataBind();
}
}
protected void gvEmployee_RowCommand(object sender, GridViewCommandEventArgs e)
{
int index = ((GridViewRow)(((LinkButton)e.CommandSource).NamingContainer)).RowIndex;
if (e.CommandName == "EditRow")
{
gvEmployee.EditIndex = index;
LoadGrid();
}
if (e.CommandName == "UpdateRow")
{
int Id = Convert.ToInt32(e.CommandArgument);
string Name = ((TextBox)gvEmployee.Rows[index].FindControl("TextBox1")).Text;
int Salary = Convert.ToInt32((((TextBox)gvEmployee.Rows[index].FindControl("TextBox3")).Text));
string Gender = ((RadioButtonList)gvEmployee.Rows[index].FindControl("chkGender")).SelectedItem.Text;
DateTime DOB = ((Calendar)gvEmployee.Rows[index].FindControl("Calendar1")).SelectedDate;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spEmployeeData", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Type", "Update");
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@Gender", Gender);
cmd.Parameters.AddWithValue("@Salary", Salary);
cmd.Parameters.AddWithValue("@Id", Id);
con.Open();
cmd.ExecuteNonQuery();
}
gvEmployee.EditIndex = -1;
LoadGrid();
}
if (e.CommandName == "DeleteRow")
{
int Id = Convert.ToInt32(e.CommandArgument);
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spEmployeeData", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Type", "Delete");
cmd.Parameters.AddWithValue("@Id", Id);
con.Open();
cmd.ExecuteNonQuery();
}
gvEmployee.EditIndex = -1;
LoadGrid();
}
if (e.CommandName == "Add")
{
int Id = Convert.ToInt32(e.CommandArgument);
string Name = ((TextBox)gvEmployee.Rows[index].FindControl("txtName")).Text;
int Salary = Convert.ToInt32((((TextBox)gvEmployee.Rows[index].FindControl("txtSalary")).Text));
string Gender = ((RadioButtonList)gvEmployee.Rows[index].FindControl("rdoGender")).SelectedItem.Text;
DateTime DOB = ((Calendar)gvEmployee.Rows[index].FindControl("calDOB")).SelectedDate;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spEmployeeData", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Type", "Insert");
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@Gender", Gender);
cmd.Parameters.AddWithValue("@Salary", Salary);
cmd.Parameters.AddWithValue("@Id", Id);
con.Open();
cmd.ExecuteNonQuery();
}
gvEmployee.EditIndex = -1;
LoadGrid();
}
}
}

Stored procedure

create proc spEmployeeData
@Id int=null,
@Name varchar(50)=null,
@Salary int=null,
@Gender varchar(50)=null,
@DOB dateTime=null,
@Type varchar(50)
as
begin
if(@Type='Select')
begin
select * from Employee
end
if(@Type='Insert')
begin
Insert into Employee values (@Name,@DOB,@Salary,@Gender)
end
if(@Type='Update')
begin
update Employee set Name=@Name,Salary=@Salary,DOB=@DOB,Gender=@Gender where Id=@Id
end
if(@Type='Delete')
begin
Delete from Employee where Id=@Id
end
if(@Type='Edit')
begin
Select* from Employee where Id=@Id
end
end

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
Copy paste and Run
1.Aspx page(design)
"server" ID="gvEmployee" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataKeyNames="Id" OnRowCommand="gvEmployee_RowCommand" ShowFooter="True">



                           "lbEdit" CommandArgument='<%# Eval("Id") %>' CommandName="EditRow" ForeColor="#8C4510" runat="server">Edit
                           "lbDelete" CommandArgument='<%# Eval("Id") %>' CommandName="DeleteRow" ForeColor="#8C4510" runat="server" CausesValidation="false">Delete


                           "lbUpdate" CommandArgument='<%# Eval("Id") %>' CommandName="UpdateRow" ForeColor="#8C4510" runat="server">Update
                           "lbCancel" CommandArgument='<%# Eval("Id") %>' CommandName="CancelUpdate" ForeColor="#8C4510" runat="server" CausesValidation="false">Cancel


                   "Id" InsertVisible="False" SortExpression="Id">

                           "Label1" runat="server" Text='<%# Eval("Id") %>'>


                           "LblId" runat="server" Text='<%# Bind("Id") %>'>


                            "lbAdd"   CommandName="Add" ForeColor="#8C4510" runat="server">Add


                   "Name" SortExpression="Name">

                           "TextBox1" runat="server" Text='<%# Bind("Name") %>'>


                           "Label2" runat="server" Text='<%# Bind("Name") %>'>


                            "txtName" runat="server">


                   "DOB" SortExpression="DOB">
                      <%--
                           "Calendar1" runat="server" SelectedDate='<%# Bind("DOB").ToString(("dd/M/yyyy", CultureInfo.InvariantCulture)) %>'>
                       --%>

                           "Label3" runat="server" Text='<%# Bind("DOB") %>'>


                             "calDOB" runat="server" >


                   "Salary" SortExpression="Salary">

                           "TextBox3" runat="server" Text='<%# Bind("Salary") %>'>


                           "Label4" runat="server" Text='<%# Bind("Salary") %>'>


                            "txtSalary" runat="server">


                   "Gender" SortExpression="Gender">

                           "chkGender" runat="server" SelectedValue='<%# Bind("Gender") %>'>
                               "Male" Value="Male">
                               "Female" Value="Female">




                           "lblGender" runat="server" Text='<%# Bind("Gender") %>'>


                         "rdoGender" runat="server">
                               "Male" Value="Male">
                               "Female" Value="Female">




               "#F7DFB5" ForeColor="#8C4510" />
               "#A55129" Font-Bold="True" ForeColor="White" />
               "#8C4510" HorizontalAlign="Center" />
               "#FFF7E7" ForeColor="#8C4510" />
               "#738A9C" Font-Bold="True" ForeColor="White" />
               "#FFF1D4" />
               "#B95C30" />
               "#F1E5CE" />
               "#93451F" />
Aspx.cs(code behind)
public partial class Home : System.Web.UI.Page
    {
        string cs = ConfigurationManager.ConnectionStrings["KendoGridConnectionString"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadGrid();
            }
        }

        public void LoadGrid()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spEmployeeData", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Type", "Select");
                con.Open();
                gvEmployee.DataSource = cmd.ExecuteReader();
                gvEmployee.DataBind();

            }
        }



        protected void gvEmployee_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            int index = ((GridViewRow)(((LinkButton)e.CommandSource).NamingContainer)).RowIndex;
            if (e.CommandName == "EditRow")
            {

                gvEmployee.EditIndex = index;
                LoadGrid();
            }

            if (e.CommandName == "UpdateRow")
            {
                int Id = Convert.ToInt32(e.CommandArgument);
                string Name = ((TextBox)gvEmployee.Rows[index].FindControl("TextBox1")).Text;
                int Salary = Convert.ToInt32((((TextBox)gvEmployee.Rows[index].FindControl("TextBox3")).Text));
                string Gender = ((RadioButtonList)gvEmployee.Rows[index].FindControl("chkGender")).SelectedItem.Text;
                DateTime DOB = ((Calendar)gvEmployee.Rows[index].FindControl("Calendar1")).SelectedDate;
                using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlCommand cmd = new SqlCommand("spEmployeeData", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Type", "Update");
                    cmd.Parameters.AddWithValue("@Name", Name);
                    cmd.Parameters.AddWithValue("@Gender", Gender);
                    cmd.Parameters.AddWithValue("@Salary", Salary);
                    cmd.Parameters.AddWithValue("@Id", Id);
                    con.Open();
                    cmd.ExecuteNonQuery();

                }
                gvEmployee.EditIndex = -1;
                LoadGrid();
            }
            if (e.CommandName == "DeleteRow")
            {
                int Id = Convert.ToInt32(e.CommandArgument);

                using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlCommand cmd = new SqlCommand("spEmployeeData", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Type", "Delete");

                    cmd.Parameters.AddWithValue("@Id", Id);
                    con.Open();
                    cmd.ExecuteNonQuery();

                }
                gvEmployee.EditIndex = -1;
                LoadGrid();

            }
            if (e.CommandName == "Add")
            {
                int Id = Convert.ToInt32(e.CommandArgument);
                string Name = ((TextBox)gvEmployee.Rows[index].FindControl("txtName")).Text;
                int Salary = Convert.ToInt32((((TextBox)gvEmployee.Rows[index].FindControl("txtSalary")).Text));
                string Gender = ((RadioButtonList)gvEmployee.Rows[index].FindControl("rdoGender")).SelectedItem.Text;
                DateTime DOB = ((Calendar)gvEmployee.Rows[index].FindControl("calDOB")).SelectedDate;
                using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlCommand cmd = new SqlCommand("spEmployeeData", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Type", "Insert");
                    cmd.Parameters.AddWithValue("@Name", Name);
                    cmd.Parameters.AddWithValue("@Gender", Gender);
                    cmd.Parameters.AddWithValue("@Salary", Salary);
                    cmd.Parameters.AddWithValue("@Id", Id);
                    con.Open();
                    cmd.ExecuteNonQuery();

                }
                gvEmployee.EditIndex = -1;
                LoadGrid();

            }
        }


    }
Stored procedure
create proc spEmployeeData
@Id int=null,
@Name varchar(50)=null,
@Salary int=null,
@Gender varchar(50)=null,
@DOB dateTime=null,
@Type varchar(50)
as
begin
if(@Type='Select')
begin
select * from Employee
end
if(@Type='Insert')
begin
Insert into Employee values (@Name,@DOB,@Salary,@Gender)
end
if(@Type='Update')
begin
update Employee set Name=@Name,Salary=@Salary,DOB=@DOB,Gender=@Gender where Id=@Id
end
if(@Type='Delete')
begin
Delete from Employee where Id=@Id
end
if(@Type='Edit')
begin
Select* from Employee where Id=@Id
end
end
0
Nikunj Satasiya5y ago
Hi Prashant, Visit this to learn the Stored procedure and ASP.NET web applications.

https://www.codingvila.com/search/label/Stored%20Procedure

https://www.codingvila.com/search/label/Asp.net
0
pankaj baviskar5y ago
http://pankaj22kar.blogspot.com/?m=1
0