本文采用总-分的形式讲述流程
1.前端外部可以使用的接口
ExternalDataWebService.asmx?op=ReportWaterForWayder
新建ExternalDataWebService.asmx 文件
<%@ WebService Language="C#" CodeBehind="~/App_Code/ExternalDataWebService.cs" Class="ExternalDataWebService" %>新建ExternalDataWebService.cs文件，用于外部使用的接口方法
public void ReportWaterForWayder(string userid, string token, string begindate, string enddate)
{
Context.Response.ContentType = "application/json";
Context.Response.ContentEncoding = System.Text.Encoding.UTF8;
Newtonsoft.Json.JsonSerializerSettings jsetting = new Newtonsoft.Json.JsonSerializerSettings();
JsonConvert.DefaultSettings = new Func<JsonSerializerSettings>(() =>
{
//日期类型默认格式化处理
jsetting.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat;
jsetting.DateFormatString = "yyyy-MM-dd HH:mm:ss";
//空值处理
jsetting.NullValueHandling = NullValueHandling.Ignore;
return jsetting;
});
Resultobject<List<WayderWaterReport>> result = new Resultobject<List<WayderWaterReport>>();
try
{
result.Reponsecode = "0000";
result.Reponsedesc = "";
result.Resultmsg = new ExternalDataBizService().WayderWaterReportDingzhi(userid, token, begindate, enddate);
Context.Response.Write(JsonConvert.SerializeObject(result, Formatting.Indented, jsetting));
}
catch (AuthException ae)
{
result.Reponsecode = "2001";
result.Reponsedesc = ae.Message;
result.Resultmsg = null;
Context.Response.Write(JsonConvert.SerializeObject(result, Formatting.Indented, jsetting));
}
catch (BizException le)
{
result.Reponsecode = "1001";
result.Reponsedesc = le.Message;
result.Resultmsg = null;
Context.Response.Write(JsonConvert.SerializeObject(result, Formatting.Indented, jsetting));
}
catch (Exception e)
{
result.Reponsecode = "1002";
result.Reponsedesc = e.Message;
result.Resultmsg = null;
Context.Response.Write(JsonConvert.SerializeObject(result, Formatting.Indented, jsetting));
}
finally
{
Context.Response.End();
}
}2.文中使用到的 new ExternalDataBizService().WayderWaterReportDingzhi(userid, token, begindate, enddate)方法以及WayderWaterReport的数据类型需要分别在ExternalDataBizService.cs文件和WayderWaterReport.cs文件中声明
public List<WayderWaterReport> WayderWaterReportDingzhi(string userid, string token, string begindate,string enddate)
{
#region 日志信息
UploadLog uploadLog = new UploadLog();
uploadLog.Uuid = Guid.NewGuid().ToString();
uploadLog.Cixu = 1;
uploadLog.Commiter = userid;
uploadLog.Committime = DateTime.Now;
uploadLog.Methodname = "WayderWaterReportDingzhi";
uploadLog.JsonString = token.ToString() + "," + userid + "," + begindate + "," + enddate;
uploadLog.Desc = "伟达定制：水报表";
uploadLog.Isdeal = 0;
#endregion
#region 初始化json类
Newtonsoft.Json.JsonSerializerSettings jsetting = new Newtonsoft.Json.JsonSerializerSettings();
JsonConvert.DefaultSettings = new Func<JsonSerializerSettings>(() =>
{
//日期类型默认格式化处理
jsetting.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat;
jsetting.DateFormatString = "yyyy-MM-dd HH:mm:ss";
//空值处理
jsetting.NullValueHandling = NullValueHandling.Ignore;
return jsetting;
});
#endregion
try
{
#region 判断token有效性
try
{
Api_Auth api_Auth = new Api_Auth(true, DbUtility.getDbconn());
Api_Auth.AuthInfo authInfo = api_Auth.CheckAuth(userid, token);
if (authInfo.AccessRes == false)
{
throw new AuthException(authInfo.ResMsg);
}
userid = authInfo.Username;
}
catch (Exception e)
{
throw new AuthException(e.Message);
}
#endregion
//数据库连接
MySqlConnection conn = new MySqlConnection(DbUtility.getDbconn());
MySqlTransaction trans = null;
try
{
conn.Open();
if (String.IsNullOrEmpty(begindate))
{
throw new BizException("begindate不能为空，请检查");
}
if (String.IsNullOrEmpty(enddate))
{
throw new BizException("enddate不能为空，请检查");
}
//获得用户信息
Xt_user user = new Xt_userDataService().Get(conn, trans, userid);
if (user == null) throw new BizException("用户不存在，请检查");
Xt_dept dept = new Xt_deptDataService().Get(conn, trans, user.Dept);
if (dept == null) throw new BizException("部门不存在，请检查");
String cuscode = dept.Abbr;
if (String.IsNullOrEmpty(cuscode)) throw new BizException("客户代码未设置，请检查");
//List<Bb_api_authorization> authorization = new Bb_api_authorizationDataService().GetValid(conn, trans, cuscode);
//if ((authorization == null) || (authorization.Count == 0)) throw new BizException("客户API访问未授权，请检查");
Report_dailywaterDataService report_DailywaterDataService = new Report_dailywaterDataService(cuscode);
Wayder_watersetDataService wayder_WatersetDataService = new Wayder_watersetDataService();
List<WayderWaterReport> result = new List<WayderWaterReport>();
DateTime begintime = Convert.ToDateTime(begindate);
DateTime endtime = Convert.ToDateTime(enddate);
//循环体按照日期处理
do
{
WayderWaterReport setdataItem = new WayderWaterReport();
setdataItem.date = begintime.Date.ToString("yyyy-MM-dd");
//水表1
Report_dailywater dailywater1= report_DailywaterDataService.GetStationDaily(conn,trans, "3007001", begintime.Date);
if (dailywater1 != null) setdataItem.dailywater3007001 = dailywater1; else setdataItem.dailywater3007001 = new Report_dailywater();
//水表2
Report_dailywater dailywater2 = report_DailywaterDataService.GetStationDaily(conn, trans, "30501", begintime.Date);
if (dailywater1 != null) setdataItem.dailywater30501 = dailywater2; else setdataItem.dailywater30501 = new Report_dailywater();
//水表3
Report_dailywater dailywater3 = report_DailywaterDataService.GetStationDaily(conn, trans, "3007002", begintime.Date);
if (dailywater1 != null) setdataItem.dailywater3007002 = dailywater3; else setdataItem.dailywater3007002 = new Report_dailywater();
//获取设置值
Wayder_waterset waterset =wayder_WatersetDataService.GetByDate(conn, trans, begintime.Date);
if (waterset != null)
{
setdataItem.wayder_Waterset = waterset;
}
else
{
setdataItem.wayder_Waterset = new Wayder_waterset();
setdataItem.wayder_Waterset.Wastewater_living = 0;
setdataItem.wayder_Waterset.Unpolluted_used = 0;
setdataItem.wayder_Waterset.Unpolluted_living = 0;
setdataItem.wayder_Waterset.Total_water = (decimal)141.3;
setdataItem.wayder_Waterset.Industrial_wastewater = 0;
}
result.Add(setdataItem);
begintime = begintime.AddDays(1);
} while (begintime <= endtime);
if (trans != null)
{
trans.Commit(); ;
}
return result;
}
catch (Exception e)
{
if (trans != null)
{
trans.Rollback();
}
uploadLog.Isdeal = 1;
uploadLog.Desc = "出错" + e.Message;
throw new BizException(e.Message);
}
finally
{
trans = null;
new UploadLogDataService().AddData(conn, trans, uploadLog);
conn.Close();
}
}
catch (AuthException ea)
{
throw ea;
}
catch (Exception e)
{
throw new BizException(e.Message);
}
}using ENMSLibrary.entity.enms;
using ENMSLibrary.entity.enms.wayder;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
/// <summary>
/// WayderWaterReport 的摘要说明
/// </summary>
public class WayderWaterReport
{
public WayderWaterReport()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
///
/// </summary>
public string date { get; set; }
public Report_dailywater dailywater3007001 { get; set; }
public Report_dailywater dailywater30501 { get; set; }
public Report_dailywater dailywater3007002 { get; set; }
public Wayder_waterset wayder_Waterset { get; set; }
}
public class SetdataItem
{
/// <summary>
///
/// </summary>
public string date { get; set; }
public Report_dailywater dailywater3007001 { get; set; }
public Report_dailywater dailywater30501 { get; set; }
public Report_dailywater dailywater3007002 { get; set; }
public Wayder_waterset wayder_Waterset { get; set; }
/// <summary>
///
/// </summary>
public int WASTEWATER_LIVING { get; set; }
/// <summary>
///
/// </summary>
public int UNPOLLUTED_USED { get; set; }
/// <summary>
///
/// </summary>
public int UNPOLLUTED_LIVING { get; set; }
/// <summary>
///
/// </summary>
public int TOTAL_WATER { get; set; }
/// <summary>
///
/// </summary>
public int INDUSTRIAL_WASTEWATER { get; set; }
}
public class WaterdataItem
{
/// <summary>
///
/// </summary>
public string date { get; set; }
/// <summary>
///
/// </summary>
public string deviceid { get; set; }
/// <summary>
/// 工业用水总表
/// </summary>
public string devicename { get; set; }
/// <summary>
///
/// </summary>
public string occurdate { get; set; }
/// <summary>
///
/// </summary>
public double watermin { get; set; }
/// <summary>
///
/// </summary>
public double watermax { get; set; }
/// <summary>
///
/// </summary>
public double waterdata { get; set; }
}我们在业务层面使用到的文件ExternalDataBizService.cs。处理业务逻辑，通过数据库的方式，找到对应的数据。其中还包含其他的数据格式Wayder_waterset.cs和Report_dailywater.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ENMSLibrary.entity.enms.wayder
{
[Serializable]
public class Wayder_waterset
{
/// <summary>
/// ID
/// </summary>
[DisplayName("ID")]
public string Id { get; set; }
/// <summary>
/// CREATTIME
/// </summary>
[DisplayName("CREATTIME")]
public DateTime Creattime { get; set; }
/// <summary>
/// 废水回收-用于生活
/// </summary>
[DisplayName("废水回收-用于生活")]
public decimal? Wastewater_living { get; set; }
/// <summary>
/// 未污染-用于生产
/// </summary>
[DisplayName("未污染-用于生产")]
public decimal? Unpolluted_used { get; set; }
/// <summary>
/// 未污染--用于生活
/// </summary>
[DisplayName("未污染--用于生活")]
public decimal? Unpolluted_living { get; set; }
/// <summary>
/// 总储水量
/// </summary>
[DisplayName("总储水量")]
public decimal? Total_water { get; set; }
/// <summary>
/// 工业废水排放量
/// </summary>
[DisplayName("工业废水排放量")]
public decimal? Industrial_wastewater { get; set; }
/// <summary>
/// 生效时间
/// </summary>
[DisplayName("生效时间")]
public DateTime Begintime { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ENMSLibrary.entity.enms
{
[Serializable]
public class Report_dailywater
{
/// <summary>
/// id
/// </summary>
[DisplayName("id")]
public Int64 Id { get; set; }
/// <summary>
/// deviceid
/// </summary>
[DisplayName("deviceid")]
public int Deviceid { get; set; }
/// <summary>
/// devicetype
/// </summary>
[DisplayName("devicetype")]
public int Devicetype { get; set; }
/// <summary>
/// devicename
/// </summary>
[DisplayName("devicename")]
public string Devicename { get; set; }
/// <summary>
/// occurdate
/// </summary>
[DisplayName("occurdate")]
public DateTime Occurdate { get; set; }
/// <summary>
/// watermin
/// </summary>
[DisplayName("watermin")]
public decimal? Watermin { get; set; }
/// <summary>
/// watermax
/// </summary>
[DisplayName("watermax")]
public decimal? Watermax { get; set; }
/// <summary>
/// waterdata
/// </summary>
[DisplayName("waterdata")]
public decimal? Waterdata { get; set; }
/// <summary>
/// creattime
/// </summary>
[DisplayName("creattime")]
public DateTime Creattime { get; set; }
}
}
两个数据库处理方式
Report_dailywaterDataService.cs和Wayder_watersetDataService.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using MySql.Data;
using ENMSLibrary.entity.enms;
using QCLibrary.exception;
using System.Data;
namespace ENMSLibrary.dataservice.enms
{
public class Report_dailywaterDataService
{
private string cuscode = "";
private string custable = "";
/// <summary>
/// 构造函数
/// </summary>
public Report_dailywaterDataService(string cuscode)
{
this.cuscode = cuscode;
this.custable = cuscode + "_" + "report_dailywater";
}
/// <summary>
/// 生成创建 增加sql语句
/// </summary>
/// <param name="data">Report_dailywater实体类</param>
/// <returns>新建类SQL语句</returns>
private string GenerateAddSql(Report_dailywater data)
{
string ls_pre = "INSERT INTO " + custable + "(";
string ls_value = "VALUES(";
if (data == null)
{
throw new BizException("新建的数据不能为空");
}
#region deviceid
if (data.Deviceid == null)
{
}
else
{
ls_pre = ls_pre + " DEVICEID";
ls_value = ls_value + " " + data.Deviceid + "";
}
#endregion
#region devicetype
if (data.Devicetype == null)
{
}
else
{
ls_pre = ls_pre + ", DEVICETYPE";
ls_value = ls_value + "," + data.Devicetype + "";
}
#endregion
#region devicename
if (data.Devicename == null)
{
}
else
{
if (data.Devicename.Length > 255)
{
throw new BizException("devicename不能超过255");
}
ls_pre = ls_pre + ", DEVICENAME";
ls_value = ls_value + ",'" + data.Devicename + "'";
}
#endregion
#region occurdate
if (data.Occurdate == null)
{
}
else
{
ls_pre = ls_pre + ", OCCURDATE";
ls_value = ls_value + ",'" + data.Occurdate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
#endregion
#region watermin
if (data.Watermin == null)
{
}
else
{
ls_pre = ls_pre + ", watermin";
ls_value = ls_value + "," + data.Watermin.ToString() + "";
}
#endregion
#region watermax
if (data.Watermax == null)
{
}
else
{
ls_pre = ls_pre + ", watermax";
ls_value = ls_value + "," + data.Watermax.ToString() + "";
}
#endregion
#region waterdata
if (data.Waterdata == null)
{
}
else
{
ls_pre = ls_pre + ", waterdata";
ls_value = ls_value + "," + data.Waterdata.ToString() + "";
}
#endregion
#region creattime
if ((data.Creattime == null) || (data.Creattime.ToString().Substring(0, 4).Equals("1900")) || (data.Creattime.ToString().Substring(0, 4).Equals("0001")))
{
}
else
{
ls_pre = ls_pre + ", CREATTIME";
ls_value = ls_value + ",'" + data.Creattime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
#endregion
return ls_pre + ") " + ls_value + ")";
}
/// <summary>
/// 添加记录
/// </summary>
/// <param name="model">Data.Model.Report_dailywater实体类</param>
/// <returns>新增记录的ID</returns>
public void Add(MySqlConnection conn, MySqlTransaction trans, Report_dailywater data)
{
try
{
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
string sql = this.GenerateAddSql(data);
MySqlCommand command;
if (trans != null)
{
command = new MySqlCommand(sql, conn, trans);
}
else
{
command = new MySqlCommand(sql, conn);
}
command.ExecuteNonQuery();
}
catch (BizException be)
{
throw be;
}
catch (Exception e)
{
throw new BizException(e.Message);
}
}
/// <summary>
/// 生成修改sql语句
/// </summary>
/// <param name="data">Report_dailywater实体类</param>
/// <returns>修改类SQL语句</returns>
private string GenerateUpdateSql(Report_dailywater data)
{
string ls_pre = "UPDATE " + custable + " SET ";
string ls_value = "";
if (data == null)
{
throw new BizException("新建的数据不能为空");
}
#region deviceid
if (data.Deviceid == null)
{
}
else
{
ls_value = "DEVICEID=" + data.Deviceid + "";
ls_pre = ls_pre + ls_value;
}
#endregion
#region devicetype
if (data.Devicetype == null)
{
}
else
{
ls_value = ",DEVICETYPE=" + data.Devicetype + "";
ls_pre = ls_pre + ls_value;
}
#endregion
#region devicename
if (data.Devicename == null)
{
}
else
{
if (data.Devicename.Length > 255)
{
throw new BizException("devicename不能超过255");
}
ls_value = ",DEVICENAME='" + data.Devicename + "'";
ls_pre = ls_pre + ls_value;
}
#endregion
#region occurdate
if (data.Occurdate == null)
{
}
else
{
ls_value = ",OCCURDATE='" + data.Occurdate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
ls_pre = ls_pre + ls_value;
}
#endregion
#region watermin
if (data.Watermin == null)
{
}
else
{
ls_value = ",watermin='" + data.Watermin.ToString() + "'";
ls_pre = ls_pre + ls_value;
}
#endregion
#region watermax
if (data.Watermax == null)
{
}
else
{
ls_value = ",watermax='" + data.Watermax.ToString() + "'";
ls_pre = ls_pre + ls_value;
}
#endregion
#region waterdata
if (data.Waterdata == null)
{
}
else
{
ls_value = ",waterdata='" + data.Waterdata.ToString() + "'";
ls_pre = ls_pre + ls_value;
}
#endregion
#region creattime
if (data.Creattime == null)
{
}
else
{
ls_value = ",CREATTIME='" + data.Creattime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
ls_pre = ls_pre + ls_value;
}
#endregion
string result = ls_pre + " WHERE id='" + data.Id + "'";
return result;
}
/// <summary>
/// 根据主键修改记录
/// </summary>
public int Update(MySqlConnection conn, MySqlTransaction trans, Report_dailywater data)
{
string sql = GenerateUpdateSql(data);
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
try
{
MySqlCommand command;
if (trans != null)
{
command = new MySqlCommand(sql, conn, trans);
}
else
{
command = new MySqlCommand(sql, conn);
}
return command.ExecuteNonQuery();
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
/// <summary>
/// 删除记录
/// </summary>
public int Delete(MySqlConnection conn, MySqlTransaction trans, Int64 id)
{
string sql = "DELETE FROM " + custable + " WHERE id='" + id + "'";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
try
{
MySqlCommand command;
if (trans != null)
{
command = new MySqlCommand(sql, conn, trans);
}
else
{
command = new MySqlCommand(sql, conn);
}
return command.ExecuteNonQuery();
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
/// <summary>
/// 将DataSet转换为List
/// </summary>
private List<Report_dailywater> DataSetToList(DataSet dataSet)
{
List<Report_dailywater> resultList = new List<Report_dailywater>();
Report_dailywater model = null;
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
model = new Report_dailywater();
model.Id = Convert.ToInt64(dataSet.Tables[0].Rows[i]["ID"].ToString());
if (dataSet.Tables[0].Rows[i]["DEVICEID"] != DBNull.Value)
model.Deviceid = Convert.ToInt32(dataSet.Tables[0].Rows[i]["DEVICEID"].ToString());
if (dataSet.Tables[0].Rows[i]["DEVICETYPE"] != DBNull.Value)
model.Devicetype = Convert.ToInt32(dataSet.Tables[0].Rows[i]["DEVICETYPE"].ToString());
if (dataSet.Tables[0].Rows[i]["DEVICENAME"] != DBNull.Value)
model.Devicename = dataSet.Tables[0].Rows[i]["DEVICENAME"].ToString();
if (dataSet.Tables[0].Rows[i]["OCCURDATE"] != DBNull.Value)
model.Occurdate = Convert.ToDateTime(dataSet.Tables[0].Rows[i]["OCCURDATE"].ToString());
if (dataSet.Tables[0].Rows[i]["WATERMIN"] != DBNull.Value)
model.Watermin = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["WATERMIN"].ToString());
if (dataSet.Tables[0].Rows[i]["WATERMAX"] != DBNull.Value)
model.Watermax = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["WATERMAX"].ToString());
if (dataSet.Tables[0].Rows[i]["WATERDATA"] != DBNull.Value)
model.Waterdata = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["WATERDATA"].ToString());
if (dataSet.Tables[0].Rows[i]["CREATTIME"] != DBNull.Value)
model.Creattime = Convert.ToDateTime(dataSet.Tables[0].Rows[i]["CREATTIME"].ToString());
resultList.Add(model);
}
return resultList;
}
/// <summary>
/// 查询所有记录
/// </summary>
public List<Report_dailywater> GetAll(MySqlConnection conn, MySqlTransaction trans)
{
string sql = "SELECT * FROM " + custable;
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
List<Report_dailywater> resultList = DataSetToList(dataSet);
return resultList;
}
else
{
return null;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 查询所有记录数量
/// </summary>
public Int32 GetCount(MySqlConnection conn, MySqlTransaction trans)
{
string sql = "SELECT COUNT(*) FROM " + custable;
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(dataSet.Tables[0].Rows[0][0].ToString());
}
else
{
return 0;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 查询所有记录
/// </summary>
public Report_dailywater Get(MySqlConnection conn, MySqlTransaction trans, Int64 id)
{
string sql = "SELECT * FROM " + custable + " WHERE id='" + id + "'";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
List<Report_dailywater> resultList = DataSetToList(dataSet);
return resultList[0];
}
else
{
return null;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 删除记录
/// </summary>
public int DeleteByOccuerdate(MySqlConnection conn, MySqlTransaction trans, DateTime occuerdate)
{
string sql = "DELETE FROM " + custable + " WHERE occurdate='" + occuerdate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
try
{
MySqlCommand command;
if (trans != null)
{
command = new MySqlCommand(sql, conn, trans);
}
else
{
command = new MySqlCommand(sql, conn);
}
return command.ExecuteNonQuery();
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
/// <summary>
/// 查询所有记录
/// </summary>
public List<Report_dailywater> GetAllByDate(MySqlConnection conn, MySqlTransaction trans, DateTime ocuordate)
{
string sql = "SELECT * FROM " + custable + " where occurdate='" + ocuordate.Date.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
List<Report_dailywater> resultList = DataSetToList(dataSet);
return resultList;
}
else
{
return null;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 查询站点日数据
/// </summary>
public Report_dailywater GetStationDaily(MySqlConnection conn, MySqlTransaction trans, string stationid, DateTime ocuordate)
{
string sql = "SELECT * FROM " + custable + " where deviceid='" + stationid + "' and occurdate='" + ocuordate.Date.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
List<Report_dailywater> resultList = DataSetToList(dataSet);
return resultList[0];
}
else
{
return null;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 汇总月数据
/// </summary>
public Decimal GetSum(MySqlConnection conn, MySqlTransaction trans, string stationid, DateTime begintime, DateTime endtime)
{
string sql = "SELECT SUM(waterdata) FROM " + custable + " where deviceid='" + stationid + "' and occurdate >= '" + begintime.ToString("yyyy-MM-dd HH:mm:ss") + "' and occurdate <= '" + endtime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if ((dataSet.Tables[0].Rows != null) && (dataSet.Tables[0].Rows.Count > 0))
{
return Convert.ToDecimal(dataSet.Tables[0].Rows[0][0].ToString());
}
else
{
return 0;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
}
}
using ENMSLibrary.entity.enms.wayder;
using MySql.Data.MySqlClient;
using QCLibrary.exception;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ENMSLibrary.dataservice.enms.wayder
{
public class Wayder_watersetDataService
{
/// <summary>
/// 构造函数
/// </summary>
public Wayder_watersetDataService()
{
}
/// <summary>
/// 生成创建 增加sql语句
/// </summary>
/// <param name="data">Wayder_waterset实体类</param>
/// <returns>新建类SQL语句</returns>
private string GenerateAddSql(Wayder_waterset data)
{
string ls_pre = "INSERT INTO WAYDER_WATERSET(";
string ls_value = "VALUES(";
if (data == null)
{
throw new BizException("新建的数据不能为空");
}
#region ID
if (data.Id == null)
{
}
else
{
if (data.Id.Length > 36)
{
throw new BizException("ID不能超过36");
}
ls_pre = ls_pre + " ID";
ls_value = ls_value + "'" + data.Id + "'";
}
#endregion
#region CREATTIME
if (data.Creattime == null)
{
}
else
{
ls_pre = ls_pre + ", CREATTIME";
ls_value = ls_value + ",'" + data.Creattime + "'";
}
#endregion
#region WASTEWATER_LIVING
if (data.Wastewater_living == null)
{
}
else
{
ls_pre = ls_pre + ", WASTEWATER_LIVING";
ls_value = ls_value + "," + data.Wastewater_living + "";
}
#endregion
#region UNPOLLUTED_USED
if (data.Unpolluted_used == null)
{
}
else
{
ls_pre = ls_pre + ", UNPOLLUTED_USED";
ls_value = ls_value + "," + data.Unpolluted_used + "";
}
#endregion
#region UNPOLLUTED_LIVING
if (data.Unpolluted_living == null)
{
}
else
{
ls_pre = ls_pre + ", UNPOLLUTED_LIVING";
ls_value = ls_value + "," + data.Unpolluted_living + "";
}
#endregion
#region TOTAL_WATER
if (data.Total_water == null)
{
}
else
{
ls_pre = ls_pre + ", TOTAL_WATER";
ls_value = ls_value + "," + data.Total_water + "";
}
#endregion
#region INDUSTRIAL_WASTEWATER
if (data.Industrial_wastewater == null)
{
}
else
{
ls_pre = ls_pre + ", INDUSTRIAL_WASTEWATER";
ls_value = ls_value + "," + data.Industrial_wastewater + "";
}
#endregion
#region BEGINTIME
if (data.Begintime == null)
{
}
else
{
ls_pre = ls_pre + ", BEGINTIME";
ls_value = ls_value + ",'" + data.Begintime + "'";
}
#endregion
return ls_pre + ") " + ls_value + ")";
}
/// <summary>
/// 添加记录
/// </summary>
/// <param name="model">Data.Model.Wayder_waterset实体类</param>
/// <returns>操作所影响的行数</returns>
public void Add(MySqlConnection conn, MySqlTransaction trans, Wayder_waterset data)
{
try
{
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
string sql = this.GenerateAddSql(data);
MySqlCommand command;
if (trans != null)
{
command = new MySqlCommand(sql, conn, trans);
}
else
{
command = new MySqlCommand(sql, conn);
}
command.ExecuteNonQuery();
}
catch (BizException be)
{
throw be;
}
catch (Exception e)
{
throw new BizException(e.Message);
}
}
/// <summary>
/// 将DataSet转换为List
/// </summary>
private List<Wayder_waterset> DataSetToList(DataSet dataSet)
{
List<Wayder_waterset> resultList = new List<Wayder_waterset>();
Wayder_waterset model = null;
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
model = new Wayder_waterset();
if (dataSet.Tables[0].Rows[i]["ID"] != DBNull.Value)
model.Id = dataSet.Tables[0].Rows[i]["ID"].ToString();
if (dataSet.Tables[0].Rows[i]["CREATTIME"] != DBNull.Value)
model.Creattime = Convert.ToDateTime(dataSet.Tables[0].Rows[i]["CREATTIME"].ToString());
if (dataSet.Tables[0].Rows[i]["WASTEWATER_LIVING"] != DBNull.Value)
model.Wastewater_living = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["WASTEWATER_LIVING"].ToString());
if (dataSet.Tables[0].Rows[i]["UNPOLLUTED_USED"] != DBNull.Value)
model.Unpolluted_used = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["UNPOLLUTED_USED"].ToString());
if (dataSet.Tables[0].Rows[i]["UNPOLLUTED_LIVING"] != DBNull.Value)
model.Unpolluted_living = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["UNPOLLUTED_LIVING"].ToString());
if (dataSet.Tables[0].Rows[i]["TOTAL_WATER"] != DBNull.Value)
model.Total_water = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["TOTAL_WATER"].ToString());
if (dataSet.Tables[0].Rows[i]["INDUSTRIAL_WASTEWATER"] != DBNull.Value)
model.Industrial_wastewater = Convert.ToDecimal(dataSet.Tables[0].Rows[i]["INDUSTRIAL_WASTEWATER"].ToString());
if (dataSet.Tables[0].Rows[i]["BEGINTIME"] != DBNull.Value)
model.Begintime = Convert.ToDateTime(dataSet.Tables[0].Rows[i]["BEGINTIME"].ToString());
resultList.Add(model);
}
return resultList;
}
/// <summary>
/// 查询所有记录
/// </summary>
public List<Wayder_waterset> GetAll(MySqlConnection conn, MySqlTransaction trans)
{
string sql = "SELECT * FROM wayder_waterset";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
List<Wayder_waterset> resultList = DataSetToList(dataSet);
return resultList;
}
else
{
return null;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 查询所有记录
/// </summary>
public Wayder_waterset GetByDate(MySqlConnection conn, MySqlTransaction trans,DateTime queryDate)
{
string sql = "SELECT * FROM wayder_waterset where Begintime <='"+ queryDate.ToString("yyyy-MM-dd") + "' order by Begintime desc limit 0,1 ";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
List<Wayder_waterset> resultList = DataSetToList(dataSet);
return resultList[0];
}
else
{
return null;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
/// <summary>
/// 查询所有记录数量
/// </summary>
public Int32 GetCount(MySqlConnection conn, MySqlTransaction trans)
{
string sql = "SELECT COUNT(*) FROM wayder_waterset";
if (conn == null || conn.State != ConnectionState.Open)
{
throw new Exception("数据连接不可用");
}
DataSet dataSet = new DataSet();
try
{
MySqlDataAdapter sqlDA = new MySqlDataAdapter(sql, conn);
if (trans != null) sqlDA.SelectCommand.Transaction = trans;
sqlDA.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(dataSet.Tables[0].Rows[0][0].ToString());
}
else
{
return 0;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception("数据库操作异常", ex);
}
catch (Exception e)
{
throw e;
}
finally
{
dataSet = null;
}
}
}
}