public popDBInit()
{
InitializeComponent();
conn = vari.conn;
conn.strDataBase = "master";
ctrls = new Control[] { inpDBName, btnDBInit };
}
/// <summary>
/// 기초 데이터를 입력 하여 준다.
/// </summary>
/// <param name="conn"></param>
public static void data_insert(MsSQL.strConnect conn)
{
MsSQL sql = new MsSQL(conn);
string qry = @"
Insert into T_CODE (COMPANY_ID, PROG_ID, CODE, CODENAME, CODETYPE ) values ( 'GM', 'TORQUE', 'DEV_NAME', '장비이름', 'S');
Insert into T_CODE (COMPANY_ID, PROG_ID, CODE, CODENAME, CODETYPE ) values ( 'GM', 'TORQUE', 'RST_NAME', '결과 이름', 'S');
Insert into T_CODE (COMPANY_ID, PROG_ID, CODE, CODENAME, CODETYPE ) values ( 'GM', 'TORQUE', 'TOOL_NAME', '툴 이름', 'S');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'DEV_NAME', 'DEV_01', '1호기', '0');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'DEV_NAME', 'DEV_02', '2호기', '1');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'RST_NAME', 'N01', 'Torque', '0');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'RST_NAME', 'N02', 'Angle', '1');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'RST_NAME', 'N03', 'SUB', '2');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T01', 'N01', '0');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T02', 'N02', '1');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T03', 'N03', '2');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T04', 'N04', '3');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T05', 'N05', '4');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T06', 'N06', '5');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T07', 'N07', '6');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T08', 'N08', '7');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T09', 'N11', '8');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T10', 'N12A', '9');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T11', 'N12B', '10');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T12', 'N13', '11');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T13', 'N14A', '12');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T14', 'N14B', '13');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T15', 'N15A', '14');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T16', 'N15B', '15');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T17', 'N16A', '16');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T18', 'N16B', '17');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T19', 'N17A', '18');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T20', 'N17B', '19');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T21', 'N18A', '20');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T22', 'N18B', '21');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T23', '', '22');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T24', '', '23');
Insert into T_CODEDETAIL ([COMPANY_ID], [PROG_ID], [CODE], [CODEVALUE], [CODEVALUENAME], PRIORITY ) values ( 'GM', 'TORQUE', 'TOOL_NAME', 'T25', '', '24');
";
sql.Excute_Query(qry, "");
}
/// <summary>
/// 상대 db 링크를 만들어 낸다.
/// </summary>
/// <param name="conn"></param>
/// <param name="ip"></param>
/// <param name="id"></param>
/// <param name="pass"></param>
public static void db_link_create(MsSQL.strConnect conn, string ip, string id, string pass)
{
MsSQL sql = new MsSQL(conn);
string qry = string.Format(@"
EXEC master.dbo.sp_addlinkedserver @server = N'LNK_TORQUE', @srvproduct=N'torque_lnk', @provider=N'SQLNCLI', @datasrc=N'{0}'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LNK_TORQUE',@useself=N'False',@locallogin=NULL,@rmtuser=N'{1}',@rmtpassword='******'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LNK_TORQUE', @optname=N'remote proc transaction promotion', @optvalue=N'true'"
, ip, id, pass);
sql.Excute_Query(qry, "");
}
/// <summary>
/// if 처리 프로시져를 만들어 준다.
/// </summary>
/// <param name="conn"></param>
public static void if_proc_create(MsSQL.strConnect conn)
{
MsSQL sql = new MsSQL(conn);
string qry = @"
-- =============================================
-- Author: CSW
-- Create date: 2017.07.28
-- Description: V0.1
-- =============================================
Create PROCEDURE [dbo].[P_RESULT_IF]
AS
DECLARE @DT1 AS DATETIME,
@DT2 AS DATETIME,
@CreateDate datetime ,
@PONO nvarchar(6) ,
@TrimInSeq nvarchar(4) ,
@VIN nvarchar(17) ,
@CarType nvarchar(3) ,
@TotalResult nvarchar(2),
@STATION_ID nchar(10) ,
@T01_N01 nvarchar(4),
@T01_N02 nvarchar(4),
@T01_N03 nvarchar(2),
@T02_N01 nvarchar(4),
@T02_N02 nvarchar(4),
@T02_N03 nvarchar(2),
@T03_N01 nvarchar(4),
@T03_N02 nvarchar(4),
@T03_N03 nvarchar(2),
@T04_N01 nvarchar(4),
@T04_N02 nvarchar(4),
@T04_N03 nvarchar(2),
@T05_N01 nvarchar(4),
@T05_N02 nvarchar(4),
@T05_N03 nvarchar(2),
@T06_N01 nvarchar(4),
@T06_N02 nvarchar(4),
@T06_N03 nvarchar(2),
@T07_N01 nvarchar(4),
@T07_N02 nvarchar(4),
@T07_N03 nvarchar(2),
@T08_N01 nvarchar(4),
@T08_N02 nvarchar(4),
@T08_N03 nvarchar(2),
@T09_N01 nvarchar(4),
@T09_N02 nvarchar(4),
@T09_N03 nvarchar(2),
@T10_N01 nvarchar(4),
@T10_N02 nvarchar(4),
@T10_N03 nvarchar(2),
@T11_N01 nvarchar(4),
@T11_N02 nvarchar(4),
@T11_N03 nvarchar(2),
@T12_N01 nvarchar(4),
@T12_N02 nvarchar(4),
@T12_N03 nvarchar(2),
@T13_N01 nvarchar(4),
@T13_N02 nvarchar(4),
@T13_N03 nvarchar(2),
@T14_N01 nvarchar(4),
@T14_N02 nvarchar(4),
@T14_N03 nvarchar(2),
@T15_N01 nvarchar(4),
@T15_N02 nvarchar(4),
@T15_N03 nvarchar(2),
@T16_N01 nvarchar(4),
@T16_N02 nvarchar(4),
@T16_N03 nvarchar(2),
@T17_N01 nvarchar(4),
@T17_N02 nvarchar(4),
@T17_N03 nvarchar(2),
@T18_N01 nvarchar(4),
@T18_N02 nvarchar(4),
@T18_N03 nvarchar(2),
@T19_N01 nvarchar(4),
@T19_N02 nvarchar(4),
@T19_N03 nvarchar(2),
@T20_N01 nvarchar(4),
@T20_N02 nvarchar(4),
@T20_N03 nvarchar(2),
@T21_N01 nvarchar(4),
@T21_N02 nvarchar(4),
@T21_N03 nvarchar(2),
@T22_N01 nvarchar(4),
@T22_N02 nvarchar(4),
@T22_N03 nvarchar(2),
@T23_N01 nvarchar(4),
@T23_N02 nvarchar(4),
@T23_N03 nvarchar(2),
@T24_N01 nvarchar(4),
@T24_N02 nvarchar(4),
@T24_N03 nvarchar(2),
@T25_N01 nvarchar(4),
@T25_N02 nvarchar(4),
@T25_N03 nvarchar(2),
@cnt int,
@TCnt int
BEGIN
SET @DT1 = GETDATE()
set @cnt = 0
PRINT '작업 시작' + CONVERT(VARCHAR(20), @DT1,120)
--전체 대상 조회
SELECT @TCnt = COUNT(*)
FROM [LNK_TORQUE].[TorqueTool].[dbo].[T_Result] R
WHERE IFFLAG = 'N'
AND STATION_ID IN
(
SELECT CODEVALUE FROM T_CODEDETAIL WHERE COMPANY_ID = 'GM' AND CODE = 'DEV_NAME' AND PROG_ID = 'TORQUE' AND INFO1 = 'N'
)
AND NOT EXISTS (SELECT CreateDate, STATION_ID FROM T_RESULT T WHERE T.CreateDate = R.CreateDate AND T.STATION_ID = R.STATION_ID)
--돌면서작업할커서확인..
declare CusorInterface CURSOR
LOCAL
FORWARD_ONLY
STATIC --static:이후에반영되는데이터를감지못함/key set:키의삭제수정에대해서는감지insert에대해서는감지못함/dynamic:모두감지
READ_ONLY
FOR
SELECT TOP 100 CreateDate, PONO, TrimInSeq, VIN, CarType, TotalResult, STATION_ID, T01_N01, T01_N02, T01_N03, T02_N01, T02_N02, T02_N03, T03_N01, T03_N02, T03_N03, T04_N01,
T04_N02, T04_N03, T05_N01, T05_N02, T05_N03, T06_N01, T06_N02, T06_N03, T07_N01, T07_N02, T07_N03, T08_N01, T08_N02, T08_N03, T09_N01, T09_N02, T09_N03, T10_N01,
T10_N02, T10_N03, T11_N01, T11_N02, T11_N03, T12_N01, T12_N02, T12_N03, T13_N01, T13_N02, T13_N03, T14_N01, T14_N02, T14_N03, T15_N01, T15_N02, T15_N03, T16_N01,
T16_N02, T16_N03, T17_N01, T17_N02, T17_N03, T18_N01, T18_N02, T18_N03, T19_N01, T19_N02, T19_N03, T20_N01, T20_N02, T20_N03, T21_N01, T21_N02, T21_N03, T22_N01,
T22_N02, T22_N03, T23_N01, T23_N02, T23_N03, T24_N01, T24_N02, T24_N03, T25_N01, T25_N02, T25_N03
FROM [LNK_TORQUE].[TorqueTool].[dbo].[T_Result] R
WHERE IFFLAG = 'N'
AND STATION_ID IN
(
SELECT CODEVALUE FROM T_CODEDETAIL WHERE COMPANY_ID = 'GM' AND CODE = 'DEV_NAME' AND PROG_ID = 'TORQUE' AND INFO1 = 'N'
)
AND NOT EXISTS (SELECT CreateDate, STATION_ID FROM T_RESULT T WHERE T.CreateDate = R.CreateDate AND T.STATION_ID = R.STATION_ID)
--2.인터페이별로작업을처리한다.
OPEN CusorInterface
Fetch next from CusorInterface into
@CreateDate, @PONO, @TrimInSeq, @VIN, @CarType, @TotalResult, @STATION_ID, @T01_N01, @T01_N02, @T01_N03, @T02_N01, @T02_N02, @T02_N03, @T03_N01, @T03_N02, @T03_N03, @T04_N01,
@T04_N02, @T04_N03, @T05_N01, @T05_N02, @T05_N03, @T06_N01, @T06_N02, @T06_N03, @T07_N01, @T07_N02, @T07_N03, @T08_N01, @T08_N02, @T08_N03, @T09_N01, @T09_N02, @T09_N03, @T10_N01,
@T10_N02, @T10_N03, @T11_N01, @T11_N02, @T11_N03, @T12_N01, @T12_N02, @T12_N03, @T13_N01, @T13_N02, @T13_N03, @T14_N01, @T14_N02, @T14_N03, @T15_N01, @T15_N02, @T15_N03, @T16_N01,
@T16_N02, @T16_N03, @T17_N01, @T17_N02, @T17_N03, @T18_N01, @T18_N02, @T18_N03, @T19_N01, @T19_N02, @T19_N03, @T20_N01, @T20_N02, @T20_N03, @T21_N01, @T21_N02, @T21_N03, @T22_N01,
@T22_N02, @T22_N03, @T23_N01, @T23_N02, @T23_N03, @T24_N01, @T24_N02, @T24_N03, @T25_N01, @T25_N02, @T25_N03
WHILE @@FETCH_STATUS=0
begin
--데이터 인서트
INSERT INTO T_RESULT
(
CreateDate, PONO, TrimInSeq, VIN, CarType, TotalResult, STATION_ID, T01_N01, T01_N02, T01_N03, T02_N01, T02_N02, T02_N03, T03_N01, T03_N02, T03_N03, T04_N01,
T04_N02, T04_N03, T05_N01, T05_N02, T05_N03, T06_N01, T06_N02, T06_N03, T07_N01, T07_N02, T07_N03, T08_N01, T08_N02, T08_N03, T09_N01, T09_N02, T09_N03, T10_N01,
T10_N02, T10_N03, T11_N01, T11_N02, T11_N03, T12_N01, T12_N02, T12_N03, T13_N01, T13_N02, T13_N03, T14_N01, T14_N02, T14_N03, T15_N01, T15_N02, T15_N03, T16_N01,
T16_N02, T16_N03, T17_N01, T17_N02, T17_N03, T18_N01, T18_N02, T18_N03, T19_N01, T19_N02, T19_N03, T20_N01, T20_N02, T20_N03, T21_N01, T21_N02, T21_N03, T22_N01,
T22_N02, T22_N03, T23_N01, T23_N02, T23_N03, T24_N01, T24_N02, T24_N03, T25_N01, T25_N02, T25_N03,
IFFLAG, IFDATE
)
VALUES
(
@CreateDate, @PONO, @TrimInSeq, @VIN, @CarType, @TotalResult, @STATION_ID, @T01_N01, @T01_N02, @T01_N03, @T02_N01, @T02_N02, @T02_N03, @T03_N01, @T03_N02, @T03_N03, @T04_N01,
@T04_N02, @T04_N03, @T05_N01, @T05_N02, @T05_N03, @T06_N01, @T06_N02, @T06_N03, @T07_N01, @T07_N02, @T07_N03, @T08_N01, @T08_N02, @T08_N03, @T09_N01, @T09_N02, @T09_N03, @T10_N01,
@T10_N02, @T10_N03, @T11_N01, @T11_N02, @T11_N03, @T12_N01, @T12_N02, @T12_N03, @T13_N01, @T13_N02, @T13_N03, @T14_N01, @T14_N02, @T14_N03, @T15_N01, @T15_N02, @T15_N03, @T16_N01,
@T16_N02, @T16_N03, @T17_N01, @T17_N02, @T17_N03, @T18_N01, @T18_N02, @T18_N03, @T19_N01, @T19_N02, @T19_N03, @T20_N01, @T20_N02, @T20_N03, @T21_N01, @T21_N02, @T21_N03, @T22_N01,
@T22_N02, @T22_N03, @T23_N01, @T23_N02, @T23_N03, @T24_N01, @T24_N02, @T24_N03, @T25_N01, @T25_N02, @T25_N03,
'T', GETDATE()
)
--ifflag update
UPDATE [LNK_TORQUE].[TorqueTool].[dbo].[T_Result]
SET IFFLAG = 'Y',
IFDATE = GETDATE()
WHERE CreateDate = @CreateDate
AND STATION_ID = @STATION_ID
set @cnt = @CNT + 1
SET @DT2 = GETDATE()
--PRINT '임시 테이블 데이트 입력 완료' + CONVERT(VARCHAR(20), @DT1,120) + ' / ' + CONVERT(VARCHAR(20), @DT2 - @DT1,120)
SET @DT1 = GETDATE()
Fetch next from CusorInterface into
@CreateDate, @PONO, @TrimInSeq, @VIN, @CarType, @TotalResult, @STATION_ID, @T01_N01, @T01_N02, @T01_N03, @T02_N01, @T02_N02, @T02_N03, @T03_N01, @T03_N02, @T03_N03, @T04_N01,
@T04_N02, @T04_N03, @T05_N01, @T05_N02, @T05_N03, @T06_N01, @T06_N02, @T06_N03, @T07_N01, @T07_N02, @T07_N03, @T08_N01, @T08_N02, @T08_N03, @T09_N01, @T09_N02, @T09_N03, @T10_N01,
@T10_N02, @T10_N03, @T11_N01, @T11_N02, @T11_N03, @T12_N01, @T12_N02, @T12_N03, @T13_N01, @T13_N02, @T13_N03, @T14_N01, @T14_N02, @T14_N03, @T15_N01, @T15_N02, @T15_N03, @T16_N01,
@T16_N02, @T16_N03, @T17_N01, @T17_N02, @T17_N03, @T18_N01, @T18_N02, @T18_N03, @T19_N01, @T19_N02, @T19_N03, @T20_N01, @T20_N02, @T20_N03, @T21_N01, @T21_N02, @T21_N03, @T22_N01,
@T22_N02, @T22_N03, @T23_N01, @T23_N02, @T23_N03, @T24_N01, @T24_N02, @T24_N03, @T25_N01, @T25_N02, @T25_N03
end
close CusorInterface
deallocate CusorInterface
PRINT '데이터 인서트 완료' + CONVERT(VARCHAR(20), @DT1,120) + ' / ' + CONVERT(VARCHAR(20), @DT2 - @DT1,120)
SELECT @TCnt TotalCount, @cnt WorkCount, @TCnt-@cnt RemainQty
END
";
sql.Excute_Query(qry, "");
}
/// <summary>
/// data base를 생성 한다.
/// </summary>
/// <param name="conn"></param>
/// <param name="dbName"></param>
/// <param name="path"></param>
public static void db_create(MsSQL.strConnect conn, string dbName, string path)
{
MsSQL sql = new MsSQL(conn);
string qry = string.Format(@"
CREATE DATABASE [{0}] ON PRIMARY
( NAME = N'{0}', FILENAME = N'{1}{0}.mdf' , SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'{0}_log', FILENAME = N'{1}{0}.ldf' , SIZE = 51200KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [{0}] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [{0}] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [{0}] SET ANSI_NULLS OFF
ALTER DATABASE [{0}] SET ANSI_PADDING OFF
ALTER DATABASE [{0}] SET ANSI_WARNINGS OFF
ALTER DATABASE [{0}] SET ARITHABORT OFF
ALTER DATABASE [{0}] SET AUTO_CLOSE ON
ALTER DATABASE [{0}] SET AUTO_SHRINK OFF
ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [{0}] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [{0}] SET CURSOR_DEFAULT GLOBAL
ALTER DATABASE [{0}] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [{0}] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [{0}] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [{0}] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [{0}] SET DISABLE_BROKER
ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [{0}] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [{0}] SET TRUSTWORTHY OFF
ALTER DATABASE [{0}] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [{0}] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [{0}] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [{0}] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [{0}] SET RECOVERY SIMPLE
ALTER DATABASE [{0}] SET MULTI_USER
ALTER DATABASE [{0}] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [{0}] SET DB_CHAINING OFF
ALTER DATABASE [{0}] SET READ_WRITE
", dbName, path);
sql.Excute_Query(qry, "");
}
public static void tables_create(MsSQL.strConnect conn)
{
MsSQL sql = new MsSQL(conn);
string qry = @"
CREATE TABLE [dbo].[T_Result](
[CreateDate] [datetime] NOT NULL,
[PONO] [nvarchar](6) NOT NULL,
[TrimInSeq] [nvarchar](4) NOT NULL,
[VIN] [nvarchar](17) NOT NULL,
[CarType] [nvarchar](3) NOT NULL,
[TotalResult] [nvarchar](2) NULL,
[STATION_ID] [nchar](10) NOT NULL,
[T01_N01] [nvarchar](4) NULL,
[T01_N02] [nvarchar](4) NULL,
[T01_N03] [nvarchar](2) NULL,
[T02_N01] [nvarchar](4) NULL,
[T02_N02] [nvarchar](4) NULL,
[T02_N03] [nvarchar](2) NULL,
[T03_N01] [nvarchar](4) NULL,
[T03_N02] [nvarchar](4) NULL,
[T03_N03] [nvarchar](2) NULL,
[T04_N01] [nvarchar](4) NULL,
[T04_N02] [nvarchar](4) NULL,
[T04_N03] [nvarchar](2) NULL,
[T05_N01] [nvarchar](4) NULL,
[T05_N02] [nvarchar](4) NULL,
[T05_N03] [nvarchar](2) NULL,
[T06_N01] [nvarchar](4) NULL,
[T06_N02] [nvarchar](4) NULL,
[T06_N03] [nvarchar](2) NULL,
[T07_N01] [nvarchar](4) NULL,
[T07_N02] [nvarchar](4) NULL,
[T07_N03] [nvarchar](2) NULL,
[T08_N01] [nvarchar](4) NULL,
[T08_N02] [nvarchar](4) NULL,
[T08_N03] [nvarchar](2) NULL,
[T09_N01] [nvarchar](4) NULL,
[T09_N02] [nvarchar](4) NULL,
[T09_N03] [nvarchar](2) NULL,
[T10_N01] [nvarchar](4) NULL,
[T10_N02] [nvarchar](4) NULL,
[T10_N03] [nvarchar](2) NULL,
[T11_N01] [nvarchar](4) NULL,
[T11_N02] [nvarchar](4) NULL,
[T11_N03] [nvarchar](2) NULL,
[T12_N01] [nvarchar](4) NULL,
[T12_N02] [nvarchar](4) NULL,
[T12_N03] [nvarchar](2) NULL,
[T13_N01] [nvarchar](4) NULL,
[T13_N02] [nvarchar](4) NULL,
[T13_N03] [nvarchar](2) NULL,
[T14_N01] [nvarchar](4) NULL,
[T14_N02] [nvarchar](4) NULL,
[T14_N03] [nvarchar](2) NULL,
[T15_N01] [nvarchar](4) NULL,
[T15_N02] [nvarchar](4) NULL,
[T15_N03] [nvarchar](2) NULL,
[T16_N01] [nvarchar](4) NULL,
[T16_N02] [nvarchar](4) NULL,
[T16_N03] [nvarchar](2) NULL,
[T17_N01] [nvarchar](4) NULL,
[T17_N02] [nvarchar](4) NULL,
[T17_N03] [nvarchar](2) NULL,
[T18_N01] [nvarchar](4) NULL,
[T18_N02] [nvarchar](4) NULL,
[T18_N03] [nvarchar](2) NULL,
[T19_N01] [nvarchar](4) NULL,
[T19_N02] [nvarchar](4) NULL,
[T19_N03] [nvarchar](2) NULL,
[T20_N01] [nvarchar](4) NULL,
[T20_N02] [nvarchar](4) NULL,
[T20_N03] [nvarchar](2) NULL,
[T21_N01] [nvarchar](4) NULL,
[T21_N02] [nvarchar](4) NULL,
[T21_N03] [nvarchar](2) NULL,
[T22_N01] [nvarchar](4) NULL,
[T22_N02] [nvarchar](4) NULL,
[T22_N03] [nvarchar](2) NULL,
[T23_N01] [nvarchar](4) NULL,
[T23_N02] [nvarchar](4) NULL,
[T23_N03] [nvarchar](2) NULL,
[T24_N01] [nvarchar](4) NULL,
[T24_N02] [nvarchar](4) NULL,
[T24_N03] [nvarchar](2) NULL,
[T25_N01] [nvarchar](4) NULL,
[T25_N02] [nvarchar](4) NULL,
[T25_N03] [nvarchar](2) NULL,
[IFFLAG] [nchar](1) NULL CONSTRAINT [DF_T_Result_IFFLAG] DEFAULT ('N'),
[IFDATE] [datetime] NULL,
CONSTRAINT [PK_TResult] PRIMARY KEY CLUSTERED
(
[CreateDate] ASC,
[STATION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_RESULT_IFFLAG] ON [dbo].[T_Result]
(
[IFFLAG] ASC,
[STATION_ID] ASC,
[CreateDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE TABLE [dbo].[T_CODE](
[COMPANY_ID] [nvarchar](10) NOT NULL,
[PROG_ID] [nvarchar](10) NOT NULL,
[CODE] [nvarchar](10) NOT NULL,
[CODENAME] [nvarchar](100) NULL,
[CODETYPE] [nvarchar](1) NULL,
[INFO1DESC] [nvarchar](50) NULL,
[INFO1TYPE] [nvarchar](10) NULL,
[INFO2DESC] [nvarchar](50) NULL,
[INFO2TYPE] [nvarchar](10) NULL,
[INFO3DESC] [nvarchar](50) NULL,
[INFO3TYPE] [nvarchar](10) NULL,
[INFO4DESC] [nvarchar](50) NULL,
[INFO4TYPE] [nvarchar](10) NULL,
[INFO5DESC] [nvarchar](50) NULL,
[INFO5TYPE] [nvarchar](10) NULL,
[CREATEDATE] [date] NULL,
[CREATEUSER] [nvarchar](10) NULL,
[LASTUPDATEDATE] [date] NULL,
[LASTUPDATEUSER] [nvarchar](10) NULL,
CONSTRAINT [PK_T_CODE] PRIMARY KEY CLUSTERED
(
[COMPANY_ID] ASC,
[PROG_ID] ASC,
[CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[T_CODEDETAIL](
[COMPANY_ID] [nvarchar](10) NOT NULL,
[PROG_ID] [nvarchar](10) NOT NULL,
[CODE] [nvarchar](10) NOT NULL,
[CODEVALUE] [nvarchar](10) NOT NULL,
[CODEVALUENAME] [nvarchar](100) NULL,
[INFO1] [nvarchar](100) NULL,
[INFO2] [nvarchar](100) NULL,
[INFO3] [nvarchar](100) NULL,
[INFO4] [nvarchar](100) NULL,
[INFO5] [nvarchar](100) NULL,
[CREATEDATE] [date] NULL,
[CREATEUSER] [nvarchar](10) NULL,
[LASTUPDATEDATE] [date] NULL,
[LASTUPDATEUSER] [nvarchar](10) NULL,
[PRIORITY] [int] NULL,
CONSTRAINT [PK_T_CODEDETAIL] PRIMARY KEY CLUSTERED
(
[COMPANY_ID] ASC,
[PROG_ID] ASC,
[CODE] ASC,
[CODEVALUE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
";
sql.Excute_Query(qry, "");
}
public DBSetting(MsSQL.strConnect _sql)
{
dbType = enDBType.MsSQL;
sql = _sql;
Form_Init();
}
