public async Task InputCursors_BeginTransaction() { var db = new SPTestsDatabase(); using (var conn = await db.OpenConnectionAsync()) { // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle) using (var trans = conn.BeginTransaction()) { var cursors = await db.ExecuteProcedureAsync("cursorNByOne", outParams : new { c1 = new Cursor(), c2 = new Cursor() }, connection : conn); var cursor1 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = cursors.c1 }, connection : conn); int count1 = 0; await cursor1.ForEachAsync(item => { Assert.AreEqual(11, item.myint1); Assert.AreEqual(22, item.myint2); count1++; }); Assert.AreEqual(1, count1); var cursor2 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = cursors.c2 }, connection : conn); int count2 = 0; await cursor2.ForEachAsync(item => { Assert.AreEqual(33, item.myint1); Assert.AreEqual(44, item.myint2); count2++; }); Assert.AreEqual(1, count2); trans.Commit(); } } }
public async Task InputCursors_1XN(bool explicitConnection) { var db = new SPTestsDatabase(explicitConnection); db.NpgsqlAutoDereferenceCursors = false; // for this instance only if (explicitConnection) { MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db); } // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle) using (var conn = await db.OpenConnectionAsync( explicitConnection ? MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, TestConstants.ProviderName) : null )) { using (var trans = conn.BeginTransaction()) { // Including a cursor param is optional and makes no difference, because Npgsql/PostgreSQL is lax about such things // and we don't need to hint to Massive to do anything special var cursors = await db.QueryFromProcedureAsync("cursorOneByN", connection : conn); //, outParams: new { abcdef = new Cursor() }); string[] cursor = new string[2]; int i = 0; await cursors.ForEachAsync(item => { cursor[i++] = item.cursoronebyn; }); Assert.AreEqual(2, i); var cursor1 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = new Cursor(cursor[0]) }, connection : conn); int count1 = 0; await cursor1.ForEachAsync(item => { Assert.AreEqual(1, item.myint1); Assert.AreEqual(2, item.myint2); count1++; }); Assert.AreEqual(1, count1); var cursor2 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = new Cursor(cursor[1]) }, connection : conn); int count2 = 0; await cursor2.ForEachAsync(item => { Assert.AreEqual(3, item.myint1); Assert.AreEqual(4, item.myint2); count2++; }); Assert.AreEqual(1, count2); trans.Commit(); } } }
public async Task InputCursors_TransactionScope(bool explicitConnection) { var db = new SPTestsDatabase(explicitConnection); if (explicitConnection) { MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db); } // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle) using (var conn = await db.OpenConnectionAsync( explicitConnection ? MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, TestConstants.ProviderName) : null )) { using (var trans = conn.BeginTransaction()) { var cursors = await db.ExecuteProcedureAsync("cursorNByOne", outParams : new { c1 = new Cursor(), c2 = new Cursor() }, connection : conn); var cursor1 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = cursors.c1 }, connection : conn); int count1 = 0; await cursor1.ForEachAsync(item => { Assert.AreEqual(11, item.myint1); Assert.AreEqual(22, item.myint2); count1++; }); Assert.AreEqual(1, count1); var cursor2 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = cursors.c2 }, connection : conn); int count2 = 0; await cursor2.ForEachAsync(item => { Assert.AreEqual(33, item.myint1); Assert.AreEqual(44, item.myint2); count2++; }); Assert.AreEqual(1, count2); trans.Commit(); } } }
public async Task QuerySetOfRecordsFromFunction() { var db = new SPTestsDatabase(); var setOfRecords = await db.QueryFromProcedureAsync("sum_n_product_with_tab", new { x = 10 }); int count = 0; await setOfRecords.ForEachAsync(innerRecord => { Console.WriteLine(innerRecord.sum + "\t|\t" + innerRecord.product); count++; }); Assert.AreEqual(4, count); }
public async Task QueryFromStoredProcedure() { var db = new SPTestsDatabase(); var people = await db.QueryFromProcedureAsync("uspGetEmployeeManagers", new { BusinessEntityID = 35 }); int count = 0; await people.ForEachAsync(person => { Console.WriteLine(person.FirstName + " " + person.LastName); count++; }); Assert.AreEqual(3, count); }
public async Task DereferenceCursorOutputParameter() { var db = new SPTestsDatabase(); // Unlike the Oracle data access layer, Npgsql v3 does not dereference cursor parameters. // We have added back the support for this which was previously in Npgsql v2. var employees = await db.QueryFromProcedureAsync("cursor_employees", outParams: new { refcursor = new Cursor() }); int count = 0; await employees.ForEachAsync(employee => { Console.WriteLine(employee.firstname + " " + employee.lastname); count++; }); Assert.AreEqual(9, count); }
public async Task DereferenceCursorOutputParameter() { var db = new SPTestsDatabase(ProviderName); // Oracle procedure one cursor output variables var moreEmployees = await db.QueryFromProcedureAsync("myproc", outParams : new { prc = new Cursor() }); int count = 0; await moreEmployees.ForEachAsync(employee => { Console.WriteLine(employee.EMPNO + " " + employee.ENAME); count++; }); Assert.AreEqual(14, count); }
public async Task DereferenceCursorValuedFunction() { var db = new SPTestsDatabase(ProviderName); // Oracle function one cursor return value var employees = await db.QueryFromProcedureAsync("get_dept_emps", inParams : new { p_DeptNo = 10 }, returnParams : new { v_rc = new Cursor() }); int count = 0; await employees.ForEachAsync(employee => { Console.WriteLine(employee.EMPNO + " " + employee.ENAME); count++; }); Assert.AreEqual(3, count); }
public async Task LargeCursor_AutomaticDereferencing() { var db = new SPTestsDatabase(); // Either of these will show big server-side buffers in PostrgeSQL logs (but will still pass) //db.AutoDereferenceFetchSize = -1; // FETCH ALL //db.AutoDereferenceFetchSize = 400000; var fetchTest = await db.QueryFromProcedureAsync("lump", returnParams: new { cursor = new Cursor() }); int count = 0; await fetchTest.ForEachAsync(item => { count++; // there is no ORDER BY (it would not be sensible on such a huge data set) - this only sometimes works... //Assert.AreEqual(count, item.id); }); Assert.AreEqual(LargeCursorSize, count); }
public async Task DereferenceCursor() { // There is probably no situation in which it would make sense to do this (a procedure returning a cursor should be for use by another // procedure only - if at all); the remarks above and the example immediately below document why this is the wrong thing to do. var db = new SPTestsDatabase(); var SQL = "DECLARE @MyCursor CURSOR;\r\n" + "EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;\r\n" + "WHILE(@@FETCH_STATUS = 0)\r\n" + "BEGIN;\r\n" + "\tFETCH NEXT FROM @MyCursor;\r\n" + "END;\r\n" + "CLOSE @MyCursor;\r\n" + "DEALLOCATE @MyCursor;\r\n"; var resultSets = await db.QueryMultipleAsync(SQL); int count = 0; await resultSets.ForEachAsync(async results => { await results.ForEachAsync(item => { count++; Assert.AreEqual(typeof(string), item.CurrencyCode.GetType()); Assert.AreEqual(typeof(string), item.Name.GetType()); }); }); Assert.AreEqual(105, count); // An example of the correct way to do it var fastResults = await db.QueryFromProcedureAsync("uspCurrencySelect"); int fastCount = 0; await fastResults.ForEachAsync(item => { fastCount++; Assert.AreEqual(typeof(string), item.CurrencyCode.GetType()); Assert.AreEqual(typeof(string), item.Name.GetType()); }); Assert.AreEqual(105, fastCount); }
public async Task Dereferencing_RespondsToCancellation() { using (CancellationTokenSource cts = new CancellationTokenSource()) { var db = new SPTestsDatabase(); // Unlike the Oracle data access layer, Npgsql v3 does not dereference cursor parameters. // We have added back the support for this which was previously in Npgsql v2. var employees = await db.QueryFromProcedureAsync("cursor_employees", cts.Token, outParams: new { refcursor = new Cursor() }); int count = 0; Assert.ThrowsAsync<TaskCanceledException>(async () => { await employees.ForEachAsync(employee => { Console.WriteLine(employee.firstname + " " + employee.lastname); count++; if (count == 5) { cts.Cancel(); } }); }); Assert.AreEqual(5, count); } }