In Focus

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

In this article you will learn how to get 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.