In this article, we will learn .NET MVC with SQL Server database and entity framework.
Previous Chapter: Routing in MVC
The .NET MVC (Model-View-Controller) framework is a popular framework for building web applications. It promotes a clean separation of concerns by dividing the application into three main components:
- Model: Represents the application’s data and business logic.
- View: Displays the data (the UI).
- Controller: Handles user input and interactions, updating the Model and View as needed.
When working with an SQL Server database in an ASP.NET MVC application, Entity Framework (EF) is a powerful ORM (Object-Relational Mapping) tool that simplifies data access. It allows developers to interact with the database using .NET objects, reducing the need to write raw SQL queries.
Basics of Entity Framework in ASP.NET MVC
Entity Framework (EF) can be used in different ways:
- Database-First: Start with an existing database and generate the EF model.
- Model-First: Design the EF model first and then generate the database schema.
- Code-First: Write the model classes and let EF create the database schema based on these classes.
For simplicity, we’ll focus on Code-First approach, which is often used for new projects.
Setting Up Entity Framework
- Install Entity Framework: We can install EF via NuGet Package Manager in Visual Studio.
Install-Package EntityFramework
2. Create Model Classes: Define the model classes that represent the database tables.
public class Product
{
public int ProductId { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
public class ApplicationDbContext : DbContext
{
public DbSet<Product> Products { get; set; }
}
3. Configure Connection String: Add the connection string to the web.config
file.
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
4. Initialize Database: Use migrations to create the database schema.
Enable-Migrations
Add-Migration InitialCreate
Update-Database
Using LINQ for CRUD Operations
LINQ (Language Integrated Query) is a powerful querying tool integrated into C#. It can be used with Entity Framework to perform CRUD (Create, Read, Update, Delete) operations easily.
Create
To add a new record to the database:
using (var context = new ApplicationDbContext())
{
var product = new Product
{
Name = "New Product",
Price = 9.99M
};
context.Products.Add(product);
context.SaveChanges();
}
Read/ Get
To retrieve records from the database:
using (var context = new ApplicationDbContext())
{
var products = context.Products.ToList();
// Fetch specific product
var singleProduct = context.Products.FirstOrDefault(p => p.ProductId == 1);
}
Update
To update an existing record:
using (var context = new ApplicationDbContext())
{
var product = context.Products.FirstOrDefault(p => p.ProductId == 1);
if (product != null)
{
product.Price = 19.99M;
context.SaveChanges();
}
}
Delete
To delete a record from the database:
using (var context = new ApplicationDbContext())
{
var product = context.Products.FirstOrDefault(p => p.ProductId == 1);
if (product != null)
{
context.Products.Remove(product);
context.SaveChanges();
}
}
Conclusion
Using .NET MVC with a SQL Server database and Entity Framework simplifies web application development. By leveraging LINQ, developers can perform CRUD operations efficiently and write cleaner, more maintainable code. This approach also ensures a seamless interaction with the database while keeping the application logic organized and manageable.
Keep Following: SharePointCafe.NET