/// <summary> /// 生成UpdateSQL /// </summary> private static string GetUpdateSql(ICTableInfo tableInfo, List <ICTableField> fields) { string tableName = tableInfo.FTableName; //var fields = ICTableDAL.GetTableFields(tableInfo.FID, tableInfo.FTableName); if (fields.Find(x => x.bUpdate) == null) { return(null); } StringBuilder sb = new StringBuilder(); sb.Append("update " + tableName + " set "); fields.ForEach(x => { if (x.bUpdate) { sb.Append(x.FFieldName + " = @" + x.FFieldName + ", "); } }); sb.Remove(sb.Length - 2, 2); sb.Append(" where " + tableInfo.FPKName + " = @" + tableInfo.FPKName); return(sb.ToString()); }
/// <summary> /// 生成InsertSQL /// </summary> private static string GetInsertSql(ICTableInfo tableInfo, List <ICTableField> fields) { StringBuilder sb = new StringBuilder(); StringBuilder sb2 = new StringBuilder(); string tableName = tableInfo.FTableName; //var fields = ICTableDAL.GetTableFields(tableInfo.FID, tableInfo.FTableName); if (fields.Find(x => x.bInsert) == null) { return(null); } sb.Append("insert into " + tableName + "("); fields.ForEach(x => { if (x.bInsert) { sb.Append(x.FFieldName + ", "); sb2.Append("@" + x.FFieldName + ", "); } }); sb.Remove(sb.Length - 2, 2); sb2.Remove(sb2.Length - 2, 2); sb.Append(")"); if (tableInfo.PK_identity) { sb.Append(" output inserted." + tableInfo.FPKName); } sb.Append(" values ("); sb.Append(sb2.ToString() + ")"); return(sb.ToString()); }
/// <summary> /// 生成SelectSQL /// </summary> private static string GetSelectSql(ICTableInfo tableInfo, List <ICTableField> fields) { string tableName = tableInfo.FTableName; //查询字段 List <string> fieldList = new List <string>(); //查询表 HashSet <string> hashset = new HashSet <string>(); fields.ForEach(x => { if (!string.IsNullOrEmpty(x.FFormula)) { fieldList.Add(x.FFormula + " as " + x.FFieldName); } else if (!string.IsNullOrEmpty(x.FRefTable)) { //查询字段(table.FieldName) var refTable = string.IsNullOrEmpty(x.FRefTableAliases) ? x.FRefTable : x.FRefTableAliases; fieldList.Add(refTable + "." + x.FRefField + " as " + x.FFieldName); //查询关联表(table as aliasesName) string str = string.IsNullOrEmpty(x.FRefTableAliases) ? x.FRefTable : x.FRefTable + " as " + x.FRefTableAliases; if (x.FKeyField.Contains(".")) { str += " on " + x.FKeyField + " = " + refTable + "." + x.FRefKeyField; //附加关联条件 if (!string.IsNullOrEmpty(x.FConditions)) { str += " and " + x.FConditions; } } else { str += " on T." + x.FKeyField + " = " + refTable + "." + x.FRefKeyField; //附加关联条件 if (!string.IsNullOrEmpty(x.FConditions)) { str += " and " + refTable + "." + x.FConditions; } } hashset.Add(str); } else { fieldList.Add("T." + x.FFieldName); } }); StringBuilder sb = new StringBuilder("select "); sb.Append(string.Join(",", fieldList.ToArray())); sb.Append(" from " + tableName + " T"); if (hashset.Count > 0) { sb.Append(" left join "); } sb.Append(string.Join(" left join ", hashset.ToArray())); return(sb.ToString()); }