public void InsertYucerawdata(Yucerawdata data) { int total = 0; string conStr = "server=localhost;User Id=root;database=aicai;" + "Password=root;Character Set=utf8;"; using (MySqlConnection mySqlCon = new MySqlConnection(conStr)) { mySqlCon.Open(); int recordCount = 0; using (MySqlCommand myCountCom = mySqlCon.CreateCommand()) { //有可能存在p2没有数据的情况,这时候只需要通过url进行比较就对了 if (string.IsNullOrEmpty(data.P2)) { string sqlCountStr = @" select count(*) from yuce_rawdata where url = ?url;"; myCountCom.CommandText = sqlCountStr; myCountCom.Parameters.AddWithValue("?url", data.Url); object obj = myCountCom.ExecuteScalar(); recordCount = Convert.ToInt32(obj); } else { string sqlCountStr = @" select count(*) from yuce_rawdata where url = ?url and p2 = ?p2;"; myCountCom.CommandText = sqlCountStr; myCountCom.Parameters.AddWithValue("?url", data.Url); myCountCom.Parameters.AddWithValue("?p2", data.P2); object obj = myCountCom.ExecuteScalar(); recordCount = Convert.ToInt32(obj); } } if (recordCount == 0) { using (MySqlCommand mySqlCom = mySqlCon.CreateCommand()) { //这里需要注意的是在 Sql 语句中有一个参数是 ?BlogsName //很明显,这个参数有点古怪,和我们一直使用的 @ 不一样, //这是因为在 MySql 以前的提供的 .NET Connector 中 //都是采用 ? 来标志一个参数的,而现在的 .NET Connector 呢, //您既可以采用 ? 来标志一个参数,您也可以使用一个 @ 符号来标志一个参数 //新版的 .NET Connector 对此都是支持的 string sqlStr = @" insert into yuce_rawdata(caizhongtype,publishdate,bisaishijian,title,url,p1,p2,p3,p4,p5,p6,p7,p8,operator,operatetime) values(?caizhongtype,?publishdate,?bisaishijian,?title,?url,?p1,?p2,?p3,?p4,?p5,?p6,?p7,?p8,?operator,?operatetime) "; mySqlCom.CommandText = sqlStr; mySqlCom.Parameters.AddWithValue("?caizhongtype", data.Caizhongtype); mySqlCom.Parameters.AddWithValue("?publishdate", data.Publishdate); mySqlCom.Parameters.AddWithValue("?bisaishijian", data.Bisaishijian); mySqlCom.Parameters.AddWithValue("?title", data.Title); mySqlCom.Parameters.AddWithValue("?url", data.Url); mySqlCom.Parameters.AddWithValue("?p1", data.P1); mySqlCom.Parameters.AddWithValue("?p2", data.P2); mySqlCom.Parameters.AddWithValue("?p3", data.P3); mySqlCom.Parameters.AddWithValue("?p4", data.P4); mySqlCom.Parameters.AddWithValue("?p5", data.P5); mySqlCom.Parameters.AddWithValue("?p6", data.P6); mySqlCom.Parameters.AddWithValue("?p7", data.P7); mySqlCom.Parameters.AddWithValue("?p8", data.P8); mySqlCom.Parameters.AddWithValue("?operator", data.Operator); mySqlCom.Parameters.AddWithValue("?operatetime", data.OperateTime); mySqlCom.ExecuteScalar(); } } } }
private void SaveYuceDetail(string caizhongtype, string url, string title, DateTime publishdate) { string urlTemplate = @"http://www.310win.com/{0}"; url = string.Format(urlTemplate, url); string htmlData = GetHtmlFromUrl(url); HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument(); document.LoadHtml(htmlData); HtmlNode rootNode = document.DocumentNode; HtmlNodeCollection articleContent = rootNode.SelectNodes(@"//div[@class='articleContent']"); HtmlNodeCollection p = articleContent[0].SelectNodes(@"./p"); //Console.WriteLine(p.Count); Yucerawdata data = null; bool alreadyTuijian = false; foreach (HtmlNode pChild in p) { string pText = pChild.InnerText.Trim(); if (pText.IndexOf("VS") != -1 || pText.IndexOf("vs") != -1) { //如果data没有初始化,说明没有添加信息 if (data == null) { data = new Yucerawdata(); data.OperateTime = DateTime.Now; data.Operator = "吴林"; data.Title = title; data.Url = url; data.Publishdate = publishdate; data.Caizhongtype = caizhongtype; data.P2 = pText; HtmlNode lastP = GetSiblingPNode(pChild); if (lastP != null) { data.P1 = lastP.InnerText.Trim(); } else { data.P1 = ""; } } else { //添加到数据库,并重设为初始值 new YucerawdataDAL().InsertYucerawdata(data); alreadyTuijian = false; data = new Yucerawdata(); data.OperateTime = DateTime.Now; data.Operator = "吴林"; data.Title = title; data.Url = url; data.Publishdate = publishdate; data.Caizhongtype = caizhongtype; data.P2 = pText; HtmlNode lastP = GetSiblingPNode(pChild); if (lastP != null) { data.P1 = lastP.InnerText.Trim(); } else { data.P1 = ""; } } //int vsIndex = pText.IndexOf("VS"); //string zhudui = pText.Substring(0, vsIndex).Trim(); //string kedui = pText.Substring(vsIndex + 2).Trim(); //Console.WriteLine(zhudui); //Console.WriteLine(kedui); } if (pText.IndexOf("开赛时间") != -1) { Console.WriteLine(pText); string[] innerStrings = pText.Split('\t'); data.P3 = innerStrings[0].Replace("开赛时间", "比赛时间"); if (innerStrings.Length > 1) { data.P4 = innerStrings[1].Replace(" ", ""); data.P5 = innerStrings[2].Replace(" ", ""); } if (innerStrings.Length >= 4) { data.P8 = innerStrings[3].Replace(" ", ""); } if (innerStrings.Length >= 5) { data.P9 = innerStrings[4].Replace(" ", ""); } alreadyTuijian = true; } else { if (pText.IndexOf("比赛时间") != -1) { //如果这里为空,说明之前没有取到VS,继续处理没有意义,先忽略掉 if (data == null) { data = new Yucerawdata(); data.OperateTime = DateTime.Now; data.Operator = "吴林"; data.Title = title; data.Url = url; data.Publishdate = publishdate; data.Caizhongtype = caizhongtype; data.P2 = pText; break; } data.P3 = pText; //int bisaiIndex = pText.Trim().IndexOf("比赛时间"); //string bisaishijian = pText.Trim().Substring(5); //Console.WriteLine(bisaishijian); } if (pText.IndexOf("欧赔") != -1) { data.P4 = pText; } if (pText.IndexOf("盘口") != -1) { data.P5 = pText; } if (pText.IndexOf("赛事分析") != -1) { data.P6 = pText; } if (pText.IndexOf("盘赔数据分析") != -1) { data.P7 = pText; } if (pText.IndexOf("推荐") != -1 && (pText.IndexOf("竞彩") != -1 || pText.IndexOf("北京单场") != -1 || pText.IndexOf("北单") != -1)) { if (!alreadyTuijian) { data.P8 = pText; alreadyTuijian = true; } } } } if (data != null) { new YucerawdataDAL().InsertYucerawdata(data); } }
private void btnImportCaikeDetail_Click(object sender, EventArgs e) { HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument(); document.Load("html/彩客咨询明细4.html", Encoding.UTF8); HtmlNode rootNode = document.DocumentNode; HtmlNodeCollection articleContent = rootNode.SelectNodes(@"//div[@class='articleContent']"); HtmlNodeCollection p = articleContent[0].SelectNodes(@"./p"); //Console.WriteLine(p.Count); Yucerawdata data = null; foreach (HtmlNode pChild in p) { string pText = pChild.InnerText.Trim(); if (pText.IndexOf("VS")!= -1 || pText.IndexOf("vs")!= -1) { //如果data没有初始化,说明没有添加信息 if (data == null) { data = new Yucerawdata(); data.OperateTime = DateTime.Now; data.Operator = "吴林"; data.P2 = pText; data.P1 = pChild.PreviousSibling.PreviousSibling.InnerText.Trim(); } else { //添加到数据库,并设为null new YucerawdataDAL().InsertYucerawdata(data); data = new Yucerawdata(); data.OperateTime = DateTime.Now; data.Operator = "吴林"; data.P2 = pText; data.P1 = pChild.PreviousSibling.PreviousSibling.InnerText.Trim(); } int vsIndex = pText.IndexOf("VS"); string zhudui = pText.Substring(0, vsIndex).Trim(); string kedui = pText.Substring(vsIndex+2).Trim(); Console.WriteLine(zhudui); Console.WriteLine(kedui); } if (pText.IndexOf("比赛时间") != -1) { data.P3 = pText; int bisaiIndex = pText.Trim().IndexOf("比赛时间"); string bisaishijian = pText.Trim().Substring(5); Console.WriteLine(bisaishijian); } if (pText.IndexOf("欧赔") != -1) { data.P4 = pText; } if (pText.IndexOf("盘口") != -1) { data.P5 = pText; } if (pText.IndexOf("盘口") != -1) { data.P5 = pText; } if (pText.IndexOf("赛事分析") != -1) { data.P6 = pText; } if(pText.IndexOf ("盘赔数据分析") != -1) { data.P7 = pText; } if (pText.IndexOf("推荐") != -1) //&& pText.IndexOf("竞彩") != -1 { data.P8 = pText; //int tuijianIndex = pText.Trim().IndexOf("推荐"); int tuijianIndex = pText.Trim().IndexOf(":"); string tuijian = pText.Trim().Substring(tuijianIndex); Console.WriteLine(tuijian); } } if(data != null) { new YucerawdataDAL().InsertYucerawdata(data); } }