解析文本到数据库

1. private void button1_Click(object sender, EventArgs e)
2.         {
3.             string text = this.ReadText();
4.             string clearText = this.Clear(text);
5.             IList<Air> airList = this.FormatToAirList(clearText);
6.             bool ok = true;
7.             foreach (Air airItem in airList)
8.             {
9.                 ok &= this.Add(airItem);
10.             }
12.             if (ok)
13.             {
14.                 MessageBox.Show("ok");
15.             }
16.             else
17.             {
18.                 MessageBox.Show("False");
19.             }
20.         }
21.         private bool Add(Air air)
22.         {
23.             string conStr = "server=10.10.10.96;database=DorichTicket;uid=dorichuser;pwd=2332;Min Pool Size = 3";
24.             string sqlStr = "insert into AirCity(CityCode,CName,PinYin,AirPort)values('" + air.AirCode + "','"
25.                             + air.AirName + "','" + air.Pinyin + "','" + air.AirPort + "')";
26.             SqlConnection con = new SqlConnection(conStr);
27.             SqlCommand cmd = new SqlCommand(sqlStr, con);
28.             try
29.             {
30.                 con.Open();
31.                 return cmd.ExecuteNonQuery() > 0;
32.             }
33.             catch (SqlException sqEx)
34.             {
35.                MessageBox.Show(sqEx.Message);
36.             }
37.             catch (Exception ex)
38.             {
39.                 MessageBox.Show(ex.Message);
40.             }
41.             finally
42.             {
43.                 cmd.Dispose();
44.                 con.Close();
45.             }
46.             return false;
47.         }
48.         private string ReadText()
49.         {
50.             //string phyPath = this.
51.             StreamReader sr = File.OpenText("F://sql//sql//1.txt");
52.             return sr.ReadToEnd(); 
53.         }
54.         private string Clear(string value)
55.         {
56.             string result = Regex.Replace(value, @"()/1+", "", RegexOptions.None);
57.             result = result.Replace(@"//new", "");
58.             result = result.Replace(@"//add new", "");
59.             result = result.Replace("/"", "");
60.             result = result.Replace("/"", "");
61.             return result;
62.         }
63.         private IList<Air> FormatToAirList(string value)
64.         {
65.             IList<Air> airList = new List<Air>();
66.             string[] airArr = value.Split(new string[] { "/r/n/t" }, StringSplitOptions.RemoveEmptyEntries);
67.             foreach (string airArrItem in airArr)
68.             {
69.                 Air air = new Air();
70.                 string[] airCodeArr = airArrItem.Split(new string[] { ":" }, StringSplitOptions.RemoveEmptyEntries);
71.                 //PEK : {cname : 北京,pinyin : ['bei','jing'],airport : 北京首都国际机场},
72.                 air.AirCode = airCodeArr[0].Trim().ToUpper();
73.                 air.AirName = airCodeArr[2].Split(',')[0].Trim().ToUpper();
74.                 air.Pinyin = airCodeArr[3].Replace("[", "").Replace("]", " ").Replace(",", "").Replace("/'", "").Replace("airport", "").Replace(" ", "").Trim().ToUpper();
75.                 air.AirPort = airCodeArr[4].Replace("}", "").Replace(",", "").Trim().ToUpper();
76.                 airList.Add(air);
77.             }
78.             return airList;
79.         }
80.         public class Air
81.         {
82.             private string airCode;
83.             private string airName;
84.             private string pinyin;
85.             private string airPort;
86.             private int age;
87.             public int Age
88.             {
89.                 get { return age; }
90.                 protected internal set
91.                 {
92.                     if (this.age > 200 || this.age < 0)
93.                     {
94.                         throw new InvalidOperationException("Invalid data");
95.                     }
96.                     this.age = value;
97.                 }
98.             }
99.             public string AirCode
100.             {
101.                 get { return airCode; }
102.                 set
103.                 {
104.                     if (value == null)
105.                     {
106.                         throw new NullReferenceException("Para Null");
107.                     }
108.                     this.airCode = value.Trim().ToUpper();
109.                 }
110.             }
111.             public string AirName
112.             {
113.                 get { return airName; }
114.                 set { airName = value; }
115.             }
116.             public string Pinyin
117.             {
118.                 get { return pinyin; }
119.                 set { pinyin = value; }
120.             }
121.             public string AirPort
122.             {
123.                 get { return airPort; }
124.                 set { airPort = value; }
125.             }
126.         }

 

 

原始文本

 PEK : {cname : "北京",     pinyin : ['bei','jing'],          airport : "北京首都国际机场"},
 CTU :{cname :"成都",pinyin:['cheng','du'],    airport :"成都双流机场"},
 CKG :{cname :"重庆",pinyin:['chong','qing'],  airport :"重庆江北机场"},
 DLC :{cname :"大连",pinyin:['da','lian'],airport :"大连周水子机场"},
 CAN : {cname : "广州",     pinyin : ['guang','zhou'],       airport : "广州新白云国际机场"},
 HGH :{cname :"杭州",pinyin:['hang','zhou'],   airport :"杭州萧山机场"},
 TAO :{cname :"青岛",pinyin:['qing','dao'],    airport :"青岛流亭机场"},
 SHA : {cname : "上海",     pinyin : ['shang','hai'],        airport : "上海虹桥机场"},
 SZX :{cname :"深圳",pinyin:['shen','zhen'],   airport :"深圳宝安机场"},
 WUH :{cname :"武汉",pinyin:['wu','han'], airport :"武汉天河机场"},
 XMN :{cname :"厦门",pinyin:['xia','men'],airport :"厦门高崎机场"},
 TSN :{cname :"天津",pinyin:['tian','jin'],    airport :"天津滨海国际机场"}
 AKU : {cname:"阿克苏", pinyin:['an', 'ke','su'], airport:"阿克苏机场"},
 AKA : {cname:"安康", pinyin:['an', 'kang'], airport:"安康机场"},
 AQG : {cname : "安庆", pinyin : ['an', 'qing'], airport : "天柱山机场"},
 AEB : {cname :"百色",pinyin:['bai','se'],  airport:"百色机场"} , //new
 BAV : {cname :"包头",pinyin:['bao','tou'],airport :"包头机场"},
 BSD : {cname :"保山",pinyin:['bao','shan'],  airport   :"保山机场"}, //new
 BHY : {cname :"北海",pinyin:['bei','hai'],airport :"北海福城机场"},
 PEK : {cname :"北京",pinyin:['bei','jing'],airport:"北京首都国际机场"},
 CGQ : {cname :"长春",pinyin:['chang','chun'],  airport :"长春大房身机场"},
 CSX : {cname :"长沙",pinyin:['chang','sha'],   airport :"长沙黄花机场"},
 CGD : {cname :"常德",pinyin:['chang','de'],    airport :"常德半姆湖机场"},
 CZX : {cname :"常州",pinyin:['chang','zhou'],  airport :"常州奔牛机场"},
 CIH : {cname : "长治", pinyin : ['chang', 'zhi'], airport : "长治机场"},
 CTU : {cname :"成都",pinyin:['cheng','du'],    airport :"成都双流机场"},
 CIF : {cname : "赤峰", pinyin : ['chi', 'feng'], airport : "赤峰机场"},
 CKG : {cname :"重庆",pinyin:['chong','qing'],  airport :"重庆江北机场"},
 DLU : {cname :"大理",pinyin:['da','li'],  airport :"大理机场"}, //new
 DLC : {cname :"大连",pinyin:['da','lian'],airport :"大连周水子机场"},
 DAX : {cname :"达县",pinyin:['da','xian'],airport :"达县机场"},
 DAT : {cname : "大同", pinyin : ['da', 'tong'], airport : "大同机场"},
 DDG : {cname :"丹东",pinyin:['dan','dong'],    airport :"丹东浪头机场"},
 DNH : {cname :"敦煌",pinyin:['dun','huang'],  airport  :"敦煌机场"}, //new
 ENH : {cname : "恩施", pinyin : ['en', 'shi'], airport : "恩施机场"},
 FOC : {cname :"福州",pinyin:['fu','zhou'],airport :"福州长乐机场"},
 KOW : {cname :"赣州",pinyin:['gan','zhou'],    airport :"赣州黄金机场"},
 CAN : {cname :"广州",pinyin:['guang','zhou'],  airport :"广州新白云国际机场"},
 KWE : {cname :"贵阳",pinyin:['gui','yang'],    airport :"贵阳龙洞堡机场"},
 KWL : {cname :"桂林",pinyin:['gui','lin'],airport :"桂林两江国际机场"},
 HRB : {cname :"哈尔滨",   pinyin:['ha','er','bin'], airport :"哈尔滨太平机场"},
 HAK : {cname :"海口",pinyin:['hai','kou'],airport :"海口美兰机场"},
 HLD : {cname :"海拉尔",pinyin  :['hai','la','er'],  airport:"东山机场"}, //new
 HDG : {cname :"邯郸",pinyin : ['han', 'dan'], airport : "邯郸机场"},
 HZG : {cname : "汉中", pinyin : ['han', 'zhong'], airport : "汉中机场"},
 HGH : {cname :"杭州",pinyin:['hang','zhou'],   airport :"杭州萧山机场"},
 HFE : {cname :"合肥",pinyin:['he','fei'], airport :"合肥骆岗机场"},
 HET : {cname :"呼和浩特", pinyin:['hu','he','hao','te'],  airport:"呼和浩特白塔机场"}, 
 TXN : {cname :"黄山",pinyin:['huang','shan'],  airport :"黄山屯溪机场"},
 HYN : {cname :"黄岩",pinyin:['huang','yan'],   airport :"黄岩路桥机场"},
 JIL : {cname :"吉林",pinyin:['ji','lin'], airport :"吉林二台子机场"},
 TNA : {cname :"济南",pinyin:['ji','nan'], airport :"济南遥墙机场"},
 JMU : {cname :"佳木斯",pinyin  :['jia','mu','si'],  airport:"东郊机场"}, //new
 JNZ : {cname :"锦州",pinyin:['jin','zhou'],    airport :"锦州小岭子机场"},
 JJN : {cname :"晋江",pinyin:['jin','jiang'],   airport :"晋江泉州机场"},
 JDZ : {cname :"景德镇",pinyin  :['jing','de','zhen'],  airport :"景德镇机场"}, //new
 JIU : {cname : "九江", pinyin : ['jiu', 'jiang'], airport : "九江机场"},
 JZH : {cname: "九寨沟", pinyin : ['jiu','zhai','gou'],  airport : "九黄机场"}, //new
 KRL : {cname : "库尔勒", pinyin : ['ku','er','le'], airport : "地窝堡机场"},
 KMG :{cname :"昆明",pinyin:['kun','ming'],    airport :"昆明巫家坝机场"},
 LXA :{cname :"拉萨",pinyin:['la','sa'],  airport :"拉萨贡嘎机场"},
 LHW :{cname :"兰州",pinyin:['lan','zhou'],    airport :"兰州中川机场"},
 LJG :{cname :"丽江",pinyin:['li','jiang'],  airport   :"丽江机场"} , //new
 LYG :{cname :"连云港",   pinyin:['lian','yun','gang'],  airport :"连云港白塔寺机场"},
 LNC :{cname :"临沧",pinyin:['lin','cang'],  airport   :"临沧机场"}, //new
 LYI :{cname :"临沂",pinyin:['lin','yi'],  airport:"临沂机场"}, //new
 LZH :{cname :"柳州",pinyin:['liu','zhou'],    airport :"柳州白莲机场"},
 LZO :{cname :"泸州",pinyin:['lu','zhou'],airport :"泸州兰田坝机场"},
 LYA :{cname :"洛阳",pinyin:['luo','yang'],    airport :"洛阳北郊机场"},
 LUM :{cname :"芒市",pinyin:['mang','shi'],  airport   :"芒市机场"} , //new
 MXZ :{cname :"梅州",pinyin:['mei','zhou'],    airport :"梅县机场"},
 NZH : {cname : "满洲里", pinyin : ['mian', 'zhou'], airport : "西郊机场"},
 MIG : {cname: "绵阳", pinyin : ['mian','yang'],  airport : "南郊机场"}, //add new
 MDG :{cname :"牡丹江",   pinyin:['mu','dan','jiang'],   airport :"牡丹江海浪机场"},
 KHN :{cname :"南昌",pinyin:['nan','chang'],   airport :"南昌昌北机场"},
 NAO : {cname : "南充", pinyin : ['nan', 'chong'], airport : "南充高坪机场"},
 NKG :{cname :"南京",pinyin:['nan','jing'],    airport :"南京禄口机场"},
 NNG :{cname :"南宁",pinyin:['nan','ning'],    airport :"南宁吴墟机场"},
 NTG :{cname :"南通",pinyin:['nan','tong'],    airport :"南通兴东机场"},
 NNY :{cname :"南阳",pinyin:['nan','yang'],    airport :"南阳姜营机场"},
 NAY : {cname : "南苑", pinyin : ['nan', 'yuan'], airport : "北京南苑机场"},
 NGB :{cname :"宁波",pinyin:['ning','bo'],airport :"宁波栎社机场"},
 PZI :{cname :"攀枝花",pinyin:['pan','zhi','hua'],  airport :"攀枝花机场"},  //new
 NDG :{cname :"齐齐哈尔",pinyin :['qi','qi','ha','er'],  airport :"三家子机场"} ,
 SHP :{cname :"秦皇岛",   pinyin:['qin','huang','dao'],  airport :"秦皇岛山海关机场"},
 TAO :{cname :"青岛",pinyin:['qing','dao'],    airport :"青岛流亭机场"},
 JUZ :{cname :"衢州",pinyin:['qu','zhou'],airport :"衢州机场"},
 SYX :{cname :"三亚",pinyin:['san','ya'], airport :"三亚凤凰机场"},
 SWA :{cname :"汕头",pinyin:['shan','tou'],    airport :"汕头外砂机场"},
 SHA :{cname :"上海",pinyin:['shang','hai'],   airport :"上海虹桥机场"},
 PVG :{cname :"上海浦东",  pinyin :['shang','hai','pu','dong'],   airport :"上海浦东机场"},
 SZX :{cname :"深圳",pinyin:['shen','zhen'],   airport :"深圳宝安机场"},
 SHE :{cname :"沈阳",pinyin:['she','yang'],    airport :"沈阳桃仙机场"},
 SJW :{cname :"石家庄",   pinyin:['shi','jia','zhuang'], airport :"石家庄正定机场"},
 SYM :{cname :"思茅",pinyin:['si','mao'],  airport:"思茅机场"}, //new
 TYN :{cname :"太原",pinyin:['tai','yuan'],    airport :"太原武宿机场"},
 TSN :{cname :"天津",pinyin:['tian','jin'],    airport :"天津滨海国际机场"},
 TEN :{cname :"铜仁",pinyin:['tong','ren'],  airport:"大兴机场"}, //new
 WXN :{cname :"万县",pinyin:['wan','xian'],    airport :"万县梁平机场"},
 WEH :{cname :"威海",pinyin:['wei','hai'],  airport:"大水泊机场"}, //new
 WEF :{cname :"潍坊",pinyin:['wei','fang'],  airport:"文登机场"} , //new
 WNH : {cname : "文山", pinyin : ['wen', 'shan'], airport : "文山机场"},
 WNZ :{cname :"温州",pinyin:['wen','zhou'],    airport :"温州永强机场"},
 URC :{cname :"乌鲁木齐", pinyin:['wu','lu','mu','qi'],  airport:"乌鲁木齐地窝铺机场"},
 WUX :{cname :"无锡",pinyin:['wu','xi'],  airport :"无锡机场"}, //new
 WUH :{cname :"武汉",pinyin:['wu','han'], airport :"武汉天河机场"},
 WUS :{cname :"武夷山",pinyin  :['wu','yi','shan'],  airport:"武夷山机场"}, //new
 XIY :{cname :"西安",pinyin:['xi','an'],  airport :"西安咸阳机场"},
 XIC : {cname : "西昌", pinyin : ['xi', 'chang'], airport:"青龙机场"},
 XNN :{cname :"西宁",pinyin:['xi','ning'],airport :"西宁曹家堡机场"},
 JHG :{cname :"西双版纳(景洪)",pinyin:['xi','shuang','ban','na'],  airport:"嘎洒机场"}, 
 XMN :{cname :"厦门",pinyin:['xia','men'],airport :"厦门高崎机场"},
 DIG :{cname :"香格里拉(中甸)",pinyin:['xiang','ge','li','la'],  airport  :"中甸机场"}, //new
 XFN :{cname :"襄樊",pinyin:['xiang','fan'],   airport :"襄樊刘集机场"},
 ACX : {cname : "兴义", pinyin : ['xing', 'yi'], airport : "兴义机场"},
 XUZ :{cname :"徐州",pinyin:['xu','zhou'],airport :"徐州观音机场"},
 ENY : {cname : "延安", pinyin : ['yan', 'an'], airport : "延安机场"},
 YNZ :{cname :"盐城",pinyin:['yan','cheng'],  airport  :"盐城机场"}, //new
 YNJ : {cname : "延吉", pinyin : ['yan', 'ji'], airport : "延吉机场"},
 YNT :{cname :"烟台",pinyin:['yan','tai'],airport :"烟台莱山机场"},
 YBP :{cname :"宜宾",pinyin:['yi','bin'], airport :"宜宾菜坝机场"},
 YIH :{cname :"宜昌",pinyin:['yi','chang'],    airport :"宜昌三峡机场"},
 YIN : {cname : "伊宁", pinyin : ['yi', 'ning'], airport : "伊宁机场"},
 YIW :{cname :"义乌",pinyin:['yi','wu'],  airport :"义乌机场"},
 INC :{cname :"银川",pinyin:['yin','chuan'],   airport :"银川河东机场"},
 YCU :{cname :"运城",pinyin:['yun','cheng'],  airport  :"运城机场"} , //new
 UYN : {cname : "榆林", pinyin : ['yu', 'lin'], airport : "榆林机场"},
 ZHA :{cname :"湛江",pinyin:['zhan','jiang'],  airport :"湛江机场"}, //new
 DYG :{cname :"张家界",   pinyin:['zhang','jia','jie'],  airport :"张家界机场"},
 ZAT :{cname :"昭通",pinyin:['zhao','tong'],  airport  :"昭通机场"}, //new
 CGO :{cname :"郑州",pinyin:['zheng','zhou'],  airport :"郑州新郑机场"},
 HJJ : {cname : "芷江", pinyin : ['zhi', 'jiang'], airport : "芷江机场"},
 HSN : {cname : "舟山", pinyin : ['zhou', 'shan'], airport : "朱家尖机场"},
 ZUH :{cname :"珠海",pinyin:['zhu','hai'],airport :"珠海三灶机场"}

 

=======================================================

1. protected void Page_Load(object sender, EventArgs e)
2.     {
4.     }
5.     protected void Button1_Click(object sender, EventArgs e)
6.     {
7.         string text = this.ReadText(this.FileUpload1.PostedFile.InputStream);
8.         string clearText = this.Clear(text);
9.         IList<BocTrade> bocTradeList = this.FormatToList(clearText);
10.         bool ok = true;
11.         foreach (BocTrade item in bocTradeList)
12.         {
13.             ok &= this.Add(item);
14.         }
15.         if (ok)
16.         {
17.             Response.Write("ok");
18.         }
19.         else
20.         {
21.             Response.Write("False");
22.         }
23.     }
24.     private bool Add(BocTrade bocTrade)
25.     {
26.         Response.Write(bocTrade.TerminalId + "<br>");
27.         Response.Write(bocTrade.TradeDate + "<br>");
28.         Response.Write(bocTrade.TradeTime + "<br>");
29.         Response.Write(bocTrade.TradeAccount + "<br>");
30.         Response.Write(bocTrade.TradeAount + "<br>");
31.         Response.Write(bocTrade.Commission + "<br>");
32.         Response.Write(bocTrade.BalanceAmout + "<br>");
33.         Response.Write(bocTrade.TradeType + "<br>");
34.         return true;
35.     }
36.     private string ReadText(Stream stream)
37.     {
38.         BufferedStream bfs = null;
39.         StreamReader sr = null;
40.         try
41.         {
42.             bfs = new BufferedStream(stream);
43.             sr = new StreamReader(bfs, Encoding.GetEncoding("GB2312"));
44.             bfs.Flush();
45.             return sr.ReadToEnd();
46.         }
47.         finally
48.         {
49.             if (bfs != null) { bfs.Close(); }
50.             if (sr != null) { sr.Close(); }
51.         }
52.     }
53.     private string Clear(string value)
54.     {
55.         string headText = "终端号     交易日期 交易时间     交易帐号                交易金额     回扣     结算金额 授权码 交易类型";
56.         string footText = "小计      笔数";
57.         int headPos = value.IndexOf(headText) + headText.Length;
58.         int footPos = value.IndexOf(footText);
59.         string tradeRecord = value.Substring(headPos, footPos - headPos);
60.         return Regex.Replace(tradeRecord, @"( )+", " ", RegexOptions.None);
61.     }
62.     private IList<BocTrade> FormatToList(string value)
63.     {
64.         IList<BocTrade> bocTradeList = new List<BocTrade>();
65.         string[] tradeRecordList = value.Split(new string[] { "/r/n" }, StringSplitOptions.RemoveEmptyEntries);
66.         foreach (string item in tradeRecordList)
67.         {
68.             BocTrade bocTrade = new BocTrade();
69.             string[] bocArr = item.Split(' ');
70.             bocTrade.TerminalId = bocArr[0];
71.             bocTrade.TradeDate = bocArr[1];
72.             bocTrade.TradeTime = bocArr[2];
73.             bocTrade.TradeAccount = bocArr[3];
74.             bocTrade.TradeAount = bocArr[4];
75.             bocTrade.Commission = bocArr[5];
76.             bocTrade.BalanceAmout = bocArr[6];
77.             //根据特性判定bocArr[7]为AuthorizeCode，还是TradeType
78.             bocTrade.AuthorizeCode = string.Empty;
79.             bocTrade.TradeType = bocArr[7];
80.             bocTradeList.Add(bocTrade);
81.         }
82.         return bocTradeList;
83.     }
84.     public class BocTrade
85.     {
86.         private string terminalId;
87.         private string tradeDate;
88.         private string tradeTime;
89.         private string tradeAccount;
90.         private string tradeAount;
91.         private string commission;
92.         private string balanceAount;
93.         private string authorizeCode;
94.         private string tradeType;
96.         public string TerminalId
97.         {
98.             get { return terminalId; }
99.             set { terminalId = value; }
100.         }
101.         public string TradeDate
102.         {
103.             get { return tradeDate; }
104.             set { tradeDate = value; }
105.         }
106.         public string TradeTime
107.         {
108.             get { return tradeTime; }
109.             set { tradeTime = value; }
110.         }
111.         public string TradeAccount
112.         {
113.             get { return tradeAccount; }
114.             set { tradeAccount = value; }
115.         }
116.         public string TradeAount
117.         {
118.             get { return tradeAount; }
119.             set { tradeAount = value; }
120.         }
121.         public string Commission
122.         {
123.             get { return commission; }
124.             set { commission = value; }
125.         }
126.         public string BalanceAmout
127.         {
128.             get { return balanceAount; }
129.             set { balanceAount = value; }
130.         }
131.         public string AuthorizeCode
132.         {
133.             get { return authorizeCode; }
134.             set { authorizeCode = value; }
135.         }
136.         public string TradeType
137.         {
138.             get { return tradeType; }
139.             set { tradeType = value; }
140.         }
141.     }