/// <summary> /// 生成where 语句 /// </summary> /// <param name="keyValus"></param> /// <param name="withRelation"> </param> /// <returns></returns> internal string CreateWhere(QueryField keyValus, bool withRelation, bool isTablecolom) { if (null == keyValus) { return(string.Empty); } StringBuilder sb = new StringBuilder(); string sqlwhere; string contionstr = withRelation ? keyValus.Condition.ToString() : ""; if (keyValus.Relation == RelationEnum.Between) { sqlwhere = (string.Format(BetweenStr, contionstr, keyValus.FiledName, keyValus.Value, keyValus.Value2)); } else if (keyValus.Relation == RelationEnum.In || keyValus.Relation == RelationEnum.NotIn) { sqlwhere = (string.Format(InOrNotStr, contionstr, keyValus.FiledName, GetRelationStr(keyValus.Relation), keyValus.Value)); } else if (keyValus.Relation == RelationEnum.LikeLeft) { sqlwhere = (string.Format(OtherRelationStr, contionstr, keyValus.FiledName, GetRelationStr(keyValus.Relation), string.Format("{0}", keyValus.Value))); } else if (keyValus.Relation == RelationEnum.LikeRight) { sqlwhere = (string.Format(OtherRelationStr, contionstr, keyValus.FiledName, GetRelationStr(keyValus.Relation), string.Format("{0}", keyValus.Value))); } else if (keyValus.Relation == RelationEnum.Like) { sqlwhere = (string.Format(OtherRelationStr, contionstr, keyValus.FiledName, GetRelationStr(keyValus.Relation), string.Format("{0}", keyValus.Value))); } else if (keyValus.Relation == RelationEnum.IsNotNull || keyValus.Relation == RelationEnum.IsNull) { sqlwhere = string.Format(IsnullorNotnullstr, contionstr, keyValus.FiledName); } else if (isTablecolom) { sqlwhere = (string.Format(OtherRelationStr, contionstr, keyValus.FiledName, GetRelationStr(keyValus.Relation), keyValus.Value)); } else { sqlwhere = (string.Format(OtherRelationStr, contionstr, keyValus.FiledName, GetRelationStr(keyValus.Relation), GetDbTypeString(keyValus.Value, ConvertToDbType(keyValus.Value)))); } sb.AppendLine(sqlwhere); return(sb.ToString()); }
/// <summary> /// 生成where 语句 /// </summary> /// <param name="keyValus"></param> /// <returns></returns> internal string CreateWhere(QueryField keyValus) { return(CreateWhere(keyValus, true)); }
internal string CreateWhere(QueryField keyValus, bool withRelation) { return(CreateWhere(keyValus, withRelation, false)); }
internal string CreateSql(OperateEnum operate, int pagesize, int pageindex) { CurrentOperate = operate; string where = CreateWhereStr(); switch (operate) { case OperateEnum.None: return(_tradstr); case OperateEnum.Insert: string files = string.Empty; string values = string.Empty; if (_keyValues.Count == 0) { GetKetValuesWithEntity(); } foreach (var item in _keyValues) { if (AppCons.IsParmes) { files += item.FiledName + ","; string parmarmName = DbHelper.GetParameterName(item.FiledName); values += parmarmName + ","; AddParmarms(parmarmName, item.DataType, item.Value); } else { string dbvalue = DbHelper.ReturnDBValue(item.DataType, item.Value); if (!string.IsNullOrEmpty(dbvalue)) { files += item.FiledName + ","; values += dbvalue + ","; } } } return(string.Format(SqlCompilation.InsertStr, TbName, files.TrimEnd(','), values.TrimEnd(','))); case OperateEnum.Update: string setvalues = string.Empty; if (_keyValues.Count == 0) { GetKetValuesWithEntity(); } foreach (var item in _keyValues) { switch (item.Type) { case 0: if (AppCons.IsParmes) { string parmarmName = DbHelper.GetParameterName(item.FiledName); AddParmarms(parmarmName, item.DataType, item.Value); setvalues += string.Format("{0}={1},", item.FiledName, parmarmName); } else { string dbvalue = DbHelper.ReturnDBValue(item.DataType, item.Value); if (!string.IsNullOrEmpty(dbvalue)) { setvalues += string.Format("{0}={1},", item.FiledName, dbvalue); } } break; default: setvalues += item.FiledName + ","; break; } } return(string.Format(SqlCompilation.UpdateStr, TbName, setvalues.TrimEnd(','), where)); case OperateEnum.Delete: return(string.Format(SqlCompilation.DeleteStr, TbName, where)); case OperateEnum.Select: { string order = CreateOrdeyStr(); if (ViewList == null || ViewList.Count == 0) { return(string.Format(SqlCompilation.SelectStr, SqlClomns, TbName, where, string.IsNullOrEmpty(order) ? string.Empty : string.Format("order by {0}", order), string.IsNullOrEmpty(_groupByValue) ? string.Empty : string.Format(" group by {0} ", _groupByValue.Trim(',')))); } //视图的处理 StringBuilder jointable = new StringBuilder(); List <string> tables = new List <string>(); foreach (var item in ViewList) { if (!tables.Contains(item.TableName1)) { tables.Add(item.TableName1); jointable.AppendLine(string.Format("{0} as _{0} ", item.TableName1)); } jointable.AppendLine(SqlCompilation.GetJoinEnum(item.Join)); jointable.AppendLine(string.Format("{0} as _{0} ", item.TableName2)); for (int i = 0; i < item.Joinfields.Count; i++) { item.Joinfields[i].FiledName = string.Format("_{0}.{1}", item.TableName1, item.Joinfields[i].FiledName); item.Joinfields[i].Value = string.Format("_{0}.{1}", item.TableName2, item.Joinfields[i].Value); if (i == 0) { jointable.AppendLine("on"); jointable.AppendLine(Sqlcom.CreateWhere(item.Joinfields[i], false)); continue; } jointable.AppendLine(Sqlcom.CreateWhere(item.Joinfields[i])); } } return(string.Format(SqlCompilation.SelectStr, SqlClomns, jointable, where, string.IsNullOrEmpty(order) ? string.Empty : string.Format("order by {0}", order), string.IsNullOrEmpty(_groupByValue) ? string.Empty : string.Format(" group by {0} ", _groupByValue.Trim(',')))); } case OperateEnum.SelectPage: { string order = CreateOrdeyStr(); if (ViewList == null || ViewList.Count == 0) { _selcountstr = string.Format("select count(1) from {0} where 1=1 {1} ", TbName, where); //return string.Format(SqlCompilation.SelectStr, SqlClomns, // TbName, // where, // string.IsNullOrEmpty(order) // ? string.Empty // : string.Format("order by {0}", order), // string.IsNullOrEmpty(_groupByValue) // ? string.Empty // : string.Format(" group by {0} ", _groupByValue.Trim(','))); return(DbHelper.CreatePageString(TbName, SqlClomns, where, _groupByValue.Trim(','), string.IsNullOrEmpty(order) ? string.Empty : order , pageindex, pagesize, out _selcountstr)); } //视图的处理 StringBuilder jointable = new StringBuilder(); List <string> tables = new List <string>(); foreach (var item in ViewList) { string table1 = string.Empty; string table2 = string.Empty; table1 = string.Format("_{0} ", item.TableName1); if (!tables.Contains(item.TableName1)) { tables.Add(item.TableName1); jointable.AppendLine(string.Format("{0} as {1} ", item.TableName1, table1)); } jointable.AppendLine(SqlCompilation.GetJoinEnum(item.Join)); if (!tables.Contains(item.TableName2)) { table2 = string.Format("_{0} ", item.TableName2); } else { table2 = string.Format("_{0}{1} ", item.TableName2, tables.FindAll(p => p.Equals(item.TableName2)).Count); } jointable.AppendLine(string.Format("{0} as {1} ", item.TableName2, table2)); tables.Add(item.TableName2); for (int i = 0; i < item.Joinfields.Count; i++) { QueryField field = new QueryField(); field.FiledName = string.Format("{0}.{1}", table1, item.Joinfields[i].FiledName); field.Value = string.Format("{0}.{1}", table2, item.Joinfields[i].Value); if (i == 0) { jointable.AppendLine("on"); jointable.AppendLine(Sqlcom.CreateWhere(field, false, true)); continue; } jointable.AppendLine(Sqlcom.CreateWhere(field)); } } return(DbHelper.CreatePageString(jointable.ToString(), SqlClomns, where, _groupByValue.Trim(','), string.IsNullOrEmpty(order) ? string.Empty : order , pageindex, pagesize, out _selcountstr)); } } return(string.Empty); }