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