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

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:

Method for Insert

1. public string Insertstudent(StudentStore objss)
2. {
3. var para = new DynamicParameters();
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");
10. para.Add("@Myout", dbType: DbType.Int32, direction: ParameterDirection.Output);
11. // Getting Out Parameter
13. para.Add("@Ret", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
14. // Getting Return value
16. Con.Open(); // opening connection
18. Con.Execute("Usp_getallstudents", para, commandType: CommandType.StoredProcedure);
19. //Executing Command
20. // mapping this StoredProcedure with Database one.
22. int Valueout = para.Get<int>("@Myout"); //Getting Out Value
24. int Valuereturn = para.Get<int>("@Ret"); //Getting Out Return
26. Con.Close(); // Closing connection
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. )
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
  5. using (var multi = Con.QueryMultiple(sql))
  6. {
  7. var customer = multi.Read<StudentStore>().ToList();
  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]
  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:

How to get Records from Stored Procedures
Here is a simple way to get a value return from a Stored Procedure.