一、新建ASP.NET Core Web API项目
（1）建立步骤如下图所示：
（2）建立后的目录：
其中 Helper文件夹里面是自己写的一些通用方法类，主要完成代码在Program.cs进行编写
二、创建Helper里面的通用方法类，比如连接数据库进行增删改查等
（1）GlobalFunction.cs 具体方法看以下代码，每一步基本都有注释
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace WebFrontService
{
public class GlobalFunction
{
public SqlConnection con = null;
public SqlConnection GetConnection()
{
if (con == null)
{
// var str = GlobalData.connectStr;//获取配置文件中的数据库连接字符串
con = new SqlConnection(GetConnString());//实例化
try
{
con.Open();
}
catch (Exception e)
{
Sqlclose();
return null;
}
}
else
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
}
return con;
}
///Sql数据库登录
public string ConnectDb(string sql, string UserPwd)
{
string connStr = "Data Source=服务器;database=数据库; uid = 账号; pwd = 密码";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
var reader = cmd.ExecuteScalar();
if (reader.ToString() == UserPwd)
{
conn.Close();
return "success";
}
else
{
conn.Close();
return "failure";
}
}
/// <summary>
///MsSql数据库查询，返回datatable
/// </summary>
/// <param name="SearchStr">sql字符串</param>
/// <param name="dt">回传数据datatable</param>
/// <param name="con">数据库连接</param>
public string selectDB(string SearchStr, ref DataTable dt)
{
//连接数据库///
//SqlConnection con = new SqlConnection(GetConnString());
try
{
GetConnection();
}
catch
{
return ("数据库连接错误，请检查通信线路和数据库配置及运行状态！");
}
//
DataSet ds = new DataSet();
SqlDataAdapter DaSearch = new SqlDataAdapter(SearchStr, con);
try
{
DaSearch.Fill(ds, "table");
}
catch
{
Sqlclose();
return ("数据库查询错误！");
}
finally
{
//con.Close();
}
dt = ds.Tables["table"];
return ("success");
}
public string selectDB1(string SearchStr, ref DataSet dt)
{
//连接数据库///
//SqlConnection con = new SqlConnection(GetConnString());
try
{
GetConnection();
}
catch
{
return ("数据库连接错误，请检查通信线路和数据库配置及运行状态！");
}
//
SqlDataAdapter DaSearch = new SqlDataAdapter(SearchStr, con);
DaSearch.Fill(dt, "table");
return ("success");
}
/// <summary>
///MsSql数据库更新、删除、插入
/// </summary>
/// <param name="SearchStr">sql字符串</param>
/// <param name="con">数据库连接</param>
public string updateDB(string updateStr)
{
连接数据库///
//SqlConnection con = new SqlConnection(GetConnString());
try
{
GetConnection();
}
catch
{
return ("数据库连接错误，请检查通信线路和数据库运行状态！");
}
SqlCommand UpdateCmd = new SqlCommand(updateStr, con);
try
{
UpdateCmd.ExecuteNonQuery();
}
catch
{
Sqlclose();
return ("更新数据库错误！");
}
finally
{
// con.Close();
}
return ("更新数据库成功！");
}
protected static string GetConnString()
{
//return "Data Source=服务器;database=数据库; uid =账号; pwd =密码";
return "Data Source=服务器;database=数据库; uid =账号; pwd =密码";
}
public static DataTable buildDataTable(string strTableColumn, string strTableData)
{
DataTable dt = new DataTable();
string[] strColumn = strTableColumn.Split('|');
for (int i = 0; i < strColumn.Length; i++)
{
dt.Columns.Add(strColumn[i], Type.GetType("System.String"));
}
DataRow newRow;
newRow = dt.NewRow();
string[] strData = strTableData.Split('|');
for (int i = 0; i < strColumn.Length; i++)
{
newRow[i] = strData[i];
}
dt.Rows.Add(newRow);
return dt;
}
public void Sqlclose()
{
if (con != null)
{
try
{
con.Close();
}
catch
{
}
}
}
}
}
（2）SqlHelper.cs 代码过多，这里就不展示，功能和上面类似
三、Program.cs代码编写
（1）首先引入通用类，方便后续调用：
public static GlobalFunction GlobalFunction = new GlobalFunction();
public static SqlHelper SqlHelper = new SqlHelper();
（2）构建requests方法，方便后续请求：
public static string Requests(string RequestUrl, string RequestMethod)
{
string RequestUrls = RequestUrl;
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(RequestUrls);
request.Method = RequestMethod;
request.ContentType = "textml;charset=UTF-8";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
Stream myResponseStream = response.GetResponseStream();
StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.UTF8);
string jsonData = myStreamReader.ReadToEnd();
myStreamReader.Close();
myResponseStream.Close();
string jsonString = jsonData;
return jsonString;
}
（3）参照百度统计api接口构建请求（具体请求方法参照这个网址：https://tongji.baidu.com/api/manual/Chapter1/trend_time_a.html）
参数如下：
请求api地址如下：https://openapi.baidu.com/rest/2.0/tongji/report/getData?access_token=xxxxxxxx&site_id=xxxx&method=trend/time/a&start_date=20190101&end_date=20190105&metrics=pv_count,avg_visit_time&gran=day&source=through&clientDevice=pc&area=china
同时也可以通过分析网页查看请求的地址和参数，如下图所示：
api接口构建代码：
string RequestUrl1 = String.Format("https://openapi.baidu.com/rest/2.0/tongji/report/getData?access_token={0}&site_id={1}&method=trend/time/a&start_date={2}&end_date={3}&metrics=pv_count,pv_ratio,visit_count,visitor_count," +
"new_visitor_count,new_visitor_ratio,ip_count,bounce_ratio,avg_visit_time,avg_visit_pages,trans_count,trans_ratio&gran=hour{4}", AccessToken, 19825497, ntime1, ntime1, List11[j]);
string jsonString1 = Requests(RequestUrl1, "GET");
JObject json1 = JObject.Parse(jsonString1);
这里的json1就是返回的数据（json格式）
（4）讲数据存入sql server：
string SearchStr = " insert into [DataAnalysis].[dbo].[TrendAnalysis](SiteId,PvCount,PvRatio,VisitCount,VisitorCount,NewVisitorCount,NewVisitorRatio,IpCount,BounceRatio,AvgVisitTime,AvgVisitPages,TransCount,TransRatio,Hour,Days,InsertTime,Type)"
+ " values(" + 19825497 + ",'" + json1["result"]["items"][1][z][0].ToString() + "','" + json1["result"]["items"][1][z][1].ToString() + "','" + json1["result"]["items"][1][z][2].ToString() + "',"
+ " '" + json1["result"]["items"][1][z][3].ToString() + "','" + json1["result"]["items"][1][z][4].ToString() + "','" + json1["result"]["items"][1][z][5].ToString() + "','" + json1["result"]["items"][1][z][6].ToString() + "'," +
" '" + json1["result"]["items"][1][z][7].ToString() + "','" + json1["result"]["items"][1][z][8].ToString() + "','" + json1["result"]["items"][1][z][9].ToString() + "','" + json1["result"]["items"][1][z][10].ToString() + "'," +
" '" + json1["result"]["items"][1][z][11].ToString() + "','" + json1["result"]["items"][0][z][0].ToString() + "', '" + ntime1 + "','" + DateTime.Now + "','" + List11[j] + "')";
string str2 = GlobalFunction.updateDB(SearchStr);
（5）定时任务的编写：
System.Timers.Timer timer = new System.Timers.Timer();
timer.Enabled = true;
timer.Interval = 60000;//执行间隔时间,单位为毫秒;此时时间间隔为1分钟
timer.Start();
timer.Elapsed += new System.Timers.ElapsedEventHandler(GetBasic1);
Console.ReadKey();
四、完整Program.cs代码
using Newtonsoft.Json.Linq;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Net;
using System.Text;
using System.Threading;
using System.Timers;
using System.Xml.Linq;
using WebFrontService;
namespace TimerExample1
{
class Program
{
public static GlobalFunction GlobalFunction = new GlobalFunction();
public static SqlHelper SqlHelper = new SqlHelper();
public static string Requests(string RequestUrl, string RequestMethod)
{
string RequestUrls = RequestUrl;
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(RequestUrls);
request.Method = RequestMethod;
request.ContentType = "textml;charset=UTF-8";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
Stream myResponseStream = response.GetResponseStream();
StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.UTF8);
string jsonData = myStreamReader.ReadToEnd();
myStreamReader.Close();
myResponseStream.Close();
string jsonString = jsonData;
return jsonString;
}
static void Main(string[] args)
{
/*
DateTime start = Convert.ToDateTime("2023-09-01");
DateTime end = Convert.ToDateTime(DateTime.Now);
TimeSpan sp = end.Subtract(start);
Console.WriteLine(sp.Days);
string sql1 = "select AccessToken from [DataAnalysis].[dbo].[UpdateAccessToken] where Id = 1 ";
string AccessToken = SqlHelper.SelctDataj(sql1);
string RequestUrl1 = String.Format("https://openapi.baidu.com/rest/2.0/tongji/report/getData?access_token={0}&site_id={1}&method=trend/time/a&start_date={2}&end_date={3}&metrics=pv_count,pv_ratio,visit_count,visitor_count," +
"new_visitor_count&gran=day&area=china", AccessToken, 19825497, 20231205, 20231210);
string jsonString1 = Requests(RequestUrl1, "GET");
JObject json1 = JObject.Parse(jsonString1);
Console.WriteLine(json1);
List<object> List1 = new List<object>();
List1.Add("&clientDevice=pc");
List1.Add("&clientDevice=mobile");
List1.Add("&visitor=new");
List1.Add("&visitor=old");
List1.Add("&clientDevice=pc&visitor=new");
List1.Add("&clientDevice=pc&visitor=old");
List1.Add("&clientDevice=mobile&visitor=new");
List1.Add("&clientDevice=mobile&visitor=old");
Console.WriteLine(List1.Count());
*/
System.Timers.Timer timer = new System.Timers.Timer();
timer.Enabled = true;
timer.Interval = 60000;//执行间隔时间,单位为毫秒;此时时间间隔为1分钟
timer.Start();
timer.Elapsed += new System.Timers.ElapsedEventHandler(GetBasic1);
Console.ReadKey();
}
private static void test(object source, ElapsedEventArgs e)
{
if (DateTime.Now.Hour == 13 && DateTime.Now.Minute == 28)
{
List<object> List11 = new List<object>();
List11.Add("&clientDevice=pc&visitor=all");
List11.Add("&clientDevice=mobile&visitor=all");
List11.Add("&clientDevice=all&visitor=new");
List11.Add("&clientDevice=all&visitor=old");
List11.Add("&clientDevice=all&visitor=all");
List11.Add("&clientDevice=pc&visitor=new");
List11.Add("&clientDevice=pc&visitor=old");
List11.Add("&clientDevice=mobile&visitor=new");
List11.Add("&clientDevice=mobile&visitor=old");
for (int j = 0; j < List11.Count(); j++)
{
string sql1 = "select AccessToken from [DataAnalysis].[dbo].[UpdateAccessToken] where Id = 1 ";
string AccessToken = SqlHelper.SelctDataj(sql1);
DateTime dt = Convert.ToDateTime("2023-12-06");
for (int i = 35; i > 0; i--)
{
string day1 = dt.AddDays(-i).ToString("yyyy-MM-dd");
int ntime1 = int.Parse(day1.Split('-')[0] + day1.Split('-')[1] + day1.Split('-')[2]);
string RequestUrl1 = String.Format("https://openapi.baidu.com/rest/2.0/tongji/report/getData?access_token={0}&site_id={1}&method=trend/time/a&start_date={2}&end_date={3}&metrics=pv_count,pv_ratio,visit_count,visitor_count," +
"new_visitor_count,new_visitor_ratio,ip_count,bounce_ratio,avg_visit_time,avg_visit_pages,trans_count,trans_ratio&gran=hour{4}", AccessToken, 19825497, ntime1, ntime1, List11[j]);
string jsonString1 = Requests(RequestUrl1, "GET");
JObject json1 = JObject.Parse(jsonString1);
for (int z = 0; z < json1["result"]["items"][0].Count(); z++)
{
string SearchStr = " insert into [DataAnalysis].[dbo].[TrendAnalysis](SiteId,PvCount,PvRatio,VisitCount,VisitorCount,NewVisitorCount,NewVisitorRatio,IpCount,BounceRatio,AvgVisitTime,AvgVisitPages,TransCount,TransRatio,Hour,Days,InsertTime,Type)"
+ " values(" + 19825497 + ",'" + json1["result"]["items"][1][z][0].ToString() + "','" + json1["result"]["items"][1][z][1].ToString() + "','" + json1["result"]["items"][1][z][2].ToString() + "',"
+ " '" + json1["result"]["items"][1][z][3].ToString() + "','" + json1["result"]["items"][1][z][4].ToString() + "','" + json1["result"]["items"][1][z][5].ToString() + "','" + json1["result"]["items"][1][z][6].ToString() + "'," +
" '" + json1["result"]["items"][1][z][7].ToString() + "','" + json1["result"]["items"][1][z][8].ToString() + "','" + json1["result"]["items"][1][z][9].ToString() + "','" + json1["result"]["items"][1][z][10].ToString() + "'," +
" '" + json1["result"]["items"][1][z][11].ToString() + "','" + json1["result"]["items"][0][z][0].ToString() + "', '" + ntime1 + "','" + DateTime.Now + "','" + List11[j] + "')";
string str2 = GlobalFunction.updateDB(SearchStr);
}
Console.WriteLine(ntime1);
}
Console.WriteLine(List11[j]);
}
}
}
private static void GetBasic1(object source, System.Timers.ElapsedEventArgs e)
{
if (DateTime.Now.Hour == 00 && DateTime.Now.Minute == 17)
{
DataTable dt1 = new DataTable();
string s1 = "select SiteId from [DataAnalysis].[dbo].[SiteList]";
string str1 = GlobalFunction.selectDB(s1, ref dt1);
for (int j = 0; j < dt1.Rows.Count; j++)
{
int SiteId = int.Parse(dt1.Rows[j]["SiteId"].ToString());
string st1 = "select Top 1 InsertTime from [DataAnalysis].[dbo].[TrendAnalysis] where SiteId = '" + SiteId + "' order by InsertTime desc ";
string time1 = SqlHelper.SelctDataj(st1);
DateTime start = Convert.ToDateTime(time1);
DateTime end = Convert.ToDateTime(DateTime.Now);
TimeSpan sp = end.Subtract(start);
string sql1 = "select AccessToken from [DataAnalysis].[dbo].[UpdateAccessToken] where Id = 1 ";
string AccessToken = SqlHelper.SelctDataj(sql1);
List<object> List11 = new List<object>();
List11.Add("&clientDevice=pc&visitor=all");
List11.Add("&clientDevice=mobile&visitor=all");
List11.Add("&clientDevice=all&visitor=new");
List11.Add("&clientDevice=all&visitor=old");
List11.Add("&clientDevice=all&visitor=all");
List11.Add("&clientDevice=pc&visitor=new");
List11.Add("&clientDevice=pc&visitor=old");
List11.Add("&clientDevice=mobile&visitor=new");
List11.Add("&clientDevice=mobile&visitor=old");
if (sp.Days <= 1)
{
for (int j1 = 0; j1 < List11.Count(); j1++)
{
string time = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
int ntime1 = int.Parse(time.Split('-')[0] + time.Split('-')[1] + time.Split('-')[2]);
string RequestUrl1 = String.Format("https://openapi.baidu.com/rest/2.0/tongji/report/getData?access_token={0}&site_id={1}&method=trend/time/a&start_date={2}&end_date={3}&metrics=pv_count,pv_ratio,visit_count,visitor_count," +
"new_visitor_count,new_visitor_ratio,ip_count,bounce_ratio,avg_visit_time,avg_visit_pages,trans_count,trans_ratio&gran=hour{4}", AccessToken, SiteId, ntime1, ntime1, List11[j1]);
string jsonString1 = Requests(RequestUrl1, "GET");
JObject json1 = JObject.Parse(jsonString1);
for (int z = 0; z < json1["result"]["items"][0].Count(); z++)
{
string SearchStr = " insert into [DataAnalysis].[dbo].[TrendAnalysis](SiteId,PvCount,PvRatio,VisitCount,VisitorCount,NewVisitorCount,NewVisitorRatio,IpCount,BounceRatio,AvgVisitTime,AvgVisitPages,TransCount,TransRatio,Hour,Days,InsertTime,Type)"
+ " values(" + SiteId + ",'" + json1["result"]["items"][1][z][0].ToString() + "','" + json1["result"]["items"][1][z][1].ToString() + "','" + json1["result"]["items"][1][z][2].ToString() + "',"
+ " '" + json1["result"]["items"][1][z][3].ToString() + "','" + json1["result"]["items"][1][z][4].ToString() + "','" + json1["result"]["items"][1][z][5].ToString() + "','" + json1["result"]["items"][1][z][6].ToString() + "'," +
" '" + json1["result"]["items"][1][z][7].ToString() + "','" + json1["result"]["items"][1][z][8].ToString() + "','" + json1["result"]["items"][1][z][9].ToString() + "','" + json1["result"]["items"][1][z][10].ToString() + "'," +
" '" + json1["result"]["items"][1][z][11].ToString() + "','" + json1["result"]["items"][0][z][0].ToString() + "', '" + ntime1 + "','" + DateTime.Now + "','" + List11[j1] + "')";
string str2 = GlobalFunction.updateDB(SearchStr);
}
}
Console.WriteLine(String.Format("插入数据成功，当前时间：{0}，相差天数 {1} 天！", DateTime.Now, sp.Days));
}
else
{
DateTime dt = Convert.ToDateTime(DateTime.Now);
for (int j1 = 0; j1 < List11.Count(); j1++)
{
for (int i = sp.Days; i > 0; i--)
{
string day1 = dt.AddDays(-i).ToString("yyyy-MM-dd");
int ntime1 = int.Parse(day1.Split('-')[0] + day1.Split('-')[1] + day1.Split('-')[2]);
string RequestUrl1 = String.Format("https://openapi.baidu.com/rest/2.0/tongji/report/getData?access_token={0}&site_id={1}&method=trend/time/a&start_date={2}&end_date={3}&metrics=pv_count,pv_ratio,visit_count,visitor_count," +
"new_visitor_count,new_visitor_ratio,ip_count,bounce_ratio,avg_visit_time,avg_visit_pages,trans_count,trans_ratio&gran=hour{4}", AccessToken, SiteId, ntime1, ntime1, List11[j1]);
string jsonString1 = Requests(RequestUrl1, "GET");
JObject json1 = JObject.Parse(jsonString1);
for (int z = 0; z < json1["result"]["items"][0].Count(); z++)
{
string SearchStr = " insert into [DataAnalysis].[dbo].[TrendAnalysis](SiteId,PvCount,PvRatio,VisitCount,VisitorCount,NewVisitorCount,NewVisitorRatio,IpCount,BounceRatio,AvgVisitTime,AvgVisitPages,TransCount,TransRatio,Hour,Days,InsertTime,Type)"
+ " values(" + SiteId + ",'" + json1["result"]["items"][1][z][0].ToString() + "','" + json1["result"]["items"][1][z][1].ToString() + "','" + json1["result"]["items"][1][z][2].ToString() + "',"
+ " '" + json1["result"]["items"][1][z][3].ToString() + "','" + json1["result"]["items"][1][z][4].ToString() + "','" + json1["result"]["items"][1][z][5].ToString() + "','" + json1["result"]["items"][1][z][6].ToString() + "'," +
" '" + json1["result"]["items"][1][z][7].ToString() + "','" + json1["result"]["items"][1][z][8].ToString() + "','" + json1["result"]["items"][1][z][9].ToString() + "','" + json1["result"]["items"][1][z][10].ToString() + "'," +
" '" + json1["result"]["items"][1][z][11].ToString() + "','" + json1["result"]["items"][0][z][0].ToString() + "', '" + ntime1 + "','" + DateTime.Now + "','" + List11[j1] + "')";
string str2 = GlobalFunction.updateDB(SearchStr);
}
}
}
Console.WriteLine(String.Format("插入数据成功，当前时间：{0}，相差天数 {1} 天！", DateTime.Now, sp.Days));
}
}
}
else
{
Console.WriteLine(String.Format("未到数据更新时间----当前时间{0}",DateTime.Now));
}
}
}
}
五、存入数据库中的数据
2498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
