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