private static string GetDateRangeSql(WhereData cp, char _separator, bool _ignoreEmpty = true) { var sSql = string.Empty; var _from = "datediff(day,'{1}',{0}) >=0"; var _to = "datediff(day,'{1}',{0})<=0"; var values = ZConvert.ToString(cp.Value).Split(_separator); if (values.Length == 1) { values = new string[] { values[0], values[0] } } ; if (!string.IsNullOrWhiteSpace(values[0]) || !_ignoreEmpty) { sSql = string.Format(_from, cp.Column, values[0]); } if (!string.IsNullOrWhiteSpace(values[1]) || !_ignoreEmpty) { sSql += (sSql.Length > 0 ? " and " : string.Empty) + string.Format(_to, cp.Column, values[1]); } return(sSql); } }
private static string SQL(WhereData cp, string stringFormat) { var list = cp.Extend.ToList(); list.Insert(0, cp.Value); list.Insert(0, cp.Column); return string.Format(stringFormat, list.ToArray()); }
private static string SQL(WhereData cp, string stringFormat) { var list = cp.Extend.ToList(); list.Insert(0, cp.Value); list.Insert(0, cp.Column); return(string.Format(stringFormat, list.ToArray())); }
private static string GetDateRangeSql(WhereData cp, char _separator, bool _ignoreEmpty = true) { var sSql = string.Empty; var _from = "datediff(day,'{1}',{0}) >=0"; var _to = "datediff(day,'{1}',{0})<=0"; var values = ZConvert.ToString(cp.Value).Split(_separator); if (values.Length == 1) values = new string[] { values[0], values[0] }; if (!string.IsNullOrWhiteSpace(values[0]) || !_ignoreEmpty) sSql = string.Format(_from,cp.Column, values[0]); if (!string.IsNullOrWhiteSpace(values[1]) || !_ignoreEmpty) sSql += (sSql.Length > 0 ? " and " : string.Empty) + string.Format(_to, cp.Column, values[1]); return sSql; }
public static string DtGreaterEqual(WhereData data) { return(SQL(data, "datediff(day,'{1}',{0}) > =0")); }
public static string DtLessEqual(WhereData data) { return SQL(data, "datediff(day,'{1}',{0}) < =0"); }
public static string CharindexEqual(WhereData data) { return(SQL(data, "isnull(CHARINDEX('{0}',{1}),0)=0")); }
public static string NotIn(WhereData data) { return(SQL(data, " {0} not in ({1})")); }
public static string Child(WhereData data) { return(SQL(data, "{0} in (select ID from [dbo].[GetChild]('{0}','{1}'))")); }
public static string DateRange(WhereData data) { return(GetDateRangeSql(data, '到')); }
public static string EndWith(WhereData data) { return(SQL(data, "{0} like '%{1}'")); }
public static string DateRange(WhereData data) { return GetDateRangeSql(data, '到'); }
public static string Between(WhereData data) { return SQL(data, "{0} between '{1}' '{0}'"); }
public static string EndWith(WhereData data) { return SQL(data, "{0} like '%{1}'"); }
public static string StartWithPY(WhereData data) { return SQL(data, "{0} like '{1}%' or [dbo].[fun_getPY]({0}) like N'{1}%'"); }
public static string StartWith(WhereData data) { return SQL(data, "{0} like '{1}%'"); }
public static string Like(WhereData data) { return SQL(data, "{0} like '%{1}%'"); }
public static string In(WhereData data) { return SQL(data, "{0} in ({1})"); }
public static string LessEqualSql(WhereData data) { return(SQL(data, "{0} <= {1}")); }
public static string GreaterEqual(WhereData data) { return SQL(data, "{0} >= '{1}'"); }
public static string Map(WhereData data) { return SQL(data, "{0} in (select {0} from {0} where {0}='{1}')"); }
public static string Between(WhereData data) { return(SQL(data, "{0} between '{1}' '{0}'")); }
public static string Child(WhereData data) { return SQL(data, "{0} in (select ID from [dbo].[GetChild]('{0}','{1}'))"); }
public static string Map(WhereData data) { return(SQL(data, "{0} in (select {0} from {0} where {0}='{1}')")); }
public static string MapChild(WhereData data) { return SQL(data, "{0} in (select {0} from {2} where {3} in (select ID from [dbo].[GetChild]('{4}','{1}')))"); }
public static string MapChild(WhereData data) { return(SQL(data, "{0} in (select {0} from {2} where {3} in (select ID from [dbo].[GetChild]('{4}','{1}')))")); }
public static string DtGreaterEqual(WhereData data) { return SQL(data, "datediff(day,'{1}',{0}) > =0"); }
public static string Replace(WhereData data) { return(SQL(data, " ISNULL({0},0)=0 or {0} not in ({1})")); }
public static string NotNull(WhereData data) { return(SQL(data, "({0} is not null or {0}<>'') ")); }
public static string NotEqual(WhereData data) { return(SQL(data, "{0} <> '{1}'")); }
public static string LessEqual(WhereData data) { return SQL(data, "{0} <= '{1}'"); }
public static string SqlEqual(WhereData data) { return(SQL(data, "{0} = {1}")); }
public static string Like(WhereData data) { return(SQL(data, "{0} like '%{1}%'")); }
public static string IsNull(WhereData data) { return(SQL(data, "{0} is {1}")); }
public static string LikeDate(WhereData data) { return(SQL(data, " CONVERT(varchar,{0},20) like '%{1}%'")); }
public static string GreaterEqual(WhereData data) { return(SQL(data, "{0} >= '{1}'")); }
public static string StartWith(WhereData data) { return(SQL(data, "{0} like '{1}%'")); }
public static string Less(WhereData data) { return(SQL(data, "{0} < '{1}'")); }
public static string StartWithPY(WhereData data) { return(SQL(data, "{0} like '{1}%' or [dbo].[fun_getPY]({0}) like N'{1}%'")); }
public static string DtLessEqual(WhereData data) { return(SQL(data, "datediff(day,'{1}',{0}) < =0")); }
public static string NotEqual(WhereData data) { return SQL(data, "{0} <> '{1}'"); }