搜尋 DB 結果：
從A主機DB直接讀取B主機的DB，使用圖形介面一直無法設定成功，改用SQL語法一下就搞定了。
操作界面：
T-SQL:
操作界面：
T-SQL:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'連線名稱',
@srvproduct=N'SQLNCLI',
@provider=N'SQLOLEDB',
@datasrc=N'192.168.8.198\SQL2008'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'連線名稱',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'資料庫登入帳號',
@rmtpassword = N'資料庫連線密碼'
GO
Reiko, 2019/10/30 下午 03:45:41
查現有 SQL Server 中登入帳號的 SID 語法:
建立使用者語法
組合如下:
記得要改密碼 (XXX) 和 'username' ..
select sid, loginname, dbname from dbo.syslogins;
建立使用者語法
CREATE LOGIN [loginname] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[dbname ], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,
SID = sid
組合如下:
select 'CREATE LOGIN [' + loginname + '] WITH PASSWORD=N''XXX'', DEFAULT_DATABASE=[' + dbname + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,SID =' + master.dbo.fn_varbintohexstr(sid) from dbo.syslogins Where loginname = 'username';
記得要改密碼 (XXX) 和 'username' ..
Bike, 2019/6/6 下午 03:30:08
如下, 要記得修改 database_name, userOLD, userNEW 三個參數
CREATE TABLE #Command
(
Id int NOT NULL IDENTITY (1, 1),
command nvarchar(MAX) NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
USE database_name -- Use the database from which you want to extract the permissions
GO
SET NOCOUNT ON
DECLARE @OldUser sysname, @NewUser sysname
SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from
SET @NewUser = 'userNEW' --The user or role to which to copy the permissions to
insert into #Command(command)
Select convert(nvarchar(max), '--Database Context')
insert into #Command(command)
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME())
insert into #Command(command)
SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)
insert into #Command(command)
SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
insert into #Command(command)
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC
insert into #Command(command)
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Select command from #Command order by Id
drop table #Command
Bike, 2019/6/5 下午 08:42:56
建立兩個表 city 和 area, 並填入值:
CREATE TABLE [dbo].[city](
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
PRIMARY KEY CLUSTERED
(
[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]
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (1, '基隆市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (2, '台北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (3, '新北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (4, '桃園市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (5, '新竹市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (6, '新竹縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (7, '苗栗縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (8, '台中市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (9, '彰化縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (10, '南投縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (11, '雲林縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (12, '嘉義市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (13, '嘉義縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (14, '台南市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (15, '高雄市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (16, '屏東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (17, '台東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (18, '花蓮縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (19, '宜蘭縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (20, '澎湖縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (21, '金門縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (22, '連江縣')
CREATE TABLE [dbo].[area](
[city_id] [int] NULL,
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
[post_code] [varchar](3) NULL,
PRIMARY KEY CLUSTERED
(
[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]
GO
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(1, 1, '仁愛區', '200')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(2, 1, '信義區', '201')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(3, 1, '中正區', '202')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(4, 1, '中山區', '203')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(5, 1, '安樂區', '204')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(6, 1, '暖暖區', '205')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(7, 1, '七堵區', '206')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(8, 2, '中正區', '100')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(9, 2, '大同區', '103')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(10, 2, '中山區', '104')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(11, 2, '松山區', '105')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(12, 2, '大安區', '106')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(13, 2, '萬華區', '108')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(14, 2, '信義區', '110')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(15, 2, '士林區', '111')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(16, 2, '北投區', '112')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(17, 2, '內湖區', '114')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(18, 2, '南港區', '115')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(19, 2, '文山區', '116')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(20, 3, '萬里區', '207')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(21, 3, '金山區', '208')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(22, 3, '板橋區', '220')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(23, 3, '汐止區', '221')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(24, 3, '深坑區', '222')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(25, 3, '石碇區', '223')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(26, 3, '瑞芳區', '224')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(27, 3, '平溪區', '226')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(28, 3, '雙溪區', '227')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(29, 3, '貢寮區', '228')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(30, 3, '新店區', '231')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(31, 3, '坪林區', '232')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(32, 3, '烏來區', '233')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(33, 3, '永和區', '234')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(34, 3, '中和區', '235')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(35, 3, '土城區', '236')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(36, 3, '三峽區', '237')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(37, 3, '樹林區', '238')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(38, 3, '鶯歌區', '239')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(39, 3, '三重區', '241')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(40, 3, '新莊區', '242')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(41, 3, '泰山區', '243')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(42, 3, '林口區', '244')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(43, 3, '蘆洲區', '247')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(44, 3, '五股區', '248')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(45, 3, '八里區', '249')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(46, 3, '淡水區', '251')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(47, 3, '三芝區', '252')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(48, 3, '石門區', '253')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(49, 4, '中壢區', '320')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(50, 4, '平鎮區', '324')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(51, 4, '龍潭區', '325')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(52, 4, '楊梅區', '326')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(53, 4, '新屋區', '327')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(54, 4, '觀音區', '328')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(55, 4, '桃園區', '330')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(56, 4, '龜山區', '333')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(57, 4, '八德區', '334')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(58, 4, '大溪區', '335')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(59, 4, '復興區', '336')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(60, 4, '大園區', '337')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(61, 4, '蘆竹區', '338')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(62, 5, '新竹市', '300')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(63, 6, '竹北市', '302')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(64, 6, '湖口鄉', '303')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(65, 6, '新豐鄉', '304')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(66, 6, '新埔鎮', '305')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(67, 6, '關西鎮', '306')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(68, 6, '芎林鄉', '307')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(69, 6, '寶山鄉', '308')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(70, 6, '竹東鎮', '310')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(71, 6, '五峰鄉', '311')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(72, 6, '橫山鄉', '312')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(73, 6, '尖石鄉', '313')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(74, 6, '北埔鄉', '314')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(75, 6, '峨眉鄉', '315')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(76, 7, '竹南鎮', '350')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(77, 7, '頭份鎮', '351')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(78, 7, '三灣鄉', '352')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(79, 7, '南庄鄉', '353')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(80, 7, '獅潭鄉', '354')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(81, 7, '後龍鎮', '356')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(82, 7, '通霄鎮', '357')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(83, 7, '苑裡鎮', '358')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(84, 7, '苗栗市', '360')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(85, 7, '造橋鄉', '361')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(86, 7, '頭屋鄉', '362')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(87, 7, '公館鄉', '363')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(88, 7, '大湖鄉', '364')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(89, 7, '泰安鄉', '365')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(90, 7, '銅鑼鄉', '366')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(91, 7, '三義鄉', '367')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(92, 7, '西湖鄉', '368')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(93, 7, '卓蘭鎮', '369')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(94, 8, '中區', '400')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(95, 8, '東區', '401')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(96, 8, '南區', '402')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(97, 8, '西區', '403')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(98, 8, '北區', '404')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(99, 8, '北屯區', '406')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(100, 8, '西屯區', '407')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(101, 8, '南屯區', '408')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(102, 8, '太平區', '411')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(103, 8, '大里區', '412')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(104, 8, '霧峰區', '413')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(105, 8, '烏日區', '414')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(106, 8, '豐原區', '420')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(107, 8, '后里區', '421')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(108, 8, '石岡區', '422')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(109, 8, '東勢區', '423')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(110, 8, '和平區', '424')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(111, 8, '新社區', '426')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(112, 8, '潭子區', '427')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(113, 8, '大雅區', '428')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(114, 8, '神岡區', '429')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(115, 8, '大肚區', '432')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(116, 8, '沙鹿區', '433')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(117, 8, '龍井區', '434')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(118, 8, '梧棲區', '435')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(119, 8, '清水區', '436')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(120, 8, '大甲區', '437')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(121, 8, '外埔區', '438')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(122, 8, '大安區', '439')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(123, 9, '彰化市', '500')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(124, 9, '芬園鄉', '502')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(125, 9, '花壇鄉', '503')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(126, 9, '秀水鄉', '504')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(127, 9, '鹿港鎮', '505')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(128, 9, '福興鄉', '506')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(129, 9, '線西鄉', '507')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(130, 9, '和美鄉', '508')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(131, 9, '伸港鄉', '509')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(132, 9, '員林鎮', '510')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(133, 9, '社頭鄉', '511')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(134, 9, '永靖鄉', '512')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(135, 9, '埔心鄉', '513')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(136, 9, '溪湖鎮', '514')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(137, 9, '大村鄉', '515')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(138, 9, '埔鹽鄉', '516')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(139, 9, '田中鎮', '520')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(140, 9, '北斗鎮', '521')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(141, 9, '田尾鄉', '522')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(142, 9, '埤頭鄉', '523')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(143, 9, '溪州鄉', '524')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(144, 9, '竹塘鄉', '525')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(145, 9, '二林鎮', '526')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(146, 9, '大城鄉', '527')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(147, 9, '芳苑鄉', '528')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(148, 9, '二水鄉', '530')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(149, 10, '南投市', '540')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(150, 10, '中寮鄉', '541')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(151, 10, '草屯鎮', '542')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(152, 10, '國姓鄉', '544')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(153, 10, '埔里鎮', '545')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(154, 10, '仁愛鄉', '546')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(155, 10, '名間鄉', '551')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(156, 10, '集集鎮', '552')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(157, 10, '水里鄉', '553')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(158, 10, '魚池鄉', '555')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(159, 10, '信義鄉', '556')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(160, 10, '竹山鎮', '557')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(161, 10, '鹿谷鄉', '558')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(162, 11, '斗南鎮', '630')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(163, 11, '大埤鄉', '631')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(164, 11, '虎尾鎮', '632')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(165, 11, '土庫鎮', '633')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(166, 11, '褒忠鄉', '634')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(167, 11, '東勢鄉', '635')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(168, 11, '台西鄉', '636')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(169, 11, '崙背鄉', '637')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(170, 11, '麥寮鄉', '638')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(171, 11, '斗六市', '640')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(172, 11, '林內鄉', '643')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(173, 11, '古坑鄉', '646')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(174, 11, '莿桐鄉', '647')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(175, 11, '西螺鎮', '648')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(176, 11, '二崙鄉', '649')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(177, 11, '北港鎮', '651')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(178, 11, '水林鄉', '652')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(179, 11, '口湖鄉', '653')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(180, 11, '四湖鄉', '654')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(181, 11, '元長鄉', '655')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(182, 12, '嘉義市', '600')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(183, 13, '番路鄉', '602')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(184, 13, '梅山鄉', '603')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(185, 13, '竹崎鄉', '604')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(186, 13, '阿里山', '605')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(187, 13, '中埔鄉', '606')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(188, 13, '大埔鄉', '607')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(189, 13, '水上鄉', '608')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(190, 13, '鹿草鄉', '611')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(191, 13, '太保鄉', '612')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(192, 13, '朴子市', '613')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(193, 13, '東石鄉', '614')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(194, 13, '六腳鄉', '615')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(195, 13, '新港鄉', '616')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(196, 13, '民雄鄉', '621')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(197, 13, '大林鎮', '622')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(198, 13, '溪口鄉', '623')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(199, 13, '義竹鄉', '624')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(200, 13, '布袋鄉', '625')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(201, 14, '中西區', '700')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(202, 14, '東區', '701')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(203, 14, '南區', '702')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(204, 14, '北區', '704')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(205, 14, '安平區', '708')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(206, 14, '安南區', '709')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(207, 14, '永康區', '710')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(208, 14, '歸仁區', '711')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(209, 14, '新化區', '712')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(210, 14, '左鎮區', '713')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(211, 14, '玉井區', '714')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(212, 14, '楠西區', '715')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(213, 14, '南化區', '716')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(214, 14, '仁德區', '717')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(215, 14, '關廟區', '718')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(216, 14, '龍崎區', '719')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(217, 14, '官田區', '720')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(218, 14, '麻豆區', '721')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(219, 14, '佳里區', '722')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(220, 14, '西港區', '723')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(221, 14, '七股區', '724')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(222, 14, '將軍區', '725')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(223, 14, '學甲區', '726')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(224, 14, '北門區', '727')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(225, 14, '新營區', '730')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(226, 14, '後壁區', '731')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(227, 14, '白河區', '732')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(228, 14, '東山區', '733')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(229, 14, '六甲區', '734')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(230, 14, '下營區', '735')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(231, 14, '柳營區', '736')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(232, 14, '鹽水區', '737')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(233, 14, '善化區', '741')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(234, 14, '大內區', '742')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(235, 14, '山上區', '743')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(236, 14, '新市區', '744')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(237, 14, '安定區', '745')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(238, 15, '新興區', '800')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(239, 15, '前金區', '801')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(240, 15, '苓雅區', '802')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(241, 15, '鹽埕區', '803')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(242, 15, '鼓山區', '804')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(243, 15, '旗津區', '805')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(244, 15, '前鎮區', '806')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(245, 15, '三民區', '807')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(246, 15, '楠梓區', '811')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(247, 15, '小港區', '812')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(248, 15, '左營區', '813')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(249, 15, '仁武區', '814')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(250, 15, '大社區', '815')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(251, 15, '岡山區', '820')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(252, 15, '路竹區', '821')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(253, 15, '阿蓮區', '822')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(254, 15, '田寮區', '823')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(255, 15, '燕巢區', '824')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(256, 15, '橋頭區', '825')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(257, 15, '梓官區', '826')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(258, 15, '彌陀區', '827')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(259, 15, '永安區', '828')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(260, 15, '湖內區', '829')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(261, 15, '鳳山區', '830')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(262, 15, '大寮區', '831')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(263, 15, '林園區', '832')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(264, 15, '鳥松區', '833')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(265, 15, '大樹區', '840')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(266, 15, '旗山區', '842')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(267, 15, '美濃區', '843')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(268, 15, '六龜區', '844')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(269, 15, '內門區', '845')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(270, 15, '杉林區', '846')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(271, 15, '甲仙區', '847')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(272, 15, '桃源區', '848')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(273, 15, '那瑪夏區', '849')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(274, 15, '茂林區', '851')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(275, 15, '茄萣區', '852')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(276, 15, '東沙', '817')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(277, 15, '南沙', '819')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(278, 16, '屏東市', '900')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(279, 16, '三地鄉', '901')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(280, 16, '霧台鄉', '902')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(281, 16, '瑪家鄉', '903')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(282, 16, '九如鄉', '904')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(283, 16, '里港鄉', '905')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(284, 16, '高樹鄉', '906')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(285, 16, '鹽埔鄉', '907')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(286, 16, '長治鄉', '908')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(287, 16, '麟洛鄉', '909')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(288, 16, '竹田鄉', '911')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(289, 16, '內埔鄉', '912')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(290, 16, '萬丹鄉', '913')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(291, 16, '潮州鎮', '920')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(292, 16, '泰武鄉', '921')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(293, 16, '來義鄉', '922')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(294, 16, '萬巒鄉', '923')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(295, 16, '崁頂鄉', '924')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(296, 16, '新埤鄉', '925')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(297, 16, '南州鄉', '926')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(298, 16, '林邊鄉', '927')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(299, 16, '東港鄉', '928')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(300, 16, '琉球鄉', '929')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(301, 16, '佳冬鄉', '931')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(302, 16, '新園鄉', '932')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(303, 16, '枋寮鄉', '940')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(304, 16, '枋山鄉', '941')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(305, 16, '春日鄉', '942')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(306, 16, '獅子鄉', '943')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(307, 16, '車城鄉', '944')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(308, 16, '牡丹鄉', '945')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(309, 16, '恆春鎮', '946')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(310, 16, '滿洲鄉', '947')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(311, 17, '台東市', '950')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(312, 17, '綠島鄉', '951')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(313, 17, '蘭嶼鄉', '952')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(314, 17, '延平鄉', '953')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(315, 17, '卑南鄉', '954')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(316, 17, '鹿野鄉', '955')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(317, 17, '關山鎮', '956')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(318, 17, '海端鄉', '957')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(319, 17, '池上鄉', '958')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(320, 17, '東河鄉', '959')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(321, 17, '成功鎮', '961')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(322, 17, '長濱鄉', '962')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(323, 17, '太麻里', '963')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(324, 17, '金峰鄉', '964')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(325, 17, '大武鄉', '965')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(326, 17, '達仁鄉', '966')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(327, 18, '花蓮市', '970')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(328, 18, '新城鄉', '971')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(329, 18, '秀林鄉', '972')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(330, 18, '吉安鄉', '973')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(331, 18, '壽豐鄉', '974')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(332, 18, '鳳林鎮', '975')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(333, 18, '光復鄉', '976')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(334, 18, '豐濱鄉', '977')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(335, 18, '瑞穗鄉', '978')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(336, 18, '萬榮鄉', '979')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(337, 18, '玉里鎮', '981')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(338, 18, '卓溪鄉', '982')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(339, 18, '富里鄉', '983')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(340, 19, '宜蘭巿', '260')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(341, 19, '頭城鎮', '261')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(342, 19, '礁溪鄉', '262')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(343, 19, '壯圍鄉', '263')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(344, 19, '員山鄉', '264')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(345, 19, '羅東鎮', '265')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(346, 19, '三星鄉', '266')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(347, 19, '大同鄉', '267')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(348, 19, '五結鄉', '268')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(349, 19, '冬山鄉', '269')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(350, 19, '蘇澳鎮', '270')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(351, 19, '南澳鄉', '272')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(352, 19, '釣魚台', '290')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(353, 20, '馬公市', '880')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(354, 20, '西嶼鄉', '881')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(355, 20, '望安鄉', '882')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(356, 20, '七美鄉', '883')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(357, 20, '白沙鄉', '884')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(358, 20, '湖西鄉', '885')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(359, 21, '金沙鎮', '890')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(360, 21, '金湖鎮', '891')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(361, 21, '金寧鄉', '892')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(362, 21, '金城鎮', '893')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(363, 21, '烈嶼鄉', '894')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(364, 21, '烏坵', '896')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(365, 22, '南竿', '209')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(366, 22, '北竿', '210')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(367, 22, '莒光', '211')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(368, 22, '東引', '212')
CREATE TABLE [dbo].[city](
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
PRIMARY KEY CLUSTERED
(
[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]
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (1, '基隆市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (2, '台北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (3, '新北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (4, '桃園市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (5, '新竹市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (6, '新竹縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (7, '苗栗縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (8, '台中市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (9, '彰化縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (10, '南投縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (11, '雲林縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (12, '嘉義市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (13, '嘉義縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (14, '台南市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (15, '高雄市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (16, '屏東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (17, '台東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (18, '花蓮縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (19, '宜蘭縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (20, '澎湖縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (21, '金門縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (22, '連江縣')
CREATE TABLE [dbo].[area](
[city_id] [int] NULL,
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
[post_code] [varchar](3) NULL,
PRIMARY KEY CLUSTERED
(
[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]
GO
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(1, 1, '仁愛區', '200')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(2, 1, '信義區', '201')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(3, 1, '中正區', '202')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(4, 1, '中山區', '203')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(5, 1, '安樂區', '204')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(6, 1, '暖暖區', '205')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(7, 1, '七堵區', '206')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(8, 2, '中正區', '100')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(9, 2, '大同區', '103')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(10, 2, '中山區', '104')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(11, 2, '松山區', '105')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(12, 2, '大安區', '106')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(13, 2, '萬華區', '108')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(14, 2, '信義區', '110')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(15, 2, '士林區', '111')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(16, 2, '北投區', '112')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(17, 2, '內湖區', '114')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(18, 2, '南港區', '115')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(19, 2, '文山區', '116')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(20, 3, '萬里區', '207')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(21, 3, '金山區', '208')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(22, 3, '板橋區', '220')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(23, 3, '汐止區', '221')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(24, 3, '深坑區', '222')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(25, 3, '石碇區', '223')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(26, 3, '瑞芳區', '224')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(27, 3, '平溪區', '226')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(28, 3, '雙溪區', '227')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(29, 3, '貢寮區', '228')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(30, 3, '新店區', '231')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(31, 3, '坪林區', '232')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(32, 3, '烏來區', '233')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(33, 3, '永和區', '234')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(34, 3, '中和區', '235')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(35, 3, '土城區', '236')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(36, 3, '三峽區', '237')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(37, 3, '樹林區', '238')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(38, 3, '鶯歌區', '239')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(39, 3, '三重區', '241')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(40, 3, '新莊區', '242')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(41, 3, '泰山區', '243')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(42, 3, '林口區', '244')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(43, 3, '蘆洲區', '247')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(44, 3, '五股區', '248')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(45, 3, '八里區', '249')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(46, 3, '淡水區', '251')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(47, 3, '三芝區', '252')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(48, 3, '石門區', '253')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(49, 4, '中壢區', '320')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(50, 4, '平鎮區', '324')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(51, 4, '龍潭區', '325')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(52, 4, '楊梅區', '326')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(53, 4, '新屋區', '327')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(54, 4, '觀音區', '328')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(55, 4, '桃園區', '330')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(56, 4, '龜山區', '333')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(57, 4, '八德區', '334')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(58, 4, '大溪區', '335')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(59, 4, '復興區', '336')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(60, 4, '大園區', '337')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(61, 4, '蘆竹區', '338')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(62, 5, '新竹市', '300')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(63, 6, '竹北市', '302')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(64, 6, '湖口鄉', '303')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(65, 6, '新豐鄉', '304')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(66, 6, '新埔鎮', '305')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(67, 6, '關西鎮', '306')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(68, 6, '芎林鄉', '307')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(69, 6, '寶山鄉', '308')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(70, 6, '竹東鎮', '310')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(71, 6, '五峰鄉', '311')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(72, 6, '橫山鄉', '312')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(73, 6, '尖石鄉', '313')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(74, 6, '北埔鄉', '314')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(75, 6, '峨眉鄉', '315')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(76, 7, '竹南鎮', '350')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(77, 7, '頭份鎮', '351')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(78, 7, '三灣鄉', '352')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(79, 7, '南庄鄉', '353')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(80, 7, '獅潭鄉', '354')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(81, 7, '後龍鎮', '356')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(82, 7, '通霄鎮', '357')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(83, 7, '苑裡鎮', '358')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(84, 7, '苗栗市', '360')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(85, 7, '造橋鄉', '361')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(86, 7, '頭屋鄉', '362')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(87, 7, '公館鄉', '363')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(88, 7, '大湖鄉', '364')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(89, 7, '泰安鄉', '365')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(90, 7, '銅鑼鄉', '366')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(91, 7, '三義鄉', '367')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(92, 7, '西湖鄉', '368')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(93, 7, '卓蘭鎮', '369')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(94, 8, '中區', '400')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(95, 8, '東區', '401')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(96, 8, '南區', '402')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(97, 8, '西區', '403')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(98, 8, '北區', '404')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(99, 8, '北屯區', '406')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(100, 8, '西屯區', '407')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(101, 8, '南屯區', '408')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(102, 8, '太平區', '411')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(103, 8, '大里區', '412')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(104, 8, '霧峰區', '413')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(105, 8, '烏日區', '414')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(106, 8, '豐原區', '420')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(107, 8, '后里區', '421')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(108, 8, '石岡區', '422')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(109, 8, '東勢區', '423')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(110, 8, '和平區', '424')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(111, 8, '新社區', '426')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(112, 8, '潭子區', '427')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(113, 8, '大雅區', '428')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(114, 8, '神岡區', '429')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(115, 8, '大肚區', '432')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(116, 8, '沙鹿區', '433')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(117, 8, '龍井區', '434')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(118, 8, '梧棲區', '435')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(119, 8, '清水區', '436')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(120, 8, '大甲區', '437')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(121, 8, '外埔區', '438')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(122, 8, '大安區', '439')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(123, 9, '彰化市', '500')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(124, 9, '芬園鄉', '502')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(125, 9, '花壇鄉', '503')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(126, 9, '秀水鄉', '504')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(127, 9, '鹿港鎮', '505')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(128, 9, '福興鄉', '506')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(129, 9, '線西鄉', '507')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(130, 9, '和美鄉', '508')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(131, 9, '伸港鄉', '509')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(132, 9, '員林鎮', '510')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(133, 9, '社頭鄉', '511')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(134, 9, '永靖鄉', '512')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(135, 9, '埔心鄉', '513')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(136, 9, '溪湖鎮', '514')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(137, 9, '大村鄉', '515')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(138, 9, '埔鹽鄉', '516')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(139, 9, '田中鎮', '520')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(140, 9, '北斗鎮', '521')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(141, 9, '田尾鄉', '522')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(142, 9, '埤頭鄉', '523')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(143, 9, '溪州鄉', '524')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(144, 9, '竹塘鄉', '525')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(145, 9, '二林鎮', '526')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(146, 9, '大城鄉', '527')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(147, 9, '芳苑鄉', '528')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(148, 9, '二水鄉', '530')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(149, 10, '南投市', '540')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(150, 10, '中寮鄉', '541')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(151, 10, '草屯鎮', '542')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(152, 10, '國姓鄉', '544')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(153, 10, '埔里鎮', '545')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(154, 10, '仁愛鄉', '546')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(155, 10, '名間鄉', '551')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(156, 10, '集集鎮', '552')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(157, 10, '水里鄉', '553')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(158, 10, '魚池鄉', '555')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(159, 10, '信義鄉', '556')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(160, 10, '竹山鎮', '557')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(161, 10, '鹿谷鄉', '558')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(162, 11, '斗南鎮', '630')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(163, 11, '大埤鄉', '631')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(164, 11, '虎尾鎮', '632')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(165, 11, '土庫鎮', '633')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(166, 11, '褒忠鄉', '634')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(167, 11, '東勢鄉', '635')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(168, 11, '台西鄉', '636')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(169, 11, '崙背鄉', '637')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(170, 11, '麥寮鄉', '638')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(171, 11, '斗六市', '640')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(172, 11, '林內鄉', '643')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(173, 11, '古坑鄉', '646')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(174, 11, '莿桐鄉', '647')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(175, 11, '西螺鎮', '648')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(176, 11, '二崙鄉', '649')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(177, 11, '北港鎮', '651')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(178, 11, '水林鄉', '652')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(179, 11, '口湖鄉', '653')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(180, 11, '四湖鄉', '654')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(181, 11, '元長鄉', '655')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(182, 12, '嘉義市', '600')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(183, 13, '番路鄉', '602')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(184, 13, '梅山鄉', '603')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(185, 13, '竹崎鄉', '604')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(186, 13, '阿里山', '605')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(187, 13, '中埔鄉', '606')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(188, 13, '大埔鄉', '607')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(189, 13, '水上鄉', '608')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(190, 13, '鹿草鄉', '611')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(191, 13, '太保鄉', '612')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(192, 13, '朴子市', '613')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(193, 13, '東石鄉', '614')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(194, 13, '六腳鄉', '615')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(195, 13, '新港鄉', '616')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(196, 13, '民雄鄉', '621')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(197, 13, '大林鎮', '622')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(198, 13, '溪口鄉', '623')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(199, 13, '義竹鄉', '624')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(200, 13, '布袋鄉', '625')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(201, 14, '中西區', '700')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(202, 14, '東區', '701')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(203, 14, '南區', '702')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(204, 14, '北區', '704')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(205, 14, '安平區', '708')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(206, 14, '安南區', '709')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(207, 14, '永康區', '710')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(208, 14, '歸仁區', '711')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(209, 14, '新化區', '712')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(210, 14, '左鎮區', '713')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(211, 14, '玉井區', '714')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(212, 14, '楠西區', '715')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(213, 14, '南化區', '716')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(214, 14, '仁德區', '717')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(215, 14, '關廟區', '718')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(216, 14, '龍崎區', '719')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(217, 14, '官田區', '720')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(218, 14, '麻豆區', '721')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(219, 14, '佳里區', '722')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(220, 14, '西港區', '723')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(221, 14, '七股區', '724')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(222, 14, '將軍區', '725')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(223, 14, '學甲區', '726')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(224, 14, '北門區', '727')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(225, 14, '新營區', '730')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(226, 14, '後壁區', '731')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(227, 14, '白河區', '732')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(228, 14, '東山區', '733')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(229, 14, '六甲區', '734')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(230, 14, '下營區', '735')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(231, 14, '柳營區', '736')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(232, 14, '鹽水區', '737')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(233, 14, '善化區', '741')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(234, 14, '大內區', '742')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(235, 14, '山上區', '743')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(236, 14, '新市區', '744')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(237, 14, '安定區', '745')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(238, 15, '新興區', '800')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(239, 15, '前金區', '801')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(240, 15, '苓雅區', '802')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(241, 15, '鹽埕區', '803')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(242, 15, '鼓山區', '804')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(243, 15, '旗津區', '805')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(244, 15, '前鎮區', '806')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(245, 15, '三民區', '807')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(246, 15, '楠梓區', '811')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(247, 15, '小港區', '812')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(248, 15, '左營區', '813')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(249, 15, '仁武區', '814')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(250, 15, '大社區', '815')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(251, 15, '岡山區', '820')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(252, 15, '路竹區', '821')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(253, 15, '阿蓮區', '822')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(254, 15, '田寮區', '823')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(255, 15, '燕巢區', '824')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(256, 15, '橋頭區', '825')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(257, 15, '梓官區', '826')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(258, 15, '彌陀區', '827')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(259, 15, '永安區', '828')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(260, 15, '湖內區', '829')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(261, 15, '鳳山區', '830')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(262, 15, '大寮區', '831')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(263, 15, '林園區', '832')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(264, 15, '鳥松區', '833')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(265, 15, '大樹區', '840')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(266, 15, '旗山區', '842')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(267, 15, '美濃區', '843')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(268, 15, '六龜區', '844')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(269, 15, '內門區', '845')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(270, 15, '杉林區', '846')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(271, 15, '甲仙區', '847')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(272, 15, '桃源區', '848')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(273, 15, '那瑪夏區', '849')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(274, 15, '茂林區', '851')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(275, 15, '茄萣區', '852')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(276, 15, '東沙', '817')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(277, 15, '南沙', '819')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(278, 16, '屏東市', '900')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(279, 16, '三地鄉', '901')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(280, 16, '霧台鄉', '902')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(281, 16, '瑪家鄉', '903')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(282, 16, '九如鄉', '904')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(283, 16, '里港鄉', '905')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(284, 16, '高樹鄉', '906')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(285, 16, '鹽埔鄉', '907')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(286, 16, '長治鄉', '908')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(287, 16, '麟洛鄉', '909')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(288, 16, '竹田鄉', '911')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(289, 16, '內埔鄉', '912')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(290, 16, '萬丹鄉', '913')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(291, 16, '潮州鎮', '920')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(292, 16, '泰武鄉', '921')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(293, 16, '來義鄉', '922')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(294, 16, '萬巒鄉', '923')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(295, 16, '崁頂鄉', '924')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(296, 16, '新埤鄉', '925')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(297, 16, '南州鄉', '926')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(298, 16, '林邊鄉', '927')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(299, 16, '東港鄉', '928')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(300, 16, '琉球鄉', '929')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(301, 16, '佳冬鄉', '931')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(302, 16, '新園鄉', '932')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(303, 16, '枋寮鄉', '940')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(304, 16, '枋山鄉', '941')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(305, 16, '春日鄉', '942')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(306, 16, '獅子鄉', '943')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(307, 16, '車城鄉', '944')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(308, 16, '牡丹鄉', '945')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(309, 16, '恆春鎮', '946')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(310, 16, '滿洲鄉', '947')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(311, 17, '台東市', '950')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(312, 17, '綠島鄉', '951')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(313, 17, '蘭嶼鄉', '952')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(314, 17, '延平鄉', '953')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(315, 17, '卑南鄉', '954')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(316, 17, '鹿野鄉', '955')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(317, 17, '關山鎮', '956')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(318, 17, '海端鄉', '957')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(319, 17, '池上鄉', '958')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(320, 17, '東河鄉', '959')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(321, 17, '成功鎮', '961')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(322, 17, '長濱鄉', '962')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(323, 17, '太麻里', '963')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(324, 17, '金峰鄉', '964')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(325, 17, '大武鄉', '965')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(326, 17, '達仁鄉', '966')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(327, 18, '花蓮市', '970')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(328, 18, '新城鄉', '971')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(329, 18, '秀林鄉', '972')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(330, 18, '吉安鄉', '973')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(331, 18, '壽豐鄉', '974')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(332, 18, '鳳林鎮', '975')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(333, 18, '光復鄉', '976')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(334, 18, '豐濱鄉', '977')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(335, 18, '瑞穗鄉', '978')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(336, 18, '萬榮鄉', '979')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(337, 18, '玉里鎮', '981')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(338, 18, '卓溪鄉', '982')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(339, 18, '富里鄉', '983')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(340, 19, '宜蘭巿', '260')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(341, 19, '頭城鎮', '261')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(342, 19, '礁溪鄉', '262')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(343, 19, '壯圍鄉', '263')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(344, 19, '員山鄉', '264')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(345, 19, '羅東鎮', '265')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(346, 19, '三星鄉', '266')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(347, 19, '大同鄉', '267')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(348, 19, '五結鄉', '268')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(349, 19, '冬山鄉', '269')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(350, 19, '蘇澳鎮', '270')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(351, 19, '南澳鄉', '272')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(352, 19, '釣魚台', '290')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(353, 20, '馬公市', '880')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(354, 20, '西嶼鄉', '881')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(355, 20, '望安鄉', '882')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(356, 20, '七美鄉', '883')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(357, 20, '白沙鄉', '884')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(358, 20, '湖西鄉', '885')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(359, 21, '金沙鎮', '890')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(360, 21, '金湖鎮', '891')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(361, 21, '金寧鄉', '892')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(362, 21, '金城鎮', '893')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(363, 21, '烈嶼鄉', '894')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(364, 21, '烏坵', '896')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(365, 22, '南竿', '209')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(366, 22, '北竿', '210')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(367, 22, '莒光', '211')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(368, 22, '東引', '212')
Bike, 2018/8/7 下午 12:06:12
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id;
--這個步驟要看前一步驟出現的 Name , 修改 'DBMirroringEndPoint'
IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DBMirroringEndPoint')
DROP ENDPOINT DBMirroringEndPoint;
IF NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DBMirroringEndPoint')
CREATE ENDPOINT DBMirroringEndPoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED,
ROLE=ALL);
修改完畢, 在做 Security 設定時會看到新的 port "5023"
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id;
--這個步驟要看前一步驟出現的 Name , 修改 'DBMirroringEndPoint'
IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DBMirroringEndPoint')
DROP ENDPOINT DBMirroringEndPoint;
IF NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'DBMirroringEndPoint')
CREATE ENDPOINT DBMirroringEndPoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED,
ROLE=ALL);
修改完畢, 在做 Security 設定時會看到新的 port "5023"
Bike, 2018/7/26 上午 10:43:02
UW -> DB 物件產生器會產生兩個function
GetAllDataFromBaseTableWithCache(string OrderFields = "_Default", bool IsCopy = true )
以及
Get物件名稱FromCachedDT(string Key)
-----------------------------------------------------------
設計上是希望將所有資料Cache在記憶體，然後前端直接抓出資料，不須到資料庫抓資料
理論上網頁速度應該比較佳，實際上卻發現是效能殺手
原因在於 GetAllDataFromBaseTableWithCache 是抓出整個table的資料放到 Cache
然後又做了一件事，當有人要資料時，會把整個 DataTable 做 Copy() 再 return
也就是說，當DataTable有數千筆資料列時
每叫用一次 "Get物件名稱FromCachedDT(string Key)" 就會把數千筆資料整個複製一次
再從複製出來的DataTable Select 出一列 return
------------------------------------------------------------
解決方法:
Get物件名稱FromCachedDT(string Key) --> 改成一筆一筆資料做 Cache，這樣 Copy() 只會Copy一筆
實測上，原本頁面要跑1.8秒，調整後可以 0.4 秒就完成
-----------------------------------------------------------
結論:
PG產生器就把 GetAllDataFromBaseTableWithCache 拿掉吧
GetAllDataFromBaseTableWithCache(string OrderFields = "_Default", bool IsCopy = true )
以及
Get物件名稱FromCachedDT(string Key)
-----------------------------------------------------------
設計上是希望將所有資料Cache在記憶體，然後前端直接抓出資料，不須到資料庫抓資料
理論上網頁速度應該比較佳，實際上卻發現是效能殺手
原因在於 GetAllDataFromBaseTableWithCache 是抓出整個table的資料放到 Cache
然後又做了一件事，當有人要資料時，會把整個 DataTable 做 Copy() 再 return
也就是說，當DataTable有數千筆資料列時
每叫用一次 "Get物件名稱FromCachedDT(string Key)" 就會把數千筆資料整個複製一次
再從複製出來的DataTable Select 出一列 return
------------------------------------------------------------
解決方法:
Get物件名稱FromCachedDT(string Key) --> 改成一筆一筆資料做 Cache，這樣 Copy() 只會Copy一筆
實測上，原本頁面要跑1.8秒，調整後可以 0.4 秒就完成
-----------------------------------------------------------
結論:
PG產生器就把 GetAllDataFromBaseTableWithCache 拿掉吧
darren, 2018/5/21 下午 03:17:00
程式碼 1:
程式碼 2:
foreach (DataRow oRow in DT.Rows)
{
var oSP = new DB.StorePointRecord(oRow);
:
:
}
程式碼 2:
var oSP = new DB.StorePointRecord();
foreach (DataRow oRow in DT.Rows)
{
oSP.row = oRow;
:
:
}
Bike, 2018/4/10 上午 06:45:55
https://stackoverflow.com/questions/628565/display-lines-number-in-stack-trace-for-net-assembly-in-release-mode/628590#628590
1. Go into the Properties window for the project where you want to see stack trace line numbers.
2. Click on the Build "vertical tab".
3. Select "Release" configuration.Check the DEBUG constant parameter.
4. Uncheck the "Optimize code" parameter to avoid the occasional trace issue with inlined code (this step is not essential).
5. Press the Advanced... button and choose Output -> Debug Info -> pdb-only.
6. Deploy the generated .pdb file with the assembly.
1. Go into the Properties window for the project where you want to see stack trace line numbers.
2. Click on the Build "vertical tab".
3. Select "Release" configuration.
4. Uncheck the "Optimize code" parameter to avoid the occasional trace issue with inlined code (this step is not essential).
5. Press the Advanced... button and choose Output -> Debug Info -> pdb-only.
6. Deploy the generated .pdb file with the assembly.
Bike, 2018/4/9 上午 09:40:12
以前年輕不懂事, 寫了一些很遜的程式碼, 例如:
舊的程式碼:
新的程式碼:
可以參考:
https://dotblogs.com.tw/yc421206/archive/2012/01/03/64154.aspx
舊的程式碼:
public static DataTable DTFromSQL(string Sql, string DBC = null, Int32 timeout = 0)
{
SqlDataAdapter DA = new SqlDataAdapter(Sql, GetDBC(DBC));
DataTable DT = new DataTable();
try
{
DA = new SqlDataAdapter(Sql, DBC);
if (timeout > 0)
{
DA.SelectCommand.CommandTimeout = timeout;
}
DA.Fill(DT);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
DA.Dispose();
}
return DT;
}
新的程式碼:
public static DataTable DTFromSQL(string Sql, string DBC = null, Int32 timeout = 30)
{
using (var DA = new SqlDataAdapter(Sql, GetDBC(DBC)))
{
//不可為 null
DataTable DT = new DataTable();
DA.SelectCommand.CommandTimeout = timeout;
DA.Fill(DT);
return DT;
}
}
可以參考:
https://dotblogs.com.tw/yc421206/archive/2012/01/03/64154.aspx
Bike, 2018/4/4 上午 07:56:39
LINE Pay款流程：
reserve Page(紅科) 發送 reserve API => 取得 payment URL => 轉址到 payment URL => LINE Pay 檢查USER狀態 => 轉回confirm page(紅科), 發送 confirm API => DOWN.
付款需要程式：
reserve API, confirm API:
send request JSON and read return JSON:
C#:
.vb
工具網站：
JSON 轉物件 http://json2csharp.com/
C# to VB : http://converter.telerik.com/
#專案： 紅科
reserve Page(紅科) 發送 reserve API => 取得 payment URL => 轉址到 payment URL => LINE Pay 檢查USER狀態 => 轉回confirm page(紅科), 發送 confirm API => DOWN.
付款需要程式：
reserve API, confirm API:
send request JSON and read return JSON:
C#:
using System.Net;
using System.IO;
public partial class TEST : System.Web.UI.Page
{
DB.OrderMain oOrderObj = null;
protected void Page_Load(object sender, EventArgs e)
{
//Setting request header
HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create("__APIRootURL__");
httpWebRequest.ContentType = "application/json; charset=UTF-8";
httpWebRequest.Method = "POST";
httpWebRequest.Headers.Add("X-LINE-ChannelId", DB.SysConfig.LINEPay.ChannelId);
httpWebRequest.Headers.Add("X-LINE-ChannelSecret", DB.SysConfig.LINEPay.SecretKey);
//加入參數
using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
{
string requestJSON = "{\"productName\": \"" + DB.SysConfig.SYSTEM_NAME + "\"," +
"\"productImageUrl\": \"" + DB.SysConfig.URL_Shopping + "images/Logo.jpg\"," +
"\"amount\": " + oOrderObj.TotalAmount() + "," +
"\"currency\": \"TWD\"," +
"\"orderId\": \"" + oOrderObj.DisplayOrderId + "\"," +
"\"confirmUrl\": \"" + DB.SysConfig.URL_Shopping + "Handler/LinePay/GotoComfirm.aspx?Id=" + oOrderObj.Id + "\"," +
"\"cancelUrl\": \"" + DB.SysConfig.URL_Shopping + "Shopping/OrderComplete.aspx?Id=" + oOrderObj.Id + "\"," +
"\"capture\": \"true\"," +
"\"confirmUrlType\": \"CLIENT\"}";
streamWriter.Write(requestJSON);
streamWriter.Flush();
streamWriter.Close();
}
//取得回覆資訊
var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
//解讀回覆資訊
using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
{
var responseJSON = streamReader.ReadToEnd();
//將 JSON 轉為物件
GN.LinePay.reserveRes.ReturnJSON oReturnObj = (GN.LinePay.reserveRes.ReturnJSON)Newtonsoft.Json.JsonConvert.DeserializeObject(responseJSON, typeof(GN.LinePay.reserveRes.ReturnJSON));
if (oReturnObj.returnCode == "0000")
{
//成功
}
else
{
//失敗
string ErrMsg = "Error Code: " + oReturnObj.returnCode + "\r\n" + oReturnObj.returnMessage;
}
}
}
}
.vb
Imports System.Net
Imports System.IO
Partial Class TEST
Inherits System.Web.UI.Page
Dim oOrderObj As DB.OrderMain
Private Sub Admin_TEST_Load(sender As Object, e As EventArgs) Handles Me.Load
'Setting request header
Dim httpWebRequest As HttpWebRequest = WebRequest.Create("__APIRootURL__")
httpWebRequest.ContentType = "application/json; charset=UTF-8"
httpWebRequest.Method = "POST"
httpWebRequest.Headers.Add("X-LINE-ChannelId", DB.SysConfig.LINEPay.ChannelId)
httpWebRequest.Headers.Add("X-LINE-ChannelSecret", DB.SysConfig.LINEPay.SecretKey)
'加入參數
Using streamWriter As New StreamWriter(httpWebRequest.GetRequestStream())
Dim requestJSON As String = "{""productName"": """ + DB.SysConfig.SYSTEM_NAME + """," +
"""productImageUrl"": """ + DB.SysConfig.URL_Shopping + "images/Logo.jpg""," +
"""amount"": " + oOrderObj.TotalAmount() + "," +
"""currency"": ""TWD""," +
"""orderId"": """ + oOrderObj.DisplayOrderId + """," +
"""confirmUrl"": """ + DB.SysConfig.URL_Shopping + "Handler/LinePay/GotoComfirm.aspx?Id=" + oOrderObj.Id + """," +
"""cancelUrl"": """ + DB.SysConfig.URL_Shopping + "Shopping/OrderComplete.aspx?Id=" + oOrderObj.Id + """," +
"""capture"": ""true""," +
"""confirmUrlType"": ""CLIENT""}"
streamWriter.Write(requestJSON)
streamWriter.Flush()
streamWriter.Close()
End Using
'取得回覆資訊
Dim httpResponse As HttpWebResponse = httpWebRequest.GetResponse
'解讀回覆資訊
Using streamReader As New StreamReader(httpResponse.GetResponseStream())
Dim responseJSON As String = streamReader.ReadToEnd()
'將 JSON 轉為物件
Dim oReturnObj As GN.LinePay.reserveRes.ReturnJSON = Newtonsoft.Json.JsonConvert.DeserializeObject(responseJSON, GetType(GN.LinePay.reserveRes.ReturnJSON))
Dim ResMsg As String = ""
If oReturnObj.returnCode = "0000" Then
'成功
Else
'失敗
End If
End Using
End Sub
End Class
工具網站：
JSON 轉物件 http://json2csharp.com/
C# to VB : http://converter.telerik.com/
#專案： 紅科
Reiko, 2017/6/21 下午 07:22:58