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