using System using System Collections Generic using System Text using

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Analyse2
{
class AnalyseMSSql
{
protected SqlConnection _connection = null;
protected DateTime _interval_start;
protected DateTime _interval_end;
public AnalyseMSSql(string ConnectionString, DateTime startDate, DateTime endDate)
{
_connection = new SqlConnection(ConnectionString);
_interval_start = startDate;
_interval_end = endDate;
}
public bool OpenConnection()
{
_connection.Open();
return true;
}
public bool CloseConnection()
{
_connection.Close();
return true;
}
public ConnectionState StateConnection
{
get { return _connection.State; }
}
// public SensorInformation GetSensorInformation(int id)
// {
// if (this.StateConnection != ConnectionState.Open)
// this.OpenConnection();
// OracleCommand command = _connection.CreateCommand();
// command.CommandText = String.Format(@"select * from sensor where sensor_id={0}", id);
// SensorInformation res = new SensorInformation();
// try
// {
// OracleDataReader reader = command.ExecuteReader();
// if (reader != null)
// {
// while (reader.Read())
// {
// res.ID = reader["sensor_id"].ToString();
// res.Oldcode = reader["sensor_oldcode"].ToString();
// res.Description = reader["sensor_descr"].ToString();
// }
// }
// }
// catch
// {
// return null;
// }
// command.CommandText = String.Format(@"select /*+ INDEX_DESC (t idx$measure$measure_date) */
// max(t.measure_id) from measure t where sensor_id={2} and t.measure_date between '{0}' and '{1}'",
// _interval_start.ToShortDateString(),
// _interval_end.ToShortDateString(),
// id);
// try
// {
// double measure_id = (double)(OracleNumber)command.ExecuteOracleScalar();
// command.CommandText = String.Format("select measure_date from measure where measure_id={0}", measure_id);
// res.LastMeasure = command.ExecuteOracleScalar().ToString();
// command.CommandText = String.Format(@"select count(*) as count_lane from (
// select t.sensor_lane_num from measure_values t where measure_id = {0} group by t.sensor_lane_num) tmp", measure_id);
// res.LineCount = command.ExecuteOracleScalar().ToString();
// }
// catch { }
// return res;
// }
// public int GetOldcode(int id)
// {
// //select sensor_oldcode from measure where sensor_id={0}
// if (this.StateConnection != ConnectionState.Open)
// this.OpenConnection();
// OracleCommand command = _connection.CreateCommand();
// command.CommandText = String.Format(@"select sensor_oldcode from measure where sensor_id={0}", id);
// try
// {
// return (int)(OracleNumber)command.ExecuteOracleScalar();
// }
// catch
// {
// return -1;
// }
// }
public string GetMeasureCount(int id)
{
//select /*+ INDEX_DESC (t idx$measure$measure_date) */ count(distinct(measure_date)) from measure t where t.sensor_id={0} and t.measure_date between '{1}' and '{2}'
if (this.StateConnection != ConnectionState.Open)
this.OpenConnection();
SqlCommand command = _connection.CreateCommand();
command.CommandText = String.Format("select /*+ INDEX_DESC (t idx$sensordata$datetime_) */ count(*) from sensordata t where sensor_id={0} and t.datetime_ between '{1}' and '{2}'",
id,
_interval_start.ToString("yyyy-MM-dd"),
_interval_end.ToString("yyyy-MM-dd"));
try
{
return command.ExecuteScalar().ToString();
}
catch(Exception e)
{
return e.Message;
}
}
}
}