public async Task using_ISqlConnectionController_extension_methods_asynchronous()
{
string tableName = "CK.t" + Guid.NewGuid().ToString( "N" );
var create = new SqlCommand( $"create table {tableName} ( id int, name varchar(10) ); insert into {tableName}(id,name) values (1,'One'), (2,'Two'), (3,'Three');" );
var scalar = new SqlCommand( $"select name from {tableName} where id=@Id;" );
scalar.Parameters.AddWithValue( "@Id", 3 );
var row = new SqlCommand( $"select top 1 id, name from {tableName} order by id;" );
var reader = new SqlCommand( $"select id, name from {tableName} order by id;" );
var clean = new SqlCommand( $"drop table {tableName};" );
using( var ctx = new SqlStandardCallContext( TestHelper.Monitor ) )
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
c.Connection.State.Should().Be( ConnectionState.Closed );
await c.ExecuteNonQueryAsync( create );
(await c.ExecuteScalarAsync( scalar )).Should().Be( "Three" );
var rowResult = await c.ExecuteSingleRowAsync( row, r => Tuple.Create( r.GetInt32( 0 ), r.GetString( 1 ) ) );
rowResult.Item1.Should().Be( 1 );
rowResult.Item2.Should().Be( "One" );
var readerResult = await c.ExecuteReaderAsync( reader, r => Tuple.Create( r.GetInt32( 0 ), r.GetString( 1 ) ) );
readerResult.Should().HaveCount( 3 );
readerResult[0].Item1.Should().Be( 1 );
readerResult[1].Item2.Should().Be( "Two" );
readerResult[2].Item2.Should().Be( "Three" );
await c.ExecuteNonQueryAsync( clean );
}
}
public async Task ExplicitOpenAsync_and_Dispose_are_order_independent()
{
using (var ctx = new SqlStandardCallContext(TestHelper.Monitor))
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
c.Connection.State.Should().Be(ConnectionState.Closed);
var d1 = await c.ExplicitOpenAsync();
c.Connection.State.Should().Be(ConnectionState.Open);
var d2 = await c.ExplicitOpenAsync();
var d3 = await c.ExplicitOpenAsync();
d1.Should().NotBeNull();
d2.Should().NotBeNull();
d3.Should().NotBeNull();
c.Connection.State.Should().Be(ConnectionState.Open);
d1.Dispose();
d1.Dispose();
d2.Dispose();
c.Connection.State.Should().Be(ConnectionState.Open);
d3.Dispose();
c.Connection.State.Should().Be(ConnectionState.Closed);
}
}
public static Task <List <T> > ExecuteReaderAsync <T>(this ISqlConnectionController @this,
SqlCommand cmd,
Func <SqlDataRow, T?> builder,
CancellationToken cancellationToken = default) where T : notnull
{
async Task <List <T> > ReadRowsAsync(SqlCommand c, CancellationToken t)
{
var collector = new List <T>();
using (var r = await c.ExecuteReaderAsync(t).ConfigureAwait(false))
{
var row = new SqlDataRow(r);
while (await r.ReadAsync(t).ConfigureAwait(false))
{
var o = builder(row);
if (o is not null)
{
collector.Add(o);
}
}
}
return(collector);
}
return(ExecuteQueryAsync(@this, cmd, ReadRowsAsync, cancellationToken));
}
public async Task ExecuteScalarAsync_works_on_closed_or_opened_connection()
{
using( var cmd = new SqlCommand( "select count(*) from sys.objects" ) )
using( var ctx = new SqlStandardCallContext() )
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
c.Connection.State.Should().Be( ConnectionState.Closed );
using( c.ExplicitOpen() )
{
c.Connection.State.Should().Be( ConnectionState.Open );
((int)c.ExecuteScalar( cmd )).Should().BeGreaterThan( 0 );
}
c.Connection.State.Should().Be( ConnectionState.Closed );
((int)await c.ExecuteScalarAsync( cmd )).Should().BeGreaterThan( 0 );
c.Connection.State.Should().Be( ConnectionState.Closed );
cmd.CommandText = "select count(*) from sys.objects where name='no-object-here'";
using( await c.ExplicitOpenAsync() )
{
c.Connection.State.Should().Be( ConnectionState.Open );
((int)await c.ExecuteScalarAsync( cmd )).Should().Be( 0 );
}
c.Connection.State.Should().Be( ConnectionState.Closed );
((int)await c.ExecuteScalarAsync( cmd )).Should().Be( 0 );
c.Connection.State.Should().Be( ConnectionState.Closed );
cmd.CommandText = "select name from sys.tables where name='no-object-here'";
using( await c.ExplicitOpenAsync() )
{
(await c.ExecuteScalarAsync( cmd )).Should().BeNull();
}
c.Connection.State.Should().Be( ConnectionState.Closed );
}
}
public static int ExecuteNonQuery(this ISqlConnectionController @this, SqlCommand cmd)
{
var ctx = @this.SqlCallContext;
using (@this.ExplicitOpen())
{
return(ctx.Executor.ExecuteQuery(ctx.Monitor, @this.Connection, @this.Transaction, cmd, c => c.ExecuteNonQuery()));
}
}
public static T ExecuteQuery <T>(this ISqlConnectionController @this, SqlCommand cmd, Func <SqlCommand, T> innerExecutor)
{
var ctx = @this.SqlCallContext;
using (@this.ExplicitOpen())
{
return(ctx.Executor.ExecuteQuery(ctx.Monitor, @this.Connection, @this.Transaction, cmd, innerExecutor));
}
}
public static async Task <object?> ExecuteScalarAsync(this ISqlConnectionController @this, SqlCommand cmd, CancellationToken cancellationToken = default)
{
var ctx = @this.SqlCallContext;
using (await @this.ExplicitOpenAsync())
{
return(await ctx.Executor.ExecuteQueryAsync(ctx.Monitor, @this.Connection, @this.Transaction, cmd, (c, t) => c.ExecuteScalarAsync(t), cancellationToken));
}
}
public static async Task <T> ExecuteQueryAsync <T>(this ISqlConnectionController @this,
SqlCommand cmd,
Func <SqlCommand, CancellationToken, Task <T> > innerExecutor,
CancellationToken cancellationToken = default)
{
var ctx = @this.SqlCallContext;
using (await @this.ExplicitOpenAsync())
{
return(await ctx.Executor.ExecuteQueryAsync(ctx.Monitor, @this.Connection, @this.Transaction, cmd, innerExecutor, cancellationToken));
}
}
public static Task <T> ExecuteSingleRowAsync <T>(this ISqlConnectionController @this, SqlCommand cmd, Func <SqlDataRow?, T> builder, CancellationToken cancellationToken = default)
{
async Task <T> ReadRowAsync(SqlCommand c, CancellationToken t)
{
using (var r = await c.ExecuteReaderAsync(CommandBehavior.SingleRow, t).ConfigureAwait(false))
{
return(await r.ReadAsync(t).ConfigureAwait(false)
? builder(new SqlDataRow(r))
: builder(null));
}
}
return(ExecuteQueryAsync(@this, cmd, ReadRowAsync, cancellationToken));
}
public static T ExecuteSingleRow <T>(this ISqlConnectionController @this, SqlCommand cmd, Func <SqlDataRow?, T> builder)
{
T ReadRow(SqlCommand c)
{
using (var r = c.ExecuteReader(CommandBehavior.SingleRow))
{
return(r.Read()
? builder(new SqlDataRow(r))
: builder(null));
}
}
return(ExecuteQuery(@this, cmd, ReadRow));
}
void SyncCallCatch <TException>(string cmd, string connectionString = null)
{
using (IDisposableSqlCallContext c = new SqlStandardCallContext(TestHelper.Monitor))
using (var command = new SqlCommand(cmd))
{
ISqlConnectionController con = c[connectionString ?? TestHelper.GetConnectionString()];
try
{
con.ExecuteNonQuery(command);
}
catch (Exception ex)
{
Assert.That(ex, Is.InstanceOf <TException>());
}
}
}
public void Directly_opening_and_closing_connection_async()
{
SqlConnection directRef;
using (var ctx = new SqlStandardCallContext(TestHelper.Monitor))
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
directRef = c.Connection;
c.Connection.Awaiting(oCon => oCon.OpenAsync()).Should().NotThrow();
c.Connection.State.Should().Be(ConnectionState.Open);
c.Connection.Invoking(oCon => oCon.Close()).Should().NotThrow();
c.Connection.Awaiting(oCon => oCon.OpenAsync()).Should().NotThrow();
c.Connection.State.Should().Be(ConnectionState.Open);
}
directRef.State.Should().Be(ConnectionState.Closed);
}
static async Task Do200Prints(ThreadSafeTraceCounter t)
{
var m = new ActivityMonitor(false);
m.Output.RegisterClient(t);
using (var ctx = new SqlStandardCallContext(m))
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
c.ExplicitOpen();
var cmd = c.Connection.CreateCommand();
cmd.CommandText = "print 'Here I am: a print.';";
for (int i = 0; i < 200; ++i)
{
await cmd.ExecuteNonQueryAsync();
Thread.Sleep(5);
}
}
}
public void InfoMessage_are_monitored_when_SqlHelper_LogSqlServerInfoMessage_is_set_to_true()
{
SqlHelper.LogSqlServerInfoMessage = true;
IReadOnlyList <ActivityMonitorSimpleCollector.Entry> entries = null;
var m = new ActivityMonitor(false);
// The messages are Traced but the monitor level is temporarily set to LogFilter.Trace:
// Even if the monitor should not catch them, info messages traces will be emitted.
m.MinimalFilter = LogFilter.Release;
using (m.CollectEntries(logs => entries = logs, LogLevelFilter.Debug))
using (m.Output.CreateBridgeTo(TestHelper.Monitor.Output.BridgeTarget))
using (var ctx = new SqlStandardCallContext(m))
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
c.ExplicitOpen();
var cmd = c.Connection.CreateCommand();
cmd.CommandText = "print 'Here I am: a print.';";
cmd.ExecuteNonQuery();
}
entries.Any(e => e.Text.Contains("Here I am: a print.")).Should().BeTrue();
SqlHelper.LogSqlServerInfoMessage = false;
}
public static List <T> ExecuteReader <T>(this ISqlConnectionController @this, SqlCommand cmd, Func <SqlDataRow, T?> builder) where T : notnull
{
List <T> ReadRows(SqlCommand c)
{
var collector = new List <T>();
using (var r = c.ExecuteReader())
{
var row = new SqlDataRow(r);
while (r.Read())
{
var o = builder(row);
if (o is not null)
{
collector.Add(o);
}
}
}
return(collector);
}
return(ExecuteQuery(@this, cmd, ReadRows));
}
void AsyncCallCatch <TException>(string cmd, string connectionString = null)
{
using (IDisposableSqlCallContext c = new SqlStandardCallContext(TestHelper.Monitor))
using (var command = new SqlCommand(cmd))
{
ISqlConnectionController con = c[connectionString ?? TestHelper.GetConnectionString()];
try
{
// If the asynchronous process is lost (if the exception is not correctly managed),
// this test will fail with a task Cancelled exception after:
// - 30 second when testing for connection string.... because when trying to resolve a bad server name it takes a loooooooong time.
// - 1 second in other cases.
CancellationTokenSource source = new CancellationTokenSource();
source.CancelAfter(connectionString == null ? 1000 : 30 * 1000);
con.ExecuteNonQueryAsync(command, source.Token)
.Wait();
}
catch (AggregateException ex)
{
Assert.That(ex.GetBaseException(), Is.InstanceOf <TException>());
}
}
}
public void Direct_open_close_of_the_connection_is_possible()
{
void DoSomething(SqlConnection con)
{
bool wasClosed = con.State == ConnectionState.Closed;
if (wasClosed)
{
con.Open();
}
var cmd = con.CreateCommand();
cmd.CommandText = "select 1;";
cmd.ExecuteScalar().Should().Be(1);
if (wasClosed)
{
con.Close();
}
}
using (var ctx = new SqlStandardCallContext(TestHelper.Monitor))
{
ISqlConnectionController c = ctx[TestHelper.GetConnectionString()];
// Closed.
DoSomething(c.Connection);
c.Connection.State.Should().Be(ConnectionState.Closed);
using (c.ExplicitOpen())
{
c.Connection.State.Should().Be(ConnectionState.Open);
DoSomething(c.Connection);
c.Connection.State.Should().Be(ConnectionState.Open);
}
c.Connection.State.Should().Be(ConnectionState.Closed);
}
}
static string ReadMessage(ISqlConnectionController c, int id)
{
_readMessageCommand.Parameters[0].Value = id;
return((string)c.ExecuteScalar(_readMessageCommand));
}
public static Task PreOpenAsync(this ISqlConnectionController ctx, CancellationToken cancellationToken = default)
{
return(ctx.ExplicitOpenAsync(cancellationToken));
}
public GroupQueries(ISqlConnectionController controller)
{
_controller = controller;
}
public static void PreOpen(this ISqlConnectionController ctx)
{
ctx.ExplicitOpen();
}
static int AddMessage(ISqlConnectionController c, string msg)
{
_addMessageCommand.Parameters[0].Value = msg;
return((int)c.ExecuteScalar(_addMessageCommand));
}
static async Task <int> AddMessageAsync(ISqlConnectionController c, string msg)
{
_addMessageCommand.Parameters[0].Value = msg;
return((int)await c.ExecuteScalarAsync(_addMessageCommand));
}
public ProjectQueries(ISqlCallContext ctx, SqlDefaultDatabase sqlDefaultDatabase)
{
_controller = ctx.GetConnectionController(sqlDefaultDatabase);
}
static async Task <string> ReadMessageAsync(ISqlConnectionController c, int id)
{
_readMessageCommand.Parameters[0].Value = id;
return((string)await c.ExecuteScalarAsync(_readMessageCommand));
}