Dapper learning, dapper

Dapper learning, dapper

The previous article does not seem to introduce user-defined functions and stored procedures, because these two functions can also be implemented through queries.

1. Create and call custom functions (mysql)

Delimiter $$drop function if exists func_test;CREATE FUNCTION func_test (idIn INT) RETURNS intBEGINDECLARE res int DEFAULT 0;select count(1) into res from tch_teacher where id > idIn ;return res;END $$Delimiter ;

Note that in mysql, the use of delimiter $ starts a split function. In some compilers, if you do not write this, these functions are not treated as methods and stored procedures are processed, as a common query statement, an error is reported.

The following is the call method:

// Method 1: directly write the SQL statement and then call SQL = "select func_test (@ id);"; var res = conn. query <int> (SQL, new {id = 10 }). firstOrDefault (); // 90Console. writeLine ("Count =" + res); // Count = 90 // Method 2: directly use the Query method to pass in the function name, parameter, however, you must set CommondType to StoredProcedure // and the Return parameter is required when calling it. Otherwise, the error var para = new DynamicParameters (); para. add ("@ idIn", 20); para. add ("@ res", 0, DbType. int32, ParameterDirection. returnValue); var res1 = conn. query ("func_test", para, null, true, null, CommandType. storedProcedure ). firstOrDefault (); // 0Console. writeLine ("Query @ res =" + para. get <int> ("@ res"); // Query @ res = 80 // method 3: Execute can also be used, add a return parameter var param = new DynamicParameters (); param. add ("@ idIn", 25); param. add ("@ res", 0, DbType. int32, ParameterDirection. returnValue); var res2 = conn. execute ("func_test", param, null, null, CommandType. storedProcedure); // 0Console. writeLine ("Execute @ res =" + param. get <int> ("@ res"); // Execute @ res = 75

In general, I am used to method 1, which is more convenient, because the function does not return parameters here. In use, you do not need to define parameters in SQL, and then execute. method 1 is very convenient.

This method can also comply with some situations when calling the stored procedure. See the decomposition below.

 

Ii. Create and call stored procedures

Delimiter $$drop PROCEDURE if EXISTS pro_test;create PROCEDURE pro_test(in idIn int)beginselect count(1) as Count from tch_teacher where id > idIn;end $$Delimiter ;Delimiter $$drop PROCEDURE if EXISTS pro_test1;create PROCEDURE pro_test1(in idIn int, out count int)beginselect count(1) into count from tch_teacher where id > idIn;select * from tch_teacher where id > idIn;end $$Delimiter ;call pro_test(11);set @count =0;call pro_test1(11, @count);select @count;

 

Here I have created two stored procedures, one with return parameters and the other.

// Method 1

SQL = "call pro_test (@ id);"; var res = conn. query <int> (SQL, new {id = 15 }). firstOrDefault (); // 85Console. writeLine ("res =" + res); // res = 85 // method 2 var param = new DynamicParameters (); param. add ("@ idIn", 20); param. add ("@ count", 0, DbType. int32, ParameterDirection. output); var res2 = conn. query <Tch_Teacher> ("pro_test1", param, null, true, null, CommandType. storedProcedure); // res2.Count = 80Console. writeLine ("Query count =" + param. get <object> ("@ count"); // Query count = 80 // method 3 var res3 = conn. execute ("pro_test1", param, null, null, CommandType. storedProcedure); // 0Console. writeLine ("Execute count =" + param. get <object> ("@ count"); // Execute count = 80

If there are input parameters in the stored procedure, the method is not applicable. An error is returned when the. Dapper is parsed to "@ count.

Therefore, method 2 is recommended for stored procedures. Of course, method 1 is simpler and more crude when no output parameters are provided.