csharp: MySQL Stored Procedure using DAL，csharpstored

csharp: MySQL Stored Procedure using DAL，csharpstored

# 建表 塗聚文 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)
);
#存儲過程
# 添加
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)
)
BEGIN
INSERT 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)
)
BEGIN
INSERT 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
)
BEGIN
INSERT 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;
		}

　　類似於SQL Server中的:sp_executesql

sql server script:

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

　　MySql script:

#表有多少條記錄 Geovin Du
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCount $$
CREATE PROCEDURE proc_Select_AttendrecordCount
(
	IN wherestr varchar(1000)
)
BEGIN
declare sqlstr varchar(2000);
set sqlstr='SELECT count(1) as H FROM attendrecord';
if wherestr='' then
set sqlstr=sqlstr;
else
set sqlstr=sqlstr+wherestr;
end if;
set @sqlstr=sqlstr;
-- call(sqlstr);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
# 測試
call proc_Select_AttendrecordCount('');