Connectivity Asp.Net with Sql Server Disconnected Mode

(Using State,City concept with foreign key and runtime binding CheckBoxList and DropDownList)

//create database test and use this database for  table

Create  database test

use test

//create table  StateName …………………………..

create table StateName (SrNo int identity(1,1),StateCode nvarchar(50) primary key,StateName nvarchar(50) unique)

//create table  CityName  relationship with StateName (foreign key with StateName)

create table CityName (SrNo int identity(1,1),StateCode nvarchar(50) foreign keyreferences stateName(StateCode) on delete cascade,CityCode nvarchar(50) primary key,CityName nvarchar(50) unique)

Note: using on delete cascade for which concept when you will delete any state from state table by default delete  all city  from city table related to state table

//insert record  both table……………………..

 insert into StateName values ('up11','Uttar Pradesh')

insert into StateName values ('mp11','Madhya Pradesh')

insert into StateName values ('hi11','Himachal Pradesh')

insert into CityName values ('up11','vns11','Varanasi')

insert into CityName values ('up11','gzb11','Ghaziabad')

insert into CityName values ('up11','gb11','G B Nagar')

insert into CityName values ('mp11','bh11','Bhopal')

insert into CityName values ('mp11','re11','Reewa')

//create table EmpDetails   …………………………..

create table EmpDetails(Srno int primary key,Name nvarchar(50),Gendernvarchar(50),EmailId nvarchar(50) unique,Password nvarchar(50),QulificationDetailsnvarchar(200),LastQulification nvarchar(50),MobileNo nvarchar(50) unique ,CollegeNamenvarchar(50),StateName nvarchar(50),CityName nvarchar(50))

 //state  city with drop down list in update panel like …………………………..

<asp:UpdatePanel ID="UpdateCity" runat="server" ChildrenAsTriggers="False"

                    UpdateMode="Conditional">

                    <ContentTemplate>

                        <asp:DropDownList ID="ddlcityname" runat="server" Width="135px" >

                        </asp:DropDownList>

                    </ContentTemplate>

                </asp:UpdatePanel>

###############################################################################

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

 public partial class Regitration : System.Web.UI.Page

{

    SqlConnection con;

    string quldetails;

    //create method  for  gridview binding.........

    private void GridViewBind()

    {

        // fetch  data  from table using substing metthod....

  string com = "select SrNo,Name,Gender,EmailId,QulificationDetails= substring (QulificationDetails,0,20)+'....',LastQulification,MobileNo,CollegeName,StateName,CityName from EmpDetails";

        SqlCommand cmd = new SqlCommand(com, con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds.Tables[0];

        GridView1.DataBind();

    }

    //create method  for city binding in drop down list .........

    private void CityBind()

    {

       

        SqlCommand cmd = new SqlCommand("select CityCode,CityName from CityName   where StateCode='" + ddlstatename.SelectedValue + "'", con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        ddlcityname.DataSource = ds.Tables[0];

        ddlcityname.DataTextField = "CityName";

        ddlcityname.DataValueField = "CityCode";

        ddlcityname.DataBind();

        ddlcityname.Items.Insert(0, "Select");

    }

    //create method  for State binding in drop down list .........

    private void StateBind()

    {

        SqlCommand cmd = new SqlCommand("select StateCode,StateName from StateName", con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        ddlstatename.DataSource = ds.Tables[0];

        ddlstatename.DataTextField = "StateName";

        ddlstatename.DataValueField = "statecode";

        ddlstatename.DataBind();

        ddlstatename.Items.Insert(0, "Select");

    }

  

   

 protected void Page_Load(object sender, EventArgs e)

    {

        

con = new SqlConnection("Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True");

        if (!IsPostBack)

        {

            //call method....for gridview binding

            GridViewBind();

            //call method....for State binding

            StateBind();

            ddlcityname.Items.Insert(0, "Select"); //for  initial record "Select"

            //this code  for  runtime course  bind with drop down list..............................

 string[] course = new string[10] { "Select", "B A", "B C A", "B B A", "B.Sc.", "M A", "M C A", "B. Tech", "M. Tech", "B.Com" };

            foreach (var c in course)

            {

                ddlCourse.Items.Add(c.ToString());

            }

            //this code  for  runtime qulification  bind with checkboxlist..............................

            string[] qul = new string[4] { "High School", "Intermidiate", "Graduation", "Post Graduation" };

            foreach (var q in qul)

            {

                CblQulification.Items.Add(q.ToString());

            }

        }

    }

  

    protected void ddlstatename_SelectedIndexChanged(object sender, EventArgs e)

    {

        UpdateCity.Update();

        CityBind();

       

    }

    protected void BtnSave_Click(object sender, EventArgs e)

    {

        // How to use CheckBoxList ...........................

        foreach (ListItem li in CblQulification.Items)

        {

            if (li.Selected)

            {

                quldetails += li.Value.ToString() + ",";

            }

        }

        quldetails = quldetails.Remove(quldetails.Length - 1);

        string com = "insert into EmpDetails values('" + Convert.ToInt32(txtsrno.Text) +"','" + txtname.Text + "','" + RbGender.SelectedValue + "','" + txtemailid.Text + "','" + txtpassword.Text + "','" + quldetails + "','" + ddlCourse.SelectedItem.Text + "','" + txtmobileno.Text + "','" + txtcollegename.Text + "','" + ddlstatename.SelectedItem.Text + "','" + ddlcityname.SelectedItem.Text + "')";

        SqlCommand cmd = new SqlCommand(com, con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

     LMessage.Visible = true;

     LMessage.Text = "Save  Data Successfully";

      //this  code for empty all field after insert data..................

     txtname.Text = String.Empty; txtmobileno.Text = ""; txtcpassowd.Text = ""; txtcollegename.Text = ""; txtemailid.Text = "";

     GridViewBind();

    }

    //this code  for update record by  srno...................

    protected void btnupdate_Click(object sender, EventArgs e)

    {

         //How to use CheckBoxList ...........................

        foreach (ListItem li in CblQulification.Items)

        {

            if (li.Selected)

            {

                quldetails += li.Value.ToString() + ",";

            }

        }

        quldetails = quldetails.Remove(quldetails.Length - 1);

        string com = "update EmpDetails set Name='"+txtname.Text+"',Gender='"+RbGender.SelectedItem.Text+"',EmailId='"+txtemailid.Text+"',Password='"+txtpassword.Text+"',QulificationDetails='"+quldetails+"',LastQulification='"+ddlCourse.SelectedItem.Text+"', MobileNo='"+txtmobileno.Text+"',CollegeName='"+txtcollegename.Text+"',StateName='"+ddlstatename.SelectedItem.Text+"',CityName='"+ddlcityname.SelectedItem.Text+"' where Srno='"+Convert.ToInt32(txtsrno.Text)+"'";

        SqlCommand cmd = new SqlCommand(com, con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

     

        LMessage.Visible = true;

        LMessage.Text = "Data update Successfully";

        GridViewBind();

    }

    // code for  show  record  by  SrNo...............................

    protected void btnshow_Click(object sender, EventArgs e)

    {

        string com = "select * from EmpDetails where srno='" +Convert.ToInt32(txtsrno.Text) + "'";

        SqlCommand cmd = new SqlCommand(com, con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        if (ds.Tables[0].Rows.Count >= 1)

        {

            DataRow dr = ds.Tables[0].Rows[0];

            txtsrno.Text = dr[0].ToString();

            txtname.Text = dr["name"].ToString();

            RbGender.SelectedValue = dr["Gender"].ToString();

            txtemailid.Text = dr["EmailId"].ToString();

            txtpassword.Text = dr["Password"].ToString();

            txtcpassowd.Text = dr["Password"].ToString();

            txtmobileno.Text = dr["MobileNo"].ToString();

            txtcollegename.Text = dr["CollegeName"].ToString();

            ddlCourse.DataBind();

            ddlCourse.SelectedItem.Selected = false;

            ddlCourse.Items.FindByText(dr["LastQulification"].ToString()).Selected = true;

         

            ddlstatename.DataBind();

            ddlstatename.SelectedItem.Selected = false;

            ddlstatename.Items.FindByText(dr["StateName"].ToString()).Selected = true;

            CityBind();

            ddlcityname.DataBind();

            ddlcityname.SelectedItem.Selected = false;

            ddlcityname.Items.FindByText(dr["CityName"].ToString()).Selected = true;

          

      

            string[] kush = dr["QulificationDetails"].ToString().Split(',');

            for (int i = 0; i < kush.Length; i++)

            {

                foreach (ListItem li in CblQulification.Items)

                {

                    if (li.Text == kush[i])

                    {

                        li.Selected = true;

                    }

                }

            }

          }

   }

    //code  for delete record with srno.........................................

    protected void btnDelete_Click(object sender, EventArgs e)

    {

        string com = "delete from EmpDetails where Srno='" +Convert.ToInt32(txtsrno.Text) + "'";

        SqlCommand cmd = new SqlCommand(com, con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        LMessage.Visible = true;

        LMessage.Text = "Data delete Successfully";

        GridViewBind();

    }

}