public string GetSql(DBTablePropertie <T> Entity)
{
lock (_selectLocker)
{
StringBuilder SELECTSql = new StringBuilder();
#region 插入SELECT字段
SELECTSql.Append(SqlModel.SELECT);
#endregion
#region 插入{TOP或其他}字段
SELECTSql.Append(_SELECTLimit);
#endregion
#region 插入查询字段
if (String.IsNullOrWhiteSpace(_SELECTColumn))
{
_SELECTColumn = String.Join(" , ", Entity.ColumnsList);
}
#region 添加函数
if (!String.IsNullOrWhiteSpace(_SELECTFunction))
{
SELECTSql.Append(_SELECTFunction);
_SELECTColumn = $"({_SELECTColumn})";
}
#endregion
SELECTSql.Append(_SELECTColumn);
#endregion
#region 插入FROM
SELECTSql.Append(SqlModel.FROM);
SELECTSql.Append(Entity.TableName);
#endregion
#region 插入WHERE条件
if (!String.IsNullOrEmpty(_SELECTWhere))
{
if (!_SELECTWhere.Trim().StartsWith(SqlModel.WHERE))
{
SELECTSql.Append(SqlModel.WHERE);
}
SELECTSql.Append(_SELECTWhere);
}
#endregion
#region 插入排序
if (!String.IsNullOrWhiteSpace(_SELECTSorting))
{
SELECTSql.Append(SqlModel.ORDERBY);
SELECTSql.Append(_SELECTSorting);
SELECTSql.Append(" ");//TODO 可以再次优化查询排序生成语句
SELECTSql.Append(_SELECTSortingType);
}
#endregion
return(SELECTSql.ToString());
}
}
public string GetSql(DBTablePropertie <T> Entity)
{
lock (_InsertSqlLocker)
{
StringBuilder InsertSql = new StringBuilder();
InsertSql.Append(SqlModel.INSERT);
InsertSql.Append(Entity.TableName);
InsertSql.Append($" ( {String.Join(" , ", Entity.ColumnsList)} ) ");
InsertSql.Append(SqlModel.VALUES);
InsertSql.Append($" ( {String.Join(" , ", Entity.Attributes.Values)} ) ");
return(InsertSql.ToString());
}
}
public string GetSql(DBTablePropertie<T> Entity)
{
if (String.IsNullOrEmpty(Entity.Key) || !Entity.Attributes.ContainsKey(Entity.Key))
throw new Exception("未找到主键");
if (Entity.Attributes[Entity.Key] == null)
throw new Exception("主键不可为空");
lock (_UpdateSqlLocker)
{
var UpdateSqlBu = new StringBuilder();
UpdateSqlBu.Append(SqlModel.UPDATE);
UpdateSqlBu.Append(Entity.TableName);
UpdateSqlBu.Append(SqlModel.SET);
UpdateSqlBu.Append(String.Join(" , ", Entity.Attributes.Select(m => $"{m.Key.ToString()} = {m.Value.ToString()}")));
UpdateSqlBu.Append(SqlModel.WHERE);
UpdateSqlBu.Append(Entity.Key);
UpdateSqlBu.Append(" = ");
UpdateSqlBu.Append(Entity.Attributes[Entity.Key]);
return UpdateSqlBu.ToString();
}
}
public string GetSql(DBTablePropertie <T> Entity)
{
if (String.IsNullOrEmpty(Entity.Key) || !Entity.Attributes.ContainsKey(Entity.Key))
{
throw new Exception("未找到主键");
}
if (Entity.Attributes[Entity.Key] == null)
{
throw new Exception("主键不可为空");
}
lock (_DeleteSqlLocker)
{
var DeleteSqlBu = new StringBuilder();
DeleteSqlBu.Append(SqlModel.DELETE);
DeleteSqlBu.Append(SqlModel.FROM);
DeleteSqlBu.Append(Entity.TableName);
DeleteSqlBu.Append(SqlModel.WHERE);
DeleteSqlBu.Append(Entity.Key);
DeleteSqlBu.Append(" = ");
DeleteSqlBu.Append(Entity.Attributes[Entity.Key]);
return(DeleteSqlBu.ToString());
}
}//TODO 缺少WHERE组装判断
public new string GetSql(DBTablePropertie <T> Entity)
{
lock (_PageSqlLocker)
{
base.SetSelectColumn("ROW_NUMBER() OVER (ORDER BY CreateTime) AS ROW, COUNT(*) OVER() AS TotalNum,*");
var DBSelectSql = base.GetSql(Entity);//查询语句
StringBuilder PageSql = new StringBuilder();
PageSql.Append(SqlModel.SELECT);
PageSql.Append(SqlModel.TOP);
PageSql.Append($" ({_PAGESIZE}) ");
PageSql.Append(" * ");
PageSql.Append(SqlModel.FROM);
PageSql.Append($" ({DBSelectSql}) ");
PageSql.Append(SqlModel.AS);
PageSql.Append(_TableName);
PageSql.Append(SqlModel.WHERE);
PageSql.Append($" {_TableName}.ROW > ({_PAGESIZE * (_PAGEINDEX - 1)})");
PageSql.Append(SqlModel.ORDERBY);
PageSql.Append($" {_TableName}.ROW ");
return(PageSql.ToString());
}
}