/// <summary>
/// Executes a select command that returns a collection of records.
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
private List <LogSession> PerformSelect(SqlPreparedCommand command)
{
var result = new List <LogSession>();
using (IDataReader reader = command.Command.ExecuteReader()) {
while (reader.Read())
{
var session = new LogSession()
{
Id = Sql.GetInt64(reader, "Id"),
ClientId = Sql.GetInt64(reader, "ClientId"),
StartTime = Sql.GetDateTime(reader, "StartTime"),
EndTime = Sql.GetDateTime(reader, "EndTime"),
CountRequests = Sql.GetInt64(reader, "CountRequests"),
OtherBytesSent = Sql.GetInt64(reader, "OtherBytesSent"),
HtmlBytesSent = Sql.GetInt64(reader, "HtmlBytesSent"),
JsonBytesSent = Sql.GetInt64(reader, "JsonBytesSent"),
ImageBytesSent = Sql.GetInt64(reader, "ImageBytesSent"),
AudioBytesSent = Sql.GetInt64(reader, "AudioBytesSent"),
};
result.Add(session);
}
}
return(result);
}
/// <summary>
/// Adds a new record to the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="client"></param>
public void Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogClient client)
{
SqlPreparedCommand command = PrepareInsert(connection, transaction, "Insert",
"Id",
"IpAddress", "ReverseDns", "ReverseDnsDate");
client.Id = Sql.ExecuteInsert(command, log, client.IpAddress, client.ReverseDns, client.ReverseDnsDate);
}
/// <summary>
/// Deletes a record from the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="client"></param>
public void Delete(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogClient client)
{
SqlPreparedCommand command = PrepareCommand(connection, transaction, "Delete",
String.Format("DELETE FROM [{0}] WHERE [Id] = ?", TableName), 1);
Sql.SetParameters(command, client.Id);
Sql.LogCommand(log, command.Command);
command.Command.ExecuteNonQuery();
}
/// <summary>
/// Updates an existing record on the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="client"></param>
public void Update(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogClient client)
{
SqlPreparedCommand command = PrepareCommand(connection, transaction, "Update",
String.Format("UPDATE [{0}] SET [IpAddress] = ?, [ReverseDns] = ?, [ReverseDnsDate] = ?" +
" WHERE [Id] = ?", TableName), 4);
Sql.SetParameters(command, client.IpAddress, client.ReverseDns, client.ReverseDnsDate, client.Id);
Sql.LogCommand(log, command.Command);
command.Command.ExecuteNonQuery();
}
/// <summary>
/// Adds a new record to the table.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="session"></param>
public void Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogSession session)
{
SqlPreparedCommand command = PrepareInsert(connection, transaction, "Insert",
"Id",
"ClientId", "StartTime", "EndTime", "CountRequests", "OtherBytesSent",
"HtmlBytesSent", "JsonBytesSent", "ImageBytesSent", "AudioBytesSent");
session.Id = Sql.ExecuteInsert(command, log,
session.ClientId, session.StartTime, session.EndTime, session.CountRequests, session.OtherBytesSent,
session.HtmlBytesSent, session.JsonBytesSent, session.ImageBytesSent, session.AudioBytesSent);
}
/// <summary>
/// Fetches a collection of every record from the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <returns></returns>
public List <LogSession> SelectAll(IDbConnection connection, IDbTransaction transaction, TextWriter log)
{
SqlPreparedCommand command = PrepareCommand(connection, transaction, "SelectAll",
String.Format("SELECT {0}" +
" FROM [{1}]",
AllFields, TableName), 0);
Sql.LogCommand(log, command.Command);
return(PerformSelect(command));
}
/// <summary>
/// Executes the single record fetch command passed across and returns the client record arising from it,
/// or null if no client matches the criteria.
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
private LogClient PerformSelect(SqlPreparedCommand command)
{
LogClient result = null;
using (IDataReader reader = command.Command.ExecuteReader()) {
if (reader.Read())
{
result = DecodeReader(reader);
}
}
return(result);
}
/// <summary>
/// Fetches a client record from the database for the IP address passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="ipAddress"></param>
/// <returns></returns>
public LogClient SelectByIpAddress(IDbConnection connection, IDbTransaction transaction, TextWriter log, string ipAddress)
{
SqlPreparedCommand command = PrepareCommand(connection, transaction, "SelectByIpAddress",
String.Format("SELECT [Id], [IpAddress], [ReverseDns], [ReverseDnsDate]" +
" FROM [{0}]" +
" WHERE [IpAddress] = ?",
TableName), 1);
Sql.SetParameters(command, ipAddress);
Sql.LogCommand(log, command.Command);
return(PerformSelect(command));
}
/// <summary>
/// Updates an existing record.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="session"></param>
public void Update(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogSession session)
{
SqlPreparedCommand command = PrepareCommand(connection, transaction, "Update",
String.Format("UPDATE [{0}] SET" +
" [ClientId] = ?, [StartTime] = ?, [EndTime] = ?, [CountRequests] = ?, " +
" [OtherBytesSent] = ?, [HtmlBytesSent] = ?, [JsonBytesSent] = ?, " +
" [ImageBytesSent] = ?, [AudioBytesSent] = ?" +
" WHERE [Id] = ?", TableName), 10);
Sql.SetParameters(command, session.ClientId, session.StartTime, session.EndTime, session.CountRequests,
session.OtherBytesSent, session.HtmlBytesSent, session.JsonBytesSent,
session.ImageBytesSent, session.AudioBytesSent,
session.Id);
Sql.LogCommand(log, command.Command);
command.Command.ExecuteNonQuery();
}
/// <summary>
/// Fetches a collection of all clients on the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <returns></returns>
public List <LogClient> SelectAll(IDbConnection connection, IDbTransaction transaction, TextWriter log)
{
List <LogClient> result = new List <LogClient>();
SqlPreparedCommand command = PrepareCommand(connection, transaction, "SelectAll",
String.Format("SELECT [Id], [IpAddress], [ReverseDns], [ReverseDnsDate]" +
" FROM [{0}]",
TableName), 0);
Sql.LogCommand(log, command.Command);
using (IDataReader reader = command.Command.ExecuteReader()) {
while (reader.Read())
{
result.Add(DecodeReader(reader));
}
}
return(result);
}
/// <summary>
/// Fetches a collection of records from the database whose start or end time was within the two times passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="earliest"></param>
/// <param name="latest"></param>
/// <returns></returns>
public List <LogSession> SelectByStartDate(IDbConnection connection, IDbTransaction transaction, TextWriter log, DateTime earliest, DateTime latest)
{
earliest = earliest.ToUniversalTime();
latest = latest.ToUniversalTime();
SqlPreparedCommand command = PrepareCommand(connection, transaction, "SelectByStartDate",
String.Format("SELECT {0}" +
" FROM [{1}]" +
" WHERE (([StartTime] >= ? AND [StartTime] <= ?)" +
" OR ([EndTime] >= ? AND [EndTime] <= ?)" +
" OR ([StartTime] < ? AND [EndTime] > ?)" +
")",
AllFields, TableName), 6);
Sql.SetParameters(command, earliest, latest, earliest, latest, earliest, latest);
Sql.LogCommand(log, command.Command);
return(PerformSelect(command));
}
/// <summary>
/// Executes the single record fetch command passed across and returns the client record arising from it,
/// or null if no client matches the criteria.
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
private LogClient PerformSelect(SqlPreparedCommand command)
{
LogClient result = null;
using(IDataReader reader = command.Command.ExecuteReader()) {
if(reader.Read()) result = DecodeReader(reader);
}
return result;
}
/// <summary>
/// Executes a select command that returns a collection of records.
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
private List<LogSession> PerformSelect(SqlPreparedCommand command)
{
var result = new List<LogSession>();
using(IDataReader reader = command.Command.ExecuteReader()) {
while(reader.Read()) {
var session = new LogSession() {
Id = Sql.GetInt64(reader, "Id"),
ClientId = Sql.GetInt64(reader, "ClientId"),
StartTime = Sql.GetDateTime(reader, "StartTime"),
EndTime = Sql.GetDateTime(reader, "EndTime"),
CountRequests = Sql.GetInt64(reader, "CountRequests"),
OtherBytesSent = Sql.GetInt64(reader, "OtherBytesSent"),
HtmlBytesSent = Sql.GetInt64(reader, "HtmlBytesSent"),
JsonBytesSent = Sql.GetInt64(reader, "JsonBytesSent"),
ImageBytesSent = Sql.GetInt64(reader, "ImageBytesSent"),
AudioBytesSent = Sql.GetInt64(reader, "AudioBytesSent"),
};
result.Add(session);
}
}
return result;
}
