Dapper Micro ORM Series

Recently before some time I have created a blog post about list of blog post I have written about Petapoco Micro ORM. So one of the friend suggested I should write same kind of series post about Dapper Micro ORM so that reader of my blog can find the all the posts on same page. So that’s why I writing this blog post.

What is dapper:

Dapper is Micro ORM developed by Sam Saffron few years ago while he was working as lead developer at stack exchange. This ORM was developed specially for Stack Exchange QA sites like stackoverflow.com and superuser.com for the performance improvement. It has got a single file where all the code has been written. You can download dapper Micro ORM from the following location.

http://code.google.com/p/dapper-dot-net/

Dapper Micro ORM related posts on dotnetjalps.com:

Following is a list of post related to dapper Micro ORM that I have written on this blog.

Playing with dapper Micro ORM and ASP.NET MVC 3.0
Insert with Dapper Micro ORM and ASP.NET MVC 3
Edit/Update with dapper ORM and ASP.NET MVC 3
Delete with Dapper ORM and ASP.NET MVC 3

If I write blog post about dapper then I will keep adding into this list. That’s it.

Hope you like it. Stay tuned for more!!

Continue Reading

CRUD operations with PetaPoco and ASP.NET MVC

In this post we are going to see how we can do CRUD operations with ASP.NET MVC and PetaPoco Micro ORM.

What is PetaPoco?

Petapoco is a tiny ORM developed by topten software. As per them it’s a tiny, fast, single-file micro-ORM for .NET and Mono.

• Like Massive it's a single file that you easily add to any project
• Unlike Massive it works with strongly typed POCO's
• Like Massive, it now also supports dynamic Expandos too - read more
• Like ActiveRecord, it supports a close relationship between object and database table
• Like SubSonic, it supports generation of poco classes with T4 templates
• Like Dapper, it's fast because it uses dynamic method generation (MSIL) to assign column values to properties

Features of PetaPoco:

As per topten software it contains following features.

• Tiny, no dependencies... a single C# file you can easily add to any project.
• Works with strictly undecorated POCOs, or attributed almost-POCOs.
• Helper methods for Insert/Delete/Update/Save and IsNew
• Paged requests automatically work out total record count and fetch a specific page.
• Easy transaction support.
• Better parameter replacement support, including grabbing named parameters from object properties.
• Great performance by eliminating Linq and fast property assignment with DynamicMethod generation.
• Includes T4 templates to automatically generate POCO classes for you.
• The query language is SQL... no weird fluent or Linq syntaxes (yes, matter of opinion)
• Includes a low friction SQL builder class that makes writing inline SQL much easier.
• Hooks for logging exceptions, installing value converters and mapping columns to properties without attributes.
• Works with SQL Server, SQL Server CE, MySQL, PostgreSQL and Oracle.
• Works under .NET 3.5 or Mono 2.6 and later.
• Experimental support for dynamic under .NET 4.0 and Mono 2.8
• NUnit unit tests.
• OpenSource (Apache License)
• All of this in about 1,500 lines of code

Continue Reading

PetaPoco with parameterised stored procedure and Asp.Net MVC

I have been playing with Micro ORMs as this is very interesting things that are happening in developer communities and I already liked the concept of it. It’s tiny easy to use and can do performance tweaks. PetaPoco is also one of them I have written few blog post about this. In this blog post I have explained How we can use the PetaPoco with stored procedure which are having parameters. I am going to use same Customer table which I have used in my previous posts. For those who have not read my previous post following is the link for that.

Get started with ASP.NET MVC and PetaPoco
PetaPoco with stored procedures

Now our customer table is ready. So let’s Create a simple process which will fetch a single customer via CustomerId. Following is a code for that.

CREATE PROCEDURE mysp_GetCustomer
 @CustomerId as INT
AS
SELECT * FROM [dbo].Customer where  CustomerId=@CustomerId

Now we are ready with our stored procedures. Now lets create code in CustomerDB class to retrieve single customer like following.

using System.Collections.Generic;

namespace CodeSimplified.Models
{
  public class CustomerDB
  {
      public IEnumerable<Customer> GetCustomers()
      {
          var databaseContext = new PetaPoco.Database("MyConnectionString");
          databaseContext.EnableAutoSelect = false;
          return databaseContext.Query<Customer>("exec mysp_GetCustomers");

      }
      public Customer GetCustomer(int customerId)
      {
          var databaseContext = new PetaPoco.Database("MyConnectionString");
          databaseContext.EnableAutoSelect = false;
          var customer= databaseContext.SingleOrDefault<Customer>("exec mysp_GetCustomer @customerId",new {customerId});
          return customer;
      }
  }
}

Here in above code you can see that I have created a new method call GetCustomer which is having customerId as parameter and then I have written to code to use stored procedure which we have created to fetch customer Information. Here I have set EnableAutoSelect=false because I don’t want to create Select statement automatically I want to use my stored procedure for that. Now Our Customer DB class is ready and now lets create a ActionResult Detail in our controller like following

using System.Web.Mvc;

namespace CodeSimplified.Controllers
{
  public class HomeController : Controller
  {
      public ActionResult Index()
      {
          ViewBag.Message = "Welcome to ASP.NET MVC!";

          return View();
      }

      public ActionResult About()
      {
          return View();
      }

      public ActionResult Customer()
      {
          var customerDb = new Models.CustomerDB();
          return View(customerDb.GetCustomers());
      }
      public ActionResult Details(int id)
      {
          var customerDb = new Models.CustomerDB();
          return View(customerDb.GetCustomer(id));
      }
  }
}

Now Let’s create view based on that ActionResult Details method like following.



Now everything is ready let’s test it in browser. So lets first goto customer list like following.



Now I am clicking on details for first customer and Let’s see how we can use the stored procedure with parameter to fetch the customer details and below is the output.



So that’s it. It’s very easy. Hope you liked it. Stay tuned for more..Happy Programming.

Continue Reading

PetaPoco with stored procedures

In previous post I have written that How we can use PetaPoco with the asp.net MVC. One of my dear friend Kirti asked me that How we can use it with Stored procedure. So decided to write a small post for that. So let’s first create a simple stored procedure for customer table which I have used in my previous post. I have written simple code a single query that will return customers. Following is a code for that.

CREATE PROCEDURE mysp_GetCustomers
AS
SELECT * FROM [dbo].Customer

Now our stored procedure is ready so I just need to change my CustomDB file from the my previous post example like following.

using System.Collections.Generic;

namespace CodeSimplified.Models
{
  public class CustomerDB
  {
      public IEnumerable<Customer> GetCustomers()
      {
          var databaseContext = new PetaPoco.Database("MyConnectionString");
          return databaseContext.Query<Customer>("exec mysp_GetCustomers");

      }
  }
}

That's It. Now It's time to run this in browser and Here is the output.



In future post I will explain How we can use PetaPoco with parameterised stored procedure. Hope you liked it.. Stay tuned for more.. Happy programming.

Continue Reading

Get started with ASP.NET MVC and PetaPoco

Micro ORM are having their pro and cons and right now its a buzz in the Microsoft.NET developers buzz. I personally like a Micro ORM Concept and I already posted some blog posts with asp.net mvc and dapper Micro ORM. Today I am going to explain how we can use PetaPoco in asp.net mvc application. First lets get little bit introduction about PetaPoco.

PetaPoco is developed by Top Ten software and It’s Micro ORM inspired by Masive and Dapper. Following is link where you can get more information about that.
http://www.toptensoftware.com/petapoco/.

To install PetaPoco in our application first we need to add a library reference as Its already available there. so Let’s add library reference like following.



Once click on add library reference a dialog box appears to find the the library package like following. I have written PetaPoco in search box and it will be appear in list like following.



Select first one then click Install and It will install the PetaPoco in your application. Once installation done you will have one t4 teamplte and one PetaPoco.cs file in your model section of ASP.NET application like following.



Now we are ready use the PetaPoco in our application. Now let’s use same DB which we have use for dapper demonstration let’s take same customer table like following.Following is a script to create table.

/****** Object:  Table [dbo].[Customer]    Script Date: 06/17/2011 02:27:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
   [CustomerId] [int] IDENTITY(1,1) NOT NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   [Address] [nvarchar](256) NULL,
   [City] [nvarchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
   [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now I have inserted some sample data in the table and now its ready to use. Now I have added a Model Entity class which contains same properties as Table columns. Just like following.

namespace CodeSimplified.Models
{
   public class Customer
   {
       public int CustomerId { get; set; }
       public string FirstName { get; set; }
       public string LastName { get; set; }
       public string Address { get; set; }
       public string City { get; set; }
   }
}

Now we are ready with our class I have create a new class called CustomerDB to fetch customer from the database. In that class I have created method called GetCustomers which will fetch all the customer from database. Here I have created a database object of PetaPoco and then I have using Its query method to query database. Following is a code for that.

using System.Collections.Generic;

namespace CodeSimplified.Models
{
   public class CustomerDB
   {
       public IEnumerable<Customer> GetCustomers()
       {
           var databaseContext = new PetaPoco.Database("MyConnectionString");
           return databaseContext.Query<Customer>("Select * from Customer");
       }
   }
}

Now as this is for demo purpose only lets create a method in Home Controller class to get customers like following.

using System.Web.Mvc;

namespace CodeSimplified.Controllers
{
   public class HomeController : Controller
   {
       public ActionResult Index()
       {
           ViewBag.Message = "Welcome to ASP.NET MVC!";

           return View();
       }

       public ActionResult About()
       {
           return View();
       }

       public ActionResult Customer()
       {
           var customerDb = new Models.CustomerDB();
           return View(customerDb.GetCustomers());
       }
   }
}

Now once done we with Customer Method in Home Controller let’s create view for that. Here I have created a strongly typed view like following for customers.



Now we are done with our view also So let’s run the project and let’s see output in browser. You can see output in browser as expected like following.



So it’s very easy to use PetaPoco with ASP.NET MVC. Hope you liked it. Stay tuned for more..

Continue Reading

Insert with Dapper Micro ORM and ASP.NET MVC 3

As I have already posted about the how to fetch data in my earlier post for Dapper ORM. In this post I am going to explain how we can insert data with the dapper ORM. So let’s extend the earlier post project. As explained in earlier post I have already created a class called CustomerDB this class will contains all the operation with Dapper Micro ORM. So For inserting data let’s first create CREATE method like following in CutomerDB Class like following. In that I have create a simple Insert Query in string and then using connection.execute method to execute method. Following is code for that.

public class CustomerDB
{
  public string Connectionstring = @"Data Source=DotNetJalps\SQLExpress;Initial Catalog=CodeBase;Integrated Security=True";

  public IEnumerable<Customer> GetCustomers()
  {
      using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
      {
          sqlConnection.Open();
          var customer = sqlConnection.Query<Customer>("Select * from Customer");
          return customer;

      }
  }

   
  public string  Create(Customer customerEntity)
  {
      try
      {
          using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
          {
              sqlConnection.Open();
           
              string sqlQuery = "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName,@LastName,@Address,@City)";
              sqlConnection.Execute(sqlQuery,
                                    new
                                        {
                                            customerEntity.FirstName,
                                            customerEntity.LastName,
                                            customerEntity.Address,
                                            customerEntity.City
                                        });

              sqlConnection.Execute(sqlQuery);
              sqlConnection.Close();

          }
          return "Created";
      }
      catch (Exception ex)
      {
          return ex.Message;
      }

  }

}

Now we are ready with Create Method for database now let’s create two ActionResult for the Creating customer like following in Customer Controller like following.

public ActionResult Create()
{
return View();
}

//
// POST: /Customer/Create

[HttpPost]
public ActionResult Create(Customer customer)
{
try
{
    // TODO: Add insert logic here
    var customerEntities = new CustomerDB();
    customerEntities.Create(customer);
    return RedirectToAction("Index");
}
catch
{
    return View();
}
}

Now we are ready with the both ActionResult. First ActionResult will return simple view of Create which we are going to create now and another ActionResult Create will get customer object from the form submitted and will call our create method of CustomerDB Class. Now it’s time to create a view for adding customer. Right Click return view Statement in Create Action Result and Click Add View and Just Create view like following.


That’s it now we are ready. Now let’s test it in browser. Like following.


Now let’s click and create and then it will redirect us to customer list page like following. Where you can see the newly added details.


So that’s it. Its very easy to Insert data with Dapper Micro ORM. Hope you like it…Stat tuned for more.

Note: For reference of this post you can also see my first post called -Playing with dapper Micro ORM and ASP.NET MVC 3.0.

Continue Reading

Playing with dapper Micro ORM and ASP.NET MVC 3.0

Some time ago Sam Saffron a lead developer from stackoverflow.com has made dapper micro ORM open source. This micro orm is specially developed for stackovewflow.com for keeping performance in mind. It’s very good single file which contains some cool functions which you can directly use in your browser. So I have decided to have a look into it. You can download dapper code from the following location it’s a single static class file called SQLMapper.

http://code.google.com/p/dapper-dot-net/

So once you download that file you can use that file into your project. So I have decided to create a sample application with asp.net mvc3. So I have created a simple asp.net mvc 3 project called DapperMVC. Now let’s first add that SQLMapper class file into our project at Model Folder like following.



Now let’s first create sample table from which we will fetch the data with the help of dapper file. I have created sample customer table with following script.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
 [CustomerId] [int] NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [Address] [nvarchar](256) NULL,
 [City] [nvarchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Once I have created table I have populated some test data like following.



Now we are ready with database table Now its time to add a customer entity class. So I have created sample customer class with properties same as Database columns like following.

public class Customer
{
 public int CustomerId { get; set; }
 public string FirstName { get; set; }
 public string LastName { get; set; }
 public string Address { get; set; }
 public string City { get; set; }
}

Now we are done with the Customer Entity class then I have created a new class called CustomerDB and a created a GetCustomer Method where I have used Query Method of Dapper to select all customers with ‘select * from customer’ simple query. I know it’s not a best practice to write ‘select * from customer’ but this is just for demo purpose so I have written like this. Query method accepts query as parameter and returns IEnumerable<T> where T is any valid class. In our case it will be Customer which we have just created before. Following is the code for CustomerDB Class.

using System.Collections.Generic;

public class CustomerDB
{
 public string Connectionstring=@"Data Source=DotNetJalps\SQLExpress;Initial Catalog=CodeBase;Integrated Security=True";

 public  IEnumerable<Customer> GetCustomers()
 {
     using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
     {
         sqlConnection.Open();
         var customer = sqlConnection.Query<Customer>("Select * from Customer");
         return customer;

     }
 }

}

Now we are ready with our model classes now It’s time to Create a Controller so I have created a Customer Controller like following.



It will create customer controller in controller folder with by default ActionResult Index. I have modified Action Result just like to following to return customerEntities with IndexView.

public class CustomerController : Controller
{
 //
 // GET: /Customer/

 public ActionResult Index()
 {
     var customerEntities = new CustomerDB();
     return View(customerEntities.GetCustomers());

 }

}

Now we are ready with our Customer Controller and now it’s time to create a view from the customer entities. So I have just right clicked customer entities and Create a View like following.



It will popup the following dialogbox where I have selected Razor View with Strongly Typed View. Also I have selected Customer Model class customer and selected list template like following.



That’s it now we are done with all the coding and It’s now time to run the project and result is as accepted as following.



That’s it. Isn’t that cool? Hope you liked this. Stay tuned for more.. Happy programming

Continue Reading