搜尋 182 結果：
1. 使用 "具名的 ConnectionString"時，必需在 ODBC Data Sources (32-bit) 中設定，不是 ODBC Data Sources (64-bit)
2. 若日期或數字欄位為 Null 時，會無法轉換，此時必需用 "寫入查詢來指定要傳送的資料"
2. 若日期或數字欄位為 Null 時，會無法轉換，此時必需用 "寫入查詢來指定要傳送的資料"
Bike, 2024/2/17 下午 04:03:59
Entity Framework 提供了 ExecuteSqlRawAsync 和 FromSqlRaw 之後，可以和 Dapper 非常類似的用法。
我們在用 Dapper 時。最常用的就是 sql command 加上一個物件做為參數，就可以執行 CRUD 的動作。
其實用 Entity Framework 的 ExecuteSqlRawAsync 和 FromSqlRaw 也可以逹到幾乎一樣的效果。
ExecuteSqlRawAsync 和 FromSqlRaw 接受的參數是 object array (其實是 Microsoft.Data.SqlClient.SqlParameter 的 array)
所以我們先做一個 Object to Microsoft.Data.SqlClient.SqlParameter Array 的擴充, 可參考: https://gist.github.com/aliozgur/75182b2e9b0a58b83443
不過很奇怪的是，原作者提供的擴充轉出來的會是 System.Data.SqlClient.SqlParameter Array 無法直接使用於 ExecuteSqlRawAsync 和 FromSqlRaw，所以要稍微改一下，把 using System.Data.SqlClient; 改為 using Microsoft.Data.SqlClient; 即可:
另外，我們再自行 對 DbContext 做一個擴充如下:
最後的結果可以做到以下的效果: (res3 和 res4 是配合 FormattableString 的範例 )
範例程式碼:
我們在用 Dapper 時。最常用的就是 sql command 加上一個物件做為參數，就可以執行 CRUD 的動作。
其實用 Entity Framework 的 ExecuteSqlRawAsync 和 FromSqlRaw 也可以逹到幾乎一樣的效果。
ExecuteSqlRawAsync 和 FromSqlRaw 接受的參數是 object array (其實是 Microsoft.Data.SqlClient.SqlParameter 的 array)
所以我們先做一個 Object to Microsoft.Data.SqlClient.SqlParameter Array 的擴充, 可參考: https://gist.github.com/aliozgur/75182b2e9b0a58b83443
不過很奇怪的是，原作者提供的擴充轉出來的會是 System.Data.SqlClient.SqlParameter Array 無法直接使用於 ExecuteSqlRawAsync 和 FromSqlRaw，所以要稍微改一下，把 using System.Data.SqlClient; 改為 using Microsoft.Data.SqlClient; 即可:
另外，我們再自行 對 DbContext 做一個擴充如下:
/// <summary>
/// 會把物件 Parameter 的各 Property 帶入 SQL 中.
/// </summary>
/// <param name="dbct"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static async Task<int> ExecuteParameterSqlAsync(this DbContext dbct, string sql, object parameters)
{
return await dbct.Database.ExecuteSqlRawAsync(sql, parameters.ToSqlParamsArray());
}
最後的結果可以做到以下的效果: (res3 和 res4 是配合 FormattableString 的範例 )
範例程式碼:
public async Task<object> EfTest()
{
var dbct = Ds.NewContext.GvContext;
var insertSql = @"Insert into ProfileEvent(JobId,[Name],GvShoplineId,LiShoplineId,Phone,Email,LineId,GaClientId)
Values(@JobId, @Name, null , null , null , @Email, @GaClientId , @GaClientId )";
//執行 SQL 的原生寫法
await dbct.Database.ExecuteSqlRawAsync(insertSql, new object[]
{
new SqlParameter("@JobId", 10),
new SqlParameter("@Email", "XX'TT"),
new SqlParameter("@Name", "AA'BB"),
new SqlParameter("@GaClientId", "GaClientId"),
});
//執行 SQL 的擴充寫法
await dbct.ExecuteParameterSqlAsync(insertSql, new
{
JobId = 10,
Email = "XX'TT",
Name = "AA'BB",
GaClientId = "GaClientId"
});
//關於查詢
string name = "%'B%";
//原生寫法
var res1 = await dbct.ProfileEvents.FromSqlRaw("Select top 100 * from ProfileEvent where Name like @name",
new object[]
{
new SqlParameter("@name", "%'B%")
})
.ToListAsync();
//擴充, object to SqlParameter array
var res2 = await dbct.ProfileEvents.FromSqlRaw("Select top 100 * from ProfileEvent where Name like @name",
new { name }.ToSqlParamsArray())
.ToListAsync();
var res3 = await dbct.ProfileEvents.FromSql($"Select top 100 * from ProfileEvent where Name like {name}")
.ToListAsync();
var res4 = await dbct.ProfileEvents.FromSqlInterpolated($"Select top 100 * from ProfileEvent where Name like {name}")
.ToListAsync();
return new { res1, res2, res3, res4 };
}
Bike, 2023/5/6 下午 05:37:19
httpRuntime 加 maxRequestLength 沒作用, 請到 system.webServer 設定 maxAllowedContentLength
<system.webServer>
...
<security>
<requestFiltering>
<!--1073741824 ==> 1GB-->
<requestLimits maxAllowedContentLength="1073741824" />
</requestFiltering>
</security>
...
</system.webServer>
Reiko, 2021/3/25 下午 02:36:36
建立兩個表 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
我想大家一定會遇到要把資料匯出成 Excel 的需求. 以現有的工具, 大家想到作法大概都是先把資料放到一個 datatable 之中, 後叫用 UW.ExcelPOI.DTToExcelAndWriteToClient 就結束了.
前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖
於是乎把 UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.
1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row
2. 實作:
A. 先定義 Cell
B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.
C. 多個 Row 的表示法: List(Of List(Of Cell))
D. 來把 Cell 填入 WorkSheet 吧,
共有三個參數: WS 和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.
E. 完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)
F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄:
2. 如何設定字型大小和對齊方式:
這裡有件有有趣的事, 我一開始是這樣寫的.
結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.
G. 使用方式:
前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖
於是乎把 UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.
1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row
2. 實作:
A. 先定義 Cell
Public Class Cell
Public Content As String
Public Colspan As Int32 = 1
Public Alignment As NPOI.SS.UserModel.HorizontalAlignment
Public FontHeightInPoints As Int32 = 0
Sub New(Content As String, Optional Colspan As Int32 = 1,
Optional Alignment As NPOI.SS.UserModel.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General,
Optional FontHeightInPoints As Int32 = 0)
Me.Content = Content
Me.Colspan = Colspan
Me.Alignment = Alignment
Me.FontHeightInPoints = FontHeightInPoints
End Sub
End Class
B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.
C. 多個 Row 的表示法: List(Of List(Of Cell))
D. 來把 Cell 填入 WorkSheet 吧,
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
共有三個參數: WS 和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.
E. 完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
Dim WR As HSSFRow
If ltRows IsNot Nothing Then
For Each ltRow As List(Of Cell) In ltRows
WR = WS.CreateRow(StartRow)
Dim C As Int32 = 0
For Each cell As Cell In ltRow
Dim ic As NPOI.SS.UserModel.ICell = WR.CreateCell(C)
ic.SetCellValue(cell.Content)
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
If cell.Colspan > 1 Then
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
C += cell.Colspan - 1
End If
C += 1
Next
StartRow += 1
Next
End If
End Sub
F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄:
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
2. 如何設定字型大小和對齊方式:
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
這裡有件有有趣的事, 我一開始是這樣寫的.
ic.CellStyle.Alignment = cell.Alignment
結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.
G. 使用方式:
Dim ltHeader As New List(Of List(Of UW.ExcelPOI.Cell))
Dim ltLine As New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell(DB.SysConfig.SYSTEM_NAME & "應收明細表", 16,
NPOI.SS.UserModel.HorizontalAlignment.Center, 28))
ltHeader.Add(ltLine)
'第二行
ltLine = New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell("期間: " & Me.txtbl_date_s.Text & " ~ " & Me.txtbl_date_e.Text, 10,
NPOI.SS.UserModel.HorizontalAlignment.Left, 20))
ltLine.Add(New UW.ExcelPOI.Cell("製表日期: " & Now.ToString("yyyy-MM-dd"), 6,
NPOI.SS.UserModel.HorizontalAlignment.Right, 20))
ltHeader.Add(ltLine)
UW.ExcelPOI.DTToExcelAndWriteToClient(newdt, ltHeader:=ltHeader)
Bike, 2017/6/4 下午 07:19:27
最近把 shopunt 網站重啟的Log加上，發現網站有兩次時無預警的重啟
但是沒有exception log, 於是去看系統的 Event log, 發現出現以下的錯誤
原因是另開 Thread 以非同步取得非停售產品的資料時 產生 sql timeout，這時網站就整個 Crash
約兩分鐘後才重新啟動，我想雙11網站一直Crash應該跟這個有關
改善方式:
1. RebuildNotStopProducDT 要 Try Catch 不要丟 exception 直接寄 mail 就好
2. 資料庫取得 "非停售產品" 有點沒有效率，不忙時至少要跑 4 秒，資料庫一忙就會Timeout，所以應該要把確定下架且不會販售的產品設定為停售 (Is_StopSell = 'Y')，這樣至少少了2000件產品的查詢，可以秒殺
另外 .NET 4.0 有新的物件叫做 Task 可以用來處理非同步程式，也可以處理 exception 狀況，有時間的話再來改看看
但是沒有exception log, 於是去看系統的 Event log, 發現出現以下的錯誤
應用程式: w3wp.exe
Framework 版本: v4.0.30319
描述: 處理序已終止，因為有未處理的例外狀況。
例外狀況資訊: System.Exception
堆疊:
於 UW.SQL.DTFromSQL(System.String, System.String)
於 UW.SQL.DTFromSQL(System.String, System.Data.SqlClient.SqlConnection ByRef, Boolean)
於 SHOPUNT.DB.Product.RebuildNotStopProducDT()
於 System.Threading.ExecutionContext.runTryCode(System.Object)
於 System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode, CleanupCode, System.Object)
於 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
於 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
於 System.Threading.ThreadHelper.ThreadStart()
---------------------------------------------------------------------------------------------------------------------------------------
失敗的應用程式名稱: w3wp.exe，版本: 7.5.7601.17514，時間戳記: 0x4ce7afa2
失敗的模組名稱: KERNELBASE.dll，版本: 6.1.7601.18229，時間戳記: 0x51fb1677
例外狀況碼: 0xe0434352
錯誤位移: 0x000000000000940d
失敗的處理程序識別碼: 0x57c4
失敗的應用程式開始時間: 0x01d012faf33398f8
失敗的應用程式路徑: c:\windows\system32\inetsrv\w3wp.exe
失敗的模組路徑: C:\Windows\system32\KERNELBASE.dll
報告識別碼: 90ad2f4d-7f89-11e4-8ad2-e41f13b7d81e
原因是另開 Thread 以非同步取得非停售產品的資料時 產生 sql timeout，這時網站就整個 Crash
約兩分鐘後才重新啟動，我想雙11網站一直Crash應該跟這個有關
改善方式:
1. RebuildNotStopProducDT 要 Try Catch 不要丟 exception 直接寄 mail 就好
2. 資料庫取得 "非停售產品" 有點沒有效率，不忙時至少要跑 4 秒，資料庫一忙就會Timeout，所以應該要把確定下架且不會販售的產品設定為停售 (Is_StopSell = 'Y')，這樣至少少了2000件產品的查詢，可以秒殺
另外 .NET 4.0 有新的物件叫做 Task 可以用來處理非同步程式，也可以處理 exception 狀況，有時間的話再來改看看
darren, 2014/12/13 下午 05:44:05