Friday, June 24, 2011

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.

AddView

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

CustomerList

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.

CustomerDetails

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

Shout it

kick it on DotNetKicks.com
Share:

6 comments:

  1. Hey nice blog and very well wrote. Thanks for sharing such good stuff with us. Our site also having men formal collection and various information. Please take a look. Hope you like it.asp.net training
    check it out.

    ReplyDelete
  2. You shouldn't have to do this:

    databaseContext.EnableAutoSelect = false;

    because it will detect that you are using exec and turn it off automatically.

    ReplyDelete
  3. Thank you very much Elan. I have been search it from morning...

    ReplyDelete

Your feedback is very important to me. Please provide your feedback via putting comments.

About Me
My Photo

Full Stack Developer,Blogger,Microsoft Mvp, Mentor, Life long learner, 10+ Years of Experience for JavaScript, web and Microsoft technologies. ASP.NET MVC,Web Forms,C#,AngularJs,Golang
Mvp profile
Subscribe to my blog

Enter your email address:

Follow us on facebook
Blog Archive
Total Pageviews