public static async Task <int> ExecuteNonQueryAsync(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, IEnumerable <TempTableData> tempTableData, CancellationToken ct)
{
if (existingConn is not null)
{
await LoadTempTablesAsync(existingConn, tempTableData, tran, ct);
await using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
return(await cmd.ExecuteNonQueryAsync(ct).ConfigureAwait(false));
}
// ReSharper disable once InvertIf
if (connStr is not null)
{
await using var conn = new SqlConnection(connStr);
configureNewConn(conn);
await conn.OpenAsync(ct);
await LoadTempTablesAsync(conn, tempTableData, tran, ct);
await using var cmd = conn.CreateCommand();
configureCmd(cmd);
return(await cmd.ExecuteNonQueryAsync(ct).ConfigureAwait(false));
}
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
protected internal override object?ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection? con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
object result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
{
return(null);
}
return(result);
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
/// <inheritdoc />
public ICollectionSaver CreateCollectionSaver()
{
if (_targetController.Cleanup(out var border))
{
//something were deleted from the file system
//so we need to delete obsolete records from the DB
CleanupDatabase(border);
}
SqlConnection? connection = null;
SqlTransaction?transaction = null;
try
{
connection = new SqlConnection(_connectionString);
connection.Open();
transaction = connection.BeginTransaction();
return(new SqlServerCollectionSaver(
connection,
transaction,
_tableName,
_targetController
));
}
catch
{
transaction?.SafelyDispose();
connection?.SafelyDispose();
throw;
}
}
public static FSharpValueOption <T> ExecuteQuerySingleVoption <T>(SqlConnection?existingConn, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem)
{
if (existingConn is not null)
{
using var cmd = existingConn.CreateCommand();
configureCmd(cmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow);
if (reader.Read())
{
initOrdinals(reader);
return(FSharpValueOption <T> .Some(getItem(reader)));
}
return(FSharpValueOption <T> .None);
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
using var cmd = conn.CreateCommand();
configureCmd(cmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow);
if (reader.Read())
{
initOrdinals(reader);
return(FSharpValueOption <T> .Some(getItem(reader)));
}
return(FSharpValueOption <T> .None);
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
protected internal override DataSet ExecuteDataSet(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection? con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet result = new DataSet();
da.Fill(result);
return(result);
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
protected override async Task RunBatch(CancellationToken cancellation)
{
SqlConnection?connection = null;
if (connectionBuilder != null)
{
await using (connection = await connectionBuilder(cancellation))
{
var reader = new QueueManager(table, connection);
await RunBatch(
reader,
messageFunc : message => connectionCallback !(connection, message, cancellation),
persistFunc : () => connectionPersistRowVersion !(connection, startingRow, cancellation),
cancellation);
}
return;
}
SqlTransaction?transaction = null;
try
{
transaction = await transactionBuilder !(cancellation);
connection = transaction.Connection;
var reader = new QueueManager(table, transaction);
try
{
await RunBatch(
reader,
messageFunc : message => transactionCallback !(transaction, message, cancellation),
persistFunc : () => transactionPersistRowVersion !(transaction, startingRow, cancellation),
cancellation);
await transaction.CommitAsync(cancellation);
}
catch
{
await transaction.RollbackAsync(cancellation);
throw;
}
}
finally
{
if (transaction != null)
{
await transaction.DisposeAsync();
}
if (connection != null)
{
await connection.DisposeAsync();
}
}
}
/// <summary>
/// Returns the specified shared <see cref="SqlConnection"/> instance if
/// provided, or creates a new, owned instance using the specified
/// context, database name, and cmdlet.
/// </summary>
/// <param name="connection">
/// The shared connection. If provided, the method returns this
/// connection.
/// </param>
/// <param name="context">
/// An object containing information necessary to connect to a database
/// if <paramref name="connection"/> is <see langword="null"/>. If not
/// provided, the method will use a context with default property values
/// as necessary.
/// </param>
/// <param name="databaseName">
/// The name of the database to which to connect if <paramref name="connection"/>
/// is <see langword="null"/>. If not provided, the method connects to
/// the default database for the context.
/// </param>
/// <param name="cmdlet">
/// A cmdlet whose
/// <see cref="Cmdlet.WriteHost(string, bool, ConsoleColor?, ConsoleColor?)"/>
/// and
/// <see cref="System.Management.Automation.Cmdlet.WriteWarning(string)"/>
/// methods will be used to print messges received from the server over
/// the new connection created if <paramref name="connection"/> is
/// <see langword="null"/>.
/// </param>
/// <returns>
/// A tuple consisting of the resulting connection and a value that
/// indicates whether the caller owns the connection and must ensure its
/// disposal. If <paramref name="connection"/> is provided, the method
/// returns that connection and <see langword="false"/> (shared).
/// Otherwise, the method creates a new connection as specified by
/// <paramref name="context"/> and <paramref name="databaseName"/> and
/// returns the connection and <see langword="true"/> (owned).
/// </returns>
internal static (SqlConnection, bool owned) EnsureConnection(
SqlConnection?connection,
SqlContext?context,
string?databaseName,
Cmdlet cmdlet)
{
return(connection != null
? (connection, owned : false)
: (new SqlConnection(context, databaseName, cmdlet), owned : true));
}
internal object ExecuteScalar(string cmdText, SqlConnection?sqlConnection = null)
{
using (var connection = sqlConnection ?? GetConnection())
using (var command = CreateCommand(cmdText, connection))
{
connection.Open();
var result = command.ExecuteScalar();
connection.Close();
return(result);
}
}
/// <summary>
/// Executes the specified implementation asynchronously.
/// </summary>
/// <param name="executionToken">The execution token.</param>
/// <param name="implementation">The implementation.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <param name="state">The state.</param>
/// <returns>The caller is expected to use the StreamingCommandCompletionToken to close any lingering connections and fire appropriate events.</returns>
/// <exception cref="System.NotImplementedException"></exception>
public override async Task <StreamingCommandCompletionToken> ExecuteStreamAsync(CommandExecutionToken <SqlCommand, SqlParameter> executionToken, StreamingCommandImplementationAsync <SqlCommand> implementation, CancellationToken cancellationToken, object?state)
{
if (executionToken == null)
{
throw new ArgumentNullException(nameof(executionToken), $"{nameof(executionToken)} is null.");
}
if (implementation == null)
{
throw new ArgumentNullException(nameof(implementation), $"{nameof(implementation)} is null.");
}
var startTime = DateTimeOffset.Now;
OnExecutionStarted(executionToken, startTime, state);
SqlConnection?con = null;
try
{
con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false);
var cmd = new SqlCommand();
cmd.Connection = con;
executionToken.PopulateCommand(cmd, DefaultCommandTimeout);
await implementation(cmd).ConfigureAwait(false);
return(new StreamingCommandCompletionToken(this, executionToken, startTime, state, cmd, con));
}
catch (Exception ex)
{
#if NET6_0_OR_GREATER
if (con != null)
{
await con.DisposeAsync().ConfigureAwait(false);
}
#else
con?.Dispose();
#endif
if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionCanceled(executionToken, startTime, DateTimeOffset.Now, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}
public static async Task <List <T> > ExecuteQueryEagerAsyncWithSyncRead <T>(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, IEnumerable <TempTableData> tempTableData, CancellationToken ct)
{
if (existingConn is not null)
{
await LoadTempTablesAsync(existingConn, tempTableData, tran, ct);
await using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult, ct).ConfigureAwait(false);
var list = new List <T>();
if (!reader.HasRows)
{
return(list);
}
initOrdinals(reader);
// ReSharper disable once MethodHasAsyncOverloadWithCancellation
while (reader.Read())
{
list.Add(getItem(reader));
}
return(list);
}
// ReSharper disable once InvertIf
if (connStr is not null)
{
await using var conn = new SqlConnection(connStr);
configureNewConn(conn);
await conn.OpenAsync(ct).ConfigureAwait(false);
await LoadTempTablesAsync(conn, tempTableData, tran, ct);
await using var cmd = conn.CreateCommand();
configureCmd(cmd);
await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult, ct).ConfigureAwait(false);
var list = new List <T>();
if (!reader.HasRows)
{
return(list);
}
initOrdinals(reader);
// ReSharper disable once MethodHasAsyncOverloadWithCancellation
while (reader.Read())
{
list.Add(getItem(reader));
}
return(list);
}
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
public static async Task <List <T> > ExecuteQueryEagerAsync <T>(SqlConnection?existingConn, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, IEnumerable <TempTableData> tempTableData, CancellationToken ct)
{
if (existingConn is not null)
{
await LoadTempTablesAsync(existingConn, tempTableData, ct);
using var cmd = existingConn.CreateCommand();
configureCmd(cmd);
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult, ct).ConfigureAwait(false);
var list = new List <T>();
if (reader.HasRows)
{
initOrdinals(reader);
while (await reader.ReadAsync(ct).ConfigureAwait(false))
{
list.Add(getItem(reader));
}
}
return(list);
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
await conn.OpenAsync(ct).ConfigureAwait(false);
await LoadTempTablesAsync(conn, tempTableData, ct);
using var cmd = conn.CreateCommand();
configureCmd(cmd);
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult, ct).ConfigureAwait(false);
var list = new List <T>();
if (reader.HasRows)
{
initOrdinals(reader);
while (await reader.ReadAsync(ct).ConfigureAwait(false))
{
list.Add(getItem(reader));
}
}
return(list);
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
public async Task TestConnection(CancellationToken token)
{
SqlConnection?con = null;
try
{
con = await GetConnectionAsync(token);
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public override async Task <IDbConnection> CreateOpenConnectionAsync()
{
if (_connection is null)
{
_logger.LogDebug("Initiating connection");
_connection = new SqlConnection(_connectionBuilder.ConnectionString);
}
if (_connection.State != ConnectionState.Open)
{
_logger.LogDebug("Connection hasn't opened yet or it was closed");
await _connection.OpenAsync();
}
return(_connection);
}
public override void WriteJson(
JsonWriter writer,
SqlConnection?connection,
JsonSerializer serializer,
IReadOnlyDictionary <string, object> context)
{
if (connection == null)
{
return;
}
var schemaSettings = context.GetSchemaSettings();
SqlScriptBuilder builder = new(schemaSettings);
var script = builder.BuildScript(connection);
writer.WriteValue(script);
}
public static List <T> ExecuteQueryEager <T>(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, IEnumerable <TempTableData> tempTableData)
{
if (existingConn is not null)
{
LoadTempTables(existingConn, tempTableData, tran);
using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var list = new List <T>();
if (!reader.HasRows)
{
return(list);
}
initOrdinals(reader);
while (reader.Read())
{
list.Add(getItem(reader));
}
return(list);
}
// ReSharper disable once InvertIf
if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
LoadTempTables(conn, tempTableData, tran);
using var cmd = conn.CreateCommand();
configureCmd(cmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var list = new List <T>();
if (!reader.HasRows)
{
return(list);
}
initOrdinals(reader);
while (reader.Read())
{
list.Add(getItem(reader));
}
return(list);
}
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
public static async IAsyncEnumerable <T> ExecuteQueryLazyAsync <T>(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, IEnumerable <TempTableData> tempTableData, [EnumeratorCancellation] CancellationToken ct)
{
if (existingConn is not null)
{
await LoadTempTablesAsync(existingConn, tempTableData, tran, ct);
await using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult, ct).ConfigureAwait(false);
if (!reader.HasRows)
{
yield break;
}
initOrdinals(reader);
while (await reader.ReadAsync(ct).ConfigureAwait(false))
{
yield return(getItem(reader));
}
}
else if (connStr is not null)
{
await using var conn = new SqlConnection(connStr);
configureNewConn(conn);
await conn.OpenAsync(ct).ConfigureAwait(false);
await LoadTempTablesAsync(conn, tempTableData, tran, ct);
await using var cmd = conn.CreateCommand();
configureCmd(cmd);
await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult, ct).ConfigureAwait(false);
if (!reader.HasRows)
{
yield break;
}
initOrdinals(reader);
while (await reader.ReadAsync(ct).ConfigureAwait(false))
{
yield return(getItem(reader));
}
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
public static async Task <SqlConnection> OpenAsync(string connectionString, CancellationToken cancellationToken = default)
{
SqlConnection?connection = null;
try
{
connection = NewConnection(connectionString);
await connection.OpenAsync(cancellationToken);
return(connection);
}
catch
{
connection?.Dispose();
throw;
}
}
public static void SafelyClose(
this SqlConnection?connection
)
{
if (connection is null)
{
return;
}
try
{
connection.Close();
}
catch
{
//nothing to do
}
}
public static IEnumerable <T> ExecuteQueryLazy <T>(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, IEnumerable <TempTableData> tempTableData)
{
if (existingConn is not null)
{
LoadTempTables(existingConn, tempTableData, tran);
using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
if (!reader.HasRows)
{
yield break;
}
initOrdinals(reader);
while (reader.Read())
{
yield return(getItem(reader));
}
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
LoadTempTables(conn, tempTableData, tran);
using var cmd = conn.CreateCommand();
configureCmd(cmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
if (!reader.HasRows)
{
yield break;
}
initOrdinals(reader);
while (reader.Read())
{
yield return(getItem(reader));
}
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
public static List <T> ExecuteQueryEager <T>(SqlConnection?existingConn, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem)
{
if (existingConn is not null)
{
using var cmd = existingConn.CreateCommand();
configureCmd(cmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var list = new List <T>();
if (reader.HasRows)
{
initOrdinals(reader);
while (reader.Read())
{
list.Add(getItem(reader));
}
}
return(list);
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
using var cmd = conn.CreateCommand();
configureCmd(cmd);
using var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var list = new List <T>();
if (reader.HasRows)
{
initOrdinals(reader);
while (reader.Read())
{
list.Add(getItem(reader));
}
}
return(list);
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
protected internal override void BulkCopy(DataTable dt, ObjectName destinationTable, SqlBulkCopyOptions options, int?timeout)
{
using (SqlConnection? con = EnsureConnection())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
options.HasFlag(SqlBulkCopyOptions.UseInternalTransaction) ? con : (SqlConnection)Transaction.CurrentConnection !,
options,
options.HasFlag(SqlBulkCopyOptions.UseInternalTransaction) ? null : (SqlTransaction)Transaction.CurrentTransaccion !))
using (HeavyProfiler.Log("SQL", () => destinationTable.ToString() + " Rows:" + dt.Rows.Count))
{
bulkCopy.BulkCopyTimeout = timeout ?? Connector.ScopeTimeout ?? this.CommandTimeout ?? bulkCopy.BulkCopyTimeout;
foreach (DataColumn c in dt.Columns)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
}
bulkCopy.DestinationTableName = destinationTable.ToString();
bulkCopy.WriteToServer(dt);
}
}
public static async Task <FSharpOption <T> > ExecuteQuerySingleAsync <T>(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, IEnumerable <TempTableData> tempTableData, CancellationToken ct)
{
if (existingConn is not null)
{
await LoadTempTablesAsync(existingConn, tempTableData, tran, ct);
await using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow, ct).ConfigureAwait(false);
if (!await reader.ReadAsync(ct).ConfigureAwait(false))
{
return(FSharpOption <T> .None);
}
initOrdinals(reader);
return(FSharpOption <T> .Some(getItem(reader)));
}
// ReSharper disable once InvertIf
if (connStr is not null)
{
await using var conn = new SqlConnection(connStr);
configureNewConn(conn);
await conn.OpenAsync(ct).ConfigureAwait(false);
await LoadTempTablesAsync(conn, tempTableData, tran, ct);
await using var cmd = conn.CreateCommand();
configureCmd(cmd);
await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow, ct).ConfigureAwait(false);
if (!await reader.ReadAsync(ct).ConfigureAwait(false))
{
return(FSharpOption <T> .None);
}
initOrdinals(reader);
return(FSharpOption <T> .Some(getItem(reader)));
}
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
public static async Task <int> ExecuteNonQueryAsync <T>(SqlConnection?existingConn, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, CancellationToken ct)
{
if (existingConn is not null)
{
using var cmd = existingConn.CreateCommand();
configureCmd(cmd);
return(await cmd.ExecuteNonQueryAsync(ct).ConfigureAwait(false));
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
using var cmd = conn.CreateCommand();
configureCmd(cmd);
return(await cmd.ExecuteNonQueryAsync(ct).ConfigureAwait(false));
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
public static int ExecuteNonQuery <T>(SqlConnection?existingConn, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd)
{
if (existingConn is not null)
{
using var cmd = existingConn.CreateCommand();
configureCmd(cmd);
return(cmd.ExecuteNonQuery());
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
using var cmd = conn.CreateCommand();
configureCmd(cmd);
return(cmd.ExecuteNonQuery());
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
/// <summary>
/// Executes the specified implementation.
/// </summary>
/// <param name="executionToken">The execution token.</param>
/// <param name="implementation">The implementation.</param>
/// <param name="state">The state.</param>
/// <returns>The caller is expected to use the StreamingCommandCompletionToken to close any lingering connections and fire appropriate events.</returns>
/// <exception cref="System.NotImplementedException"></exception>
public override StreamingCommandCompletionToken ExecuteStream(CommandExecutionToken <SqlCommand, SqlParameter> executionToken, StreamingCommandImplementation <SqlCommand> implementation, object?state)
{
if (executionToken == null)
{
throw new ArgumentNullException(nameof(executionToken), $"{nameof(executionToken)} is null.");
}
if (implementation == null)
{
throw new ArgumentNullException(nameof(implementation), $"{nameof(implementation)} is null.");
}
var startTime = DateTimeOffset.Now;
OnExecutionStarted(executionToken, startTime, state);
SqlConnection?con = null;
try
{
con = CreateConnection();
var cmd = new SqlCommand();
cmd.Connection = con;
executionToken.PopulateCommand(cmd, DefaultCommandTimeout);
implementation(cmd);
return(new StreamingCommandCompletionToken(this, executionToken, startTime, state, cmd, con));
}
catch (Exception ex)
{
con?.Dispose();
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection? con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
int result = cmd.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
public static async Task <FSharpOption <T> > ExecuteQuerySingleAsync <T>(SqlConnection?existingConn, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, Action <SqlDataReader> initOrdinals, Func <SqlDataReader, T> getItem, CancellationToken ct)
{
if (existingConn is not null)
{
using var cmd = existingConn.CreateCommand();
configureCmd(cmd);
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow, ct).ConfigureAwait(false);
if (await reader.ReadAsync(ct).ConfigureAwait(false))
{
initOrdinals(reader);
return(FSharpOption <T> .Some(getItem(reader)));
}
return(FSharpOption <T> .None);
}
else if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
await conn.OpenAsync(ct).ConfigureAwait(false);
using var cmd = conn.CreateCommand();
configureCmd(cmd);
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow, ct).ConfigureAwait(false);
if (await reader.ReadAsync(ct).ConfigureAwait(false))
{
initOrdinals(reader);
return(FSharpOption <T> .Some(getItem(reader)));
}
return(FSharpOption <T> .None);
}
else
{
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
}
SqlCommand NewCommand(SqlPreCommandSimple preCommand, SqlConnection?overridenConnection, CommandType commandType)
{
SqlCommand cmd = new SqlCommand {
CommandType = commandType
};
int?timeout = Connector.ScopeTimeout ?? CommandTimeout;
if (timeout.HasValue)
{
cmd.CommandTimeout = timeout.Value;
}
if (overridenConnection != null)
{
cmd.Connection = overridenConnection;
}
else
{
cmd.Connection = (SqlConnection)Transaction.CurrentConnection !;
cmd.Transaction = (SqlTransaction)Transaction.CurrentTransaccion !;
}
cmd.CommandText = preCommand.Sql;
if (preCommand.Parameters != null)
{
foreach (SqlParameter param in preCommand.Parameters)
{
cmd.Parameters.Add(param);
}
}
Log(preCommand);
return(cmd);
}
public static int ExecuteNonQuery(SqlConnection?existingConn, SqlTransaction?tran, string?connStr, Action <SqlConnection> configureNewConn, Action <SqlCommand> configureCmd, IEnumerable <TempTableData> tempTableData)
{
if (existingConn is not null)
{
LoadTempTables(existingConn, tempTableData, tran);
using var cmd = existingConn.CreateCommand();
ConfigureCommand(cmd, tran, configureCmd);
return(cmd.ExecuteNonQuery());
}
// ReSharper disable once InvertIf
if (connStr is not null)
{
using var conn = new SqlConnection(connStr);
configureNewConn(conn);
conn.Open();
LoadTempTables(conn, tempTableData, tran);
using var cmd = conn.CreateCommand();
configureCmd(cmd);
return(cmd.ExecuteNonQuery());
}
throw new Exception($"{nameof(existingConn)} and {nameof(connStr)} may not both be null");
}
public void ExecuteDataReaderDependency(SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action reconect, Action <FieldReader> forEach, CommandType commandType)
{
bool reconected = false;
retry:
try
{
using (SqlConnection? con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL-Dependency"))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
if (change != null)
{
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += change;
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
FieldReader fr = new FieldReader(reader);
int row = -1;
try
{
while (reader.Read())
{
row++;
forEach(fr);
}
}
catch (Exception ex)
{
FieldReaderException fieldEx = fr.CreateFieldReaderException(ex);
fieldEx.Command = preCommand;
fieldEx.Row = row;
throw fieldEx;
}
}
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
catch (InvalidOperationException ioe)
{
if (ioe.Message.Contains("SqlDependency.Start()") && !reconected)
{
reconect();
reconected = true;
goto retry;
}
throw;
}
}