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