public void using_ISqlConnectionController_extension_methods()
{
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 );
c.ExecuteNonQuery( create );
c.ExecuteScalar( scalar ).Should().Be( "Three" );
var rowResult = c.ExecuteSingleRow( row, r => Tuple.Create( r.GetInt32( 0 ), r.GetString( 1 ) ) );
rowResult.Item1.Should().Be( 1 );
rowResult.Item2.Should().Be( "One" );
var readerResult = c.ExecuteReader( 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" );
c.ExecuteNonQuery( clean );
}
}
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 );
}
}
static string ReadMessage(ISqlConnectionController c, int id)
{
_readMessageCommand.Parameters[0].Value = id;
return((string)c.ExecuteScalar(_readMessageCommand));
}
static int AddMessage(ISqlConnectionController c, string msg)
{
_addMessageCommand.Parameters[0].Value = msg;
return((int)c.ExecuteScalar(_addMessageCommand));
}