# 建表 塗聚文 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('');
#视图有多少条记录 涂聚文 DELIMITER $$ DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCountView $$ CREATE PROCEDURE proc_Select_AttendrecordCountView ( IN wherestr varchar(1000) ) BEGIN declare sqlstr varchar(2000); set sqlstr='SELECT count(1) as H FROM View_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 ; #查询某记录的字段艺工作者Geovin Du DELIMITER $$ DROP PROCEDURE IF EXISTS proc_Select_AttendrecordTitle $$ CREATE PROCEDURE proc_Select_AttendrecordTitle ( IN FieldName varchar(1000), IN param1id int ) BEGIN declare sqlstr varchar(2000); declare wherestr varchar(1000); set sqlstr=CONCAT('select ',FieldName); set wherestr=CONCAT(' from attendrecord WHERE seq =',cast(param1id as char(20))); set sqlstr=CONCAT(sqlstr,wherestr); set @sqlstr=sqlstr; -- select @sqlstr; -- call(sqlstr); PREPARE stmt FROM @sqlstr; -- 5.1 up EXECUTE stmt; DEALLOCATE PREPARE stmt; END $$ DELIMITER ; call proc_Select_AttendrecordTitle('rdescription',1); select concat(2); select cast(2 as char(20)); #模糊查询 DELIMITER $$ DROP PROCEDURE IF EXISTS proc_Select_AttendrecordFuzzySearch $$ CREATE PROCEDURE proc_Select_AttendrecordFuzzySearch ( IN FieldList varchar(1000), IN wherestr varchar(2000) ) BEGIN declare sqlstr varchar(2000); declare ifwherestr varchar(1000); declare iflist varchar(1000); set ifwherestr=''; if FieldList='' then set iflist=' * '; else set iflist=FieldList; end if; if wherestr<>'' then set ifwherestr=CONCAT(' WHERE ',wherestr); end if; set sqlstr=CONCAT('select ',iflist); set sqlstr=CONCAT(sqlstr,' from attendrecord '); set sqlstr=CONCAT(sqlstr,ifwherestr); set @sqlstr=sqlstr; -- select @sqlstr; -- call(sqlstr); PREPARE stmt FROM @sqlstr; EXECUTE stmt; DEALLOCATE PREPARE stmt; END $$ DELIMITER ; -- seq =1 call proc_Select_AttendrecordFuzzySearch('rdescription','seq=1');