public static DataTable ExecuteQuery(LoginUser loginUser, SqlCommand command) { DataTable result = new DataTable(); BaseCollection.FixCommandParameters(command); using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(result); } transaction.Commit(); } catch (Exception e) { transaction.Rollback(); e.Data["CommandText"] = command.CommandText; ExceptionLogs.LogException(loginUser, e, "SqlExecutor.Fill"); throw; } connection.Close(); } return(result); }
public virtual void ExecuteNonQuery(SqlCommand command, string tableName) { FixCommandParameters(command); using (SqlConnection connection = new SqlConnection(_loginUser.ConnectionString)) { //SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot); connection.Open(); command.Connection = connection; //command.Transaction = transaction; try { command.ExecuteNonQuery(); //transaction.Commit(); } catch (Exception e) { //transaction.Rollback(); e.Data["CommandText"] = command.CommandText; ExceptionLogs.LogException(LoginUser, e, "BaseCollection.ExecuteNonQuery"); throw; } connection.Close(); } if (DataCache != null) { DataCache.InvalidateItem(tableName, _loginUser.OrganizationID); } }
public static object ExecuteScalar(LoginUser loginUser, SqlCommand command) { BaseCollection.FixCommandParameters(command); using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString)) { object o; connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { o = command.ExecuteScalar(); transaction.Commit(); } catch (Exception e) { transaction.Rollback(); e.Data["CommandText"] = command.CommandText; ExceptionLogs.LogException(loginUser, e, "SqlExecutor.ExecuteScalar"); throw; } connection.Close(); if (o == null || o == DBNull.Value) { return(null); } return(o); } }
public virtual void Fill(SqlCommand command, string tableNames, bool includeSchema = true) { FixCommandParameters(command); if (_table != null) { _table.Dispose(); } if (_useCache && DataCache != null) { _table = DataCache.GetTable(command); if (_table != null) { return; } } _table = new DataTable(); using (SqlConnection connection = new SqlConnection(_loginUser.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { if (includeSchema) { adapter.FillSchema(_table, SchemaType.Source); } adapter.Fill(_table); } transaction.Commit(); } catch (Exception e) { transaction.Rollback(); e.Data["CommandText"] = command.CommandText; ExceptionLogs.LogException(LoginUser, e, "BaseCollection.Fill"); throw; } connection.Close(); } if (DataCache != null) { if (tableNames == "") { tableNames = TableName; } DataCache.AddTable(command, tableNames, _cacheExpirationSeconds, _table, _loginUser.OrganizationID); } }
public void UpdateCustomFieldsFromXml(string data) { StringReader reader = new StringReader(data); DataSet dataSet = new DataSet(); dataSet.ReadXml(reader); DataRow row = dataSet.Tables[0].Rows[0]; foreach (DataColumn column in dataSet.Tables[0].Columns) { foreach (CustomField field in CustomFields) { if (field.ApiFieldName.ToLower() == column.ColumnName.ToLower()) { bool isValid = false; try { isValid = IsValid(row[column], field.FieldType, field.IsRequired); } catch (Exception ex) { isValid = true; //if the validation check threw an exception then set the value anyway, basically what was done before, and log the exception string columnName = string.Empty; string value = string.Empty; if (column != null) { columnName = column.ColumnName; if (row != null) { value = row[column].ToString(); } } ExceptionLogs.LogException(_baseCollection.LoginUser, ex, "BaseCollection.UpdateCustomFieldsFromXml", string.Format("Validating {0} with value {1}", string.IsNullOrEmpty(columnName) ? "a field" : columnName, string.IsNullOrEmpty(value) ? "{ex.found}" : value)); } if (isValid) { field.SetValue(PrimaryKeyID, row[column]); } break; } } } }
private static void PostSyncData(string url, string json) { try { string result = ""; using (var client = new WebClient()) { client.Headers[HttpRequestHeader.ContentType] = "application/json"; result = client.UploadString(url, "POST", json); } // possible to do something with result } catch (Exception ex) { ExceptionLogs.LogException(LoginUser.Anonymous, ex, "TeamSupportSync", url + "-> " + json); } }
public static List <DataRow> GetWhiteListTicketPathsByHubID(int customerHubID) { List <DataRow> result = new List <DataRow>(); using (SqlConnection connection = new SqlConnection(LoginUser.Anonymous.ConnectionString)) { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = @"SELECT TicketID, CNameURL, PortalName FROM [Tickets] AS Tickets INNER JOIN [CustomerHubs] AS Hubs ON Tickets.OrganizationID = Hubs.OrganizationID INNER JOIN [CustomerHubFeatureSettings] AS HubFeatures ON Hubs.CustomerHubID = HubFeatures.CustomerHubID AND HubFeatures.EnableKnowledgeBase = 1 AND HubFeatures.EnableAnonymousProductAssociation = 0 AND HubFeatures.EnableCustomerSpecificKB = 0 LEFT JOIN Products AS Products ON Products.ProductFamilyID = Hubs.ProductFamilyID WHERE IsKnowledgeBase = 1 AND IsVisibleOnPortal = 1 AND Hubs.CustomerHubID = @CustomerHubID AND (Hubs.ProductFamilyID IS NULL OR Products.ProductFamilyID IS NOT NULL)" ; command.Parameters.AddWithValue("@CustomerHubID", customerHubID); connection.Open(); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { try { adapter.Fill(dt); result.AddRange(dt.AsEnumerable().Select(x => x).ToList()); } catch (Exception ex) { ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Deflector"); return(null); } } } return(result); }
public virtual object ExecuteScalar(SqlCommand command, string tableNames) { FixCommandParameters(command); using (SqlConnection connection = new SqlConnection(_loginUser.ConnectionString)) { object o; if (_useCache && DataCache != null) { o = DataCache.GetScalar(command); if (o != null) { return(o); } } connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { o = command.ExecuteScalar(); transaction.Commit(); } catch (Exception e) { transaction.Rollback(); e.Data["CommandText"] = command.CommandText; ExceptionLogs.LogException(LoginUser, e, "BaseCollection.ExecuteScalar"); throw; } connection.Close(); if (DataCache != null) { if (tableNames == "") { tableNames = TableName; } DataCache.AddScalar(command, tableNames, _cacheExpirationSeconds, o, _loginUser.OrganizationID); } return(o); } }
private void Debug(string message) { try { System.Collections.Specialized.NameValueCollection settings = System.Configuration.ConfigurationManager.AppSettings; string orgIdToDebug = settings["DebugOrgId"]; if (OrganizationId.ToString() == orgIdToDebug) { string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location); string debugFile = System.IO.Path.Combine(path, "Debug.txt"); System.IO.File.AppendAllText(debugFile, string.Format("{0} (orgId {1}): {2}{3}", DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"), orgIdToDebug, message, Environment.NewLine)); } } catch (Exception ex) { ExceptionLogs.LogException(LoginUser.Anonymous, ex, "ScheduledReports-Debug", "Tickets.Clone - Actions"); } }
private string SendAPIRequest(HttpWebRequest request) { string ResponseText = ""; try { using (WebResponse response = request.GetResponse()) { if (request.HaveResponse && response != null) { using (StreamReader reader = new StreamReader(response.GetResponseStream(), ASCIIEncoding.UTF8)) { ResponseText = reader.ReadToEnd(); } } } } catch (Exception ex) { ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Deflector"); } return(ResponseText); }
private async Task <string> SendAPIAsyncRequest(HttpWebRequest request) { string ResponseText = ""; try { using (WebResponse response = await request.GetResponseAsync().ConfigureAwait(false)) { if (request.HaveResponse && response != null) { using (StreamReader reader = new StreamReader(response.GetResponseStream(), ASCIIEncoding.UTF8)) { ResponseText = reader.ReadToEnd(); } } } } catch (Exception ex) { ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Deflector"); } return(ResponseText); }
public SlaLevel Clone(string cloneName = null) { int cloneSlaLevelId = 0; LoginUser loginUser = Collection.LoginUser; SlaLevels slaLevels = new SlaLevels(loginUser); SlaLevel clone = slaLevels.AddNewSlaLevel(); clone.OrganizationID = OrganizationID; if (string.IsNullOrEmpty(cloneName)) { clone.Name = Name + " (Clone)"; } else { clone.Name = cloneName; } clone.Collection.Save(); cloneSlaLevelId = clone.SlaLevelID; string actionLog = string.Format("{0} cloned SLA Level {1} into {2}.", loginUser.GetUserFullName(), this.Name, clone.Name); ActionLogs.AddActionLog(loginUser, ActionLogType.Insert, ReferenceType.Sla, cloneSlaLevelId, actionLog); //Clone SLA's triggers SlaTriggers clonedSlaTriggers = new SlaTriggers(loginUser); SlaTriggers originalSlaTriggers = new SlaTriggers(loginUser); try { originalSlaTriggers.LoadBySlaLevel(OrganizationID, SlaLevelID); foreach (SlaTrigger slaTrigger in originalSlaTriggers.OrderBy(o => o.SlaTriggerID).ToList()) { SlaTrigger clonedSlaTrigger = clonedSlaTriggers.AddNewSlaTrigger(); slaTrigger.ClonePropertiesTo(clonedSlaTrigger); clonedSlaTrigger.SlaLevelID = cloneSlaLevelId; } clonedSlaTriggers.BulkSave(); } catch (Exception ex) { actionLog = string.Format("Failed to clone sla {0} Triggers into {1}.", this.SlaLevelID, clone.SlaLevelID); ActionLogs.AddActionLog(loginUser, ActionLogType.Insert, ReferenceType.Sla, cloneSlaLevelId, actionLog); ExceptionLogs.LogException(loginUser, ex, "Cloning Sla Triggers", "SlaLevels.Clone - Triggers"); } //Clone SLA's Pause on specific days try { clonedSlaTriggers = new SlaTriggers(loginUser); clonedSlaTriggers.LoadBySlaLevel(OrganizationID, cloneSlaLevelId); foreach (SlaTrigger slaTrigger in originalSlaTriggers) { SlaPausedDays clonedSlaPausedDays = new SlaPausedDays(loginUser); SlaPausedDays originalSlaPausedDays = new SlaPausedDays(loginUser); originalSlaPausedDays.LoadByTriggerID(slaTrigger.SlaTriggerID); int newTriggerId = clonedSlaTriggers.Where(p => p.TicketTypeID == slaTrigger.TicketTypeID && p.TicketSeverityID == slaTrigger.TicketSeverityID && p.SlaLevelID == cloneSlaLevelId).First().SlaTriggerID; foreach (SlaPausedDay slaPausedDay in originalSlaPausedDays.OrderBy(o => o.DateToPause).ToList()) { SlaPausedDay clonedSlaPausedDay = clonedSlaPausedDays.AddNewSlaPausedDay(); clonedSlaPausedDay.SlaTriggerId = newTriggerId; clonedSlaPausedDay.DateToPause = slaPausedDay.DateToPauseUtc; } clonedSlaPausedDays.BulkSave(); } } catch (Exception ex) { actionLog = string.Format("Failed to clone sla {0} DaysToPause into {1}.", this.SlaLevelID, clone.SlaLevelID); ActionLogs.AddActionLog(loginUser, ActionLogType.Insert, ReferenceType.Sla, cloneSlaLevelId, actionLog); ExceptionLogs.LogException(loginUser, ex, "Cloning Sla Pause On Days", "SlaLevels.Clone - Pause On Days"); } return(clone); }
public static void WriteEvent(TSEventLogEventType tsEventLogEventType, HttpRequest httpRequest = null, User user = null, Organization organization = null, string[] extraInfo = null) { try { string source = "TeamSupport"; if (EventLog.SourceExists(source)) { List <object> prams = extraInfo == null ? new List <object>() : new List <object>(extraInfo); EventLogEntryType eventLogEntryType = EventLogEntryType.Information; switch (tsEventLogEventType) { case TSEventLogEventType.LoginSuccess: prams.Add("User logged in"); eventLogEntryType = EventLogEntryType.SuccessAudit; break; case TSEventLogEventType.LogoutSuccess: prams.Add("User logged out"); eventLogEntryType = EventLogEntryType.SuccessAudit; break; case TSEventLogEventType.FailedLoginAttempt: prams.Add("Failed log in attempt"); eventLogEntryType = EventLogEntryType.FailureAudit; break; case TSEventLogEventType.AccountLocked: prams.Add("Account locked out"); eventLogEntryType = EventLogEntryType.Warning; break; default: break; } if (httpRequest != null) { prams.Add(string.Format("IPAddress: {0}", httpRequest.UserHostAddress)); } if (organization != null) { prams.Add(string.Format("OrganizationID: {0}", organization.OrganizationID.ToString())); prams.Add(string.Format("Account: {0}", organization.Name)); } if (user != null) { prams.Add(string.Format("UserID: {0}", user.UserID.ToString())); prams.Add(string.Format("User: {0}", user.FirstLastName)); prams.Add(string.Format("Email: {0}", user.Email)); } EventLog.WriteEvent(source, new EventInstance((int)tsEventLogEventType, 0, eventLogEntryType), prams.ToArray()); //EventLog.WriteEntry(source, message, eventLogEntryType, (int)tsEventLogEventType, (short)tsEventLogCategoryType); } else { // ExceptionLogs.AddLog(LoginUser.Anonymous, "TeamSupport has not been setup as a source in the event logs."); } } catch (Exception ex) { ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Event Logs", "Error writing event log"); } }
private void GetChildrenByParentValue(int organizationID, int?auxID, int ticketID, int parentID, string parentValue, int?productID, ref DataTable result) { SqlCommand command = new SqlCommand(); command.CommandText = @" SELECT Distinct cv.CustomValueID, cv.RefID, cv.CustomValue, cv.DateCreated, cv.DateModified, cv.CreatorID, cv.ModifierID, cv.ImportFileID, cf.Name, cf.ApiFieldName, cf.FieldType, cf.ListValues, cf.Description, cf.RefType, cf.AuxID, cf.Position, cf.IsVisibleOnPortal, cf.IsFirstIndexSelect, cf.IsRequired, cf.OrganizationID, cf.CustomFieldID, cf.IsRequiredToClose, cf.Mask, cf.CustomFieldCategoryID, CASE WHEN EXISTS(SELECT NULL FROM CustomFields cf2 WHERE cf2.ParentCustomFieldID = cf.CustomFieldID) THEN 1 ELSE 0 END As IsConditionalParent FROM CustomFields cf LEFT JOIN CustomValues cv ON cv.CustomFieldID = cf.CustomFieldID AND cv.RefID = @RefID WHERE cf.OrganizationID = @OrganizationID AND cf.IsVisibleOnPortal = 1 AND cf.RefType = 17 AND (cf.AuxID = @AuxID OR @AuxID < 0) AND cf.ParentCustomFieldID = @ParentID AND ( cf.ParentCustomValue = @ParentValue OR ( @ParentValue IS NULL AND cf.ParentCustomValue IS NULL ) ) AND ( cf.ParentProductID = @ProductID OR cf.ParentProductID IS NULL ) ORDER BY cf.Position"; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@OrganizationID", organizationID); command.Parameters.AddWithValue("@RefID", ticketID); command.Parameters.AddWithValue("@AuxID", auxID ?? -1); command.Parameters.AddWithValue("@ParentID", parentID); command.Parameters.AddWithValue("@ParentValue", parentValue); command.Parameters.AddWithValue("@ProductID", productID ?? -1); DataTable children = new DataTable(); using (SqlConnection connection = new SqlConnection(LoginUser.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(children); } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); ExceptionLogs.LogException(LoginUser, ex, "Children Custom Values", DataUtils.GetCommandTextSql(command)); throw; } connection.Close(); } int childID = -1; string childValue = string.Empty; for (int i = 0; i < children.Rows.Count; i++) { result.ImportRow(children.Rows[i]); childID = (int)children.Rows[i]["CustomFieldID"]; childValue = children.Rows[i]["CustomValue"].ToString(); GetChildrenByParentValue(organizationID, auxID, ticketID, childID, childValue, productID, ref result); } }
private DataTable GetParentsByReferenceType(int organizationID, int?auxID, int ticketID, int?parentProductID) { SqlCommand command = new SqlCommand(); command.CommandText = @" SELECT Distinct cv.CustomValueID, cv.RefID, cv.CustomValue, cv.DateCreated, cv.DateModified, cv.CreatorID, cv.ModifierID, cv.ImportFileID, cf.Name, cf.ApiFieldName, cf.FieldType, cf.ListValues, cf.Description, cf.RefType, cf.AuxID, cf.Position, cf.IsVisibleOnPortal, cf.IsFirstIndexSelect, cf.IsRequired, cf.OrganizationID, cf.CustomFieldID, cf.IsRequiredToClose, cf.Mask, cf.CustomFieldCategoryID, CASE WHEN EXISTS(SELECT NULL FROM CustomFields cf2 WHERE cf2.ParentCustomFieldID = cf.CustomFieldID) THEN 1 ELSE 0 END As IsConditionalParent FROM CustomFields cf LEFT JOIN CustomValues cv ON cv.CustomFieldID = cf.CustomFieldID AND cv.RefID = @RefID WHERE cf.OrganizationID = @OrganizationID AND cf.IsVisibleOnPortal = 1 AND cf.RefType = 17 AND (cf.AuxID = @AuxID OR @AuxID < 0) AND cf.ParentCustomFieldID IS NULL AND (cf.ParentProductID IS NULL OR cf.ParentProductID = @ParentProductID) ORDER BY cf.CustomFieldCategoryID asc, cf.Position asc"; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@OrganizationID", organizationID); command.Parameters.AddWithValue("@RefID", ticketID); command.Parameters.AddWithValue("@AuxID", auxID ?? -1); command.Parameters.AddWithValue("@ParentProductID", parentProductID ?? -1); DataTable result = new DataTable(); using (SqlConnection connection = new SqlConnection(LoginUser.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(result); } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); ExceptionLogs.LogException(LoginUser, ex, "Parent Custom Values", DataUtils.GetCommandTextSql(command)); throw; } connection.Close(); } return(result); }