.NET MVC with SQL Server Database

In this article, we will learn .NET MVC with SQL Server database and entity framework.

Table of Contents

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