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