-- 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_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`;
-- 查詢外來鍵
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`;
/*
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;
-- 訂單檢視
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();
}
}
}
實體類生成：
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; }
}
}
}
資料處理層：
/// <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;
}
}
/// <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;
}
}