public override SqlProvider FormatInsert <T>(T entity)
{
var paramsAndValuesSql = FormatInsertParamsAndValues(entity);
if (IfNotExistsExpression == null)
{
SqlString = $"INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]})";
}
else
{
var ifnotexistsWhere = ResolveExpression.ResolveWhere(IfNotExistsExpression, "INT_");
SqlString = string.Format(@"INSERT INTO {0}({1})
SELECT {2}
WHERE NOT EXISTS(
SELECT 1
FROM {0}
{3}
); ", FormatTableName(false), paramsAndValuesSql[0], paramsAndValuesSql[1], ifnotexistsWhere.SqlCmd);
Params.AddDynamicParams(ifnotexistsWhere.Param);
}
return(this);
}
public override SqlProvider FormatToPageList <T>(int pageIndex, int pageSize)
{
var orderbySql = ResolveExpression.ResolveOrderBy(OrderbyExpressionList);
if (string.IsNullOrEmpty(orderbySql))
{
throw new DapperExtensionException("order by takes precedence over pagelist");
}
var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SelectExpression);
var fromTableSql = FormatTableName();
var nolockSql = ResolveExpression.ResolveWithNoLock(NoLock);
var whereParams = ResolveExpression.ResolveWhere(WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"SELECT COUNT(1) {fromTableSql} {nolockSql} {whereSql};";
SqlString += $"{selectSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql} LIMIT {pageSize} OFFSET {(pageIndex - 1) * pageSize}";
return(this);
}
public override SqlProvider FormatDelete()
{
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.ResolveWhere(WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"DELETE {fromTableSql} {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 FormatCount()
{
var selectSql = "SELECT COUNT(*)";
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.ResolveWhere(Context.Set.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"{selectSql} {fromTableSql} {whereSql} ";
return(this);
}
public override SqlProvider FormatSum <T>(LambdaExpression lambdaExpression)
{
var selectSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.ResolveWhere(Context.Set.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"{selectSql} {fromTableSql} {whereSql} ";
return(this);
}
public override SqlProvider FormatExists()
{
var selectSql = "SELECT 1";
var fromTableSql = FormatTableName();
var nolockSql = ResolveExpression.ResolveWithNoLock(NoLock);
var whereParams = ResolveExpression.ResolveWhere(WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} LIMIT 1";
return(this);
}
public override SqlProvider FormatGet <T>()
{
var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), Context.Set.SelectExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.ResolveWhere(Context.Set.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.ResolveOrderBy(Context.Set.OrderbyExpressionList);
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} LIMIT 1";
return(this);
}
public override SqlProvider FormatToList <T>()
{
var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), Context.Set.SelectExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.ResolveWhere(Context.Set.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.ResolveOrderBy(Context.Set.OrderbyExpressionList);
var topNum = DataBaseContext <T>().QuerySet.TopNum;
var limitSql = topNum.HasValue ? " LIMIT " + topNum.Value : "";
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
return(this);
}
public override SqlProvider FormatToList <T>()
{
var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SelectExpression);
var fromTableSql = FormatTableName();
var nolockSql = ResolveExpression.ResolveWithNoLock(NoLock);
var whereParams = ResolveExpression.ResolveWhere(WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.ResolveOrderBy(OrderbyExpressionList);
var topNum = TopNum;
var limitSql = topNum.HasValue ? " LIMIT " + topNum.Value : "";
SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql} {limitSql}";
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);
}
public PostgreSqlProvider()
{
ProviderOption = new ProviderOption(OpenQuote, CloseQuote, ParameterPrefix);
ResolveExpression.InitOption(ProviderOption);
}