Csharp: MySQL Stored Procedure using DAL, csharpstored

Csharp: MySQL Stored Procedure using DAL, csharpstored

# Create a table: 20160907 drop table attendrecord; create table attendrecord (seq int not null primary key AUTO_INCREMENT, emp_no varchar (20) null, rdate datetime not null, rtime time not null, rdescription varchar (100), rdes_reasnon varchar (100), branch varchar (50); # Stored PROCEDURE # Add DELIMITER $ drop procedure if exists 'attend '. 'proc _ Insert_Attendrecord '$ create procedure 'attend '. 'proc _ Insert_Attendrecord '(IN param1emp_no VarChar (20), IN param1rdate Datetime, IN param1rtime Time, IN param1rdescription VarChar (100), IN param1rdes_reasnon VarChar (100 ), IN param1branch VarChar (50) begininsert into attendrecord (emp_no, rdate, rtime, rdescription, region, branch) VALUES (VALUES, param1rdate, param1rtime, param1rdescription, region, param1branch ); END $ DELIMITER; -- add DELIMITER $ drop procedure if exists proc_Insert_Attendrecord $ create procedure partition (IN param1emp_no VarChar (20), IN param1rdate Datetime, IN param1rtime Time, IN param1rdescription VarChar (100), IN bytes VarChar (100), IN param1branch VarChar (50) begininsert into attendrecord (emp_no, rdate, rtime, rdescription, rdes_reasnon, branch) VALUES (param1emp_no, param1rdate, param1rtime, param1rdescription, param1rdes_reasnon, param1branch); END $ DELIMITER; # Add DELIMITER $ drop procedure if exists success $ create procedure success (IN param1emp_no VarChar (20), IN param1rdate Datetime, IN param1rtime Time, IN param1rdescription VarChar (100 ), IN bytes VarChar (100), IN param1branch VarChar (50), out param1seq int) begininsert into attendrecord (emp_no, rdate, rtime, rdescription, region, branch) VALUES (param1emp_no, param1rdate, param1rtime, param1rdescription, param1rdes_reasnon, param1branch); SELECT LAST_INSERT_ID () into param1seq; END $ DELIMITER;

　　

/// <Summary> /// Attendrecord data access layer // generation time 17:24:08 // Geovin Du) self-built code generator generation (simple stored procedure can also be generated) /// </summary> public class AttendrecordDAL: IAttendrecord {// <summary> // append record Stored Procedure /// </summary> /// <param name = "AttendrecordInfo"> </param> /// <returns> </returns> public int InsertAttendrecord (AttendrecordInfo attendrecord) {int ret = 0; try {MySqlParameter [] par = new MySqlParameter [] {new MySqlParameter ("? Param1emp_no ", MySqlDbType. VarChar, 20), new MySqlParameter ("? Param1rdate ", MySqlDbType. Datetime), new MySqlParameter ("? Param1rtime ", MySqlDbType. string), // tu juwen Note: MySqlDbType cannot be used. time otherwise, the following error occurs: base {System. data. common. dbException }={ "Only TimeSpan objects can be serialized by MySqlTimeSpan"} new MySqlParameter ("? Param1rdescription ", MySqlDbType. VarChar, 100), new MySqlParameter ("? Param1rdes_reasnon ", MySqlDbType. VarChar, 100), new MySqlParameter ("? Param1branch ", MySqlDbType. varChar, 50),}; par [0]. value = attendrecord. emp_no; par [1]. value = attendrecord. rdate; par [2]. value = attendrecord. rtime; par [3]. value = attendrecord. rdescription; par [4]. value = attendrecord. rdes_reasnon; par [5]. value = attendrecord. branch; ret = DBHelper. executeSql ("proc_Insert_Attendrecord", CommandType. storedProcedure, par);} catch (MySqlException ex) {throw ex;} return ret ;}/// <Summary> /// SQL script for append record /// </summary> /// <param name = "AttendrecordInfo"> </param> /// <returns> </ returns> public int InsertSqlAttendrecord (AttendrecordInfo attendrecord) {int ret = 0; try {StringBuilder strSql = new StringBuilder (); strSql. append ("insert into attendrecord (emp_no, rdate, rtime, rdescription, rdes_reasnon, branch"); strSql. append (") VALUES ("); strSql. append ("? Param1emp_no ,? Param1rdate ,? Param1rtime ,? Param1rdescription ,? Param1rdes_reasnon ,? Param1branch) "); MySqlParameter [] par = new MySqlParameter [] {new MySqlParameter ("? Param1emp_no ", MySqlDbType. VarChar, 20), new MySqlParameter ("? Param1rdate ", MySqlDbType. Datetime), new MySqlParameter ("? Param1rtime ", MySqlDbType. String), new MySqlParameter ("? Param1rdescription ", MySqlDbType. VarChar, 100), new MySqlParameter ("? Param1rdes_reasnon ", MySqlDbType. VarChar, 100), new MySqlParameter ("? Param1branch ", MySqlDbType. varChar, 50),}; par [0]. value = attendrecord. emp_no; par [1]. value = attendrecord. rdate; par [2]. value = attendrecord. rtime; par [3]. value = attendrecord. rdescription; par [4]. value = attendrecord. rdes_reasnon; par [5]. value = attendrecord. branch; ret = DBHelper. executeSql (strSql. toString (), CommandType. text, par);} catch (MySqlException ex) {throw ex;} return ret ;}

Similar to: sp_executesql in SQL Server

SQL server script:

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount')DROP PROCEDURE proc_Select_DuDeptUserCountGOCREATE PROCEDURE proc_Select_DuDeptUserCount( @where NVARCHAR(1000))ASDECLARE @sql NVARCHAR(4000)SET @sql='select count(*) as H from DuDeptUser 'IF @where<>'' SET @sql=@sql+@whereEXEC(@sql)GO

MySql script:

# Number of Geovin DuDELIMITER records IN the table $ drop procedure if exists proc_Select_AttendrecordCount $ create procedure tables (IN wherestr varchar (1000) BEGINdeclare sqlstr varchar (2000 ); set sqlstr = 'select count (1) as h from attendrecord '; if wherestr = ''thenset sqlstr = sqlstr; elseset sqlstr = sqlstr + wherestr; end if; set @ sqlstr = sqlstr; -- call (sqlstr); PREPARE stmt FROM @ sqlstr; EXECUTE stmt; deallocate prepare stmt; END $ DELIMITER; # Test call proc_Select_AttendrecordCount ('');