public static UserUsageSummary GetUserUsage(string alias, UsagePeriod period) { SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try { connection = new SqlConnection(ServerSettings.SpeciesDsn); connection.Open(); command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT SUM(UsageMinutes) AS UsageMinutes FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)"; command.Parameters.AddWithValue("@Alias", alias); DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; GetPeriodDates(period, ref startDate, ref endDate); command.Parameters.AddWithValue("@StartDate", startDate.ToString()); command.Parameters.AddWithValue("@EndDate", endDate.ToString()); reader = command.ExecuteReader(); UserUsageSummary summary = new UserUsageSummary(); summary.Alias = alias; summary.Period = period; if (reader.Read()) { if (!((reader["UsageMinutes"]) is DBNull)) { summary.TotalHours = Convert.ToInt32(reader["UsageMinutes"]) / 60; } } TimeSpan span = endDate - startDate; if (span.Days == 0) { span = new TimeSpan(1, 0, 0, 0); } summary.AverageHours = (float)summary.TotalHours / (float)span.Days; return summary; } finally { if (reader != null) { reader.Close(); reader = null; } if (command != null) { command.Dispose(); command = null; } if (connection != null) { connection.Close(); connection = null; } } }
public static ReadOnlyCollection<UserUsageSummary> GetUserSummaries(UsagePeriod period) { SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try { string cacheKey = "GetUserSummaries(" + period.ToString() + ")"; if (HttpContext.Current.Cache[cacheKey] != null) { return new ReadOnlyCollection<UserUsageSummary>((List<UserUsageSummary>)HttpContext.Current.Cache[cacheKey]); } else { List<UserUsageSummary> userList = new List<UserUsageSummary>(); connection = new SqlConnection(ServerSettings.SpeciesDsn); connection.Open(); command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT TOP 10 Alias, SUM(UsageMinutes) AS UsageTotal FROM Usage WHERE TickTime >= @StartDate AND TickTime <= @EndDate GROUP BY Alias ORDER BY UsageTotal DESC"; DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; GetPeriodDates(period, ref startDate, ref endDate); command.Parameters.AddWithValue("@StartDate", startDate.ToString()); command.Parameters.AddWithValue("@EndDate", endDate.ToString()); reader = command.ExecuteReader(); while (reader.Read()) { UserUsageSummary summary = new UserUsageSummary(); summary.Alias = Convert.ToString(reader["Alias"]); summary.Period = period; summary.TotalHours = Convert.ToInt32(reader["UsageTotal"]) / 60; userList.Add(summary); } HttpContext.Current.Cache.Add(cacheKey, userList, null, DateTime.Now.AddMinutes(59), TimeSpan.Zero, CacheItemPriority.Normal, null); return new ReadOnlyCollection<UserUsageSummary>(userList); } } finally { if (reader != null) { reader.Close(); reader = null; } if (command != null) { command.Dispose(); command = null; } if (connection != null) { connection.Close(); connection = null; } } }
public static ReadOnlyCollection<UsageData> GetUserDetails(string alias, UsagePeriod period) { SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try { connection = new SqlConnection(ServerSettings.SpeciesDsn); connection.Open(); command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT * FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)"; command.Parameters.AddWithValue("@Alias", alias); DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; GetPeriodDates(period, ref startDate, ref endDate); command.Parameters.AddWithValue("@StartDate", startDate.ToString()); command.Parameters.AddWithValue("@EndDate", endDate.ToString()); reader = command.ExecuteReader(); UserUsageSummary summary = new UserUsageSummary(); summary.Alias = alias; summary.Period = period; List<UsageData> usageList = new List<UsageData>(); while (reader.Read()) { UsageData data = new UsageData(); data.Alias = Convert.ToString(reader["Alias"]); data.Domain = Convert.ToString(reader["Domain"]); data.TickTime = Convert.ToDateTime(reader["TickTime"]); data.UsageMinutes = Convert.ToInt32(reader["UsageMinutes"]); data.IPAddress = Convert.ToString(reader["IPAddress"]); data.GameVersion = Convert.ToString(reader["GameVersion"]); data.PeerChannel = Convert.ToString(reader["PeerChannel"]); data.PeerCount = Convert.ToInt32(reader["PeerCount"]); data.AnimalCount = Convert.ToInt32(reader["AnimalCount"]); data.MaxAnimalCount = Convert.ToInt32(reader["MaxAnimalCount"]); data.WorldHeight = Convert.ToInt32(reader["WorldHeight"]); data.WorldWidth = Convert.ToInt32(reader["WorldWidth"]); data.MachineName = Convert.ToString(reader["MachineName"]); data.OSVersion = Convert.ToString(reader["OSVersion"]); data.ProcessorCount = Convert.ToInt32(reader["ProcessorCount"]); data.ClrVersion = Convert.ToString(reader["ClrVersion"]); data.WorkingSet = Convert.ToInt32(reader["WorkingSet"]); data.MaxWorkingSet = Convert.ToInt32(reader["MaxWorkingSet"]); data.MinWorkingSet = Convert.ToInt32(reader["MinWorkingSet"]); data.ProcessorTimeInSeconds = Convert.ToInt32(reader["ProcessorTime"]); data.ProcessStartTime = Convert.ToDateTime(reader["ProcessStartTime"]); usageList.Add(data); } return new ReadOnlyCollection<UsageData>(usageList); } finally { if (reader != null) { reader.Close(); reader = null; } if (command != null) { command.Dispose(); command = null; } if (connection != null) { connection.Close(); connection = null; } } }
public static ReadOnlyCollection <UserUsageSummary> GetUserSummaries(UsagePeriod period) { SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try { string cacheKey = "GetUserSummaries(" + period + ")"; if (HttpContext.Current.Cache[cacheKey] != null) { return (new ReadOnlyCollection <UserUsageSummary>( (List <UserUsageSummary>)HttpContext.Current.Cache[cacheKey])); } else { List <UserUsageSummary> userList = new List <UserUsageSummary>(); connection = new SqlConnection(ServerSettings.SpeciesDsn); connection.Open(); command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT TOP 10 Alias, SUM(UsageMinutes) AS UsageTotal FROM Usage WHERE TickTime >= @StartDate AND TickTime <= @EndDate GROUP BY Alias ORDER BY UsageTotal DESC"; DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; GetPeriodDates(period, ref startDate, ref endDate); command.Parameters.AddWithValue("@StartDate", startDate.ToString()); command.Parameters.AddWithValue("@EndDate", endDate.ToString()); reader = command.ExecuteReader(); while (reader.Read()) { UserUsageSummary summary = new UserUsageSummary(); summary.Alias = Convert.ToString(reader["Alias"]); summary.Period = period; summary.TotalHours = Convert.ToInt32(reader["UsageTotal"]) / 60; userList.Add(summary); } HttpContext.Current.Cache.Add(cacheKey, userList, null, DateTime.Now.AddMinutes(59), TimeSpan.Zero, CacheItemPriority.Normal, null); return(new ReadOnlyCollection <UserUsageSummary>(userList)); } } finally { if (reader != null) { reader.Close(); reader = null; } if (command != null) { command.Dispose(); command = null; } if (connection != null) { connection.Close(); connection = null; } } }
public static ReadOnlyCollection <UsageData> GetUserDetails(string alias, UsagePeriod period) { SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try { connection = new SqlConnection(ServerSettings.SpeciesDsn); connection.Open(); command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT * FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)"; command.Parameters.AddWithValue("@Alias", alias); DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; GetPeriodDates(period, ref startDate, ref endDate); command.Parameters.AddWithValue("@StartDate", startDate.ToString()); command.Parameters.AddWithValue("@EndDate", endDate.ToString()); reader = command.ExecuteReader(); UserUsageSummary summary = new UserUsageSummary(); summary.Alias = alias; summary.Period = period; List <UsageData> usageList = new List <UsageData>(); while (reader.Read()) { UsageData data = new UsageData(); data.Alias = Convert.ToString(reader["Alias"]); data.Domain = Convert.ToString(reader["Domain"]); data.TickTime = Convert.ToDateTime(reader["TickTime"]); data.UsageMinutes = Convert.ToInt32(reader["UsageMinutes"]); data.IPAddress = Convert.ToString(reader["IPAddress"]); data.GameVersion = Convert.ToString(reader["GameVersion"]); data.PeerChannel = Convert.ToString(reader["PeerChannel"]); data.PeerCount = Convert.ToInt32(reader["PeerCount"]); data.AnimalCount = Convert.ToInt32(reader["AnimalCount"]); data.MaxAnimalCount = Convert.ToInt32(reader["MaxAnimalCount"]); data.WorldHeight = Convert.ToInt32(reader["WorldHeight"]); data.WorldWidth = Convert.ToInt32(reader["WorldWidth"]); data.MachineName = Convert.ToString(reader["MachineName"]); data.OSVersion = Convert.ToString(reader["OSVersion"]); data.ProcessorCount = Convert.ToInt32(reader["ProcessorCount"]); data.ClrVersion = Convert.ToString(reader["ClrVersion"]); data.WorkingSet = Convert.ToInt32(reader["WorkingSet"]); data.MaxWorkingSet = Convert.ToInt32(reader["MaxWorkingSet"]); data.MinWorkingSet = Convert.ToInt32(reader["MinWorkingSet"]); data.ProcessorTimeInSeconds = Convert.ToInt32(reader["ProcessorTime"]); data.ProcessStartTime = Convert.ToDateTime(reader["ProcessStartTime"]); usageList.Add(data); } return(new ReadOnlyCollection <UsageData>(usageList)); } finally { if (reader != null) { reader.Close(); reader = null; } if (command != null) { command.Dispose(); command = null; } if (connection != null) { connection.Close(); connection = null; } } }
public static UserUsageSummary GetUserUsage(string alias, UsagePeriod period) { SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try { connection = new SqlConnection(ServerSettings.SpeciesDsn); connection.Open(); command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT SUM(UsageMinutes) AS UsageMinutes FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)"; command.Parameters.AddWithValue("@Alias", alias); DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; GetPeriodDates(period, ref startDate, ref endDate); command.Parameters.AddWithValue("@StartDate", startDate.ToString()); command.Parameters.AddWithValue("@EndDate", endDate.ToString()); reader = command.ExecuteReader(); UserUsageSummary summary = new UserUsageSummary(); summary.Alias = alias; summary.Period = period; if (reader.Read()) { if (!((reader["UsageMinutes"]) is DBNull)) { summary.TotalHours = Convert.ToInt32(reader["UsageMinutes"]) / 60; } } TimeSpan span = endDate - startDate; if (span.Days == 0) { span = new TimeSpan(1, 0, 0, 0); } summary.AverageHours = summary.TotalHours / (float)span.Days; return(summary); } finally { if (reader != null) { reader.Close(); reader = null; } if (command != null) { command.Dispose(); command = null; } if (connection != null) { connection.Close(); connection = null; } } }