public StoreMessageInSqlServer(UsageDataMessage msg, CollectorRepository repo) { message = msg; repository = repo; }
static UsageDataMessage FetchDataForUpload(SQLiteConnection connection, bool fetchIncompleteSessions) { UsageDataMessage message = new UsageDataMessage(); // Retrieve the User ID using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT value FROM Properties WHERE name = 'userID';"; string userID = (string)cmd.ExecuteScalar(); message.UserID = new Guid(userID); } // Retrieve the list of sessions using (SQLiteCommand cmd = connection.CreateCommand()) { if (fetchIncompleteSessions) { cmd.CommandText = @"SELECT id, startTime, endTime FROM Sessions;"; } else { // Fetch all sessions which are either closed or inactive for more than 24 hours cmd.CommandText = @"SELECT id, startTime, endTime FROM Sessions WHERE (endTime IS NOT NULL) OR (ifnull((SELECT max(time) FROM FeatureUses WHERE FeatureUses.session = Sessions.id), Sessions.startTime) < datetime('now','-1 day'));"; } using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { UsageDataSession session = new UsageDataSession(); session.SessionID = reader.GetInt64(0); session.StartTime = reader.GetDateTime(1); if (!reader.IsDBNull(2)) session.EndTime = reader.GetDateTime(2); message.Sessions.Add(session); } } } string commaSeparatedSessionIDList = GetCommaSeparatedIDList(message.Sessions); StringInterner stringInterning = new StringInterner(); // Retrieve the environment using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT session, name, value FROM Environment WHERE session IN (" + commaSeparatedSessionIDList + ");"; using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { long sessionID = reader.GetInt64(0); UsageDataSession session = message.FindSession(sessionID); session.EnvironmentProperties.Add( new UsageDataEnvironmentProperty { Name = stringInterning.Intern(reader.GetString(1)), Value = stringInterning.Intern(reader.GetString(2)) }); } } } // Retrieve the feature uses using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT session, time, endTime, feature, activationMethod FROM FeatureUses WHERE session IN (" + commaSeparatedSessionIDList + ");"; using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { long sessionID = reader.GetInt64(0); UsageDataSession session = message.FindSession(sessionID); UsageDataFeatureUse featureUse = new UsageDataFeatureUse(); featureUse.Time = reader.GetDateTime(1); if (!reader.IsDBNull(2)) featureUse.EndTime = reader.GetDateTime(2); featureUse.FeatureName = stringInterning.Intern(reader.GetString(3)); if (!reader.IsDBNull(4)) featureUse.ActivationMethod = stringInterning.Intern(reader.GetString(4)); session.FeatureUses.Add(featureUse); } } } // Retrieve the exceptions using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT session, time, type, stackTrace FROM Exceptions WHERE session IN (" + commaSeparatedSessionIDList + ");"; using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { long sessionID = reader.GetInt64(0); UsageDataSession session = message.FindSession(sessionID); UsageDataException exception = new UsageDataException(); exception.Time = reader.GetDateTime(1); exception.ExceptionType = stringInterning.Intern(reader.GetString(2)); if (!reader.IsDBNull(3)) exception.StackTrace = stringInterning.Intern(reader.GetString(3)); session.Exceptions.Add(exception); } } } return message; }
private void SaveMessage(string filename, UsageDataMessage message) { DateTime fileDate = File.GetLastWriteTimeUtc(filename); using (FileStream fs = File.Open(filename, FileMode.Create, FileAccess.Write, FileShare.None)) { using (GZipStream zip = new GZipStream(fs, CompressionMode.Compress)) { DataContractSerializer serializer = new DataContractSerializer(typeof(UsageDataMessage)); serializer.WriteObject(zip, message); } } File.SetLastAccessTimeUtc(filename, fileDate); }