Post
Ask Question

How to Establish Relationship Between Tables in an ASP.Net Web Application

Introduction

In this article, I am creating an ASP.NET Web Application showing relationships among Countries, States and Cities. I am using ADO.NET to connect SQL Server with ASP.NET. I am creating this application because I think it will be very helpful to you. There are many basic programmers who do not know how to create and display a relationship among a Country, States of that Country and Cities of that State. Friends, If you do not understand me then just proceed for now and at the end of the article you will get my message.

If we have three dropdownlists for each of Country, State and City and we want that when we select a Country like India then the States belonging to that Country will populate the second dropdownlist (suppose Uttaranchal), similarly if we select any State then the Cities of that State will populate the third dropdownlist. Please view the following snapshot for help:

final.jpg

Prerequisites

There are the following prerequisites for creating this application:

  • Visual Studio (Front End)
  • SQL Server (Back End)
  • ADO.NET   (For Connectivity)

Create Database

Let's create a database in which three tables are created and also create a relationship among them. I am creating this database in SQL Server 2012. Let's understand this step-by-step.

Note: You must know the Constraints like Primary Key and Foreign Key, that are very useful for creating a relationship among tables.  

Step 1: Open SQL Server (any version).

Step 2: In the New Query wizard:

NewQuery.jpg

Step 3: Create a database with the following command:

CREATE DATABASE SampleDB

Step 4: Create tables with the names Tbl_Country, Tbl_State and Tbl_City.

  1. Country Table
    1. USE SampleDB  
    2. CREATE TABLETbl_Country  
    3. (  
    4.        PKCountry_IDint primarykey identity(1,1),  
    5.        Country_Namevarchar(50)  
    6. )

    Insert values into this table, after inserting:

    Tbl_Country.jpg
     

  2. State Table

    Let's create this table with the following procedure.

    Step 1: Go to Object Explorer, expand your database and right-click on "Tables" for creating a new table.

    NewTable.jpg

    Step 2: Design the table with the fields PKState_ID (Primary Key), FKCountry_ID and State_Name and before saving the table follow the instructions given below.
     

    1. In the design wizard, right-click on the FKCountry_ID column for creating a relationship.

      Relationship-in-State.jpg

      Note: Ensure that you created the FKCountry_ID column as a Primary Key in the Tbl_Country Table. The column name may differ but the values should be the same.
       
    2. After you the click on "Relationships", the following wizard will open. Click on "Add".

      AddFK-in-State.jpg
       
    3. After clicking on Add provide the name of the Identity (optional) and click on "Tables And Columns Specific" as in the following:

      AddFK1-in-State.jpg
       
    4. In the next Tables and Columns wizard, Select Tbl_Country as a Primary Key Table and select PKCountry_ID as a Primary Key column in the columns in the left pane and in the right pane select FKCountry_ID as a Foreign Key column as in the following.

      Tables&Columns-in-State.jpg

      Click on OK.
       
    5. In the next wizard, Click "Close".

      AddFK2-in-State.jpg
       
    6. Save your table and provide the table name.

      Save-in-State.jpg
       
    7. When you click on "Save", the Save wizard will open. Ignore the warning and click "Yes".

      Save1-in-State.jpg
       
    Step 3: Insert values into the State table as in the following:

    Tbl_State.jpg
     
  3. City Table

    Create this table using the following procedure.

    Step 1: Create a new table with design view as I mentioned in the previous Step 1.

    Step 2: Design the table with the fields PKCity_ID (Primary Key), FKState_ID, City_Name and before saving the table use the following instructions.
     
    1. In the design wizard, right-click on the FKState_ID column to create the relationship.

      Relationship-in-City.jpg

      Note: Ensure that you have created the FKState_ID column as a Primary Key in the Tbl_State Table. The column name may differ but the values should be the same.
       
    2. After you the click on Relationships, a wizard will open. Click on "Add".
       
    3. Provide the name of Identity (optional) and click on "Tables And Columns Specific" as in the following.

      AddFK1-in-City.jpg
       
    4. In the next Tables and Columns wizard, select Tbl_State as a Primary Key of the table and select PKState_ID as a Primary Key column in the columns in the left pane and in the right pane select FKState_ID as a Foreign Key column as in the following:

      Tables&Columns-in-City.jpg

      Click on "OK".
       
    5. In the next wizard, click "Close".

      AddFK2-in-City.jpg
      6.    
    6. Save your table and provide the table name.

      Save-in-City.jpg
       
    7. When you click on "Save", the Save wizard will open. Ignore the warning and click on "Yes".

      Save1-in-City.jpg
       
    Step 3: Insert values into the City table as in the following:

    Tbl_City.jpg    

    Tbl_City2.jpg  

Create an application on Visual Studio

Let's create an ASP.NET Web Application using ADO.NET for connectivity with the database. I am creating this application with 2-tier architecture on Visual Studio 2013 Preview. Let's create this application step-by-step.

Step 1: "File" -> "New" -> "Project..."

Step 2: Select "ASP.NET Web Application" then provide the application name.

NewProject.jpg

Step 3: Select "Empty Project Template" to create this application.

EmptyTemplate.jpg

Step 4: In the Solution Explorer, right-click on your project and go to "Add" > "New Item".

Step 5: Select "Web Form" then enter a web form name.

webform.jpg

Step 6: Design your front-end page with the following source:

  1. <form id="form1" runat="server">  
  2.     <table align="center" style="height: 230px; width: 362px">  
  3.         <tr>  
  4.             <td colspan="2" style="font-size: x-large; text-align: center;">Welcome to CSC</td>  
  5.         </tr>  
  6.         <tr>  
  7.             <td class="auto-style1" style="font-size: large">Country</td>  
  8.             <td><asp:DropDownList ID="DDLCountry" runat="server" AutoPostBack="false" OnSelectedIndexChanged="DDLCountry_SelectedIndexChanged" Height="22px"></asp:DropDownList></td>  
  9.         </tr>  
  10.         <tr>  
  11.             <td class="auto-style1" style="font-size: large">State</td>  
  12.             <td><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="false" OnSelectedIndexChanged="DDLState_SelectedIndexChanged" Height="22px" Width="122px"></asp:DropDownList></td>  
  13.         </tr>  
  14.         <tr>  
  15.             <td class="auto-style1" style="font-size: large">City</td>  
  16.             <td><asp:DropDownList ID="DropDownList2" runat="server" Height="22px" Width="122px"></asp:DropDownList></td>  
  17.         </tr>  
  18.     </table>  
  19. </form> 

Define the following source in the "<head>":

  1. <style type="text/css">  
  2.         .auto-style1 {  
  3.             width: 145px;  
  4.         }  
  5. </style> 

cscform.jpg

Step 7: In the Solution Explorer right-click on your project and go to "Add" > "New Item".

Step 8: Select "Class", enter a class name.

dal.jpg

Step 9: Design your DAL class with the following code:

  1. using System.Data;  
  2. using System.Data.SqlClient;  
  3. namespace CSCRelationshipApp  
  4. {  
  5.     public class DAL  
  6.     {  
  7.         SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP44; Initial Catalog=SampleDB; User ID=YOUR USERNAME; Password=YOUR PASSWORD");  
  8.         SqlCommand cmd;  
  9.         SqlDataAdapter adap;  
  10.         DataTable dt;  
  11.         public DataTable Get_Country()  
  12.         {  
  13.             adap = new SqlDataAdapter("Select * from Tbl_Country",con);  
  14.             dt = new DataTable();  
  15.             adap.Fill(dt);  
  16.             return dt;  
  17.         }  
  18.         public DataTable Get_State(int Country_ID)  
  19.         {  
  20.             adap = new SqlDataAdapter("Select * from Tbl_State where FKCountry_ID=" + Country_ID + "", con);  
  21.             dt = new DataTable();  
  22.             adap.Fill(dt);  
  23.             return dt;  
  24.         }  
  25.         public DataTable Get_City(int State_ID)  
  26.         {  
  27.             adap = new SqlDataAdapter("Select * from Tbl_City where FKState_ID=" + State_ID + "", con);  
  28.             dt = new DataTable();  
  29.             adap.Fill(dt);  
  30.             return dt;  
  31.         }  
  32.     }  
  33. } 

Step 10: Design your CSCForm.cs class with the following code:

  1. namespace CSCRelationshipApp  
  2. {  
  3.     public partial class CSCForm : System.Web.UI.Page  
  4.     {  
  5.         DAL ObjDal = new DAL();  
  6.         protected void Page_Load(object sender, EventArgs e)  
  7.         {  
  8.             if (!IsPostBack)  
  9.             {  
  10.                 Fill_Country();  
  11.             }  
  12.         }  
  13.         public void Fill_Country()  
  14.         {  
  15.             DDLCountry.DataSource = ObjDal.Get_Country();  
  16.             DDLCountry.DataTextField = "Country_Name";  
  17.             DDLCountry.DataValueField = "PKCountry_ID";  
  18.             DDLCountry.DataBind();  
  19.             DDLCountry.Items.Insert(0, new ListItem("--Select--", "0"));  
  20.         }  
  21.         public void Fill_State()  
  22.         {  
  23.             DDLState.DataSource = ObjDal.Get_State(Convert.ToInt32(DDLCountry.Text));  
  24.             DDLState.DataTextField = "State_Name";  
  25.             DDLState.DataValueField = "PKState_ID";  
  26.             DDLState.DataBind();  
  27.             DDLState.Items.Insert(0, new ListItem("--Select--", "0"));  
  28.         }  
  29.         public void Fill_City()  
  30.         {  
  31.             DDLCity.DataSource = ObjDal.Get_City(Convert.ToInt32(DDLState.Text));  
  32.             DDLCity.DataTextField = "City_Name";  
  33.             DDLCity.DataValueField = "PKCity_ID";  
  34.             DDLCity.DataBind();  
  35.             DDLCity.Items.Insert(0, new ListItem("--Select--", "0"));  
  36.         }  
  37.         protected void DDLCountry_SelectedIndexChanged(object sender, EventArgs e)  
  38.         {  
  39.             Fill_State();  
  40.         }  
  41.         protected void DDLState_SelectedIndexChanged(object sender, EventArgs e)  
  42.         {  
  43.             Fill_City();  
  44.         }  
  45.     }  
  46. }   

Step 11: All done. Now debug.

final.jpg

Summary

So far in this article, we saw how to create and relate Country, State and City from a Drop Down List in an application. I used Visual Studio 2013 Preview for the Front End and SQL Server 2012 for the Back End. I am sure that this article will help for creating and showing relationships among Country, State and City. So, just go for it and let me know if you have any problem regarding the development.

Don't forget to comment.


Similar Articles