public override SqlProvider FormatInsert <T>(T entity)
{
var paramsAndValuesSql = FormatInsertParamsAndValues(entity);
if (Context.Set.IfNotExistsExpression == null)
{
SqlString = $"INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]})";
}
else
{
var ifnotexistsWhere = ResolveExpression.ResolveWhere(Context.Set.IfNotExistsExpression, "INT_");
SqlString = string.Format(@"INSERT INTO {0}({1})
SELECT {2}
FROM DUAL
WHERE NOT EXISTS(
SELECT 1
FROM {0}
{3}
); ", FormatTableName(false), paramsAndValuesSql[0], paramsAndValuesSql[1], ifnotexistsWhere.SqlCmd);
Params.AddDynamicParams(ifnotexistsWhere.Param);
}
return(this);
}
/// <summary>
/// 使用sql查询条件
/// </summary>
/// <param name="sqlWhere"></param>
/// <param name="param"></param>
/// <returns></returns>
public QuerySet <T> Where(string sqlWhere, object param = null)
{
WhereBuilder.Append(" AND " + sqlWhere);
if (param != null)
{
Params.AddDynamicParams(param, true);
}
return(this);
}
/// <summary>
/// 使用sql查询条件
/// </summary>
/// <param name="sqlWhere"></param>
/// <param name="param"></param>
/// <returns></returns>
public ICommand <T> Where(string sqlWhere, object param = null)
{
WhereBuilder.Append(" AND " + sqlWhere);
if (param != null)
{
Params.AddDynamicParams(param);
}
return(this);
}
public QuerySet <T> Where <TWhere1, TWhere2>(Expression <Func <TWhere1, TWhere2, bool> > exp)
{
var sqlWhere = new WhereExpression(exp, $"Where_{Params.ParameterNames.Count()}_", SqlProvider.ProviderOption);
WhereBuilder.Append(sqlWhere.SqlCmd);
if (sqlWhere.Param != null)
{
Params.AddDynamicParams(sqlWhere.Param);
}
return(this);
}
public override SqlProvider FormatUpdateSelect <T>(Expression <Func <T, T> > updator)
{
var update = ResolveExpression.ResolveUpdate(updator);
var selectSql = ResolveExpression.ResolveSelectOfUpdate(EntityCache.QueryEntity(typeof(T)), Context.Set.SelectExpression);
var whereSql = ResolveExpression.ResolveWhereList();
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false, false)} {update.SqlCmd} {selectSql} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdate <T>(Expression <Func <T, T> > updateExpression)
{
var update = ResolveExpression.ResolveUpdate(updateExpression);
ProviderOption.IsAsName = false;
var whereSql = ResolveExpression.ResolveWhereList();
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false, false)} {update.SqlCmd} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdate <T>(T entity)
{
var update = ResolveExpression.ResolveUpdate <T>(a => entity);
var where = ResolveExpression.ResolveWhere(entity);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdate <T>(Expression <Func <T, T> > updateExpression)
{
var update = ResolveExpression.ResolveUpdate(updateExpression);
var where = ResolveExpression.ResolveWhere(Context.Set.WhereExpression);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdate <T>(Expression <Func <T, T> > updateExpression)
{
var update = ResolveExpression.ResolveUpdate(updateExpression);
var whereSql = string.Empty;
//表查询条件
var whereParamsList = ResolveExpression.ResolveWhereList(Context.Set, ref whereSql, Params, null, false);
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false, false)} {update.SqlCmd} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdate <T>(Expression <Func <T, T> > updateExpression)
{
var update = ResolveExpression.ResolveUpdate(updateExpression);
var fromTableSql = ProviderOption.CombineFieldName(FormatTableName(false, false).Trim());
ProviderOption.IsAsName = false;
var whereSql = ResolveExpression.ResolveWhereList(Context.Set, Params);
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {fromTableSql} {update.SqlCmd} {whereSql}";
return(this);
}
/// <summary>
/// 连表
/// </summary>
/// <typeparam name="TInner">副表</typeparam>
/// <param name="expression">条件</param>
/// <param name="joinMode">连接类型</param>
/// <param name="isDisField">是否需要显示表字段</param>
/// <returns></returns>
public QuerySet <T> Join <TInner>(LambdaExpression expression, JoinMode joinMode = JoinMode.LEFT, bool isDisField = true)
{
var joinWhere = new WhereExpression(expression, $"{Params.ParameterNames.Count()}", SqlProvider);
Regex whereRex = new Regex("AND");
string tableName = SqlProvider.FormatTableName(false, true, typeof(TInner));
SqlProvider.JoinList.Add(new JoinAssTable()
{
Action = JoinAction.Sql,
JoinSql = $"{joinMode.ToString()} JOIN {tableName} ON { whereRex.Replace(joinWhere.SqlCmd, "", 1)}",
TableType = (isDisField ? typeof(TInner) : null)
});
if (joinWhere.Param != null)
{
Params.AddDynamicParams(joinWhere.Param, true);
}
return(this);
}
public override SqlProvider FormatUpdateSelect <T>(Expression <Func <T, T> > updator)
{
var update = ResolveExpression.ResolveUpdate(updator);
var fromTableSql = ProviderOption.CombineFieldName(FormatTableName(false, false).Trim());
var selectSql = ResolveExpression.ResolveSelectOfUpdate(EntityCache.QueryEntity(typeof(T)), Context.Set.SelectExpression);
var whereSql = string.Empty;
//表查询条件
var whereParamsList = ResolveExpression.ResolveWhereList(Context.Set, ref whereSql, Params, null, false);
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {fromTableSql} {update.SqlCmd} {selectSql} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdateSelect <T>(Expression <Func <T, T> > updator)
{
var update = ResolveExpression.ResolveUpdate(updator);
var selectSql = ResolveExpression.ResolveSelectOfUpdate(typeof(T).GetProperties(), SetContext.SelectExpression);
var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
var topNum = SetContext.TopNum;
var topSql = topNum.HasValue ? $" TOP ({topNum.Value})" : "";
SqlString = $"UPDATE {topSql} {FormatTableName(false)} WITH ( UPDLOCK, READPAST ) {update.SqlCmd} {selectSql} {whereSql}";
return(this);
}
public override SqlProvider FormatUpdateSelect <T>(Expression <Func <T, T> > updator)
{
var keyField = ProviderOption.CombineFieldName(typeof(T).GetKeyPropertity().GetColumnAttributeName());
var update = ResolveExpression.ResolveUpdate(updator);
var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SelectExpression, false);
var where = ResolveExpression.ResolveWhere(WhereExpression);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
var topNum = TopNum;
var limitSql = topNum.HasValue ? " LIMIT " + topNum.Value : "";
var tableName = FormatTableName(false);
SqlString = $"UPDATE {tableName} {update.SqlCmd} WHERE {keyField} IN (SELECT {keyField} FROM {tableName} {whereSql} {limitSql} FOR UPDATE SKIP LOCKED) RETURNING {selectSql}";
return(this);
}