private async void WriteCityWeather(DataTable cityList)
{
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + nowfilepath + ";"
+ "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = objConn.CreateCommand();
objCmdSelect.CommandText = "create TABLE Weather ("
+ "[QX_ZH] VarChar,"
+ "[QX_EN] VarChar,"
+ "[SF] VarChar,"
+ "[CITY] VarChar,"
+ "[TEXT] VarChar,"
+ "[CODE] VarChar,"
+ "[TEMPERATURE] VarChar,"
+ "[FEELS_LIKE] VarChar,"
+ "[PRESSURE] VarChar,"
+ "[HUMIDITY] VarChar,"
+ "[VISIBILITY] VarChar,"
+ "[WIND_DIRECTION] VarChar,"
+ "[WIND_DIRECTION_DEGREE] VarChar,"
+ "[WIND_SPEED] VarChar,"
+ "[WIND_SCALE] VarChar,"
+ "[CLOUDS] VarChar,"
+ "[DEW_POINT] VarChar,"
+ "[LAST_UPDATE] VarChar)";
objCmdSelect.ExecuteNonQuery();
int count = 0;
if (OnlyGetHLJ.Checked)
{
//cityList.DefaultView.Table.Select("SF='黑龙江'");
//cityList = cityList.DefaultView.ToTable();
DataView view = new DataView();
view.Table = cityList;
view.RowFilter = "SF='黑龙江'";
cityList = view.ToTable();
}
foreach (DataRow row in cityList.Rows)
{
string url = weatherUrl + "key=" + key + "&location=" + row[0];
HttpClient client = new HttpClient();
Task <string> getContentsTask = client.GetStringAsync(url);
string urlContents = await getContentsTask;
JObject jsonObj = JObject.Parse(urlContents);
JArray jsonArray = JArray.Parse(jsonObj["results"].ToString());
//string body = await client.GetStringAsync(url);
objCmdSelect.CommandText = "insert into Weather "
+ "values('" + row[1] + "','" + row[2] + "','" + row[3] + "','" + row[4]
+ "','" + jsonArray[0]["now"]["text"] + "','" + jsonArray[0]["now"]["code"]
+ "','" + jsonArray[0]["now"]["temperature"] + "','" + jsonArray[0]["now"]["feels_like"]
+ "','" + jsonArray[0]["now"]["pressure"] + "','" + jsonArray[0]["now"]["humidity"]
+ "','" + jsonArray[0]["now"]["visibility"] + "','" + jsonArray[0]["now"]["wind_direction"]
+ "','" + jsonArray[0]["now"]["wind_direction_degree"] + "','" + jsonArray[0]["now"]["wind_speed"]
+ "','" + jsonArray[0]["now"]["wind_scale"] + "','" + jsonArray[0]["now"]["clouds"]
+ "','" + jsonArray[0]["now"]["dew_point"] + "','" + jsonArray[0]["last_update"]
+ "')";
objCmdSelect.ExecuteNonQuery();
TextInvoke invoke1 = new TextInvoke(UpdateText1);
BeginInvoke(invoke1, new object[] { "已完成:" + ++count + "/" + cityList.Rows.Count });
BarInvoke invoke2 = new BarInvoke(UpdateWeatherBar);
BeginInvoke(invoke2, new object[] { count * 100 / cityList.Rows.Count });
//text1.Text = "已完成:" + ++count + "/" + cityList.Rows.Count;
//WeatherBar.Value = count * 100 / cityList.Rows.Count;
}
objConn.Close();
apikeylabel.Text = "本轮数据获取完毕\n\n下一轮预计开始时间：" + when.ToLocalTime();
//MessageBox.Show("本轮数据获取完毕 下一轮开始时间：");
}
private async void WriteCityAir(DataTable cityList)
{
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + nowfilepath + ";"
+ "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = objConn.CreateCommand();
objCmdSelect.CommandText = "create TABLE Air ("
+ "[SF] VarChar,"
+ "[CITY] VarChar,"
+ "[STATION] VarChar,"
+ "[AQI] VarChar,"
+ "[PM25] VarChar,"
+ "[PM10] VarChar,"
+ "[SO2] VarChar,"
+ "[NO2] VarChar,"
+ "[CO] VarChar,"
+ "[O3] VarChar,"
+ "[LAST_UPDATE] VarChar,"
+ "[CITYID] VarChar,"
+ "[LATITUDE] VarChar,"
+ "[LONGITUDE] VarChar)";
objCmdSelect.ExecuteNonQuery();
int count = 0;
//DataRow[] daraRow = cityList.Rows.Remove();
if (OnlyGetHLJ.Checked)
{
//cityList.DefaultView.Table.Select("SF='黑龙江'");
//cityList = cityList.DefaultView.ToTable();
DataView view = new DataView();
view.Table = cityList;
view.RowFilter = "SF='黑龙江'";
cityList = view.ToTable();
}
foreach (DataRow row in cityList.Rows)
{
string url = airUrl + "&key=" + key + "&location=" + row[0];
HttpClient client = new HttpClient();
Task <string> getContentsTask = client.GetStringAsync(url);
string urlContents = await getContentsTask;
JObject jsonObject = JObject.Parse(urlContents);
JArray jsonArray = JArray.Parse(jsonObject["results"].ToString());
if (jsonArray[0]["air"].ToString() == "")
{
++count;
continue;
}
JArray jsonStations = JArray.Parse(jsonArray[0]["air"]["stations"].ToString());
foreach (JObject station in jsonStations)
{
objCmdSelect.CommandText = "insert into Air "
+ "values('" + row[3] + "','" + row[4]
+ "','" + station["station"] + "','" + station["aqi"]
+ "','" + station["pm25"] + "','" + station["pm10"]
+ "','" + station["so2"] + "','" + station["no2"]
+ "','" + station["co"] + "','" + station["o3"]
+ "','" + station["last_update"] + "','" + row[0]
+ "','" + station["latitude"] + "','" + station["longitude"]
+ "')";
objCmdSelect.ExecuteNonQuery();
}
TextInvoke invoke1 = new TextInvoke(UpdateText2);
BeginInvoke(invoke1, new object[] { "已完成:" + ++count + "/" + cityList.Rows.Count });
//text2.Text = "已完成:" + ++count + "/" + cityList.Rows.Count;
BarInvoke invoke2 = new BarInvoke(UpdateAirBar);
BeginInvoke(invoke2, new object[] { count * 100 / cityList.Rows.Count });
//AirBar.Value = count * 100 / cityList.Rows.Count;
}
objConn.Close();
//MessageBox.Show("空气质量数据获取完毕");
}
