/// <summary>
///
/// </summary>
/// <param name="entityType"></param>
/// <param name="inputFields"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static Action <DbCommand, object> CompileDictionaryStringObjectDbParameterSetter(Type entityType,
IEnumerable <DbField> inputFields,
IDbSetting dbSetting)
{
var commandParameterExpression = Expression.Parameter(StaticType.DbCommand, "command");
var entityParameterExpression = Expression.Parameter(StaticType.Object, "entityParameter");
var dbParameterCollectionExpression = Expression.Property(commandParameterExpression,
StaticType.DbCommand.GetProperty("Parameters"));
var dictionaryInstanceExpression = ConvertExpressionToTypeExpression(entityParameterExpression, StaticType.IDictionaryStringObject);
var bodyExpressions = new List <Expression>();
// Clear the parameter collection first
bodyExpressions.Add(GetDbParameterCollectionClearMethodExpression(dbParameterCollectionExpression));
// Iterate the fields
foreach (var dbField in inputFields)
{
var dictionaryParameterExpression = GetDictionaryStringObjectParameterAssignmentExpression(commandParameterExpression,
0,
dictionaryInstanceExpression,
dbField,
dbSetting);
// Add to body
bodyExpressions.Add(dictionaryParameterExpression);
}
// Compile
return(Expression
.Lambda <Action <DbCommand, object> >(Expression.Block(bodyExpressions),
commandParameterExpression,
entityParameterExpression)
.Compile());
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string GetTableName(string tableName,
IDbSetting dbSetting) =>
DataEntityExtension.GetTableName(tableName, dbSetting);
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="tempTableName"></param>
/// <param name="fields"></param>
/// <param name="qualifiers"></param>
/// <param name="primaryField"></param>
/// <param name="identityField"></param>
/// <param name="hints"></param>
/// <param name="dbSetting"></param>
/// <param name="isReturnIdentity"></param>
/// <returns></returns>
private static string GetBulkMergeSqlText(string tableName,
string tempTableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers,
Field primaryField,
Field identityField,
string hints,
IDbSetting dbSetting,
bool isReturnIdentity)
{
// Validate the presence
if (fields?.Any() != true)
{
throw new MissingFieldException("There are no field(s) defined.");
}
if (qualifiers?.Any() != true)
{
throw new MissingFieldException("There is no qualifier field(s) defined.");
}
// Variables needed
var builder = new QueryBuilder();
// Insertable fields
var insertableFields = fields
.Where(field => string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase) == false);
// Updatable fields
var updateableFields = fields
.Where(field => field != identityField && field != primaryField)
.Where(field =>
qualifiers.Any(
q => string.Equals(q.Name, field.Name, StringComparison.OrdinalIgnoreCase)) == false);
// Compose the statement
builder.Clear()
// MERGE T USING S
.Merge()
.TableNameFrom(tableName, dbSetting)
.HintsFrom(hints)
.As("T")
.Using()
.OpenParen()
.Select()
.Top()
.WriteText("100 PERCENT")
//.FieldsFrom(fields, dbSetting)
.WriteText("*") // Including the [__RepoDb_OrderColumn]
.From()
.TableNameFrom(tempTableName, dbSetting);
// Return Identity
if (isReturnIdentity && identityField != null)
{
builder
.OrderBy()
.WriteText("[__RepoDb_OrderColumn]")
.Ascending();
}
// Continuation
builder
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers
.Select(
field => field.AsJoinQualifier("S", "T", dbSetting))
.Join(" AND "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(insertableFields, dbSetting)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(insertableFields, "S", dbSetting)
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(updateableFields, "T", "S", dbSetting);
// Set the output
if (isReturnIdentity == true && identityField != null)
{
builder
.WriteText(string.Concat("OUTPUT INSERTED.", identityField.Name.AsField(dbSetting)))
.As("[Result],")
.WriteText("S.[__RepoDb_OrderColumn]")
.As("[OrderColumn]");
}
// End the builder
builder.End();
// Return the sql
return(builder.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <param name="leftAlias"></param>
/// <param name="rightAlias"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string AsFieldAndAliasField(this Field field,
string leftAlias,
string rightAlias,
IDbSetting dbSetting) =>
field.Name.AsFieldAndAliasField(leftAlias, rightAlias, dbSetting);
/*
* Add
*/
/// <summary>
/// Adds a mapping between the type of <see cref="DbConnection"/> and an instance of <see cref="IDbSetting"/> object.
/// </summary>
/// <typeparam name="TDbConnection">The type of <see cref="DbConnection"/> object.</typeparam>
/// <param name="dbSetting">The instance of <see cref="IDbSetting"/> object to mapped to.</param>
/// <param name="override">Set to true if to override the existing mapping, otherwise an exception will be thrown if the mapping is already present.</param>
public static void Add <TDbConnection>(IDbSetting dbSetting,
bool @override)
where TDbConnection : DbConnection =>
Add(StaticType.DbConnection, dbSetting, @override);
/// <summary>
///
/// </summary>
/// <param name="fields"></param>
/// <param name="leftAlias"></param>
/// <param name="rightAlias"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static IEnumerable <string> AsFieldsAndAliasFields(this IEnumerable <Field> fields,
string leftAlias,
string rightAlias,
IDbSetting dbSetting) =>
fields?.Select(field => field.AsFieldAndAliasField(leftAlias, rightAlias, dbSetting));
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <param name="alias"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string AsAliasField(this Field field,
string alias,
IDbSetting dbSetting) =>
field.Name.AsAliasField(alias, dbSetting);
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="inputFields"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
public static Action <DbCommand, TEntity> CompileDictionaryStringObjectDbParameterSetter <TEntity>(IEnumerable <DbField> inputFields,
IDbSetting dbSetting = null)
where TEntity : class =>
Compiler.CompileDictionaryStringObjectDbParameterSetter <TEntity>(inputFields, dbSetting);
/// <summary>
/// Creates a new instance of <see cref="SqLiteDbHelper"/> class.
/// </summary>
/// <param name="dbTypeResolver">The type resolver to be used.</param>
/// <param name="dbSetting">The instance of the <see cref="IDbSetting"/> object to be used.</param>
public SqLiteDbHelper(IDbSetting dbSetting,
IResolver <string, Type> dbTypeResolver)
{
DbSetting = dbSetting;
DbTypeResolver = dbTypeResolver;
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="inputFields"></param>
/// <param name="outputFields"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
public static Action <DbCommand, TEntity> CompileDataEntityDbParameterSetter <TEntity>(IEnumerable <DbField> inputFields,
IEnumerable <DbField> outputFields,
IDbSetting dbSetting = null)
where TEntity : class =>
Compiler.CompileDataEntityDbParameterSetter <TEntity>(inputFields, outputFields, dbSetting);
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="inputFields"></param>
/// <param name="outputFields"></param>
/// <param name="batchSize"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
public static Action <DbCommand, IList <TEntity> > CompileDataEntityListDbParameterSetter <TEntity>(IEnumerable <DbField> inputFields,
IEnumerable <DbField> outputFields,
int batchSize,
IDbSetting dbSetting = null)
where TEntity : class =>
Compiler.CompileDataEntityListDbParameterSetter <TEntity>(inputFields, outputFields, batchSize, dbSetting);
/// <summary>
///
/// </summary>
/// <param name="reader"></param>
/// <param name="dbFields"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
public static Func <DbDataReader, ExpandoObject> CompileDataReaderToExpandoObject(DbDataReader reader,
IEnumerable <DbField> dbFields = null,
IDbSetting dbSetting = null) =>
Compiler.CompileDataReaderToExpandoObject(reader, dbFields, dbSetting);
/// <summary>
///
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="reader"></param>
/// <param name="dbFields">The list of the <see cref="DbField"/> objects to be used.</param>
/// <param name="dbSetting">The instance of <see cref="IDbSetting"/> object to be used.</param>
/// <returns></returns>
public static Func <DbDataReader, TResult> CompileDataReaderToType <TResult>(DbDataReader reader,
IEnumerable <DbField> dbFields = null,
IDbSetting dbSetting = null) =>
Compiler.CompileDataReaderToType <TResult>(reader, dbFields, dbSetting);
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="field"></param>
/// <param name="parameterName"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
public static Action <TEntity, DbCommand> CompileDbCommandToProperty <TEntity>(Field field,
string parameterName,
int index,
IDbSetting dbSetting = null)
where TEntity : class =>
Compiler.CompileDbCommandToProperty <TEntity>(field, parameterName, index, dbSetting);
/// <summary>
///
/// </summary>
/// <param name="fields"></param>
/// <param name="alias"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static IEnumerable <string> AsAliasFields(this IEnumerable <Field> fields,
string alias,
IDbSetting dbSetting) =>
fields?.Select(field => field.AsAliasField(alias, dbSetting));
/// <summary>
/// Appends a stringified fields and parameters to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The current instance.</returns>
public QueryBuilder FieldsAndParametersFrom <TEntity>(int index,
IDbSetting dbSetting)
where TEntity : class
{
return(Append(FieldCache.Get <TEntity>(dbSetting)?.AsFieldsAndParameters(index, dbSetting).Join(", ")));
}
/// <summary>
///
/// </summary>
/// <param name="fields"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static IEnumerable <string> AsFieldsAndParameters(this IEnumerable <Field> fields,
int index,
IDbSetting dbSetting) =>
fields?.Select(field => field.AsFieldAndParameter(index, dbSetting));
/// <summary>
/// Appends a word HAVING COUNT and a conditional field to the SQL Query Statement.
/// </summary>
/// <param name="queryField">The conditional field object used for composition.</param>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The current instance.</returns>
public QueryBuilder HavingCountFrom(QueryField queryField,
int index,
IDbSetting dbSetting)
{
return(Append(string.Concat("HAVING COUNT(", queryField.Field.Name, ") ", queryField.GetOperationText(), ", ", queryField.AsParameter(index, dbSetting))));
}
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string AsField(this Field field,
IDbSetting dbSetting) =>
field.Name.AsQuoted(true, dbSetting);
/// <summary>
/// Appends the target name to the SQL Query Statement.
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The current instance.</returns>
public QueryBuilder TableNameFrom(string tableName,
IDbSetting dbSetting)
{
return(Append(tableName?.AsQuoted(true, dbSetting)));
}
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string AsFieldAndParameter(this Field field,
int index,
IDbSetting dbSetting) =>
field.Name.AsFieldAndParameter(index, dbSetting);
/// <summary>
/// Appends the mapped entity name to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The current instance.</returns>
public QueryBuilder TableNameFrom <TEntity>(IDbSetting dbSetting)
where TEntity : class
{
return(Append(ClassMappedNameCache.Get <TEntity>(dbSetting)));
}
/// <summary>
/// Parses a property from the data entity object based on the given <see cref="Expression"/> and converts the result
/// to <see cref="OrderField"/> object with <see cref="Order.Descending"/> value.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity that contains the property to be parsed.</typeparam>
/// <param name="expression">The expression to be parsed.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>An instance of <see cref="OrderField"/> object with <see cref="Order.Descending"/> value.</returns>
public static OrderField Descending <TEntity>(Expression <Func <TEntity, object> > expression,
IDbSetting dbSetting)
where TEntity : class
{
return(Parse <TEntity>(expression, Order.Descending, dbSetting));
}
/// <summary>
/// Append the mapped properpties name to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The current instance.</returns>
public QueryBuilder ParametersFrom <TEntity>(int index,
IDbSetting dbSetting)
where TEntity : class
{
return(ParametersFrom(FieldCache.Get <TEntity>(dbSetting), index, dbSetting));
}
/// <summary>
///
/// </summary>
/// <param name="commandParameterExpression"></param>
/// <param name="entityIndex"></param>
/// <param name="entityExpression"></param>
/// <param name="propertyExpression"></param>
/// <param name="dbField"></param>
/// <param name="classProperty"></param>
/// <param name="direction"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static Expression GetDataEntityParameterAssignmentExpression(ParameterExpression commandParameterExpression,
int entityIndex,
Expression entityExpression,
ParameterExpression propertyExpression,
DbField dbField,
ClassProperty classProperty,
ParameterDirection direction,
IDbSetting dbSetting)
{
var parameterAssignmentExpressions = new List <Expression>();
var parameterVariableExpression = Expression.Variable(StaticType.DbParameter,
string.Concat("parameter", dbField.Name.AsUnquoted(true, dbSetting).AsAlphaNumeric()));
// Variable
var createParameterExpression = GetDbCommandCreateParameterExpression(commandParameterExpression, dbField);
parameterAssignmentExpressions.AddIfNotNull(Expression.Assign(parameterVariableExpression, createParameterExpression));
// DbParameter.Name
var nameAssignmentExpression = GetDbParameterNameAssignmentExpression(parameterVariableExpression,
dbField,
entityIndex,
dbSetting);
parameterAssignmentExpressions.AddIfNotNull(nameAssignmentExpression);
// DbParameter.Value
if (direction != ParameterDirection.Output)
{
var valueAssignmentExpression = GetDataEntityDbParameterValueAssignmentExpression(parameterVariableExpression,
entityExpression,
propertyExpression,
classProperty,
dbField,
dbSetting);
parameterAssignmentExpressions.AddIfNotNull(valueAssignmentExpression);
}
// DbParameter.DbType
var dbTypeAssignmentExpression = GetDbParameterDbTypeAssignmentExpression(parameterVariableExpression,
classProperty, dbField);
parameterAssignmentExpressions.AddIfNotNull(dbTypeAssignmentExpression);
// DbParameter.Direction
if (dbSetting.IsDirectionSupported)
{
var directionAssignmentExpression = GetDbParameterDirectionAssignmentExpression(parameterVariableExpression, direction);
parameterAssignmentExpressions.AddIfNotNull(directionAssignmentExpression);
}
// DbParameter.Size
if (dbField.Size != null)
{
var sizeAssignmentExpression = GetDbParameterSizeAssignmentExpression(parameterVariableExpression, dbField.Size.Value);
parameterAssignmentExpressions.AddIfNotNull(sizeAssignmentExpression);
}
// DbParameter.Precision
if (dbField.Precision != null)
{
var precisionAssignmentExpression = GetDbParameterPrecisionAssignmentExpression(parameterVariableExpression, dbField.Precision.Value);
parameterAssignmentExpressions.AddIfNotNull(precisionAssignmentExpression);
}
// DbParameter.Scale
if (dbField.Scale != null)
{
var scaleAssignmentExpression = GetDbParameterScaleAssignmentExpression(parameterVariableExpression, dbField.Scale.Value);
parameterAssignmentExpressions.AddIfNotNull(scaleAssignmentExpression);
}
// Npgsql (Unknown)
if (IsPostgreSqlUserDefined(dbField))
{
var setToUnknownExpression = GetSetToUnknownNpgsqlParameterExpression(parameterVariableExpression, dbField);
parameterAssignmentExpressions.AddIfNotNull(setToUnknownExpression);
}
// PropertyValueAttributes / DbField must precide
var propertyValueAttributeAssignmentExpressions = GetPropertyValueAttributeAssignmentExpressions(parameterVariableExpression,
classProperty);
parameterAssignmentExpressions.AddRangeIfNotNullOrNotEmpty(propertyValueAttributeAssignmentExpressions);
// DbCommand.Parameters.Add
var dbParametersAddExpression = GetDbCommandParametersAddExpression(commandParameterExpression, parameterVariableExpression);
parameterAssignmentExpressions.AddIfNotNull(dbParametersAddExpression);
// Return the value
return(Expression.Block(new[] { parameterVariableExpression }, parameterAssignmentExpressions));
}
/// <summary>
/// Append the stringified parameter as fields to the SQL Query Statement.
/// </summary>
/// <param name="fields">The list of fields to be stringified.</param>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The current instance.</returns>
public QueryBuilder ParametersAsFieldsFrom(IEnumerable <Field> fields,
int index,
IDbSetting dbSetting)
{
return(Append(fields?.AsParametersAsFields(index, dbSetting).Join(", ")));
}
/// <summary>
///
/// </summary>
/// <param name="tempTableName"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string GetDropTemporaryTableSqlText(string tempTableName,
IDbSetting dbSetting) =>
$"DROP TABLE {tempTableName.AsQuoted(dbSetting)};";
/// <summary>
/// Creates a string representation of JOIN statement for the target <see cref="Field"/> object.
/// </summary>
/// <param name="field">The instance of the <see cref="Field"/> object.</param>
/// <param name="leftAlias">The left alias.</param>
/// <param name="rightAlias">The right alias.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>The currently in used database setting.</returns>
public static string AsJoinQualifier(this Field field,
string leftAlias,
string rightAlias,
IDbSetting dbSetting) =>
field.Name.AsJoinQualifier(leftAlias, rightAlias, dbSetting);
/// <summary>
/// Gets the cached list of <see cref="Field"/> objects of the data entity.
/// </summary>
/// <typeparam name="TEntity">The type of the target entity.</typeparam>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The cached list <see cref="Field"/> objects.</returns>
public static IEnumerable <Field> Get <TEntity>(IDbSetting dbSetting)
where TEntity : class
{
return(Get(typeof(TEntity), dbSetting));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="reader"></param>
/// <param name="dbFields">The list of the <see cref="DbField"/> objects to be used.</param>
/// <param name="dbSetting">The instance of <see cref="IDbSetting"/> object to be used.</param>
/// <returns></returns>
internal static Func <DbDataReader, TResult> GetDataReaderToTypeCompiledFunction <TResult>(DbDataReader reader,
IEnumerable <DbField> dbFields = null,
IDbSetting dbSetting = null) =>
DataReaderToTypeCache <TResult> .Get(reader, dbFields, dbSetting);
