Asp.Net Mvc Connect Sql Server Database Complete Guide

 Last Update:2025-06-23T00:00:00     .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    12 mins read      Difficulty-Level: beginner

Understanding the Core Concepts of ASP.NET MVC Connect SQL Server Database

Connecting ASP.NET MVC with SQL Server Database

Introduction

ASP.NET MVC (Model-View-Controller) is a web application framework developed by Microsoft which allows developers to build scalable and robust web applications by dividing them into the Model, View, and Controller. On the other hand, SQL Server is a relational database management system developed by Microsoft. Integrating these two technologies allows ASP.NET MVC applications to interact with SQL Server databases for data storage, retrieval, and manipulation.

Step-by-Step Guide to Connecting ASP.NET MVC with SQL Server

  1. Create an ASP.NET MVC Application

    • Open Visual Studio.
    • Select "File" > "New" > "Project".
    • In the "New Project" dialog, choose "ASP.NET Web Application (.NET Framework)".
    • Click "Next", enter the project name, and click "Create".
    • In the next dialog, choose "MVC" and uncheck "Configure for HTTPS".
    • Click "Create" to create the project.

  2. Set Up SQL Server Database

    • Open SQL Server Management Studio (SSMS).
    • Connect to your SQL Server instance.
    • Create a new database (right-click "Databases" > "New Database").
    • Define tables, views, stored procedures, and other database objects as per your application requirements.

  3. Install Entity Framework (EF)

    • Entity Framework is an ORM (Object-Relational Mapping) framework which allows the .NET applications to interact with the database using .NET objects.
    • Install EF by opening the NuGet Package Manager console and executing the following command: 
      Install-Package EntityFramework

  4. Configure EF with SQL Server Database

    • Open Web.config file in the project and add the following connection string within <configuration> tag, replace YourDBName, YourServerName, YourUser, and YourPassword with your actual database name, server name, username, and password. 
      <connectionStrings>
    <add name="YourDBContext" connectionString="Data Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=True;User ID=YourUser;Password=YourPassword;" providerName="System.Data.SqlClient" />
</connectionStrings>

  5. Create a Model Class

    • Right-click the "Models" folder and add a class named YourModelName.

  6. Create a DBContext Class

    • Add a new class called YourDBContext in the "Models" folder and inherit it from DbContext class.
    • Define DbSets of your entities within this class. 
      using System.Data.Entity;

public class YourDBContext : DbContext
{
    public DbSet<YourModelName> YourModelName { get; set; }

    public YourDBContext() : base("name=YourDBContext")
    {
    }
}

  7. Configure Controller to Use DBContext

    • Right-click the "Controllers" folder and add a new controller.
    • Select "MVC 5 Controller with views, using Entity Framework" and choose your model and data context class.

  8. CRUD Operations

    • The generated controller includes CRUD (Create, Read, Update, Delete) operations for your model.
    • You can modify these operations as per your requirements or add additional functionalities.

Important Points

  • Connection String: Verify that the connection string in Web.config is correctly pointing to your SQL Server instance and database.
  • EF Versions: Depending on your needs, you might need to install different versions of Entity Framework.
  • Database Migrations: Use database migrations to manage changes in the database schema throughout the development lifecycle.
  • Security: Avoid hardcoding sensitive information such as usernames and passwords in the connection string. Consider using secure methods such as environment variables or Azure Key Vault for storing sensitive data.
  • Performance: Ensure that your SQL queries are optimized to avoid performance bottlenecks. Use indexing, query optimization, and caching strategies as needed.
  • Error Handling: Implement proper error handling in your data access layer to gracefully handle exceptions and provide meaningful user feedback.

Conclusion

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement ASP.NET MVC Connect SQL Server Database

Step 1: Create a New ASP.NET MVC Project

  1. Open Visual Studio: Launch Visual Studio and select "Create a new project".
  2. Choose a Template: Select "ASP.NET Web Application (.NET Framework)" and click "Next".
  3. Name the Project: Provide a name (e.g., MvcAppWithSql) and choose a location to save the project.
  4. Configure the Project: Click "Next", and select "MVC". Ensure ".NET Framework 4.7.2" or later is selected, then click "Create".

Step 2: Set Up the SQL Server Database

  1. Open SQL Server Management Studio (SSMS): Use SSMS to create a new database.

  2. Create a New Database: Execute the following SQL command:

    CREATE DATABASE MvcAppWithSqlDB;

  3. Create a Table: Execute the following SQL command to create a table in your database:

    USE MvcAppWithSqlDB;

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Department NVARCHAR(50)
);

Step 3: Configure the Connection String

  1. Open Web.config: In the Solution Explorer, locate and open the Web.config file.
  2. Add the Connection String: Inside the <connectionStrings> section, add the following line: 
    <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MvcAppWithSqlDB;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

Step 4: Create a Model

  1. Add a Model Class: In the Solution Explorer, right-click on the "Models" folder and select "Add" -> "Class". Name it Employee.cs and define the properties: 
    namespace MvcAppWithSql.Models
{
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Department { get; set; }
    }
}

Step 5: Create a Data Access Layer

  1. Add a Repository Class: Right-click on the "Models" folder, select "Add" -> "Class", and name it EmployeeRepository.cs: 
    using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MvcAppWithSql.Models
{
    public class EmployeeRepository
    {
        private string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        public IEnumerable<Employee> GetAllEmployees()
        {
            List<Employee> employees = new List<Employee>();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con);
                SqlDataReader sdr = cmd.ExecuteReader();

                while (sdr.Read())
                {
                    employees.Add(new Employee
                    {
                        Id = Convert.ToInt32(sdr["Id"]),
                        FirstName = sdr["FirstName"].ToString(),
                        LastName = sdr["LastName"].ToString(),
                        Email = sdr["Email"].ToString(),
                        Department = sdr["Department"].ToString()
                    });
                }
            }

            return employees;
        }

        public Employee GetEmployeeById(int id)
        {
            Employee employee = new Employee();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM Employees WHERE Id=@Id", con);
                cmd.Parameters.AddWithValue("@Id", id);
                SqlDataReader sdr = cmd.ExecuteReader();

                if (sdr.Read())
                {
                    employee.Id = Convert.ToInt32(sdr["Id"]);
                    employee.FirstName = sdr["FirstName"].ToString();
                    employee.LastName = sdr["LastName"].ToString();
                    employee.Email = sdr["Email"].ToString();
                    employee.Department = sdr["Department"].ToString();
                }
            }

            return employee;
        }

        public int AddEmployee(Employee employee)
        {
            int id = 0;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO Employees (FirstName, LastName, Email, Department) VALUES (@FirstName, @LastName, @Email, @Department)", con);
                cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
                cmd.Parameters.AddWithValue("@LastName", employee.LastName);
                cmd.Parameters.AddWithValue("@Email", employee.Email);
                cmd.Parameters.AddWithValue("@Department", employee.Department);
                id = cmd.ExecuteNonQuery();
            }

            return id;
        }

        public int UpdateEmployee(Employee employee)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("UPDATE Employees SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Department=@Department WHERE Id=@Id", con);
                cmd.Parameters.AddWithValue("@Id", employee.Id);
                cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
                cmd.Parameters.AddWithValue("@LastName", employee.LastName);
                cmd.Parameters.AddWithValue("@Email", employee.Email);
                cmd.Parameters.AddWithValue("@Department", employee.Department);
                return cmd.ExecuteNonQuery();
            }
        }

        public int DeleteEmployee(int id)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("DELETE FROM Employees WHERE Id=@Id", con);
                cmd.Parameters.AddWithValue("@Id", id);
                return cmd.ExecuteNonQuery();
            }
        }
    }
}

Step 6: Create a Controller

  1. Add a Controller: Right-click on the "Controllers" folder, select "Add" -> "Controller", choose "MVC 5 Controller - Empty", and name it EmployeeController.
  2. Implement the Controller: Add the actions for CRUD operations: 
    using System.Web.Mvc;
using MvcAppWithSql.Models;

namespace MvcAppWithSql.Controllers
{
    public class EmployeeController : Controller
    {
        private EmployeeRepository repository = new EmployeeRepository();

        // GET: Employee
        public ActionResult Index()
        {
            var employees = repository.GetAllEmployees();
            return View(employees);
        }

        // GET: Employee/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Employee/Create
        [HttpPost]
        public ActionResult Create(Employee employee)
        {
            if (ModelState.IsValid)
            {
                repository.AddEmployee(employee);
                return RedirectToAction("Index");
            }

            return View(employee);
        }

        // GET: Employee/Edit/5
        public ActionResult Edit(int id)
        {
            var employee = repository.GetEmployeeById(id);
            return View(employee);
        }

        // POST: Employee/Edit/5
        [HttpPost]
        public ActionResult Edit(Employee employee)
        {
            if (ModelState.IsValid)
            {
                repository.UpdateEmployee(employee);
                return RedirectToAction("Index");
            }

            return View(employee);
        }

        // GET: Employee/Delete/5
        public ActionResult Delete(int id)
        {
            var employee = repository.GetEmployeeById(id);
            return View(employee);
        }

        // POST: Employee/Delete/5
        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            repository.DeleteEmployee(id);
            return RedirectToAction("Index");
        }
    }
}

Step 7: Create Views

  1. Create a Folder: Right-click on "Views" in the Solution Explorer, select "Add" -> "New Folder", and name it Employee.

  2. Create Index View: Right-click on the Employee folder, select "Add" -> "View", name it Index, and check "Create a strongly-typed view". Select Employee as the model class and List as the template, then click "Add":

    @model IEnumerable<MvcAppWithSql.Models.Employee>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.FirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.LastName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Email)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Department)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.FirstName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.LastName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Email)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Department)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
}

</table>

  3. Create Create View: Similarly, add a Create view with the following content:

    @model MvcAppWithSql.Models.Employee

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Employee</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Department, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Department, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Department, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

  4. Create Edit View: Add an Edit view with similar fields:

    @model MvcAppWithSql.Models.Employee

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Employee</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.Id)

        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Department, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Department, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Department, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

  5. Create Delete View: Add a Delete view with the following content:

    @model MvcAppWithSql.Models.Employee

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Employee</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.FirstName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.FirstName)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.LastName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.LastName)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Email)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Email)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Department)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Department)
        </dd>

    </dl>

    @using (Html.BeginForm()) {
        @Html.AntiForgeryToken()

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    }
</div>

Step 8: Run the Application

  1. Set the Start Page: Right-click on the EmployeeController class in the Solution Explorer, and select "Set as Start Page".
  2. Run the Application: Press F5 to start the application.
  3. Test the Application: Ensure the Create, Read, Update, and Delete operations (CRUD) work as expected.

Summary

In this step-by-step example, we created a simple ASP.NET MVC application that connects to a SQL Server database to perform CRUD operations on an Employees table. We discussed setting up the project, configuring the database and connection string, creating the model, data access layer, controller, and views.

Top 10 Interview Questions & Answers on ASP.NET MVC Connect SQL Server Database

Top 10 Questions and Answers: ASP.NET MVC Connect SQL Server Database

1. How do I configure SQL Server database connection in ASP.NET MVC?

<connectionStrings>
  <add name="DefaultConnection" 
       connectionString="Data Source=YourServerName;Initial Catalog=YourDatabaseName;Integrated Security=True" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

For .NET Core, update appsettings.json as follows:

"ConnectionStrings": {
  "DefaultConnection": "Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=True;"
}

2. What are the steps to create a simple controller that interacts with the database in ASP.NET MVC?

First, create a model class, then a data access layer, and finally a controller. Here's a quick example using Entity Framework:

  1. Model:
public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}
  1. Data Context:
public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() : base("DefaultConnection") {}

    public DbSet<Product> Products { get; set; }
}
  1. Controller:
public class ProductController : Controller
{
    private ApplicationDbContext db = new ApplicationDbContext();

    public ActionResult Index()
    {
        return View(db.Products.ToList());
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }
}

3. How do you handle CRUD operations in ASP.NET MVC with SQL Server?

Use Entity Framework to perform CRUD operations on your database. Here's a sample action for each operation:

  1. Create:
[HttpPost]
public ActionResult Create(Product product)
{
    if (ModelState.IsValid)
    {
        db.Products.Add(product);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(product);
}

  1. Read: Already shown in the previous answer with the Index() action.

  2. Update:

[HttpPost]
public ActionResult Edit(Product product)
{
    if (ModelState.IsValid)
    {
        db.Entry(product).State = EntityState.Modified;
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(product);
}
  1. Delete:
public ActionResult Delete(int id)
{
    Product product = db.Products.Find(id);
    return View(product);
}

[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
    Product product = db.Products.Find(id);
    db.Products.Remove(product);
    db.SaveChanges();
    return RedirectToAction("Index");
}

4. How can I ensure database migrations work in ASP.NET MVC?

For Entity Framework migrations, you can use the Package Manager Console:

  • Enable migrations: Enable-Migrations
  • Add a migration: Add-Migration InitialCreate
  • Apply the migration to your database: Update-Database

Ensure your project has the Entity Framework Core tools installed if you are using .NET Core.

5. What are the common pitfalls when connecting SQL Server and ASP.NET MVC?

  • Connection String: Verify that the connection string is correctly formed and points to the right server and database.
  • Database Initialization: Ensure the database is initialized properly (use migrations or scripts).
  • Async Operations: Use asynchronous methods like ToListAsync() for database queries to improve performance and responsiveness.
  • Permissions: Ensure the application has the necessary permissions to connect to and interact with the database.
  • Error Handling: Implement proper error handling around database operations to manage exceptions effectively.

6. How to use LINQ to query the database in ASP.NET MVC?

LINQ (Language Integrated Query) allows you to query collections, including databases, in a readable and concise manner. Here’s an example:

Assuming you have a database context ApplicationDbContext and a Product model:

public ActionResult FilteredProducts(decimal priceLimit)
{
    var products = db.Products.Where(p => p.Price <= priceLimit).ToList();
    return View(products);
}

7. How to optimize database queries in ASP.NET MVC?

  • Indexes: Use indexes on columns that are frequently searched.
  • Force Execution Plan: Analyze execution plans to identify slow queries and optimize them.
  • Caching: Implement caching mechanisms to reduce unnecessary database hits.
  • Batch Operations: Use batch inserts, updates, and deletes to minimize overhead.
  • Asynchronous Queries: Use async methods to prevent blocking and improve scalability.

8. How to implement repository pattern in ASP.NET MVC for database operations?

The repository pattern encapsulates data access logic, promoting separation of concerns and unit testing:

  1. Repository Interface:

You May Like This Related .NET Topic

📥 Download Code ▶️ Watch Online 📖 Read Description

Login to post a comment.