//For Select Data
public static string DBFilDTable(ref DataTable nDTable, string SQLQuery)
{
string nRtnValue = "N";
try
{
SqlDataAdapter mSQLDA = new SqlDataAdapter(SQLQuery, _SqlConnection);
nDTable.Clear();
nDTable.Columns.Clear();
mSQLDA.Fill(nDTable);
nRtnValue = (nDTable.Rows.Count > 0) ? "Y" : "N";
}
catch (SqlException SqlEx)
{
nRtnValue = SqlEx.ToString();
}
catch (Exception ex)
{
//Throw ex
nRtnValue = ex.Message;
}
return(nRtnValue);
}
/*Return a resultset from a select statement with parameters,*/
public SqlDataReader ReadQueryStoredProcedure(String Command, List <SqlParameter> parameters)
{
log.Info("DataAccess:ReadQuery[parameter overload]");
SqlDataReader Odr;
using (SqlCommand Odc = new SqlCommand(Command, SQlConn))
{
try
{
log.Debug("ReadQuery:QueryString:" + Command);
Odc.CommandType = CommandType.StoredProcedure;
foreach (var param in parameters)
{
Odc.Parameters.Add(param);
}
Odr = Odc.ExecuteReader();
return(Odr);
}
catch (SqlException SqlEx)
{
try
{
log.Error("[ReadQuery] returned: " + SqlEx.ToString());
throw SqlEx;
}
catch (Exception e)
{
throw e;
}
}
}
}
/*Return a resultset from a select statement*/
public SqlDataReader ReadQuery(String Command)
{
log.Info("DataAccess:ReadQuery");
SqlDataReader Odr;
using (SqlCommand Odc = new SqlCommand(Command, SQlConn))
{
try
{
log.Debug("ReadQuery:QueryString:" + Command);
Odr = Odc.ExecuteReader();
return(Odr);
}
catch (SqlException SqlEx)
{
try
{
log.Error("[CountQuery] returned: " + SqlEx.ToString());
throw SqlEx;
}
catch (Exception e)
{
throw e;
}
}
}
}
//For Save, Update and Delete Data
public static string DB_ExecuteNonQuery(string pSQL)
{
string nRtnValue = "Started";
try
{
if (_SqlConnection.State != ConnectionState.Open)
{
_SqlConnection.Open();
}
SqlCommand _SqlCommand = new SqlCommand(pSQL, _SqlConnection);
_SqlCommand.CommandType = CommandType.Text;
_SqlCommand.ExecuteNonQuery();
if (_SqlConnection.State != ConnectionState.Closed)
{
_SqlConnection.Close();
}
nRtnValue = "Y";
}
catch (SqlException SqlEx)
{
nRtnValue = SqlEx.ToString();
}
catch (Exception ex)
{
//Throw ex
nRtnValue = ex.Message;
}
return(nRtnValue);
}
public void TeselarData()
{
try
{
if (AbrirConexion())
{
lock (this)
{
string sSql = "";
try
{
sSql = "SELECT DISTINCT ip FROM `00equipos`;";
MySql.Data.MySqlClient.MySqlCommand MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySql.Data.MySqlClient.MySqlDataReader MyReaderEquipos = MySQLComm.ExecuteReader();
IList <string> Equipos = new List <string>();
if (MyReaderEquipos.HasRows)
{
while (MyReaderEquipos.Read())
{
if (!MyReaderEquipos.IsDBNull(0))
{
Equipos.Add(MyReaderEquipos.GetString("ip"));
}
}
}
MyReaderEquipos.Close();
MyReaderEquipos.Dispose();
foreach (string Eq in Equipos)
{
//Solo calcula los datos a día pasado
sSql = "SELECT COUNT(equipo) AS Registros FROM `02diario` WHERE (fecha = '" + DateTime.Now.Date.AddDays(-1.0).AddHours(12.0).ToString("yyyy-MM-dd HH:mm:ss") + "') AND (equipo = '" + Eq + "')";
MySQLComm =
new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
if (MySQLComm.ExecuteScalar().ToString() == "0")
{
//Selecciona los datos diarios
//De cada dispositivo
sSql = "SELECT fecha, temperatura, humedad, co, consumo FROM `01input` WHERE (equipo = '" + Eq + "') AND (fecha BETWEEN '" + DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss") + "')";
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySql.Data.MySqlClient.MySqlDataReader MyReaderDiario = MySQLComm.ExecuteReader();
IList <Lectura> Lecturas = new List <Lectura>();
if (MyReaderDiario.HasRows)
{
while (MyReaderDiario.Read())
{
Lecturas.Add(new Lectura(MyReaderDiario.GetDateTime("fecha"),
"",
MyReaderDiario.GetDouble("temperatura"),
MyReaderDiario.GetDouble("humedad"),
MyReaderDiario.GetDouble("co"),
MyReaderDiario.GetDouble("consumo")));
}
}
MyReaderDiario.Close();
MyReaderDiario.Dispose();
//Genera el resumen diario
if (Lecturas.Count > 0)
{
Lectura LecturaDiaria = new Lectura(DateTime.Now.Date.AddDays(-1.0).AddHours(12.0),
Eq,
Lecturas.Select(L => L.Temperatura).Average(),
Lecturas.Select(L => L.Humedad).Average(),
Lecturas.Select(L => L.CO).Average(),
Lecturas.Select(L => L.Consumo).Average());
sSql = string.Format("INSERT" + "\n" +
"INTO {0} ({1}, {3}, {5}, {7}, {9}, {11})" + "\n" +
"VALUES('{2}', '{4}', {6}, {8}, {10}, {12});",
"`02diario`",
"fecha", LecturaDiaria.Fecha.ToString("yyyy-MM-dd HH:mm:ss"),
"equipo", LecturaDiaria.IpEquipo,
"temperatura", LecturaDiaria.Temperatura.ToString("0.00").Replace(',', '.'),
"humedad", LecturaDiaria.Humedad.ToString("0.00").Replace(',', '.'),
"co", LecturaDiaria.CO.ToString("0.0000").Replace(',', '.'),
"consumo", LecturaDiaria.Consumo.ToString("0.00").Replace(',', '.'));
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySQLComm.ExecuteNonQuery();
}
}
//Solo calcula los datos a mes pasado
DateTime mespasado = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1);
sSql = "SELECT COUNT(equipo) AS Registros FROM `03mensual` WHERE (fecha = '" + mespasado.AddDays(14.0).AddHours(12.0).ToString("yyyy-MM-dd HH:mm:ss") + "') AND (equipo = '" + Eq + "')";
MySQLComm =
new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
if (MySQLComm.ExecuteScalar().ToString() == "0")
{
//Selecciona los datos diarios
//De cada dispositivo
sSql = "SELECT fecha, temperatura, humedad, co, consumo FROM `02diario` WHERE (equipo = '" + Eq + "') AND (fecha BETWEEN '" + mespasado.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + mespasado.AddMonths(1).ToString("yyyy-MM-dd HH:mm:ss") + "');";
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySql.Data.MySqlClient.MySqlDataReader MyReaderMensual = MySQLComm.ExecuteReader();
IList <Lectura> Lecturas = new List <Lectura>();
if (MyReaderMensual.HasRows)
{
while (MyReaderMensual.Read())
{
Lecturas.Add(new Lectura(MyReaderMensual.GetDateTime("fecha"),
"",
MyReaderMensual.GetDouble("temperatura"),
MyReaderMensual.GetDouble("humedad"),
MyReaderMensual.GetDouble("co"),
MyReaderMensual.GetDouble("consumo")));
}
}
MyReaderMensual.Close();
MyReaderMensual.Dispose();
//Genera el resumen diario
if (Lecturas.Count > 0)
{
Lectura LecturaDiaria = new Lectura(mespasado.AddDays(14.0).AddHours(12.0),
Eq,
Lecturas.Select(L => L.Temperatura).Average(),
Lecturas.Select(L => L.Humedad).Average(),
Lecturas.Select(L => L.CO).Average(),
Lecturas.Select(L => L.Consumo).Average());
sSql = string.Format("INSERT INTO {0} ({1}, {3}, {5}, {7}, {9}, {11})" + "\n" +
"VALUES('{2}', '{4}', {6}, {8}, {10}, {12})",
"`03mensual`",
"fecha", LecturaDiaria.Fecha.ToString("yyyy-MM-dd HH:mm:ss"),
"equipo", LecturaDiaria.IpEquipo,
"temperatura", LecturaDiaria.Temperatura.ToString("0.00").Replace(',', '.'),
"humedad", LecturaDiaria.Humedad.ToString("0.00").Replace(',', '.'),
"co", LecturaDiaria.CO.ToString("0.0000").Replace(',', '.'),
"consumo", LecturaDiaria.Consumo.ToString("0.00").Replace(',', '.'));
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySQLComm.ExecuteNonQuery();
}
}
}
}
catch (MySql.Data.MySqlClient.MySqlException MyEx)
{
ErrLogger.LogError(MyEx.ToString() + "\n" + sSql, System.Diagnostics.EventLogEntryType.Error);
}
}
}
else
{
ErrLogger.LogError("Sin conexión con SQLServer.", System.Diagnostics.EventLogEntryType.Error);
}
}
catch (System.Data.SqlClient.SqlException SqlEx)
{
ErrLogger.LogError(SqlEx.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
catch (Exception Ex)
{
ErrLogger.LogError(Ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
}
public bool SetData(string CadenaLectura)
{
try
{
if (AbrirConexion())
{
Lectura RecLectura = new Lectura(CadenaLectura);
bool Res = false;
lock (this)
{
string sSql = "";
try
{
sSql = "SELECT ip FROM `00equipos` WHERE (ip = '" + RecLectura.IpEquipo + "')";
MySql.Data.MySqlClient.MySqlCommand MySQLComm =
new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySql.Data.MySqlClient.MySqlDataReader DR1 = MySQLComm.ExecuteReader();
bool hasRows = DR1.HasRows;
DR1.Close();
DR1.Dispose();
if (!hasRows)
{
sSql = "INSERT INTO `00equipos` (ip, nombre) VALUES('" + RecLectura.IpEquipo + "', 'Pendiente de Nombre');";
//no existe el equipo
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySQLComm.ExecuteNonQuery();
}
sSql = string.Format("INSERT" + "\n" +
"INTO {0} ({1}, {3}, {5}, {7}, {9}, {11})" + "\n" +
"VALUES('{2}', '{4}', {6}, {8}, {10}, {12});",
"`01input`",
"fecha", RecLectura.Fecha.ToString("yyyy-MM-dd HH:mm:ss"),
"equipo", RecLectura.IpEquipo,
"temperatura", RecLectura.Temperatura.ToString("0.00").Replace(',', '.'),
"humedad", RecLectura.Humedad.ToString("0.00").Replace(',', '.'),
"co", RecLectura.CO.ToString("0.0000").Replace(',', '.'),
"consumo", RecLectura.Consumo.ToString("0.00").Replace(',', '.'));
//TODO: UPDATE en 00current para la última lectura
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySQLComm.ExecuteNonQuery();
sSql = string.Format("INSERT INTO {0} ({1}, {3}, {5}, {7}, {9}, {11})" + "\n" +
"VALUES('{2}', '{4}', {6}, {8}, {10}, {12})" + "\n" +
"ON DUPLICATE KEY" + "\n" +
"UPDATE {1}='{2}', {5}={6}, {7}={8}, {9}={10}, {11}={12}",
"`00current`",
"fecha", RecLectura.Fecha.ToString("yyyy-MM-dd HH:mm:ss"),
"equipo", RecLectura.IpEquipo,
"temperatura", RecLectura.Temperatura.ToString("0.00").Replace(',', '.'),
"humedad", RecLectura.Humedad.ToString("0.00").Replace(',', '.'),
"co", RecLectura.CO.ToString("0.0000").Replace(',', '.'),
"consumo", RecLectura.Consumo.ToString("0.00").Replace(',', '.'));
MySQLComm = new MySql.Data.MySqlClient.MySqlCommand(sSql, MySQLConn);
MySQLComm.ExecuteNonQuery();
Res = true;
}
catch (MySql.Data.MySqlClient.MySqlException MyEx)
{
ErrLogger.LogError(MyEx.ToString() + "\n" + sSql, System.Diagnostics.EventLogEntryType.Error);
Res = false;
}
}
return(Res);
}
else
{
ErrLogger.LogError("Sin conexión con SQLServer.", System.Diagnostics.EventLogEntryType.Error);
return(false);
}
}
catch (System.Data.SqlClient.SqlException SqlEx)
{
ErrLogger.LogError(SqlEx.ToString(), System.Diagnostics.EventLogEntryType.Error);
return(false);
}
catch (Exception Ex)
{
ErrLogger.LogError(Ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
return(false);
}
}
/*~RecopiladorDatos()
* {
* //Finaliza el web service
* MySQLConn.Close();
* MySQLConn.Dispose();
* }*/
private bool AbrirConexion(bool Reiniciar = false)
{
try
{
if (MySQLConn != null)
{
if (MySQLConn.State == System.Data.ConnectionState.Open)
{
//Ya está abierta
if (Reiniciar)
{
ReiniciarConexion();
}
}
else if (MySQLConn.State == System.Data.ConnectionState.Closed)
{
MySQLConn.Open();
}
else if (MySQLConn.State == System.Data.ConnectionState.Connecting)
{
lock (this)
{
while (MySQLConn.State != System.Data.ConnectionState.Open)
{
System.Threading.Thread.Sleep(10);
}
}
}
else
{
//reinicia la conexión
ReiniciarConexion();
}
return(true);
}
else
{
ErrLogger.LogError("Sin conexión con SQLServer.", System.Diagnostics.EventLogEntryType.Error);
return(false);
}
}
catch (System.Data.SqlClient.SqlException SqlEx)
{
switch (SqlEx.Number)
{
case 0:
ErrLogger.LogError("No se puede conectar con el servidor. Contacte con el administrador", System.Diagnostics.EventLogEntryType.Error);
break;
case 1045:
ErrLogger.LogError("Usuario/Clave incorrectos", System.Diagnostics.EventLogEntryType.Error);
break;
default:
ErrLogger.LogError(SqlEx.ToString(), System.Diagnostics.EventLogEntryType.Error);
break;
}
return(false);
}
catch (Exception Ex)
{
ErrLogger.LogError(Ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
return(false);
}
}
public void ProcessRequest(HttpContext context)
{
if (context.Request.HttpMethod == "POST" && (string)context.Request.Form["action"] == "bid_add")
{
string referrer = context.Request.UrlReferrer.AbsoluteUri;
try
{
if (!Int32.TryParse(context.Request.Form["itemid"], out itemid))
{
// If the itemid cannot be parsed into an integer, redirect the user
context.Response.Redirect(referrer);
return;
}
if (!Double.TryParse(context.Request.Form["bid_amount"], out bid_amount))
{
// If the bid amount cannot be parsed into a Double, redirect the user
context.Response.Redirect(referrer);
return;
}
else
{
// If the bid amount is less than 0, redirect the user
if (bid_amount < 0)
{
context.Response.Redirect(referrer);
}
}
// Create the bid
db.CreateBid((int)context.Session["id"], itemid, bid_amount, DateTime.Now);
}
catch (SqlException SqlEx)
{
if (SqlEx.Number == 2627)
{
// User already has a bid for this item, update it with the new value
db.UpdateBid((int)context.Session["id"], (int)Int32.Parse(context.Request.Form["itemid"]), (double)Double.Parse(context.Request.Form["bid_amount"]), DateTime.Now);
}
else
{
// Unknown Exception in the SQL Command
context.Response.StatusCode = 400;
context.Response.Write("Caught Exception: " + SqlEx.ToString());
context.Response.End();
return;
}
}
catch (Exception e)
{
{
// Unknown Exception in try block
context.Response.StatusCode = 400;
context.Response.Write("Caught Exception: " + e.Message + ":<br>" + e.StackTrace.ToString());
context.Response.End();
return;
}
}
finally
{
// Redirect the user to the referring uri
context.Response.Redirect(referrer);
}
}
}
public ArrayList QuerySystemSP_Output(ArrayList ParameterArrayList, ArrayList ValueArrayList, ArrayList TypeArrayList, ArrayList OutputParameterArrayList, ArrayList OutputTypeArrayList, string SPName)
{
SqlConnection conn;
SqlCommand cmd;
SqlParameter myPar;
SqlDataReader drData;
SqlDbType ParType = SqlDbType.VarChar;
ArrayList alData = new ArrayList();
m_bQuerySystemSPErr = false;
try
{
cmd = new SqlCommand();
conn = DBConn();
cmd.Connection = conn;
cmd.CommandText = SPName;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <= ParameterArrayList.Count - 1; i++)
{
switch (Convert.ToString(TypeArrayList[i]).ToLower())
{
case "myvarchar":
ParType = myVarChar;
break;
case "myint":
ParType = myInt;
break;
case "mydatetime":
ParType = myDateTime;
break;
case "myfloat":
ParType = myFloat;
break;
case "mybit":
ParType = myBit;
break;
case "mybinary":
ParType = myBinary;
break;
case "mydecimal":
ParType = myDecimal;
break;
case "mytext":
ParType = myText;
break;
}
myPar = cmd.CreateParameter();
myPar.ParameterName = Convert.ToString(ParameterArrayList[i]);
myPar.Direction = ParameterDirection.Input;
myPar.SqlDbType = ParType;
myPar.Value = ValueArrayList[i];
cmd.Parameters.Add(myPar);
}
for (int i = 0; i <= OutputParameterArrayList.Count - 1; i++)
{
switch (Convert.ToString(OutputTypeArrayList[i]).ToLower())
{
case "myvarchar":
ParType = myVarChar;
break;
case "myint":
ParType = myInt;
break;
case "mydatetime":
ParType = myDateTime;
break;
case "myfloat":
ParType = myFloat;
break;
case "mybit":
ParType = myBit;
break;
case "mybinary":
ParType = myBinary;
break;
case "mydecimal":
ParType = myDecimal;
break;
case "mytext":
ParType = myText;
break;
}
myPar = cmd.CreateParameter();
myPar.ParameterName = (string)OutputParameterArrayList[i];
myPar.Direction = ParameterDirection.Output;
myPar.SqlDbType = ParType;
if (ParType == myVarChar)
{
myPar.Size = OutputParameterSize(myPar.ParameterName, SPName);
}
cmd.Parameters.Add(myPar);
}
conn.Open();
drData = cmd.ExecuteReader(CommandBehavior.Default);
for (int i = 0; i <= cmd.Parameters.Count - 1; i++)
{
if (cmd.Parameters[i].Direction == ParameterDirection.Output)
{
alData.Add(cmd.Parameters[i].Value);
}
}
cmd.Dispose();
conn.Close();
conn.Dispose();
}
catch (SqlException SqlEx)
{
m_bQuerySystemSPErr = true;
m_sExceptionMessage = SqlEx.ToString();
}
catch (FormatException FormEx)
{
m_bQuerySystemSPErr = true;
m_sExceptionMessage = FormEx.ToString();
}
catch (Exception ex)
{
m_bQuerySystemSPErr = true;
m_sExceptionMessage = ex.ToString();
}
return(alData);
}