PetaPoco with parameterised stored procedure and Asp.Net MVC
- by Jalpesh P. Vadgama
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
