C # calls the MySQL stored procedure with parameters

1. First create a stored procedure with parameters

① Stored Procedure name =proc_bookinfo

② Stored Procedure 2 parameters one in an out in Parameter name =ispay out parameter name =unpaycount ③This stored procedure outputs the number of items that have not been paid based on the incoming unpaid field name

DELIMITER $$    Use ' Bookshop ' $$    DROP PROCEDURE IF EXISTS ' proc_bookinfo ' $$    CREATE definer= ' @ ' PROCEDURE ' proc_bookinfo ' (In Ispay VARCHAR (ten), out Unpaycount INT)  BEGIN   SELECT COUNT (*) to Unpaycount from Tb_ BookInfo as a,tb_shopbook as b WHERE b.ispay=ispay;   end$$    

2.c# code calls the stored procedure

The ①sql statement only needs to write the name of the stored procedure

② the name and data type of the 2 parameters and the data length are exactly the same as the parameters of the stored procedure defined in the database (capitalization does not affect)

③ the direction attribute of 2 parameters to the corresponding type

④ call the Excuteproc method to execute, the output value is the second parameter passed in

StringBuilder sql=NewStringBuilder (); Sql. Append ("Proc_bookinfo"); mysqlparameter[] Parameters= {                     NewMysqlparameter ("@ispay", Mysqldbtype.varchar,Ten),                     NewMysqlparameter ("@unPaycount", Mysqldbtype.int32, One)                                           }; parameters[0]. Value ="not paid"; parameters[0]. Direction =ParameterDirection.Input; parameters[1]. Value =0 ; parameters[1]. Direction =ParameterDirection.Output; Dbhelpermysql.executeproc (convert.tostring (SQL), parameters);

3. How to perform database operations

Note: The CommandType property must be assigned a value of CommandType.StoredProcedure

     Public Static ObjectExecuteproc (stringSQLString,paramsmysqlparameter[] cmdparms) {                  using(Mysqlconnection connection =Newmysqlconnection (connectionString)) {                      using(Mysqlcommand cmd =NewMysqlcommand ()) {                          Try{preparecommand (cmd, connection, NULL, SQLString, cmdparms); Cmd.commandtype=CommandType.StoredProcedure; introws =cmd.                              ExecuteNonQuery (); Console.WriteLine ("The return value is"+cmdparms[1].                              Value); Cmd.                              Parameters.clear (); returnrows; }                          Catch(MySql.Data.MySqlClient.MySqlException e) {connection.                              Close (); Throwe; }                      }                  }              }  

C # calls the MySQL stored procedure with parameters