public int InsertPeilv(Peilv peilv)
{
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();
using (MySqlCommand mySqlCom = mySqlCon.CreateCommand())
{
//这里需要注意的是在 Sql 语句中有一个参数是 ?BlogsName
//很明显，这个参数有点古怪，和我们一直使用的 @ 不一样，
//这是因为在 MySql 以前的提供的 .NET Connector 中
//都是采用 ? 来标志一个参数的，而现在的 .NET Connector 呢，
//您既可以采用 ? 来标志一个参数，您也可以使用一个 @ 符号来标志一个参数
//新版的 .NET Connector 对此都是支持的
string sqlStr = @"insert into peilv
(
riqi,liansai,bianhao,zhudui,zhuduipaiming,kedui,keduipaiming,shengsp,pingsp,fusp,rangqiu,operator,operatetime,endtime
)
values
(
?riqi,?liansai,?bianhao,?zhudui,?zhuduipaiming,?kedui,?keduipaiming,?shengsp,?pingsp,?fusp,?rangqiu,?operator,?operatetime,?endtime
)
";
//"SELECT COUNT(*) FROM BlogsUsers WHERE BlogsName=?BlogsName";
mySqlCom.CommandText = sqlStr;
mySqlCom.Parameters.AddWithValue("?riqi", peilv.Riqi);
mySqlCom.Parameters.AddWithValue("?liansai", peilv.Liansai);
mySqlCom.Parameters.AddWithValue("?bianhao", peilv.Bianhao);
mySqlCom.Parameters.AddWithValue("?zhudui", peilv.Zhudui);
mySqlCom.Parameters.AddWithValue("?zhuduipaiming", peilv.Zhuduipaiming);
mySqlCom.Parameters.AddWithValue("?kedui", peilv.Kedui);
mySqlCom.Parameters.AddWithValue("?keduipaiming", peilv.Keduipaiming);
mySqlCom.Parameters.AddWithValue("?shengsp", peilv.ShengSp);
mySqlCom.Parameters.AddWithValue("?pingsp", peilv.PingSp);
mySqlCom.Parameters.AddWithValue("?fusp", peilv.FuSp);
mySqlCom.Parameters.AddWithValue("?rangqiu", peilv.Rangqiu);
mySqlCom.Parameters.AddWithValue("?operator", peilv.Operator);
mySqlCom.Parameters.AddWithValue("?operatetime", peilv.OperateTime);
mySqlCom.Parameters.AddWithValue("?endtime", peilv.OperateTime);
total = Convert.ToInt32(mySqlCom.ExecuteScalar());
}
}
return total;
}
private void btnImportPeilv_Click(object sender, EventArgs e)
{
try
{
HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument();
//document.LoadHtml(html);
//string fileName = "html/赔率20140829.html";
//document.Load(fileName, Encoding.UTF8);
string url = @"http://www.aicai.com/jczq/";
string jsonData = GetHtmlFromUrl(url);
//预处理，某些页面的class='phaoTd' 带有空格
if (jsonData.IndexOf(@"class=""phaoTd """) != -1)
{
jsonData = jsonData.Replace(@"class=""phaoTd """, @"class=""phaoTd""");
}
if (jsonData.IndexOf(@"class=""phaoTd pr""") != -1)
{
jsonData = jsonData.Replace(@"class=""phaoTd pr""", @"class=""phaoTd""");
}
document.LoadHtml(jsonData);
HtmlNode rootNode = document.DocumentNode;
HtmlNodeCollection tbodies = rootNode.SelectNodes("//tbody[@class='jq_gdhhspf_sort_table']");
foreach (HtmlNode tbody in tbodies)
{
HtmlNode gameTime = tbody.SelectSingleNode("./tr/td[@class='gameTime']");
String xinqi = gameTime.InnerText.Substring(0, 3);
String riqi = gameTime.InnerText.Substring(4, 10);
//增加前先删掉原来的数据
new PeilvDAL().DeletePeilv(riqi);
HtmlNodeCollection matches = tbody.SelectNodes("./tr[@class='jq_gdhhspf_match_select_tr']");
if (matches == null)
{
continue;
}
//增加偶数节点
HtmlNodeCollection matchesEven =
tbody.SelectNodes("./tr[@class='jq_gdhhspf_match_select_tr evenTr']");
//jq_gdhhspf_match_select_tr evenTr
foreach (HtmlNode m in matchesEven)
{
matches.Add(m);
}
foreach (HtmlNode match in matches)
{
Peilv peilv = new Peilv();
peilv.Riqi = riqi;
HtmlNode phaoTd = match.SelectSingleNode("./td[@class='phaoTd']");
//peilv.Bianhao = phaoTd.InnerText;
//编号的逻辑由于新版本，需要进行修改
peilv.Bianhao = phaoTd.SelectSingleNode("./a[@class='jq_gdhhspf_selectmatch']").InnerText;
HtmlNode saiTd = match.SelectSingleNode("./td[@class='saiTd']");
peilv.Liansai = saiTd.InnerText;
HtmlNode zhuTeamTd = match.SelectSingleNode("./td[@class='zhuTeamTd']");
peilv.Zhudui = zhuTeamTd.InnerText;
HtmlNode keTeamTd = match.SelectSingleNode("./td[@class='keTeamTd']");
peilv.Kedui = keTeamTd.InnerText;
HtmlNodeCollection betPanels = match.SelectNodes("./td/div[@class='betPanel']");
foreach (HtmlNode betPanel in betPanels)
{
string rangqiu = betPanel.ChildNodes[1].ChildNodes[0].InnerText;
peilv.Rangqiu = Convert.ToInt32(rangqiu);
HtmlNodeCollection sps = betPanel.SelectNodes("./div[@class='betChan betChoose ']");
if (sps == null)
{
peilv.ShengSp = 0;
peilv.PingSp = 0;
peilv.FuSp = 0;
}
else
{
peilv.ShengSp = Convert.ToDouble(sps[0].InnerText);
peilv.PingSp = Convert.ToDouble(sps[1].InnerText);
peilv.FuSp = Convert.ToDouble(sps[2].InnerText);
}
peilv.OperateTime = DateTime.Now;
peilv.Operator = "吴林";
new PeilvDAL().InsertPeilv(peilv);
}
}
}
MessageBox.Show("导入赔率成功！");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void ImportPeilv(DateTime importdate)
{
try
{
DateTime importDate = importdate;
string url = GetKaijiangUrl(importDate);
string jsonData = GetHtmlFromUrl(url);
Dictionary<string, object> dict = JsonConvert.DeserializeObject<Dictionary<string, object>>(jsonData);
String raceList = dict["raceList"].ToString();
Dictionary<string, object> dict2 = JsonConvert.DeserializeObject<Dictionary<string, object>>(raceList);
String weekDay = dict["weekDay"].ToString();
string weekDate = dict["weekDate"].ToString();
foreach (string key in dict2.Keys)
{
string value = dict2[key].ToString();
Race race = JsonConvert.DeserializeObject<Race>(value);
Peilv peilv = new Peilv();
peilv.Bianhao = key;
peilv.Liansai = race.matchName;
peilv.Zhudui = race.homeTeam;
peilv.Kedui = race.guestTeam;
peilv.Riqi = weekDate;
peilv.Operator = "开奖导入";
peilv.OperateTime = DateTime.Now;
peilv.EndTime = Convert.ToDateTime(race.endTime);
string[] xsp = race.xspfSp.Split('-');
string[] sp = race.spfSp.Split('-');
peilv.Rangqiu = 0;
peilv.ShengSp = Convert.ToDouble(xsp[0]);
peilv.PingSp = Convert.ToDouble(xsp[1]);
peilv.FuSp = Convert.ToDouble(xsp[2]);
PeilvDAL dal = new PeilvDAL();
dal.InsertPeilv(peilv);
peilv.Rangqiu = Convert.ToInt32(race.concede);
peilv.ShengSp = Convert.ToDouble(sp[0]);
peilv.PingSp = Convert.ToDouble(sp[1]);
peilv.FuSp = Convert.ToDouble(sp[2]);
dal.InsertPeilv(peilv);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
