private DataTable GetData(SettingsHelper helper) { //Try and connect CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (helper.GetSetting("ConfigFile").Value.EndsWith(".config")) { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + helper.GetSetting("ConfigFile").Value); } else { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(helper.GetSetting("ConfigFile").Value); } odbcConn.ConnectionTest(); var ex = new Exception(); var queryString = _literalStringReplacer.Process(helper.GetSetting("QueryText").Value); DataTable dt; if (Convert.ToInt16(helper.GetSetting("QueryTimeout", 0).Value) > 0) { dt = odbcConn.ConnectToERP(queryString, ref ex, Convert.ToInt16(helper.GetSetting("QueryTimeout").Value)); } else { dt = odbcConn.ConnectToERP(queryString, ref ex); } if (ex != null) { throw ex; } return(dt); }
public object TestConnection(string _connectionFile, Guid _portletID) { Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID); if (portlet.AccessCheck("CanAdminQueries")) { if (_connectionFile.EndsWith(".config") && !System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/ClientConfig/" + _connectionFile))) { return(new { success = false, message = "Connection File specified does not exist in ClientConfig folder.", looked = HttpContext.Current.Server.MapPath("~/ClientConfig/" + _connectionFile).ToString() }); } else { try { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (_connectionFile.EndsWith(".config")) { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile); } else { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile); } odbcConn.ConnectionTest(); return(new { success = true, message = "Connection test was successful." }); } catch (Exception ex) { return(new { success = false, message = "Connection could not be established using the Connection provided.", exception = ex.ToString() }); } } } else { return(new { success = false, message = "No Permissions to Modify Queries" }); } }
public object TestConnection(string _connectionFile, Guid _portletID) { Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance<IPortletFacade>().FindByGuid(_portletID); if (portlet.AccessCheck("CanAdminQueries")) { if (_connectionFile.EndsWith(".config") && !System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/ClientConfig/" + _connectionFile))) { return new { success = false, message = "Connection File specified does not exist in ClientConfig folder.", looked = HttpContext.Current.Server.MapPath("~/ClientConfig/" + _connectionFile).ToString() }; } else { try { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (_connectionFile.EndsWith(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile); odbcConn.ConnectionTest(); return new { success = true, message = "Connection test was successful." }; } catch (Exception ex) { return new { success = false, message = "Connection could not be established using the Connection provided.", exception = ex.ToString() }; } } } else { return new { success = false, message = "No Permissions to Modify Queries" }; } }
private DataTable GetData() { //Try and connect CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; try { if (_helper.GetSetting("ConfigFile").Value.EndsWith(".config")) { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _helper.GetSetting("ConfigFile").Value); } else { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_helper.GetSetting("ConfigFile").Value); } } catch { this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Unable to locate usable connection string. Contact portal administrator."); throw; } try { odbcConn.ConnectionTest(); } catch { this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Database connection test failed. Contact portal administrator."); throw; } var ex = new Exception(); try { var literalStringReplacer = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <ILiteralStringReplacer>(); var queryString = literalStringReplacer.Process(_helper.GetSetting("QueryText").Value, ParentPortlet.Portlet); if (Convert.ToInt16(_helper.GetSetting("QueryTimeout", 0).Value) > 0) { return(odbcConn.ConnectToERP(queryString, ref ex, Convert.ToInt16(_helper.GetSetting("QueryTimeout").Value))); } else { return(odbcConn.ConnectToERP(queryString, ref ex)); } } catch (Exception ee) { this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Query Failed. Contact portal administrator. " + ex.ToString() + ee.ToString()); throw; } }
private DataTable GetData(Guid _portletID) { var mapper = new NHSimpleQuerySettingsMapper(); var settings = mapper.GetSettings(_portletID).ToList(); var _helper = new SettingsHelper(settings, _portletID, mapper); var portlet = ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID); OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (_helper.GetSetting("ConfigFile").Value.EndsWith(".config")) { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _helper.GetSetting("ConfigFile").Value); } else { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_helper.GetSetting("ConfigFile").Value); } odbcConn.ConnectionTest(); Exception ex = null; DataTable dt; var literalStringReplacer = ObjectFactoryWrapper.GetInstance <ILiteralStringReplacer>(); var queryString = literalStringReplacer.Process(_helper.GetSetting("QueryText").Value, portlet); if (Convert.ToInt16(_helper.GetSetting("QueryTimeout", 0).Value) > 0) { dt = odbcConn.ConnectToERP(queryString, ref ex, Convert.ToInt16(_helper.GetSetting("QueryTimeout").Value)); } else { dt = odbcConn.ConnectToERP(queryString, ref ex); } if (ex != null) { throw ex; } return(dt); }
private DataTable GetData(SettingsHelper helper, string username) { //Try and connect CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (helper.GetSetting("ConfigFile").Value.EndsWith(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + helper.GetSetting("ConfigFile").Value); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(helper.GetSetting("ConfigFile").Value); odbcConn.ConnectionTest(); var ex = new Exception(); var queryStringFiller = new Helpers.FillQueryString(helper.GetSetting("QueryText").Value, _userFacade.FindByUsername(username)); DataTable dt; if (Convert.ToInt16(helper.GetSetting("QueryTimeout", 0).Value) > 0) dt = odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex, Convert.ToInt16(helper.GetSetting("QueryTimeout").Value)); else dt = odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex); if (ex != null) { throw ex; } return dt; }
public object RunQuery(Guid _portletID) { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID); if (portlet.ParentPage.CanView(PortalUser.Current)) { try { NHSimpleQueryMapper mapper = new NHSimpleQueryMapper(); NHSimpleQuery NHS = mapper.GetById(_portletID); if (NHS.ConfigFile.Contains(".config")) { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + NHS.ConfigFile); } else { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(NHS.ConfigFile); } odbcConn.ConnectionTest(); Exception exError = null; try { DataTable dt = new DataTable(); if (NHS.QueryTimeout > 0) { dt = odbcConn.ConnectToERP(NHS.FilledQueryString(), ref exError, NHS.QueryTimeout); } else { dt = odbcConn.ConnectToERP(NHS.FilledQueryString(), ref exError); } StoreExportData(NHS, dt, _portletID, PortletUtilities.GetSettingValue(portlet, "DisplayColumnHeadings") == "Checked", portlet); JSDataTableConverter jsdtc = new JSDataTableConverter(dt, NHS.ExpandedColumns.Split(','), NHS.ColumnLabels.Split(',')); JSDataTable data = jsdtc.GetJsDataTable(); if (exError != null) { throw exError; } return(new { success = true, data = data.data, columns = data.columns }); } catch (Exception ex) { return(new { success = false, message = "Query Failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex : "") }); } } catch (Exception ex) { return(new { success = false, message = "Connection failed before query was executed." + (PortalUser.Current.IsSiteAdmin ? ": " + ex : "") }); } } else { return(new { success = false, message = "You do not have permissions to view this portlet" }); } }
public object TestQuery(string _connectionFile, string _queryString, Guid _portletID, string _expandedColumns, string _columnLabels, string queryTimeout) { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance<IPortletFacade>().FindByGuid(_portletID); if (portlet.AccessCheck("CanAdminQueries")) { try { if (_connectionFile.Contains(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3( _connectionFile); odbcConn.ConnectionTest(); Exception exError = null; try { QuerySafe QS = new QuerySafe(); if (QS.IsQuerySafeEnough(_queryString, portlet)) { FillQueryString FQS = new FillQueryString(_queryString); DataTable dt = new DataTable(); int qt = 0; if (Int32.TryParse(queryTimeout, out qt) && qt > 0) dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError, qt); else dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError); List<String> expandedColumns = new List<String>(); List<String> columnLabels = new List<String>(); if (_expandedColumns.Trim().Length > 0) { if (_expandedColumns.Contains(',')) { foreach (String column in _expandedColumns.Split(',')) { expandedColumns.Add(column.Trim()); } } else { expandedColumns.Add(_expandedColumns.Trim()); } } if (_columnLabels.Trim().Length > 0) { if (_columnLabels.Contains(',')) { foreach (String label in _columnLabels.Split(',')) { columnLabels.Add(label.Trim()); } } else { columnLabels.Add(_columnLabels.Trim()); } } JSDataTableConverter jsdtc = new JSDataTableConverter(dt, expandedColumns.ToArray(), columnLabels.ToArray()); JSDataTable data = jsdtc.GetJsDataTable(); if (exError != null) throw exError; return new { success = true, message = "Query test was successful. " + dt.Rows.Count.ToString() + " rows returned. ", data = data.data, columns = data.columns, a = expandedColumns.ToArray(), b = columnLabels.ToArray() }; } else { return new { success = false, message = "You do not have permissions to create advanced queries that use Update, Delete, Insert, or Execute." }; } } catch (Exception ex) { return new { success = false, message = "Query Failed. Test your query using an external tool and paste your corrected version into place. <br>Error:<br>" + ex.Message }; } } catch { return new { success = false, message = "Connection failed before query was executed." }; } } else { return new { success = false, message = "You do not have permissions to create queries." }; } }
public object RunQuery(Guid _portletID) { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance<IPortletFacade>().FindByGuid(_portletID); if (portlet.ParentPage.CanView(PortalUser.Current)) { try { NHSimpleQueryMapper mapper = new NHSimpleQueryMapper(); NHSimpleQuery NHS = mapper.GetById(_portletID); if (NHS.ConfigFile.Contains(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + NHS.ConfigFile); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(NHS.ConfigFile); odbcConn.ConnectionTest(); Exception exError = null; try { DataTable dt = new DataTable(); if (NHS.QueryTimeout > 0) dt = odbcConn.ConnectToERP(NHS.FilledQueryString(), ref exError, NHS.QueryTimeout); else dt = odbcConn.ConnectToERP(NHS.FilledQueryString(), ref exError); StoreExportData(NHS, dt, _portletID, PortletUtilities.GetSettingValue(portlet, "DisplayColumnHeadings") == "Checked", portlet); JSDataTableConverter jsdtc = new JSDataTableConverter(dt, NHS.ExpandedColumns.Split(','), NHS.ColumnLabels.Split(',')); JSDataTable data = jsdtc.GetJsDataTable(); if (exError != null) throw exError; return new { success = true, data = data.data, columns = data.columns }; } catch (Exception ex) { return new { success = false, message = "Query Failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex : "") }; } } catch (Exception ex) { return new { success = false, message = "Connection failed before query was executed." + (PortalUser.Current.IsSiteAdmin ? ": " + ex : "") }; } } else { return new { success = false, message = "You do not have permissions to view this portlet" }; } }
private DataTable GetData( Guid _portletID) { var mapper = new NHSimpleQuerySettingsMapper(); var settings = mapper.GetSettings(_portletID).ToList(); var _helper = new SettingsHelper(settings, _portletID, mapper); OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (_helper.GetSetting("ConfigFile").Value.EndsWith(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _helper.GetSetting("ConfigFile").Value); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_helper.GetSetting("ConfigFile").Value); odbcConn.ConnectionTest(); Exception ex = null; DataTable dt; var queryStringFiller = new FillQueryString(_helper.GetSetting("QueryText").Value); if (Convert.ToInt16(_helper.GetSetting("QueryTimeout", 0).Value) > 0) dt = odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex, Convert.ToInt16(_helper.GetSetting("QueryTimeout").Value)); else dt = odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex); if (ex != null) { throw ex; } return dt; }
private DataTable GetData() { //Try and connect CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; try { if (_helper.GetSetting("ConfigFile").Value.EndsWith(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _helper.GetSetting("ConfigFile").Value); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_helper.GetSetting("ConfigFile").Value); } catch { this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Unable to locate usable connection string. Contact portal administrator."); throw new Exception(); } try { odbcConn.ConnectionTest(); } catch { this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Database connection test failed. Contact portal administrator."); throw new Exception(); } var ex = new Exception(); try { var queryStringFiller = new FillQueryString(_helper.GetSetting("QueryText").Value); if (Convert.ToInt16(_helper.GetSetting("QueryTimeout", 0).Value) > 0) return odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex, Convert.ToInt16(_helper.GetSetting("QueryTimeout").Value)); else return odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex); } catch (Exception ee) { this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Query Failed. Contact portal administrator. " + ex.ToString() + ee.ToString()); throw new Exception(); } }
public object TestQuery(string _connectionFile, string _queryString, Guid _portletID, string _expandedColumns, string _columnLabels, string queryTimeout) { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID); if (portlet.AccessCheck("CanAdminQueries")) { try { if (_connectionFile.Contains(".config")) { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile); } else { odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile); } odbcConn.ConnectionTest(); Exception exError = null; try { QuerySafe QS = new QuerySafe(); if (QS.IsQuerySafeEnough(_queryString, portlet)) { FillQueryString FQS = new FillQueryString(_queryString); DataTable dt = new DataTable(); int qt = 0; if (Int32.TryParse(queryTimeout, out qt) && qt > 0) { dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError, qt); } else { dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError); } List <String> expandedColumns = new List <String>(); List <String> columnLabels = new List <String>(); if (_expandedColumns.Trim().Length > 0) { if (_expandedColumns.Contains(',')) { foreach (String column in _expandedColumns.Split(',')) { expandedColumns.Add(column.Trim()); } } else { expandedColumns.Add(_expandedColumns.Trim()); } } if (_columnLabels.Trim().Length > 0) { if (_columnLabels.Contains(',')) { foreach (String label in _columnLabels.Split(',')) { columnLabels.Add(label.Trim()); } } else { columnLabels.Add(_columnLabels.Trim()); } } JSDataTableConverter jsdtc = new JSDataTableConverter(dt, expandedColumns.ToArray(), columnLabels.ToArray()); JSDataTable data = jsdtc.GetJsDataTable(); if (exError != null) { throw exError; } return(new { success = true, message = "Query test was successful. " + dt.Rows.Count.ToString() + " rows returned. ", data = data.data, columns = data.columns, a = expandedColumns.ToArray(), b = columnLabels.ToArray() }); } else { return(new { success = false, message = "You do not have permissions to create advanced queries that use Update, Delete, Insert, or Execute." }); } } catch (Exception ex) { return(new { success = false, message = "Query Failed. Test your query using an external tool and paste your corrected version into place. <br>Error:<br>" + ex.Message }); } } catch { return(new { success = false, message = "Connection failed before query was executed." }); } } else { return(new { success = false, message = "You do not have permissions to create queries." }); } }
public object TestQuery(string _connectionFile, string _queryString, Guid _portletID, string _expandedColumns, string _columnLabels, string _queryTimeout, string _testHostId) { Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance<IPortletFacade>().FindByGuid(_portletID); if (portlet.AccessCheck("CanAdminQueries")) { try { CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn; if (_connectionFile.Contains(".config")) odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile); else odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3( _connectionFile); odbcConn.ConnectionTest(); try { var qs = new QuerySafe(); if (qs.IsQuerySafeEnough(_queryString, portlet)) { var fqs = new FillQueryString(_queryString, _testHostId); Exception exError = null; DataTable dt; var qt = 0; if (Int32.TryParse(_queryTimeout, out qt) && qt > 0) dt = odbcConn.ConnectToERP(fqs.FilledQueryString(), ref exError, qt); else dt = odbcConn.ConnectToERP(fqs.FilledQueryString(), ref exError); if (exError != null) { return new { success = false, message = "Query Test Failed. " + exError.Message + " " + exError.StackTrace }; } if (dt == null) { return new { success = true, message = "Query test was successful, but no results were returned." }; } var expandedColumns = new List<String>(); var columnLabels = new List<String>(); if (_expandedColumns.Trim().Length > 0) { if (_expandedColumns.Contains(',')) { expandedColumns.AddRange(_expandedColumns.Split(',').Select(column => column.Trim())); } else { expandedColumns.Add(_expandedColumns.Trim()); } } if (_columnLabels.Trim().Length > 0) { if (_columnLabels.Contains(',')) { columnLabels.AddRange(_columnLabels.Split(',').Select(label => label.Trim())); } else { columnLabels.Add(_columnLabels.Trim()); } } var jsdtc = new JSDataTableConverter(dt, expandedColumns.ToArray(), columnLabels.ToArray()); var data = jsdtc.GetJsDataTable(); return new { success = true, message = "Query test was successful. " + dt.Rows.Count.ToString() + " rows returned. ", data.data, data.columns }; } return new { success = false, message = "You do not have permissions to create advanced queries that use Update, Delete, Insert, or Execute." }; } catch (Exception ex) { return new { success = false, message = "Query Failed. Test your query using an external tool and paste your corrected version into place. <br>Error:<br>" + ex.Message }; } } catch { return new { success = false, message = "Connection failed before query was executed." }; } } return new { success = false, message = "You do not have permissions to create queries." }; }