Mapping Stored Procedures and Getting Multiple Records From Stored Procedures Using Dapper

Here In this document I will explain the following topics:

  • Mapping Stored Procedure
  • How to Map multiple records from a single Stored Procedure
  • How to get records from Stored Procedures

Let's start.

Mapping Stored Procedures

I am taking 2 classes to show how to map these things.

  • Studentstore
  • Deparmentstore 
  1. public class StudentStore  
  2. {  
  3. [Key]  
  4. public int STUDENTID { getset; }  
  5. public string STUDENTName { getset; }  
  6. public string ROLLNO { getset; }  
  7. public string COURSE { getset; }  
  8. }  
  9.   
  10. public class Deparmentstore  
  11. {  
  12. [Key]  
  13. public int DeparmentID {get ;set;}  
  14. public string DeparmentName { getset; }  
  15. }  
The normal way of writing an inline query with parameters in dapper is: 
    With Inline query
    1. public string Insert(StudentStore objss)  
    2. {  
    3. string query = "INSERT INTO [dbo].[Student]   
    4. ([STUDENT],  
    5. [ROLLNO],  
    6. [COURSE])   
    7. VALUES   
    8. (@STUDENT ,  
    9. @ROLLNO ,  
    10. @COURSE)";  
    11. Con.Open();  
    12. Con.Execute(query, new { objss.STUDENTName, objss.ROLLNO, objss.COURSE });  
    13. Con.Close();  
    14. return "Inserted";  
    15. }  
    With Stored Procedures

    Here is a snapshot of the Execute Method:
    /Snapshot of Execute Method
    Method for Insert 
    1. public string Insertstudent(StudentStore objss)  
    2. {  
    3. var para = new DynamicParameters();  
    4.   
    5. para.Add("@STUDENTName", objss.STUDENTName); // Normal Parameters  
    6. para.Add("@ROLLNO", objss.ROLLNO);  
    7. para.Add("@COURSE", objss.COURSE);  
    8. para.Add("@DeparmentID""1");  
    9.   
    10. para.Add("@Myout", dbType: DbType.Int32, direction: ParameterDirection.Output);   
    11. // Getting Out Parameter  
    12.   
    13. para.Add("@Ret", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);   
    14. // Getting Return value  
    15.   
    16. Con.Open(); // opening connection  
    17.   
    18. Con.Execute("Usp_getallstudents", para, commandType: CommandType.StoredProcedure);   
    19. //Executing Command   
    20. // mapping this StoredProcedure with Database one.  
    21.   
    22. int Valueout = para.Get<int>("@Myout"); //Getting Out Value  
    23.   
    24. int Valuereturn = para.Get<int>("@Ret"); //Getting Out Return  
    25.   
    26. Con.Close(); // Closing connection  
    27.   
    28. return "Inserted";  
    29. }  
    Stored Procedures for Insert
    1. CREATE PROCEDURE Usp_getallstudents @STUDENTName VARCHAR(64)  
    2. ,@ROLLNO VARCHAR(16)  
    3. ,@COURSE VARCHAR(32)  
    4. ,@DeparmentID INT  
    5. ,@Myout INT OUTPUT  
    6. AS  
    7. BEGIN  
    8. INSERT INTO [dbo].[Student] (  
    9. [STUDENTName]  
    10. ,[ROLLNO]  
    11. ,[COURSE]  
    12. ,[DeparmentID]  
    13. )  
    14. VALUES (  
    15. @STUDENTName  
    16. ,@ROLLNO  
    17. ,@COURSE  
    18. ,@DeparmentID  
    19. )  
    20.   
    21. SET @Myout = @@IDENTITY  
    22. END  

Here in the Insertstudent method above I created DynamicParameters and assiged values to the parameters after with showing how to get parameterdirection.output and return a value from it. It's simple; if go throug this code then you will get it in one shot.

How to Map multiple records from a single Stored Procedure

QueryMultiple : That is defined for compound SQL statements that return multiple result sets.

  • Stored Procedure for gemultipalRecords
    1. public void get_all_Records ()  
    2. {  
    3. var sql = "Usp_GetRec"// Stored Procedure Name  
    4.   
    5. using (var multi = Con.QueryMultiple(sql))  
    6. {  
    7. var customer = multi.Read<StudentStore>().ToList();  
    8.   
    9. var orders = multi.Read<Deparmentstore>().ToList();  
    10. }  
    11. }  
  • Update Stored Procedure for gemultipalRecords
    1. ALTER PROCEDURE Usp_GetRec  
    2. AS  
    3. BEGIN  
    4. SELECT *  
    5. FROM [Student]  
    6.   
    7. SELECT *  
    8. FROM Department  
    9. END  

Here in Multiple Records reading I have created Stored Procedures that return 2 tables and that use Multi.Read.

We can get all the records of a student in a studentStore class and Department in Deparmentstore .

On the other hand, you are trying to get two different columns from a single result set, so you should just use the normal Query method as in the following:

  1. ALTER PROCEDURE Usp_GetRec  
  2. AS  
  3. BEGIN  
  4. SELECT *  
  5. FROM [Student]  
  6.   
  7. SELECT *  
  8. FROM Department  
  9. END  
How to get Records from Stored Procedures
  1. var employees = Con.Query<StudentStore>("Usp_getstudent", commandType: CommandType.StoredProcedure);  
Here is a simple way to get a value return from a Stored Procedure.