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); }
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); }
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; } }
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); }
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); }
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; } } }