public List <SqlParameter> GetWhereParameterCollection <T>(List <SearchField> lsf) where T : class
{
DBCommandBetweenOperator betweenOprt = new DBCommandBetweenOperator();
List <SqlParameter> sqlParameters = new List <SqlParameter>();
foreach (SearchField item in lsf)
{
if (!string.IsNullOrEmpty(item.Name) & !string.IsNullOrEmpty(item.Operator) & item.Value != null)
{
string colName = item.Name.Replace(@"""", "").Replace("'", "").Trim();
PropertyInfo colProp = ColumnPropGet.GetInstance.GetColumnProps <T>(colName);
if (colProp != null & !string.IsNullOrEmpty(item.Operator) & !string.IsNullOrEmpty(item.Value.ToString()))
{
string value = item.Value.ToString().Replace("'", "''");
string queryOperator = item.Operator.ToLower().Trim();
string colType = ColumnPropGet.GetInstance.GetColumnType(colProp);
if (colType == "boolean")
{
item.Value = item.Value.ToString().Trim().ToLower() == "true" ? true : false;
}
DbType dbType = TypeBantuan.GetInstance.DictTypes[colType].GetConvertedDbType(colType);
if (queryOperator == operatorbetween)
{
var listParameter = betweenOprt.CreateBetweenParameter(item, colType);
sqlParameters.AddRange(listParameter);
}
else
{
if (queryOperator == operatorlike)
{
value = string.Format(@"'%" + value + "%'");
}
var myParams = new SqlParameter
{
ParameterName = "@" + colProp.Name,
Value = item.Value.ToString().Replace("'", "''"),
DbType = dbType
};
if (!sqlParameters.Contains(myParams))
{
sqlParameters.Add(myParams);
}
}
}
}
}
return(sqlParameters);
}
public string GetWhereParameterizedQuery <T>(List <SearchField> lsf) where T : class
{
DBCommandBetweenOperator betweenOprt = new DBCommandBetweenOperator();
string result = " where 1=1 ";
var activeBoolProp = ColumnPropGet.GetInstance.GetColumnProps <T>("activebool");
if (activeBoolProp == null)
{
activeBoolProp = ColumnPropGet.GetInstance.GetColumnProps <T>("boolactive");
}
if (activeBoolProp != null)
{
result += " and " + activeBoolProp.Name + " = 1 ";
}
foreach (SearchField item in lsf)
{
if (!string.IsNullOrEmpty(item.Name) & !string.IsNullOrEmpty(item.Operator) & item.Value != null)
{
string colName = item.Name.Replace(@"""", "").Replace("'", "").Trim();
PropertyInfo colProp = ColumnPropGet.GetInstance.GetColumnProps <T>(colName);
if (colProp != null)
{
string colType = ColumnPropGet.GetInstance.GetColumnType(colProp);
if (item.Operator == "between")
{
result += " and " + betweenOprt.CreateBetweenWhereClause(item, colType);
}
else
{
result += " and " + colProp.Name + " " + item.Operator + " " + "@" + colProp.Name;
}
}
}
}
return(result);
}