public static OEliteDbQueryString Delete <T, TA>(this IRestmeDbQuery <TA> dbQuery, T data,
string whereConditionClause, string[] chosenColumnsOnly = null, string[] columnsToExclude = null,
string initQuery = null)
where T : IRestmeDbEntity where TA : IRestmeDbEntity
{
if (whereConditionClause.IsNullOrEmpty())
{
throw new ArgumentException(
"Delete without condition will remove all data of the requested table(s), the action is disabled for data protection.");
}
var paramValues = dbQuery.PrepareParamValues(RestmeDbQueryType.Delete, data, chosenColumnsOnly,
columnsToExclude);
var query =
$"delete from {dbQuery.CustomDeleteTableSource ?? dbQuery.DefaultTableSource} " +
(whereConditionClause.IsNullOrEmpty() ? "" : $" where ({whereConditionClause}) ");
var result = new OEliteDbQueryString(query, paramValues, dbQuery.DbCentre ?? new RestmeDb());
if (initQuery.IsNotNullOrEmpty())
{
result.InitialQuery = initQuery;
}
return(result);
}
public static OEliteDbQueryString Update <TA>(this IRestmeDbQuery <TA> dbQuery,
Dictionary <string, string> updateColumnNamesMatchedWithPropertyNames, string whereConditionClause,
dynamic paramValues, string initQuery = null) where TA : IRestmeDbEntity
{
if (whereConditionClause.IsNullOrEmpty())
{
throw new ArgumentException(
"Update without condition will update all data records of the requested table(s), the action is disabled for data protection.");
}
if ((updateColumnNamesMatchedWithPropertyNames?.Count).GetValueOrDefault() == 0)
{
throw new ArgumentException("Cannot update without update columns.");
}
var query =
$"update {dbQuery.CustomUpdateTableSource ?? dbQuery.DefaultTableSource} set {string.Join(", ", updateColumnNamesMatchedWithPropertyNames.Select(c => c.Key + " = @" + c.Value))} " +
(whereConditionClause.IsNullOrEmpty() ? "" : $" where ({whereConditionClause}) ");
var result = new OEliteDbQueryString(query, paramValues, dbQuery.DbCentre ?? new RestmeDb());
if (initQuery.IsNotNullOrEmpty())
{
result.InitialQuery = initQuery;
}
return(result);
}
public static OEliteDbQueryString Update <T, TA>(this IRestmeDbQuery <TA> dbQuery, T data,
string whereConditionClause, string[] chosenColumnsOnly = null, string[] columnsToExclude = null,
string initQuery = null)
where T : IRestmeDbEntity where TA : IRestmeDbEntity
{
if (whereConditionClause.IsNullOrEmpty())
{
throw new ArgumentException(
"Update without condition will update all data records of the requested table(s), the action is disabled for data protection.");
}
//var columnsInQuery = dbQuery.MapUpdateColumns<T>(chosenColumnsOnly, columnsToExclude);
var paramUpdateValues = dbQuery.PrepareParamValues(RestmeDbQueryType.Update, data, chosenColumnsOnly,
columnsToExclude);
var paramValues = dbQuery.PrepareParamValues(RestmeDbQueryType.Select, data, chosenColumnsOnly,
columnsToExclude);
var query =
$"update {dbQuery.CustomUpdateTableSource ?? dbQuery.DefaultTableSource} set {string.Join(", ", paramUpdateValues.Select(c => "[" + c.Key + "] = @" + c.Key))} " +
(whereConditionClause.IsNullOrEmpty() ? "" : $" where ({whereConditionClause}) ");
var result = new OEliteDbQueryString(query, paramValues, dbQuery.DbCentre ?? new RestmeDb());
if (initQuery.IsNotNullOrEmpty())
{
result.InitialQuery = initQuery;
}
return(result);
}
public static OEliteDbQueryString Insert <T, TA>(this IRestmeDbQuery <TA> dbQuery, T data,
string[] chosenColumnsOnly = null, string[] columnsToExclude = null, bool expectIdentityScope = true,
string initQuery = null)
where T : IRestmeDbEntity where TA : IRestmeDbEntity
{
var paramValues = dbQuery.PrepareParamValues(RestmeDbQueryType.Insert, data, chosenColumnsOnly,
columnsToExclude);
var query =
$"insert into {dbQuery.CustomInsertTableSource ?? dbQuery.DefaultTableSource}({string.Join(",", paramValues.Select(c => "[" + c.Key + "]"))}) " +
$" values({string.Join(",", paramValues.Select(c => "@" + c.Key))});";
var result = new OEliteDbQueryString(query, paramValues, dbQuery.DbCentre ?? new RestmeDb());
if (initQuery.IsNotNullOrEmpty())
{
result.InitialQuery = initQuery;
}
if (!expectIdentityScope)
{
return(result);
}
query = query + $" SELECT CAST(SCOPE_IDENTITY() as bigint)";
result.Query = query;
result.IsExpectingIdentityScope = true;
return(result);
}
public static OEliteDbQueryString Query <T, TA>(this IRestmeDbQuery <TA> dbQuery,
string whereConditionClause = null,
string orderByClause = null,
string[] chosenColumnsOnly = null, string[] columnsToExclude = null, string initQuery = null)
where T : IRestmeDbEntity where TA : IRestmeDbEntity
{
var tableSource = dbQuery.CustomSelectTableSource ?? dbQuery.DefaultTableSource;
if (orderByClause.IsNullOrEmpty())
{
orderByClause = dbQuery.DefaultOrderByClauseInQuery;
}
var columnsInQuery = dbQuery.MapSelectColumns <T>(chosenColumnsOnly, columnsToExclude);
var selectedColumnsInQuery = columnsInQuery.Values;
var query = $"select {string.Join(", ", selectedColumnsInQuery)} " +
$"from {tableSource} " +
(whereConditionClause.IsNullOrEmpty() ? "" : $"where ({whereConditionClause}) ") +
(orderByClause.IsNullOrEmpty() ? "" : $"order by {orderByClause}");
var result = new OEliteDbQueryString(query, dbCentre: dbQuery.DbCentre ?? new RestmeDb(),
selectColumnNames: selectedColumnsInQuery.ToArray());
result.InitialQuery = initQuery;
return(result);
}
public static Task <IList <T> > FetchEnumerableAsync <T>(this OEliteDbQueryString query,
CommandType?dbCommandType = null, int commandTimeout = 0)
{
try
{
return(query.DbCentre.FetchEnumerableAsync <T>(
$"{(query.InitialQuery.IsNullOrEmpty() ? "" : query.InitialQuery + ";")}{query.Query}",
query.ParamValues,
query.IsPaginated,
dbCommandType, commandTimeout));
}
catch (Exception ex)
{
RestmeDb.Logger?.LogError(ex.Message, ex, query);
throw;
}
}
public static Task <TC> FetchAsync <T, TC>(this OEliteDbQueryString query, CommandType?dbCommandType = null,
int commandTimeout = 0)
where TC : IRestmeDbEntityCollection <T>, new() where T : IRestmeDbEntity
{
try
{
return
(query.DbCentre.FetchAsync <T, TC>(
$"{(query.InitialQuery.IsNullOrEmpty() ? "" : query.InitialQuery + ";")}{query.Query}",
query.ParamValues,
query.IsPaginated, dbCommandType,
commandTimeout));
}
catch (Exception ex)
{
RestmeDb.Logger?.LogError(ex.Message, ex, query);
throw;
}
}
public static async Task <long> ExecuteInsertAsync(this OEliteDbQueryString query,
CommandType?dbCommandType = null, int commandTimeout = 0)
{
try
{
if (query.IsExpectingIdentityScope)
{
return(await query.DbCentre.ExecuteInsertAsync(query.Query, query.ParamValues, dbCommandType,
commandTimeout));
}
return(await query.DbCentre.ExecuteAsync(
$"{(query.InitialQuery.IsNullOrEmpty() ? "" : query.InitialQuery + ";")}{query.Query}",
query.ParamValues,
dbCommandType, commandTimeout));
}
catch (Exception ex)
{
RestmeDb.Logger?.LogError(ex.Message, ex, query);
throw;
}
}
public static OEliteDbQueryString Delete <T>(this IRestmeDbQuery <T> dbQuery, string whereConditionClause,
string initQuery = null)
where T : IRestmeDbEntity
{
if (whereConditionClause.IsNullOrEmpty())
{
throw new ArgumentException(
"Delete without condition will remove all data of the requested table(s), the action is disabled for data protection.");
}
var query =
$"delete from {dbQuery.CustomDeleteTableSource ?? dbQuery.DefaultTableSource} " +
(whereConditionClause.IsNullOrEmpty() ? "" : $" where ({whereConditionClause}) ");
var result = new OEliteDbQueryString(query, null, dbQuery.DbCentre ?? new RestmeDb());
if (initQuery.IsNotNullOrEmpty())
{
result.InitialQuery = initQuery;
}
return(result);
}
public static OEliteDbQueryString Paginated(this OEliteDbQueryString query, int pageIndex, int pageSize,
string outerOrderByClause = null)
{
var offset = pageIndex * pageSize;
if (outerOrderByClause.IsNullOrEmpty() && !query.Query.ToLower().Contains(" order by "))
{
throw new ArgumentException("invalid order by clause.");
}
// string newQuery;
var orderByIndex = query.Query.IndexOf(" order by ", StringComparison.CurrentCultureIgnoreCase);
var queryWithoutOrderby = string.Empty;
if (orderByIndex >= 0)
{
queryWithoutOrderby = query.Query.Substring(0, orderByIndex);
// newQuery = $"select count(*) from ({queryWithoutOrderby}) resultSet;";
}
// else
// newQuery = $"select count(*) from ({query.Query}) resultSet;";
if (pageIndex >= 0 && pageSize > 0)
{
query.IsPaginated = true;
var queryFirstFromClause = query.Query.Substring(0,
query.Query.IndexOf(" from ", StringComparison.InvariantCultureIgnoreCase));
var queryAfterFromClause =
query.Query.Substring(query.Query.IndexOf(" from ", StringComparison.InvariantCultureIgnoreCase));
query.Query =
$" {queryFirstFromClause},count(*) over () as BaseSearchCount {queryAfterFromClause}";
if (outerOrderByClause.IsNullOrEmpty())
{
query.Query = $"{query.Query} " +
$" OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY";
}
else
{
if (query.SelectColumnNames?.Length > 0)
{
var names = query.SelectColumnNames.ToList();
if (names?.Count(item =>
item.Equals("count(*) over () as BaseSearchCount",
StringComparison.InvariantCultureIgnoreCase)) <= 0)
{
names.Add("count(*) over () as BaseSearchCount");
query.SelectColumnNames = names.ToArray();
}
}
if (orderByIndex >= 0)
{
query.Query = queryWithoutOrderby;
}
query.Query =
$"select {(query.SelectColumnNames?.Length > 0 ? string.Join(",", query.SelectColumnNames) : "*")} from ({query.Query}) resultSet order by {outerOrderByClause} " +
$"OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY";
}
// query.Query = newQuery + query.Query;
}
else
{
query.IsPaginated = false;
if (outerOrderByClause.IsNotNullOrEmpty())
{
if (orderByIndex >= 0)
{
query.Query = queryWithoutOrderby;
}
query.Query =
$"{query.Query} order by {outerOrderByClause} ";
// query.Query =
// $"select {(query.SelectColumnNames?.Length > 0 ? string.Join(",", query.SelectColumnNames) : "*")} from ({query.Query}) resultSet order by {outerOrderByClause} ";
}
}
query.Query = query.Query.Trim(';', ' ');
return(query);
}