csharp: MySQL Stored Procedure using DAL

標籤：

# 建表 塗聚文 20160907drop 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));#預存程序# 添加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 ,rdes_reasnon ,branch)VALUES(param1emp_no ,param1rdate ,param1rtime ,param1rdescription ,param1rdes_reasnon ,param1branch); END $$DELIMITER ;-- 添加DELIMITER $$DROP PROCEDURE  IF EXISTS  proc_Insert_Attendrecord $$CREATE PROCEDURE 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 ,rdes_reasnon ,branch)VALUES(param1emp_no ,param1rdate ,param1rtime ,param1rdescription ,param1rdes_reasnon ,param1branch); END $$DELIMITER ;#添加DELIMITER $$DROP PROCEDURE IF EXISTS proc_Insert_AttendrecordOutput $$CREATE PROCEDURE proc_Insert_AttendrecordOutput(IN param1emp_no VarChar(20),IN param1rdate Datetime,IN param1rtime Time,IN param1rdescription VarChar(100),IN param1rdes_reasnon VarChar(100),IN param1branch VarChar(50), out param1seq int)BEGININSERT INTO attendrecord(emp_no ,rdate ,rtime ,rdescription ,rdes_reasnon ,branch)VALUES(param1emp_no ,param1rdate ,param1rtime ,param1rdescription ,param1rdes_reasnon ,param1branch);SELECT LAST_INSERT_ID() into param1seq;END $$DELIMITER ;

　　

/// <summary>/// Attendrecord資料訪問層///產生時間2016-9-6 17:24:08///塗聚文(Geovin Du) 自建代碼產生器產生（簡單預存程序也可以產生）///</summary>public class AttendrecordDAL : IAttendrecord{///<summary>/// 追加記錄 預存程序///</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), //塗聚文註：不能用MySqlDbType.Time否則報錯：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指令碼///</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;}

　　

csharp: MySQL Stored Procedure using DAL