MySQL5.7: sql script demo

 

?
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
-- MyISAM Foreign Keys显示不了外键，MyISAM此为5.0 以下版本使用 InnoDB 为5.0以上版本使用
drop table IF EXISTS city;
CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT comment'',
`Name` char(35) NOT NULL DEFAULT '' comment'',
`CountryCode` char(3) NOT NULL DEFAULT '' comment'',
`District` char(20) NOT NULL DEFAULT '' comment'',
`Population` int(11) NOT NULL DEFAULT '0' comment'',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=MyISAM COMMENT='城市表' AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8;
 
drop table IF EXISTS country;
CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '' comment'',
`Name` char(52) NOT NULL DEFAULT '' comment'',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' comment'',
`Region` char(26) NOT NULL DEFAULT '' comment'',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00' comment'',
`IndepYear` smallint(6) DEFAULT NULL comment'',
`Population` int(11) NOT NULL DEFAULT '0' comment'',
`LifeExpectancy` float(3,1) DEFAULT NULL comment'',
`GNP` float(10,2) DEFAULT NULL comment'',
`GNPOld` float(10,2) DEFAULT NULL comment'',
`LocalName` char(45) NOT NULL DEFAULT '' comment'',
`GovernmentForm` char(45) NOT NULL DEFAULT '' comment'',
`HeadOfState` char(60) DEFAULT NULL comment'',
`Capital` int(11) DEFAULT NULL comment'',
`Code2` char(2) NOT NULL DEFAULT '' comment'',
PRIMARY KEY (`Code`)
) ENGINE=MyISAM COMMENT='国家表' DEFAULT CHARSET=utf8;

　

?
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
select *
 from information_schema.TABLES where TABLE_SCHEMA='geovindu';
  
   
   
  -- 主键  Geovin Du 涂聚文
 select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL'
 from information_schema.columns where  table_schema='geovindu' and column_key='PRI';
  
 -- 主键 ，有注释
  select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',a.COLUMN_COMMENT,b.TABLE_COMMENT
 from information_schema.columns as a,information_schema.TABLES as b
 where  a.table_schema='geovindu' and b.table_schema='geovindu' and column_key='PRI'
 and a.table_name=b.table_name;
   
   
  -- 外键
  select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL'
 from information_schema.columns where  table_schema='geovindu' and column_key='MUL'; 
  
  SELECT * FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_SCHEMA = 'geovindu'  AND REFERENCED_TABLE_NAME IS NOT NULL;
  
  
 select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',  b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME
 from information_schema.columns as a,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b  where a.TABLE_NAME=b.TABLE_NAME and a.table_schema='geovindu' and b.table_schema='geovindu' and a.column_key='MUL'
  AND b.REFERENCED_TABLE_NAME IS NOT NULL;
   
  -- 自表外键  列有注释
select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',a.COLUMN_COMMENT,b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME
 from information_schema.columns as a,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b  where a.table_name=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.table_schema='geovindu' and b.table_schema='geovindu'
 and a.column_key='MUL'
AND b.REFERENCED_TABLE_NAME IS NOT NULL and a.table_name='productorderdetails';
   
 -- 主表的主键作的外键表 列有注释
select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',a.COLUMN_COMMENT, b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME
from information_schema.columns as a,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b  where a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.table_schema='geovindu' and b.table_schema='geovindu' and a.column_key='MUL'
AND b.REFERENCED_TABLE_NAME IS NOT NULL and b.REFERENCED_TABLE_NAME='unitlist';
   
  
 -- 表
  select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL'
 from information_schema.columns where  table_schema='geovindu' and column_key='PRI' and table_name=('orderdetails');
  
 --  表
 select column_name as 'FieldName',data_type as 'FieldType',ifnull(character_maximum_length,8) as 'FieldLength' from information_schema.columns where table_schema='geovindu' and table_name=('orderdetails');
  
  
   select *
 from information_schema.columns where  table_schema='geovindu' and column_key='PRI' and table_name=('orderdetails');
  
  -- UNI
  select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL'
 from information_schema.columns where  table_schema='geovindu' and column_key='UNI'; 

　　

　

 

?
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
-- 查询外键
select
    concat(table_name, '.', column_name) as 'foreign key', 
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;
 
-- 查询外键   
select
    concat(table_name, '.', column_name) as 'foreign key', 
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'geovindu';
     
--  table_name  查询表和视图
SELECT * FROM information_schema.tables
    WHERE table_schema = 'geovindu';
-- 表   
SELECT * FROM information_schema.tables
    WHERE table_schema = 'geovindu' and table_type='base table';
 
-- 视图
SELECT * FROM information_schema.tables
    WHERE table_schema = 'geovindu' and table_type='VIEW';
 -- 列  
 SELECT * FROM information_schema.COLUMNS;
  
 -- 主外键
 SELECT * FROM information_schema.KEY_COLUMN_USAGE;
  
 SELECT * FROM information_schema.PARAMETERS;
  
 -- 存储过程，自定义函数
  SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu';
-- 'PROCEDURE'
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu' and routine_type='PROCEDURE';
 
--  'FUNCTION'
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu'  and routine_type='FUNCTION';
 
   
SELECT * FROM information_schema.PROCESSLIST;
   
 --
 SELECT * FROM information_schema.SCHEMATA;
  
 -- 表，视图
 SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'geovindu';
   
     
     
-- 主键
select * from information_schema.KEY_COLUMN_USAGE;
 
-- https://dev.mysql.com/doc/refman/8.0/en/keywords-table.html
select * from information_schema.KEYWORDS;
 
SELECT * FROM INFORMATION_SCHEMA.KEYWORDS;
 
select
    concat(table_name, '.', column_name) as 'foreign key', 
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;
     
select `column_name`, `column_type`, `column_default`, `column_comment`
from `information_schema`.`COLUMNS`
where `table_name` = 'customerlist'
and `table_schema` = 'geovindu';
 
select *
from `information_schema`.`COLUMNS`
where `table_name` = 'customerlist'
and `table_schema` = 'geovindu';  
     
select *
from `information_schema`.`COLUMNS`
where `table_schema` = 'geovindu';  
-- column_key  PRI,MUL，UNI  pri 主键，mul 外键
-- EXTRA  auto increment 自动增长
-- DATA_TYPE 数据类型
 
-- 外键表与主表关系
SELECT
  `TABLE_SCHEMA`,                          -- Foreign key schema
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`,                           -- Foreign key column
  `REFERENCED_TABLE_SCHEMA`,               -- Origin key schema
  `REFERENCED_TABLE_NAME`,                 -- Origin key table
  `REFERENCED_COLUMN_NAME`                 -- Origin key column
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege
WHERE
  `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE
  AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys
  
 -- 
 SELECT
*
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege
WHERE
  `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE
  AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys
   
   
--  
SELECT
    count(1) totalrelationships ,
    c.table_name tablename,
    CONCAT(' ',GROUP_CONCAT(c.column_name ORDER BY ordinal_position SEPARATOR ', ')) columnname,
    CONCAT(' ',GROUP_CONCAT(c.column_type ORDER BY ordinal_position SEPARATOR ', ')) columntype   
FROM
    information_schema.columns c RIGHT JOIN
    (SELECT column_name , column_type FROM information_schema.columns WHERE
    -- column_key in ('PRI','MUL') AND  -- uncomment this line if you want to see relations only with indexes
    table_schema = DATABASE() AND table_name = 'productitorderdetails') AS p
    USING (column_name,column_type)
WHERE
    c.table_schema = DATABASE()
    -- AND c.table_name != 'YourTableName'
    GROUP BY tablename
    -- HAVING (locate(' YourColumnName',columnname) > 0) -- uncomment this line to search for specific column
    ORDER BY totalrelationships desc, columnname
;
 
--
SELECT i.TABLE_SCHEMA, i.TABLE_NAME,
       i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME,
       k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
  FROM information_schema.TABLE_CONSTRAINTS i
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k
       ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
 WHERE i.TABLE_SCHEMA = 'productitorderdetails' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY'
 ORDER BY i.TABLE_NAME;
  
 -- 
 select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'geovindu';
     
 SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'geovindu'
AND REFERENCED_TABLE_NAME = 'productitorderdetails';
 
SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
and i.table_schema = 'geovindu';
  
 SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND i.TABLE_SCHEMA = DATABASE()
AND i.TABLE_NAME = 'productitorderdetails';
  
  
 SELECT *
FROM information_schema.REFERENTIAL_CONSTRAINTS;
 
 
SELECT
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'geovindu' AND
  REFERENCED_TABLE_NAME = 'productitorderdetails';
   
  SELECT
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'geovindu' AND
  TABLE_NAME = 'productitorderdetails';

　　

 

?
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
/*
CREATE DATABASE geovindu
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
 
use geovindu;
 
*/
-- 查詢編碼格式
show variables like '%char%';
 
-- SET NAMES GB2312;
 
ALTER DATABASE  geovindu
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
 
-- 修改數据的編碼格式
ALTER DATABASE geovindu
    DEFAULT CHARACTER SET utf8
   -- default character set=utf8
   -- default character set server=utf8
   -- default collation server=utf8_general_ci
    DEFAULT COLLATE utf8_general_ci;
     
 -- 設置  
SET character_set_server = utf8;
 
set character_set_results=gb2312;
 
SET character_set_database = latin1;
 
select 'host' from user; -- where user='root';
 
/*
szcentilc.com
 
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
 
 
20180709
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
 
*/
 /*
 原始的
 character_set_client   utf8
character_set_connection    utf8
character_set_database  latin1  -- phpmyadmin2 客戶查詢亂碼
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
 
mysql> SET character_set_client = utf8 ;
mysql> SET character_set_connection = utf8 ;
mysql> SET character_set_database = utf8 ;
mysql> SET character_set_results = utf8 ;
mysql> SET character_set_server = utf8 ;
  
mysql> SET collation_connection = utf8 ;
mysql> SET collation_database = utf8 ;
mysql> SET collation_server = utf8 ;
 */ 
  
SHOW DATABASES;
SHOW DATABASES; -- 列出 MySQL Server 上的資料庫。
SHOW TABLES FROM test; -- 列出資料庫的資料表。
SHOW TABLE STATUS FROM test; -- 列出資料庫的資料表，提供比較詳細的訊息。
SHOW COLUMNS FROM test; -- 列出資料表的欄位，同 SHOW FIELDS FROM tbl_name [FROM db_name]，DESCRIBE tbl_name [col_name]。
SHOW FULL COLUMNS FROM test; -- 列出資料表的欄位，提供比較詳細的訊息，同 SHOW FULL FIELDS FROM tbl_name [FROM db_name]。
SHOW INDEX FROM test; -- 列出資料表的索引訊息。
SHOW STATUS; -- 列出 Server 的狀態訊息。
SHOW VARIABLES; -- 列出 MySQL 系統變數的值。
SHOW PROCESSLIST; -- 顯示哪個執行緒正在運行。
SHOW GRANTS FOR user; -- 列出對一個用戶必須發出以重複授權的授權命令
 
-- 主键
select * from information_schema.KEY_COLUMN_USAGE;
 
-- https://dev.mysql.com/doc/refman/8.0/en/keywords-table.html
select * from information_schema.KEYWORDS;
 
SELECT * FROM INFORMATION_SCHEMA.KEYWORDS;
 
select
    concat(table_name, '.', column_name) as 'foreign key', 
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;
 
 
drop table  IF EXISTS  BookKindList;
create table BookKindList
(
    BookKindID INT NOT NULL AUTO_INCREMENT  comment '自動增長ID',
    BookKindName nvarchar(500) not null comment '书类名',
    BookKindParent int null comment '父节点',
   PRIMARY KEY(BookKindID)  #主键
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='书类表' AUTO_INCREMENT=1;
 
 
#更新
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Update_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Update_BookKindList` (IN param1ID Int,IN param1Name NVarChar(1000),IN param1Parent Int)
BEGIN
IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then
UPDATE BookKindList
    SET
        BookKindName=param1Name ,
        BookKindParent=param1Parent
    where
        BookKindID=param1ID;
ELSE
    UPDATE BookKindList
    SET BookKindParent=param1Parent
    where
        BookKindID=param1ID;
END IF;
END $$
 
#IN 表示输入参数
#OUT表示输出参数
#INOUT:表示即可以输入参数也可以输出参数
#存储过程 利用mysql-query-browser创建存储过程和函数
 
#删除
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`DeleteBookKind` $$
CREATE PROCEDURE `geovindu`.`DeleteBookKind` (IN param1 INT)
BEGIN
         Delete From bookkindlist WHERE BookKindID  = param1;
END $$
DELIMITER ;
 
#查询所有
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindListAll` $$
CREATE PROCEDURE `geovindu`.`proc_Select_BookKindListAll` ()
BEGIN
    SELECT * FROM bookkindlist;
END $$
DELIMITER ;
 
 
select * from  `geovindu`.`bookkindlist`;
SELECT * FROM bookkindlist;
 
#统计
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`BookKindCount` $$
CREATE PROCEDURE `geovindu`.`BookKindCount` (OUT param1ID INT)
BEGIN
        select COUNT(*) into param1ID  From bookkindlist;
END $$
DELIMITER ;
#查询一条
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Select_BookKindList` (IN param1 INT)
BEGIN
        SELECT * FROM BookKindList WHERE BookKindID = param1;
END $$
DELIMITER ;
 
#插入一条
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindList` (IN param1Name NVarChar(1000),IN param1Parent Int)
BEGIN
        insert into BookKindList(BookKindName,BookKindParent) values(param1Name,param1Parent);
END $$
DELIMITER ;
 
#插入一条返回值
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$
CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Parent Int,OUT ID INT)
BEGIN
     IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then
        INSERT INTO BookKindList (BookKindName,BookKindParent)VALUES(param1Name ,param1Parent);
        #set ID=Last_insert_id()
        SELECT LAST_INSERT_ID() into ID;
      end if;
END $$
 
DELIMITER ;
 
 
/*自定义函数*/
#部门函数
DELIMITER $$
DROP FUNCTION IF EXISTS `geovindu`.`f_GetDepartmentName` $$
CREATE FUNCTION `geovindu`.`f_GetDepartmentName` (did int) RETURNS varchar(100)
BEGIN
declare str varchar(100);
return(select DepartmentName from DepartmentList where DepartmentID=did);
END $$
DELIMITER ;
 
#使用函数
select f_GetDepartmentName(1);
 
select * from BookInfoList;
#作家函数
 
DELIMITER $$
DROP FUNCTION IF EXISTS `geovindu`.`f_GetBookKindName` $$
CREATE FUNCTION `geovindu`.`f_GetBookKindName` (did int) RETURNS varchar(400)
BEGIN
   declare str varchar(100);
return(select BookKindName from BookKindList where BookKindID=did);
END $$
DELIMITER ;
 
 
-- 用户浏览记录
drop table  IF EXISTS  DuwebStat;
CREATE TABLE IF NOT EXISTS `DuwebStat` (
    `id` int(11) NOT NULL auto_increment  comment '自動增長ID',
    `seepage` varchar(200)  character set utf8 null comment '瀏覽的網頁',
    `userip` varchar(100)  character set utf8 null comment '登錄IP',
    `OSer` varchar(200)  character set utf8 null comment '操作系統',
    `Operating` varchar(100)  character set utf8 null comment '操作系統',
    `Browser` varchar(500)  character set utf8 null comment '瀏覽器',
    `Visioner` varchar(100)  character set utf8 null comment '版本',
    `Languageer` varchar(100)  character set utf8 null comment '語言版本',
    `IsCookie` varchar(100)  character set utf8 null comment '是否有Cookie',
    `BSize` varchar(100)  character set utf8 null comment '瀏覽器尺寸',
    `DetectedOpertor` text  character set utf8 null comment '客戶端環境描述',
    `Addtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',
  PRIMARY KEY  (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='用户浏览记录表' AUTO_INCREMENT=1;
 
-- 考虑主键外键
--
drop table  IF EXISTS  EnterpriseType;
-- 2  企业类型表
create table EnterpriseType
(
   EnterpriseTypeID int(20) NOT NULL auto_increment  comment '自動增長ID',
   EnterpriseTypeName nvarchar(100) not null comment '企业类型名称',                  -- 企业类型名称
   PRIMARY KEY  (EnterpriseTypeID)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='企业类型表' AUTO_INCREMENT=1;
 
insert into EnterpriseType (EnterpriseTypeName) values(N'分公司');
insert into EnterpriseType (EnterpriseTypeName) values(N'店铺');
 
select * from OperatingUser;
-- 3 公司表
drop table CompanyBranch;
 
create table CompanyBranch
(
    CompanyID int(20) NOT NULL auto_increment  comment '自動增長ID',
    CompanyName nvarchar(100) not null  comment '公司名称',
    CompanyTypeID int not null  comment '企业类型ID',
    CompanyDate  timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',
    CompanyDesc NVARCHAR(500) null  comment '公司描述',
    CompanyTel varchar(100) null  comment '公司电话',
    CompanyFax VARCHAR(100) NULL  comment '公司传真',
    CompanyAddress NVARCHAR(500) NULL  comment '公司地址',
    PRIMARY KEY  (CompanyID),
    FOREIGN KEY(CompanyTypeID) REFERENCES EnterpriseType(EnterpriseTypeID)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='公司表' AUTO_INCREMENT=1;
 
 
insert into CompanyBranch(CompanyName,CompanyTypeID,CompanyTel,CompanyFax,CompanyAddress,CompanyDesc) values('六福珠宝营销策划(深圳）有限公司',1,'','','','');
 
select * from CompanyBranch;
 
select * from EnterpriseType;
 
drop table OperatingUser;
-- 1
create table OperatingUser
(
    UserID  int(20) PRIMARY KEY  NOT NULL auto_increment  comment '自動增長ID',
    UserName nvarchar(200) not null comment '用户名',                      -- 用户名
    RealName NVARCHAR(50) NOT NULL comment '真实姓名',                      -- 真姓名
    UserPassword varchar(100) not null comment '密码',                    -- 密码
    UserCompanyId int not null comment '公司ID',                      -- 公司ID
    UserPasswordProblems nvarchar(100) comment '找回密码问题',                    -- 找回密码问题  
    UserMail varchar(100) null comment '邮件',                        -- 邮件
    UserDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',                                    -- 默认日期
    FOREIGN KEY(UserCompanyId) REFERENCES CompanyBranch(CompanyID)
 
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='用户表' AUTO_INCREMENT=1;
 
select * from OperatingUser;
 
 
-- 4 登录日志表
drop table LoginDiaryList;
drop table  IF EXISTS  LoginDiaryList;
CREATE TABLE LoginDiaryList
(
    LoginDiaryID int(20)  PRIMARY KEY  NOT NULL auto_increment  comment '自動增長ID',
    LoginDiaryUserName nvarchar(50) null comment '登錄用戶名',       -- 登錄用戶名   
    LoginDiaryUserId int not null comment '員工ID',           -- 員工ID
    LoginDiaryBrowser varchar(50) null comment '客戶端瀏覽',     -- 客戶端瀏覽
    LoginDiaryScreen varchar(50) null comment '显示器大小',          -- 显示器大小
    LoginDiaryOpertor varchar(50) null comment '操作系統',      -- 操作系統
    LoginDiaryInput nvarchar(150) null comment '輸入法',       -- 輸入法
    LoginDiaryDate   timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',   -- 日期
    FOREIGN KEY(LoginDiaryUserId) REFERENCES OperatingUser(UserID)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='登录日志表' AUTO_INCREMENT=1;
 
 
select * from LoginDiaryList;
 
select * from PrintWordDocumentTemplateList;
 
-- 5
drop table  IF EXISTS  PrintWordDocumentTemplateList;
create table PrintWordDocumentTemplateList
(
    PrintWordDocumentId int PRIMARY KEY auto_increment  comment '自動增長ID',
    -- PrintWordDocumentUid Uniqueidentifier DEFAULT(NEWID())  PRIMARY KEY,
    -- PrintWordPayTypeUidKey Uniqueidentifier,             -- 考核類型(試用期,年終,特別)
    -- PrintWordJobTypeUidKey Uniqueidentifier,             -- -職位類型(文職類,員工類,管理級別類等)
    PrintWordOnlyPassIs bit(1) default b'0' comment '通過試用期並成為正式員工',             -- -通過試用期並成為正式員工
    PrintWordPlusSalaryIs bit default b'0' comment '通過並加薪',             -- -通過並加薪
    PrintWordPromotionIs bit default b'0' comment '通過晉升',               -- -通過晉升
    PrintWordExtensionIs bit default b'0'  comment '延長試用期',             -- 延長試用期
    PrintWordDismissIs bit default b'0' comment '解僱',                   -- 解僱
    PrintWordDepartmentIs bit default b'0' comment '新部門',               -- 新部門
    PrintWordDocumentName Nvarchar(100) not null comment '文檔標題',                -- 文檔標題
    PrintWordDocumentUrl nvarchar(200) null comment '文檔鏈接',             -- 文檔鏈接
    PrintWordDocumentContent nvarchar(300) null comment '文檔簡要描述',               -- 文檔簡要描述
    PrintWordDocumentAddDate   timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',
    PrintWordDocumentByte BLOB null comment '文檔',
    PrintWordType int(20) default 1 comment '文檔類型'              -- 文檔類型 1.分公司，2.分店
    -- PrintWordLetterSignature nvarchar(100) null              -- 信函簽名
 
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='打印单表' AUTO_INCREMENT=1;
 
 
-- 6
-- 客戶表Customer（需方） 名稱，工地名稱
drop table CustomerList;
 
drop table  IF EXISTS  CustomerList;
CREATE TABLE CustomerList
(
    CustomerID int(20) PRIMARY KEY auto_increment  comment '自動增長ID',
    CustomerName NVARCHAR(200) NOT NULL comment '客户姓名',
    CustomerNamePin VARCHAR(500) NULL comment '拼音首字母',
    CustomerContact NVARCHAR(50) NULL comment '聯系人',            -- 聯系人
    CustomerTel VARCHAR(100) NULL comment '聯系人電話',          -- 聯系人電話
    CustomerDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='客戶表' AUTO_INCREMENT=1;
 
 
-- 7表單關聯人類型RelationshipsType： 指定收貨人 跟單業務員 工地驗收人 運輸人
drop table RelationshipsType;
drop table  IF EXISTS  RelationshipsType;
 
CREATE TABLE RelationshipsType
(
    RelationshipsTypeID int(20) PRIMARY KEY auto_increment  comment '自動增長ID',
    RelationshipsTypeName NVARCHAR(100) NOT NULL comment '关系类型名称',
    RelationshipsTypePin VARCHAR(500) NULL comment '拼音首字母'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='表單關聯人類型表' AUTO_INCREMENT=1;
 
insert into RelationshipsType(RelationshipsTypeName) values('指定收货人');
insert into RelationshipsType(RelationshipsTypeName) values('跟单业务员');
insert into RelationshipsType(RelationshipsTypeName) values('工地验收人');
insert into RelationshipsType(RelationshipsTypeName) values('运输人');
 
-- 8表單關係人錶RelationshipsPerson 
drop table RelationshipsPerson;
drop table  IF EXISTS  RelationshipsPerson;
CREATE TABLE RelationshipsPerson
(
    PersonID int(20) PRIMARY KEY  auto_increment  comment '自動增長ID',
    PersonName NVARCHAR(100) NOT NULL comment '姓名',
    PersonNamePin VARCHAR(500) NULL comment '拼音首字母',
    PersonTel VARCHAR(100) comment '电话',
    PersonType int NOT NULL comment '类型',
    PersonDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',
    FOREIGN KEY(PersonType) REFERENCES RelationshipsType(RelationshipsTypeID)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='表單關係人' AUTO_INCREMENT=1;
 
select * from RelationshipsPerson;
 
 
 
-- 9產品名稱表 ProductTypeList
drop table ProductTypeList;
 
drop table  IF EXISTS  ProductTypeList;
CREATE TABLE ProductTypeList
(
    ProductTypeID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    ProductTypeName NVARCHAR(800) NOT NULL comment '产品名称',
    ProductTypePin VARCHAR(500) NULL comment '拼音首字母'     -- 字首字母
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='產品名稱表' AUTO_INCREMENT=1;
 
-- 10單位表 UnitList   comment ''
drop table UnitList;
 
drop table  IF EXISTS  UnitList;
 
CREATE TABLE UnitList
(
    UnitID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    UnitName NVARCHAR(100) NOT NULL comment '单位名称',
    UnitPin VARCHAR(500) NULL comment '拼音首字母'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='單位表' AUTO_INCREMENT=1;
 
select * from UnitList;
 
delete from UnitList where UnitName='';
 
drop table ProductModel;
-- 产品规格
drop table  IF EXISTS  ProductModel;
 
CREATE TABLE ProductModel
(
    ModelID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    ModelProductTypeID INT NOT NULL comment '产品名称ID',        -- 产品名称ID 外键 ProductTypeList
    ModelName NVARCHAR(800) NOT NULL comment '产品规格',
    ModelPin VARCHAR(500) NULL comment '拼音首字母', 
    KEY ModelProductTypeID (ModelProductTypeID),
    CONSTRAINT ProductModel_ibfk_1 FOREIGN KEY(ModelProductTypeID) REFERENCES ProductTypeList(ProductTypeID)
    ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='产品规格表' AUTO_INCREMENT=1;
 
 
--  MyISAM Foreign Keys显示不了外键
drop table  IF EXISTS  city;
CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT comment'',
  `Name` char(35) NOT NULL DEFAULT '' comment'',
  `CountryCode` char(3) NOT NULL DEFAULT '' comment'',
  `District` char(20) NOT NULL DEFAULT '' comment'',
  `Population` int(11) NOT NULL DEFAULT '0' comment'',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=MyISAM COMMENT='城市表' AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8;
 
drop table  IF EXISTS  country;
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT ''  comment'',
  `Name` char(52) NOT NULL DEFAULT '' comment'',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' comment'',
  `Region` char(26) NOT NULL DEFAULT '' comment'',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00' comment'',
  `IndepYear` smallint(6) DEFAULT NULL comment'',
  `Population` int(11) NOT NULL DEFAULT '0' comment'',
  `LifeExpectancy` float(3,1) DEFAULT NULL comment'',
  `GNP` float(10,2) DEFAULT NULL comment'',
  `GNPOld` float(10,2) DEFAULT NULL comment'',
  `LocalName` char(45) NOT NULL DEFAULT '' comment'',
  `GovernmentForm` char(45) NOT NULL DEFAULT '' comment'',
  `HeadOfState` char(60) DEFAULT NULL comment'',
  `Capital` int(11) DEFAULT NULL comment'',
  `Code2` char(2) NOT NULL DEFAULT '' comment'',
  PRIMARY KEY (`Code`)
) ENGINE=MyISAM COMMENT='国家表' DEFAULT CHARSET=utf8;
 
 
 
--  InnoDB  Foreign Keys显示外键
 
drop table  IF EXISTS  city;
CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT comment'',
  `Name` char(35) NOT NULL DEFAULT '' comment'',
  `CountryCode` char(3) NOT NULL DEFAULT '' comment'',
  `District` char(20) NOT NULL DEFAULT '' comment'',
  `Population` int(11) NOT NULL DEFAULT '0' comment'',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB COMMENT='城市表' AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8;
 
 
 
 
drop table  IF EXISTS  country;
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT ''  comment'',
  `Name` char(52) NOT NULL DEFAULT '' comment'',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' comment'',
  `Region` char(26) NOT NULL DEFAULT '' comment'',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00' comment'',
  `IndepYear` smallint(6) DEFAULT NULL comment'',
  `Population` int(11) NOT NULL DEFAULT '0' comment'',
  `LifeExpectancy` float(3,1) DEFAULT NULL comment'',
  `GNP` float(10,2) DEFAULT NULL comment'',
  `GNPOld` float(10,2) DEFAULT NULL comment'',
  `LocalName` char(45) NOT NULL DEFAULT '' comment'',
  `GovernmentForm` char(45) NOT NULL DEFAULT '' comment'',
  `HeadOfState` char(60) DEFAULT NULL comment'',
  `Capital` int(11) DEFAULT NULL comment'',
  `Code2` char(2) NOT NULL DEFAULT '' comment'',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB COMMENT='国家表' DEFAULT CHARSET=utf8;
 
 
 
drop table UnitPrice;
 
-- 单价表
drop table  IF EXISTS  UnitPrice;
CREATE TABLE UnitPrice
(
    UnitPriceID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    UnitProductTypeID INT NOT NULL comment '产品名称ID',        -- 产品名称ID 外键 ProductTypeList
    UnitPriceNuber DECIMAL(20,2) NOT NULL comment '单价',
    UnitPricePin VARCHAR(500) NULL comment '拼音首字母',
    FOREIGN KEY(UnitProductTypeID) REFERENCES ProductTypeList(ProductTypeID)
        ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='单价表' AUTO_INCREMENT=1;
 
select * from UnitPrice;
 
drop table CustomerAddress;
--  客户地址表
drop table  IF EXISTS  CustomerAddress;
CREATE TABLE CustomerAddress
(
    AddressID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    AddressName NVARCHAR(100) NOT NULL comment '地名',
    AddressPin VARCHAR(500) NULL comment '拼音首字母'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='客户地址表' AUTO_INCREMENT=1;
 
 
-- 11級別表 LevelList
drop table LevelList;
drop table  IF EXISTS  LevelList;
CREATE TABLE LevelList
(
    LevelID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    LevelName NVARCHAR(100) NOT NULL comment '级别名称',
    LevelPin VARCHAR(500) NULL comment '拼音首字母'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='級別表' AUTO_INCREMENT=1;
 
select * from LevelList;
 
delete from LevelList where LevelName='';
 
 
-- 12工地名名稱表 建筑工地名Construction site name
drop table ConstructionNameList;
 
drop table  IF EXISTS  ConstructionNameList;
CREATE TABLE ConstructionNameList
(
    ConstructionID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    ConstructionName VARCHAR(100) NOT NULL comment '工地名称',
    ConstructionPin VARCHAR(500) NULL comment '拼音首字母'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='工地名名稱表' AUTO_INCREMENT=1;
 
select * from ConstructionNameList;
 
delete from ConstructionNameList where ConstructionName='';
 
-- 訂單產品詳情表
drop table OrderItDetails;
drop table  IF EXISTS  OrderItDetails;
CREATE TABLE OrderItDetails
(
    OrderID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    OrderDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '送货日期',      -- 送货日期
    OrderWord VARCHAR(50) NULL comment '字',             -- 字
    OrderNo VARCHAR(100) NULL comment '号',              -- 号
    OrderCustomerId INT NOT NULL comment '客户名稱',                -- 客户名稱
    OrderAddressID INT NOT NULL comment '客户地址名稱',               -- 客户地址名稱      
    OrderPrepared INT NULL comment '製單人',               -- 製單人
    OrderBusiness INT NULL comment '指定收货人',             -- 指定收货人                                           
    OrderPrintDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '打單時間', -- 打單時間
    FOREIGN KEY(OrderCustomerId) REFERENCES CustomerList(CustomerID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderAddressID) REFERENCES CustomerAddress(AddressID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderPrepared) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderBusiness) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='訂單產品詳情表' AUTO_INCREMENT=1;
 
-- 訂單產品詳情
drop table ProductItOrderDetails;
drop table  IF EXISTS  ProductItOrderDetails;
CREATE TABLE ProductItOrderDetails
(
    ProductDetailsId INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    ProductOrderId INT NOT NULL comment '產品訂單ID',           -- 產品訂單ID  外錶OrderDetails
    ProductOrderTypeId INT NOT NULL comment '產品名稱ID',           -- 產品名稱
    ProductModleId INT comment '規格ID',              -- 規格
    ProductUnitID INT comment '單位ID',               -- 單位
    ProductQty DECIMAL(18,2) DEFAULT 0 comment '數量',        -- 數量
    ProductPriceID INT NOT NULL comment '单价ID',         -- 单价
    ProductMeters DECIMAL(25,2) DEFAULT 0 comment '金额',     -- 金额
    ProductDescription VARCHAR(1000) NULL comment '說明',     -- 說明
    FOREIGN KEY(ProductOrderId) REFERENCES OrderItDetails(OrderID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductOrderTypeId) REFERENCES ProductTypeList(ProductTypeID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductModleId) REFERENCES ProductModel(ModelID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductUnitID) REFERENCES UnitList(UnitID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductPriceID) REFERENCES UnitPrice(UnitPriceID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='訂單產品詳情表' AUTO_INCREMENT=1;
 
 
-- 13打單表內容 ProductDetails
-- https://www.sqlite.org/datatype3.html
drop table OrderDetails;
drop table  IF EXISTS  OrderDetails;
CREATE TABLE OrderDetails
(
    OrderID INTEGER PRIMARY KEY auto_increment  comment '自動增長ID',
    OrderDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '添加時間',      -- 日期
    OrderWord NVARCHAR(50) NULL comment '字',                -- 字
    OrderNo NVARCHAR(100) NULL comment '号',             -- 号
    OrderCustomerId int NOT NULL comment '需求方ID',               -- 需求方
    OrderConstructionId int NOT NULL comment '工地名稱ID',              -- 工地名稱  
        -- OrderProductId int NOT NULL,             -- 產品訂單詳情ID  外錶ProductOrderDetails
    OrderAcceptor int NULL comment '工地驗收人ID',               -- 工地驗收人
    OrderTransportation  INTEGER NULL comment '運輸人ID',          -- 運輸人
    OrderPrepared int NULL comment '製單人ID',             -- 製單人
    OrderBusiness int NULL comment '指定業務人ID',               -- 指定業務人
    OrderMerchandiser int NULL comment '跟單業務員ID',               -- 跟單業務員                                           
    OrderPrintDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '打單時間',-- 打單時間
    FOREIGN KEY(OrderCustomerId) REFERENCES CustomerList(CustomerID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderConstructionId) REFERENCES ConstructionNameList(ConstructionID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderAcceptor) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderTransportation) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderPrepared) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderBusiness) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(OrderMerchandiser) REFERENCES RelationshipsPerson(PersonID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT 
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='打單表內容表' AUTO_INCREMENT=1;
 
-- 14訂單產品詳情表 NUMERIC
drop table ProductOrderDetails;
drop table  IF EXISTS  ProductOrderDetails;
CREATE TABLE ProductOrderDetails
(
    ProductDetailsId int PRIMARY KEY auto_increment  comment '自動增長ID',
    ProductOrderId INT NOT NULL comment '產品訂單ID',               -- 產品訂單ID  外錶OrderDetails
    ProductTypeId int NOT NULL comment '產品名稱規格ID',              -- 產品名稱規格
    ProductUnitID int NOT NULL comment '單位ID',              -- 單位
    ProductQty DECIMAL(18,2) DEFAULT 0.0 comment '數量',          -- 數量
    ProductLevelID int NOT NULL comment '級別ID',             -- 級別
    ProductMeters DECIMAL(25,2) DEFAULT 0 comment '米數',         -- 米數
    ProductDescription NVARCHAR(1000) NULL comment '說明',            -- 說明
    FOREIGN KEY(ProductOrderId) REFERENCES OrderDetails(OrderID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductTypeId) REFERENCES ProductTypeList(ProductTypeID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductUnitID) REFERENCES UnitList(UnitID)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY(ProductLevelID) REFERENCES LevelList(LevelID)  
    ON UPDATE CASCADE
    ON DELETE RESTRICT 
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='訂單產品詳情表' AUTO_INCREMENT=1;
 
 
 
-- 設置移動打印X，Y坐標糾正值
drop table PrintSetNumber;
drop table  IF EXISTS  PrintSetNumber;
CREATE TABLE PrintSetNumber
(
    PrintSetId int PRIMARY KEY auto_increment  comment '自動增長ID',
    PrintSetX int unsigned default 0 comment '列坐标',  -- com 列坐标
    PrintSetY int default 0 comment '行坐标', -- row 行坐标
    PrintPrinter VARCHAR(200) NULL comment '默认打印机名',    -- 默认打印机名
    PrintFont VARCHAR(200) NULL default '宋体' comment '默认字体名',--  默认字体名
    PrintBottom bit default 0 comment '底部文字是否双排',   -- 底部文字是否双排
    TitleFontSize int default 8 comment '标题字体大小',     -- 标题字体大小
    ConFontSize int default 8 comment '内容字体大小', -- 内容字体大小
    HeadFontSize int default 8 comment '表头字体大小',    -- 表头字体大小
    BoomFontSize int default 8 comment '表底字体大小' -- 表底字体大小
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='設置移動打印' AUTO_INCREMENT=1;
 
-- 表的描述
drop table DataTableDesc;
drop table  IF EXISTS  DataTableDesc;
CREATE TABLE DataTableDesc
(
    TableId int PRIMARY KEY auto_increment  comment '自動增長ID',
    TableName nvarchar(100) not null comment '表名',
    TableDesc nvarchar(100) null comment '表描述'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='表的描述' AUTO_INCREMENT=1;
 
 
-- 列的列描述 columnName
drop table DataColumnDesc;
drop table  IF EXISTS  DataColumnDesc;
CREATE TABLE DataColumnDesc
(
    ColumnId int PRIMARY KEY auto_increment  comment '自動增長ID',
    ColumnTableId int not null comment '表ID',
    ColumnName nvarchar(100) not null comment '列名',
    ColumnDesc nvarchar(100) null comment '列描述',
    -- KEY ColumnTableId (ColumnTableId),  --此项添上也可以
    -- CONSTRAINT `DataColumnDesc_ibfk_1`  --此项添上也可以
    FOREIGN KEY(ColumnTableId) REFERENCES DataTableDesc(TableId)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='列的列描述' AUTO_INCREMENT=1;
 
select * from DataTableDesc;
 
select * from DataColumnDesc;
 
 
select * from PrintSetNumber;
 
-- 视图
CREATE VIEW v_DataTableColumn as
select DataColumnDesc.*,DataTableDesc.TableName,DataTableDesc.TableDesc from DataColumnDesc,DataTableDesc
where DataTableDesc.TableId=DataColumnDesc.ColumnTableId;
 
CREATE VIEW v_OperatingUser as
select OperatingUser.*,CompanyBranch.CompanyName,CompanyBranch.companyTel,CompanyBranch.CompanyFax,CompanyBranch.CompanyAddress
 from OperatingUser,CompanyBranch where OperatingUser.UserCompanyId=CompanyBranch.CompanyID;
 
 
drop view v_CompanyBranch;
-- 公司
create view v_CompanyBranch
as
select CompanyBranch.*,EnterpriseType.EnterpriseTypeName from CompanyBranch, EnterpriseType where CompanyBranch.CompanyTypeID=EnterpriseType.EnterpriseTypeID;
 
 
select CompanyBranch.*,EnterpriseType.EnterpriseTypeName from CompanyBranch, EnterpriseType where CompanyBranch.CompanyTypeID=EnterpriseType.EnterpriseTypeID;
 
select * from v_CompanyBranch;
 
 
drop view v_OperatingUser;
-- 用户
create view v_OperatingUser
as
select OperatingUser.*,CompanyBranch.CompanyName,CompanyBranch.companyTel,CompanyBranch.CompanyFax,CompanyBranch.CompanyAddress from OperatingUser,CompanyBranch  where OperatingUser.UserCompanyId=CompanyBranch.CompanyID;
 
select * from v_OperatingUser;
 
-- 錶單用戶視圖
 
CREATE VIEW view_relationshipsPerson
AS
select RelationshipsPerson.*,RelationshipsType.RelationshipsTypeName FROM RelationshipsPerson,RelationshipsType
WHERE RelationshipsPerson.PersonType=RelationshipsType.RelationshipsTypeID;
 
 
-- 订单视图

　　

?
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;
/*
 *
 MetaDataCollections
DataSourceInformation
DataTypes
Restrictions
ReservedWords
Databases
Tables
Columns
Users
Foreign Keys
IndexColumns
Indexes
Foreign Key Columns
UDF
Views
ViewColumns
Procedure Parameters
Procedures
Triggers
  
 */
 
 
namespace MySqlDemo
{
 
    /// <summary>
    ///
    /// </summary>
    public partial class Form2 : Form
    {
 
        /// <summary>
        /// 都可以用
        /// </summary>
        private static string connectionString = @"Database='geovindu';Data Source='localhost';User Id='root';Password='88888';charset='utf8';pooling=true;Port=3306;Allow Zero Datetime=true;";
        /// <summary>
        /// 都可以用
        /// </summary>
        private static string connectionString1 = @"Database='geovindu';server='localhost';User Id='root';Password='88888';charset='utf8';pooling=true;Port=3306;Allow Zero Datetime=true;";
 
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        private DataTable setTables()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("name", typeof(string));
            dt.Rows.Add(1, "MetaDataCollections");//MetaData集合
            dt.Rows.Add(2, "DatasourceInformation");//数据库文件版本等信息
            dt.Rows.Add(3, "DataTypes");//字段类型
            dt.Rows.Add(4, "Restrictions");//架构限制
            dt.Rows.Add(5, "ReservedWords");//限制关键字
            dt.Rows.Add(6, "Databases");//数据库（包含系统数据库）
            dt.Rows.Add(7, "Tables");//所有表包括系统表
            dt.Rows.Add(8, "Columns");//表的字段
            dt.Rows.Add(9, "Users");//用户
            dt.Rows.Add(10, "Foreign Keys");//外键表和主表 （不包含列）
            dt.Rows.Add(11, "IndexColumns");//有键的表及主键
            dt.Rows.Add(12, "Indexes");//所有有主键的表
            dt.Rows.Add(13, "Foreign Key Columns");// 外键表和主表及列
            dt.Rows.Add(14, "UDF");//
            dt.Rows.Add(15, "Views");//所有视图
            dt.Rows.Add(16, "ViewColumns");//视图的字段
            dt.Rows.Add(17, "Procedure Parameters");//存储过程参数
            dt.Rows.Add(18, "Procedures");//存储过程
            dt.Rows.Add(19, "Triggers");  
            return dt;
        }
        /// <summary>
        ///
        /// </summary>
        MySqlConnection connection = new MySqlConnection(connectionString1);
        /// <summary>
        /// open connection to database
        /// </summary>
        /// <returns></returns>
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;
 
                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }
 
        /// <summary>
        /// Close connection
        /// </summary>
        /// <returns></returns>
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        /// <summary>
        ///
        /// </summary>
        public Form2()
        {
            InitializeComponent();
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form2_Load(object sender, EventArgs e)
        {
            this.comboBox1.DataSource = setTables();
            this.comboBox1.DisplayMember = "name";
            this.comboBox1.ValueMember = "id";
            //connection.Open();
            //this.dataGridView1.DataSource = connection.GetSchema();
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            connection.Open();
            this.dataGridView1.DataSource = connection.GetSchema(this.comboBox1.Text);
            connection.Close();
        }
    }
}

　　

 实体类生成：

?
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
using System;
using System.Collections.Generic;
using System.Text;
 using System.Drawing;
 
namespace GeovinDu.Model
{
    /// <summary>
    /// Orderitdetails的实体类
    ///生成時間2018/9/27 15:25:01
    ///塗聚文(Geovin Du)訂單產品詳情表
    ///</summary>
    public class OrderitdetailsInfo
    {
        private UInt32 _OrderID;
 
        /// <summary>
        /// 自動增長ID
        /// </summary>
        public UInt32 OrderID
        {
            get { return _OrderID; }
            set {_OrderID = value; }
        }
 
        private DateTime _OrderDate;
 
        /// <summary>
        /// 送货日期
        /// </summary>
        public DateTime OrderDate
        {
            get { return _OrderDate; }
            set {_OrderDate = value; }
        }
 
        private string _OrderWord;
 
        /// <summary>
        /// 字
        /// </summary>
        public string OrderWord
        {
            get { return _OrderWord; }
            set {_OrderWord = value; }
        }
 
        private string _OrderNo;
 
        /// <summary>
        /// 号
        /// </summary>
        public string OrderNo
        {
            get { return _OrderNo; }
            set {_OrderNo = value; }
        }
 
        private UInt32 _OrderCustomerId;
 
        /// <summary>
        /// 客户名稱
        /// </summary>
        public UInt32 OrderCustomerId
        {
            get { return _OrderCustomerId; }
            set {_OrderCustomerId = value; }
        }
 
        private UInt32 _OrderAddressID;
 
        /// <summary>
        /// 客户地址名稱
        /// </summary>
        public UInt32 OrderAddressID
        {
            get { return _OrderAddressID; }
            set {_OrderAddressID = value; }
        }
 
        private UInt32 _OrderPrepared;
 
        /// <summary>
        /// 製單人
        /// </summary>
        public UInt32 OrderPrepared
        {
            get { return _OrderPrepared; }
            set {_OrderPrepared = value; }
        }
 
        private UInt32 _OrderBusiness;
 
        /// <summary>
        /// 指定收货人
        /// </summary>
        public UInt32 OrderBusiness
        {
            get { return _OrderBusiness; }
            set {_OrderBusiness = value; }
        }
 
        private DateTime _OrderPrintDate;
 
        /// <summary>
        /// 打單時間
        /// </summary>
        public DateTime OrderPrintDate
        {
            get { return _OrderPrintDate; }
            set {_OrderPrintDate = value; }
        }
 
        private List<ProductitorderdetailsInfo> _Productitorderdetails;
 
        /// <summary>
        /// 主表：orderitdetails訂單產品詳情表,外键表：productitorderdetails訂單產品詳情表,外键字段：ProductOrderId;
        ///</summary>
        public  List<ProductitorderdetailsInfo> ProductitorderdetailsList
        {
            get { return _Productitorderdetails; }
            set {_Productitorderdetails = value; }
        }
 
        private DataTable _ProductitorderdetailsData;
 
        /// <summary>
        /// 主表：orderitdetails訂單產品詳情表,外键表：productitorderdetails訂單產品詳情表,外键字段：ProductOrderId;
        ///</summary>
        public  DataTable ProductitorderdetailsData
        {
            get { return _ProductitorderdetailsData; }
            set {_ProductitorderdetailsData = value; }
        }
 
        private DataSet _ProductitorderdetailsDaset;
 
        /// <summary>
        /// 主表：orderitdetails訂單產品詳情表,外键表：productitorderdetails訂單產品詳情表,外键字段：ProductOrderId;
        ///</summary>
        public  DataSet ProductitorderdetailsDaset
        {
            get { return _ProductitorderdetailsDaset; }
            set {_ProductitorderdetailsDaset = value; }
        }
 
         
    }
}

　数据处理层：

?
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
/// <summary>
    /// DataColumnDesc数据访问层
    ///生成時間2018/9/10 16:57:31
    ///塗聚文(Geovin Du)
    ///</summary>
    public class DataColumnDescDAL : IDataColumnDesc
    {
        ///<summary>
        /// 追加记录
        /// 存储过程： Insert,InsertOutput,Update,Delete,Select,
        /// sql操作：  Add,AddOutput,Edit,Del,Query,
        ///</summary>
        ///<param name="DataColumnDescInfo"></param>
        ///<returns></returns>
        public int InsertDataColumnDesc(DataColumnDescInfo dataColumnDesc)
        {
            int ret = 0;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("INSERT INTO DataColumnDesc([ColumnTableId] ,[ColumnName] ,[ColumnDesc]");
                strSql.Append(") VALUES (");
                strSql.Append("@ColumnTableId ,@ColumnName ,@ColumnDesc)");
                MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("@ColumnTableId",MySqlDbType.Int32,4),
                new MySqlParameter("@ColumnName",MySqlDbType.String,2147483647),
                new MySqlParameter("@ColumnDesc",MySqlDbType.String,100),
                };
                par[0].Value = dataColumnDesc.ColumnTableId;
                par[1].Value = dataColumnDesc.ColumnName;
                par[2].Value = dataColumnDesc.ColumnDesc;
                ret = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        /// 追加记录返回值
        ///</summary>
        ///<param name="DataColumnDescInfo"></param>
        ///<returns></returns>
        public int InsertDataColumnDescOutput (DataColumnDescInfo dataColumnDesc,out int columnId)
        {
            int ret = 0;
            columnId= 0;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("INSERT INTO DataColumnDesc([ColumnTableId] ,[ColumnName] ,[ColumnDesc]");
                strSql.Append(") VALUES (");
                strSql.Append("@ColumnTableId ,@ColumnName ,@ColumnDesc)");
                MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("@ColumnTableId",MySqlDbType.Int32,4),
                new MySqlParameter("@ColumnName",MySqlDbType.String,2147483647),
                new MySqlParameter("@ColumnDesc",MySqlDbType.String,100),
                new MySqlParameter("@ColumnId",MySqlDbType.Int32,8),
                };
                par[0].Value = dataColumnDesc.ColumnTableId;
                par[1].Value = dataColumnDesc.ColumnName;
                par[2].Value = dataColumnDesc.ColumnDesc;
                //par[3].Direction = ParameterDirection.Output;
                ret = MySqlHelpDu.ExecuteSql(strSql.ToString(), out columnId, par);
             
                if(ret>0)
                {
                 
                    //columnId=(int)par[3].Value;
             
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        ///修改记录
        ///</summary>
        ///<param name="DataColumnDescInfo"></param>
        ///<returns></returns>
        public int UpdateDataColumnDesc(DataColumnDescInfo dataColumnDesc)
        {
            int ret = 0;
            try
            {
                StringBuilder strSql = new StringBuilder();
                if (!ExistsName(dataColumnDesc.ColumnName))
                {
                     
                    strSql.Append("UPDATE DataColumnDesc SET ");
                    strSql.Append("[ColumnTableId]=@ColumnTableId ,");
                    strSql.Append("[ColumnName]=@ColumnName ,");
                    strSql.Append("[ColumnDesc]=@ColumnDesc");
                    strSql.Append(" where ");
                    strSql.Append("[ColumnId]=@ColumnId");
                    MySqlParameter[] par = new MySqlParameter[]{
                        new MySqlParameter("@ColumnTableId",MySqlDbType.Int32,4),
                        new MySqlParameter("@ColumnName",MySqlDbType.String,2147483647),
                        new MySqlParameter("@ColumnDesc",MySqlDbType.String,100),
                        new MySqlParameter("@ColumnId",MySqlDbType.Int32),
                    };
                    par[0].Value = dataColumnDesc.ColumnTableId;
                    par[1].Value = dataColumnDesc.ColumnName;
                    par[2].Value = dataColumnDesc.ColumnDesc;
                    par[3].Value = dataColumnDesc.ColumnId;
                    ret = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
                }
                else
                {
                    strSql.Append("UPDATE DataColumnDesc SET ");
                    strSql.Append("[ColumnTableId]=@ColumnTableId ,");                 
                    strSql.Append("[ColumnDesc]=@ColumnDesc");
                    strSql.Append(" where ");
                    strSql.Append("[ColumnId]=@ColumnId");
                    MySqlParameter[] par = new MySqlParameter[]{
                        new MySqlParameter("@ColumnTableId",MySqlDbType.Int32,4),                    
                        new MySqlParameter("@ColumnDesc",MySqlDbType.String,100),
                        new MySqlParameter("@ColumnId",MySqlDbType.Int32),
                    };
                    par[0].Value = dataColumnDesc.ColumnTableId;                  
                    par[1].Value = dataColumnDesc.ColumnDesc;
                    par[2].Value = dataColumnDesc.ColumnId;
                    ret = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        /// 删除记录
        ///</summary>
        ///<param name="columnIdInfo"></param>
        ///<returns></returns>
        public bool DeleteDataColumnDesc(int columnId)
        {
            bool ret = false;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from dataColumnDesc");
                strSql.Append(" where  ColumnId=");
                strSql.Append("@ColumnId ");
                MySqlParameter[] par = new MySqlParameter[]{
                     new MySqlParameter("@ColumnId",columnId)
                };
                int temp = 0 ;
                temp = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
                if(temp!=0)
                {
                    ret = true;
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
 
        ///<summary>
        /// 删除多条记录
        ///</summary>
        ///<param name="columnIdInfo"></param>
        ///<returns></returns>
        public bool DeleteDataColumnDescMore(string columnId)
        {
            bool ret = false;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from dataColumnDesc");
                strSql.Append(" where  ColumnId in(");
                strSql.Append("@ColumnId)");
                MySqlParameter[] par = new MySqlParameter[]{
                     new MySqlParameter("@ColumnId",columnId)
                };
                int temp = 0;
                temp = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
                if (temp != 0)
                {
                    ret = true;
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        /// <summary>
        /// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
        /// </summary>
        /// <param name="tableId"></param>
        /// <returns></returns>
        public bool DeleteDataColumnTableId(int tableId)
        {
            bool ret = false;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from dataColumnDesc");
                strSql.Append(" where  ColumnTableId=");
                strSql.Append("@ColumnTableId ");
                MySqlParameter[] par = new MySqlParameter[]{
                     new MySqlParameter("@ColumnTableId",tableId)
                };
                int temp = 0;
                temp = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
                if (temp != 0)
                {
                    ret = true;
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
 
        ///<summary>
        /// 查询记录
        ///</summary>
        ///<param name="columnIdInfo"></param>
        ///<returns></returns>
        public DataColumnDescInfo SelectDataColumnDesc(int columnId)
        {
            DataColumnDescInfo dataColumnDesc = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from  dataColumnDesc");
                strSql.Append(" where  ColumnId=");
                strSql.Append("@ColumnId ");
                MySqlParameter[] par = new MySqlParameter[]{
                    new MySqlParameter("@ColumnId",columnId)
                };
                using (MySqlDataReader reader = MySqlHelpDu.GetReader(strSql.ToString(), CommandType.Text, par))
                {
                    if (reader.Read())
                    {
                        dataColumnDesc = new DataColumnDescInfo();
                        dataColumnDesc.ColumnId =(!object.Equals(reader["ColumnId"],null))? int.Parse(reader["ColumnId"].ToString()):0;
                        dataColumnDesc.ColumnTableId =(!object.Equals(reader["ColumnTableId"],null))? int.Parse(reader["ColumnTableId"].ToString()):0;
                        dataColumnDesc.ColumnName =(!object.Equals(reader["ColumnName"],null))? (string) reader["ColumnName"]:"";
                        dataColumnDesc.ColumnDesc =(!object.Equals(reader["ColumnDesc"],null))? (string) reader["ColumnDesc"]:"";
                         
                    }
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return dataColumnDesc;
        }
         
        ///<summary>
        /// 查询所有记录
        ///</summary>
        ///<returns></returns>
        public List<DataColumnDescInfo> SelectDataColumnDescAll()
        {
            List<DataColumnDescInfo> list = new List<DataColumnDescInfo>();
            DataColumnDescInfo dataColumnDesc = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from  dataColumnDesc");
                using (MySqlDataReader reader = MySqlHelpDu.GetReader(strSql.ToString(), CommandType.Text, null))
                {
                    while (reader.Read())
                    {
                        dataColumnDesc = new DataColumnDescInfo();
                        dataColumnDesc.ColumnId =(!object.Equals(reader["ColumnId"],null))? int.Parse(reader["ColumnId"].ToString()):0;
                        dataColumnDesc.ColumnTableId =(!object.Equals(reader["ColumnTableId"],null))? int.Parse(reader["ColumnTableId"].ToString()):0;
                        dataColumnDesc.ColumnName =(!object.Equals(reader["ColumnName"],null))? (string) reader["ColumnName"]:"";
                        dataColumnDesc.ColumnDesc =(!object.Equals(reader["ColumnDesc"],null))? (string) reader["ColumnDesc"]:"";
                        list.Add(dataColumnDesc);
                         
                    }
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return list;
        }
 
        /// <summary>
        /// 查询外键ID的所有记录
        /// </summary>
        /// <param name="ColumnTableId">外键ID</param>
        /// <returns></returns>
        public List<DataColumnDescInfo> SelectListColumnDescTableId(int ColumnTableId)
        {
            List<DataColumnDescInfo> list = new List<DataColumnDescInfo>();
            DataColumnDescInfo dataColumnDesc = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from  dataColumnDesc");
                strSql.Append(" where  ColumnTableId=");
                strSql.Append("@ColumnTableId ");
                MySqlParameter[] par = new MySqlParameter[]{
                    new MySqlParameter("@ColumnTableId",ColumnTableId)
                };
                using (MySqlDataReader reader = MySqlHelpDu.GetReader(strSql.ToString(), CommandType.Text, par))
                {
                    while (reader.Read())
                    {
                        dataColumnDesc = new DataColumnDescInfo();
                        dataColumnDesc.ColumnId = (!object.Equals(reader["ColumnId"], null)) ? int.Parse(reader["ColumnId"].ToString()) : 0;
                        dataColumnDesc.ColumnTableId = (!object.Equals(reader["ColumnTableId"], null)) ? int.Parse(reader["ColumnTableId"].ToString()) : 0;
                        dataColumnDesc.ColumnName = (!object.Equals(reader["ColumnName"], null)) ? (string)reader["ColumnName"] : "";
                        dataColumnDesc.ColumnDesc = (!object.Equals(reader["ColumnDesc"], null)) ? (string)reader["ColumnDesc"] : "";
                        list.Add(dataColumnDesc);
 
                    }
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return list;
        }
 
        /// <summary>
        /// 查询外键ID的所有记录
        /// </summary>
        /// <param name="ColumnTableId">外键ID</param>
        /// <returns></returns>
        public DataTable SelectDataColumnDescTableId(int ColumnTableId)
        {
            DataTable dt = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from  dataColumnDesc");
                strSql.Append(" where  ColumnTableId=");
                strSql.Append("@ColumnTableId ");
                MySqlParameter[] par = new MySqlParameter[]{
                    new MySqlParameter("@ColumnTableId",ColumnTableId)
                };
                using (DataTable reader = MySqlHelpDu.GetTable(strSql.ToString(), CommandType.Text, par))
                {
                    dt = reader;
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return dt;
        }
        ///<summary>
        /// 查询所有记录
        ///</summary>
        ///<returns></returns>
        public DataTable SelectDataColumnDescDataTableAll()
        {
            DataTable dt = new DataTable();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from dataColumnDesc");
                using (DataTable reader = MySqlHelpDu.GetTable(strSql.ToString(), CommandType.Text, null))
                {
                    dt = reader;
                         
                     
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return dt;
        }
        ///<summary>
        ///SQL脚本 删除多条记录
        ///</summary>
        ///<param name="columnIdInfo"></param>
        ///<returns></returns>
        public bool DeleteDataColumnDescId(string columnId)
        {
            bool ret = false;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from dataColumnDesc");
                strSql.Append(" where  ColumnId in (");
                strSql.Append("@ColumnId )");
                //SQLiteParameter[] par = new SQLiteParameter[]{};
                MySqlParameter par = new MySqlParameter("@ColumnId", columnId);
                int temp = 0 ;
                temp = MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, par);
                if(temp!=0)
                {
                    ret = true;
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return ret;
        }
        /// <summary>
        /// SQL script查询分页
        /// </summary>
        /// <param name="pageSize">每页页数</param>
        /// <param name="currentPage">当前页码</param>
        /// <param name="strWhere">查询的条件</param>
        /// <param name="filedOrder">排序字段</param>
        /// <param name="recordCount">返回每页的记录数</param>
        /// <returns></returns>
        public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount)
        {
            int topNum = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM DataColumnDesc");
            if (strWhere.Trim() !="")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(MySqlHelpDu.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return MySqlHelpDu.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder));
        }
        /// <summary>
        /// SQL语句分页
        /// </summary>
        /// <param name="pageSize">每页页数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="filedOrder">排序字段</param>
        /// <param name="recordCount">返回每页的记录数</param>
        /// <returns></returns>
        public DataSet GetPageList(int pageSize, int pageIndex, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *  FROM DataColumnDesc");
            recordCount = Convert.ToInt32(MySqlHelpDu.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return MySqlHelpDu.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        /// <summary>
        /// SQL语句分页
        /// </summary>
        /// <param name="pageSize">每页页数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="recordCount">返回每页的记录数</param>
        /// <returns></returns>
        public DataSet GetPageList(int pageSize, int pageIndex, out int recordCount)
        {
            string filedOrder = " order by ColumnId desc";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *  FROM DataColumnDesc");
            recordCount = Convert.ToInt32(MySqlHelpDu.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return MySqlHelpDu.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
 
 
 
        /// <summary>
        ///SQL脚本 模糊查询
        /// </summary>
        /// <param name="filedlist">显示字段列表</param>
        /// <param name="strkey">输入的关键字</param>
        /// <returns></returns>
        public DataTable GetDataTableDataColumnDescFuzzySearch(string filedlist, string strkey)
        {
            DataTable dt = new DataTable();
            try
            {
                StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (string.IsNullOrEmpty(filedlist))
            {
                strSql.Append(" * ");
            }
            else
            {
                strSql.Append(" from DataColumnDesc");
            }
            if (!string.IsNullOrEmpty(strkey))
            {
                strSql.Append(" where " + StringConvert.getStrWhere("ColumnName,ColumnDesc",strkey));
            }
            dt = MySqlHelpDu.Query(strSql.ToString()).Tables[0];
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return dt;
        }
        /// <summary>
        ///SQL脚本 是否存在该记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from DataColumnDesc");
            strSql.Append(" where ColumnId=@ColumnId ");
            MySqlParameter[] parameters = {
                new MySqlParameter("@ColumnId", MySqlDbType.Int32,4)};
            parameters[0].Value = id;
            return MySqlHelpDu.Exists(strSql.ToString(), parameters);
        }
 
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool ExistsId(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from DataColumnDesc");
            strSql.Append(" where ColumnId=@ColumnId ");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@ColumnId", MySqlDbType.Int32,8)};
            parameters[0].Value = id;
 
            return MySqlHelpDu.Exists(strSql.ToString(), parameters);
        }
 
        /// <summary>
        /// 检查单位名是否存在
        /// </summary>
        /// <param name="levelName"></param>
        /// <returns></returns>
        public bool ExistsName(string columnName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from DataColumnDesc");
            strSql.Append(" where ColumnName=@ColumnName ");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@ColumnName", MySqlDbType.String,100)};
            parameters[0].Value = columnName;
            return MySqlHelpDu.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 找到名称ID
        /// </summary>
        /// <param name="unitName"></param>
        /// <returns></returns>
        public int GetName(string columnName)
        {
            int name = 0; ;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ColumnId from DataColumnDesc");
            strSql.Append(" where ColumnName=@ColumnName ");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@ColumnName", MySqlDbType.String,100)
             };
            parameters[0].Value = columnName;
            name = Convert.ToInt32(MySqlHelpDu.GetSingle(strSql.ToString(), parameters));
            return name;
 
        }
 
        /// <summary>
        /// 獲取字段描述
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetColumnNameDesc(string columnName)
        {
            string name = "";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ColumnDesc from DataColumnDesc");
            strSql.Append(" where ColumnName=@ColumnName ");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@ColumnName", MySqlDbType.String,100)
             };
            parameters[0].Value = columnName;
            name = Convert.ToString(MySqlHelpDu.GetSingle(strSql.ToString(), parameters));
            return name;
 
        }
        /// <summary>
        /// 獲取字段描述
        /// </summary>
        /// <param name="columnName">字段名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GetTableColumnNameDesc(string columnName, string tableName)
        {
            string name = "";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ColumnDesc from v_DataTableColumn");
            strSql.Append(" where ColumnName=@ColumnName and TableName=@TableName");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@ColumnName", MySqlDbType.String,100),
                    new MySqlParameter("@TableName", MySqlDbType.String,100)
             };
            parameters[0].Value = columnName;
            parameters[1].Value = tableName;
            name = Convert.ToString(MySqlHelpDu.GetSingle(strSql.ToString(), parameters));
            return name;
 
        }
        /// <summary>
        ///SQL脚本 返回视图数据总数
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public int GetCountView(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) as H ");
            strSql.Append(" from DataColumnDescView");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return Convert.ToInt32(MySqlHelpDu.GetSingle(strSql.ToString()));
        }
        /// <summary>
        ///SQL脚本 返回数据总数
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) as H ");
            strSql.Append(" from DataColumnDesc");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return Convert.ToInt32(MySqlHelpDu.GetSingle(strSql.ToString()));
        }
        /// <summary>
        ///SQL脚本 修改一列数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="strValue"></param>
        /// <returns></returns>
        public int UpdateField(int Id, string fieldValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update DataColumnDesc set  @fieldValue");
            strSql.Append(" where ColumnId= @Id");
            MySqlParameter[] parameters = {
                new MySqlParameter("@fieldValue", MySqlDbType.String),
                new MySqlParameter("@ColumnId", MySqlDbType.Int32,8),
            };
            parameters[0].Value = Id;
            parameters[1].Value = fieldValue;
            return MySqlHelpDu.ExecuteSql(strSql.ToString(), CommandType.Text, parameters);
        }
        /// <summary>
        ///SQL脚本 返回指字字段的字串
        /// </summary>
        /// <param name="id"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public string GetTitle(int Id, string fieldName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 " + fieldName + " from DataColumnDesc");
            strSql.Append(" where ColumnId=" + Id);
            string title = Convert.ToString(MySqlHelpDu.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return string.Empty;
            }
            return title;
        }
         
    }
?
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
/// <summary>
/// Companybranch数据访问层
///生成時間2018/9/29 12:15:41
///塗聚文(Geovin Du)公司表
///</summary>
public class CompanybranchDAL : ICompanybranch
{
    ///<summary>
    ///存储过程 追加记录
    ///</summary>
    ///<param name="CompanybranchInfo"></param>
    ///<returns></returns>
    public int InsertCompanybranch(CompanybranchInfo companybranch)
    {
        int ret = 0;
        try
        {
            MySqlParameter[] par = new MySqlParameter[]{
            new MySqlParameter("?param1CompanyName",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyTypeID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyDate",MySqlDbType.Timestamp,8),
            new MySqlParameter("?param1CompanyDesc",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyTel",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyFax",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyAddress",MySqlDbType.VarChar,500),
            };
            par[0].Value = companybranch.CompanyName;
            par[1].Value = companybranch.CompanyTypeID;
            par[2].Value = companybranch.CompanyDate;
            par[3].Value = companybranch.CompanyDesc;
            par[4].Value = companybranch.CompanyTel;
            par[5].Value = companybranch.CompanyFax;
            par[6].Value = companybranch.CompanyAddress;
            ret = MySqlDBHelper.ExecuteSql("proc_Insert_Companybranch", CommandType.StoredProcedure, par);
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///SQL脚本 追加记录
    ///</summary>
    ///<param name="CompanybranchInfo"></param>
    ///<returns></returns>
    public int InsertSqlCompanybranch(CompanybranchInfo companybranch)
    {
        int ret = 0;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO companybranch(CompanyName,CompanyTypeID,CompanyDate,CompanyDesc,CompanyTel,CompanyFax,CompanyAddress");
            strSql.Append(") VALUES (");
            strSql.Append("?param1CompanyName ,?param1CompanyTypeID ,?param1CompanyDate ,?param1CompanyDesc ,?param1CompanyTel ,?param1CompanyFax ,?param1CompanyAddress)");
            MySqlParameter[] par = new MySqlParameter[]{
            new MySqlParameter("?param1CompanyName",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyTypeID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyDate",MySqlDbType.Timestamp,8),
            new MySqlParameter("?param1CompanyDesc",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyTel",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyFax",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyAddress",MySqlDbType.VarChar,500),
            };
            par[0].Value = companybranch.CompanyName;
            par[1].Value = companybranch.CompanyTypeID;
            par[2].Value = companybranch.CompanyDate;
            par[3].Value = companybranch.CompanyDesc;
            par[4].Value = companybranch.CompanyTel;
            par[5].Value = companybranch.CompanyFax;
            par[6].Value = companybranch.CompanyAddress;
            ret = MySqlDBHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///存储过程 追加记录返回值
    ///</summary>
    ///<param name="CompanybranchInfo"></param>
    ///<returns></returns>
    public int InsertCompanybranchOutput (CompanybranchInfo companybranch,out int companyID)
    {
        int ret = 0;
        companyID= 0;
        try
        {
            MySqlParameter[] par = new MySqlParameter[]{
            new MySqlParameter("?param1CompanyName",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyTypeID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyDate",MySqlDbType.Timestamp,8),
            new MySqlParameter("?param1CompanyDesc",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyTel",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyFax",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyAddress",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyID",MySqlDbType.Int32,8),
            };
            par[0].Value = companybranch.CompanyName;
            par[1].Value = companybranch.CompanyTypeID;
            par[2].Value = companybranch.CompanyDate;
            par[3].Value = companybranch.CompanyDesc;
            par[4].Value = companybranch.CompanyTel;
            par[5].Value = companybranch.CompanyFax;
            par[6].Value = companybranch.CompanyAddress;
            par[7].Direction = ParameterDirection.Output;
            ret = MySqlDBHelper.ExecuteSql("proc_Insert_CompanybranchOutput", CommandType.StoredProcedure, par);
         
            if(ret>0)
            {
             
                companyID=(int)par[7].Value;
         
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///SQL脚本 追加记录返回值
    ///</summary>
    ///<param name="CompanybranchInfo"></param>
    ///<returns></returns>
    public int InsertSqlCompanybranchOutput (CompanybranchInfo companybranch,out int companyID)
    {
        int ret = 0;
        companyID= 0;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO companybranch(CompanyName,CompanyTypeID,CompanyDate,CompanyDesc,CompanyTel,CompanyFax,CompanyAddress");
            strSql.Append(") VALUES (");
            strSql.Append("?param1CompanyName ,?param1CompanyTypeID ,?param1CompanyDate ,?param1CompanyDesc ,?param1CompanyTel ,?param1CompanyFax ,?param1CompanyAddress)");
            MySqlParameter[] par = new MySqlParameter[]{
            new MySqlParameter("?param1CompanyName",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyTypeID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyDate",MySqlDbType.Timestamp,8),
            new MySqlParameter("?param1CompanyDesc",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyTel",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyFax",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyAddress",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyID",MySqlDbType.Int32,8),
            };
            par[0].Value = companybranch.CompanyName;
            par[1].Value = companybranch.CompanyTypeID;
            par[2].Value = companybranch.CompanyDate;
            par[3].Value = companybranch.CompanyDesc;
            par[4].Value = companybranch.CompanyTel;
            par[5].Value = companybranch.CompanyFax;
            par[6].Value = companybranch.CompanyAddress;
            par[7].Direction = ParameterDirection.Output;
            ret = MySqlDBHelper.ExecuteSql(strSql.ToString(), out companyID, par);
         
            if(ret>0)
            {
             
                companyID=(int)par[7].Value;
         
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///存储过程 修改记录
    ///</summary>
    ///<param name="CompanybranchInfo"></param>
    ///<returns></returns>
    public int UpdateCompanybranch(CompanybranchInfo companybranch)
    {
        int ret = 0;
        try
        {
            MySqlParameter[] par = new MySqlParameter[]{
            new MySqlParameter("?param1CompanyID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyName",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyTypeID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyDate",MySqlDbType.Timestamp,8),
            new MySqlParameter("?param1CompanyDesc",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyTel",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyFax",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyAddress",MySqlDbType.VarChar,500),
            };
            par[0].Value = companybranch.CompanyID;
            par[1].Value = companybranch.CompanyName;
            par[2].Value = companybranch.CompanyTypeID;
            par[3].Value = companybranch.CompanyDate;
            par[4].Value = companybranch.CompanyDesc;
            par[5].Value = companybranch.CompanyTel;
            par[6].Value = companybranch.CompanyFax;
            par[7].Value = companybranch.CompanyAddress;
            ret = MySqlDBHelper.ExecuteSql("proc_Update_Companybranch", CommandType.StoredProcedure, par);
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///修改记录SQL脚本
    ///</summary>
    ///<param name="CompanybranchInfo"></param>
    ///<returns></returns>
    public int UpdateSqlCompanybranch(CompanybranchInfo companybranch)
    {
        int ret = 0;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE companybranch SET ");
            strSql.Append("CompanyName=?param1CompanyName ,");
            strSql.Append("CompanyTypeID=?param1CompanyTypeID ,");
            strSql.Append("CompanyDate=?param1CompanyDate ,");
            strSql.Append("CompanyDesc=?param1CompanyDesc ,");
            strSql.Append("CompanyTel=?param1CompanyTel ,");
            strSql.Append("CompanyFax=?param1CompanyFax ,");
            strSql.Append("CompanyAddress=?param1CompanyAddress");
            strSql.Append(" where ");
            strSql.Append("CompanyID=?param1CompanyID");
            MySqlParameter[] par = new MySqlParameter[]{
            new MySqlParameter("?param1CompanyName",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyTypeID",MySqlDbType.Int32,8),
            new MySqlParameter("?param1CompanyDate",MySqlDbType.Timestamp,8),
            new MySqlParameter("?param1CompanyDesc",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyTel",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyFax",MySqlDbType.VarChar,100),
            new MySqlParameter("?param1CompanyAddress",MySqlDbType.VarChar,500),
            new MySqlParameter("?param1CompanyID",MySqlDbType.Int32),
            };
            par[1].Value = companybranch.CompanyName;
            par[2].Value = companybranch.CompanyTypeID;
            par[3].Value = companybranch.CompanyDate;
            par[4].Value = companybranch.CompanyDesc;
            par[5].Value = companybranch.CompanyTel;
            par[6].Value = companybranch.CompanyFax;
            par[7].Value = companybranch.CompanyAddress;
            par[8].Value = companybranch.CompanyID;
            ret = MySqlDBHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///存储过程 删除记录
    ///</summary>
    ///<param name="companyIDInfo"></param>
    ///<returns></returns>
    public bool DeleteCompanybranch(int companyID)
    {
        bool ret = false;
        try
        {
            MySqlParameter par =new MySqlParameter("?param1CompanyID",companyID);
            int temp = 0 ;
            temp = MySqlDBHelper.ExecuteSql("proc_Delete_Companybranch", CommandType.StoredProcedure, par);
            if(temp!=0)
            {
                ret = true;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///SQL脚本 删除记录
    ///</summary>
    ///<param name="companyIDInfo"></param>
    ///<returns></returns>
    public bool DeleteSqlCompanybranch(int companyID)
    {
        bool ret = false;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from companybranch");
            strSql.Append(" where  CompanyID=");
            strSql.Append("?param1CompanyID ");
            //MySqlParameter[] par = new MySqlParameter[]{};
            MySqlParameter par =new MySqlParameter("?param1CompanyID",companyID);
            int temp = 0 ;
            temp = MySqlDBHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
            if(temp!=0)
            {
                ret = true;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    /// <summary>
    /// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
    /// </summary>
    /// <param name="tableId"></param>
    /// <returns></returns>
    public bool DeleteCompanyTypeID(UInt32 companyTypeID)
    {
        bool ret = false;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from companybranch");
            strSql.Append(" where  CompanyTypeID=");
            strSql.Append("@CompanyTypeID ");
            MySqlParameter[] par = new MySqlParameter[]{
                    new MySqlParameter("@CompanyTypeID",companyTypeID)
                };
            int temp = 0;
            temp = MySqlDBHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
            if (temp != 0)
            {
                ret = true;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
        }
    ///<summary>
    ///存储过程 查询记录
    ///</summary>
    ///<param name="companyIDInfo"></param>
    ///<returns></returns>
    public CompanybranchInfo SelectCompanybranch(int companyID)
    {
        CompanybranchInfo companybranch = null;
        try
        {
            MySqlParameter par =new MySqlParameter("?param1CompanyID",companyID);
            using (MySqlDataReader reader = MySqlDBHelper.GetReader("proc_Select_Companybranch", CommandType.StoredProcedure, par))
            {
                if (reader.Read())
                {
                    companybranch = new CompanybranchInfo();
                    companybranch.CompanyID = (UInt32) reader["CompanyID"];
                    companybranch.CompanyName =(!object.Equals(reader["CompanyName"],null))? (string) reader["CompanyName"]:"";
                    companybranch.CompanyTypeID = (UInt32) reader["CompanyTypeID"];
                    companybranch.CompanyDate =(!object.Equals(reader["CompanyDate"],null))? (DateTime) reader["CompanyDate"]:DateTime.Now;
                    companybranch.CompanyDesc =(!object.Equals(reader["CompanyDesc"],null))? (string) reader["CompanyDesc"]:"";
                    companybranch.CompanyTel =(!object.Equals(reader["CompanyTel"],null))? (string) reader["CompanyTel"]:"";
                    companybranch.CompanyFax =(!object.Equals(reader["CompanyFax"],null))? (string) reader["CompanyFax"]:"";
                    companybranch.CompanyAddress =(!object.Equals(reader["CompanyAddress"],null))? (string) reader["CompanyAddress"]:"";
                     
                }
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return companybranch;
    }
     
    ///<summary>
    ///SQL脚本 查询记录
    ///</summary>
    ///<param name="companyIDInfo"></param>
    ///<returns></returns>
    public CompanybranchInfo SelectSqlCompanybranch(int companyID)
    {
        CompanybranchInfo companybranch = null;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from  companybranch");
            strSql.Append(" where  CompanyID=");
            strSql.Append("?param1CompanyID ");
            //MySqlParameter[] par = new MySqlParameter[]{};
            MySqlParameter par =new MySqlParameter("?param1CompanyID",companyID);
            using (MySqlDataReader reader = MySqlDBHelper.GetReader(strSql.ToString(), CommandType.Text, par))
            {
                if (reader.Read())
                {
                    companybranch = new CompanybranchInfo();
                    companybranch.CompanyID = (UInt32) reader["CompanyID"];
                    companybranch.CompanyName =(!object.Equals(reader["CompanyName"],null))? (string) reader["CompanyName"]:"";
                    companybranch.CompanyTypeID = (UInt32) reader["CompanyTypeID"];
                    companybranch.CompanyDate =(!object.Equals(reader["CompanyDate"],null))? (DateTime) reader["CompanyDate"]:DateTime.Now;
                    companybranch.CompanyDesc =(!object.Equals(reader["CompanyDesc"],null))? (string) reader["CompanyDesc"]:"";
                    companybranch.CompanyTel =(!object.Equals(reader["CompanyTel"],null))? (string) reader["CompanyTel"]:"";
                    companybranch.CompanyFax =(!object.Equals(reader["CompanyFax"],null))? (string) reader["CompanyFax"]:"";
                    companybranch.CompanyAddress =(!object.Equals(reader["CompanyAddress"],null))? (string) reader["CompanyAddress"]:"";
                     
                }
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return companybranch;
    }
     
    ///<summary>
    /// 存储过程 查询所有记录
    ///</summary>
    ///<returns></returns>
    public List<CompanybranchInfo> SelectCompanybranchAll()
    {
        List<CompanybranchInfo> list = new List<CompanybranchInfo>();
        CompanybranchInfo companybranch = null;
        try
        {
            using (MySqlDataReader reader = MySqlDBHelper.GetReader("proc_Select_CompanybranchAll", CommandType.StoredProcedure, null))
            {
                while (reader.Read())
                {
                    companybranch = new CompanybranchInfo();
                    companybranch.CompanyID = (UInt32) reader["CompanyID"];
                    companybranch.CompanyName =(!object.Equals(reader["CompanyName"],null))? (string) reader["CompanyName"]:"";
                    companybranch.CompanyTypeID = (UInt32) reader["CompanyTypeID"];
                    companybranch.CompanyDate =(!object.Equals(reader["CompanyDate"],null))? (DateTime) reader["CompanyDate"]:DateTime.Now;
                    companybranch.CompanyDesc =(!object.Equals(reader["CompanyDesc"],null))? (string) reader["CompanyDesc"]:"";
                    companybranch.CompanyTel =(!object.Equals(reader["CompanyTel"],null))? (string) reader["CompanyTel"]:"";
                    companybranch.CompanyFax =(!object.Equals(reader["CompanyFax"],null))? (string) reader["CompanyFax"]:"";
                    companybranch.CompanyAddress =(!object.Equals(reader["CompanyAddress"],null))? (string) reader["CompanyAddress"]:"";
                    list.Add(companybranch);
                     
                }
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return list;
    }
    /// <summary>
    /// 查询外键ID的所有记录
    /// </summary>
    /// <param name="CompanyTypeID">外键ID</param>
    /// <returns></returns>
    public List<companybranchInfo> SelectListcompanybranchCompanyTypeID(UInt32 companyTypeID)
    {
        List<companybranchInfo> list = new List<companybranchInfo>();
        companybranchInfo companybranch = null;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from  companybranch");
            strSql.Append(" where  CompanyTypeID=");
            strSql.Append("@CompanyTypeID ");
            MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("@CompanyTypeID",companyTypeID)
            };
            using (MySqlDataReader reader = MySqlDBHelper.GetReader(strSql.ToString(), CommandType.Text, par))
            {
                while (reader.Read())
                {
                    companybranch = new companybranchInfo();
                    companybranch.CompanyID = (UInt32) reader["CompanyID"];
                    companybranch.CompanyName =(!object.Equals(reader["CompanyName"],null))? (string) reader["CompanyName"]:"";
                    companybranch.CompanyTypeID = (UInt32) reader["CompanyTypeID"];
                    companybranch.CompanyDate =(!object.Equals(reader["CompanyDate"],null))? (DateTime) reader["CompanyDate"]:DateTime.Now;
                    companybranch.CompanyDesc =(!object.Equals(reader["CompanyDesc"],null))? (string) reader["CompanyDesc"]:"";
                    companybranch.CompanyTel =(!object.Equals(reader["CompanyTel"],null))? (string) reader["CompanyTel"]:"";
                    companybranch.CompanyFax =(!object.Equals(reader["CompanyFax"],null))? (string) reader["CompanyFax"]:"";
                    companybranch.CompanyAddress =(!object.Equals(reader["CompanyAddress"],null))? (string) reader["CompanyAddress"]:"";
                    list.Add(companybranch);
                }
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
        return list;
    }
    /// <summary>
    /// 查询外键ID的所有记录
    /// </summary>
    /// <param name="CompanyTypeID">外键ID</param>
    /// <returns></returns>
    public DataTable SelectcompanybranchCompanyTypeID(UInt32 companyTypeID)
    {
        DataTable dt = null;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from  companybranch");
            strSql.Append(" where  CompanyTypeID=");
            strSql.Append("@CompanyTypeID ");
            MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("@CompanyTypeID",companyTypeID)
            };
            using (DataTable reader = MySqlDBHelper.GetTable(strSql.ToString(), CommandType.Text, par))
            {
                dt = reader;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return dt;
    }
    ///<summary>
    /// SQL脚本 查询所有记录
    ///</summary>
    ///<returns></returns>
    public List<CompanybranchInfo> SelectSqlCompanybranchAll()
    {
        List<CompanybranchInfo> list = new List<CompanybranchInfo>();
        CompanybranchInfo companybranch = null;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from  companybranch");
            MySqlParameter[] par = new MySqlParameter[]{};
            using (MySqlDataReader reader = MySqlDBHelper.GetReader(strSql.ToString(),CommandType.Text, null))
            {
                while (reader.Read())
                {
                    companybranch = new CompanybranchInfo();
                    companybranch.CompanyID = (UInt32) reader["CompanyID"];
                    companybranch.CompanyName =(!object.Equals(reader["CompanyName"],null))? (string) reader["CompanyName"]:"";
                    companybranch.CompanyTypeID = (UInt32) reader["CompanyTypeID"];
                    companybranch.CompanyDate =(!object.Equals(reader["CompanyDate"],null))? (DateTime) reader["CompanyDate"]:DateTime.Now;
                    companybranch.CompanyDesc =(!object.Equals(reader["CompanyDesc"],null))? (string) reader["CompanyDesc"]:"";
                    companybranch.CompanyTel =(!object.Equals(reader["CompanyTel"],null))? (string) reader["CompanyTel"]:"";
                    companybranch.CompanyFax =(!object.Equals(reader["CompanyFax"],null))? (string) reader["CompanyFax"]:"";
                    companybranch.CompanyAddress =(!object.Equals(reader["CompanyAddress"],null))? (string) reader["CompanyAddress"]:"";
                    list.Add(companybranch);
                     
                }
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return list;
    }
    ///<summary>
    /// 查询所有记录
    ///</summary>
    ///<returns></returns>
    public DataTable SelectCompanybranchDataTableAll()
    {
        DataTable dt = new DataTable();
        try
        {
            using (DataTable reader = MySqlDBHelper.GetTable("proc_Select_CompanybranchAll", CommandType.StoredProcedure, null))
            {
                dt = reader;
                     
                 
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return dt;
    }
    ///<summary>
    /// SQL脚本 查询所有记录
    ///</summary>
    ///<returns></returns>
    public DataTable SelectSqlCompanybranchDataTableAll()
    {
        DataTable dt = new DataTable();
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from companybranch");
            //MySqlParameter[] par = new MySqlParameter[]{};
            using (DataTable reader = MySqlDBHelper.GetTable(strSql.ToString(), CommandType.Text, null))
            {
                dt = reader;
                     
                 
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return dt;
    }
    ///<summary>
    ///存储过程  删除多条记录
    ///</summary>
    ///<param name="companyID">输入参数:CompanyID</param>
    ///<returns>返回删除多少记录</returns>
    public bool DeleteCompanybranchId(string companyID)
    {
        bool ret = false;
        try
        {
            MySqlParameter par =new MySqlParameter("?param1CompanyID",companyID);
            int temp = 0 ;
            temp = MySqlDBHelper.ExecuteSql("proc_Delete_CompanybranchId", CommandType.StoredProcedure, par);
            if(temp!=0)
            {
                ret = true;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    ///<summary>
    ///SQL脚本  删除多条记录
    ///</summary>
    ///<param name="companyID">输入参数:CompanyID</param>
    ///<returns>返回删除多少记录</returns>
    public bool DeleteSqlCompanybranchId(string companyID)
    {
        bool ret = false;
        try
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from companybranch");
            strSql.Append(" where  CompanyID IN(");
            strSql.Append("?param1CompanyID )");
            MySqlParameter par =new MySqlParameter("?param1CompanyID",companyID);
            int temp = 0 ;
            temp = MySqlDBHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
            if(temp!=0)
            {
                ret = true;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    /// <summary>
    /// SQL script查询分页
    /// </summary>
    /// <param name="pageSize">每页页数</param>
    /// <param name="currentPage">当前页码</param>
    /// <param name="strWhere">查询的条件</param>
    /// <param name="filedOrder">排序字段</param>
    /// <param name="recordCount">每页的记录数</param>
    /// <returns></returns>
    public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount)
    {
        int topNum = pageSize * currentPage;
        StringBuilder strSql = new StringBuilder();
        strSql.Append("select * FROM Companybranch");
        if (strWhere.Trim() !="")
        {
            strSql.Append(" where " + strWhere);
        }
        recordCount = Convert.ToInt32(MySqlDBHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
        return MySqlDBHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder));
    }
    /// <summary>
    /// SQL语句分页
    /// </summary>
    /// <param name="pageSize">每页页数</param>
    /// <param name="pageIndex">当前页码</param>
    /// <param name="filedOrder">排序字段</param>
    /// <param name="recordCount">返回每页的记录数</param>
    /// <returns></returns>
    public DataSet GetPageList(int pageSize, int pageIndex, string filedOrder, out int recordCount)
    {
        StringBuilder strSql = new StringBuilder();
        strSql.Append("select *  FROM Companybranch");
        recordCount = Convert.ToInt32(MySqlDBHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
        return MySqlDBHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
    }
    /// <summary>
    /// SQL语句分页
    /// </summary>
    /// <param name="pageSize">每页页数</param>
    /// <param name="pageIndex">当前页码</param>
    /// <param name="recordCount">返回每页的记录数</param>
    /// <returns></returns>
    public DataSet GetPageList(int pageSize, int pageIndex, out int recordCount)
    {
        string filedOrder = " order by CompanyID desc";
        StringBuilder strSql = new StringBuilder();
        strSql.Append("select *  FROM Companybranch");
        recordCount = Convert.ToInt32(MySqlDBHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
        return MySqlDBHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
    }
    /// <summary>
    ///存储过程  模糊查询
    /// </summary>
    /// <param name="filedlist">显示字段列表</param>
    /// <param name="strkey">输入的关键字</param>
    /// <returns></returns>
    public DataTable GetDataTableCompanybranchFuzzySearch(string filedlist, string strkey)
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("?FieldList",MySqlDbType.VarChar,2000),//可以考虑类型
                new MySqlParameter("?wherestr",MySqlDbType.VarChar,1000),
            };
            par[0].Value = filedlist;
            par[1].Value = StringConvert.getStrWhere("CompanyName,CompanyDesc,CompanyTel,CompanyFax,CompanyAddress",strkey);//获取字段为字符串的字段列表（varchar,nvarchar,text,ntext)
            using (DataTable reader = MySqlDBHelper.GetTable("proc_Select_CompanybranchFuzzySearch", CommandType.StoredProcedure, par))
            {
                dt = reader;
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return dt;
        }
    /// <summary>
    ///存储过程  是否存在该记录
    /// </summary>
    /// <param name="Id"></param>
    /// <returns></returns>
    public bool Exists(int Id)
    {
        bool isok = false;
        int count = 0;
        try
        {
            MySqlParameter par = new MySqlParameter("?param1CompanyID", Id);
            using (MySqlDataReader reader = MySqlDBHelper.GetReader("proc_Select_CompanybranchExists", CommandType.StoredProcedure, par))
            {
                if (reader.Read())
                {
                    count = (!DBNull.Equals(reader["H"], null)) ? (int)reader["H"] : 0;
                    if (count > 0)
                        isok = true;
                    }
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            return isok;
    }
    /// <summary>
    /// 是否存在该记录
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public bool ExistsId(int id)
    {
        StringBuilder strSql = new StringBuilder();
        strSql.Append("select count(1) from Companybranch");
        strSql.Append(" where CompanyID=@CompanyID ");
        MySqlParameter[] parameters = {
            new MySqlParameter("@CompanyID", MySqlDbType.Int32,10)};
        parameters[0].Value = id;
        return MySqlDBHelper.Exists(strSql.ToString(), parameters);
    }
    /// <summary>
    /// 检查单位名是否存在
    /// </summary>
    /// <param name="columnName"></param>
    /// <returns></returns>
    public bool ExistsName(string columnName)
    {
        StringBuilder strSql = new StringBuilder();
        strSql.Append("select count(1) from Companybranch");
        strSql.Append(" where ColumnName=@ColumnName ");
        MySqlParameter[] parameters = {
            new MySqlParameter("@ColumnName", MySqlDbType.String,100)};
        parameters[0].Value = columnName;
        return MySqlDBHelper.Exists(strSql.ToString(), parameters);
    }
    /// <summary>
    /// 找到名称ID
    /// </summary>
    /// <param name="columnName"></param>
    /// <returns></returns>
    public int GetName(string columnName)
    {
        int name = 0;
        StringBuilder strSql = new StringBuilder();
        strSql.Append("select CompanyID from Companybranch");
        strSql.Append(" where ColumnName=@ColumnName ");
        MySqlParameter[] parameters = {
            new MySqlParameter("@ColumnName", DbType.String,100)
    };
    parameters[0].Value = columnName;
    name = Convert.ToInt32(MySqlDBHelper.GetSingle(strSql.ToString(), parameters));
    return name;
    }
    /// <summary>
    ///存储过程  返回数据表总数
    /// </summary>
    /// <param name="strWhere">查询条件</param>
    /// <returns></returns>
    public int GetCount(string where)
    {
        int count = 0;
        try
        {
            MySqlParameter par = new MySqlParameter("?wherestr", where);
            using (MySqlDataReader reader = MySqlDBHelper.GetReader("proc_Select_CompanybranchCount", CommandType.StoredProcedure, par))
            {
                if (reader.Read())
                {
                    count = (!DBNull.Equals(reader["H"], null)) ? (int)reader["H"] : 0;
                }
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return count;
    }
    /// <summary>
    ///存储过程  返回视图数据总数
    /// </summary>
    /// <param name="strWhere">查询条件</param>
    /// <returns></returns>
    public int GetCountView(string where)
    {
        int count = 0;
        try
        {
            MySqlParameter par = new MySqlParameter("?wherestr", where);
            using (MySqlDataReader reader = MySqlDBHelper.GetReader("proc_Select_CompanybranchCountView", CommandType.StoredProcedure, par))
            {
                if (reader.Read())
                {
                    count = (!DBNull.Equals(reader["H"], null)) ? (int)reader["H"] : 0;
                }
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return count;
    }
    /// <summary>
    ///存储过程  更新一列数据
    /// </summary>
    /// <param name="Id"></param>
    /// <param name="strValue">字段名=值</param>
    /// <returns></returns>
    public int UpdateField(int Id, string fieldValue)
    {
        int ret = 0;
        try
        {
            MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("?param1CompanyID",MySqlDbType.Int32,8),
                new MySqlParameter("?FieldValue",MySqlDbType.VarChar,2000),
            };
            par[0].Value = Id;
            par[1].Value = fieldValue;
            ret = MySqlDBHelper.ExecuteSql("proc_Update_CompanybranchField", CommandType.StoredProcedure, par);
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return ret;
    }
    /// <summary>
    ///存储过程  返回指字字段的字串
    /// </summary>
    /// <param name="Id"></param>
    /// <param name="fieldName">字段名</param>
    /// <returns></returns>
    public string GetTitle(int Id, string fieldName)
    {
        string title = string.Empty;
        try
        {
            MySqlParameter[] par = new MySqlParameter[]{
                new MySqlParameter("?FieldName",MySqlDbType.VarChar,2000),
                new MySqlParameter("?param1CompanyID",MySqlDbType.Int32,8),
            };
            par[0].Value = fieldName;
            par[1].Value = Id;
            using (MySqlDataReader reader = MySqlDBHelper.GetReader("proc_Select_CompanybranchTitle", CommandType.StoredProcedure, par))
            {
                title =(!DBNull.Equals(reader[0],null))? (string) reader[0]:"";
            }
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        return title;
    }
     
}

　　

　　

　

posted @   ®Geovin Du Dream Park™  阅读(414)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2011-09-19 sql uniqueidentifier转换成varchar 数据类型
< 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
点击右上角即可分享
微信分享提示