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(); } }
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; }
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." }; }