CRUD Operations In MVC Using Stored Procedure Without Entity Framework

In this example, we are going to use an Employee table with three columns: Id, FirstName, LastName, and Address. We will then write code for CRUD operations in ASP.NET MVC using stored procedures without Entity Framework.

We will discuss code for each action, i.e., insert, update, and delete in detail. Stored Procedures: Create stored procedures in your database for each CRUD operation (GetAllEmployees, GetEmployeeById, InsertEmployee, UpdateEmployee, DeleteEmployee).

Below example provides a basic example, and you may need to change the code based on your requirements.

Below is an example of SQL Server stored procedures for the CRUD operations on the Employee table. You should execute these scripts in SQL Server database to create the stored procedures. GetAllEmployees:

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employee;
END

GetEmployeeById:

CREATE PROCEDURE GetEmployeeById
    @Id INT
AS
BEGIN
    SELECT * FROM Employee WHERE Id = @Id;
END

InsertEmployee:

CREATE PROCEDURE InsertEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Address NVARCHAR(100)
AS
BEGIN
    INSERT INTO Employee (FirstName, LastName, Address)
    VALUES (@FirstName, @LastName, @Address);
END

UpdateEmployee:

CREATE PROCEDURE UpdateEmployee
    @Id INT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Address NVARCHAR(100)
AS
BEGIN
    UPDATE Employee
    SET FirstName = @FirstName, LastName = @LastName, Address = @Address
    WHERE Id = @Id;
END

DeleteEmployee:

CREATE PROCEDURE DeleteEmployee
    @Id INT
AS
BEGIN
    DELETE FROM Employee WHERE Id = @Id;
END

Employee table having columns Id, FirstName, LastName, and Address.

After creating these stored procedures, you can use the C# methods in the EmployeeController to perform CRUD operations on the Employee table in ASP.NET MVC application.

Below is an example of how you can perform CRUD operations in ASP.NET Core MVC for an Employee table using stored procedures without Entity Framework. This example assumes we have a database with stored procedures for each action.

Model (Employee.cs):

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
}

Controller (EmployeeController.cs):

public class EmployeeController : Controller
{
    private string connectionString = "Data Source=WIN-AdminPC;Initial Catalog=ToolDb;User ID=sa;Password=pass@1234";

    public ActionResult Index()
    {
        List<Employee> employees = GetEmployees();
        return View(employees);
    }

    public ActionResult Create()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Create(Employee employee)
    {
        InsertEmployee(employee.FirstName, employee.LastName, employee.Address);
        return RedirectToAction("Index");
    }

    public ActionResult Edit(int id)
    {
        Employee employee = GetEmployeeById(id);
        return View(employee);
    }

    [HttpPost]
    public ActionResult Edit(Employee employee)
    {
        UpdateEmployee(employee.Id, employee.FirstName, employee.LastName, employee.Address);
        return RedirectToAction("Index");
    }

    public ActionResult Delete(int id)
    {
        Employee employee = GetEmployeeById(id);
        return View(employee);
    }

    [HttpPost, ActionName("Delete")]
    public ActionResult DeleteConfirmed(int id)
    {
        DeleteEmployee(id);
        return RedirectToAction("Index");
    }

    private List<Employee> GetEmployees()
    {
        List<Employee> employees = new List<Employee>();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("GetAllEmployees", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Employee employee = new Employee
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            Address = reader["Address"].ToString()
                        };
                        employees.Add(employee);
                    }
                }
            }
        }
        return employees;
    }

    private Employee GetEmployeeById(int id)
    {
        Employee employee = new Employee();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("GetEmployeeById", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        employee.Id = Convert.ToInt32(reader["Id"]);
                        employee.FirstName = reader["FirstName"].ToString();
                        employee.LastName = reader["LastName"].ToString();
                        employee.Address = reader["Address"].ToString();
                    }
                }
            }
        }
        return employee;
    }

    private void InsertEmployee(string firstName, string lastName, string address)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("InsertEmployee", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@FirstName", firstName);
                command.Parameters.AddWithValue("@LastName", lastName);
                command.Parameters.AddWithValue("@Address", address);
                command.ExecuteNonQuery();
            }
        }
    }

    private void UpdateEmployee(int id, string firstName, string lastName, string address)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("UpdateEmployee", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);
                command.Parameters.AddWithValue("@FirstName", firstName);
                command.Parameters.AddWithValue("@LastName", lastName);
                command.Parameters.AddWithValue("@Address", address);
                command.ExecuteNonQuery();
            }
        }
    }

    private void DeleteEmployee(int id)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("DeleteEmployee", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);
                command.ExecuteNonQuery();
            }
        }
    }
}

Views: Index (Index.cshtml):

@model List<Employee>

<h2>Employee List</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model[0].FirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model[0].LastName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model[0].Address)
        </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.Address)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { id = item.Id }) |
                @Html.ActionLink("Delete", "Delete", new { id = item.Id })
            </td>
        </tr>
    }
</table>

Create (Create.cshtml):

@model Employee

<h2>Create</h2>

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

    <div class="form-horizontal">
        <hr />
        <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.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", 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-primary" />
            </div>
        </div>
    </div>
}

Edit (Edit.cshtml):

@model Employee

<h2>Edit</h2>

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

    <div class="form-horizontal">
        <hr />
        @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.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", 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-primary" />
            </div>
        </div>
    </div>
}

Delete (Delete.cshtml):

@model Employee

<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.Address)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Address)
        </dd>
    </dl>

    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
        <input type="submit" value="Delete" class="btn btn-danger" />
    }

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

So, this is the complete code for performing CRUD operations in ASP.NET Core MVC using stored procedures without using Entity Framework. If you have any queries, please write to us, we will respond to you.

CRUD Operations In MVC Using Stored Procedure Without Entity Framework

Read Similar Articles