csharp: MySQL Stored Procedure using DAL

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
# 建表 塗聚文 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 ;

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
/// <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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#表有多少条记录 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('');

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
#视图有多少条记录 涂聚文
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');

　　

posted @   ®Geovin Du Dream Park™  阅读(350)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2012-09-07 csharp: word or excel Convert to PDF
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示