コード例 #1
0
ファイル: ScratchDb.cs プロジェクト: HabitatFramework/HLUTool
        /// <summary>
        /// Create a list of sql filter conditions using a table of selected Incids.
        /// </summary>
        /// <param name="incidSelection">A DataTable containing the selected Incids.</param>
        /// <param name="gisApp">Which GIS system is being used.</param>
        /// <returns>
        /// A list of sql filter conditions.
        /// </returns>
        public static List<SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp, bool useIncidTable)
        {
            List<SqlFilterCondition> whereClause = new List<SqlFilterCondition>();
            SqlFilterCondition cond = new SqlFilterCondition();

            //StringBuilder incidList = new StringBuilder();

            // Split the table of selected Incids into chunks of continuous Incids so
            // that each chunk contains a continuous series of one or more Incids.
            var query = incidSelection.AsEnumerable().Select((r, index) => new
            {
                RowIndex = RecordIds.IncidNumber(r.Field<string>(0)) - index,
                Incid = r.Field<string>(0)
            }).ChunkBy(r => r.RowIndex);

            // Create a temporary list for storing some of the Incids.
            List<string> inList = new List<string>();

            // Determine which table to base the conditions on.
            DataTable condTable;
            if (useIncidTable)
                condTable = _incidTable;
            else
                condTable = _incidMMTable;

            // Loop through each chunk/series of Incids. If there are at least three
            // then it is worth processing them within ">=" and "<=" operators (as
            // this keeps the filter conditions short). If there are only one or two
            // Incids in the chunk then add them to the temporary 'inList' for
            // processing later.
            foreach (var item in query)
            {
                if (item.Count() < 3)
                {
                    if (gisApp != null)
                        inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
                    else
                        inList.AddRange(item.Select(t => t.Incid));
                }
                else
                {
                    cond = new SqlFilterCondition();
                    cond.BooleanOperator = "OR";
                    cond.OpenParentheses = "(";
                    cond.Column = _incidMMTable.incidColumn;
                    cond.Table = condTable;
                    cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
                    cond.Operator = ">=";
                    cond.Value = item.First().Incid;
                    cond.CloseParentheses = String.Empty;
                    whereClause.Add(cond);

                    cond = new SqlFilterCondition();
                    cond.BooleanOperator = "AND";
                    cond.OpenParentheses = String.Empty;
                    cond.Column = _incidMMTable.incidColumn;
                    cond.Table = condTable;
                    cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
                    cond.Operator = "<=";
                    cond.Value = item.Last().Incid;
                    cond.CloseParentheses = ")";
                    whereClause.Add(cond);
                }
            }

            // Any Incids that are not part of a continuous series of at least
            // three Incid must be queried using an "=" or "IN" sql operator.
            // So loop through all these Incids and lump them together into
            // strings of 254 Incids at a time so that each string can be used
            // in an "IN" statement.
            int i = 0;
            while (i < inList.Count)
            {
                int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
                string[] oneList = new string[numElems];
                inList.CopyTo(i, oneList, 0, numElems);

                cond = new SqlFilterCondition();
                cond.BooleanOperator = "OR";
                cond.OpenParentheses = "(";
                cond.Column = _incidMMTable.incidColumn;
                cond.Table = condTable;
                cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
                //---------------------------------------------------------------------
                // FIX: 001 Improve speed of 'Select current Incid on Map'
                // Use " INCID =" in SQL statement instrad of "INCID IN ()"
                // if there is only on item in the list (as it is much quicker)
                if (inList.Count == 1)
                    cond.Operator = "=";
                else
                    cond.Operator = "IN ()";
                //---------------------------------------------------------------------
                cond.Value = String.Join(",", oneList);
                cond.CloseParentheses = ")";
                whereClause.Add(cond);

                i += numElems;
            }

            return whereClause;
        }
コード例 #2
0
ファイル: ScratchDb.cs プロジェクト: Wolfgang-HBIC/HLUTool
        public static List<SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp)
        {
            List<SqlFilterCondition> whereClause = new List<SqlFilterCondition>();
            SqlFilterCondition cond = new SqlFilterCondition();

            StringBuilder incidList = new StringBuilder();

            var query = incidSelection.AsEnumerable().Select((r, index) => new
            {
                RowIndex = RecordIds.IncidNumber(r.Field<string>(0)) - index,
                Incid = r.Field<string>(0)
            }).ChunkBy(r => r.RowIndex);

            List<string> inList = new List<string>();

            foreach (var item in query)
            {
                if (item.Count() < 3)
                {
                    inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
                }
                else
                {
                    cond = new SqlFilterCondition();
                    cond.BooleanOperator = "OR";
                    cond.OpenParentheses = "(";
                    cond.Column = _incidTable.incidColumn;
                    cond.Table = _incidTable;
                    cond.ColumnSystemType = _incidTable.incidColumn.DataType;
                    cond.Operator = ">=";
                    cond.Value = item.First().Incid;
                    cond.CloseParentheses = String.Empty;
                    whereClause.Add(cond);
                    cond = new SqlFilterCondition();
                    cond.BooleanOperator = "AND";
                    cond.OpenParentheses = String.Empty;
                    cond.Column = _incidTable.incidColumn;
                    cond.Table = _incidTable;
                    cond.ColumnSystemType = _incidTable.incidColumn.DataType;
                    cond.Operator = "<=";
                    cond.Value = item.Last().Incid;
                    cond.CloseParentheses = ")";
                    whereClause.Add(cond);
                }
            }

            int i = 0;
            while (i < inList.Count)
            {
                int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
                string[] oneList = new string[numElems];
                inList.CopyTo(i, oneList, 0, numElems);

                cond = new SqlFilterCondition();
                cond.BooleanOperator = "OR";
                cond.OpenParentheses = "(";
                cond.Column = _incidMMTable.incidColumn;
                cond.Table = _incidMMTable;
                cond.ColumnSystemType = _incidTable.incidColumn.DataType;
                cond.Operator = "IN ()";
                cond.Value = String.Join(",", oneList);
                cond.CloseParentheses = ")";
                whereClause.Add(cond);

                i += numElems;
            }

            return whereClause;
        }
コード例 #3
0
        public static List <SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp)
        {
            List <SqlFilterCondition> whereClause = new List <SqlFilterCondition>();
            SqlFilterCondition        cond        = new SqlFilterCondition();

            StringBuilder incidList = new StringBuilder();

            var query = incidSelection.AsEnumerable().Select((r, index) => new
            {
                RowIndex = RecordIds.IncidNumber(r.Field <string>(0)) - index,
                Incid    = r.Field <string>(0)
            }).ChunkBy(r => r.RowIndex);

            List <string> inList = new List <string>();

            foreach (var item in query)
            {
                if (item.Count() < 3)
                {
                    inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
                }
                else
                {
                    cond = new SqlFilterCondition();
                    cond.BooleanOperator  = "OR";
                    cond.OpenParentheses  = "(";
                    cond.Column           = _incidTable.incidColumn;
                    cond.Table            = _incidTable;
                    cond.ColumnSystemType = _incidTable.incidColumn.DataType;
                    cond.Operator         = ">=";
                    cond.Value            = item.First().Incid;
                    cond.CloseParentheses = String.Empty;
                    whereClause.Add(cond);
                    cond = new SqlFilterCondition();
                    cond.BooleanOperator  = "AND";
                    cond.OpenParentheses  = String.Empty;
                    cond.Column           = _incidTable.incidColumn;
                    cond.Table            = _incidTable;
                    cond.ColumnSystemType = _incidTable.incidColumn.DataType;
                    cond.Operator         = "<=";
                    cond.Value            = item.Last().Incid;
                    cond.CloseParentheses = ")";
                    whereClause.Add(cond);
                }
            }

            int i = 0;

            while (i < inList.Count)
            {
                int      numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
                string[] oneList  = new string[numElems];
                inList.CopyTo(i, oneList, 0, numElems);

                cond = new SqlFilterCondition();
                cond.BooleanOperator  = "OR";
                cond.OpenParentheses  = "(";
                cond.Column           = _incidMMTable.incidColumn;
                cond.Table            = _incidMMTable;
                cond.ColumnSystemType = _incidTable.incidColumn.DataType;
                cond.Operator         = "IN ()";
                cond.Value            = String.Join(",", oneList);
                cond.CloseParentheses = ")";
                whereClause.Add(cond);

                i += numElems;
            }

            return(whereClause);
        }
コード例 #4
0
        /// <summary>
        /// Create a list of sql filter conditions using a table of selected Incids.
        /// </summary>
        /// <param name="incidSelection">A DataTable containing the selected Incids.</param>
        /// <param name="gisApp">Which GIS system is being used.</param>
        /// <returns>
        /// A list of sql filter conditions.
        /// </returns>
        public static List <SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp, bool useIncidTable)
        {
            List <SqlFilterCondition> whereClause = new List <SqlFilterCondition>();
            SqlFilterCondition        cond        = new SqlFilterCondition();

            //StringBuilder incidList = new StringBuilder();

            // Split the table of selected Incids into chunks of continuous Incids so
            // that each chunk contains a continuous series of one or more Incids.
            var query = incidSelection.AsEnumerable().Select((r, index) => new
            {
                RowIndex = RecordIds.IncidNumber(r.Field <string>(0)) - index,
                Incid    = r.Field <string>(0)
            }).ChunkBy(r => r.RowIndex);

            // Create a temporary list for storing some of the Incids.
            List <string> inList = new List <string>();

            // Determine which table to base the conditions on.
            DataTable condTable;

            if (useIncidTable)
            {
                condTable = _incidTable;
            }
            else
            {
                condTable = _incidMMTable;
            }

            // Loop through each chunk/series of Incids. If there are at least three
            // then it is worth processing them within ">=" and "<=" operators (as
            // this keeps the filter conditions short). If there are only one or two
            // Incids in the chunk then add them to the temporary 'inList' for
            // processing later.
            foreach (var item in query)
            {
                if (item.Count() < 3)
                {
                    if (gisApp != null)
                    {
                        inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
                    }
                    else
                    {
                        inList.AddRange(item.Select(t => t.Incid));
                    }
                }
                else
                {
                    cond = new SqlFilterCondition();
                    cond.BooleanOperator  = "OR";
                    cond.OpenParentheses  = "(";
                    cond.Column           = _incidMMTable.incidColumn;
                    cond.Table            = condTable;
                    cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
                    cond.Operator         = ">=";
                    cond.Value            = item.First().Incid;
                    cond.CloseParentheses = String.Empty;
                    whereClause.Add(cond);

                    cond = new SqlFilterCondition();
                    cond.BooleanOperator  = "AND";
                    cond.OpenParentheses  = String.Empty;
                    cond.Column           = _incidMMTable.incidColumn;
                    cond.Table            = condTable;
                    cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
                    cond.Operator         = "<=";
                    cond.Value            = item.Last().Incid;
                    cond.CloseParentheses = ")";
                    whereClause.Add(cond);
                }
            }

            // Any Incids that are not part of a continuous series of at least
            // three Incid must be queried using an "=" or "IN" sql operator.
            // So loop through all these Incids and lump them together into
            // strings of 254 Incids at a time so that each string can be used
            // in an "IN" statement.
            int i = 0;

            while (i < inList.Count)
            {
                int      numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
                string[] oneList  = new string[numElems];
                inList.CopyTo(i, oneList, 0, numElems);

                cond = new SqlFilterCondition();
                cond.BooleanOperator  = "OR";
                cond.OpenParentheses  = "(";
                cond.Column           = _incidMMTable.incidColumn;
                cond.Table            = condTable;
                cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
                //---------------------------------------------------------------------
                // FIX: 001 Improve speed of 'Select current Incid on Map'
                // Use " INCID =" in SQL statement instrad of "INCID IN ()"
                // if there is only on item in the list (as it is much quicker)
                if (inList.Count == 1)
                {
                    cond.Operator = "=";
                }
                else
                {
                    cond.Operator = "IN ()";
                }
                //---------------------------------------------------------------------
                cond.Value            = String.Join(",", oneList);
                cond.CloseParentheses = ")";
                whereClause.Add(cond);

                i += numElems;
            }

            return(whereClause);
        }