你的位置:首页 > ASP.net教程

[ASP.net教程]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;		}