MENU

How To Pass Stored Procedure Using Dapper In MVC

Filed Under: , on
Many forum post I have read people asking question how to pass stored procedure Using Dapper.NET ORM in MVC. many answers were good but much complicated. So I have decided to make it very simple .Please read my previous article  to understand the basics about MVC:
Before going to read this article I hope you are aware about Dapper.NET ORM , Let us look into the following example .
Suppose you have following Model class
public class EmpModel
    {
        [Display(Name = "Id")]
        public int Empid { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }

        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }

    }
Now consider following stored procedure we have
Create procedure [dbo].[AddNewEmpDetails]  
(  @Id int=null),
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End
Now following is the function which is used to pass above stored procedure to Dapper.NET

public void AddEmployee(EmpModel objEmp)
        {
            //Passing stored procedure using Dapper.NET
                connection();
                con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure);
               
        }
 In the above function
  •  Connection() is the method which contains the connection string .
  •  Con is the SqlConnection class object.
  • AddNewEmpDetails is the stored procedure.
  • ObjEmp is the object of model class
The above code works fine ,there is no need to do extra thing ,but remember following things.

Notes
  • The above function will throw too much parameters than stored procedure because it must have to pass all parameters which are  in model class . 
  • To avoid parameter exception error you must set null to the stored procedure parameter which is not passing model class parameter to stored procedure.
  • If you are using above technique you must have same parameters in stored procedure as in model class.
  • If you can not be wants to pass any model class parameter to the stored procedure then set null that parameter which I have set null to the Id parameter in the above stored procedure .
  • You can use same function ,if you are passing list , nothing need to change .
Summary
I hope this article is useful for all readers. If you have any suggestion then please contact me.

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode