How to Get Data from SQL Database in Asp.net Core MVC Using C#.Net

In today's article I will show you simple tutorial with example how you can get or retrieve the data from MS SQL server data base in asp.net core mvc by using entity framework using c#.net. Here I have used DB first approach to make the connection from the MS SQL server database. So, in this article we will get or retrieve the data from the database and display in a tabular or table format. Here is the SQL table data which we are going to get and display in view.

Scaffold command syntax(Use to prepare Entity objects) Scaffold-DbContext "Server=<MS SQL Server Name>;Database=<Database name>;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir data

What are the nuget packages required to connect MS SQL database? Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer

Now for this article first we will create a new asp.net core 8 mvc application with c#.net. After running scaffold command, we will make the connection to database. After connection the database we will add model class file, in this file we will add some properties which we will use to display the data in view.

 public class EmployeeModel
{
    public List<EmployeeDetail> Employees { get; set; }
}
public class EmployeeDetail
{
    public int Id { get; set; }
    public string Employee_Name { get; set; }
    public string Department { get; set; }
    public int Salary { get; set; }
}

In above model class file, I have added some properties, this property we will use for mapping the retrieved data and pass it to view to display it. Here we are having two class First for employee and second for employee list. Here EmployeeList we will pass for displaying the data.Now we will add a controller class file and add a HttpGet method. In this method we will add come code to get and add the data and pass it to view to display the data in table format.

[HttpGet]
public IActionResult Index()
{
    EmployeeModel employeeModel = new EmployeeModel();
    employeeModel.Employees = new List<EmployeeDetail>();
    EmployeeContext employeeContext = new EmployeeContext();
    var data = employeeContext.Employees.ToList();
    foreach (var item in data)
    {
        employeeModel.Employees.Add(new EmployeeDetail
        {
            Id = item.Id,
            Employee_Name = item.EmployeeName,
            Department = item.Department,
            Salary = Convert.ToInt32(item.Salary)
        });
    }
    return View(employeeModel);
}

In above code first I have created the object of model class and after that i have created object for DBcontext. With the help of DBContect object I have access the DB Employee table. With the help of foreach I have add the data collection in the employee list. after making the collection I have passed it to view. Now let's create a view (.cshtml) and the below code in it.

 @model EmployeeModel
@{
	ViewData["Title"] = "Home Page";
}
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
	<table class="table">
		<thead>
			<tr>
				<th>ID</th>
				<th>NAME</th>
				<th>DEPARTMENT</th>
				<th>SALARY</th>
			</tr>
		</thead>
		<tbody>
			@foreach (var item in Model.Employees)
			{
				<tr>
					<td>@item.Id</td>
					<td>@item.Employee_Name</td>
					<td>@item.Department</td>
					<td>@item.Salary</td>
				</tr>
			}
		</tbody>
	</table>
}

In above code in the very first line, I have added reference of the model class. After that i have defined the form tag. After form tag HTML table have been used in this table i have used foreach loop to display the data in tabula format. Now let's run the code to check the output.