How to get collection of records to View in ASP.NET MVC? (Dapper ORM)

In previous we seen how CRUD operations are performed in ASP.NET MVC using Dapper ORM.

Now today we'll see how to get a collection of records to View using ViewBag.

Display the collection of records in View is done by various way.

I'll show you how to do it using ViewBag.

I've already shown you how to  install Dapper  and give connection to database. If you are not aware for that than kindly view previous post.

Now I'll continue with creating model. I have a table name District_Master which contains District and it's code.

Model: (District.cs)

using System;

using System.Collections.Generic;
using System.Linq;

using System.ComponentModel.DataAnnotations;
using System.Web;
using Dapper;

using System.ComponentModel.DataAnnotations.Schema;
namespace GetData.Models
{

    [Dapper.Table("District_Matser")]

        private string _districtcode;
public class District
    {
        private string _district;



            get { return _district; }
[Display(Name="District")]
        public string DISTRICT
        {

        public string DISTRICTCODE
set { _district = value; }
        }
         [Display(Name = "DistrictCode")]
        {

}
get { return _districtcode; }
            set { _districtcode = value; }
        }

    }

Now we have created a model that will get and set value to database for district_master.

But how would it perform dml or ddl operations?

So we'll create new model and let us call it as MainModel which would contain helper methods.

This method we'll access through controller and get the records to View.

MainModel.cs

using System;

using System.Collections.Generic;
using System.Linq;

using System.ComponentModel.DataAnnotations;
using System.Web;
using Dapper;

using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;


        OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["dbOraConn"].ConnectionString);
namespace GetData.Models
{

    public class MainModel
    {



        public IEnumerable districtdata()
        {

            string query = "select district,districtcode from district_master";
if (con.State == ConnectionState.Closed)
            {

            var records = con.Query(query);
con.Open();
            }



            return records;

}
}

    }

Controller:

Now create ActionResult method that will access the data and pass the data to view using ViewBag.

using System;

using System.Collections.Generic;
using System.Linq;

using System.Web.Mvc;
using System.Web;

using Dapper;
using GetRainData.Models;


    public class DefaultController : Controller
namespace GetData.Controllers
{
    {

        public ActionResult Index()
// GET: Default
        {

            var district = mm.districtdata().ToList();
MainModel mm = new MainModel();


        }
ViewBag.ddldistrict = district;
            return View();
    }

}

View:

Now data is passed to view. So after passing the data we've to cast the viewbag to it's datatype. I've casted to ICollection<T>. And for using models in view we have to import namespace using WebApplication.Models;

Just see now how it is being done.

Now run the application and see the output.

How to use Dapper dot Net ORM with ASP.NET MVC??

For comparisons between different ORM kindly refer the previous post of this blog.

Dapper's primary feature is mapping from .NET classes to database tables (and from CLR data types to SQL data types). Dapper also provides data query and retrieval facilities.

Dapper is a micro-ORM: it does not offer the full range of features of a full ORM such as NHibernate or Entity Framework. This is by design. It focuses on simplicity and performance rather than full automation. Dapper does not generate the SQL queries, but only maps the result to Plain Old CLR Objects (POCOs). Third party extensions are available for mapping objects to INSERT and UPDATE queries. It is optimized for use with .NET 4.0 or higher.

The single class ORM is also available on NuGet.

Dapper was started by Sam Saffron and Marc Gravell because of the N+1 and other performance issues with Entity framework. It was written for and is used by Stack Overflow.

Let us now see how to create a web application in ASP.NET MVC using Dapper ORM.

Open Visual Studio (used by me is :2013) -> New Project -> Choose  MVC Project however you want.

Now as you use to give the reference to Entity Framework using Nuget Packet Manager, similarly, go to Manager and search for Dapper & Dapper.SimpleCRUD and install that packages as shown in fig.

I have created a database in Sql Server and given connection string in web.config as we use to do normally. My Connection Name is EmployeeDBContext(* Not necessary to use as DBContext)

Table : Emp_Personal. Column of my tables are as E_Id, E_Name, E_Address, E_Contact, E_DOB.

Now create a model in model folder. I have created Model named Employee and defined the attributes as shown.

Namespace:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using Dapper;

using System.Data;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

Class:

Now after creating a model we'll create another model in which we would write the methods to get the data from database. 

Let us call that model as MainModel.cs

NameSpace:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

using Dapper;

In class first thing we'll give a connection to database and yes welcome back to old format of code again in Dapper.

Now go to controller folder and add a controller. 

Display All Employees:

In controller I've created a method to get all employee details as GetDetails(). 

For details we'll  call the MainModel class and access the method EmployeeDetails() which is as shown :

Now accessing through controller:

Now to view the records add the view by rightclick on Action Method and Add View -> List & select  a model as shown:

Note while running it may ask to map the SQLMAPPER.cs file for that you can goto Git or Google and find out sqlmapper.cs file for dapper or you may comment down.

Insert Records:

Similarly we've to go for all Insert, Edit and delete.

         MainModel Method:

Make sure here values are passed as  "@Columnname" and connection.Execute(query,new {attributes}); we've to pass.

       Controller Method:

In controller first we'll use HTTP GET for redirecting to Create View and from there we'll use HTTP POST on submission. 

   View : (rightclick and details , select model) 

Your view should be something like this.

@model DapperORM.Models.Employee

    

    

Edit Records:

For edit and delete we'll create a method which will get the data by selected id from details page and display the data to view and there after perform the HTTP POST(submission).

        MainModel Method: 

 public Employee GetEmployeeList(string E_Id)
        {

            string query = "select E_Id,E_Name,E_Address,E_Contact,E_DOB from Emp_Personal where E_Id=@E_Id";

            var result = con.Query(query, new { E_Id }).Single();

            return result;

        }

public string Updateemployee(Employee objemp)
        {

            string query = "update Emp_Personal set E_Name=@E_Name,E_Address=@E_Address,E_Contact=@E_Contact,E_DOB=@E_DOB where E_Id=@E_Id";
con.Execute(query, new { objemp.E_Name, objemp.E_Address, objemp.E_Contact, objemp.E_DOB, objemp.E_Id });

        }
string result = "updated";

            return result;

        Controller Method: 























        View Method:  (Some thing like this your view should be)

         

          Delete Records:

                MainModel Method :

              Controller Method :

             View :

Note: be sure that while edit and delete GetEmployeeList ()method should be called during httpget. and then perform operation.

That's it. And yes for details of particular employee.

Details of particular employee

               MainModel Method:

                        Same GetEmployeeList as for delete and edit.

              Controller Method:

[HttpGet]

        public ActionResult Details(string id)
{

            return View(mm.GetEmployeeList(id));
MainModel mm = new MainModel();


        }

            View :

And you are done.

Which ORM should I use with ASP.NET. Dapper | Linq to Sql | Entity Framework | NHibernate ???

Let me show you some comparison between different ORM which was posted on SlideShare.NET .

• In next blog I'll show you how to create a web application in ASP.NET MVC using Dapper as an ORM. 

Why we use explicit dispose() method in asp.net MVC controllers?

protected override void Dispose(bool disposing)
{
            if (disposing)

            }
{
                db.Dispose();

        }

            base.Dispose(disposing);

There are questions why we use to call this method.

Well the reason behind this is well given by C Hao in one of the Stack Overflow forums which would I like to share with you.

As said by Hao,

Dispose is for releasing "unmanaged" resources (for example, sockets, file handles, Bitmap handles, etc), and if it's being called outside a finalizer (that's what the disposing flag signifies, BTW), for disposing other IDisposable objects it holds that are no longer useful.

"Unmanaged" resources aren't managed by the CLR (hence the name), and GC doesn't mess with them or free them all by itself; absent a Dispose method (and code actually using it!), it'll rely on the object's finalizer to clean up. Eventually the finalizer will run (if the app's healthy, and the object has a finalizer), and if the finalizer does its job then all's semi OK....but it'll take its sweet time in doing so -- and if you run out of handles in the meantime, oh well. Too bad for that other thread/process/whatever that needed them.

If you Dispose, though, the resources are released immediately, and things run better all around.

(By the way, this is not restricted to EF, SQL Server, or any other technology. The Disposable pattern is found throughout the .net framework, and it's considered good practice to take advantage of it whenever you have an IDisposable that's no longer being used.)

As for why IDisposable is implemented so far up the tree, rather than you just implementing it on a case by case basis...i'm not 100% sure. But imagine you were writing a framework. Consider that if everything weren't an IDisposable, you'd have to check -- every time you wanted to get rid of something! -- whether the object is disposable, and Dispose it if so. If you instead implement IDisposable "just in case", though, things are simplified -- you just always dispose. (If an object doesn't have anything to clean up, it just doesn't override Dispose -- in which case its parent's Dispose gets called and does whatever cleanup it has to, which may be nothing as well...) And it's a common enough case for controllers to have stuff to clean up, that even if that's not the real reason, it makes a lot of sense to do anyway.

Thanks .

Binding data from database to table using JQuery datatable's plugin in ASP.NET MVC

• JQuery provides a plugin through which one can display data from database to your web application.
• Let's see how?
• Goto JQuery datatables.net and copy the css and JS from there and add to your page.

• F

• First of all let us see how to access the data from database to application.
• Here we'll use Json and ajax request for displaying the data and we'll get the data from database using WebService.
• First of All create a database and install entity framework in your application through nuget.
• Create a model for working with database as shown.
• 
  
• Now create a Web Service first that will return the data in Json format.
• Create a service named  and a Web Method  as shown in below figure.

• Here above JavaScriptSerializer(Serialize) is used to return the data in Json format.
• Run this service you should see the data in Json format something like this as shown below.
•  

 

•  (* Note you should have added the css and JS which I described before for the reference to JQuery DataTables and CSS)
• Add headers to the table as shown.

• Now write the script as shown in figure below.

• Let me explain you what is exactly done above.
• As you can see we have given reference for JQuery, Datatables.min.css & DataTables.min.js.
• Now coming to <script> portion.
• We are calling the webservice as shown through url parameter of ajax.
• DataType we used is Json because the data we'll consume would be in Json.
• Now on success let us define a parameter called 'data', on tableid='datatable' selector provide property to dataTable attribute of JQuery DataTables plugin '('#datatable').dataTable()'.
• Now data to that plugin datatable we would provide is our data parameter on success('data:data').
• There after just bind the column names of database with the data as shown.
• After binding if you have given the date as datatype in db then you wouldn't see date for that some script need to be changed and return the date by rendering the date in a proper way as shown above.
• Similarly  if you want to display currency before salary then as shown in fig for Mobile '(M)' can be done easily.
• This is how to use the JQuery DataTables.net in a simple way.

JQuery UI: Datepicker- Set Range of year and choose your own date format

• For using any kind of  libraries, it's better to go online and find the updated one and link it to your website. 
• So for using JQuery Datepicker, first goto Jquery CDN and get the updated library.
• Get these three libraries and give reference to your website:

1. jquery-ui.css
2. jquery-1.10.2.js
3. jquery-ui.js

• Now include one textbox in page .

• Add script for datepicker as shown for allowing user to select the date.

• Now just run the website you'll see something like this while clicking on textbox.

• Notice if you want to select year 2010 or before how ridiculous it would be by going one-by-one step back.
• So for removing this you have to add some parameters to datepicker as shown in figure.
• Script block shows you how to set Month & Year dropdown to be visible and set year range and how to get value in your desired format.

• This is it now just run the website and click on textbox you'll be able to see what you desired.