Using Raw ADO.Net/Business object for data accessing in MVC

In Breif: In this article I’m going to explain how to use Raw ADO.NET as model in MVC.4 or Accessing Database by using business object/ADO.Net. This is the another approach to be used in the data layer. Previously in Dataaccess using entity framework article we have seen how to use entity framework for interacting Database.

In Detail:

Let us build the simple mvc application by making use of Business object to display the employee details.

Step 1: Preparing Model for the project.

Here our model is nothing but the Business object. It is taken in the separate assembly.

a)To do this Right click on solution->Add ->New Project->ClassLibrary->say BusinessLayer->Add. Change the class name clsEmployee. Add the following auto implemented property.

public int Id { get;set;}
public string Name { get; set; }
public string Gender { get; set; }
public string Designation { get; set; }
public string City { get; set; }

b) Add another class with name clsEmployeeBusinesLayer. This class is used to write the ADO.Net code to access the database.

Declare a property Employee of type of clsEmployee. To Returns the list of employee from the table tblEmployee.

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace BusinessLayer
{
public class clsEmployeeBusinesLayer
{
string strConectionString = ConfigurationManager.ConnectionStrings["mvcSampleDb"].ConnectionString;
List<clsEmployee> objClsEmployee = new List<clsEmployee>();
public IEnumerable<clsEmployee> EmployeeList
{
get
{
using (SqlConnection con = new SqlConnection(strConectionString))
{
SqlCommand cmd = new SqlCommand("select Name,Gender,Designation,City from tblEmployee", con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
clsEmployee Employees = new clsEmployee();
Employees.Name = rdr["Name"].ToString();
Employees.Gender = rdr["Gender"].ToString();
Employees.Designation = rdr["Designation"].ToString();
Employees.City = rdr["City"].ToString();
objClsEmployee.Add(Employees);
}
}
return objClsEmployee;
}
}
}
}

c) Build the class library BusinessLayer . This is the model for our application.

4) Add reference of BusinessLayer to the project. Right click on reference->Add reference

Step 2: Add a Controller with name EmployeeController.

Create an object of clsEmployeeBusinesLayer.

Employees property which returns the List of employee and pass this to view.

using BusinessLayer;

namespace mvcApp.Controllers
{
public class EmployeeController : Controller
{
public ActionResult Index()
{
clsEmployeeBusinesLayer objEmployeeBusinessLayer = new clsEmployeeBusinesLayer();
List<clsEmployee> Employee = objEmployeeBusinessLayer.EmployeeList.ToList();
return View(Employee);
}
}
}

Step 3: Add View.

Right click on Index method->Add View->select strongly typed view->select scaffold template as List.

scaffold template List which writes the code on behalf of us to display the employee details in Index.cshtml.

this adds the following code

@model IEnumerable

@{
    ViewBag.Title = "Using Raw ADO.Net/Business object  for data accessing in MVC";
}


Using Raw ADO.Net/Business object  for data accessing in MVC
@Html.ActionLink("Create New", "Create")

@foreach (var item in Model) {
    
}


    


        
@Html.DisplayNameFor(model => model.Name)
        
        
@Html.DisplayNameFor(model => model.Gender)
        
        
@Html.DisplayNameFor(model => model.Designation)
        
        
@Html.DisplayNameFor(model => model.City)
        
        

    

        
@Html.DisplayFor(modelItem => item.Name)
        
        
@Html.DisplayFor(modelItem => item.Gender)
        
        
@Html.DisplayFor(modelItem => item.Designation)
        
        
@Html.DisplayFor(modelItem => item.City)
        
        
@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        
    

First line @model IenumerableclsEmployee> indicates that,views manipulates on Employee object of type list. List implemented by Ienumerable. Also the made use of HTML helpers HtmlDisplayName and actionLink will discuss more on this in the next article.

Step 4: Run the application. You can see the output of employee details list fetched database using Raw ADO.Net method.

Conclusion: 
Adding to EntityFramework, using the Raw ADO.NET /Business object is the another method of programming in MVC.Net. Here we need to write the SQL queries against the table/procedures and less object oriented.

In EF select, update, insert & delete operation will be directly handled by the framework.
happy coding :) & keep visiting.