public int UndoUpdate(LogMySqlDataModel LogToUpdate) { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { string sqlFormattedTimeStamp = LogToUpdate.DateTimeStamp.ToString("yyyy-MM-dd HH:mm:ss"); PetaPoco.Sql sql = new PetaPoco.Sql(); string nullString = "null"; sql.Append("SET SQL_SAFE_UPDATES = 0; "); sql.Append(string.Format("UPDATE logmysql SET Important = {0} ", nullString)); string where = string.Format("WHERE DateTimeStamp = \"{0}\" AND logmysql.Function = \"{1}\" AND Message = \"{2}\" AND Type = \"{3}\"; ", sqlFormattedTimeStamp, LogToUpdate.Function, LogToUpdate.Message, LogToUpdate.Type); sql.Append(where); sql.Append("SET SQL_SAFE_UPDATES = 1; "); db.Execute(sql); return(1); } } catch (Exception ex) { string errorMessage = ex.Message; return(0); } finally { } }
public int UndoUpdate(CombinedLogDataModel LogToUpdate) { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { string sqlFormattedTimeStamp = LogToUpdate.TimeStamp.ToString("yyyy-MM-dd HH:mm:ss"); PetaPoco.Sql sql = new PetaPoco.Sql(); string nullString = "null"; sql.Append("SET SQL_SAFE_UPDATES = 0; "); sql.Append(string.Format("UPDATE log SET Important = {0} ", nullString)); string where = string.Format("WHERE TimeStamp = \"{0}\" AND LogID = GuidToBinary(\"{1}\") AND Level = \"{2}\" AND Source = \"{3}\" ; ", sqlFormattedTimeStamp, LogToUpdate.LogID, LogToUpdate.Level, LogToUpdate.Source); sql.Append(where); sql.Append("SET SQL_SAFE_UPDATES = 1; "); db.Execute(sql); return(1); } } catch (Exception ex) { string errorMessage = ex.Message; return(0); } finally { } }
public ExampleDataModel Insert(ExampleDataModel dataModel) // always do insert and update together. Check if it already exists, if so update, if not insert { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { ExampleDataModel returnModel = GetFromID(dataModel.ExampleID); ExamplePoco poco = dataModel.ToPoco(); if (returnModel != null) { db.Update(poco); } else { poco.ExampleID = GuidMapper.Map(Guid.NewGuid()); poco.DateAdded = DateTime.Now; db.Insert(poco); } } } catch (Exception ex) { string errorMessage = ex.Message; } finally { } return(null); }
public int Update(CombinedLogControllerActionDataModel LogToUpdate, string username) { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { Byte[] bytes = new Byte[16]; Guid allZeros = new Guid(bytes); Guid UserID = GetUserIDFromUsername(username); if (UserID != allZeros) { if (username.Contains("@")) //format email { string[] sections = username.Split(new[] { '@' }); sections[1] = sections[1].Insert(0, "@@"); username = string.Join("", sections); } string sqlFormattedTimeStamp = LogToUpdate.TimeStamp.ToString("yyyy-MM-dd HH:mm:ss"); PetaPoco.Sql sql = new PetaPoco.Sql(); username = string.Format("\"{0}\"", username); sql.Append("SET SQL_SAFE_UPDATES = 0; "); sql.Append(string.Format("UPDATE logcontrolleraction SET Important = {0} ", username)); string where = string.Format("WHERE TimeStamp = \"{0}\" AND WebRequestID = GuidToBinary(\"{1}\") AND ControllerName = \"{2}\" AND ActionName = \"{3}\" AND UserID = GuidToBinary(\"{4}\"); ", sqlFormattedTimeStamp, LogToUpdate.WebRequestID, LogToUpdate.ControllerName, LogToUpdate.ActionName, LogToUpdate.UserID); sql.Append(where); sql.Append("SET SQL_SAFE_UPDATES = 1; "); db.Execute(sql); return(1); } else { return(0); } } } catch (Exception ex) { string errorMessage = ex.Message; } finally { } return(0); }
public int Update(LogMySqlDataModel LogToUpdate, string username) { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { Byte[] bytes = new Byte[16]; Guid allZeros = new Guid(bytes); Guid UserID = GetUserIDFromUsername(username); //checking if username is in User table if (UserID != allZeros) { if (username.Contains("@")) //format email { string[] sections = username.Split(new[] { '@' }); sections[1] = sections[1].Insert(0, "@@"); username = string.Join("", sections); } string sqlFormattedTimeStamp = LogToUpdate.DateTimeStamp.ToString("yyyy-MM-dd HH:mm:ss"); PetaPoco.Sql sql = new PetaPoco.Sql(); username = string.Format("\"{0}\"", username); sql.Append("SET SQL_SAFE_UPDATES = 0; "); sql.Append(string.Format("UPDATE logmysql SET Important = {0} ", username)); string where = string.Format("WHERE DateTimeStamp = \"{0}\" AND logmysql.Function = \"{1}\" AND Message = \"{2}\" AND Type = \"{3}\"; ", sqlFormattedTimeStamp, LogToUpdate.Function, LogToUpdate.Message, LogToUpdate.Type); sql.Append(where); sql.Append("SET SQL_SAFE_UPDATES = 1; "); db.Execute(sql); return(1); } else { return(0); } } } catch (Exception ex) { string errorMessage = ex.Message; } finally { } return(0); }
public IEnumerable <ExampleDataModel> GetAll() { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { return(db.Fetch <ExamplePoco>(ExamplePoco.SelectAll).Select(S => S.ToModel())); } } catch (Exception ex) { string errorMessage = ex.Message; } finally { } return(null); }
//public ExampleRepository(string connectionStringName) // : base(typeof(ExampleRepository), connectionStringName) //{ } public ExampleDataModel GetFromID(Guid ExampleID) { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { return(db.FirstOrDefault <ExamplePoco>(ExamplePoco.SelectByIDSQL, GuidMapper.Map(ExampleID)).ToModel()); } } catch (Exception ex) { string errorMessage = ex.Message; } finally { } return(null); }
public Guid GetUserIDFromUsername(string username) { try { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { PetaPoco.Sql sql = new PetaPoco.Sql(); sql.Append("SELECT UserID"); sql.Append("from user"); if (username.Contains("@")) //format email { string[] sections = username.Split(new[] { '@' }); sections[1] = sections[1].Insert(0, "@@"); username = string.Join("", sections); } username = string.Format("\"{0}\" ", username); sql.Append(string.Format("WHERE EmailAddress = {0} ", username)); sql.Append(";"); CombinedLogExceptionPoco poco = db.FirstOrDefault<CombinedLogExceptionPoco>(sql); CombinedLogExceptionDataModel model = poco.ToModel(); if (model != null) { return model.UserID; } else { Byte[] allZeroByte = new Byte[16]; return new Guid(allZeroByte); } } } catch (Exception ex) { string errorMessage = ex.Message; } finally { } Byte[] bytes = new Byte[16]; return new Guid(bytes); }
public PagedDataModelCollection <CombinedLogDataModel> CombinedPageLog(int pageSize, int pageNumber, string sortColumn, string sortDirection, Dictionary <string, string> searchDictionary) { using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName)) { try { PetaPoco.Sql sql = new PetaPoco.Sql(); sql.Append("SELECT "); sql.Append("user.EmailAddress, log.UserID, log.TimeStamp, log.LogID, log.Level, log.Message, log.Source, log.Important "); sql.Append("from log "); sql.Append(" INNER JOIN user on user.userID = log.userID "); //a boolean to keep track of whether sql string is at first search clause bool FirstClause = true; string dateString = ""; foreach (KeyValuePair <string, string> entry in searchDictionary) { string userInput = entry.Value; if (!string.IsNullOrWhiteSpace(userInput)) { if (entry.Key == "Important") //only get logs that are marked as important { if (!FirstClause) { sql.Append(string.Format("AND Important != \'\'")); } else { sql.Append(string.Format("WHERE Important != \'\'")); } FirstClause = false; } else if (entry.Key == "TimeStamp") //get logs from one specific date { if (userInput[0] != '\'') { userInput = string.Format("\'{0}\'", userInput); } if (!FirstClause) { sql.Append(string.Format("AND DATE(TimeStamp) = {0} ", userInput)); } else { sql.Append(string.Format("WHERE DATE(TimeStamp) = {0} ", userInput)); } FirstClause = false; } else if (entry.Key == "beginDate") //format date at beginning of range { if (userInput[0] != '\'') { userInput = string.Format("\'{0}\'", userInput); } dateString = string.Format("DATE(TimeStamp) BETWEEN {0} AND ", userInput); } else if (entry.Key == "endDate" && dateString != "") //format date at end of range { if (userInput[0] != '\'') { userInput = string.Format("\'{0}\'", userInput); } if (!FirstClause) { sql.Append(string.Format("AND {0} {1} ", dateString, userInput)); } else { sql.Append(string.Format("WHERE {0} {1} ", dateString, userInput)); } FirstClause = false; } else { if (userInput.Contains("@")) //format email { string[] sections = userInput.Split(new[] { '@' }); sections[1] = sections[1].Insert(0, "@@"); userInput = string.Join("", sections); } string newKey = entry.Key; //formatting for "get logs marked as important by a specific username" if (entry.Key == "UserImportant") { newKey = "Important"; } if (userInput[0] != '\'') { userInput = string.Format("\'%{0}%\'", userInput); } if (!FirstClause) { sql.Append(string.Format("AND {0} LIKE {1} ", newKey, userInput)); } else { sql.Append(string.Format("WHERE {0} LIKE {1} ", newKey, userInput)); } FirstClause = false; } } } sql.Append(string.Format("ORDER BY {0} {1}", sortColumn, sortDirection)); PetaPoco.Page <CombinedLogPoco> page = db.Page <CombinedLogPoco>(pageNumber, pageSize, sql); if (page == null) { return(null); } return(new PagedDataModelCollection <CombinedLogDataModel>() { Items = page.Items.Select(s => s.ToModel()), PageNumber = pageNumber, PageSize = pageSize, TotalItems = page.TotalItems, TotalPages = page.TotalPages, SortBy = sortColumn, SortDirection = sortDirection, SearchDictionary = searchDictionary, }); } catch (Exception ex) { string errorMessage = ex.Message; } finally { } } return(null); }