コード例 #1
0
        public static void CombineSQL(System.Web.UI.DataSourceView _dataSourceView, string _columnName, Object _valueFirst, Object _valueLast)
        {
            if ((_valueFirst == null || _valueFirst.ToString().Trim() == "") && (_valueLast == null || _valueLast.ToString().Trim() == ""))
            {
                return;
            }

            if (_valueFirst == null || _valueFirst.ToString().Trim() == "")
            {
                _valueLast = _valueLast.ToString().Trim();
                CombineSQL(_dataSourceView, _columnName, _valueLast, EnumSQLOperator.Equals);
                return;
            }
            if (_valueLast == null || _valueLast.ToString().Trim() == "")
            {
                _valueFirst = _valueFirst.ToString().Trim();
                CombineSQL(_dataSourceView, _columnName, _valueFirst, EnumSQLOperator.Equals);
                return;
            }
            _valueFirst = _valueFirst.ToString().Trim();
            _valueLast  = _valueLast.ToString().Trim();
            if (String.Compare(_valueFirst.ToString(), _valueLast.ToString()) > 0)
            {
                Object _temp;
                _temp       = _valueFirst;
                _valueFirst = _valueLast;
                _valueLast  = _temp;
            }

            CombineSQL(_dataSourceView, _columnName, _valueFirst, EnumSQLOperator.GreaterThanOrEqual);
            CombineSQL(_dataSourceView, _columnName, _valueLast, EnumSQLOperator.LessThanOrEqual);
        }
コード例 #2
0
        public static void CombineSQL(System.Web.UI.DataSourceView _dataSourceView, string _columnName, Object _value, EnumSQLOperator _op)
        {
            string _parameterName, _parameterName1 = "";

            int _parameterCount = 0;

            if ((_columnName == null) || (_columnName.Trim() == ""))
            {
                return;
            }

            if (!(_op == EnumSQLOperator.IsNull || _op == EnumSQLOperator.IsNotNull))
            {
                if (_value == null || _value.ToString() == "")
                {
                    return;
                }

                if (_dataSourceView is System.Web.UI.WebControls.SqlDataSourceView)
                {
                    _parameterCount = ((System.Web.UI.WebControls.SqlDataSourceView)_dataSourceView).SelectParameters.Count;
                }
                else
                {
                    return;
                }

                //_parameterName = "@" + _columnName.Replace('.', '_');
                _parameterName = "@__para" + Convert.ToString(_parameterCount + 1);
                //_parameterName1 = _columnName.Replace('.', '_');
                _parameterName1 = "__para" + Convert.ToString(_parameterCount + 1);
            }
            CombineSQL(_dataSourceView, _columnName, _value, _parameterName1, _op);
        }
コード例 #3
0
        public static void CombineSQL(System.Web.UI.DataSourceView _dataSourceView, string _columnName, Object _value, string _paramName, EnumSQLOperator _op)
        {
            StringBuilder _sb = new StringBuilder(256);
            string        _parameterName, _parameterName1, _whereSQL, _originalSelectCommand = "";
            int           _where_pos;

            //int _parameterCount = 0;

            if ((_columnName == null) || (_columnName.Trim() == ""))
            {
                return;
            }
            if (!(_op == EnumSQLOperator.IsNull || _op == EnumSQLOperator.IsNotNull))
            {
                if (_value == null || _value.ToString() == "")
                {
                    return;
                }
            }

            //if (_dataSource is System.Web.UI.WebControls.SqlDataSource)
            //{
            //    _parameterCount = ((System.Web.UI.WebControls.SqlDataSource)_dataSource).SelectParameters.Count;
            //}
            //else
            //{
            //    return;
            //}

            if (!(_dataSourceView is System.Web.UI.WebControls.SqlDataSourceView))
            {
                return;
            }

            _parameterName  = "@" + _paramName;
            _parameterName1 = _paramName;
            _whereSQL       = CombineSQL(_columnName, _parameterName, _op);

            if (_dataSourceView is System.Web.UI.WebControls.SqlDataSourceView)
            {
                _originalSelectCommand = ((System.Web.UI.WebControls.SqlDataSourceView)_dataSourceView).SelectCommand;
            }


            _sb.Append(_originalSelectCommand);
            _sb.Replace("\r\n", "  ");
            _where_pos = _sb.ToString().ToUpper().IndexOf(" WHERE ");
            if (_where_pos >= 0)
            {
                _sb.Insert(_where_pos + 7, _whereSQL + " AND ");
            }
            else
            {
                int _group_pos = _sb.ToString().ToUpper().IndexOf(" GROUP ");
                if (_group_pos >= 0)
                {
                    _sb.Insert(_group_pos, " WHERE " + _whereSQL + " ");
                }
                else
                {
                    int _order_pos = _sb.ToString().ToUpper().IndexOf(" ORDER ");
                    if (_order_pos >= 0)
                    {
                        _sb.Insert(_order_pos, " WHERE " + _whereSQL + " ");
                    }
                    else
                    {
                        _sb.Append(" WHERE " + _whereSQL + " ");
                    }
                }
            }

            if (_dataSourceView is System.Web.UI.WebControls.SqlDataSourceView)
            {
                ((System.Web.UI.WebControls.SqlDataSourceView)_dataSourceView).SelectCommand = _sb.ToString();

                if (_op == EnumSQLOperator.IsNull || _op == EnumSQLOperator.IsNotNull)
                {
                    return;
                }

                string _data = "";
                if (_op == EnumSQLOperator.Like || _op == EnumSQLOperator.NotLike)
                {
                    _data = "%" + _value.ToString() + "%";
                }
                else
                {
                    _data = _value.ToString();
                }


                System.Web.UI.WebControls.SqlDataSourceView _ds = (System.Web.UI.WebControls.SqlDataSourceView)_dataSourceView;
                bool _flag  = false;
                int  _index = 0;
                while (_index < _ds.SelectParameters.Count && _flag == false)
                {
                    if (_ds.SelectParameters[_index].Name.Trim().Contains(_parameterName1))
                    {
                        _flag = true;
                    }
                    _index++;
                }

                if (!_flag)
                {
                    _ds.SelectParameters.Add(_parameterName1, _data);
                }
                else
                {
                    _ds.SelectParameters[_parameterName1].DefaultValue = _data;
                }
            }
        }