Dapper ORM for .Net

Dapper is opensource micro ORM framework that will help you access data from database in .Net projects. The project is maintained by stackoverflow community and hosted on GitHub

Usually database query return table which contains rows of information. In the object based programming world require data as class/objects. ORM help us to convert row into an object. It can also perform all CURD operations too.

The ORM can be utilized in Model-View-Model View pattern and it will help minimize the complexity of the code.

Dapper is not a database specific package. It can be used with any database (SQL Server,MySQL, MS Access etc). It uses a IDbConnection for performing operations.

It does not replacing anything at all, a simple example will explain the concept.

Dapper Object List example

using Dapper;

class Group
       public int ID { get; set; }
       public string g_name { get; set; }
       public double g_dr_loc { get; set; }
       public static List<Group> GetAll()
           using (IDbConnection db = new System.Data.OleDb.OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["cstring"].ConnectionString))
               return db.Query<Group>("select id,g_name from groups").ToList();


var groups=Group.GetAll()

Here in the example, the Group model’s GetAll() static method will query all the rows into List of Group(model) using the Dapper. Without dapper you have to use Linq or a loop to store the row as a list of objects.

You can see how Linq can be used to achieve this in the following posts

Linq – suming rows,create list of custom fields,create model class object list from data table - Linq - suming rows,create list of custom fieds,create model class list from data table

Nullable date with Linq and Model class in C#

In C# Model class you may have experienced a nullable Error while fetching the rows with nullable column from database to your Model objects.

To treat the nullable in proper way , you have to tell your Model class and Linq this is nullable field.

Model class

*The nullable is applied to Value Type .This is not required for string

In the Model class I have a field edate which is in database is a nullable field. I want to make this field nullable in my model

 class StocKBatchModel
        public int B_Id { get; set; }        
        public string Name { get; set; }
        public DateTime? Edate { get; set; }

By placing ? after the type of my class property , it becomes nullable. Say the DateTime becomes nullable , I can use the same in Linq in my LInq query as follows

var blist = (from m in batch_table.AsEnumerable()                                 
                                 select new
                                     obj = new Models.StocKBatchModel()
                                         B_Id = m.Field<int>("b_id"),
                                         Name = m.Field<string>("name"),
                                         Edate = m.Field<DateTime?>("exp_date"),                                       

Hope this will helpful for someone.

How to find sum from obervablecollection list in C#

This example shows how you can simply find aggregate functions like sum, average etc from a Obervablecollection list.

ObservableCollection and Model-View-ViewModel is a ideal for a typical WPF application, these are programmers two of the best tools.

In this example I am using a model class , and want to find sum of inventory qty from batch list.

double qty=_batches.Sum((b) => b.qty));

Here _batches is representing the ObservableCollection of Model batch.

C#Tips/Tricks – Linq and Functions

Do you know Linq in c# has special capabilities to handle data smoothly. As we learned you can operate on user defined types and list using Linq.

Even you can use your own functions from within the Linq

C# Linq

The following example illustrate how to use functions within Linq

 var salesSummary = (from s in salesTable.AsEnumerable()
                           join c in Customer.AsEnumerable() on s.Field<int>("cid").ToString() equals
                           select new
                               sv= new SalesView()
                                   Date = s.Field<DateTime>("date"),                                   
                                   invice = s.Field<int>("vno"),                                   
                                   Customer = c.Field<string>("name"),
                                   Amount = string.Format("{0:0.00}", s.Field<decimal>("amount")),                 
 InvBalance = string.Format("{0:0.00}", GetActBalance(s.Field<DateTime>("date"), s.Field<int>("cid"), s.Field<string>("vno"))),                  

In the above example I used a function GetActBalance to display, the invoice balance at the point of sale , assume that they may paid some amount at the time of sale.

Linq – suming rows,create list of custom fields,create model class object list from data table

Querying data from a data table in .Net is easy with Linq. Linq is a query language which is applicable to all type of objects such as List,Tables etc in .Net . Infact you can simply convert the table data to object like List of generic type or cutom Model class object with Linq operation, in limited sense you can forget loops.

Sum table column

Let’ learn how to simply sum up a column in your data table using Linq.

var comms = (from p in public_members.payroll_posting.AsEnumerable() where p.Field<string>
("eid") == "1001" select p.Field<decimal>("amount")).Sum();

The above linq simply fetch amount from the data table for employee id “1001”. I also place an outer pareanthesis for summing up. The parenthesis come handy when you want to convert the values you selected to Arrays,List or perform aggragate functions like Sum,Count etct

Create List of rows with custom list

 var comm_list = (from p in posting.AsEnumerable() where  p.Field<string>("type") == 
"Commission" select new { amount = p.Field<decimal>("amount"), eno = p.Field<int>("pp_no"),
 edate = p.Field<DateTime>("post_date") }).ToList();

I have a table in which I store all employee payments under seperate types. I just want to create a list of commission paid to the employees as new list with custom column which is not exist in the data table .

The select new will create a model class like fields.

Create a list of objects from Linq query

Create list of Objects[Model class] with Linq

Following LinQ will help to build Model class object list from a data table, remember I used myown Model class which I not provided here. Use your own

 var payrolls = (from pp in payroll_entries.AsEnumerable()
                                  join emp in employees.AsEnumerable() on pp.Field<string>("eid") equals emp.Field<string>("eid")
                                  select new
                                      p = new PayRollEntryModel()
                                          VNO = pp.Field<int>("pe_no").ToString(),
                                          DATE = pp.Field<DateTime>("pe_date"),
                                          Employee = LedgeName(emp.Field<int>("lid")),
                                          EID = Convert.ToInt32(pp.Field<string>("eid")),
                                          CashAccount = LedgeName(pp.Field<int>("crledger")),
                                          Total = string.Format("{0:0.00}", pp.Field<decimal>("amount")),
                                          CrACID = Convert.ToInt32( pp.Field<int>("cash_ac")),
                                          Narration = pp.Field<string>("narration"),
                                          DrAcid = Convert.ToInt32( emp.Field<int>("lid")),                                         
                                          WDs= pp.Field<decimal>("wdays").ToString() ,
                                          WHs=  pp.Field<decimal>("whours").ToString(),                                       


At the 26th line of the above code I used .p because the linq only create a list of p which is a anonimous type. I want is a list of the Model class object which is PayRollEntryModel.

I think this will help somebody out there… leave comment and share your thoughts

All the examples I demonstrated here is part of my personal project and I should say these are working very well.


C#Tips/Tricks – Linq to array

You can easily convert a data table rows into object array using Linq query in C#.Net

var names_array = (from cols in tblcols.AsEnumerable() select cols.Field<string>("student_name")).ToArray();

Array can be used to supply items to list box and combo box controls as follows

 listbox1.Items.AddRange(names_array );

DataGridView meets Linq

DataGridView is a tool for displaying, modifying, entering values in Visual Studio.Net. Usually we are using loops or readers to traverse through rows or columns.

Linq query make it possible to do these take with few lines, instead of complicated loops.

We have a data entry GridView which accepts purchase details such as name,qty,price etc. Lets get the sum of quantity supplied in the grid using Linq query

We do the following

  1. Create List of values
  2. Call the Sum() for total function for the column we specified

The Linq Querry

var qt = from DataGridViewRow row in dataGridView1.Rows where row.Cells[COL_QTY.Index].FormattedValue.ToString().Trim().Length!=0 && row.Cells[COL_QTY.Index].FormattedValue != null select ( row.Cells[COL_QTY.Index].FormattedValue.ToString().Trim());

We have to make sure the cell is not null or it has any values at all, we place where section, otherwise the System will fire you for an unhandled exception, lol.

Summing up values

txt_tqty.Text = qt.Sum(c => Convert.ToDouble(c)).ToString();

That’s all