Click here to Skip to main content
11,648,785 members (81,989 online)
Click here to Skip to main content

Implementing Many-To-Many Relationship in ASP.NET

, 28 May 2014 CPOL 4.3K 40 6
Rate this:
Please Sign up or sign in to vote.
In this tip, we are going to create a project to see how many-to-many relationship can be implemented in ASP.NET.

Introduction

This is in continuation to my previous article regarding implementing One-To-Many Relationship in ASP.NET. You may visit the following link:

http://www.codeproject.com/Articles/778643/Implementing-One-To-Many-Relationship-in-ASP-NET

As we know, there are three different types of database relationships:
  • One-to-one relationships occur when each entry in the first table has one and only one counterpart in the second table. One-to-one relationships are rarely used because it is often more efficient to simply put all of the information in a single table.
  • One-to-many relationships are the most common type of database relationship. They occur when each record in the first table corresponds to one or more records in the second table but each record in the second table corresponds to only one record in the first table. For example, the relationship between a Teacherstable and a Studentstable in an elementary school database would likely be a one-to-many relationship, because each student has only one teacher, but each teacher may have multiple students.
  • Many-to-many relationships occur when each record in the first table corresponds to one or more records in the second table and each record in the second table corresponds to one or more records in the first table. For example, the relationship between a Teachersand a Coursestable would likely be many-to-many because each teacher may instruct more than one course and each course may have more than one instructor.

In this tip, we are going to create a project to see how Many-to-Many Relationship can be implemented in ASP.NET.

Background

We will take a simple example of BookAuthorrelationship. For example, an Authorcan write several Books, and a Bookcan be written by several Authors:

We will complete this example in the following three steps:

  1. Database changes
  2. Data Access Layer changes
  3. Presentation Layer changes

Step 1: Database Changes

In this example, we will be creating three tables as follows:

  1. tblBook- Master table to keep Bookdetails
  2. tblAuthor- Master table to keep Authordetails
  3. tblBookAuthor- Table to keep the relational data between Bookand Author

Let’s execute the following SQL statements to create required tables, Stored Procedures and insert some sample data:

CREATE TABLE [tblBook](
    [BookID] [int] NOT NULL,
    [Name] [varchar](50) NULL
    )
CREATE TABLE [tblAuthor](
    [AuthorID] [int] NOT NULL,
    [Name] [varchar](50) NULL
    )
CREATE TABLE [tblBookAuthor](
    [BookID] [int] NOT NULL,
    [AuthorID] [int] NOT NULL
    )

CREATE PROCEDURE [spDeleteBookAuthorRelationByBookID]
    @BookID  int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM [tblBookAuthor]
    WHERE [BookID] = @BookID
END

CREATE PROCEDURE [spInsertBookAuthorRelation]
    @BookID  int,
    @AuthorID int
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [tblBookAuthor]
    VALUES(@BookID, @AuthorID)
END

CREATE PROCEDURE [spGetAllBookAuthor]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT B.Name AS [Book], A.Name AS [Author] FROM [tblBookAuthor] BA
    INNER JOIN [tblBook] B ON BA.BookID = B.BookID
    INNER JOIN [tblAuthor] A ON BA.AuthorID = A.AuthorID
END

CREATE PROCEDURE [spGetAllAuthorByBookID]
    @BookID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [AuthorID] FROM [tblBookAuthor]
    WHERE [BookID] = @BookID
END

INSERT INTO [tblBook] VALUES(1, 'Book-1')
INSERT INTO [tblBook] VALUES(2, 'Book-2')
INSERT INTO [tblBook] VALUES(3, 'Book-3')
INSERT INTO [tblBook] VALUES(4, 'Book-4')
INSERT INTO [tblBook] VALUES(5, 'Book-5')
INSERT INTO [tblBook] VALUES(6, 'Book-6')
INSERT INTO [tblBook] VALUES(7, 'Book-7')
INSERT INTO [tblBook] VALUES(8, 'Book-8')

INSERT INTO [tblAuthor] VALUES(1, 'Author-1')
INSERT INTO [tblAuthor] VALUES(2, 'Author-2')
INSERT INTO [tblAuthor] VALUES(3, 'Author-3')
INSERT INTO [tblAuthor] VALUES(4, 'Author-4')
INSERT INTO [tblAuthor] VALUES(5, 'Author-5')
INSERT INTO [tblAuthor] VALUES(6, 'Author-6')

Step 2: Data Access Layer Changes

Once database changes are ready with us, we are good to proceed with creating the project. So, let’s create a new ASP.NET Empty Web Application - ManyToManyRelation > click OK:

Right-click on the project > Add a Class file > DAL.cs

We will make this as a staticclass so that the methods can be called without instantiating any object of this class. Let’s add the following four public staticmethods to call the respective stored procedures that we have already created:

public static class DAL
{
    public static List<int> GetAllAuthorByBookID(int bookID)
    {
        List<int> authorIDs = new List<int>();
        //Get the Database Connection string from web.config file
        string connString = ConfigurationManager.ConnectionStrings["TESTConnectionString"].ConnectionString;

        using (SqlConnection con = new SqlConnection(connString))
        {
            //Create SqlParameter to hold Lectuere ID
            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@BookID";
            param1.Value = bookID;

            //Create SqlCommand to execute the Stored Procedure
            SqlCommand cmd = new SqlCommand("spGetAllAuthorByBookID", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //Add the parameter to the SqlCommand object
            cmd.Parameters.Add(param1);

            //Open Sql Connection
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                authorIDs.Add(Convert.ToInt32(dr["AuthorID"]));
            }
        }
        return authorIDs;
    }

    public static DataTable GetAllBookAuthor()
    {
        DataTable dtBookAuthor = new DataTable();

        //Get the Database Connection string from web.config file
        string connString = ConfigurationManager.ConnectionStrings["TESTConnectionString"].ConnectionString;

        using (SqlConnection con = new SqlConnection(connString))
        {
            //Open Sql Connection
            con.Open();
            //Create Data Adapter object to fill DataTable with the data
            SqlDataAdapter da = new SqlDataAdapter("spGetAllBookAuthor", con);
            da.Fill(dtBookAuthor);
        }
        return dtBookAuthor;
    }

    public static void DeleteBookAuthorRelationByBookID(int bookID)
    {
        //Get the Database Connection string from web.config file
        string connString = ConfigurationManager.ConnectionStrings["TESTConnectionString"].ConnectionString;

        using (SqlConnection con = new SqlConnection(connString))
        {
            //Create SqlParameter to hold Lectuere ID
            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@BookID";
            param1.Value = bookID;

            //Create SqlCommand to execute the Stored Procedure
            SqlCommand cmd = new SqlCommand("spDeleteBookAuthorRelationByBookID", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //Add the parameter to the SqlCommand object
            cmd.Parameters.Add(param1);

            //Open Sql Connection
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    public static void InsertBookAuthorRelation(int bookID, int authorID)
    {
        //Get the Database Connection string from web.config file
        string connString = ConfigurationManager.ConnectionStrings["TESTConnectionString"].ConnectionString;

        using (SqlConnection con = new SqlConnection(connString))
        {
            //Create SqlParameter to hold Lectuere ID
            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@BookID";
            param1.Value = bookID;

            //Create SqlParameter to hold Author ID
            SqlParameter param2 = new SqlParameter();
            param2.ParameterName = "@AuthorID";
            param2.Value = authorID;

            //Create SqlCommand to execute the Stored Procedure
            SqlCommand cmd = new SqlCommand("spInsertBookAuthorRelation", con);
            cmd.CommandType = CommandType.StoredProcedure;

            //Add both the parameters to the SqlCommand object
            cmd.Parameters.Add(param1);
            cmd.Parameters.Add(param2);

            //Open Sql Connection
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Step 3: Presentation Layer Changes

Now we are only left with frontend changes. Let’s add a new Web Form – WebForm1.aspx (keep the default name).

We will have a ListBox(to display Books) control and a CheckBoxList(to display Authors) control:

<table style="width: 500px;" border="1">
    <tr>
        <th>Book</th>
        <th>Author</th>
    </tr>
    <tr>
        <td style="width: 50%; height: 100px">
            <asp:ListBox ID="lstBook" runat="server" 
            Width="100%" Height="100%"></asp:ListBox>
        </td>
        <td style="width: 50%; height: 100px">
            <!-- added Panel to show vertical scrollbar -->
            <asp:Panel ID="Panel2" runat="server" 
            ScrollBars="Vertical" Height="100%">
                <asp:CheckBoxList ID="cblAuthor" 
                runat="server" Width="100%">
                </asp:CheckBoxList>
            </asp:Panel>
        </td>
    </tr>
</table>

NOTE: We have added an <asp:Panel> control to show a vertical scrollbar when there are more records to display.

Let’s use Data Source Configuration Wizard to bind Data Source as follows:

  • lstBook => tblBook
  • cblAuthor=> tblAuthor

NOTE: Please visit http://www.codeproject.com/Articles/778643/Implementing-One-To-Many-Relationship-in-ASP-NET for detailed steps about how to use Data Source Configuration Wizard.

You should find <connectionStrings> section is added in the web.config file:

<connectionStrings>
    <add name="TESTConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TEST;Integrated Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Now check the WebForm.aspx to see the changes that have taken place:

<table style="width: 500px;" border="1">
    <tr>
        <th>Book</th>
        <th>Author</th>
    </tr>
    <tr>
        <td style="width: 50%; height: 100px">
            <asp:ListBox ID="lstBook" 
            runat="server" Width="100%" Height="100%"                
DataSourceID="SqlDataSource1" DataTextField="Name" 
DataValueField="BookID"></asp:ListBox>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:TESTConnectionString %>"
                SelectCommand="SELECT * FROM [tblBook]"></asp:SqlDataSource>
        </td>
        <td style="width: 50%; height: 100px">
            <!-- added Panel to show vertical scrollbar -->
            <asp:Panel ID="Panel2" runat="server" 
            ScrollBars="Vertical" Height="100%">
                <asp:CheckBoxList ID="cblAuthor" 
                runat="server" Width="100%"
                    DataSourceID="SqlDataSource2" 
                    DataTextField="Name" DataValueField="AuthorID">
                </asp:CheckBoxList>
                <asp:SqlDataSource ID="SqlDataSource2" runat="server"
                    ConnectionString="<%$ ConnectionStrings:TESTConnectionString %>"
                    SelectCommand="SELECT * FROM [tblAuthor]"></asp:SqlDataSource>
            </asp:Panel>
        </td>
    </tr>
</table>

Note: SqlDataSource1and SqlDataSource2controls have been added by the Data Source Configuration Wizard.

Let’s run the web site once and see if the page is displaying data as expected:

Now we will add a Buttoncontrol to call the method which will actually delete the existing mappings and insert new relations in the database and a GridViewcontrol to display the existing relationships:

<asp:Button ID="btnSave" runat="server" 
Text="Save" OnClick="btnSave_Click" />
<asp:GridView ID="GridView1" runat="server" 
Width="500px"></asp:GridView>

Here is the code written in Save button click event in WebForm1.aspx.cs file:

protected void btnSave_Click(object sender, EventArgs e)
{
    int bookId, authorId;
    // Check if a Book is selected
    if (lstBook.SelectedIndex >= 0)
    {
        bookId = Convert.ToInt32(lstBook.Items[lstBook.SelectedIndex].Value);
        // Call method to delete all the existing entries from tblBookAuthor by BookID
        DAL.DeleteBookAuthorRelationByBookID(bookId);
        foreach (ListItem author in cblAuthor.Items)
        {
            if (author.Selected == true)
            {
                authorId = Convert.ToInt32(author.Value);
                // code to insert Book - Author relation in a table
                DAL.InsertBookAuthorRelation(bookId, authorId);
            }
        }
        //Bind the GridView to display existing Book-Author relationships
        GridView1.DataSource = DAL.GetAllBookAuthor();
        GridView1.DataBind();
    }
}

Let’s run the web site and click Save button after selecting a Bookfrom the ListBoxand one or more Authorin the CheckBoxListcontrol. The inserted data should immediately reflected to the GridViewcontrol:

Now let’s add functionality which to allow user to select a Bookfrom the ListBoxand display the corresponding Authoralready checked in the CheckBoxListcontrol:

Set AutoPostBack="true"and OnSelectedIndexChanged="lstBook_SelectedIndexChanged" to the lstBook ListBoxcontrol and add the following code in code behind:

protected void lstBook_SelectedIndexChanged(object sender, EventArgs e)
{
    int bookId, authorId;
    // Check if a Book is selected
    if (lstBook.SelectedIndex >= 0)
    {
        bookId = Convert.ToInt32(lstBook.Items[lstBook.SelectedIndex].Value);
        //Get all the Authors associated with the selected Book
        List<int> authorIDs = DAL.GetAllAuthorByBookID(bookId);
        foreach (ListItem author in cblAuthor.Items)
        {
            authorId = Convert.ToInt32(author.Value);
            if (authorIDs.Contains(authorId))
            {
                author.Selected = true;
            }
            else
            {
                author.Selected = false;
            }
        }
    }
}

Let’s execute the web site and select one Bookfrom the ListBoxcontrol. If there are Authors already associated with the selected Bookin the database, it will show those Authors checked in the CheckBoxListcontrol:

In this tip, we created a simple end-to-end project to implement many-to-many relationship in ASP.NET. Hope you guys like this post. Please share your queries or feedback.

Happy coding Smile | :)

History

  • 28th May, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Debabrata_Das
Architect
India India
My name is Debabrata Das, also known as DD. I started working as a FoxPro 2.6 developer then gradually moved towards VB6, Classic ASP, COM, DCOM. Presently in love with ASP.NET and C#.

I believe in "the best way to learn is to teach". Passionate about finding a more efficient solution of any given problem.

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150804.4 | Last Updated 28 May 2014
Article Copyright 2014 by Debabrata_Das
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid