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"); } }