public async Task DereferenceFromQuery_ManualWrapping()
{
var db = new SPTestsDatabase();
// without a cursor param, nothing will trigger the wrapping transaction support in Massive
// so in this case we need to add the wrapping transaction manually (with TransactionScope or
// BeginTransaction, see other examples in this file)
int count = 0;
using (var conn = await db.OpenConnectionAsync())
{
using (var trans = conn.BeginTransaction())
{
var employees = await db.QueryAsync("SELECT * FROM cursor_employees()", conn);
await employees.ForEachAsync(employee =>
{
Console.WriteLine(employee.firstname + " " + employee.lastname);
count++;
});
//scope.Complete();
}
}
Assert.AreEqual(9, count);
}
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 PassingCursorInputParameter(bool explicitConnection)
{
var db = new SPTestsDatabase(ProviderName, explicitConnection);
if (explicitConnection)
{
MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db);
}
// To share cursors between commands in Oracle the commands must use the same connection
using (var conn = await db.OpenConnectionAsync(
explicitConnection ?
MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, ProviderName) :
null))
{
var res1 = await db.ExecuteWithParamsAsync("begin open :p_rc for select * from emp where deptno = 10; end;", outParams : new { p_rc = new Cursor() }, connection : conn);
Assert.AreEqual(typeof(Cursor), res1.p_rc.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)res1.p_rc).CursorRef.GetType().Name);
await db.ExecuteAsync("delete from processing_result", connection : conn);
// oracle demo code takes the input cursor and writes the results to `processing_result` table
var res2 = await db.ExecuteProcedureAsync("cursor_in_out.process_cursor", inParams : new { p_cursor = res1.p_rc }, connection : conn);
Assert.AreEqual(0, ((IDictionary <string, object>)res2).Count);
var processedRows = await(await db.QueryAsync("select * from processing_result", connection: conn)).ToListAsync();
Assert.AreEqual(3, processedRows.Count);
}
}
public async Task LargeCursor_ExplicitFetch()
{
int FetchSize = 20000;
int count = 0;
int batchCount = 0;
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 result = await db.ExecuteProcedureAsync("lump", returnParams: new { cursor = new Cursor() }, connection: conn);
while(true)
{
var fetchTest = await db.QueryAsync($@"FETCH {FetchSize} FROM ""{result.cursor.CursorRef}""", connection: conn);
int subcount = 0;
await fetchTest.ForEachAsync(item => {
count++;
subcount++;
// 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);
});
if(subcount == 0)
{
break;
}
batchCount++;
}
await db.ExecuteAsync($@"CLOSE ""{result.cursor.CursorRef}""", connection: conn);
trans.Commit();
}
}
Assert.AreEqual((LargeCursorSize + FetchSize - 1) / FetchSize, batchCount);
Assert.AreEqual(LargeCursorSize, count);
}
public async Task Devart_ParameterCheck(bool explicitConnection)
{
var db = new SPTestsDatabase(ProviderName, explicitConnection);
if (explicitConnection)
{
MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db);
}
dynamic result;
using (var connection = await db.OpenConnectionAsync(
explicitConnection ?
WhenDevart.AddLicenseKey(ProviderName, MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadTestConnection, ProviderName)) :
null))
{
using (var command = db.CreateCommandWithParams("testproc_in_out", isProcedure: true, connection: connection))
{
// uses a dynamic cast to set a provider-specific property without explicitly depending on the provider library
((dynamic)command).ParameterCheck = true;
// Devart-specific: makes a round-trip to the database to fetch the parameter names
command.Prepare();
command.Parameters["param1"].Value = 10;
await db.ExecuteAsync(command, connection : connection);
result = db.ResultsAsExpando(command);
}
}
Assert.AreEqual(20, result.param2);
}
public async Task DereferenceFromQuery_ManualWrapping(bool explicitConnection)
{
var db = new SPTestsDatabase(explicitConnection);
if (explicitConnection)
{
MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db);
}
// without a cursor param, nothing will trigger the wrapping transaction support in Massive
// so in this case we need to add the wrapping transaction manually (with TransactionScope or
// BeginTransaction, see other examples in this file)
int count = 0;
using (var conn = await db.OpenConnectionAsync(
explicitConnection ?
MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, TestConstants.ProviderName) :
null
))
{
using (var trans = conn.BeginTransaction())
{
var employees = await db.QueryAsync("SELECT * FROM cursor_employees()", conn);
await employees.ForEachAsync(employee =>
{
Console.WriteLine(employee.firstname + " " + employee.lastname);
count++;
});
//scope.Complete();
}
}
Assert.AreEqual(9, count);
}
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 Devart_ParameterCheck()
{
var db = new SPTestsDatabase(ProviderName);
dynamic result;
using (var connection = await db.OpenConnectionAsync())
{
using (var command = db.CreateCommandWithParams("testproc_in_out", isProcedure: true, connection: connection))
{
// uses a dynamic cast to set a provider-specific property without explicitly depending on the provider library
((dynamic)command).ParameterCheck = true;
// Devart-specific: makes a round-trip to the database to fetch the parameter names
command.Prepare();
command.Parameters["param1"].Value = 10;
await db.ExecuteAsync(command);
result = db.ResultsAsExpando(command);
}
}
Assert.AreEqual(20, result.param2);
}