internal IEnumerable <TemperatureLogRecord> GetTemperatureChartData(DateTime lastKnownTime)
{
var whereClause = new SqlCommandText("origin_datetime > @lastKnownTime");
whereClause.Parameters.Add("lastKnownTime", lastKnownTime);
return(getTemperatureChartData(whereClause));
}
private IEnumerable <DeviceStateLogRecord> getDeviceStateLog(SqlCommandText whereClause)
{
var records = new List <DeviceStateLogRecord>();
var selectCommand = new SqlCommandInfo(
"SELECT" +
" switch_datetime," +
" device," +
" state" +
" FROM DeviceStateLog" +
(whereClause.IsEmpty ? string.Empty : " WHERE " + whereClause.CommandText) +
" ORDER BY switch_datetime DESC");
selectCommand.AddParameters(whereClause);
SqlExecutionHelper.ExecuteReader(
selectCommand,
row =>
{
DateTime datetime = row.GetDateTime(row.GetOrdinal("switch_datetime"));
var device = (Devices)row.GetInt16(row.GetOrdinal("device"));
var state = (DeviceStates)row.GetInt16(row.GetOrdinal("state"));
records.Add(new DeviceStateLogRecord(datetime, device, state));
});
return(records);
}
internal IEnumerable <DeviceStateLogRecord> GetDeviceStateLog(TimeSpan period, Devices[] devices)
{
SqlCommandText conditionByDevices = getConditionToDeviceStateLogByDevices(devices);
SqlCommandText conditionByPeriod = getConditionToDeviceStateLogByPeriod(period);
SqlCommandText whereClause = conditionByDevices.Concat(conditionByPeriod);
return(getDeviceStateLog(whereClause));
}
private static SqlCommandText getConditionByPeriod(string datetimeFieldName, TimeSpan period)
{
var whereClause = new SqlCommandText($"{datetimeFieldName} >= DATEADD(MINUTE, -@errorMinutes, DATEADD(SECOND, -@seconds, GETUTCDATE()))");
whereClause.Parameters.Add("seconds", period.TotalSeconds);
whereClause.Parameters.Add("errorMinutes", ErrorMinutes);
return(whereClause);
}
private static SqlCommandText getConditionToDeviceStateLogByDevices(Devices[] devices)
{
if (devices.Length == 0)
{
return(SqlCommandText.Empty);
}
var whereClause = new SqlCommandText();
var parametersListBuilder = new StringBuilder();
foreach (Devices device in devices)
{
parametersListBuilder.Append($"@{device},");
whereClause.Parameters.Add(device.ToString(), (byte)device);
}
parametersListBuilder.Length--;
whereClause.CommandText = $"device IN ({parametersListBuilder})";
return(whereClause);
}
public static void ExecuteReader(this IDbConnection conn, SqlCommandText commandText, SqlCommandType commandType, Action <IDataReader, int> dataMapper, params SqlParameter[] sqlParameters)
=>
Using(new SqlCommand(), cmd =>
{
int resultSet = 0;
cmd.Connection = (SqlConnection)conn;
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.CommandTimeout = 5000;
if (sqlParameters != null)
{
foreach (SqlParameter parm in sqlParameters)
{
if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output) && parm.Value == null)
{
parm.Value = DBNull.Value;
}
cmd.Parameters.Add(parm);
}
}
IDataReader reader = cmd.ExecuteReader();
while (true)
{
while (reader.Read())
{
dataMapper?.Invoke(reader, resultSet);
}
resultSet++;
if (reader.IsClosed || !reader.NextResult())
{
break;
}
}
return(resultSet);
});
private IEnumerable <TemperatureLogRecord> getTemperatureChartData(SqlCommandText whereClause)
{
var data = new List <TemperatureLogRecord>();
var selectCommand = new SqlCommandInfo("SELECT origin_datetime, temperature FROM TemperatureLog");
if (!whereClause.IsEmpty)
{
selectCommand.CommandText += " WHERE " + whereClause.CommandText;
selectCommand.AddParameters(whereClause);
}
SqlExecutionHelper.ExecuteReader(
selectCommand,
row =>
{
DateTime datetime = row.GetDateTime(row.GetOrdinal("origin_datetime"));
decimal temperature = row.GetDecimal(row.GetOrdinal("temperature"));
data.Add(new TemperatureLogRecord(datetime, temperature));
});
return(data.ToArray());
}
public SqlEventProcessorPositionStore(SqlConnectionStringBuilder builder, string schema)
{
_builder = builder ?? throw new ArgumentNullException(nameof(builder));
_text = new SqlCommandText(schema);
}
public SqlBlobClient(SqlConnectionStringBuilder builder, string schema)
{
_builder = builder ?? throw new ArgumentNullException(nameof(builder));
_text = new SqlCommandText(schema);
}
internal IEnumerable <TemperatureLogRecord> GetTemperatureChartData(TimeSpan period)
{
SqlCommandText whereClause = getConditionByPeriod("origin_datetime", period);
return(getTemperatureChartData(whereClause));
}
internal IEnumerable <DeviceStateLogRecord> GetDeviceStateLog(Devices[] devices)
{
SqlCommandText whereClause = getConditionToDeviceStateLogByDevices(devices);
return(getDeviceStateLog(whereClause));
}
public static int ExecuteNonQuery(this IDbConnection conn, SqlCommandType commandType, SqlCommandText commandText, Action <IDbDataParameter[]> returnParams = null, IDbDataParameter[] sqlParameters = null)
=>
Using(new SqlCommand(), cmd =>
{
cmd.Connection = (SqlConnection)conn;
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.CommandTimeout = 5000;
if (sqlParameters != null)
{
foreach (SqlParameter parm in sqlParameters)
{
if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output) && parm.Value == null)
{
parm.Value = DBNull.Value;
}
cmd.Parameters.Add(parm);
}
}
int result = cmd.ExecuteNonQuery();
returnParams?.Invoke(sqlParameters);
return(result);
});
