Dapper Study Notes (5)-stored procedures, dapper Study Notes

Dapper Study Notes (5)-stored procedures, dapper Study Notes

1. No parameter Stored Procedure

Step 1: Create a stored procedure without parameters. The Code is as follows:

1 CREATE PROCEDURE [dbo].[QueryRoleNoParms] 2     3 AS4 BEGIN5     SELECT * FROM T_Role;6 END

Step 2: Execute the stored procedure and return the result. The Code is as follows:

 1         private List<Role> ExecuteStoredProcedureNoParms() 2         { 3             using (IDbConnection con = OpenConnection()) 4             { 5                 List<Role> roleList = new List<Role>(); 6                 roleList = con.Query<Role>("QueryRoleNoParms", 7                                         null, 8                                         null, 9                                         true,10                                         null,11                                         CommandType.StoredProcedure).ToList();12                 return roleList;13             }14         }

 

2. Stored Procedures with Input and Output Parameters

Step 1: Create a stored procedure with input and output parameters. The Code is as follows:

1 CREATE PROCEDURE [dbo].[QueryRoleWithParms]2     @RoleId int,3     @RoleName nvarchar(256)='' out4 AS5 BEGIN6     SELECT @RoleName = RoleName FROM T_Role WHERE RoleId =@RoleId7 END

Step 2: Execute the stored procedure and return the execution result. The Code is as follows:

 1         private string ExecuteStoredProcedureWithParms() 2         { 3             DynamicParameters dp = new DynamicParameters(); 4             dp.Add("@RoleId", "1"); 5             dp.Add("@RoleName", "", DbType.String, ParameterDirection.Output); 6             using (IDbConnection con = OpenConnection()) 7             { 8                 con.Execute("QueryRoleWithParms", dp, null, null, CommandType.StoredProcedure); 9                 string roleName = dp.Get<string>("@RoleName");10                 return roleName;11             }12         }