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