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 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 <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 OEliteDbQueryString Query <T>(this IRestmeDbQuery <T> dbQuery, string whereConditionClause = null,
string orderByClause = null,
string[] chosenColumnsOnly = null, string[] columnsToExclude = null, string initQuery = null)
where T : IRestmeDbEntity
{
return(Query <T, T>(dbQuery, whereConditionClause, orderByClause, chosenColumnsOnly,
columnsToExclude, initQuery));
}
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 FullQuery <T>(this IRestmeDbQuery <T> dbQuery, string fullQuery)
where T : IRestmeDbEntity
{
return(new OEliteDbQueryString(fullQuery, dbCentre: dbQuery.DbCentre ?? new RestmeDb()));
}