using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
namespace BRServer.DAL
{
public class BR : IDAL.IBR
{
public static readonly string ConnectionStringOrderDistributedTransaction =
System.Configuration.ConfigurationManager.AppSettings["MySqlConnString"];
private const string SQL_INSERT_ELEC = "insert into data_elec (devcode,voltage1,voltage2,voltage3,voltage4,"
+"electricity1,electricity2,electricity3,electricity4,electricity5,electricity6,electricity7,electricity8,"
+"electricity9,electricity10,electricity11,electricity12,temperature,humidity,windspeed,uptime)"
+" values (@devcode,@voltage1,@voltage2,@voltage3,@voltage4,"
+"@electricity1,@electricity2,@electricity3,@electricity4,@electricity5,@electricity6,@electricity7,@electricity8,"
+"@electricity9,@electricity10,@electricity11,@electricity12,@temperature,@humidity,@windspeed,@uptime)";
private const string SQL_INSERT_ALARM = "insert into alarm_elec (devcode,alarm) values (@devcode,@alarm)";
public void insert(Model.BR br)
{
MySqlTransaction tran = null;
using (MySqlConnection conn = new MySqlConnection(ConnectionStringOrderDistributedTransaction))
{
try
{
conn.Open();
tran = conn.BeginTransaction();
if (string.IsNullOrEmpty(br.DEVCODE))
{
throw new Exception("设备ID为空！");
}
MySqlParameter[] parms = GetAdapterParameters();
SetAdapterParameters(parms, br);
MySqlCommand cmd = new MySqlCommand(SQL_INSERT_ELEC, conn);
foreach (MySqlParameter pram in parms)
cmd.Parameters.Add(pram);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
if (null != tran)
{
tran.Rollback();
}
throw (e);
}
}
}
/// <summary>
/// An internal function to get the database parameters
/// </summary>
/// <returns>Parameter array</returns>
private static MySqlParameter[] GetAdapterParameters()
{
MySqlParameter[] parms = null;//OracleHelperParameterCache.GetCachedParameterSet(SQL_INSERT_WS, "INSERT:UPDATE");
if (parms == null)
{
parms = new MySqlParameter[]{
new MySqlParameter("@devcode", MySqlDbType.VarChar, 20),
new MySqlParameter("@voltage1", MySqlDbType.Float),
new MySqlParameter("@voltage2", MySqlDbType.Float),
new MySqlParameter("@voltage3", MySqlDbType.Float),
new MySqlParameter("@voltage4", MySqlDbType.Float),
new MySqlParameter("@electricity1", MySqlDbType.Float),
new MySqlParameter("@electricity2", MySqlDbType.Float),
new MySqlParameter("@electricity3", MySqlDbType.Float),
new MySqlParameter("@electricity4", MySqlDbType.Float),
new MySqlParameter("@electricity5", MySqlDbType.Float),
new MySqlParameter("@electricity6", MySqlDbType.Float),
new MySqlParameter("@electricity7", MySqlDbType.Float),
new MySqlParameter("@electricity8", MySqlDbType.Float),
new MySqlParameter("@electricity9", MySqlDbType.Float),
new MySqlParameter("@electricity10", MySqlDbType.Float),
new MySqlParameter("@electricity11", MySqlDbType.Float),
new MySqlParameter("@electricity12", MySqlDbType.Float),
new MySqlParameter("@temperature", MySqlDbType.Float),
new MySqlParameter("@humidity", MySqlDbType.Float),
new MySqlParameter("@windspeed", MySqlDbType.Float),
new MySqlParameter("@uptime", MySqlDbType.DateTime)
};
}
return parms;
}
/// <summary>
/// An internal function to bind values parameters for insert
/// </summary>
/// <param name="parms">Database parameters</param>
/// <param name="RQ">Values to bind to parameters</param>
private void SetAdapterParameters(MySqlParameter[] parms, Model.BR br)
{
parms[0].Value = br.DEVCODE;
int i = 1;
foreach(float voltage in br.VOLTAGES)
{
parms[i++].Value = voltage;
}
int j = 5;
foreach (float electricity in br.ELECTRICITYS)
{
parms[j++].Value = electricity;
}
parms[17].Value = br.TEMPERATURE;
parms[18].Value = br.HUMIDITY;
parms[19].Value = br.WINDSPEED;
parms[20].Value = br.UPTIME;
}
public void insertAlarm(Model.Alarm alarm)
{
MySqlTransaction tran = null;
using (MySqlConnection conn = new MySqlConnection(ConnectionStringOrderDistributedTransaction))
{
try
{
conn.Open();
tran = conn.BeginTransaction();
if (string.IsNullOrEmpty(alarm.DEVCODE))
{
throw new Exception("设备ID为空！");
}
string sql = "insert into alarm_elec (devcode,alarm) values ("
+ alarm.DEVCODE + ",'" + alarm.ALARM + "')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
if (null != tran)
{
tran.Rollback();
}
throw (e);
}
}
}
public Model.Config selectConfig(String devcode)
{
using (MySqlConnection conn = new MySqlConnection(ConnectionStringOrderDistributedTransaction))
{
MySqlDataReader reader = null;
try
{
conn.Open();
if (string.IsNullOrEmpty(devcode))
{
throw new Exception("设备ID为空！");
}
string sql = "select * from config_elec where status = '0' and devcode='" + devcode + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
reader = cmd.ExecuteReader();
Model.Config config = new Model.Config();
while (reader.Read())
{
if (reader.HasRows)
{
config.DEVCODE = reader.GetString(1);
config.IP = reader.IsDBNull(2) ? null : reader.GetString(2);
config.PORT = reader.IsDBNull(3) ? (UInt16)0 : reader.GetUInt16(3);
config.INTERVAL = reader.IsDBNull(4) ? (UInt16)0 : reader.GetUInt16(4);
}
}
return config;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
reader.Close();
conn.Close();
}
}
}
public void updateConfigStatus(String devcode)
{
MySqlTransaction tran = null;
using (MySqlConnection conn = new MySqlConnection(ConnectionStringOrderDistributedTransaction))
{
try
{
conn.Open();
tran = conn.BeginTransaction();
if (string.IsNullOrEmpty(devcode))
{
throw new Exception("设备ID为空！");
}
string sql = "update config_elec set status = '1' where devcode='" + devcode + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
if (null != tran)
{
tran.Rollback();
}
throw (e);
}
}
}
}
}
