/// <summary>
/// 根据sql查询得到数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable QueryData(string sql)
{
SqlConnection conn = new SqlConnection(CONNSTR);
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{sql}\n{Params2String(cmd.Parameters)}");
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
catch (Exception ex)
{
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
//收益排行榜
public successUserRank restsharp_userRank(string prov, string city, string company, string startTime, string endTime, string pageSize, string pageNo, string userId)
{
///// RestSharp post 请求 ///////
var client = new RestClient(BaseUrl);
var request = new RestRequest("miniapp/user/rank", Method.POST);
request.AddParameter("prov", prov);
request.AddParameter("city", city);
request.AddParameter("company", company);
request.AddParameter("startTime", startTime);
request.AddParameter("endTime", endTime);
request.AddParameter("pageSize", pageSize);
request.AddParameter("pageNo", pageNo);
request.AddParameter("userId", userId);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
MyLogHelper.Info("BaseUrl:" + BaseUrl);
IRestResponse response = client.Execute(request);
var content = response.Content;
// JObject json1 = (JObject)JsonConvert.DeserializeObject(content);
if (content.Equals(""))
{
return(null);
}
else
{
//把拿到的进行反序列化
try
{
if (content.Equals("{}"))
{
return(null);
}
else
{
successUserRank responseObj = JsonConvert.DeserializeObject <successUserRank>(content);
return(responseObj);
}
}
catch (Exception)
{
throw;
}
}
}
private string BaseUrl = System.Configuration.ConfigurationManager.AppSettings["Request_Url"]; //string BaseUrl = "http://58.220.234.147:8090/water/";
//查询运企
public List <operationEnAndLic> restsharp_operationEnterprise(string R_prov, string R_city)
{
///// RestSharp post 请求 ///////
var client = new RestClient(BaseUrl);
var request = new RestRequest("miniapp/company/query", Method.POST);
request.AddParameter("prov", R_prov);
request.AddParameter("city", R_city);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
MyLogHelper.Info("BaseUrl:" + BaseUrl);
IRestResponse response = client.Execute(request);
var content = response.Content;
// JObject json1 = (JObject)JsonConvert.DeserializeObject(content);
if (content.Equals(""))
{
return(null);
}
else
{
//把拿到的进行反序列化
try
{
List <codeAndName> responseObj = JsonConvert.DeserializeObject <List <codeAndName> >(content);
//格式转换
List <operationEnAndLic> operationObj = new List <operationEnAndLic>();
for (int i = 0; i < responseObj.Count; i++)
{
operationEnAndLic s = new operationEnAndLic();
s.operationEnterpriseCode = responseObj[i].code;
s.operationEnterprise = responseObj[i].name;
operationObj.Add(s);
}
return(operationObj);
}
catch (Exception)
{
throw;
}
}
}
//查询车队
public List <fleetAndCode> restsharp_fleet(string company)
{
///// RestSharp post 请求 ///////
var client = new RestClient(BaseUrl);
var request = new RestRequest("miniapp/busgroup/query", Method.POST);
request.AddParameter("company", company);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
MyLogHelper.Info("BaseUrl:" + BaseUrl);
IRestResponse response = client.Execute(request);
var content = response.Content;
// JObject json1 = (JObject)JsonConvert.DeserializeObject(content);
if (content.Equals(""))
{
return(null);
}
else
{
//把拿到的进行反序列化
try
{
List <codeAndName> responseObj = JsonConvert.DeserializeObject <List <codeAndName> >(content);
//格式转换
List <fleetAndCode> returnObj = new List <fleetAndCode>();
for (int i = 0; i < responseObj.Count; i++)
{
fleetAndCode s = new fleetAndCode();
s.fleetcode = responseObj[i].code;
s.fleet = responseObj[i].name;
returnObj.Add(s);
}
return(returnObj);
}
catch (Exception)
{
throw;
}
}
}
//查询每日收益
public List <successUserWalletDetail> restsharp_userWalletDetail(string userId, string startTime, string endTime)
{
///// RestSharp post 请求 ///////
var client = new RestClient(BaseUrl);
var request = new RestRequest("miniapp/user/wallet/detail", Method.POST);
request.AddParameter("userId", userId);
request.AddParameter("startTime", startTime);
request.AddParameter("endTime", endTime);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
MyLogHelper.Info("BaseUrl:" + BaseUrl);
IRestResponse response = client.Execute(request);
var content = response.Content;
// JObject json1 = (JObject)JsonConvert.DeserializeObject(content);
if (content.Equals(""))
{
return(null);
}
else
{
//把拿到的进行反序列化
try
{
if (content.Equals("{}"))
{
return(null);
}
else
{
List <successUserWalletDetail> responseObj = JsonConvert.DeserializeObject <List <successUserWalletDetail> >(content);
return(responseObj);
}
}
catch (Exception)
{
throw;
}
}
}
//用户提现
public string restsharp_userWithDraw(string userId, string money)
{
///// RestSharp post 请求 ///////
var client = new RestClient(BaseUrl);
var request = new RestRequest("driverpl/user/withdraw", Method.POST);
request.AddParameter("userId", userId);
request.AddParameter("money", money);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
MyLogHelper.Info("BaseUrl:" + BaseUrl);
IRestResponse response = client.Execute(request);
var content = response.Content;
// JObject json1 = (JObject)JsonConvert.DeserializeObject(content);
if (content.Equals(""))
{
return("没有数据");
}
else
{
//把拿到的进行反序列化
try
{
successObj responseObj = JsonConvert.DeserializeObject <successObj>(content);
if (responseObj.success.Equals("false"))
{
failedErrObj failedObj = JsonConvert.DeserializeObject <failedErrObj>(content);
return(failedObj.err);
}
else
{
return("ok");
}
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// 执行DML（增删改）的SQL语句，返回受影响的行数
/// </summary>
/// <param name="sql">带有占位符的SQL</param>
/// <param name="sqlParams">参数化查询的数组</param>
/// <returns></returns>
public static int ExecuteMyDML(string sql, SqlParameter[] sqlParams)
{
SqlConnection conn = new SqlConnection(CONNSTR);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (null != sqlParams && sqlParams.Length > 0)
{
CheckNull2DbNull(sqlParams);
cmd.Parameters.AddRange(sqlParams);
}
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{sql}\n{Params2String(cmd.Parameters)}");
int res = cmd.ExecuteNonQuery();
return(res);
}
catch (Exception ex)
{
MyLogHelper.Error(ex);
return(-1);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
/// <summary>
/// 获取第一行第一列的值
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static object ExecuteFirstValue(string sql, SqlParameter[] sqlParams)
{
SqlConnection conn = new SqlConnection(CONNSTR);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (null != sqlParams && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{sql}\n{Params2String(cmd.Parameters)}");
return(cmd.ExecuteScalar());
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message);
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
/// <summary>
/// 返回单个实体，第一行数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="cmdText"></param>
/// <param name="sqlParams"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public static T ExecuteReaderFirst <T>(string cmdText, SqlParameter[] sqlParams,
CommandType cmdType = CommandType.Text) where T : class, new()
{
SqlConnection conn = new SqlConnection(CONNSTR);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{cmdText}\n{Params2String(cmd.Parameters)}");
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
// 创建指定类型的实例
// 等同于 T entity = new T();
T entity = Activator.CreateInstance <T>();
//遍历reader字段
for (int i = 0; i < reader.FieldCount; i++)
{
//判断字段值是否为空或不存在
if (!IsNullOrDbNull(reader[i]))
{
//根据reader序列返回对应名称，并反射找到匹配的属性
PropertyInfo pi = typeof(T).GetProperty(reader.GetName(i),
BindingFlags.GetProperty | BindingFlags.Public
| BindingFlags.Instance | BindingFlags.IgnoreCase);
if (pi != null)
{
//设置对象中匹配属性的值
pi.SetValue(entity, CheckType(reader[i], pi.PropertyType), null);
}
}
}
return(entity);
}
return(null);
}
}
catch (Exception ex)
{
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
/// 查询，返回泛型集合，需要注意约束类型的属性名称需要和数据库表字段名称保持一致，但不区分大小写
/// </summary>
/// <typeparam name="T">约束类型</typeparam>
/// <param name="cmdText">SQL语句</param>
/// <param name="sqlParams">SQL参数</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回约束的类型集合</returns>
public static List <T> ExecuteReader <T>(string cmdText, SqlParameter[] sqlParams,
CommandType cmdType = CommandType.Text) where T : new()
{
//connStr 为web.config中数据库连接字符串
SqlConnection conn = new SqlConnection(CONNSTR);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{cmdText}\n{Params2String(cmd.Parameters)}");
using (SqlDataReader reader = cmd.ExecuteReader())
{
List <T> lstRes = new List <T>();
//获取指定的数据类型
Type modelType = typeof(T);
//遍历reader
while (reader.Read())
{
//创建指定类型的实例
T entity = Activator.CreateInstance <T>();
//遍历reader字段
for (int i = 0; i < reader.FieldCount; i++)
{
//判断字段值是否为空或不存在
if (!IsNullOrDbNull(reader[i]))
{
//根据reader序列返回对应名称，并反射找到匹配的属性
PropertyInfo pi = typeof(T).GetProperty(reader.GetName(i),
BindingFlags.GetProperty | BindingFlags.Public
| BindingFlags.Instance | BindingFlags.IgnoreCase);
if (pi != null)
{
//设置对象中匹配属性的值
pi.SetValue(entity, CheckType(reader[i], pi.PropertyType), null);
}
}
}
lstRes.Add(entity);
}
return(lstRes);
}
}
catch (Exception ex)
{
// 应该在这里记录崩溃，保存异常信息
//MyLogHelper.Write("发生异常", ex.Message);
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}