Post
Ask Question

Repository Pattern with ADO.Net in MVC

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;    
  12.     
  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. 


Similar Articles