Пример #1
0
        public static string CombineSQL(string _columnName, string _parameterName, EnumSQLOperator op, Object _value, System.Data.SqlDbType datatype)
        {
            string _rtn;

            if (datatype == System.Data.SqlDbType.DateTime)
            {
                _columnName = " convert(datetime, convert(char(10), " + _columnName + " )) ";
            }

            switch (op)
            {
            case EnumSQLOperator.Equals:
                if (datatype == System.Data.SqlDbType.DateTime)
                {
                    _rtn = _columnName + " = '" + _value + "' ";
                }
                else
                {
                    _rtn = _columnName + " = " + _parameterName;
                }
                break;

            case EnumSQLOperator.NotEqual:
                if (datatype == System.Data.SqlDbType.DateTime)
                {
                    _rtn = _columnName + " <> '" + _value + "' ";
                }
                else
                {
                    _rtn = _columnName + " <> " + _parameterName;
                }
                break;

            case EnumSQLOperator.GreaterThan:
                if (datatype == System.Data.SqlDbType.DateTime)
                {
                    _rtn = _columnName + " = '" + _value + "' ";
                }
                _rtn = _columnName + " > " + _parameterName;
                break;

            case EnumSQLOperator.GreaterThanOrEqual:
                _rtn = _columnName + " >= " + _parameterName;
                break;

            case EnumSQLOperator.LessThan:
                _rtn = _columnName + " < " + _parameterName;
                break;

            case EnumSQLOperator.LessThanOrEqual:
                _rtn = _columnName + " <= " + _parameterName;
                break;

            case EnumSQLOperator.Like:
                _rtn = _columnName + " Like '%" + _value.ToString() + "%' ";
                break;

            case EnumSQLOperator.NotLike:
                _rtn = " NOT " + _columnName + " Like " + _parameterName;
                break;

            case EnumSQLOperator.In:
                _rtn = _columnName + "IN" + _parameterName;
                break;

            case EnumSQLOperator.NotIn:
                _rtn = " NOT " + _columnName + " IN " + _parameterName;
                break;

            case EnumSQLOperator.Blanks:
                _rtn = _columnName + " = " + _parameterName;
                break;

            case EnumSQLOperator.NoneBlanks:
                _rtn = " NOT " + _columnName + " = " + _parameterName;
                break;

            case EnumSQLOperator.IsNull:
                _rtn = " " + _columnName + " is null ";
                break;

            case EnumSQLOperator.IsNotNull:
                _rtn = " " + _columnName + " is not null ";
                break;

            default:
                _rtn = "";
                break;
            }

            return(_rtn);
        }
Пример #2
0
        public static void CombineSQL(System.Web.UI.DataSourceControl _dataSource, 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 (_dataSource is System.Web.UI.WebControls.SqlDataSource)
                {
                    _parameterCount = ((System.Web.UI.WebControls.SqlDataSource)_dataSource).SelectParameters.Count;
                }
                else
                {
                    return;
                }

                //_parameterName = "@" + _columnName.Replace('.', '_');
                _parameterName = "@__para" + Convert.ToString(_parameterCount + 1);
                //_parameterName1 = _columnName.Replace('.', '_');
                _parameterName1 = "__para" + Convert.ToString(_parameterCount + 1);
            }
            CombineSQL(_dataSource, _columnName, _value, _parameterName1, _op);
        }
Пример #3
0
        public static void CombineSQL(System.Data.SqlClient.SqlCommand _sqlCommand, string _columnName, Object _value, EnumSQLOperator _op, System.Data.SqlDbType _datatype, EnumLogical _enumlogical)
        {
            StringBuilder _sb = new StringBuilder(256);
            string        _parameterName, _whereSQL;
            int           _where_pos;
            string        _logical;

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

            switch (_enumlogical)
            {
            case EnumLogical.AND:
                _logical = " AND ";
                break;

            case EnumLogical.OR:
                _logical = " OR ";
                break;

            default:
                _logical = " AND ";
                break;
            }

            _parameterName = "@" + _columnName.Replace('.', '_');
            _parameterName = "@__para" + Convert.ToString(_sqlCommand.Parameters.Count + 1);



            _whereSQL = CombineSQL(_columnName, _parameterName, _op, _value, _datatype);

            _sb.Append(_sqlCommand.CommandText);
            _sb.Replace("\r\n", "  ");
            //_where_pos = _sb.ToString().ToUpper().IndexOf(" WHERE ");
            //if (_where_pos >= 0)
            //{
            //    _sb.Insert(_where_pos + 7, " ( " + _whereSQL + ") " + _logical);
            //}
            //else
            //{
            //    _sb.Append(" WHERE " + " ( " + _whereSQL + ") ");
            //}
            _where_pos = _sb.ToString().ToUpper().IndexOf(" WHERE ");
            if (_where_pos >= 0)
            {
                _sb.Insert(_where_pos + 7, _whereSQL + _logical);
            }
            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 + " ");
                    }
                }
            }



            //System.Windows.Forms.MessageBox.Show(_sb.ToString());
            _sqlCommand.CommandText = _sb.ToString();
            if (_op == EnumSQLOperator.IsNull || _op == EnumSQLOperator.IsNotNull)
            {
                return;
            }
            //if (op != EnumSQLOperator.Like && datatype != System.Data.SqlDbType.DateTime)
            if (_op != EnumSQLOperator.Like)
            {
                _sqlCommand.Parameters.Add(_parameterName, _datatype);
                _sqlCommand.Parameters[_parameterName].Value = _value;
            }
        }
Пример #4
0
 public static void CombineSQL(System.Data.SqlClient.SqlCommand _sqlCommand, string _columnName, Object _value, EnumSQLOperator _enumSQLOperator)
 {
     CombineSQL(_sqlCommand, _columnName, _value, _enumSQLOperator, System.Data.SqlDbType.NVarChar, EnumLogical.AND);
 }
Пример #5
0
        public static string CombineSQL(string _columnName, string _parameterName, EnumSQLOperator _op)
        {
            string _rtn;

            switch (_op)
            {
            case EnumSQLOperator.Equals:
                _rtn = _columnName + " = " + _parameterName;
                break;

            case EnumSQLOperator.NotEqual:
                _rtn = _columnName + " <> " + _parameterName;
                break;

            case EnumSQLOperator.GreaterThan:
                _rtn = _columnName + " > " + _parameterName;
                break;

            case EnumSQLOperator.GreaterThanOrEqual:
                _rtn = _columnName + " >= " + _parameterName;
                break;

            case EnumSQLOperator.LessThan:
                _rtn = _columnName + " < " + _parameterName;
                break;

            case EnumSQLOperator.LessThanOrEqual:
                _rtn = _columnName + " <= " + _parameterName;
                break;

            case EnumSQLOperator.Like:
                _rtn = _columnName + " Like " + _parameterName;
                break;

            case EnumSQLOperator.NotLike:
                _rtn = " NOT " + _columnName + " Like " + _parameterName;
                break;

            case EnumSQLOperator.In:
                _rtn = _columnName + " IN " + _parameterName;
                break;

            case EnumSQLOperator.NotIn:
                _rtn = " NOT " + _columnName + " IN " + _parameterName;
                break;

            case EnumSQLOperator.Blanks:
                _rtn = _columnName + " = " + _parameterName;
                break;

            case EnumSQLOperator.NoneBlanks:
                _rtn = " NOT " + _columnName + " = " + _parameterName;
                break;

            case EnumSQLOperator.IsNull:
                _rtn = " " + _columnName + " is null ";
                break;

            case EnumSQLOperator.IsNotNull:
                _rtn = " " + _columnName + " is not null ";
                break;

            default:
                _rtn = "";
                break;
            }

            return(_rtn);
        }
Пример #6
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;
                }
            }
        }