/// <summary> /// Creates an instance of a database of type <see cref="RelationalDbStorage"/>. /// </summary> /// <param name="invariantName">The invariant name of the connector for this database.</param> /// <param name="connectionString">The connection string this database should use for database operations.</param> public RelationalDbStorage(string invariantName, string connectionString) { ConnectionString = connectionString; InvariantName = invariantName; SupportsCommandCancellation = DbConstantsStore.SupportsCommandCancellation(InvariantName); IsSynchronousAdoNetImplementation = DbConstantsStore.IsSynchronousAdoNetImplementation(InvariantName); _databaseCommandInterceptor = DbConstantsStore.GetDatabaseCommandInterceptor(InvariantName); }
/// <summary> /// Executes a multi-record insert query clause with <em>SELECT UNION ALL</em>. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="storage">The storage to use.</param> /// <param name="tableName">The table name to against which to execute the query.</param> /// <param name="parameters">The parameters to insert.</param> /// <param name="cancellationToken">The cancellation token. Defaults to <see cref="CancellationToken.None"/>.</param> /// <param name="nameMap">If provided, maps property names from <typeparamref name="T"/> to ones provided in the map.</param> /// <param name="onlyOnceColumns">If given, SQL parameter values for the given <typeparamref name="T"/> property types are generated only once. Effective only when <paramref name="useSqlParams"/> is <em>TRUE</em>.</param> /// <param name="useSqlParams"><em>TRUE</em> if the query should be in parameterized form. <em>FALSE</em> otherwise.</param> /// <returns>The rows affected.</returns> public static Task <int> ExecuteMultipleInsertIntoAsync <T>( this IRelationalDbStorage storage, string tableName, IEnumerable <T> parameters, CancellationToken cancellationToken = default, IReadOnlyDictionary <string, string> nameMap = null, IEnumerable <string> onlyOnceColumns = null, bool useSqlParams = true) { if (string.IsNullOrWhiteSpace(tableName)) { throw new ArgumentException("The name must be a legal SQL table name", nameof(tableName)); } if (parameters == null) { throw new ArgumentNullException(nameof(parameters)); } var storageConstants = DbConstantsStore.GetDbConstants(storage.InvariantName); var startEscapeIndicator = storageConstants.StartEscapeIndicator; var endEscapeIndicator = storageConstants.EndEscapeIndicator; //SqlParameters map is needed in case the query needs to be parameterized in order to avoid two //reflection passes as first a query needs to be constructed and after that when a database //command object has been created, parameters need to be provided to them. var sqlParameters = new Dictionary <string, object>(); const string insertIntoValuesTemplate = "INSERT INTO {0} ({1}) SELECT {2};"; var columns = string.Empty; var values = new List <string>(); if (parameters.Any()) { //Type and property information are the same for all of the objects. //The following assumes the property names will be retrieved in the same //order as is the index iteration done. var onlyOnceRow = new List <string>(); var properties = parameters.First().GetType().GetProperties(); columns = string.Join(",", nameMap == null ? properties.Select(pn => $"{startEscapeIndicator}{pn.Name}{endEscapeIndicator}") : properties.Select(pn => $"{startEscapeIndicator}{(nameMap.ContainsKey(pn.Name) ? nameMap[pn.Name] : pn.Name)}{endEscapeIndicator}")); if (onlyOnceColumns != null && onlyOnceColumns.Any()) { var onlyOnceProperties = properties.Where(pn => onlyOnceColumns.Contains(pn.Name)).Select(pn => pn).ToArray(); var onlyOnceData = parameters.First(); foreach (var t in onlyOnceProperties) { var currentProperty = t; var parameterValue = currentProperty.GetValue(onlyOnceData, null); if (useSqlParams) { var parameterName = $"@{(nameMap.ContainsKey(t.Name) ? nameMap[t.Name] : t.Name)}"; onlyOnceRow.Add(parameterName); sqlParameters.Add(parameterName, parameterValue); } else { onlyOnceRow.Add(string.Format(AdoNetFormatProvider, "{0}", parameterValue)); } } } var dataRows = new List <string>(); var multiProperties = onlyOnceColumns == null ? properties : properties.Where(pn => !onlyOnceColumns.Contains(pn.Name)).Select(pn => pn).ToArray(); var parameterCount = 0; foreach (var row in parameters) { foreach (var currentProperty in multiProperties) { var parameterValue = currentProperty.GetValue(row, null); if (useSqlParams) { var parameterName = string.Format(IndexedParameterTemplate, parameterCount); dataRows.Add(parameterName); sqlParameters.Add(parameterName, parameterValue); ++parameterCount; } else { dataRows.Add(string.Format(AdoNetFormatProvider, "{0}", parameterValue)); } } values.Add($"{string.Join(",", onlyOnceRow.Concat(dataRows))}"); dataRows.Clear(); } } var query = string.Format(insertIntoValuesTemplate, tableName, columns, string.Join(storageConstants.UnionAllSelectTemplate, values)); return(storage.ExecuteAsync(query, command => { if (!useSqlParams) { return; } foreach (var sp in sqlParameters) { var p = command.CreateParameter(); p.ParameterName = sp.Key; p.Value = sp.Value ?? DBNull.Value; p.Direction = ParameterDirection.Input; command.Parameters.Add(p); } }, cancellationToken)); }