-- 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;
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_NAMEfrom 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';
-- 查询外键select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references'from information_schema.key_column_usagewhere 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_usagewhere 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.htmlselect * 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_usagewhere 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 columnFROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` -- Will fail if user don't have privilegeWHERE `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 privilegeWHERE `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_usagewhere referenced_table_name is not null and table_schema = 'geovindu'; SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAMEFROM information_schema.REFERENTIAL_CONSTRAINTSWHERE 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_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE REFERENCED_TABLE_SCHEMA = 'geovindu' AND REFERENCED_TABLE_NAME = 'productitorderdetails'; SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE REFERENCED_TABLE_SCHEMA = 'geovindu' AND TABLE_NAME = 'productitorderdetails';
/*CREATE DATABASE geovinduDEFAULT CHARACTER SET utf8DEFAULT COLLATE utf8_general_ci;use geovindu;*/-- 查詢編碼格式show variables like '%char%';-- SET NAMES GB2312; ALTER DATABASE geovinduDEFAULT CHARACTER SET utf8DEFAULT 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'20180709character_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 utf8character_set_connection utf8character_set_database latin1 -- phpmyadmin2 客戶查詢亂碼character_set_filesystem binarycharacter_set_results utf8character_set_server latin1character_set_system utf8mysql> 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.htmlselect * 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_usagewhere 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)BEGINIF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) thenUPDATE 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)BEGINdeclare 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;-- 5drop 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產品名稱表 ProductTypeListdrop 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級別表 LevelListdrop 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 namedrop 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.htmldrop 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;-- 列的列描述 columnNamedrop 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_CompanyBranchasselect 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_OperatingUserasselect OperatingUser.*,CompanyBranch.CompanyName,CompanyBranch.companyTel,CompanyBranch.CompanyFax,CompanyBranch.CompanyAddress from OperatingUser,CompanyBranch where OperatingUser.UserCompanyId=CompanyBranch.CompanyID;select * from v_OperatingUser;-- 錶單用戶視圖CREATE VIEW view_relationshipsPersonASselect RelationshipsPerson.*,RelationshipsType.RelationshipsTypeName FROM RelationshipsPerson,RelationshipsTypeWHERE RelationshipsPerson.PersonType=RelationshipsType.RelationshipsTypeID;-- 订单视图
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;/* * MetaDataCollectionsDataSourceInformationDataTypesRestrictionsReservedWordsDatabasesTablesColumnsUsersForeign KeysIndexColumnsIndexesForeign Key ColumnsUDFViewsViewColumnsProcedure ParametersProceduresTriggers */namespace MySqlDemo{ /// /// /// public partial class Form2 : Form { /// /// 都可以用 /// private static string connectionString = @"Database='geovindu';Data Source='localhost';User Id='root';Password='88888';charset='utf8';pooling=true;Port=3306;Allow Zero Datetime=true;"; /// /// 都可以用 /// private static string connectionString1 = @"Database='geovindu';server='localhost';User Id='root';Password='88888';charset='utf8';pooling=true;Port=3306;Allow Zero Datetime=true;"; /// /// /// /// /// /// MySqlConnection connection = new MySqlConnection(connectionString1); /// /// open connection to database /// /// /// Close connection /// /// /// /// public Form2() { InitializeComponent(); } /// /// /// /// /// 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(); } /// /// /// /// /// private void button1_Click(object sender, EventArgs e) { connection.Open(); this.dataGridView1.DataSource = connection.GetSchema(this.comboBox1.Text); connection.Close(); } }}
实体类生成：
using System;using System.Collections.Generic;using System.Text; using System.Drawing;namespace GeovinDu.Model{ /// /// Orderitdetails的实体类 ///生成時間2018/9/27 15:25:01 ///塗聚文(Geovin Du)訂單產品詳情表 /// public class OrderitdetailsInfo { private UInt32 _OrderID; /// /// 自動增長ID /// public UInt32 OrderID { get { return _OrderID; } set {_OrderID = value; } } private DateTime _OrderDate; /// /// 送货日期 /// public DateTime OrderDate { get { return _OrderDate; } set {_OrderDate = value; } } private string _OrderWord; /// /// 字 /// public string OrderWord { get { return _OrderWord; } set {_OrderWord = value; } } private string _OrderNo; /// /// 号 /// public string OrderNo { get { return _OrderNo; } set {_OrderNo = value; } } private UInt32 _OrderCustomerId; /// /// 客户名稱 /// public UInt32 OrderCustomerId { get { return _OrderCustomerId; } set {_OrderCustomerId = value; } } private UInt32 _OrderAddressID; /// /// 客户地址名稱 /// public UInt32 OrderAddressID { get { return _OrderAddressID; } set {_OrderAddressID = value; } } private UInt32 _OrderPrepared; /// /// 製單人 /// public UInt32 OrderPrepared { get { return _OrderPrepared; } set {_OrderPrepared = value; } } private UInt32 _OrderBusiness; /// /// 指定收货人 /// public UInt32 OrderBusiness { get { return _OrderBusiness; } set {_OrderBusiness = value; } } private DateTime _OrderPrintDate; /// /// 打單時間 /// public DateTime OrderPrintDate { get { return _OrderPrintDate; } set {_OrderPrintDate = value; } } private List _Productitorderdetails; /// /// 主表：orderitdetails訂單產品詳情表,外键表：productitorderdetails訂單產品詳情表,外键字段：ProductOrderId; /// public List ProductitorderdetailsList { get { return _Productitorderdetails; } set {_Productitorderdetails = value; } } private DataTable _ProductitorderdetailsData; /// /// 主表：orderitdetails訂單產品詳情表,外键表：productitorderdetails訂單產品詳情表,外键字段：ProductOrderId; /// public DataTable ProductitorderdetailsData { get { return _ProductitorderdetailsData; } set {_ProductitorderdetailsData = value; } } private DataSet _ProductitorderdetailsDaset; /// /// 主表：orderitdetails訂單產品詳情表,外键表：productitorderdetails訂單產品詳情表,外键字段：ProductOrderId; /// public DataSet ProductitorderdetailsDaset { get { return _ProductitorderdetailsDaset; } set {_ProductitorderdetailsDaset = value; } } }} 数据处理层：
////// DataColumnDesc数据访问层 ///生成時間2018/9/10 16:57:31 ///塗聚文(Geovin Du) /// public class DataColumnDescDAL : IDataColumnDesc { ////// 追加记录 /// 存储过程： Insert,InsertOutput,Update,Delete,Select, /// sql操作： Add,AddOutput,Edit,Del,Query, /// /// ///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; } /// /// 追加记录返回值 /// /// ///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; } /// ///修改记录 /// /// ///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; } /// /// 删除记录 /// /// ///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; } /// /// 删除多条记录 /// /// ///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; } /// /// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除 /// /// ///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; } /// /// 查询记录 /// /// ///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; } /// /// 查询所有记录 /// ///public List SelectDataColumnDescAll() { List list = new List (); 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; } /// /// 查询外键ID的所有记录 /// /// 外键ID ///public List SelectListColumnDescTableId(int ColumnTableId) { List list = new List (); 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; } /// /// 查询外键ID的所有记录 /// /// 外键ID ///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; } /// /// 查询所有记录 /// ///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; } /// ///SQL脚本 删除多条记录 /// /// ///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; } /// /// SQL script查询分页 /// /// 每页页数 /// 当前页码 /// 查询的条件 /// 排序字段 /// 返回每页的记录数 ///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)); } /// /// SQL语句分页 /// /// 每页页数 /// 当前页码 /// 排序字段 /// 返回每页的记录数 ///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)); } /// /// SQL语句分页 /// /// 每页页数 /// 当前页码 /// 返回每页的记录数 ///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)); } /// ///SQL脚本 模糊查询 /// /// 显示字段列表 /// 输入的关键字 ///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; } /// ///SQL脚本 是否存在该记录 /// /// ///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); } /// /// 是否存在该记录 /// /// ///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); } /// /// 检查单位名是否存在 /// /// ///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); } /// /// 找到名称ID /// /// ///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; } /// /// 獲取字段描述 /// /// ///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; } /// /// 獲取字段描述 /// /// 字段名 /// 表名 ///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; } /// ///SQL脚本 返回视图数据总数 /// /// ///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())); } /// ///SQL脚本 返回数据总数 /// /// ///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())); } /// ///SQL脚本 修改一列数据 /// /// /// ///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); } /// ///SQL脚本 返回指字字段的字串 /// /// /// ///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; } }
////// Companybranch数据访问层 ///生成時間2018/9/29 12:15:41 ///塗聚文(Geovin Du)公司表 /// public class CompanybranchDAL : ICompanybranch { //////存储过程 追加记录 /// /// ///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; } /// ///SQL脚本 追加记录 /// /// ///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; } /// ///存储过程 追加记录返回值 /// /// ///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; } /// ///SQL脚本 追加记录返回值 /// /// ///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; } /// ///存储过程 修改记录 /// /// ///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; } /// ///修改记录SQL脚本 /// /// ///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; } /// ///存储过程 删除记录 /// /// ///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; } /// ///SQL脚本 删除记录 /// /// ///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; } /// /// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除 /// /// ///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; } /// ///存储过程 查询记录 /// /// ///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; } /// ///SQL脚本 查询记录 /// /// ///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; } /// /// 存储过程 查询所有记录 /// ///public List SelectCompanybranchAll() { List list = new List (); 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; } /// /// 查询外键ID的所有记录 /// /// 外键ID ///public List SelectListcompanybranchCompanyTypeID(UInt32 companyTypeID) { List list = new List (); 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; } /// /// 查询外键ID的所有记录 /// /// 外键ID ///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; } /// /// SQL脚本 查询所有记录 /// ///public List SelectSqlCompanybranchAll() { List list = new List (); 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; } /// /// 查询所有记录 /// ///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; } /// /// SQL脚本 查询所有记录 /// ///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; } /// ///存储过程 删除多条记录 /// /// 输入参数:CompanyID ///返回删除多少记录 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; } //////SQL脚本 删除多条记录 /// /// 输入参数:CompanyID ///返回删除多少记录 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; } ////// SQL script查询分页 /// /// 每页页数 /// 当前页码 /// 查询的条件 /// 排序字段 /// 每页的记录数 ///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)); } /// /// SQL语句分页 /// /// 每页页数 /// 当前页码 /// 排序字段 /// 返回每页的记录数 ///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)); } /// /// SQL语句分页 /// /// 每页页数 /// 当前页码 /// 返回每页的记录数 ///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)); } /// ///存储过程 模糊查询 /// /// 显示字段列表 /// 输入的关键字 ///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; } /// ///存储过程 是否存在该记录 /// /// ///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; } /// /// 是否存在该记录 /// /// ///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); } /// /// 检查单位名是否存在 /// /// ///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); } /// /// 找到名称ID /// /// ///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; } /// ///存储过程 返回数据表总数 /// /// 查询条件 ///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; } /// ///存储过程 返回视图数据总数 /// /// 查询条件 ///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; } /// ///存储过程 更新一列数据 /// /// /// 字段名=值 ///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; } /// ///存储过程 返回指字字段的字串 /// /// /// 字段名 ///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; } }