public static void EnsureDbSetup(string connectionString)
{
var tds = TdsConnectionPools.GetConnectionPool(connectionString);
tds.ExecuteNonQuery(@"
If (Object_Id('Posts') Is Null)
Begin
CREATE Table Posts
(
Id int identity primary key,
[Text] varchar(max) not null,
CreationDate datetime not null,
LastChangeDate datetime not null,
Counter1 int,
Counter2 int,
Counter3 int,
Counter4 int,
Counter5 int,
Counter6 int,
Counter7 int,
Counter8 int,
Counter9 int
);
Set NoCount On;
Declare @i int = 0;
While @i <= 5001
Begin
Insert Posts ([Text],CreationDate, LastChangeDate) values (replicate('x', 2000), GETDATE(), GETDATE());
Set @i = @i + 1;
End
End
");
}
public async Task can_Serailize_normal_sqltype_to_object()
{
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
var x = await tds.ExecuteQueryAsync <TestNullType>(TestStatements.SelectAllNotNullTypes);
var v = x[0].a == null ? x[1] : x[0];
Assert.Equal(new DateTime(2018, 12, 31), v.a);
Assert.Equal(new TimeSpan(10, 11, 12), v.b);
Assert.Equal(new DateTime(2018, 12, 31, 10, 11, 12), v.c);
Assert.Equal(new DateTimeOffset(2018, 12, 31, 10, 11, 12, new TimeSpan(5, 0, 0)), v.d);
Assert.True(v.e);
Assert.Equal((byte?)1, v.f);
Assert.Equal((short?)1, v.g);
Assert.Equal(1, v.h);
Assert.Equal(1, v.i);
Assert.Equal(1, v.j);
Assert.Equal(1, v.k);
Assert.Equal(1, v.l);
Assert.Equal(1, v.m);
Assert.Equal(new DateTime(2018, 12, 31), v.n);
Assert.Equal(new DateTime(2018, 12, 31), v.o);
Assert.Equal(new Guid("9e383328-69d7-4e73-8126-e25a1be94ae9"), v.p);
Assert.Equal(new byte[] { 1 }, v.q);
Assert.Equal(new byte[] { 49, 50, 51, 52, 53, 54, 55, 56, 57 }, v.r);
Assert.Equal(new[] { '1' }, v.s);
Assert.Equal("123456789", v.t);
Assert.Equal(new[] { '1' }, v.u);
Assert.StartsWith("123456789", v.v);
Assert.Equal(1, v.x);
Assert.Equal(1_000_000_000_000_000_000, v.y);
Assert.Equal(9_999_999_999_999_999_583_119_736_832M, v.z);
Assert.Equal(new byte[] { 0, 0, 0, 0, 0, 0, 0, 1 }, v.zz);
}
public async Task can_Serailize_all_nulls()
{
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
var x = await tds.ExecuteQueryAsync <TestNullType>(TestStatements.SelectAllNullTypes);
var v = x[0];
Assert.Null(v.a);
Assert.Null(v.b);
Assert.Null(v.c);
Assert.Null(v.d);
Assert.Null(v.e);
Assert.Null(v.f);
Assert.Null(v.g);
Assert.Null(v.h);
Assert.Null(v.i);
Assert.Null(v.j);
Assert.Null(v.k);
Assert.Null(v.l);
Assert.Null(v.m);
Assert.Null(v.n);
Assert.Null(v.o);
Assert.Null(v.p);
Assert.Null(v.q);
Assert.Null(v.r);
Assert.Null(v.s);
Assert.Null(v.t);
Assert.Null(v.u);
Assert.Null(v.v);
Assert.Null(v.w);
Assert.Null(v.x);
Assert.Null(v.y);
Assert.Null(v.z);
Assert.Null(v.zz);
}
public async Task can_login()
{
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
await tds.ExecuteNonQueryAsync("print 1");
tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
await tds.ExecuteNonQueryAsync("print 1");
}
public async Task can_read_Udt()
{
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
await cnn.ExecuteNonQueryAsync(TestStatement);
var x = await cnn.ExecuteQueryAsync <UdtTypes>("DECLARE @u Utf8String = CONVERT(Utf8String, 'hello world') SELECT Utf8String=@u");
Assert.Empty(x[0].Utf8String);
}
public async Task ReadLargeColumn()
{
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
var result = await cnn.ExecuteQueryAsync <TestLongStringType>(@"
DECLARE @v nvarchar(max)=CAST( replicate('1',32768) AS nvarchar(max) )
SELECT s=@v");
Assert.Equal(8000, result[0].s.Length);
}
public void Can_begin_transaction()
{
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
using (var transaction = cnn.BeginTransaction())
{
transaction.ExecuteNonQuery("print 1");
transaction.Commit();
}
}
public async Task ReadWmptyXml()
{
var tds = TdsConnectionPools.GetConnectionPool(@"Server=(localdb)\mssqllocaldb;Database=TdsTest;Trusted_Connection=True;");
{
var result = await tds.ExecuteQueryAsync <TestLongStringType>(@"SELECT s=cast('' as xml)");
;
Assert.Equal(0, result[0].s.Length);
}
}
public MedellaOrm()
{
var connections = new TdsConnection[conc];
_tds = TdsConnectionPools.GetConnectionPool(_connectionString);
var task = Parallel.For(0, conc, (x) => connections[x] = _tds.GetConnection());
//OrmTester.EnsureDbSetup(_connectionString);
task = Parallel.For(0, conc, (x) => _tds.Return(connections[x]));
}
public async Task ReadLargeColumn2()
{
var tds = TdsConnectionPools.GetConnectionPool(@"Server=(localdb)\mssqllocaldb;Database=TdsTest;Trusted_Connection=True;");
{
tds.ExecuteNonQuery("if OBJECT_ID('TestVarCharMax') is not null DROP TABLE TestVarCharMax CREATE TABLE TestVarCharMax (test varchar(max))");
tds.ExecuteNonQuery("Insert TestVarCharMax (test) values (replicate(CONVERT(VARCHAR(MAX),'x'), 16000))");
var result = await tds.ExecuteQueryAsync <TestLongStringType>(@"SELECT s=test FROM TestVarCharMax");
Assert.Equal(16000, result[0].s.Length);
}
}
public async Task ReadXml()
{
var tds = TdsConnectionPools.GetConnectionPool(@"Server=(localdb)\mssqllocaldb;Database=TdsTest;Trusted_Connection=True;");
{
var result = await tds.ExecuteQueryAsync <TestLongStringType>(@"
DECLARE @v xml=CAST( '<Data><DepartmentID>x</DepartmentID></Data>' AS xml )
SELECT s=@v");
;
Assert.Equal(43, result[0].s.Length);
}
}
public void Can_AutoRollback_transaction()
{
var guid = Guid.NewGuid();
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
using (var transaction = cnn.BeginTransaction())
{
transaction.ExecuteNonQuery($"CREATE TABLE [{guid}] (id int)");
}
cnn.ExecuteNonQuery($"if OBJECT_ID('{guid}') is not null RAISERROR (N'fatal',20,1) WITH LOG ");
}
public void Can_commit_transaction()
{
var guid = Guid.NewGuid();
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
using (var transaction = cnn.BeginTransaction())
{
transaction.ExecuteNonQuery($"CREATE TABLE [{guid}] (id int)");
transaction.Commit();
}
cnn.ExecuteNonQuery($"DROP TABLE [{guid}]");
}
public void Get_50_connections_return_to_the_pool_and_use_one()
{
var x = new TdsConnection[50];
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
for (var j = 0; j < 50; j++)
{
x[j] = tds.GetConnection();
}
//OrmTester.EnsureDbSetup(_connectionString);
for (var j = 0; j < 50; j++)
{
tds.Return(x[j]);
}
}
public void Connection_returned_cleaned_to_the_pool()
{
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
var cnninternal = cnn.GetConnection();
cnninternal.ExecuteNonQuery("print 1");
Assert.Equal(3, cnninternal.SqlMessages.Count);
cnn.Return(cnninternal);
cnninternal = cnn.GetConnection();
cnninternal.ExecuteNonQuery("print 1");
Assert.Equal(3, cnninternal.SqlMessages.Count);
cnn.Return(cnninternal);
cnninternal = cnn.GetConnection();
Assert.Equal(2, cnninternal.SqlMessages.Count);
cnn.Return(cnninternal);
}
public void Can_Rollback_OnsqlError_transaction()
{
var guid = Guid.NewGuid();
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
try
{
using (var transaction = cnn.BeginTransaction())
{
transaction.ExecuteNonQuery($"RAISERROR (N'fatal',11,1) WITH LOG ");
transaction.Commit();
}
}
catch (Exception)
{
// ignored
}
cnn.ExecuteNonQuery($"if OBJECT_ID('{guid}') is not null RAISERROR (N'fatal',20,1) WITH LOG ");
}
public async Task can_upload_int_Column()
{
var size = 5531;
var obj = new TestBulkcopy[size];
for (int i = 0; i < size; i++)
{
obj[i] = new TestBulkcopy {
Id = i, Id1 = i + 1
};
}
var cnn = TdsConnectionPools.GetConnectionPool(ConnectionString);
await cnn.ExecuteNonQueryAsync("if OBJECT_ID('bulkcopy') is not null DROP TABLE bulkcopy CREATE TABLE bulkcopy (Id int, Id1 bigint) ");
await cnn.ExecuteNonQueryAsync("if OBJECT_ID('bulkcopy2') is not null DROP TABLE bulkcopy2 CREATE TABLE bulkcopy2 (Id int, Id1 bigint) ");
await cnn.BulkInsertAsync(obj.ToList(), "bulkcopy");
//var task2 = cnn.BulkInsertAsync(obj.ToList(), "bulkcopy2");
//var task3 = cnn.BulkInsertAsync(obj.ToList(), "bulkcopy");
//Task.WaitAll(task1, task2, task3);
}
public async Task can_read_timestamp()
{
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
var x = await tds.ExecuteQueryAsync <TestIntType>(@"DECLARE @zz timestamp; Select zz=@zz");
}
public async Task can_read_not_null_fields()
{
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
var x = await tds.ExecuteQueryAsync <NotNullTypes>(TestStatements.NotNullTable);
}