Implementing Many-To-Many Relationship in ASP.NET

• Download source - 50.6 KB

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

• 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 Teachers table and a Students table 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 Teachers and a Courses table 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 Book – Author relationship. For example, an Author can write several Books, and a Book can 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 Book details
2. tblAuthor - Master table to keep Author details
3. tblBookAuthor - Table to keep the relational data between Book and 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 static class so that the methods can be called without instantiating any object of this class. Let’s add the following four public static methods 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: SqlDataSource1 and SqlDataSource2 controls 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 Button control to call the method which will actually delete the existing mappings and insert new relations in the database and a GridView control 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 Book from the ListBox and one or more Author in the CheckBoxList control. The inserted data should immediately reflected to the GridView control:

Now let’s add functionality which to allow user to select a Book from the ListBox and display the corresponding Author already checked in the CheckBoxList control:

Set AutoPostBack="true" and OnSelectedIndexChanged="lstBook_SelectedIndexChanged" to the lstBook ListBox control 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 Book from the ListBox control. If there are Authors already associated with the selected Book in the database, it will show those Authors checked in the CheckBoxList control:

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 :)

History

• 28^th May, 2014: Initial version