Multiple Models to one View in ASP.Net MVC

Namespaces

You will need to import the following namespaces.

using System.Dynamic;

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

using System.Collections.Generic;

 

 

Model

Following are the two Model classes.

CustomerModel

The following Model class will be used to populate data from Customers Table.

public class CustomerModel

{

    public string CustomerId { get; set; }

    public string CustomerName { get; set; }

    public string City { get; set; }

    public string Country { get; set; }

}

 

EmployeeModel

The following Model class will be used to populate data from Employees Table.

public class EmployeeModel

{

    public string EmployeeId { get; set; }

    public string EmployeeName { get; set; }

    public string City { get; set; }

    public string Country { get; set; }

}

Controller

The Controller consists of an Index Action method. Inside this Action method, first an object of ExpandoObject class is created and it's instance is assigned to a variable of type Dynamic.

public class HomeController : Controller

{

    // GET: Home

    public ActionResult Index()

    {

        dynamic model = new ExpandoObject();

        model.Customers = GetCustomers();

        model.Employees = GetEmployees();

        return View(model);

    }

 

    private static List<CustomerModel> GetCustomers()

    {

        List<CustomerModel> customers = new List<CustomerModel>();

        string query = "SELECT TOP 10 CustomerID, ContactName, City, Country FROM Customers";

        string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;

        using (SqlConnection con = new SqlConnection(constr))

        {

            using (SqlCommand cmd = new SqlCommand(query))

            {

                cmd.Connection = con;

                con.Open();

                using (SqlDataReader sdr = cmd.ExecuteReader())

                {

                    while (sdr.Read())

                    {

                        customers.Add(new CustomerModel

                        {

                            CustomerId = sdr["CustomerID"].ToString(),

                            CustomerName = sdr["ContactName"].ToString(),

                            City = sdr["City"].ToString(),

                            Country = sdr["Country"].ToString()

                        });

                    }

                }

                con.Close();

                return customers;

            }

        }

    }

 

    private static List<EmployeeModel> GetEmployees()

    {

        List<EmployeeModel> employees = new List<EmployeeModel>();

        string query = "SELECT EmployeeID, (FirstName + ' ' + LastName) [Name], City, Country FROM Employees";

        string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;

        using (SqlConnection con = new SqlConnection(constr))

        {

            using (SqlCommand cmd = new SqlCommand(query))

            {

                cmd.Connection = con;

                con.Open();

                using (SqlDataReader sdr = cmd.ExecuteReader())

                {

                    while (sdr.Read())

                    {

                        employees.Add(new EmployeeModel

                        {

                            EmployeeId = sdr["EmployeeID"].ToString(),

                            EmployeeName = sdr["Name"].ToString(),

                            City = sdr["City"].ToString(),

                            Country = sdr["Country"].ToString()

                        });

                    }

                    con.Close();

                    return employees;

                }

            }

        }

    }

}

 

 

View

Inside the View, first you will need to import the namespace for accessing the Model classes.

Then you will need to declare the Model for the View as dynamic.

For displaying the records, two HTML Tables are used and by iterating over the Generic List Collection of Model objects, rows are added to the HTML Tables.

@using Multiple_Model_MVC.Models

@model dynamic

@{

    Layout = null;

}

 

<!DOCTYPE html>

 

<html>

<head>

    <meta name="viewport" content="width=device-width"/>

    <title>Index</title>

</head>

<body>

    <table cellpadding="0" cellspacing="0">

        <tr>

            <th>CustomerID</th>

            <th>Contact Name</th>

            <th>City</th>

            <th>Country</th>

        </tr>

        @foreach (CustomerModel customer in Model.Customers)

        {

            <tr>

                <td>@customer.CustomerId</td>

                <td>@customer.CustomerName</td>

                <td>@customer.City</td>

                <td>@customer.Country</td>

            </tr>

        }

    </table>

    <hr/>

    <table cellpadding="0" cellspacing="0">

        <tr>

            <th>EmployeeID</th>

            <th>Employee Name</th>

            <th>City</th>

            <th>Country</th>

        </tr>

        @foreach (EmployeeModel employee in Model.Employees)

        {

            <tr>

                <td>@employee.EmployeeId</td>

                <td>@employee.EmployeeName</td>

                <td>@employee.City</td>

                <td>@employee.Country</td>

            </tr>

        }

    </table>

</body>

</html>

 

 

Screenshot

 

iq

how does a web service work in c#
why we use serialization and deserialization in c#
enable view state in asp net why to using c#
table variable and temp table difference in sql server

One-to-One, One-to-Many Table Relationships in SQL Server

Types of Relationships

a. One-One Relationship (1-1 Relationship)
b. One-Many Relationship (1-M Relationship)
c. Many-Many Relationship (M-M Relationship)

This tech-recipe covers only 1-1 and 1-M relationship.

1. One-One Relationship (1-1 Relationship)

One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-Unique foreign key constraints.

With One-to-One Relationship in SQL Server, for example, a person can have only one passport. Let’s implement this in SQL Server.

CREATE TABLE dbo.Person
(
Pk_Person_Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);

CREATE TABLE dbo.PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);

INSERT INTO dbo.Person VALUES ('Niraj','v.a@emails.com');
INSERT INTO dbo.Person VALUES ('Vishwanath','v.v@emails.com');
INSERT INTO dbo.Person VALUES ('Chetan','c.v@emails.com');
GO

INSERT INTO dbo.PassportDetails VALUES (101, 'C3031R33', 1);
INSERT INTO dbo.PassportDetails VALUES (102, 'VRDK5695', 2);
INSERT INTO dbo.PassportDetails VALUES (103, 'A4DEK33D', 3);
GO

SELECT * FROM dbo.Person
SELECT * FROM dbo.PassportDetails;

One-to-One Relationship is implemented using dbo.Person(Pk_Person_Id) as the Primary key and dbo.PassportDetails(fk_person_id) as (Unique Key Constraint-Foreign Key).

Therefore, it will always have only one matching row between the Person-PassportDetails table based on the dbo.Person(Pk_Person_Id)-dbo.PassportDetails(Fk_Person_Id) relationship.

1. Create two Tables (Table A & Table B) with the Primary Key on Both the tables.
2. Create Foreign key in Table B which references the Primary key of Table A.
3. Add a Unique Constraint on the Foreign Key column of Table B.

What happens if we try to insert passport details for the same fk_person_id which already exists in the passportDetails table?

We get an error of Unique key violation.

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__Passport__04554C334F12BBB9'.
Cannot insert duplicate key in object 'dbo.PassportDetails'. The duplicate key value is (3).
The statement has been terminated.

2. One-Many Relationship (1-M Relationship)

The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship.

In the One-to-Many Relationship in SQL Server, for example, a book can have multiple authors. Let’s implement this in SQL Server.

CREATE TABLE dbo.Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(255),
ISBN VARCHAR(255)
);

CREATE TABLE dbo.Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName     VARCHAR(255),
MobileNo     CHAR(10),
Fk_Book_Id   INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);

INSERT INTO Book VALUES (1, 'Let is Snow', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'Three Cups of Tea','ISBN638242');
GO

INSERT INTO dbo.Author VALUES(100,'John Green','30303',1);
INSERT INTO dbo.Author VALUES(101,'Maureen Johnson','4343',1);
INSERT INTO dbo.Author VALUES(102,'Lauren Myracle','76665',1);
INSERT INTO dbo.Author VALUES(103,'Greg Mortenson','6434',2);
INSERT INTO dbo.Author VALUES(104,'David Oliver Relin','72322',2);
GO

SELECT * FROM dbo.Book;
SELECT * FROM dbo.Author;

One-to-Many Relationship is implemented using dbo.Book(Pk_Book_Id) as the Primary Key and dbo.Author (Fk_Book_Id) as (Foreign Key). Thus, it will always have only One-to-Many (One Book-Multiple Authors) matching rows between the Book-Author table based on the dbo.Book (Pk_Book_Id)-dbo.Author(Fk_Book_Id) relationship.

1. Create two Tables (Table A & Table B) with the Primary Key on both the tables.
2. Create a Foreign key in Table B which references the Primary key of Table A.