Saturday, 2 June 2012

Inser,Update,Delete and view in same page or choose anothe page and the data will show in main page use Request query string

Design Page
 

Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RegistrationPage.aspx.cs" Inherits="RegistrationPage" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:Button ID="btn_next" runat="server" Text="Next"
            style="position:absolute; top: 369px; left: 424px;"
            onclick="btn_next_Click" />
   
        <asp:HiddenField ID="HiddenField1" runat="server" />
   
        <asp:Label ID="Label1" runat="server" Text="Email Address"
            style="position:absolute; top: 112px; left: 67px;"></asp:Label>
        <asp:Label ID="Label2" runat="server" Text="First Name"
            style="position:absolute; top: 50px; left: 95px;"></asp:Label>
        <asp:Label ID="Label3" runat="server" Text="Mob No"
            style="position:absolute; top: 150px; left: 96px; bottom: 339px;"></asp:Label>
        <asp:Label ID="Label4" runat="server" Text="Last Name"
            style="position:absolute; top: 49px; left: 322px; width: 69px;"></asp:Label>
        <asp:Label ID="Label5" runat="server" Text="Sallary"
            style="position:absolute; top: 226px; left: 100px; height: 5px;"></asp:Label>
        <asp:Label ID="Label6" runat="server" Text="Date of Birth"
            style="position:absolute; top: 82px; left: 82px;"></asp:Label>
   
    </div>
        <br />
        <br />
        <asp:TextBox ID="TxtFName" runat="server"
        style="position:absolute; top: 53px; left: 177px;"></asp:TextBox>
    <asp:TextBox ID="TxtSallary" runat="server"
        style="position:absolute; top: 221px; left: 174px;"></asp:TextBox>
    <asp:TextBox ID="Txtdob" runat="server"
        style="position:absolute; top: 84px; left: 176px;"></asp:TextBox>
    <asp:TextBox ID="TxtlName" runat="server"
        style="position:absolute; top: 50px; left: 397px;"></asp:TextBox>
        <br />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        onrowcommand="GridView1_RowCommand" 
        style="position:absolute; top: 72px; left: 582px;"
        onrowcreated="GridView1_RowCreated">
                    <Columns>
             <asp:TemplateField HeaderText="SELECT ROW">
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1" runat="server">Select</asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
           
           <asp:BoundField HeaderText="Name" DataField="Name" />
           <asp:BoundField HeaderText="Date" DataField="DOB" />
           <asp:BoundField HeaderText="Email" DataField="Email" />
           <asp:BoundField HeaderText="MobNo" DataField="MobNo" />
           <asp:BoundField HeaderText="Address" DataField="Address" />
           <asp:BoundField HeaderText="Age" DataField="Age" />
           <asp:BoundField HeaderText="Gender" DataField="Gender" />
           <asp:BoundField HeaderText="Sallary" DataField="Sallary" />
            <asp:BoundField HeaderText="Qualification" DataField="Qualifiction" /> 
             <asp:BoundField HeaderText="RegID" DataField="RegID"  >
                        <ControlStyle Width="0px" />
                        </asp:BoundField>
            </Columns>


    </asp:GridView>


    <p>
    <asp:TextBox ID="TxtEmail" runat="server"
            style="position:absolute; top: 115px; left: 177px;"></asp:TextBox>
    <asp:TextBox ID="TxtMobNo" runat="server"
            style="position:absolute; top: 149px; left: 172px;"></asp:TextBox>
    <asp:TextBox ID="TxtAddress" runat="server"
            style="position:absolute; top: 180px; left: 172px;"></asp:TextBox>
    </p>


        <asp:DropDownList ID="DDLQualification" runat="server"
        style="position:absolute; top: 258px; left: 176px;" AutoPostBack="True">
        </asp:DropDownList>
    <p>
        <asp:Label ID="Label7" runat="server" Text="Qualification"
            style="position:absolute; top: 259px; left: 84px;"></asp:Label>
        <asp:Label ID="Label8" runat="server" Text="Address"
            style="position:absolute; top: 186px; left: 100px;"></asp:Label>
        <asp:Label ID="Label9" runat="server" Text="Gender"
            style="position:absolute; top: 292px; left: 96px; bottom: 197px;"></asp:Label>
        </p>


    <asp:RadioButton ID="RadMale" runat="server" Text="Male"
        style="position:absolute; top: 297px; left: 168px;" />
    <asp:RadioButton ID="RadFimale" runat="server" Text="Female" 
        style="position:absolute; top: 299px; left: 234px;" />


    <asp:DropDownList ID="DDLAge" runat="server"
        style="position:absolute; top: 332px; left: 171px;" AutoPostBack="True">
    </asp:DropDownList>


    <p>
        <asp:Label ID="Label10" runat="server" Text="Age"
           
           
            style="position:absolute; top: 331px; left: 96px; bottom: 158px; height: 19px; width: 46px;"></asp:Label>
        </p>
    <asp:Button ID="btn_insert" runat="server" Text="Insert"
        style="position:absolute; top: 365px; left: 125px; right: 1122px; height: 28px;"
        onclick="btn_insert_Click" />
    <asp:Button ID="btn_update" runat="server" Text="Update"
        style="position:absolute; top: 366px; left: 294px;"
        onclick="btn_update_Click1"/>
    <asp:Button ID="btndelete" runat="server" Text="Delete"
        style="position:absolute; top: 365px; left: 359px;" onclick="btndelete_Click1"/>


    </form>
</body>
</html>

C# Code (This C# Code the data show in Same page and the same page we choose and the data will show in our control)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class RegistrationPage : System.Web.UI.Page
{
    DataSet ds;
    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter adp;
    //SqlDataReader reader;
    DataTable dt;
    #region  Events
    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ConnectionString);
     
        if (!IsPostBack)
        {
            FillAge();
            FillQualification();
            FillGrid();

          
        }
    }

    private void GetaDetailsById(int sa)
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
          
            cmd = new SqlCommand("SelectEmployee_Sallary_Qualification1",con);        
          
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@RegID",sa);
            adp = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
               
                string[] Fname =dt.Rows[0]["Name"].ToString().Split(' ');
                if (Fname.Count() == 1)
                {
                    TxtFName.Text = Fname[0].ToString();
                    TxtlName.Text = "";
                }
                else if (Fname.Count() == 2)
                {
                    TxtFName.Text = Fname[0].ToString();
                    TxtlName.Text = Fname[1].ToString();
                }
                else if (Fname.Count() == 3)
                {
                    string a = Fname[0].ToString();
                    a += Fname[1].ToString();
                    TxtFName.Text = a.ToString();
                    TxtlName.Text = Fname[2].ToString();
                }
              
                Txtdob.Text = dt.Rows[0]["DOB"].ToString();
                TxtEmail.Text = dt.Rows[0]["Email"].ToString();
                TxtMobNo.Text = dt.Rows[0]["MobNo"].ToString();
                TxtAddress.Text = dt.Rows[0]["Address"].ToString();
                TxtSallary.Text = dt.Rows[0]["Sallary"].ToString();
                DDLQualification.SelectedValue = dt.Rows[0]["Qid"].ToString();
                if (dt.Rows[0]["Gender"].ToString()=="Male")
                {
                    RadMale.Checked = true;
                }
                else
                {
                    RadFimale.Checked = true;
                }
                DDLAge.SelectedValue = dt.Rows[0]["Age"].ToString();
            }
        }
        catch (Exception ex)
        {
            string a = ex.ToString();
        }

    }
    protected void Btnsubmit_Click(object sender, EventArgs e)
    {
        string gen;
        //string nam;

        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("InsertEmployee_Sallary_Qualification", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Name", TxtFName.Text.Trim() + " " + TxtlName.Text.Trim());
            cmd.Parameters.AddWithValue("@DOB", Txtdob.Text);
            cmd.Parameters.AddWithValue("@Email", TxtEmail.Text);
            cmd.Parameters.AddWithValue("@MobNo", Int64.Parse(TxtMobNo.Text));
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@Age", int.Parse(DDLAge.SelectedItem.Text));
            if (RadMale.Checked == true)
            {
                gen = RadMale.Text;
            }
            else
            {
                gen = RadFimale.Text;
            }
            cmd.Parameters.AddWithValue("@Gender", gen);
            cmd.Parameters.AddWithValue("@sallary", Convert.ToDouble(TxtSallary.Text));
            cmd.Parameters.AddWithValue("@Qualification", DDLQualification.SelectedItem.Text);
            cmd.ExecuteNonQuery();
            FillGrid();
        }
        catch (Exception EX)
        {
            string A = EX.ToString();
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            if (this.con != null) this.con = null;
            if (this.con != null) this.con = null;
        }
    }
    #endregion
    #region  Methods
    public void FillAge()
    {
        for (int i = 21; i <= 90; i++)
            DDLAge.Items.Add(i.ToString());
        DDLAge.Items.Insert(0, "Select");
    }
    public void FillQualification()
    {
     
        adp = new SqlDataAdapter("Select * from Qulification", con);
        dt = new DataTable();
        adp.Fill(dt);
        DDLQualification.DataSource = dt;
        DDLQualification.DataTextField = "Qualifiction";
        DDLQualification.DataValueField = "Qid";
        DDLQualification.DataBind();
        DDLQualification.Items.Insert(0, "Select");
     
    }
    public void FillGrid()
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("Select_Employee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                GridView1.DataSource = dt;
               
                GridView1.DataBind();
                GridView1.Columns[10].ItemStyle.Width = 0;
            }
            con.Close();
        }
        catch (Exception ex)
        {
            string ef = ex.ToString();
        }
      
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
      
        GridViewRow val = (GridViewRow)((LinkButton)e.CommandSource).NamingContainer;
        int RowIndex = val.RowIndex;
   
        string[] Fname = GridView1.Rows[RowIndex].Cells[1].Text.Split(' ');
       
        if (Fname.Count() == 1)
        {
            TxtFName.Text = Fname[0].ToString();
            TxtlName.Text = "";
        }
        else if (Fname.Count() == 2)
        {
            TxtFName.Text = Fname[0].ToString();
            TxtlName.Text = Fname[1].ToString();
        }
        Txtdob.Text = GridView1.Rows[RowIndex].Cells[2].Text;
        TxtEmail.Text = GridView1.Rows[RowIndex].Cells[3].Text;
        TxtMobNo.Text = GridView1.Rows[RowIndex].Cells[4].Text;
        TxtAddress.Text = GridView1.Rows[RowIndex].Cells[5].Text;
        DDLAge.SelectedValue = GridView1.Rows[RowIndex].Cells[6].Text;
        if (GridView1.Rows[RowIndex].Cells[7].Text == "Male")
        {
            RadFimale.Checked = false;
            RadMale.Checked = true;
        }
        else
        {
            RadMale.Checked = false;
            RadFimale.Checked = true;
        }
        DDLQualification.SelectedItem.Text = GridView1.Rows[RowIndex].Cells[9].Text;
        TxtSallary.Text = GridView1.Rows[RowIndex].Cells[8].Text;
        HiddenField1.Value = GridView1.Rows[RowIndex].Cells[10].Text;
    }     
   
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //e.Row.Cells[0].Width = Unit.Pixel(150);
            //e.Row.Cells[1].Width = Unit.Pixel(250);
            e.Row.Cells[10].Width = Unit.Pixel(0);


            //e.Row.Cells[0].Text ="Column1's Headertext";
            //e.Row.Cells[1].Text = "Column2's Headertext";
            //e.Row.Cells[2].Text = "Column3's Headertext";
        }

    }

    protected void btn_next_Click(object sender, EventArgs e)
    {
        Response.Redirect("ViewPAgebyrequeststrin.aspx");
    }
    protected void btn_update_Click1(object sender, EventArgs e)
    {
        string gen;
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            cmd = new SqlCommand("UpdateEmployee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@RegID", HiddenField1.Value);
            cmd.Parameters.AddWithValue("@Name", TxtFName.Text.Trim() + " " + TxtlName.Text.Trim());
            cmd.Parameters.AddWithValue("@DOB", Txtdob.Text);
            cmd.Parameters.AddWithValue("@Email", TxtEmail.Text);
            cmd.Parameters.AddWithValue("@MobNo", Int64.Parse(TxtMobNo.Text));
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@Age", int.Parse(DDLAge.SelectedItem.Text));
            if (RadMale.Checked == true)
            {
                gen = RadMale.Text;
            }
            else
            {
                gen = RadFimale.Text;
            }
            cmd.Parameters.AddWithValue("@Gender", gen);
            cmd.Parameters.AddWithValue("@sallary", Convert.ToDouble(TxtSallary.Text));
            cmd.Parameters.AddWithValue("@Qualification", DDLQualification.SelectedItem.Text);
            //cmd.ExecuteNonQuery();
            con.Close();
            FillGrid();


        }
        catch (Exception ex)
        {
            string ec = ex.ToString();
        }
        finally
        {
            //con.Close();
        }
    }
    protected void btndelete_Click1(object sender, EventArgs e)
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            cmd = new SqlCommand("Delete_Employee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("RegID", HiddenField1.Value);

            cmd.ExecuteNonQuery();
            con.Close();
            FillGrid();


        }
        catch (Exception ex)
        {
            string ec = ex.ToString();
        }
    }
    protected void btn_insert_Click(object sender, EventArgs e)
    {
        string gen;
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            cmd = new SqlCommand("InsertEmployee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Parameters.AddWithValue("@RegID", HiddenField1.Value);
            cmd.Parameters.AddWithValue("@Name", TxtFName.Text.Trim() + " " + TxtlName.Text.Trim());
            cmd.Parameters.AddWithValue("@DOB", Txtdob.Text);
            cmd.Parameters.AddWithValue("@Email", TxtEmail.Text);
            cmd.Parameters.AddWithValue("@MobNo", Int64.Parse(TxtMobNo.Text));
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@Age", int.Parse(DDLAge.SelectedItem.Text));
            if (RadMale.Checked == true)
            {
                gen = RadMale.Text;
            }
            else
            {
                gen = RadFimale.Text;
            }
            cmd.Parameters.AddWithValue("@Gender", gen);
            cmd.Parameters.AddWithValue("@sallary", Convert.ToDouble(TxtSallary.Text));
            cmd.Parameters.AddWithValue("@Qualification", DDLQualification.SelectedItem.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            FillGrid();


        }
        catch (Exception ex)
        {
            string ec = ex.ToString();
        }
        finally
        {
            //con.Close();
        }
    }
}

 
    #endregion
 
how to go to another page and the another page how we data retrive throug the query sting or by ID.
This Page design and code are give below

Source Code
 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ViewPAgebyrequeststrin.aspx.cs" Inherits="ViewPAgebyrequeststrin" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" runat="server"
            style="position:absolute; top: 75px; left: 336px;"></asp:TextBox>

        <asp:GridView ID="GridView1" runat="server"
            style="position:absolute; top: 166px; left: 208px;"
            AutoGenerateColumns="False" onrowcommand="GridView1_RowCommand">
            <Columns>
            <asp:TemplateField HeaderText="RegID">
            <ItemTemplate>
             <asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%#Eval("RegID") %>' Text='<%#Eval("Name") %>' CommandName="Edi"></asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
    
           <asp:BoundField HeaderText="Date" DataField="DOB" />
           <asp:BoundField HeaderText="Email" DataField="Email" />
           <asp:BoundField HeaderText="MobNo" DataField="MobNo" />
           <asp:BoundField HeaderText="Address" DataField="Address" />
           <asp:BoundField HeaderText="Age" DataField="Age" />
           <asp:BoundField HeaderText="Gender" DataField="Gender" />
           <asp:BoundField HeaderText="Sallary" DataField="Sallary" />
            <asp:BoundField HeaderText="Qualification" DataField="Qualifiction" />
           
                        </Columns>
        </asp:GridView>
    </div>
        <asp:Button ID="BtnSearch" runat="server" Text="Search"
        style="position:absolute; top: 69px; left: 505px;"/>
    </form>
    <p>
        &nbsp;</p>
</body>
</html>


Page Design
C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


public partial class ViewPAgebyrequeststrin : System.Web.UI.Page
{
    DataSet ds;
    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter adp;
    SqlDataReader reader;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ConnectionString);
        con.Open();
        if (!IsPostBack)
        {
            FillGrid();
        }
    }
    public void FillGrid()
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("Select_Employee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                GridView1.DataSource = dt;

                GridView1.DataBind();
                GridView1.Columns[10].ItemStyle.Width = 0;
            }

        }
        catch (Exception ex)
        {
            string ef = ex.ToString();
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            if (this.con != null) this.con = null;
            if (this.con != null) this.con = null;
        }
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Edi")
        {
            Response.Redirect("RegistrationPage.aspx?sa="+e.CommandArgument.ToString());
        }
    }
}

C# Code in which page we show Data
Page Design


Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RegistrationPage.aspx.cs" Inherits="RegistrationPage" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:Button ID="btn_next" runat="server" Text="Next"
            style="position:absolute; top: 369px; left: 424px;"
            onclick="btn_next_Click" />
   
        <asp:HiddenField ID="HiddenField1" runat="server" />
   
        <asp:Label ID="Label1" runat="server" Text="Email Address"
            style="position:absolute; top: 112px; left: 67px;"></asp:Label>
        <asp:Label ID="Label2" runat="server" Text="First Name"
            style="position:absolute; top: 50px; left: 95px;"></asp:Label>
        <asp:Label ID="Label3" runat="server" Text="Mob No"
            style="position:absolute; top: 150px; left: 96px; bottom: 339px;"></asp:Label>
        <asp:Label ID="Label4" runat="server" Text="Last Name"
            style="position:absolute; top: 49px; left: 322px; width: 69px;"></asp:Label>
        <asp:Label ID="Label5" runat="server" Text="Sallary"
            style="position:absolute; top: 226px; left: 100px; height: 5px;"></asp:Label>
        <asp:Label ID="Label6" runat="server" Text="Date of Birth"
            style="position:absolute; top: 82px; left: 82px;"></asp:Label>
   
    </div>
        <br />
        <br />
        <asp:TextBox ID="TxtFName" runat="server"
        style="position:absolute; top: 53px; left: 177px;"></asp:TextBox>
    <asp:TextBox ID="TxtSallary" runat="server"
        style="position:absolute; top: 221px; left: 174px;"></asp:TextBox>
    <asp:TextBox ID="Txtdob" runat="server"
        style="position:absolute; top: 84px; left: 176px;"></asp:TextBox>
    <asp:TextBox ID="TxtlName" runat="server"
        style="position:absolute; top: 50px; left: 397px;"></asp:TextBox>
        <br />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        onrowcommand="GridView1_RowCommand" 
        style="position:absolute; top: 72px; left: 582px;"
        onrowcreated="GridView1_RowCreated">
                    <Columns>
             <asp:TemplateField HeaderText="SELECT ROW">
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1" runat="server">Select</asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
           
           <asp:BoundField HeaderText="Name" DataField="Name" />
           <asp:BoundField HeaderText="Date" DataField="DOB" />
           <asp:BoundField HeaderText="Email" DataField="Email" />
           <asp:BoundField HeaderText="MobNo" DataField="MobNo" />
           <asp:BoundField HeaderText="Address" DataField="Address" />
           <asp:BoundField HeaderText="Age" DataField="Age" />
           <asp:BoundField HeaderText="Gender" DataField="Gender" />
           <asp:BoundField HeaderText="Sallary" DataField="Sallary" />
            <asp:BoundField HeaderText="Qualification" DataField="Qualifiction" /> 
             <asp:BoundField HeaderText="RegID" DataField="RegID"  >
                        <ControlStyle Width="0px" />
                        </asp:BoundField>
            </Columns>


    </asp:GridView>


    <p>
    <asp:TextBox ID="TxtEmail" runat="server"
            style="position:absolute; top: 115px; left: 177px;"></asp:TextBox>
    <asp:TextBox ID="TxtMobNo" runat="server"
            style="position:absolute; top: 149px; left: 172px;"></asp:TextBox>
    <asp:TextBox ID="TxtAddress" runat="server"
            style="position:absolute; top: 180px; left: 172px;"></asp:TextBox>
    </p>


        <asp:DropDownList ID="DDLQualification" runat="server"
        style="position:absolute; top: 258px; left: 176px;" AutoPostBack="True">
        </asp:DropDownList>
    <p>
        <asp:Label ID="Label7" runat="server" Text="Qualification"
            style="position:absolute; top: 259px; left: 84px;"></asp:Label>
        <asp:Label ID="Label8" runat="server" Text="Address"
            style="position:absolute; top: 186px; left: 100px;"></asp:Label>
        <asp:Label ID="Label9" runat="server" Text="Gender"
            style="position:absolute; top: 292px; left: 96px; bottom: 197px;"></asp:Label>
        </p>


    <asp:RadioButton ID="RadMale" runat="server" Text="Male"
        style="position:absolute; top: 297px; left: 168px;" />
    <asp:RadioButton ID="RadFimale" runat="server" Text="Female" 
        style="position:absolute; top: 299px; left: 234px;" />


    <asp:DropDownList ID="DDLAge" runat="server"
        style="position:absolute; top: 332px; left: 171px;" AutoPostBack="True">
    </asp:DropDownList>


    <p>
        <asp:Label ID="Label10" runat="server" Text="Age"
           
           
            style="position:absolute; top: 331px; left: 96px; bottom: 158px; height: 19px; width: 46px;"></asp:Label>
        </p>
    <asp:Button ID="btn_insert" runat="server" Text="Insert"
        style="position:absolute; top: 365px; left: 125px; right: 1122px; height: 28px;"
        onclick="btn_insert_Click" />
    <asp:Button ID="btn_update" runat="server" Text="Update"
        style="position:absolute; top: 366px; left: 294px;"
        onclick="btn_update_Click1"/>
    <asp:Button ID="btndelete" runat="server" Text="Delete"
        style="position:absolute; top: 365px; left: 359px;" onclick="btndelete_Click1"/>


    </form>
</body>
</html>
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class RegistrationPage : System.Web.UI.Page
{
    DataSet ds;
    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter adp;
    //SqlDataReader reader;
    DataTable dt;
    #region  Events
    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ConnectionString);
     
        if (!IsPostBack)
        {
            FillAge();
            FillQualification();
            FillGrid();
            if (Request.QueryString["sa"] != null)
            {
                GetaDetailsById(int.Parse(Request.QueryString["sa"].ToString()));
               
            }
            else
            {

            }
          
        }
    }

    private void GetaDetailsById(int sa)
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
          
            cmd = new SqlCommand("SelectEmployee_Sallary_Qualification1",con);        
          
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@RegID",sa);
            adp = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
               
                string[] Fname =dt.Rows[0]["Name"].ToString().Split(' ');
                if (Fname.Count() == 1)
                {
                    TxtFName.Text = Fname[0].ToString();
                    TxtlName.Text = "";
                }
                else if (Fname.Count() == 2)
                {
                    TxtFName.Text = Fname[0].ToString();
                    TxtlName.Text = Fname[1].ToString();
                }
                else if (Fname.Count() == 3)
                {
                    string a = Fname[0].ToString();
                    a += Fname[1].ToString();
                    TxtFName.Text = a.ToString();
                    TxtlName.Text = Fname[2].ToString();
                }
              
                Txtdob.Text = dt.Rows[0]["DOB"].ToString();
                TxtEmail.Text = dt.Rows[0]["Email"].ToString();
                TxtMobNo.Text = dt.Rows[0]["MobNo"].ToString();
                TxtAddress.Text = dt.Rows[0]["Address"].ToString();
                TxtSallary.Text = dt.Rows[0]["Sallary"].ToString();
                DDLQualification.SelectedValue = dt.Rows[0]["Qid"].ToString();
                if (dt.Rows[0]["Gender"].ToString()=="Male")
                {
                    RadMale.Checked = true;
                }
                else
                {
                    RadFimale.Checked = true;
                }
                DDLAge.SelectedValue = dt.Rows[0]["Age"].ToString();
            }
        }
        catch (Exception ex)
        {
            string a = ex.ToString();
        }

    }
    protected void Btnsubmit_Click(object sender, EventArgs e)
    {
        string gen;
        //string nam;

        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("InsertEmployee_Sallary_Qualification", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Name", TxtFName.Text.Trim() + " " + TxtlName.Text.Trim());
            cmd.Parameters.AddWithValue("@DOB", Txtdob.Text);
            cmd.Parameters.AddWithValue("@Email", TxtEmail.Text);
            cmd.Parameters.AddWithValue("@MobNo", Int64.Parse(TxtMobNo.Text));
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@Age", int.Parse(DDLAge.SelectedItem.Text));
            if (RadMale.Checked == true)
            {
                gen = RadMale.Text;
            }
            else
            {
                gen = RadFimale.Text;
            }
            cmd.Parameters.AddWithValue("@Gender", gen);
            cmd.Parameters.AddWithValue("@sallary", Convert.ToDouble(TxtSallary.Text));
            cmd.Parameters.AddWithValue("@Qualification", DDLQualification.SelectedItem.Text);
            cmd.ExecuteNonQuery();
            FillGrid();
        }
        catch (Exception EX)
        {
            string A = EX.ToString();
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            if (this.con != null) this.con = null;
            if (this.con != null) this.con = null;
        }
    }
    #endregion
    #region  Methods
    public void FillAge()
    {
        for (int i = 21; i <= 90; i++)
            DDLAge.Items.Add(i.ToString());
        DDLAge.Items.Insert(0, "Select");
    }
    public void FillQualification()
    {
     
        adp = new SqlDataAdapter("Select * from Qulification", con);
        dt = new DataTable();
        adp.Fill(dt);
        DDLQualification.DataSource = dt;
        DDLQualification.DataTextField = "Qualifiction";
        DDLQualification.DataValueField = "Qid";
        DDLQualification.DataBind();
        DDLQualification.Items.Insert(0, "Select");
     
    }
    public void FillGrid()
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("Select_Employee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                GridView1.DataSource = dt;
               
                GridView1.DataBind();
                GridView1.Columns[10].ItemStyle.Width = 0;
            }
            con.Close();
        }
        catch (Exception ex)
        {
            string ef = ex.ToString();
        }
      
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
      
        GridViewRow val = (GridViewRow)((LinkButton)e.CommandSource).NamingContainer;
        int RowIndex = val.RowIndex;
   
        string[] Fname = GridView1.Rows[RowIndex].Cells[1].Text.Split(' ');
       
        if (Fname.Count() == 1)
        {
            TxtFName.Text = Fname[0].ToString();
            TxtlName.Text = "";
        }
        else if (Fname.Count() == 2)
        {
            TxtFName.Text = Fname[0].ToString();
            TxtlName.Text = Fname[1].ToString();
        }
        Txtdob.Text = GridView1.Rows[RowIndex].Cells[2].Text;
        TxtEmail.Text = GridView1.Rows[RowIndex].Cells[3].Text;
        TxtMobNo.Text = GridView1.Rows[RowIndex].Cells[4].Text;
        TxtAddress.Text = GridView1.Rows[RowIndex].Cells[5].Text;
        DDLAge.SelectedValue = GridView1.Rows[RowIndex].Cells[6].Text;
        if (GridView1.Rows[RowIndex].Cells[7].Text == "Male")
        {
            RadFimale.Checked = false;
            RadMale.Checked = true;
        }
        else
        {
            RadMale.Checked = false;
            RadFimale.Checked = true;
        }
        DDLQualification.SelectedItem.Text = GridView1.Rows[RowIndex].Cells[9].Text;
        TxtSallary.Text = GridView1.Rows[RowIndex].Cells[8].Text;
        HiddenField1.Value = GridView1.Rows[RowIndex].Cells[10].Text;
    }     
   
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
      

    }

    protected void btn_next_Click(object sender, EventArgs e)
    {
        Response.Redirect("ViewPAgebyrequeststrin.aspx");
    }
    protected void btn_update_Click1(object sender, EventArgs e)
    {
        string gen;
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            cmd = new SqlCommand("UpdateEmployee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@RegID", HiddenField1.Value);
            cmd.Parameters.AddWithValue("@Name", TxtFName.Text.Trim() + " " + TxtlName.Text.Trim());
            cmd.Parameters.AddWithValue("@DOB", Txtdob.Text);
            cmd.Parameters.AddWithValue("@Email", TxtEmail.Text);
            cmd.Parameters.AddWithValue("@MobNo", Int64.Parse(TxtMobNo.Text));
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@Age", int.Parse(DDLAge.SelectedItem.Text));
            if (RadMale.Checked == true)
            {
                gen = RadMale.Text;
            }
            else
            {
                gen = RadFimale.Text;
            }
            cmd.Parameters.AddWithValue("@Gender", gen);
            cmd.Parameters.AddWithValue("@sallary", Convert.ToDouble(TxtSallary.Text));
            cmd.Parameters.AddWithValue("@Qualification", DDLQualification.SelectedItem.Text);
            //cmd.ExecuteNonQuery();
            con.Close();
            FillGrid();


        }
        catch (Exception ex)
        {
            string ec = ex.ToString();
        }
        finally
        {
            //con.Close();
        }
    }
    protected void btndelete_Click1(object sender, EventArgs e)
    {
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            cmd = new SqlCommand("Delete_Employee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("RegID", HiddenField1.Value);

            cmd.ExecuteNonQuery();
            con.Close();
            FillGrid();


        }
        catch (Exception ex)
        {
            string ec = ex.ToString();
        }
    }
    protected void btn_insert_Click(object sender, EventArgs e)
    {
        string gen;
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            cmd = new SqlCommand("InsertEmployee_Sallary_Qualification", con);
            adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Parameters.AddWithValue("@RegID", HiddenField1.Value);
            cmd.Parameters.AddWithValue("@Name", TxtFName.Text.Trim() + " " + TxtlName.Text.Trim());
            cmd.Parameters.AddWithValue("@DOB", Txtdob.Text);
            cmd.Parameters.AddWithValue("@Email", TxtEmail.Text);
            cmd.Parameters.AddWithValue("@MobNo", Int64.Parse(TxtMobNo.Text));
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@Age", int.Parse(DDLAge.SelectedItem.Text));
            if (RadMale.Checked == true)
            {
                gen = RadMale.Text;
            }
            else
            {
                gen = RadFimale.Text;
            }
            cmd.Parameters.AddWithValue("@Gender", gen);
            cmd.Parameters.AddWithValue("@sallary", Convert.ToDouble(TxtSallary.Text));
            cmd.Parameters.AddWithValue("@Qualification", DDLQualification.SelectedItem.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            FillGrid();


        }
        catch (Exception ex)
        {
            string ec = ex.ToString();
        }
        finally
        {
            //con.Close();
        }
    }
}

 
    #endregion
  Table Design

  Employee Table

Sallary Table
 
Qualification Table

StoreProcedure
Select Procedure
ALTER Procedure Select_Employee_Sallary_Qualification
AS
BEGIN
SELECT emp.RegID,emp.Name,emp.DOB,emp.Email,emp.MobNo,emp.Address,emp.Age,emp.Gender,sal.Sallary,Qual.Qualifiction
     FROM  Employee as emp
        INNER JOIN Sallary as sal ON emp.RegID=sal.RegID
        INNER JOIN Qulification AS Qual ON emp.RegID=Qual.RegID
END
______________________________________________________________________

ALTER Procedure SelectEmployee_Sallary_Qualification1
(
 @RegID int
 )
 as
BEGIN
        SELECT em.Name,em.Dob,em.Email,em.MobNo,em.Address,em.Age,em.Gender,sal.Sallary,qu.Qualifiction,qu.qid
        from Employee em , Sallary sal ,Qulification qu where em.RegId=sal.RegId and em.RegId=Qu.RegId and em.RegId=@RegID
         
        END
Insert Procedure in Multiple table using Foregn Key)

ALTER Procedure InsertEmployee_Sallary_Qualification
(
@Name varchar(50),
@DOB varchar(50),
@Email varchar(50),
@MobNo bigint,
@Address varchar(50),
@Age int,
@Gender varchar(50),

@sallary float,

@Qualification varchar(50)
)
As

BEGIN
BEGIN TRY
BEGIN TRANSACTION
insert into Employee (Name,DOB,Email,MobNo,Address,Age,Gender) values(@Name,@DOB,@Email,@MobNo,@Address,@Age,@Gender)

DECLARE @RegID int;
Set @RegID=(Select MAX(RegID) from Employee)

Insert into Sallary (RegID,Sallary) values (@RegID,@sallary)
insert into Qulification (RegID,Qualifiction) values(@RegID,@Qualification)

print 'success'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ERROR'
END CATCH
END
Delete Procedure
ALTER Procedure Delete_Employee_Sallary_Qualification
(
@RegID int
)
As
BEGIN
BEGIN TRY
BEGIN TRANSACTION


Delete from  Sallary  where RegID=@RegID

Delete Qulification where RegID=@RegID
Delete from Employee
where RegID=@RegID


print 'success'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ERROR'
END CATCH
END
Update StoreProcedure
ALTER Procedure UpdateEmployee_Sallary_Qualification
(
 @RegID int,
@Name varchar(50),
@DOB varchar(50),
@Email varchar(50),
@MobNo bigint,
@Address varchar(50),
@Age int,
@Gender varchar(50),

@sallary float,

@Qualification varchar(50)
)
As

BEGIN
BEGIN TRY
BEGIN TRANSACTION
Update Employee
set Name=@Name,
DOB=@DOB,
Email=@Email,
MobNo=@MobNo,
Address=@Address,
Age=@Age,
Gender=@Gender
where RegID=@RegID

Update Sallary set Sallary=@sallary where RegID=@RegID

Update Qulification set Qualifiction=@Qualification where RegID=@RegID

print 'success'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ERROR'
END CATCH
END
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)