MSSQLUtil
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Data;
using System.Net;
using System.Diagnostics;
using System.Xml;
using System.Xml.XPath;
using static OracleUtil;
using ICUValidationService.Log;
using EAPUtility;
public enum Posting
{
deduct,
debit,
}
class MSSQLUtil
{
public static String connectionString = "Data Source=1.1.1.1;Initial Catalog=hk;User ID=Query;Password=Query;Persist Security Info=True";
public static string[] ReadTemp(int station)
{
string[] results={"","" };
float temp = 0;
DateTime time = DateTime.MinValue;
SqlConnection conn = null;
try
{
using (conn = new SqlConnection(connectionString))
{
conn.Open();
String sql = @"SELECT TOP 1 a.tem ,a.humid , a.rectime
FROM eonekh a
where comp = 'D'and a.station = @station order by a.[rectime] desc";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("station", station);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
try
{
temp = float.Parse(reader["tem"].ToString());
time = DateTime.Parse(reader["rectime"].ToString());
}
catch (Exception)
{
temp = -999;
}
}
}
}
}
catch (Exception ex)
{
LogError( "ReadTemp", ex.Message);
}
results[0] = temp.ToString();
results[1] = time.ToString("MM/dd HH:mm");
return results;
}
static int catchNumber = 100;
public static List<eonekh> ReadTempHis(EDA_Table table)
{
List<eonekh> result = new List<eonekh>();
SqlConnection conn = null;
try
{
using (conn = new SqlConnection(connectionString))
{
conn.Open();
String sql = @"SELECT * FROM eonekh a
where recno > @from and recno <= @to order by a.recno ";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("from", table.LastIndex);
command.Parameters.AddWithValue("to", table.LastIndex + catchNumber);
//SELECT TOP(1000) [recno] ,[station] ,[rectime] ,[tem] ,[humid] ,[comp] FROM[eonethk].[dbo].[eonekh]
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
eonekh raw = new eonekh();
raw.comp = reader["comp"].ToString();
raw.recno = Decimal.Parse(reader["recno"].ToString());
raw.station = Int16.Parse(reader["station"].ToString());
raw.rectime = DateTime.Parse(reader["rectime"].ToString());
//raw.tem = Decimal.Parse(reader["tem"].ToString());
//raw.humid = Decimal.Parse(reader["humid"].ToString());
raw.humid = (reader["humid"] == System.DBNull.Value) ? decimal.MinValue : decimal.Parse(reader["humid"].ToString());
raw.tem = (reader["tem"] == System.DBNull.Value) ? decimal.MinValue : decimal.Parse(reader["tem"].ToString());
result.Add(raw);
}
catch (Exception ex2)
{
LogError( "ReadTempHis", ex2.Message);
//temp = -999;
}
}
}
}
}
catch (Exception ex)
{
LogError( "ReadTempHis", ex.Message);
}
return result;
}
public static List<EDA_WindSpeed_SpeedData> ReadWindHis(EDA_Table table)
{
List<EDA_WindSpeed_SpeedData> result = new List<EDA_WindSpeed_SpeedData>();
SqlConnection conn = null;
try
{
using (conn = new SqlConnection(connectionString))
{
conn.Open();
String sql = @"SELECT * FROM WindSpeed.dbo.SpeedData a
where autoindex > @from and autoindex <= @to order by a.autoindex ";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("from", table.LastIndex);
command.Parameters.AddWithValue("to", table.LastIndex + catchNumber);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
EDA_WindSpeed_SpeedData raw = new EDA_WindSpeed_SpeedData();
raw.Factory = reader["Factory"].ToString();
raw.autoindex = Decimal.Parse(reader["autoindex"].ToString());
raw.Loop = reader["Loop"].ToString();
raw.Panel = reader["Panel"].ToString();
raw.ID = reader["ID"].ToString();
raw.DataTime = DateTime.Parse(reader["DataTime"].ToString());
raw.PV = reader["PV"].ToString();
raw.SV = reader["SV"].ToString();
raw.Status = reader["Status"].ToString();
result.Add(raw);
}
catch (Exception ex2)
{
LogError("ReadWindHis", ex2.Message);
//temp = -999;
}
}
}
}
}
catch (Exception ex)
{
LogError( "ReadTempHis", ex.Message);
}
return result;
}
public static string Name = "MS_SQL_Utility";
public static void LogError(string fuction, string log)
{
Logger.Instance.WriteLog(QAUtility.logPath, fuction + ":" + log, LogType.Error, "Paser" + Name + "_E");
Console.WriteLine(DateTime.Now.ToString() + " E: " + log);
}
public static void Log(string log)
{
Logger.Instance.WriteLog(QAUtility.logPath, log, LogType.Information, "Pase" + Name);
Console.WriteLine(DateTime.Now.ToString() + " M: " + log);
}
public static string GetString(SqlDataReader reader, string columnName)
{
string result = null;
if (reader[columnName] != null)
result = reader[columnName].ToString();
return result;
}
public static DateTime GetDateTime(SqlDataReader reader, string columnName)
{
DateTime result = DateTime.MinValue;
try
{
if (reader[columnName] != null)
result = (DateTime)reader[columnName];
}
catch (Exception ex)
{
LogError("GetDateTime", ex.ToString());
}
return result;
}
}
留言
張貼留言