Working With Multiple Tables In MVC By Sagar Jaybhay

In this article we will understand Working With Multiple Tables In MVC(Asp.Net) By Sagar Jaybhay.

Working with Multiple Tables in MVC

Now we are creating an Employee table with the Department ID field added in the Employee table and another table is created which is Department below having syntax for both tables.

create table Employee (
    EmpID INT,
    EmpName VARCHAR(50),
    EmpSalary DECIMAL(8,2),
    EmpGender VARCHAR(50),
    EmpCity VARCHAR(50),
    EmpEmail VARCHAR(50),
    DepartmentID INT
);
create table Department (
    DepartmentID INT,
    DepartmentName VARCHAR(50)
);

For both table insert data script is attached to here

insert into Department (DepartmentID, DepartmentName) values (1, 'Product Management');
insert into Department (DepartmentID, DepartmentName) values (2, 'Engineering');
insert into Department (DepartmentID, DepartmentName) values (3, 'Business Development');
insert into Department (DepartmentID, DepartmentName) values (4, 'Support');
insert into Department (DepartmentID, DepartmentName) values (5, 'Testing');
insert into Department (DepartmentID, DepartmentName) values (6, 'Account');
insert into Department (DepartmentID, DepartmentName) values (7, 'Marketing');
insert into Department (DepartmentID, DepartmentName) values (8, 'Legal');
insert into Department (DepartmentID, DepartmentName) values (9, 'Human Resources');
insert into Department (DepartmentID, DepartmentName) values (10, 'Srcum Master');
insert into Department (DepartmentID, DepartmentName) values (11, 'Sales');
insert into Department (DepartmentID, DepartmentName) values (12, 'Research and Development');

We have one requirement that we need to display all the departments present in our database. And after someone clicks on department name then we need to show EmployeeId belonging to that department. If someone clicks on EmployeeId then we need to display Employee Information this is our requirement.

To get Department we create a method in business class which is below

   public List<Department> GetDepartments()
        {
            List<Department> departments = new List<Department>();
            string query = "select * from Department order by DepartmentID";
            var data = this.dataAccess.GetTable(query);
            if(data!=null&&data.Rows.Count>0)
            {

                foreach(DataRow dataRow in data.Rows)
                {
                    var dept = new Department()
                    {
                        DepartmentID = Convert.ToInt32(dataRow["DepartmentID"].ToString()),
                        DepartmentName = dataRow["DepartmentName"].ToString()
                    };
                    departments.Add(dept);
                }

            }
            return departments;

        }

Now we will create an Action method for Display Department

public ActionResult DisplayDepartments()
        {
            var data = new BusinessLogic.Business().GetDepartments();
            return View(data);
        }

Below is View For Display Department

@model IEnumerable<WebApplication1.Models.Department>

@{
    ViewBag.Title = "DisplayDepartments";
}

<h2>DisplayDepartments</h2>

<div>
    <h4>Department</h4>
    <hr />

    <table style="border:thin" class="table table-bordered table-responsive">
        <thead>
            <tr>
                <td>DepartmentID</td>
                <td>DepartmentName</td>
            </tr>
        </thead>
        <tbody>
            @foreach(var dept in Model)
            {
            <tr>
                <td>@dept.DepartmentID</td>
                <td>@Html.ActionLink(dept.DepartmentName,"EmployeeList",new { DEptID=dept.DepartmentID})</td>
            </tr>
            }
        </tbody>

    </table>
</div>

The output of this looks like below when we click on department name we will be redirected to the employee list which is belonging to that department.

Video

To display employees by Department wise we create another method in Business Class which retrieve the EmployeeIds by DepartmentId the code for that is below

public List<int> GetEmpIDs(string DepartmentID)
        {
            List<int> ids = new List<int>();
            string Query = "select  EmpID from Employee where DepartmentID="+DepartmentID;
            var data = this.dataAccess.GetTable(Query);
            if (data != null && data.Rows.Count > 0)
            {
                foreach (DataRow id in data.Rows)
                    ids.Add(Convert.ToInt32(id["EmpID"]));
            }
            return ids;
        }

Now we will create another action method in our Employee Controller which we get Employee by Department ID and code for that is below

public ActionResult EmployeeList(string DEptID)
        {
            var data = new BusinessLogic.Business().GetEmpIDs(DEptID);
            ViewBag.EmpIDs = data;
            return View();
        }

Now we create a view for this Ids which is below

@{
    ViewBag.Title = "EmployeeList";
}    

<h2>EmployeeList</h2>




<h3>Here We can display ids DepartmentWise</h3>


<ul>

    @foreach (var id in (List<int>)ViewBag.EmpIDs)
    {
        <li>
            @Html.ActionLink(id.ToString(), "Index", new { EmpID = id.ToString() })
        </li>
    }
</ul>

<p>

    @Html.ActionLink("Back to Department List", "DisplayDepartments")
</p>

Anyone clicks on Id it is redirected to our previously written Index method which takes employeeid as a parameter and displays employee information.

GitHub Project Link:- https://github.com/Sagar-Jaybhay/MVC5