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 

public class StudentStore
{
[Key]
public int STUDENTID { get; set; }
public string STUDENTName { get; set; }
public string ROLLNO { get; set; }
public string COURSE { get; set; }
}

public class Deparmentstore
{
[Key]
public int DeparmentID {get ;set;}
public string DeparmentName { get; set; }
}

The normal way of writing an inline query with parameters in dapper is: 

With Inline query


public string Insert(StudentStore objss)
{
string query = "INSERT INTO [dbo].[Student]
([STUDENT],
[ROLLNO],
[COURSE])
VALUES
(@STUDENT ,
@ROLLNO ,
@COURSE)";
Con.Open();
Con.Execute(query, new { objss.STUDENTName, objss.ROLLNO, objss.COURSE });
Con.Close();
return "Inserted";
}

With Stored Procedures

Here is a snapshot of the Execute Method:

Method for Insert 


public string Insertstudent(StudentStore objss)
{
var para = new DynamicParameters();

para.Add("@STUDENTName", objss.STUDENTName); // Normal Parameters
para.Add("@ROLLNO", objss.ROLLNO);
para.Add("@COURSE", objss.COURSE);
para.Add("@DeparmentID", "1");

para.Add("@Myout", dbType: DbType.Int32, direction: ParameterDirection.Output);
// Getting Out Parameter

para.Add("@Ret", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
// Getting Return value

Con.Open(); // opening connection

Con.Execute("Usp_getallstudents", para, commandType: CommandType.StoredProcedure);
//Executing Command
// mapping this StoredProcedure with Database one.

int Valueout = para.Get<int>("@Myout"); //Getting Out Value

int Valuereturn = para.Get<int>("@Ret"); //Getting Out Return

Con.Close(); // Closing connection

return "Inserted";
}

Stored Procedures for Insert


CREATE PROCEDURE Usp_getallstudents @STUDENTName VARCHAR(64)
,@ROLLNO VARCHAR(16)
,@COURSE VARCHAR(32)
,@DeparmentID INT
,@Myout INT OUTPUT
AS
BEGIN
INSERT INTO [dbo].[Student] (
[STUDENTName]
,[ROLLNO]
,[COURSE]
,[DeparmentID]
)
VALUES (
@STUDENTName
,@ROLLNO
,@COURSE
,@DeparmentID
)

SET @Myout = @@IDENTITY
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
  

  public void get_all_Records ()
  {
  var sql = "Usp_GetRec"; // Stored Procedure Name
  
  using (var multi = Con.QueryMultiple(sql))
  {
  var customer = multi.Read<StudentStore>().ToList();
  
  var orders = multi.Read<Deparmentstore>().ToList();
  }
  }

• Update Stored Procedure for gemultipalRecords
  

  ALTER PROCEDURE Usp_GetRec
  AS
  BEGIN
  SELECT *
  FROM [Student]
  
  SELECT *
  FROM Department
  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:

ALTER PROCEDURE Usp_GetRec
AS
BEGIN
SELECT *
FROM [Student]

SELECT *
FROM Department
END

How to get Records from Stored Procedures

var employees = Con.Query<StudentStore>("Usp_getstudent", commandType: CommandType.StoredProcedure);

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