/// <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);