Sunday, June 5, 2022

Generic Repository return multiple tables from Stored Procedure in Dapper .NET

Database changes 

 ALTER PROCEDURE [dbo].[ProcedureName] 
 ( 
@param1 VARCHAR(500) 
AS 
BEGIN
 SELECT * FROM Table1 
 SELECT * FROM Table2 
 SELECT * FROM Table3 
END

Interface 
 GridReader GetMultiple(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure); 


 Class implements interface 

 public GridReader GetMultiple(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure) 
 { 
 IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring)); 
 return db.QueryMultiple(sp, parms, commandType: commandType); 
 } 


 Repository function 

 public ModelType functionName(string param1) 
 { 
 Modelwithmultiplelists result = new SpecificationColourList(); 
 try 
 { 
 DynamicParameters _spr = new DynamicParameters(); 
 _spr.Add("param1", param1, DbType.String); 
 var x = _dapper.GetMultiple("ProcedureName", _spr, CommandType.StoredProcedure); 
 var tab1 = x.Read().FirstOrDefault(); 
 var tab2 = x.Read().ToList(); 
 var tab3 = x.Read().ToList(); 
 result = tab1; 
 result.Table2 = tab2; 
 result.Table3 = tab3; 
 } 
 catch (Exception ex) { } 
 return result; 
 }

No comments:

Post a Comment