纲要(以某普查子系统的接口为例)
- 基本使用工具和需求目的（CRUD）
- 传参方式（URL，HEADERS）
- 整体流程
1. 基本使用工具和需求目的
- 平台：Visual Studio 2015
- 语言：C#
- 组件：Elinq ORM 数据访问组件,Elinq 官网
- 数据库：ORACLE,MYSQL
- 目的：CRUD（在后台接口中实现对数据库的增删改查功能）
- 成果：实现实际需求的不同接口，归纳起来还是对数据的增删改查。
- 返回前端的结果格式：目前用的是JSON格式
2. 传参方式
- GET方法：通过URL传入参数(安全性低，不适用于传递敏感信息，如：银行卡号及付款密码)
http://localhost:29800/ZXPCZXT.asmx/SetValid?cid=1&isvalid=0
-
HEADERS方法：把敏感信息通过请求头进行传递，避免被非法截取。参数传递测试是通过HTTP模拟请求器RESTClient进行的
image.png
3. 总体流程
3.1. 基础配置
- 1.添加网站，新建一个web服务。
- 2.添加引用:System.Data.OracleClient;通过Nuget安装ELinq包和Newtonsoft.Json包；
- 3.配置数据库连接和远程访问权限信息。在Web.config文件中添加连接和远程访问字符串：
<connectionStrings>
<clear />
<add providerName="System.Data.OracleClient" name="Northwind" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=#########)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl))); User Id=#####;Password=####;" />
</connectionStrings>
//在<system.web></system.web>中添加远程访问协议
<webServices>
<protocols>
<add name="HttpSoap" />
<add name="HttpPost" />
<add name="HttpGet" />
<add name="Documentation" />
</protocols>
</webServices>
- 添加BaseService类，封装了写好的方法（解析HTTP GET/POST方法传入的参数，并转化成指定类型）
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections.Specialized;
using System.Net;
using System.Text;
using System.IO;
using System.Reflection;
/// <summary>
/// BaseService 的摘要说明
/// </summary>
public class BaseService : System.Web.Services.WebService
{
/// <summary>
/// 解析HTTP GET/POST方法传入的参数，并转化成指定类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="key"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public T ParseField<T>(string key, T defaultValue)
{
try
{
NameValueCollection paras = this.Context.Request.Params;
string value = paras[key];
if (null == value)
{
return defaultValue;
}
return (T)Convert.ChangeType(value, typeof(T));
}
catch
{
return defaultValue;
}
}
public static object ChangeType(object value, Type conversion)
{
var t = conversion;
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
if (value == null)
{
return null;
}
t = Nullable.GetUnderlyingType(t);
}
return Convert.ChangeType(value, t);
}
}
- 添加操作状态的类HeadData类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
/// <summary>
/// HeadData 的摘要说明
/// 返回操作的状态
/// </summary>
public class HeadData
{
/// <summary>
/// 一个返回值，0表示成功
/// </summary>
public int Code { get; set; }
/// <summary>
/// 错误的信息
/// </summary>
private string mMsg = "";
/// <summary>
/// 错误信息
/// </summary>
public string Msg { get { return mMsg; } set { mMsg = value; } }
private DateTime mServerTime = DateTime.Now;
/// <summary>
/// 返回服务器时间
/// </summary>
public DateTime ServerTime { get { return mServerTime; } set { mServerTime = value; } }
/// <summary>
/// 返回结果的数量
/// </summary>
public int Count { get; set; }
/// <summary>
/// 业务逻辑处理结果
/// </summary>
public bool Result { get; set; }
}
public enum codetype
{
/// <summary>
/// 执行成功
/// </summary>
CODE_OK = 0,
/// <summary>
/// 执行失败
/// </summary>
CODE_ERROR = 1
}
- 添加 JSON格式转换的类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using Newtonsoft.Json.Converters;
/// <summary>
/// ReturnType 的摘要说明
/// </summary>
public class ReturnType
{
/// <summary>
/// 返回JSON格式的字符串
/// </summary>
/// <param name="Resp"></param>
/// <param name="head"></param>
/// <param name="data"></param>
public static void Json(HttpResponse Resp, HeadData head, object data)
{
if (string.IsNullOrEmpty(Resp.ContentType))
{
Resp.ContentType = "text/json";
}
object objData = new
{
Code = head.Code,
Errmsg = head.Msg,
ServerTime = head.ServerTime,
Result = head.Result,
Count = head.Count,
Data = data
};
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
IList<JsonConverter> converts = new List<JsonConverter>();
converts.Add(timeFormat);
Resp.Write(JsonConvert.SerializeObject(objData, Newtonsoft.Json.Formatting.None,
new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
ContractResolver = new CamelCasePropertyNamesContractResolver(),
Converters = converts
}));
}
}
- 添加封装好的结果输出类-ResultOutput
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
/// <summary>
/// ResultOutput 的摘要说明
/// 以JSON格式输出结果集
/// </summary>
public class ResultOutput : System.Web.Services.WebService
{
/// <summary>
/// 以JSON格式输出结果集
/// </summary>
/// <param name="codetype"></param>
/// <param name="result"></param>
/// <param name="count"></param>
/// <param name="Msg"></param>
/// <param name="data"></param>
public void Output(int codetype,bool result,int count,string Msg,object data)
{
HeadData hd = new HeadData();
HttpResponse Response = Context.Response;
hd.Code = codetype;
hd.Result = result;
hd.Count = count;
hd.Msg = Msg;
ReturnType.Json(Response, hd,data);
}
}
3.2. Elinq ORM 配置
- 定义关系映射
using NLite.Data;
/// <summary>
/// PCTREEMODEL 的摘要说明
/// 普查内容
/// </summary>
[Table("PC_TREE")]
public class PCTREEMODEL
{
/// <summary>
/// 主键，自增长
/// </summary>
[Id(SequenceName = "seq_pc_tree", IsDbGenerated =true)]
public int CID { get; set; }
/// <summary>
/// 父ID
/// </summary>
public int PID { get; set; }
/// <summary>
/// 名称
/// </summary>
public string CNAME { get; set; }
/// <summary>
/// 排序
/// </summary>
public int CSORT { get; set; }
/// <summary>
/// 是否有效
/// </summary>
public int ISVALID { get; set; }
/// <summary>
/// 备注
/// </summary>
public string REMARK { get; set; }
}
- 创建DbContext
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NLite;
using NLite.Data;
/// <summary>
/// SsisDbContext 的摘要说明
/// 连接到数据库，并能同步对表的增删改查操作。
/// </summary>
public class SsisDbContext : DbContext
{
const string CONNECTIONNAME = "Northwind";
//连接数据库，设置sql语句输出日志，注册实体到数据表的映射关系
static NLite.Data.DbConfiguration dbConfig = NLite.Data.DbConfiguration.Configure(CONNECTIONNAME)
.SetSqlLogger(() => new SqlLog(Console.Out))
.AddClass<PCTREEMODEL>()
.AddClass<PCINFOMODEL>(m =>
{
m.OneToOne<PCTREEMODEL>(e => e.PCTREE).ThisKey(e => e.cid).OtherKey(e => e.CID);
m.OneToOne<PCFWMODEL>(e => e.PCFW).ThisKey(e => e.fwid).OtherKey(e => e.fwid);
})
.AddClass<PCFWMODEL>();
public SsisDbContext() : base(dbConfig)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
//表示用于执行插入、读取、更新和删除操作的类型化实体集，对实体集中所有的增删改查的操作会立即同步到数据库对应的表中
public readonly IDbSet<PCTREEMODEL> PCTREE;
public readonly IDbSet<PCINFOMODEL> PCINFO;
public readonly IDbSet<PCFWMODEL> PCFW;
}
值得注意的是，在注册实体到数据表的映射关系时，要先理清表与表之间的关系，是一对一，还是一对多，或者多对一，Elinq不支持多对多，需要转换成两个一对多映射。然后通过ThisKey 和 ThatKey建立关联。
映射关系理解示例：
一对一：第一种是一对一主键关联，要求两个主键必须完全一致；第二种是一对一外键关联（未懂）。例子，一个球队对应一个地址。
一对多：例如，从球队角度来说，一个球队拥有多个球员
多对一：例如，从球员角度来说，多个球员属于一个球队
多对多：如学生与选修课之间的关系，一个学生可以选择多门选修课，每门选修课又可以被多名学生选择。一般是采用中间表的方式处理，转化为两个一对多。
3.3. 增删改查
OAuth2.0（token 认证）
此技术的意义在于帮助用户将其资源授权给第三方应用，给予可控的有限的权限，并且不会泄露用户的密码或其它认证凭据。通过加入安全认证的环节，向实际生活中的第三方应用登录授权看齐。在本公司中，应用的是简化版的token认证，并非授权码模式的token认证。常见问题
1.区分非数据库操作和数据库操作，例如，对入参是否为空的验证是非数据库型的操作，切记不能放到连接数据库再验证，避免人为增加计算性能的损耗。
2.务必结合实际应用场景编写接口的功能，避免写出的接口功能完全不切合生活应用，脱离实际。在不清楚实际应用场景时，浏览对应的生活场景中的网站，通过F12查看别人的需求细节设计。
3.尽量精简自己的代码，能一句代码实现功能，就不要使用两句三句甚至更多的代码，避免冗余和计算性能浪费。
- 查询
1.单表查询。
查询所有结果集；
查询限定结果集（涉及使用where-附加条件、orderby-排序、skip和take-分页、group和foreach-分组遍历等）。
2.多表查询。
【特别注意，多张表之间的关系】
在定义映射关系时，主表可通过IList<###> ##获取其它表的数据
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NLite.Data;
namespace Example.Entities
{
[Table(Name ="customers")]
public class Customer
{
/// <summary>
///用户id
/// </summary>
[Id]
public string id { get; set; }
/// <summary>
/// 用户名字
/// </summary>
public string CUSTOMERNAME { get; set; }
/// <summary>
/// 用户信息
/// </summary>
public string CUSTOMERINFORMATION { get; set; }
public IList<order> orders { get; set; }
}
}
更新（update）
1.需要做必填项验证,确定哪些是必填项，哪些是选填项。（在连接数据库前）插入(insert)
1.需要做必填项验证（在连接数据库前）
//判断传参是否完整
if (string.IsNullOrEmpty(cids) || string.IsNullOrEmpty(csorts))
{
resout.Output((int)codetype.CODE_ERROR,false,0,"参数不足",null);
Response.End();
}
2.后台生成全局唯一的ID，而不是前端传入，这仅仅是后台的工作。
customer.id = Guid.NewGuid().ToString();
删除（delete）
某业务代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
/// <summary>
/// ZXPCZXT 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。
// [System.Web.Script.Services.ScriptService]
public class ZXPCZXT : BaseService
{
[WebMethod(Description = "插入或更新")]
public void InsertOrUpdate()
{
HeadData hd = new HeadData();
HttpResponse Response = Context.Response; //响应体
ResultOutput resout = new ResultOutput();
try
{
//解析HTTP GET方法传入的参数，并转换为指定类型
string cname = ParseField("cname", string.Empty);
string remark = ParseField("remark", string.Empty);
int cid = ParseField("cid", -1);
int pid = ParseField("pid", -1);
int isvalid = ParseField("isvalid", -1);
if ( cid == -1) //判断必要的主键是否传入
{
resout.Output((int)codetype.CODE_ERROR,false,0, "参数不足",null);
Response.End();
}
using (SsisDbContext db = new SsisDbContext()) //连接数据库
{
int count = db.PCTREE.Where(c => c.CID == cid).Count();
if (count > 0) //若count 大于0，进行更新操作，反之，进行插入操作
{
int num = db.PCTREE.Update(new { cname = cname,remark = remark} ,(p => p.CID == cid)); //更新
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "更新成功", null);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "更新失败", null);
}
}
else //进行插入操作
{
var list = db.PCTREE.OrderByDescending(c => c.CSORT).FirstOrDefault(); //查询CSORT值最大的对应记录
int MaxCsort = list.CSORT;
var p = new PCTREEMODEL
{
PID = pid,
CNAME = cname,
CSORT = MaxCsort + 1 ,
ISVALID = isvalid,
REMARK = remark
};
int num = db.PCTREE.Insert(p); //插入新记录
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "插入成功", null);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "插入失败", null);
}
}
}
}
catch (System.Threading.ThreadAbortException) { } //捕捉中止线程的异常
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "设置禁用或启用")]
public void SetValid()
{
HeadData hd = new HeadData();
HttpResponse Response = Context.Response; //响应体
ResultOutput resout = new ResultOutput();
int cid = ParseField("cid", -1); //解析http get方法传入的参数，并转化为指定类型
int isvalid = ParseField("isvalid", -1);
if (cid == -1 || isvalid == -1) //判断传参是否完整
{
resout.Output((int)codetype.CODE_ERROR, false, 0, "参数不足", null);
Response.End();
}
if (isvalid != 1 && isvalid != 0)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, "禁用或启用失败，请重新传入isvalid的值，其中默认1为启用，0为禁用", null);
Response.End();
}
try
{
using(SsisDbContext db = new SsisDbContext()) //连接数据库
{
int count = db.PCTREE.Where(p => p.CID == cid).Count(); //通过主键查询记录数量
if (count >0)
{
int sum = db.PCTREE.Update(new { isvalid = isvalid},(p => p.CID == cid || p.PID == cid)); //更新对应isvalid的值
hd.Count = sum;
if (isvalid == 1 && sum > 0) //通过判断isvalid的值，判别是启用还是禁用，默认1为启用，0为禁用
{
resout.Output((int)codetype.CODE_OK, true,sum, "启用成功", null);
}
else if(isvalid == 0 && sum > 0 )
{
resout.Output((int)codetype.CODE_OK, true, sum, "禁用成功", null);
}
else if(isvalid == 1 && sum <= 0)
{
resout.Output((int)codetype.CODE_ERROR, false, sum, "启用失败", null);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, sum, "禁用失败", null);
}
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, count, "用户不存在", null);
}
}
}
catch (System.Threading.ThreadAbortException) { } //捕捉中止线程的异常
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "保存排序结果")]
public void SaveSort()
{
HeadData hd = new HeadData();
HttpResponse Response = Context.Response; //响应体
ResultOutput resout = new ResultOutput();
string cids = ParseField("cids", string.Empty); //解析HTTP GET方法传入的参数，并转化为指定类型
string csorts = ParseField("csorts", string.Empty);
if (string.IsNullOrEmpty(cids) || string.IsNullOrEmpty(csorts)) //判断传参是否完整
{
resout.Output((int)codetype.CODE_ERROR,false,0,"参数不足",null);
Response.End();
}
string[] cidArr = cids.Split(','); //分割传入的字符串为字符串数组
string[] csortArr = csorts.Split(',');
try
{
using(SsisDbContext db = new SsisDbContext()) //连接数据库
{
int num = 0;
//循环更新对应排序值
for (int i = 0; i < cidArr.Count(); i++)
{
num += db.PCTREE.Update(new { csort = Convert.ToInt32(csortArr[i]) }, (p => p.CID == Convert.ToInt32(cidArr[i])));
}
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "保存排序成功", null);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "保存排序失败", null);
}
}
}
catch (System.Threading.ThreadAbortException) { } //捕捉中止线程的异常
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "###########")]
public void Query()
{
HeadData hd = new HeadData();
HttpResponse Response = Context.Response; //响应体
ResultOutput resout = new ResultOutput();
string s_time = ParseField("s_time", string.Empty); //获取http get/post方法传入的参数，并转化为指定类型
string e_time = ParseField("e_time", string.Empty);
string fwids = ParseField("fwids", string.Empty);
string cids = ParseField("cids", string.Empty);
int pageindex = ParseField("pageindex", -1) < 0 ? 1 : ParseField("pageindex", -1); //判断页面索引和每页数量
int pagesize = ParseField("pagesize", -1) < 0 ? 10 : ParseField("pagesize", -1);
if (!string.IsNullOrEmpty(s_time) && !string.IsNullOrEmpty(e_time)) //在非空的情况下判断终止时间是否大于起始时间
{
DateTime startdate = (Convert.ToDateTime(s_time)).Date;
DateTime enddate = (Convert.ToDateTime(e_time)).Date;
if (enddate < startdate)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, "查询失败，结束时间需要大于起始时间", null);
Response.End();
}
}
try
{
using(SsisDbContext db = new SsisDbContext())
{
//通过普查时间段，普查范围，问题，进行筛选查询
IQueryable<PCINFOMODEL> list = db.PCINFO.Include(e => e.PCTREE).Include(e => e.PCFW); // 提供对数据类型已知的特定数据源的查询进行计算的功能
//起始时间
if (!string.IsNullOrEmpty(s_time))
{
DateTime startdate = (Convert.ToDateTime(s_time)).Date;
list = list.Where(c => c.pcsj >= startdate);
}
//终止时间
if (!string.IsNullOrEmpty(e_time))
{
DateTime enddate = (Convert.ToDateTime(e_time)).Date;
list = list.Where(c => c.pcsj <= enddate);
}
//范围名称
if (!string.IsNullOrEmpty(fwids))
{
//string[] fwmcArr = fwmc.Split('，');
//for (int i = 0; i < fwmcArr.Count() - 1; i++)
//{
// list = list.Where(c => c.PCFW.fwmc == fwmcArr[i]);
// list = list.Union(list.Where(c => c.PCFW.fwmc == fwmcArr[i + 1]));
// //var resultlist = list.Concat(list.Where(c => c.PCFW.fwmc == fwmcArr[i + 1]));
// // resultlist = resultlist.Concat(list);
//}
//list = list.Where(c => fwmc.Contains(c.PCFW.fwmc));
list = list.Where(c => fwids.Split(',').Contains(c.PCFW.fwid.ToString()));
}
//普查问题名称
if (!string.IsNullOrEmpty(cids))
{
list = list.Where(c => cids.Split(',').Contains(c.PCTREE.CID.ToString()));
}
var vlist = list.Select(c => new PCDetail()
{
pcid = c.pcid,
pcsj = c.pcsj,
pcfw = c.PCFW.fwmc,
cname = c.PCTREE.CNAME
}).OrderBy(c => c.pcsj).Skip(pageindex - 1).Take(pagesize).ToList(); //把查询结果序列的每个元素投影到新表中，并排序，去除指定索引前的记录，确定每页大小，最后转换为列表形式
//int totalcount = list.Count(); ?? 报错，值过多。 可能原因： 由于是多表查询，必须先转换为list列表，然后才能计算记录个数。
int num = list.ToList().Count(); //返回结果总数。
if (num > 0)
{
resout.Output((int)codetype.CODE_OK,true, num, "查询成功",vlist);
}
else
{
resout.Output((int)codetype.CODE_ERROR,false, num, "满足条件的记录不存在",null);
}
}
}
catch (System.Threading.ThreadAbortException) { } //捕捉中止线程的异常
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "通过关键词定位")]
public void KeyQuery()
{
HeadData hd = new HeadData();
ResultOutput resout = new ResultOutput();
string keyword = ParseField("keyword",string.Empty); //解析HTTP GET方法传入的参数
try
{
using(SsisDbContext db = new SsisDbContext()) //连接数据库
{
IQueryable<PCTREEMODEL> list = db.PCTREE;
//根据关键词查询
if (!string.IsNullOrEmpty(keyword))
{
list = list.Where(c => c.CNAME.Contains(keyword));
}
var vlist = list.Select(c => new PCTREEOUT
{
CID = c.CID,
PID = c.PID,
CNAME = c.CNAME,
CSORT = c.CSORT,
ISVALID = c.ISVALID,
REMARK = c.REMARK
}).OrderBy(c =>c.CSORT).ToList(); //把查询的结果序列的每个元素投影到新表中，并排序，最后转换为列表
int num = list.Count(); //单表
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "查询成功", vlist);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "未搜索到含关键词的记录", null);
}
}
}
catch (System.Threading.ThreadAbortException) { } //捕捉中止线程异常
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "获取######结果集")]
public void Get_PCTREE()
{
HeadData hd = new HeadData();
//HttpResponse Response = Context.Response;
ResultOutput resout = new ResultOutput();
try
{
using (SsisDbContext db = new SsisDbContext())
{
var list = db.PCTREE.Select(c => new PCTREEOUT
{
CID = c.CID,
PID = c.PID,
CNAME = c.CNAME,
CSORT = c.CSORT,
ISVALID = c.ISVALID,
REMARK = c.REMARK
}).OrderBy(c => c.CSORT).ToList();
int num = list.Count();
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "查询成功", list);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "查询失败", null);
}
}
}
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
}
附相关数据查询的业务代码(加了TOKEN认证，分组遍历查询)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using MySql.Data.MySqlClient;
using NLite;
using NLite.Data;
using System.Text;
using System.Net;
using System.Collections.Specialized;
/// <summary>
/// QueryService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。
// [System.Web.Script.Services.ScriptService]
public class QueryService : BaseService
{
[WebMethod(Description = "用户登录")]
public void UserLogin()
{
string usercode = ParseField("account", string.Empty); //解析http get/post 方法传入的参数，并转换为指定类型
string password = ParseField("password", string.Empty);
HeadData hd = new HeadData();
HttpResponse Response = Context.Response;
ResultOutput resout = new ResultOutput();
if (string.IsNullOrEmpty(usercode) || string.IsNullOrEmpty(password)) //判断传入参数是否为空
{
resout.Output_detailtime((int)codetype.CODE_ERROR, false, 0, "登录账号或者密码不能为空！", null);
Response.End();
}
try
{
using(SsisDbContext db = new SsisDbContext())
{
var useraccount = db.account.FirstOrDefault(m => m.usercode == usercode);
if (useraccount == null) //判断账号是否存在
{
resout.Output_detailtime((int)codetype.CODE_ERROR, false, 0, "登录的账号不存在", null);
Response.End();
}
if (string.Compare(password,useraccount.password,true) != 0) //判断密码是否正确
{
resout.Output_detailtime((int)codetype.CODE_ERROR, false, 0, "登录的密码不正确", null);
Response.End();
}
var systoken = new accounttoken_model(); //定义一条账户令牌的新记录
systoken.userid = useraccount.userid;
systoken.login = DateTime.Now;
systoken.token = ###########; //加密令牌
var usertoken = db.account_token.SingleOrDefault(s => s.token == systoken.token); //判断令牌是否已存在，若存在，更新登录时间，若不存在，插入新令牌
int num = 0;
if (usertoken != null)
{
usertoken.login = DateTime.Now;
num = db.account_token.Update(usertoken);
}
else
{
num = db.account_token.Insert(systoken);
}
if (num > 0)
{
resout.Output_detailtime((int)codetype.CODE_OK, true, num, "登录成功", systoken.token);
}
else
{
resout.Output_detailtime((int)codetype.CODE_ERROR, false, 0, "登录失败", null);
}
}
}
catch (System.Threading.ThreadAbortException) { }
catch (Exception ex)
{
resout.Output_detailtime((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "##############")]
public void get_kqzl_aqi_hour()
{
//timeformat timeformat = new timeformat();
HeadData hd = new HeadData();
HttpResponse Response = Context.Response;
ResultOutput resout = new ResultOutput();
NameValueCollection paras = this.Context.Request.Headers;
string newtoken = paras["token"];
//string newtoken = ParseField("token", string.Empty); //token
var validtoken = CommonFunction.GetValidToken(newtoken);
if (!validtoken.RetResult)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, "查询失败，请传入有效令牌", null);
Response.End();
}
try
{
using (SsisDbContext db = new SsisDbContext()) //连接数据库
{
//List<kqzl_aqi_hour_out> vlist = db.kqzl_aqi_hour.Select(c => new kqzl_aqi_hour_out
//{
// typename = c.typename
//}).Distinct().ToList(); //获取污染物名称
//List<kqzl_aqi_hour_data_out> datalist = db.kqzl_aqi_hour.Select(c => new kqzl_aqi_hour_data_out
//{
// typename = c.typename,
// day = c.day,
// hour = c.hour,
// amount = c.amount,
// color = c.color
//}).OrderBy(c => c.hour).ToList(); //获取指定数据
//vlist.ForEach(a => a.data = datalist.Where(e => e.typename == a.typename).ToList()); //遍历对应污染物的数据指标
List<kqzl_aqi_hour_out> vlist = new List<kqzl_aqi_hour_out>();
db.kqzl_aqi_hour.GroupBy(c => c.typename).ToList().ForEach(x => vlist.Add(new kqzl_aqi_hour_out
{
typename = x.Key,
data = x.Select(y => new kqzl_aqi_hour_data_out
{
day = y.day,
hour = y.hour,
amount = y.amount,
color = y.color
}).OrderBy(y => y.hour).ToList()
}));
int num = vlist.Count();
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "查询成功", vlist);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "满足条件的记录不存在", null);
}
}
}
catch (System.Threading.ThreadAbortException) { }
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
[WebMethod(Description = "####################")]
public void Get_ylzy_ylwsjg()
{
HeadData hd = new HeadData();
ResultOutput resout = new ResultOutput();
HttpResponse Response = Context.Response;
NameValueCollection paras = this.Context.Request.Headers;
string newtoken = paras["token"];
//string newtoken = ParseField("token", string.Empty); //token
var validtoken = CommonFunction.GetValidToken(newtoken);
if (!validtoken.RetResult)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, "查询失败，请传入有效令牌", null);
Response.End();
}
string areaid = ParseField("areaid", string.Empty); //传参-区域id
try
{
using(SsisDbContext db = new SsisDbContext()) //连接数据库
{
IQueryable<ylzy_ylwsjg_model> list = db.ylzy_ylwsjg;
if (!string.IsNullOrEmpty(areaid)) //通过区域id筛选查询
{
list = list.Where(c => c.areaid == areaid);
}
var vlist = list.Select(c => new ylzy_ylwsjg_out
{
areaid = c.areaid,
areaname = c.areaname,
typeid = c.typeid,
typename = c.typename,
o_amount = c.o_amount,
b_amount = c.b_amount
}).ToList(); //查询结果转换为列表
//List<ylzy_ylwsjg_out> vlist = db.ylzy_ylwsjg.Select(c => new ylzy_ylwsjg_out
//{
// areaid = c.areaid,
// areaname = c.areaname
//}).Distinct().OrderBy(c => c.areaid).ToList(); //获取区域编码和名称
//List<ylzy_ylwsjg_jg_out> jglist = db.ylzy_ylwsjg.Select(c => new ylzy_ylwsjg_jg_out
//{
// areaid = c.areaid,
// typeid = c.typeid,
// typename = c.typename,
// o_amount = c.o_amount,
// b_amount = c.b_amount
//}).ToList(); //获取区域编码和机构详情
//vlist.ForEach(a => a.data = jglist.Where(e => e.areaid == a.areaid).ToList()); //遍历区域编码对应的机构详情
//List<ylzy_ylwsjg_out> vlist = new List<ylzy_ylwsjg_out>();
//db.ylzy_ylwsjg.GroupBy(c => c.areaid).ToList().ForEach(x => vlist.Add(new ylzy_ylwsjg_out
//{
// areaid = x.Key,
// data = x.Select(y => new ylzy_ylwsjg_jg_out
// {
// areaname = y.areaname,
// typeid = y.typeid,
// typename = y.typename,
// o_amount = y.o_amount,
// b_amount = y.b_amount
// }).ToList()
//}));
int num = vlist.Count();
if (num > 0)
{
resout.Output((int)codetype.CODE_OK, true, num, "查询成功", vlist);
}
else
{
resout.Output((int)codetype.CODE_ERROR, false, num, "满足条件的记录不存在", null);
}
}
}
catch (Exception ex)
{
resout.Output((int)codetype.CODE_ERROR, false, 0, ex.ToString(), null);
}
}
}
