public override TSqlQuery Compile(ClauseCompilationContext context)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
string properties = string.Join(TSqlSyntax.FieldsDelimeter, FieldWithValues.Keys);
string values = string.Join(TSqlSyntax.FieldsDelimeter, FieldWithValues.Keys.Select(fieldName => {
object fieldValue = FieldWithValues[fieldName];
if (fieldValue is TSqlStatement tsqlStatement)
{
return(tsqlStatement.GetDescription());
}
string parameterName = SqlBuilderHelper.ComposeParameterName(TableName, fieldName, context);
context.ParameterNames.Add(parameterName);
parameters.Add(parameterName, FieldWithValues[fieldName]);
return(SqlBuilderHelper.PrepareParameterName(parameterName));
}));
StringBuilder sb = new StringBuilder();
sb
.AppendLine($"{TSqlSyntax.Insert} {SqlBuilderHelper.PrepareTableName(TableName)} ({properties})")
.AppendLine($"{TSqlSyntax.Values} ({values})");
return(new TSqlQuery(sb.ToString(), parameters));
}
public WhereDeclaration <TSource> Comparison <TCustom>(Expression <Func <TCustom, object> > binaryExpression)
{
ComparisonClause clause = SqlBuilderHelper.ConvertExpressionToBinaryOperationClause <TCustom>(binaryExpression);
_clauses.Add(clause);
return(this);
}
public override TSqlQuery Compile(ClauseCompilationContext context)
{
if (Value == null)
{
if (!NullValueComparisonOperators.Contains(Operation))
{
throw new InvalidOperationException($"Invalid operation for null value. Should be one of {string.Join(", ", NullValueComparisonOperators)}");
}
return(GetNullValueQuery());
}
Dictionary <string, object> parameters = null;
string valueString;
if (Value is TSqlStatement tsqlStatement)
{
valueString = tsqlStatement.GetDescription();
}
else
{
string parameterName = SqlBuilderHelper.GetUniqueParameterName(SqlBuilderHelper.ComposeParameterName(Field.TableName, Field.FieldName), context);
context.ParameterNames.Add(parameterName);
parameters = new Dictionary <string, object> {
{ parameterName, Value }
};
valueString = SqlBuilderHelper.PrepareParameterName(parameterName);
}
return(new TSqlQuery(
$"{Field.GetFullName()} {SqlBuilderHelper.ConvertBinaryOperationToString(Operation)} {valueString}",
parameters
));
}
public virtual Page <HistoryQueryModel> GetPagedItems(DataTablesPageRequest pageRequest)
{
var query = Sql.Builder
.Select(@"History.*, Series.Title as SeriesTitle, Episodes.Title as EpisodeTitle,
Episodes.SeasonNumber as SeasonNumber, Episodes.EpisodeNumber as EpisodeNumber,
Episodes.Overview as EpisodeOverview")
.From("History")
.InnerJoin("Series")
.On("History.SeriesId = Series.SeriesId")
.InnerJoin("Episodes")
.On("History.EpisodeId = Episodes.EpisodeId");
var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
if (!string.IsNullOrEmpty(pageRequest.Search))
{
var whereClause = string.Join(" OR ", SqlBuilderHelper.GetSearchClause(pageRequest));
if (!string.IsNullOrEmpty(whereClause))
{
query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%");
}
}
var orderBy = string.Join(",", SqlBuilderHelper.GetOrderByClause(pageRequest));
if (!string.IsNullOrEmpty(orderBy))
{
query.Append("ORDER BY " + orderBy);
}
return(_database.Page <HistoryQueryModel>(startPage, pageRequest.DisplayLength, query));
}
public OrderByDeclaration <TSource> OrderBy <TCustom>(Expression <Func <TCustom, object> > fieldSelector, OrderDirection orderDirection)
{
Field field = new Field(typeof(TCustom).Name, SqlBuilderHelper.GetMemberNameFromExpression(fieldSelector));
_orderItems.Add(new OrderByClauseItem(field, orderDirection));
return(this);
}
private ISelectSqlBuilder TypeJoin <TFrom, TTo>(string joinType, string fromField = null, string toField = null, string toTableAlias = null, string fromTableAlias = null,
string fromTableName = null,
string toTableName = null)
{
SetTableName <TFrom>(originalTableName: fromTableName, tableAlias: fromTableAlias);
if (string.IsNullOrEmpty(fromField))
{
fromField = SqlBuilderHelper.GetTableName <TTo>(null, toTableName) + "Id";
}
if (string.IsNullOrEmpty(toField))
{
toField = "Id";
}
var fromExpresstion = $"{GetTypeAlias<TFrom>(fromTableAlias, fromTableName)}.{fromField}";
Joins.Add(string.Format("{0} JOIN {1} {2} ON {3} = {2}.{4}",
joinType.ToUpper(),
SqlBuilderHelper.GetTableName <TTo>(SchemaNamePrefix, toTableName),
GetTypeAlias <TTo>(toTableAlias, toTableName),
fromExpresstion,
toField));
return(this);
}
private Clause CreateClauseFromExpression(BinaryExpression binaryExp)
{
if (binaryExp.NodeType == ExpressionType.AndAlso || binaryExp.NodeType == ExpressionType.OrElse)
{
BinaryExpression leftExpression = binaryExp.Left as BinaryExpression;
if (leftExpression == null)
{
throw new UnsupportedExpressionException("Expression in left side is invalid.", nameof(BinaryExpression));
}
BinaryExpression rightsExpression = binaryExp.Right as BinaryExpression;
if (rightsExpression == null)
{
throw new UnsupportedExpressionException("Expression in right side is invalid.", nameof(BinaryExpression));
}
return(new LogicalClause(
SqlBuilderHelper.MapExpressionTypeToBooleanOperation(binaryExp.NodeType),
CreateClauseFromExpression(leftExpression),
CreateClauseFromExpression(rightsExpression)
));
}
return(new ComparisonClause(
SqlBuilderHelper.MapExpressionTypeToBinaryOperation(binaryExp.NodeType),
SqlBuilderHelper.GetClassNameFromExpression(binaryExp.Left),
SqlBuilderHelper.GetMemberNameFromExpression(binaryExp.Left),
SqlBuilderHelper.GetRightValueFromBinaryExpression(binaryExp)
));
}
public ISelectSqlBuilder Fields <T>(Action <ISelectFieldSelector <T> > selector, string tableName = null, string tableAlias = null)
{
var fieldSelector = new SelectFieldSelector <T>();
selector(fieldSelector);
SetTableName <T>(
() =>
{
if (string.IsNullOrEmpty(TableName) ||
!string.Equals(TableName, SqlBuilderHelper.GetTableName <T>(SchemaNamePrefix, tableName), StringExtensions.CurrentIgnoreCaseStringComparison))
{
return;
}
var alias = tableAlias.WithDefault(fieldSelector.GetFirstTableAlias(typeof(T)));
if (!string.IsNullOrEmpty(alias))
{
TableAlias = TableAlias.WithDefault(alias);
}
}, tableName, tableAlias);
FieldsList.AddRange(fieldSelector.Build());
return(this);
}
private static void Initialize()
{
string address = ConfigurationManager.AppSettings["ExcelSource"];
DataTable tb = GetExcelTable(address);
var tasks = DataConvert <Tasks> .ToList(tb);
List <TaskDto> taskSqls = ConvertToSqlFields(tasks);
StringBuilder sqls = new StringBuilder();
string dbName = ConfigurationManager.AppSettings["dbName"];
sqls.Append("Set IDENTITY_INSERT " + dbName + " on;");
sqls.AppendLine();
//bulk
string sql = SqlBuilderHelper.BulkInsertSql(taskSqls, "AppTasks");
sqls.Append(sql);
//single
//taskSqls.ForEach(task =>
//{
// sqls.Append(SqlBuilderHelper.InsertSql(task, "AppTasks"));
// sqls.AppendLine();
//});
CreateSqlFile(sqls.ToString());
Console.WriteLine(sqls);
Console.WriteLine("Successful.");
Console.ReadLine();
}
public SelectDeclaration <TSource> Field <T>(Expression <Func <T, object> > fieldSelector, string alias)
{
Field field = new Field(typeof(T).Name, SqlBuilderHelper.GetMemberNameFromExpression(fieldSelector), alias);
_selectItems.Add(new FieldSelectItem(field));
return(this);
}
public virtual Page <Log> GetPagedItems(DataTablesPageRequest pageRequest)
{
var query = Sql.Builder
.Select(@"*")
.From("Logs");
var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
if (!string.IsNullOrEmpty(pageRequest.Search))
{
var whereClause = string.Join(" OR ", SqlBuilderHelper.GetSearchClause(pageRequest));
if (!string.IsNullOrEmpty(whereClause))
{
query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%");
}
}
var orderBy = string.Join(",", SqlBuilderHelper.GetOrderByClause(pageRequest));
if (!string.IsNullOrEmpty(orderBy))
{
query.Append("ORDER BY " + orderBy);
}
return(_database.Page <Log>(startPage, pageRequest.DisplayLength, query));
}
public SelectFollowingBuilder <T> Select <T>(TableHint hints, IEnumerable <Expression <Func <T, object> > > fieldSelectors)
{
IEnumerable <Field> fields = fieldSelectors.Select(
f => new Field(typeof(T).Name, SqlBuilderHelper.GetMemberNameFromExpression(f))
);
return(Select <T>(hints, fields));
}
public ComparisonClause(ComparisonOperator operation, Expression <Func <T, object> > fieldSelector, object value)
{
Initialize(
operation,
typeof(T).Name,
SqlBuilderHelper.GetMemberNameFromExpression(fieldSelector),
value
);
}
public ComparisonClause(Expression <Func <T, object> > expression)
{
BinaryExpression binaryExp = SqlBuilderHelper.ConvertToBinaryExpression(expression);
Initialize(
SqlBuilderHelper.MapExpressionTypeToBinaryOperation(binaryExp.NodeType),
typeof(T).Name,
SqlBuilderHelper.GetMemberNameFromExpression(binaryExp.Left),
SqlBuilderHelper.GetRightValueFromBinaryExpression(binaryExp)
);
}
protected SelectDeclaration <TSource> FieldAggregation <TCustom>(AggregateFunction aggregateFunction, Expression <Func <TCustom, object> > fieldSelector, string alias = null)
{
Field field = null;
if (fieldSelector != null)
{
field = new Field(typeof(TCustom).Name, SqlBuilderHelper.GetMemberNameFromExpression(fieldSelector));
}
_selectItems.Add(new FieldAggregateSelectItem(aggregateFunction, field, alias));
return(this);
}
public override TSqlQuery Compile(ClauseCompilationContext context)
{
string parameterName = SqlBuilderHelper.ComposeParameterName(Field.TableName, Field.FieldName, context);
context.ParameterNames.Add(parameterName);
Dictionary <string, object> parameters = new Dictionary <string, object> {
{ parameterName, Value }
};
string query = $"{Field.GetFullName()} {TSqlSyntax.In} {SqlBuilderHelper.PrepareParameterName(parameterName)}";
return(new TSqlQuery(query, parameters));
}
public WhereDeclaration <TSource> Comparison <TCustom>(Expression <Func <TCustom, object> > fieldSelector, ComparisonOperator comparisonOperator, object value)
{
ComparisonClause clause = new ComparisonClause(
comparisonOperator,
typeof(TCustom).Name,
SqlBuilderHelper.GetMemberNameFromExpression(fieldSelector),
value
);
_clauses.Add(clause);
return(this);
}
public string Build()
{
var sql = $"DELETE FROM {SqlBuilderHelper.GetTableName<TClass>(SchemaNamePrefix, TableName)}";
if (Wheres.Count > 0)
{
var where = string.Join(" AND ", Wheres);
sql = $"{sql} WHERE {@where}";
}
return(sql);
}
private void SetTableName <T>(Action additionAction = null, string originalTableName = null)
{
if (!string.IsNullOrEmpty(TableName))
{
additionAction?.Invoke();
return;
}
TableName = SqlBuilderHelper.GetTableName <T>(SchemaNamePrefix, originalTableName);
TableAlias = SqlBuilderHelper.GetAliasForType <T>();
additionAction?.Invoke();
}
public override TSqlQuery Compile(ClauseCompilationContext context)
{
StringBuilder sb = new StringBuilder();
sb.Append($"{JoinType.GetDescription()} {TSqlSyntax.Join} {SqlBuilderHelper.PrepareTableName(JoinedTable)}");
if (TableHints.HasValue)
{
string hints = SqlBuilderHelper.GetTableHintString(TableHints.Value);
sb.Append(" ");
sb.Append($"{TSqlSyntax.With}({hints})");
}
sb.Append(" ");
sb.Append($"{TSqlSyntax.On} {LeftField.GetFullName()} {SqlBuilderHelper.ConvertBinaryOperationToString(Operation)} {RightField.GetFullName()}");
return(new TSqlQuery(sb.ToString()));
}
public UpdateDeclaration <T> Set(Expression <Func <T, object> > fieldExpression, object value, AssignmentOperator assignmentOperator)
{
if (fieldExpression == null)
{
throw new ArgumentNullException(nameof(fieldExpression));
}
string fieldName = SqlBuilderHelper.GetMemberNameFromExpression(fieldExpression);
if (_updateItems.Any(u => string.Compare(u.FieldName, fieldName, true) == 0))
{
throw new ArgumentException($"The column '{fieldName}' is specified more than once in the UPDATE clause. A column cannot be assigned more than one value in the same clause.");
}
_updateItems.Add(new UpdateClauseItem(fieldName, value, assignmentOperator));
return(this);
}
private Dictionary <string, object> GetFieldsToUpsertFromExpression <T>(IEnumerable <Expression <Func <T, object> > > fieldValues)
{
if (fieldValues == null || !fieldValues.Any())
{
throw new ArgumentNullException(nameof(fieldValues));
}
Dictionary <string, object> propsToInsert = new Dictionary <string, object>();
foreach (Expression <Func <T, object> > item in fieldValues)
{
BinaryExpression binaryExp = SqlBuilderHelper.ConvertToBinaryExpression(item);
string fieldName = SqlBuilderHelper.GetMemberNameFromExpression(binaryExp.Left);
object value = SqlBuilderHelper.GetRightValueFromBinaryExpression(binaryExp);
propsToInsert.Add(fieldName, value);
}
return(propsToInsert);
}
public override TSqlQuery Compile(ClauseCompilationContext context)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder queryString = new StringBuilder();
foreach (Clause clause in InnerClauses)
{
if (queryString.Length > 0)
{
queryString.Append($" {SqlBuilderHelper.ConvertBooleanOperationToString(BooleanOperation)} ");
}
TSqlQuery clauseQuery = clause.Compile(context);
queryString.Append(clauseQuery.Query);
foreach (KeyValuePair <string, object> item in clauseQuery.Parameters)
{
parameters.Add(item.Key, item.Value);
}
}
return(new TSqlQuery($"({queryString})", parameters));
}
public override TSqlQuery Compile(ClauseCompilationContext context)
{
string concatenatedFields = TSqlSyntax.AllFieldsSymbol;
if (SelectItems != null & SelectItems.Any())
{
concatenatedFields = string.Join(
TSqlSyntax.FieldsDelimeter,
SelectItems.Select(f => f.Compile())
);
}
StringBuilder sb = new StringBuilder();
sb.Append($"{TSqlSyntax.Select}");
if (Distinct)
{
sb.Append(" ");
sb.Append($"{TSqlSyntax.Distinct}");
}
if (topCount.HasValue)
{
sb.Append(" ");
sb.Append($"{TSqlSyntax.Top} {topCount}");
}
sb.Append(" ");
sb.Append($"{concatenatedFields} {TSqlSyntax.From} {SqlBuilderHelper.PrepareTableName(TableName)}");
if (TableHints.HasValue)
{
string hints = SqlBuilderHelper.GetTableHintString(TableHints.Value);
sb.Append(" ");
sb.Append($"{TSqlSyntax.With}({hints})");
}
return(new TSqlQuery(sb.ToString()));
}
public override TSqlQuery Compile(ClauseCompilationContext context)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder sb = new StringBuilder();
foreach (UpdateClauseItem item in UpdateItems)
{
if (sb.Length > 0)
{
sb.Append(TSqlSyntax.FieldsDelimeter);
}
string fieldName = SqlBuilderHelper.PrepareFieldName(TableName, item.FieldName);
string assignmentOperatorString = SqlBuilderHelper.ConvertAssignmentOperatorToString(item.AssignmentOperator);
string valueString;
if (item.Value is TSqlStatement tsqlStatement)
{
valueString = tsqlStatement.GetDescription();
}
else
{
string parameterName = SqlBuilderHelper.ComposeParameterName(TableName, item.FieldName, context);
context.ParameterNames.Add(parameterName);
parameters.Add(parameterName, item.Value);
valueString = SqlBuilderHelper.PrepareParameterName(parameterName);
}
sb.AppendLine($"{fieldName} {assignmentOperatorString} {valueString}");
}
return(new TSqlQuery(
$"{TSqlSyntax.Update} {SqlBuilderHelper.PrepareTableName(TableName)} {TSqlSyntax.Set} {sb}",
parameters
));
}
internal string WhereEqualsTables(IEnumerable<SqlColumn> columns)
{
return Store(SqlBuilderHelper.WhereEqualsTables(BulkStagingTable, FullyQualifiedTableName, columns));
}
internal string WhereEquals(IEnumerable<SqlColumn> columns)
{
return Store(SqlBuilderHelper.WhereEquals(columns));
}
internal string UpdateSetTables()
{
// "\nSET \t" + Params
return Store(SqlBuilderHelper.UpdateSetTables(BulkStagingTable, FullyQualifiedTableName, Info.UpdateColumns));
}
internal string UpdateSet(IEnumerable<SqlColumn> columns)
{
return Store(SqlBuilderHelper.UpdateSet(columns));
}
internal string SelectIntoStagingTable(IEnumerable<SqlColumn> columns)
{
return Store(SqlBuilderHelper.SelectIntoTableQuery(FullyQualifiedTableName, BulkStagingTable, columns));
}
