예제 #1
0
        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());
            }
        }
예제 #3
0
        // 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);
        }
예제 #4
0
        // 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)";
     }
 }