How to Pass Parameterised stored procedure and Asp.Net MVC using PetaPoco

No.of Views4153
Bookmarked0 times
Downloads 
Votes0
By  jalpesh   On  07 Jul 2011 09:07:02
Tag : ASP.NET , ASP.NET MVC
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.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

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

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.

 

Image Loading

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

Image Loading

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.

Image Loading

So that’s it. It’s very easy. Hope you liked it.

 
Sign Up to vote for this article
 
About Author
 
jalpesh
Occupation-Software Engineer
Company-DotNetJaps
Member Type-Gold
Location-India
Joined date-08 May 2010
Home Page-http://www.dotnetjalps.com
Blog Page-http://www.dotnetjalps.com
I am jalpesh vadgamaa an Microsoft MVP for Visual C# and BrainBench Certified ASP.NET Developer having experience of five year in Microsoft .NET Technology.I am working as Project Leader in Mid Size company.My work area comprises of Enterprise Level projects using ASP.NET and other Microsoft .NET Technologies.Please feel free to contact me for any queries via posting comments on my blog I will try to reply as early as possible.
 
 
Other popularSectionarticles
Comments
By:4retewrtDate Of Posted:5/23/2014 3:19:37 AM
gfgsdf
?dfasdf
By:vinayDate Of Posted:4/13/2013 2:35:07 AM
How to Use StoredProcedure With Select Query in MVC3 Application
Hi this is really nice article . Please check the below article http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=318&title=How-to-Use-StoredProcedure-With-Select-SQL-Query-in-MVC3-Application
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top