public void TestInsert()
{
try
{
_connection.ExecuteSql("CREATE TYPE InsertTestDataTVP AS TABLE (X [int], Z [int])");
using (var connection = _connectionStringBuilder.OpenWithTransaction())
{
connection.ExecuteSql("CREATE TABLE InsertTestDataTable (X [int] identity (5, 1), Z [int])");
connection.ExecuteSql("CREATE PROC InsertTestData @Z [int] AS INSERT INTO InsertTestDataTable (Z) OUTPUT inserted.X VALUES (@Z)");
connection.ExecuteSql("CREATE PROC UpdateTestData @X [int], @Z [int] AS UPDATE InsertTestDataTable SET Z=@Z WHERE X=@X SELECT X=0");
connection.ExecuteSql("CREATE PROC InsertMultipleTestData @data [InsertTestDataTVP] READONLY AS INSERT INTO InsertTestDataTable (Z) OUTPUT inserted.X SELECT Z FROM @data");
connection.ExecuteSql("CREATE PROC UpdateMultipleTestData @data [InsertTestDataTVP] READONLY AS UPDATE InsertTestDataTable SET Z=data.Z FROM @data data WHERE data.X = InsertTestDataTable.X SELECT X=0 FROM @data");
var i = connection.As<ITestInsertUpdate>();
{
// single insert
TestDataClasses.TestData data = new TestDataClasses.TestData() { Z = 4 };
i.InsertTestData(data);
Assert.AreEqual(5, data.X, "ID should be returned");
// single update
i.UpdateTestData(data);
Assert.AreEqual(0, data.X, "ID should be reset");
// multiple insert
var list = new[]
{
new TestDataClasses.TestData() { Z = 5 },
new TestDataClasses.TestData() { Z = 6 }
};
i.InsertMultipleTestData(list);
Assert.AreEqual(6, list[0].X, "ID should be returned");
Assert.AreEqual(7, list[1].X, "ID should be returned");
// multiple update
i.UpdateMultipleTestData(list);
Assert.AreEqual(0, list[0].X, "ID should be reset");
Assert.AreEqual(0, list[1].X, "ID should be reset");
}
{
// single insert
TestDataClasses.TestData data = new TestDataClasses.TestData() { Z = 4 };
i.InsertTestDataAsync(data).Wait();
Assert.AreEqual(8, data.X, "ID should be returned");
// single update
i.UpdateTestDataAsync(data).Wait();
Assert.AreEqual(0, data.X, "ID should be reset");
// multiple insert
var list = new[]
{
new TestDataClasses.TestData() { Z = 5 },
new TestDataClasses.TestData() { Z = 6 }
};
i.InsertMultipleTestDataAsync(list).Wait();
Assert.AreEqual(9, list[0].X, "ID should be returned");
Assert.AreEqual(10, list[1].X, "ID should be returned");
// multiple update
i.UpdateMultipleTestDataAsync(list).Wait();
Assert.AreEqual(0, list[0].X, "ID should be reset");
Assert.AreEqual(0, list[1].X, "ID should be reset");
}
}
}
finally
{
_connection.ExecuteSql("DROP TYPE InsertTestDataTVP");
}
}
public void TestOutputParameters()
{
try
{
_connection.ExecuteSql("CREATE TYPE InsertTestDataTVP AS TABLE (X [int], Z [int])");
using (var connection = _connectionStringBuilder.OpenWithTransaction())
{
connection.ExecuteSql("CREATE TABLE InsertTestDataTable (X [int] identity (5, 1), Z [int])");
connection.ExecuteSql("CREATE PROC ExecuteWithOutputParameter @p [int] = NULL OUTPUT AS SELECT @p=@p+1");
connection.ExecuteSql("CREATE PROC ExecuteScalarWithOutputParameter @p [int] = NULL OUTPUT AS SELECT @p=@p+1 SELECT 7");
connection.ExecuteSql("CREATE PROC QueryWithOutputParameter @p [int] = NULL OUTPUT AS SELECT @p=@p+1 SELECT 5");
connection.ExecuteSql("CREATE PROC QueryResultsWithOutputParameter @p int OUTPUT AS " + TestDataClasses.ParentTestData.Sql + " SELECT @p=@p+1 SELECT @p");
connection.ExecuteSql("CREATE PROC InsertWithOutputParameter @data [InsertTestDataTVP] READONLY, @p [int] OUTPUT AS INSERT INTO InsertTestDataTable (Z) OUTPUT inserted.X SELECT z FROM @data OUTPUT SELECT @p=@p+1");
var i = connection.As<ITestOutputParameters>();
// test execute with output parameter
int original = 2;
int p = original;
i.ExecuteWithOutputParameter(out p);
Assert.AreEqual(original + 1, p);
// test executescalar with output parameter
p = original;
var scalar = i.ExecuteScalarWithOutputParameter(out p);
Assert.AreEqual(original + 1, p);
Assert.AreEqual(7, scalar);
// test query with output parameters
p = original;
var results = i.QueryWithOutputParameter(out p);
Assert.AreEqual(original + 1, p);
Assert.AreEqual(1, results.Count);
Assert.AreEqual(5, results[0]);
// test query results with output parameters
p = original;
i.QueryResultsWithOutputParameter(out p);
Assert.AreEqual(original + 1, p);
// test insert with output parameters
TestDataClasses.TestData data = new TestDataClasses.TestData() { Z = 4 };
var list = new List<TestDataClasses.TestData>() { data };
p = original;
i.InsertWithOutputParameter(list, out p);
Assert.AreEqual(original + 1, p);
}
}
finally
{
_connection.ExecuteSql("DROP TYPE InsertTestDataTVP");
}
}
