private static bool InsertInfo(BusinessInfo info) { List <CommandInfo> commandList = new List <CommandInfo>(); CommandInfo insertBusiness = new CommandInfo(); insertBusiness.CommandText = "Insert Into business(BusinessID,BusinessTitle,ReleaseCom,ReleaseLocation,ReleaseTime,DetileURL,Source) Values (?BusinessID,?BusinessTitle,?ReleaseCom,?ReleaseLocation,?ReleaseTime,?DetileURL,?Source);"; MySqlParameter[] insertBusinessParameters = new MySqlParameter[] { new MySqlParameter("BusinessID", MySqlDbType.VarChar, 50), new MySqlParameter("BusinessTitle", MySqlDbType.VarChar, 200), new MySqlParameter("ReleaseCom", MySqlDbType.VarChar, 200), new MySqlParameter("ReleaseLocation", MySqlDbType.VarChar, 200), new MySqlParameter("ReleaseTime", MySqlDbType.Date), new MySqlParameter("DetileURL", MySqlDbType.VarChar, 500), new MySqlParameter("Source", MySqlDbType.VarChar, 50) }; insertBusinessParameters[0].Value = info.GUID; insertBusinessParameters[1].Value = info.Title; insertBusinessParameters[2].Value = info.ComName; insertBusinessParameters[3].Value = info.Location; insertBusinessParameters[4].Value = info.ReleaseTime; insertBusinessParameters[5].Value = info.DetileURL; insertBusinessParameters[6].Value = info.Source; insertBusiness.Parameters = insertBusinessParameters; commandList.Add(insertBusiness); CommandInfo insertDetile = new CommandInfo(); insertDetile.CommandText = "Insert Into Detile(DetileID,BusinessID,DetileURL,Content) Values(?DetileID,?BusinessID,?DetileURL,?Content);"; MySqlParameter[] insertDetileParameters = { new MySqlParameter("DetileID", MySqlDbType.VarChar, 50), new MySqlParameter("BusinessID", MySqlDbType.VarChar, 50), new MySqlParameter("DetileURL", MySqlDbType.VarChar, 500), new MySqlParameter("Content", MySqlDbType.VarChar, 5000) }; insertDetileParameters[0].Value = Guid.NewGuid().ToString(); insertDetileParameters[1].Value = info.GUID; insertDetileParameters[2].Value = info.DetileURL; insertDetileParameters[3].Value = info.Content; insertDetile.Parameters = insertDetileParameters; commandList.Add(insertDetile); int result = DbHelperMySQL.ExecuteSqlTran(commandList); if (result > 0) { return(true); } return(false); }
private static List <BusinessInfo> SearchKeyWord(List <string> keywordList) { keywordList = new List <string>() { "地理", "信息系统", "GIS", "软件", "地灾", "测绘" }; StringBuilder sb = new StringBuilder(); sb.Append("Select * From business where business.BusinessID in("); sb.Append("select BusinessID from View_QueryKeyWord where"); for (int i = 0; i < keywordList.Count; i++) { sb.Append(" View_QueryKeyWord.BusinessTitle"); sb.Append(" Like "); sb.Append(string.Format("'%{0}%'", keywordList[i])); sb.Append(" Or "); } for (int i = 0; i < keywordList.Count; i++) { sb.Append(" View_QueryKeyWord.Content"); sb.Append(" Like "); sb.Append(string.Format("'%{0}%'", keywordList[i])); if (i != (keywordList.Count - 1)) { sb.Append(" Or "); } } sb.Append(");"); string SQL_SearchKeyWord = sb.ToString(); List <BusinessInfo> result = new List <BusinessInfo>(); DataTable dt = DbHelperMySQL.GetDataTable(SQL_SearchKeyWord); for (int i = 0; i < dt.Rows.Count; i++) { BusinessInfo info = new BusinessInfo(); info.GUID = dt.Rows[i]["BusinessID"].ToString(); info.Title = dt.Rows[i]["BusinessTitle"].ToString(); info.ComName = dt.Rows[i]["ReleaseCom"].ToString(); info.Location = dt.Rows[i]["ReleaseLocation"].ToString(); info.ReleaseTime = DateTime.Parse(dt.Rows[i]["ReleaseTime"].ToString()); info.DetileURL = dt.Rows[i]["DetileURL"].ToString(); info.Source = dt.Rows[i]["Source"].ToString(); result.Add(info); } return(result); }
/// <summary> /// 处理商机List /// </summary> /// <param name="url"></param> /// <returns></returns> private static List <BusinessInfo> GetBusinessInfoList(string url) { string html = GetHTML(url); HtmlParser parser = new HtmlParser(); IHtmlDocument document = parser.Parse(html); List <AngleSharp.Dom.IElement> divList = document.QuerySelectorAll("table") .Where(table => table.GetAttribute("id") == "moredingannctable") .ToList(); IElement tableElement = divList[0]; var trList = tableElement.GetElementsByTagName("tr"); List <BusinessInfo> businessList = new List <BusinessInfo>(); for (int i = 0; i < trList.Length; i = i + 2) { BusinessInfo info = new BusinessInfo(); var aList = trList[i].QuerySelectorAll("a"); if (aList.Length > 0) { info.Title = aList[0].Text(); } string codeString = trList[i].GetAttribute("onclick"); string code = codeString.Substring(codeString.IndexOf("'") + 1, 6); info.DetileURL = "http://www.ccgp-hebei.gov.cn/zfcg/1/bidingAnncDetail_" + code + ".html"; info.Content = GetDetileByURL(info.DetileURL); var spanList = trList[i + 1].QuerySelectorAll("span"); if (spanList.Length > 0) { info.ReleaseTime = DateTime.Parse(spanList[0].Text()); info.Location = spanList[1].Text(); info.ComName = spanList[2].Text(); } info.Source = new HBZFCGW().Name; bool InsertSQL = InsertInfo(info); Console.WriteLine("{0}插入数据库{1}", info.Title, InsertSQL ? "成功" : "失败"); businessList.Add(info); } return(businessList); }