Introduction

This article will explain repository design pattern with ADO.Net in MVC from the database without using the entity framework. I am going to use ADO.Net for this article.
Step 1: Open the SQL Server with a version of your choice and create a database table and related stored procedure.
  1. CREATE TABLE [dbo].[Employee](
  2. [Id] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [nvarchar](50) NULL,
  4. [Position] [nvarchar](50) NULL,
  5. [Office] [nvarchar](50) NULL,
  6. [Age] [int] NULL,
  7. [Salary] [int] NULL,
  8. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  9. (
  10. [Id] ASC
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  12. ) ON [PRIMARY]
  13. GO
  14. /* TO GET ALL EMPLYEES AND GET EMPLOYEE BY ID*/
  15. create procedure spGetEmployee
  16. as
  17. begin
  18. select * from Employee
  19. end
  20. /* TO CREATE NEW EMPLOYEE*/
  21. create procedure spAddNew
  22. (
  23. @Name nvarchar(50),
  24. @Position nvarchar(50),
  25. @Office nvarchar(50),
  26. @Age int,
  27. @Salary int
  28. )
  29. as
  30. begin
  31. insert into Employee(Name,Position,Office,Age,Salary)
  32. values(@Name,@Position,@Office,@Age,@Salary)
  33. end
  34. /* TO UPDATE EMPLOYEE*/
  35. create procedure spUpdateEmployee
  36. (
  37. @Id int,
  38. @Name nvarchar(50),
  39. @Position nvarchar(50),
  40. @Office nvarchar(50),
  41. @Age int,
  42. @Salary int
  43. )
  44. as
  45. begin
  46. update Employee
  47. set Name=@Name,Position=@Position,Office=@Office,Age=@Age,Salary=@Salary
  48. where Id=@Id
  49. end
  50. /* TO DELETE EMPLOYEE*/
  51. create procedure spDeleteEmployee
  52. (
  53. @Id int
  54. )
  55. as
  56. begin
  57. delete from Employee where Id=@Id
  58. end
Step 2: Open Visual Studio with a version of your choice and create a new project with an appropriate name.
Step 3: Choose an "Empty" template, check MVC under "Add folders & core references", and click "Create".
Step 4: Right-click on the Models folder, select “Add”. Under “Add”, a "Choose class" window will appear. From that window, select Visual C# and give a class name as “Employee”. Then, click "Add".
Step 5: Right-click on the project “Add” folder name it Repository “Add” an interface IEmployee and “Add” class Name it MockEmployee.

IEmployee interface

  1. using RepositoryPatternWithADO.Net.Models;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Text;
  6. using System.Threading.Tasks;
  7. namespace RepositoryPatternWithADO.Net.Repository
  8. {
  9. public interface IEmployee
  10. {
  11. IList<Employee> GetEmployees();
  12. Employee GetEmployeeById(int? id);
  13. void InsertNew(Employee employee);
  14. void Update(Employee employee);
  15. void Delete(Employee employee);
  16. }
  17. }

MockEmployee Class

  1. using RepositoryPatternWithADO.Net.Models;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Configuration;
  5. using System.Data;
  6. using System.Data.SqlClient;
  7. namespace RepositoryPatternWithADO.Net.Repository
  8. {
  9. public class MockEmployee : IEmployee
  10. {
  11. private readonly string CS = ConfigurationManager.ConnectionStrings["EmployeeContext"].ConnectionString;
  13. public IList<Employee> GetEmployees()
  14. {
  15. List<Employee> employees = new List<Employee>();
  16. using (SqlConnection con = new SqlConnection(CS))
  17. {
  18. SqlCommand cmd = new SqlCommand("spGetEmployee", con);
  19. cmd.CommandType = CommandType.StoredProcedure;
  20. con.Open();
  21. SqlDataReader rdr = cmd.ExecuteReader();
  22. while (rdr.Read())
  23. {
  24. var employee = new Employee()
  25. {
  26. Id = Convert.ToInt32(rdr["Id"]),
  27. Name = rdr["Name"].ToString(),
  28. Position = rdr["Position"].ToString(),
  29. Office = rdr["Office"].ToString(),
  30. Age = Convert.ToInt32(rdr["Age"]),
  31. Salary = Convert.ToInt32(rdr["Salary"])
  32. };
  33. employees.Add(employee);
  34. }
  35. return (employees);
  36. }
  37. }
  38. public Employee GetEmployeeById(int? id)
  39. {
  40. Employee employee = new Employee();
  41. using (SqlConnection con = new SqlConnection(CS))
  42. {
  43. SqlCommand cmd = new SqlCommand("spGetEmployee", con);
  44. cmd.CommandType = CommandType.StoredProcedure;
  45. con.Open();
  46. SqlDataReader rdr = cmd.ExecuteReader();
  47. while (rdr.Read())
  48. {
  49. employee.Id = Convert.ToInt32(rdr["Id"]);
  50. employee.Name = rdr["Name"].ToString();
  51. employee.Position = rdr["Position"].ToString();
  52. employee.Office = rdr["Office"].ToString();
  53. employee.Age = Convert.ToInt32(rdr["Age"]);
  54. employee.Salary = Convert.ToInt32(rdr["Salary"]);
  55. }
  56. return employee;
  57. }
  58. }
  59. public void InsertNew(Employee employee)
  60. {
  61. using (SqlConnection con = new SqlConnection(CS))
  62. {
  63. var cmd = new SqlCommand("spAddNew", con);
  64. con.Open();
  65. cmd.CommandType = CommandType.StoredProcedure;
  66. cmd.Parameters.AddWithValue("@Name", employee.Name);
  67. cmd.Parameters.AddWithValue("@Position", employee.Position);
  68. cmd.Parameters.AddWithValue("@Office", employee.Office);
  69. cmd.Parameters.AddWithValue("@Age", employee.Age);
  70. cmd.Parameters.AddWithValue("@Salary", employee.Salary);
  71. cmd.ExecuteNonQuery();
  72. }
  73. }
  74. public void Update(Employee employee)
  75. {
  76. using (SqlConnection con = new SqlConnection(CS))
  77. {
  78. var cmd = new SqlCommand("spUpdateEmployee", con);
  79. cmd.CommandType = CommandType.StoredProcedure;
  80. con.Open();
  81. cmd.Parameters.AddWithValue("@Id", employee.Id);
  82. cmd.Parameters.AddWithValue("@Name", employee.Name);
  83. cmd.Parameters.AddWithValue("@Position", employee.Position);
  84. cmd.Parameters.AddWithValue("@Office", employee.Office);
  85. cmd.Parameters.AddWithValue("@Age", employee.Age);
  86. cmd.Parameters.AddWithValue("@Salary", employee.Salary);
  87. cmd.ExecuteNonQuery();
  88. }
  89. }
  90. public void Delete(Employee employee)
  91. {
  92. using (SqlConnection con = new SqlConnection(CS))
  93. {
  94. var cmd = new SqlCommand("spDeleteEmployee", con);
  95. cmd.CommandType = CommandType.StoredProcedure;
  96. con.Open();
  97. cmd.Parameters.AddWithValue("@Id", employee.Id);
  98. cmd.ExecuteNonQuery();
  99. }
  100. }
  101. }
  102. }
Step 6: Open on Web Config file and add database connection string.
  1. <connectionStrings>
  2. <add name="EmployeeContext" connectionString="data source=farhan; database=MvcDemo; integrated security=true;" providerName="System.Data.SqlClient"/>
  3. </connectionStrings>
Step 7: Now right click on controllers folder and Add controller
A window will appear. Choose MVC5 Controller-Empty and click "Add".
After clicking on "Add", another window will appear with DefaultController. Change the name to HomeController and click "Add". The HomeController will be added under the Controllers folder. Don’t change the Controller suffix for all controllers, change only the highlight, and instead of Default, just change Home.

Controller Class code

  1. using RepositoryPatternWithADO.Net.Models;
  2. using RepositoryPatternWithADO.Net.Repository;
  3. using System.Net;
  4. using System.Web.Mvc;
  5. namespace RepositoryPatternWithADO.Net.Controllers
  6. {
  7. public class HomeController : Controller
  8. {
  9. private readonly MockEmployee db = new MockEmployee();
  10. public ActionResult Index()
  11. {
  12. var employee = db.GetEmployees();
  13. return View(employee);
  14. }
  15. public ActionResult Details(int? id)
  16. {
  17. var employee = db.GetEmployeeById(id);
  18. return View(employee);
  19. }
  20. public ActionResult Create()
  21. {
  22. return View();
  23. }
  24. [HttpPost]
  25. [ValidateAntiForgeryToken]
  26. public ActionResult Create(Employee employee)
  27. {
  28. if (ModelState.IsValid)
  29. {
  30. db.InsertNew(employee);
  31. return RedirectToAction("Index", "Home");
  32. }
  33. return View();
  34. }
  35. [HttpGet]
  36. public ActionResult Edit(int? id)
  37. {
  38. if (id == null)
  39. {
  40. return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
  41. }
  42. var employee = db.GetEmployeeById(id);
  43. if (employee==null)
  44. {
  45. return HttpNotFound();
  46. }
  47. return View(employee);
  48. }
  49. [HttpPost]
  50. [ValidateAntiForgeryToken]
  51. public ActionResult Edit(Employee employee)
  52. {
  53. db.Update(employee);
  54. return RedirectToAction("Index", "Home");
  55. }
  56. [HttpGet]
  57. public ActionResult Delete(int? id)
  58. {
  59. if (id==null)
  60. {
  61. return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
  62. }
  63. var employee = db.GetEmployeeById(id);
  64. if (employee==null)
  65. {
  66. return HttpNotFound();
  67. }
  68. return View(employee);
  69. }
  70. [HttpPost,ActionName("Delete")]
  71. public ActionResult ConfirmDelete(int id)
  72. {
  73. var employee = db.GetEmployeeById(id);
  74. db.Delete(employee);
  75. return RedirectToAction("Index","Home");
  76. }
  77. }
  78. }
Step 8: Right click on Index of ActionResult choose “Add View” and click on it. Now you will get another window which has default view name as ActionResult name. Checked Use a lay page and click on “Add”.

Index View

  1. @model IEnumerable<RepositoryPatternWithADO.Net.Models.Employee>
  2. @{
  3. ViewBag.Title = "Index";
  4. }
  5. <link href="@Url.Content("~/Content/DataTables/css/dataTables.bootstrap4.min.css")" rel="stylesheet" />
  6. <div style="margin-bottom:10px;">
  7. @Html.ActionLink("Create New", "Create", "Home", "", new { @class = "btn btn-sm btn-primary rounded-0" })
  8. </div>
  9. <h4 class="text-center text-uppercase">List of employees</h4>
  10. <table id="example" class="table table-bordered">
  11. <thead>
  12. <tr>
  13. <th>@Html.DisplayNameFor(m => m.Name)</th>
  14. <th>@Html.DisplayNameFor(m => m.Position)</th>
  15. <th>@Html.DisplayNameFor(m => m.Office)</th>
  16. <th>@Html.DisplayNameFor(m => m.Age)</th>
  17. <th>@Html.DisplayNameFor(m => m.Salary)</th>
  18. <th>Action(s)</th>
  19. </tr>
  20. </thead>
  21. <tbody>
  22. @foreach (var item in Model)
  23. {
  24. <tr>
  25. <td>@item.Name</td>
  26. <td>@item.Position</td>
  27. <td>@item.Office</td>
  28. <td>@item.Age</td>
  29. <td>@item.Salary</td>
  30. <td>
  31. <a href="@Url.Action("Details","Home",new { id=item.Id})" class="btn btn-sm btn-primary"><i class="fa fa-eye"></i></a>
  32. <a href="@Url.Action("Edit","Home",new { id=item.Id})" class="btn btn-sm btn-info"><i class="fa fa-pencil-square"></i></a>
  33. <a href="@Url.Action("Delete","Home",new { id=item.Id})" class="btn btn-sm btn-danger"><i class="fa fa-trash-o"></i></a>
  34. </td>
  35. </tr>
  36. }
  37. </tbody>
  38. </table>
  39. <script src="@Url.Content("~/Scripts/jquery-3.4.1.min.js")"></script>
  40. <script src="@Url.Content("~/Scripts/DataTables/jquery.dataTables.min.js")"></script>
  41. <script src="@Url.Content("~/Scripts/DataTables/dataTables.bootstrap4.min.js")"></script>
  42. <script type="text/javascript">
  43. $(document).ready(function () {
  44. $('#example').DataTable();
  45. });
  46. </script>
Step 9: Now similarly right click on Details of ActionResult choose “Add View” and click on it. Now you will get another window which has default view name as ActionResult name. Checked Use a lay page and click on “Add”.

Details View

  1. @model RepositoryPatternWithADO.Net.Models.Employee
  2. @{
  3. ViewBag.Title = "Details";
  4. }
  5. <div class="card">
  6. <div class="card-header">
  7. <h5 class="text-uppercase">Employee Details</h5>
  8. </div>
  9. <div class="card-body">
  10. <dl class="dl-horizontal">
  11. <dt>
  12. @Html.DisplayNameFor(model => model.Name)
  13. </dt>
  14. <dd>
  15. @Html.DisplayFor(model => model.Name)
  16. </dd>
  17. <dt>
  18. @Html.DisplayNameFor(model => model.Position)
  19. </dt>
  20. <dd>
  21. @Html.DisplayFor(model => model.Position)
  22. </dd>
  23. <dt>
  24. @Html.DisplayNameFor(model => model.Office)
  25. </dt>
  26. <dd>
  27. @Html.DisplayFor(model => model.Office)
  28. </dd>
  29. <dt>
  30. @Html.DisplayNameFor(model => model.Age)
  31. </dt>
  32. <dd>
  33. @Html.DisplayFor(model => model.Age)
  34. </dd>
  35. <dt>
  36. @Html.DisplayNameFor(model => model.Salary)
  37. </dt>
  38. <dd>
  39. @Html.DisplayFor(model => model.Salary)
  40. </dd>
  41. </dl>
  42. @Html.ActionLink("Edit", "Edit", "Home", new { id = Model.Id }, new { @class = "btn btn-sm btn-info rounded-0" })
  43. @Html.ActionLink("Back to List", "Index", "", new { @class = "btn btn-sm btn-primary rounded-0" })
  44. </div>
  45. </div>
Step 10: Now click on Create of ActionResult choose “Add View” and click on it. Now you will get another window that has a default view name as ActionResult name. Checked Use a lay page and click on “Add”.

Create View

  1. @model RepositoryPatternWithADO.Net.Models.Employee
  2. @{
  3. ViewBag.Title = "Create";
  4. }
  5. <div class="card">
  6. <div class="card-header">
  7. <h5>New Employee</h5>
  8. </div>
  9. <div class="card-body">
  10. @using (Html.BeginForm())
  11. {
  12. @Html.AntiForgeryToken()
  13. <div class="form-group">
  14. @Html.LabelFor(m => m.Name)
  15. @Html.TextBoxFor(m => m.Name, new { @class = "form-control" })
  16. @Html.ValidationMessageFor(m => m.Name)
  17. </div>
  18. <div class="form-group">
  19. @Html.LabelFor(m => m.Position)
  20. @Html.TextBoxFor(m => m.Position, new { @class = "form-control" })
  21. @Html.ValidationMessageFor(m => m.Position)
  22. </div>
  23. <div class="form-group">
  24. @Html.LabelFor(m => m.Office)
  25. @Html.TextBoxFor(m => m.Office, new { @class = "form-control" })
  26. @Html.ValidationMessageFor(m => m.Office)
  27. </div>
  28. <div class="form-group">
  29. @Html.LabelFor(m => m.Age)
  30. @Html.TextBoxFor(m => m.Age, new { @class = "form-control" })
  31. @Html.ValidationMessageFor(m => m.Age)
  32. </div>
  33. <div class="form-group">
  34. @Html.LabelFor(m => m.Salary)
  35. @Html.TextBoxFor(m => m.Salary, new { @class = "form-control" })
  36. @Html.ValidationMessageFor(m => m.Salary)
  37. </div>
  38. <div class="form-group">
  39. <button type="submit" class="btn btn-sm btn-primary rounded-0">Submit</button>
  40. </div>
  41. }
  42. </div>
  43. </div>
Step 11: Right click on Edit of ActionResult choose “Add View” and click on it. Now you will get another window which has default view name as ActionResult name. Checked Use a lay page and click on “Add”.

Edit View

  1. @model RepositoryPatternWithADO.Net.Models.Employee
  2. @{
  3. ViewBag.Title = "Edit";
  4. }
  5. <div class="card">
  6. <div class="card-header">
  7. <h3>Edit Employee</h3>
  8. </div>
  9. <div class="card-body">
  10. @using (Html.BeginForm())
  11. {
  12. @Html.AntiForgeryToken()
  13. @Html.HiddenFor(m => m.Id)
  14. <div class="form-group">
  15. @Html.LabelFor(m => m.Name)
  16. @Html.EditorFor(m => m.Name, new { htmlAttributes = new { @class = "form-control" } })
  17. @Html.ValidationMessageFor(m => m.Name)
  18. </div>
  19. <div class="form-group">
  20. @Html.LabelFor(m => m.Position)
  21. @Html.EditorFor(m => m.Position, new { htmlAttributes = new { @class = "form-control" } })
  22. @Html.ValidationMessageFor(m => m.Position)
  23. </div>
  24. <div class="form-group">
  25. @Html.LabelFor(m => m.Office)
  26. @Html.EditorFor(m => m.Office, new { htmlAttributes = new { @class = "form-control" } })
  27. @Html.ValidationMessageFor(m => m.Office)
  28. </div>
  29. <div class="form-group">
  30. @Html.LabelFor(m => m.Age)
  31. @Html.EditorFor(m => m.Age, new { htmlAttributes = new { @class = "form-control" } })
  32. @Html.ValidationMessageFor(m => m.Age)
  33. </div>
  34. <div class="form-group">
  35. @Html.LabelFor(m => m.Salary)
  36. @Html.EditorFor(m => m.Salary, new { htmlAttributes = new { @class = "form-control" } })
  37. @Html.ValidationMessageFor(m => m.Salary)
  38. </div>
  39. <div class="form-group">
  40. <button type="submit" class="btn btn-sm btn-primary rounded-0">Update</button>
  41. @Html.ActionLink("Back to List", "Index", "", new { @class = "btn btn-sm btn-primary rounded-0" })
  42. </div>
  43. }
  44. </div>
  45. </div>
Step 12: Right click on Delete of ActionResult choose “Add View” and click on it. Now you will get another window which has default view name as ActionResult name. Checked Use a lay page and click on “Add”.

Delete View

  1. @model RepositoryPatternWithADO.Net.Models.Employee
  2. @{
  3. ViewBag.Title = "Delete";
  4. }
  5. <div class="alert alert-danger">
  6. <h6>Are you sure you want to delete this?</h6>
  7. </div>
  8. <div class="card">
  9. <div class="card-header">
  10. <h5 class="text-uppercase"> Delete Emplyee Record</h5>
  11. </div>
  12. <div class="card-body">
  13. <dl class="dl-horizontal">
  14. <dt>
  15. @Html.DisplayNameFor(model => model.Name)
  16. </dt>
  17. <dd>
  18. @Html.DisplayFor(model => model.Name)
  19. </dd>
  20. <dt>
  21. @Html.DisplayNameFor(model => model.Position)
  22. </dt>
  23. <dd>
  24. @Html.DisplayFor(model => model.Position)
  25. </dd>
  26. <dt>
  27. @Html.DisplayNameFor(model => model.Office)
  28. </dt>
  29. <dd>
  30. @Html.DisplayFor(model => model.Office)
  31. </dd>
  32. <dt>
  33. @Html.DisplayNameFor(model => model.Age)
  34. </dt>
  35. <dd>
  36. @Html.DisplayFor(model => model.Age)
  37. </dd>
  38. <dt>
  39. @Html.DisplayNameFor(model => model.Salary)
  40. </dt>
  41. <dd>
  42. @Html.DisplayFor(model => model.Salary)
  43. </dd>
  44. </dl>
  45. @using (Html.BeginForm())
  46. {
  47. @Html.AntiForgeryToken()
  48. <div class="form-actions no-color">
  49. <input type="submit" value="Delete" class="btn btn-danger btn-sm rounded-0" />
  50. @Html.ActionLink("Back to List", "Index", "", new { @class = "btn btn-sm btn-primary rounded-0" })
  51. </div>
  52. }
  53. </div>
  54. </div>
Step 13: Build your project and run by pressing ctrl+F5

Summary

In this article, I have explained CURD (Create, Update, Read and Delete) operations using repository pattern and ADO.Net where I have created IEmployee interface and EmployeeMock class for database interaction like retrieving data, inserting data, updating existing data and deleting data from SQL database table.