public string ExecSQL(string sqlExec)
{
SageCRMCustom FSageCRMCustom;
if (this.SageCRMConnection != null)
{
FSageCRMCustom = new SageCRMCustom(); //we use this to make our request
FSageCRMCustom.SageCRMConnection = FSageCRMConnection;
return(FSageCRMCustom._GetHTML(this.getExecSQLFile(), "&ExecSQL=" + sqlExec, "", false));
}
else
{
return("No SageCRMConnection set (ExecSQL Method)");
}
}
private void populate_lb_buttonimages()
{
SageCRMCustom FSageCRMCustom;
string imageliststr = "";
string[] imageListArr;
string[] stringSeparators = new string[] { "," };
try
{
lb_buttonimages.Items.Clear();
if (this.connectionObject != null)
{
FSageCRMCustom = new SageCRMCustom(); //we use this to make our request
FSageCRMCustom.SageCRMConnection = connectionObject;
imageliststr =FSageCRMCustom._GetHTML("/CustomPages/SageCRM/component/imagelist.asp", "", "", false);
imageListArr = imageliststr.Split(stringSeparators, StringSplitOptions.None);
for (int i = 0; i < imageListArr.Length; i++)
{
lb_buttonimages.Items.Add(imageListArr[i].ToString());
}
setListValue(lb_buttonimages,this.ImageName);
}
else
{
MessageBox.Show("No SageCRMConnection set (populate_lb_buttonimages Method)");
}
}
catch (Exception ex3)
{
MessageBox.Show("Error retrieving image list. " + ex3.Message.ToString());
}
}
// Get data from the underlying data source.
// Build and return a DataView, regardless of mode.
/*
* Parameters
* arguments
* A DataSourceSelectArguments that is used to request
* operations on the data beyond basic data retrieval.
* Return Value
* An IEnumerable list of data from the underlying data storage.
* RemarksRemarks
* The ExecuteSelect method is called to retrieve data from the
* underlying data store and return it as an IEnumerable object.
* All data source controls support data retrieval from their
* underlying data storage, even if other operations such as
* insertion and sorting are not supported. Because a data-bound
* control can request a list of data at any time as a result of
* a DataSourceChanged event or a DataBind method call,
* the data retrieval must be performed on demand.
*/
protected override IEnumerable ExecuteSelect(DataSourceSelectArguments selectArgs)
{
IEnumerable dataList = null;
DataColumn col;
string xmlData = "";
string xmlSchema = "";
int rowcount = 0;
this.FSelectSQL = this.FSelectSQL.Replace("+", "%2B");
this.FWhereClause = this.FWhereClause.Replace("+", "%2B");
string dataFile = "";
string dataSchemaFile = "";
if (FSelectSQL != "")
{
//get the schema xml
dataFile = this.getSelectSql_file();
dataSchemaFile = this.getTableSchema_Selectsqlfile();
xmlSchema = FSageCRMCustom._GetHTML(this.getTableSchema_Selectsqlfile(), "&SelectSQL=" + this.FSelectSQL, "", false);
//get the data xml
xmlData = FSageCRMCustom._GetHTML(this.getSelectSql_file(),
"&Top=" + selectArgs.MaximumRows.ToString() +
"&iTop=" + this.FTop +
"&iFrom=" + selectArgs.StartRowIndex.ToString() +
"&iSort=" + selectArgs.SortExpression.ToString() +
"&grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
"&SelectSQL=" + this.FSelectSQL, "", false);
}
else
{
dataFile = this.getFindRecord_file();
dataSchemaFile = this.getTableSchema_file();
//get the schema xml
xmlSchema = FSageCRMCustom._GetHTML(this.getTableSchema_file(), "&TableName=" + this.FTableName, "", false);
//get the data xml
xmlData = FSageCRMCustom._GetHTML(this.getFindRecord_file(),
"&TableName=" + this.FTableName +
"&WhereClause=" + this.FWhereClause +
"&Top=" + selectArgs.MaximumRows.ToString() +
"&iTop=" + this.FTop +
"&iFrom=" + selectArgs.StartRowIndex.ToString() +
"&iSort=" + selectArgs.SortExpression.ToString() +
"&grc=" + selectArgs.RetrieveTotalRowCount.ToString()
, "", false);
}
//build our StreamReaders
StringReader xmlStreamSchema = new StringReader(xmlSchema.ToString());
StringReader xmlStreamData = new StringReader(xmlData.ToString());
//the data dataset
try
{
objData = new DataSet();
objData.ReadXml(xmlStreamData);
}
catch (Exception e)
{
throw new InvalidOperationException("Error in schema data (check properties)." +
System.Environment.NewLine +
"ASP File=" + dataFile +
System.Environment.NewLine +
"dataSchemaFile=" + dataSchemaFile +
System.Environment.NewLine +
"TableName=" + this.FTableName +
System.Environment.NewLine +
"WhereClause=" + this.FWhereClause +
System.Environment.NewLine +
"SelectSQL=" + this.FSelectSQL +
System.Environment.NewLine +
"Top=" + selectArgs.MaximumRows.ToString() +
System.Environment.NewLine +
"iTop=" + this.FTop +
System.Environment.NewLine +
"iFrom=" + selectArgs.StartRowIndex.ToString() +
System.Environment.NewLine +
"iSort=" + selectArgs.SortExpression.ToString() +
System.Environment.NewLine +
"grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
System.Environment.NewLine +
"Error Msg=" + e.Message +
System.Environment.NewLine +
"xmlSchema=" + xmlSchema.ToString());
}
//the schema dataset
try
{
objSchema = new DataSet();
objSchema.ReadXml(xmlStreamSchema);
}
catch (Exception e)
{
throw new InvalidOperationException("Error in data schema." +
System.Environment.NewLine +
"ASP File=" + dataFile +
System.Environment.NewLine +
"dataSchemaFile=" + dataSchemaFile +
System.Environment.NewLine +
"TableName=" + this.FTableName +
System.Environment.NewLine +
"WhereClause=" + this.FWhereClause +
System.Environment.NewLine +
"SelectSQL=" + this.FSelectSQL +
System.Environment.NewLine +
"Top=" + selectArgs.MaximumRows.ToString() +
System.Environment.NewLine +
"iTop=" + this.FTop +
System.Environment.NewLine +
"iFrom=" + selectArgs.StartRowIndex.ToString() +
System.Environment.NewLine +
"iSort=" + selectArgs.SortExpression.ToString() +
System.Environment.NewLine +
"grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
System.Environment.NewLine +
"Error Msg=" + e.Message +
System.Environment.NewLine +
"xmlSchema=" + xmlSchema.ToString());
}
if (FTableName != "")
{
data = new DataTable(FTableName);
}
else
{
data = new DataTable();
}
if (objSchema.Tables.Count == 0)
{
throw new InvalidOperationException("No table data found.");
}
//setup the schema of the columns using our schema dataset
for (int i = 0; i < objSchema.Tables[0].Rows.Count; i++) //each row describes a column
{
string fieldType = objSchema.Tables[0].Rows[i]["FieldType"].ToString();
string fieldName = objSchema.Tables[0].Rows[i]["FieldName"].ToString();
string fieldCaption = objSchema.Tables[0].Rows[i]["FieldCaption"].ToString();
if (i == 0)
{
idField = fieldName;
}
if (fieldType == "int")
{
col = new DataColumn(fieldName, typeof(int));
}
else
if (fieldType == "datetime")
{
col = new DataColumn(fieldName, typeof(DateTime));
col.DateTimeMode = System.Data.DataSetDateTime.Utc;
}
else
{ //default string
col = new DataColumn(fieldName, typeof(string));
}
col.AllowDBNull = true;
col.Caption = fieldCaption;
data.Columns.Add(col);
}
string colName = "";
if (objData.Tables.Count > 0)
{
int icrmtogethertrc = 1;
if (objData.Tables[0].TableName != "crmtogethertrc")
{
for (int i = 0; i < objData.Tables[0].Rows.Count; i++)
{
string dataValues = "";
string[] dataValuesArr;
string[] stringSeparators = new string[] { "[stopitnoe]" };
for (int j = 0; j < data.Columns.Count; j++) //we loop though the schema columns to that the data is read in order
{
colName = data.Columns[j].ColumnName.ToString();
string valStr = objData.Tables[0].Rows[i][colName].ToString();
//if (dataValues != "") //MR this line meant that if the first column was null the colummn order would be wrong
if (j > 0)
{
dataValues += "[stopitnoe]";
}
if (data.Columns[j].DataType == typeof(int))
{
//we may do something here later
}
else
if (data.Columns[j].DataType == typeof(DateTime))
{
//we may do something here later
}
dataValues += System.Web.HttpUtility.UrlDecode(valStr);
}
dataValuesArr = dataValues.Split(stringSeparators, StringSplitOptions.None);
data.Rows.Add(CopyRowData(dataValuesArr, data.NewRow()));
rowcount++;
}
}
else
{
icrmtogethertrc = 0;
}
//get total row count data
if (objData.Tables[icrmtogethertrc].TableName == "crmtogethertrc")
{
for (int i = 0; i < objData.Tables[icrmtogethertrc].Rows.Count; i++)
{
string trc_res = objData.Tables[icrmtogethertrc].Rows[i]["crmtogethertotal"].ToString();
selectArgs.TotalRowCount = Convert.ToInt32(trc_res);
}
}
}
data.AcceptChanges();
this.dv = new DataView(data);
if (selectArgs.SortExpression != String.Empty)
{
this.dv.Sort = selectArgs.SortExpression;
}
dataList = this.dv;
if (null == dataList)
{
throw new InvalidOperationException("No data loaded from data source.");
}
this.dataset = this.data.DataSet;
return(dataList);
}
// Get data from the underlying data source.
// Build and return a DataView, regardless of mode.
/*
* Parameters
* arguments
* A DataSourceSelectArguments that is used to request
* operations on the data beyond basic data retrieval.
* Return Value
* An IEnumerable list of data from the underlying data storage.
* RemarksRemarks
* The ExecuteSelect method is called to retrieve data from the
* underlying data store and return it as an IEnumerable object.
* All data source controls support data retrieval from their
* underlying data storage, even if other operations such as
* insertion and sorting are not supported. Because a data-bound
* control can request a list of data at any time as a result of
* a DataSourceChanged event or a DataBind method call,
* the data retrieval must be performed on demand.
*/
protected override IEnumerable ExecuteSelect(DataSourceSelectArguments selectArgs)
{
IEnumerable dataList = null;
DataColumn col;
string xmlData = "";
string xmlSchema = "";
int rowcount = 0;
this.FSelectSQL = this.FSelectSQL.Replace("+", "%2B");
this.FWhereClause = this.FWhereClause.Replace("+", "%2B");
string dataFile = "";
string dataSchemaFile = "";
if (FSelectSQL != "")
{
string _datacachekey = getCacheKey("dataFile_" + this.FSelectSQL);
//get the schema xml
dataFile = this.getSelectSql_file();
dataSchemaFile = this.getTableSchema_Selectsqlfile();
xmlSchema = _getFromCache(_datacachekey + "schema");
//MR 4.7.1.1-fix fro caching of invalis session data
if ((xmlSchema == null) || (xmlSchema == ""))
{
xmlSchema = FSageCRMCustom._GetHTML(this.getTableSchema_Selectsqlfile(), "", "SelectSQL=" + this.FSelectSQL, false); //Fixes problem? "", "SelectSQL=" + this.FSelectSQL
if (FCachable)
{
//set the cache
_setInCache(_datacachekey + "schema", xmlSchema);
}
}
if (FCachable)
{
//check have we it already
xmlData = _getFromCache(_datacachekey);
}
//MR 4.7.1.1-fix fro caching of invalis session data
//get the data xml
if ((xmlData == null) || (xmlData == ""))
{
xmlData = FSageCRMCustom._GetHTML(this.getSelectSql_file(),
"&Top=" + selectArgs.MaximumRows.ToString() +
"&iTop=" + this.FTop +
"&iFrom=" + selectArgs.StartRowIndex.ToString() +
"&iSort=" + selectArgs.SortExpression.ToString() +
"&grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
"", "SelectSQL=" + this.FSelectSQL, false); // Fixes Problem? "", "SelectSQL=" + this.FSelectSQL
if (FCachable)
{
//set the cache
_setInCache(_datacachekey, xmlData);
}
}
}
else
{
dataFile = this.getFindRecord_file();
dataSchemaFile = this.getTableSchema_file();
string _datacachekey = getCacheKey("dataFile_" + this.FTableName + "_" + this.FWhereClause + "_" + this.FColumnList);
//get the schema xml
//to do turn back on....
xmlSchema = _getFromCache(_datacachekey + "schema2");
//MR 4.7.1.1-fix fro caching of invalis session data
if ((xmlSchema == null) || (xmlSchema == ""))
{
FSageCRMCustom.customPostData = "columnList=" + this.FColumnList;
xmlSchema = FSageCRMCustom._GetHTML(this.getTableSchema_file(), "&TableName=" + this.FTableName, "", false);
if (FCachable)
{
//set the cache
_setInCache(_datacachekey + "schema2", xmlSchema);
}
}
if (FCachable)
{
//check have we it already
xmlData = _getFromCache(_datacachekey);
}
//MR 4.7.1.1-fix fro caching of invalis session data
if ((xmlData == null) || (xmlData == ""))
{
//15 May 2020 - clever fix for ampersand appearing in the where clause
if (this.FWhereClause.IndexOf("&") >= 0)
{
this.FWhereClause = this.FWhereClause.Replace("&", "%26");
}
//get the data xml
FSageCRMCustom.customPostData = "columnList=" + this.FColumnList;
FSageCRMCustom.customPostData += "&translate=" + this.FTranslate;
xmlData = FSageCRMCustom._GetHTML(this.getFindRecord_file(),
"&TableName=" + this.FTableName +
"&WhereClause=" + this.FWhereClause +
"&Top=" + selectArgs.MaximumRows.ToString() +
"&iTop=" + this.FTop +
"&iFrom=" + selectArgs.StartRowIndex.ToString() +
"&iSort=" + selectArgs.SortExpression.ToString() +
"&grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
"&OrderBy=" + this.FOrderBy
, "", false);
if (FCachable)
{
//set the cache
_setInCache(_datacachekey, xmlData);
}
}
}
//build our StreamReaders
StringReader xmlStreamSchema = new StringReader(xmlSchema.ToString());
StringReader xmlStreamData = new StringReader(xmlData.ToString());
//the data dataset
try
{
objData = new DataSet();
objData.ReadXml(xmlStreamData);
}
catch (Exception e)
{
throw new InvalidOperationException("Error in schema data (check properties)." +
System.Environment.NewLine +
"CRMPortalPath=" + this.FSageCRMConnection.CRMPortalPath +
System.Environment.NewLine +
"ASP File=" + dataFile +
System.Environment.NewLine +
"dataSchemaFile=" + dataSchemaFile +
System.Environment.NewLine +
"TableName=" + this.FTableName +
System.Environment.NewLine +
"WhereClause=" + this.FWhereClause +
System.Environment.NewLine +
"SelectSQL=" + this.FSelectSQL +
System.Environment.NewLine +
"Top=" + selectArgs.MaximumRows.ToString() +
System.Environment.NewLine +
"iTop=" + this.FTop +
System.Environment.NewLine +
"iFrom=" + selectArgs.StartRowIndex.ToString() +
System.Environment.NewLine +
"iSort=" + selectArgs.SortExpression.ToString() +
System.Environment.NewLine +
"grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
System.Environment.NewLine +
"OrderBy=" + this.FOrderBy +
System.Environment.NewLine +
"Error Msg=" + e.Message +
System.Environment.NewLine +
"xmlSchema=" + xmlSchema.ToString());
}
//the schema dataset
try
{
objSchema = new DataSet();
objSchema.ReadXml(xmlStreamSchema);
}
catch (Exception e)
{
throw new InvalidOperationException("Error in data schema." +
System.Environment.NewLine +
"ASP File=" + dataFile +
System.Environment.NewLine +
"dataSchemaFile=" + dataSchemaFile +
System.Environment.NewLine +
"TableName=" + this.FTableName +
System.Environment.NewLine +
"WhereClause=" + this.FWhereClause +
System.Environment.NewLine +
"SelectSQL=" + this.FSelectSQL +
System.Environment.NewLine +
"Top=" + selectArgs.MaximumRows.ToString() +
System.Environment.NewLine +
"iTop=" + this.FTop +
System.Environment.NewLine +
"iFrom=" + selectArgs.StartRowIndex.ToString() +
System.Environment.NewLine +
"iSort=" + selectArgs.SortExpression.ToString() +
System.Environment.NewLine +
"grc=" + selectArgs.RetrieveTotalRowCount.ToString() +
System.Environment.NewLine +
"OrderBy=" + this.FOrderBy +
System.Environment.NewLine +
"Error Msg=" + e.Message +
System.Environment.NewLine +
"xmlSchema=" + xmlSchema.ToString());
}
if (FTableName != "")
{
data = new DataTable(FTableName);
}
else
{
data = new DataTable();
}
if (objSchema.Tables.Count == 0)
{
throw new InvalidOperationException("No table data found.");
}
//setup the schema of the columns using our schema dataset
for (int i = 0; i < objSchema.Tables[0].Rows.Count; i++) //each row describes a column
{
string fieldType = objSchema.Tables[0].Rows[i]["FieldType"].ToString();
string fieldName = objSchema.Tables[0].Rows[i]["FieldName"].ToString();
string fieldCaption = objSchema.Tables[0].Rows[i]["FieldCaption"].ToString();
if (i == 0)
{
idField = fieldName;
}
if (fieldType == "int")
{
col = new DataColumn(fieldName, typeof(int));
}
else
if (fieldType == "datetime")
{
col = new DataColumn(fieldName, typeof(DateTime));
col.DateTimeMode = System.Data.DataSetDateTime.Utc;
}
else
{ //default string
col = new DataColumn(fieldName, typeof(string));
}
col.AllowDBNull = true;
col.Caption = fieldCaption;
if (fieldName != "undefined")
{
//seen with external dbs
data.Columns.Add(col);
}
}
string colName = "";
if (objData.Tables.Count > 0)
{
int icrmtogethertrc = 1;
if (objData.Tables[0].TableName != "crmtogethertrc")
{
for (int i = 0; i < objData.Tables[0].Rows.Count; i++)
{
string dataValues = "";
string[] dataValuesArr;
string[] stringSeparators = new string[] { "[stopitnoe]" };
for (int j = 0; j < data.Columns.Count; j++) //we loop though the schema columns to that the data is read in order
{
colName = data.Columns[j].ColumnName.ToString();
string valStr = objData.Tables[0].Rows[i][colName].ToString();
//if (dataValues != "") //this line meant that if the first column was null the colummn order would be wrong
if (j > 0)
{
dataValues += "[stopitnoe]";
}
if (data.Columns[j].DataType == typeof(int))
{
//we may do something here later
//okay so when we have a 3rd party system that has an id field thats a string this is a problem
//clever....
int n;
bool isNumeric = int.TryParse(valStr, out n);
if (!isNumeric)
{
data.Columns[j].DataType = typeof(string);
}
////
}
else
if (data.Columns[j].DataType == typeof(DateTime))
{
//we may do something here later
}
dataValues += System.Web.HttpUtility.UrlDecode(valStr);
}
dataValuesArr = dataValues.Split(stringSeparators, StringSplitOptions.None);
data.Rows.Add(CopyRowData(dataValuesArr, data.NewRow()));
rowcount++;
}
}
else
{
icrmtogethertrc = 0;
}
//get total row count data
if (objData.Tables[icrmtogethertrc].TableName == "crmtogethertrc")
{
for (int i = 0; i < objData.Tables[icrmtogethertrc].Rows.Count; i++)
{
string trc_res = objData.Tables[icrmtogethertrc].Rows[i]["crmtogethertotal"].ToString();
selectArgs.TotalRowCount = Convert.ToInt32(trc_res);
}
}
}
data.AcceptChanges();
this.dv = new DataView(data);
if (selectArgs.SortExpression != String.Empty)
{
this.dv.Sort = selectArgs.SortExpression;
}
dataList = this.dv;
if (null == dataList)
{
throw new InvalidOperationException("No data loaded from data source.");
}
this.dataset = this.data.DataSet;
return(dataList);
}
public string ExecSQL(string sqlExec)
{
SageCRMCustom FSageCRMCustom;
if (this.SageCRMConnection != null)
{
FSageCRMCustom = new SageCRMCustom(); //we use this to make our request
FSageCRMCustom.SageCRMConnection = FSageCRMConnection;
return FSageCRMCustom._GetHTML(this.getExecSQLFile(), "&ExecSQL=" + sqlExec, "", false);
}
else
{
return "No SageCRMConnection set (ExecSQL Method)";
}
}
