public async Task InitialNullInputOutputParam()
{
var db = new SPTestsDatabase(ProviderName);
dynamic squareResult = await db.ExecuteProcedureAsync("squareNum", ioParams : new { x = (int?)null });
Assert.AreEqual(null, squareResult.x);
}
public async Task InputOutputParam()
{
var db = new SPTestsDatabase();
dynamic squareResult = await db.ExecuteProcedureAsync("square_num", ioParams : new { x = 4 });
Assert.AreEqual(16, squareResult.x);
}
public async Task InitialNullDateReturnParamMethod1()
{
var db = new SPTestsDatabase();
// This method will work on any provider
dynamic dateResult = await db.ExecuteProcedureAsync("get_date", returnParams: new { d = (DateTime?)null });
Assert.AreEqual(typeof(DateTime), dateResult.d.GetType());
}
public async Task InOut_Param_SP()
{
var db = new SPTestsDatabase(ProviderName);
var result = await db.ExecuteProcedureAsync("testproc_inout", ioParams : new { param1 = 10 });
Assert.AreEqual(20, result.param1);
}
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 InputAndReturnParams()
{
var db = new SPTestsDatabase(ProviderName);
dynamic fnResult = await db.ExecuteProcedureAsync("findMax", inParams : new { x = 1, y = 3 }, returnParams : new { returnValue = 0 });
Assert.AreEqual(3, fnResult.returnValue);
}
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 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 Procedure_Call()
{
var db = new SPTestsDatabase(ProviderName);
var result = await db.ExecuteProcedureAsync("rewards_report_for_date", inParams : new { min_monthly_purchases = 3, min_dollar_amount_purchased = 20, report_date = new DateTime(2005, 5, 1) }, outParams : new { count_rewardees = 0 });
Assert.AreEqual(27, result.count_rewardees);
}
public async Task InputAndOutputParams()
{
var db = new SPTestsDatabase(ProviderName);
dynamic procResult = await db.ExecuteProcedureAsync("findMin", inParams : new { x = 1, y = 3 }, outParams : new { z = 0 });
Assert.AreEqual(1, procResult.z);
}
public async Task In_Out_Params_SP()
{
var db = new SPTestsDatabase(ProviderName);
// new skool
var result = await db.ExecuteProcedureAsync("testproc_in_out", inParams : new { param1 = 10 }, outParams : new { param2 = 0 });
Assert.AreEqual(20, result.param2);
}
public async Task InitialNullInputOutputParam()
{
var db = new SPTestsDatabase();
dynamic xParam = new ExpandoObject();
xParam.x = null;
dynamic squareResult = await db.ExecuteProcedureAsync("square_num", ioParams: xParam);
Assert.AreEqual(null, squareResult.x);
}
public async Task PostgresAnonymousParametersB()
{
var db = new SPTestsDatabase();
// NB This function can't be called except with anonymous parameters.
// (I believe you can't even do it with a SQL block, because Postgres anonymous SQL blocks do not accept parameters? May be wrong...)
dynamic addResult = await db.ExecuteProcedureAsync("add_em", inParams: new object[] { 4, 2 }, returnParams: new { RETURN = 0 });
Assert.AreEqual(6, addResult.RETURN);
}
public async Task IntegerReturnParam()
{
var db = new SPTestsDatabase();
// NB Massive is converting all Postgres return params to output params because Npgsql treats all function
// output and return as output (which is because PostgreSQL itself treats them as the same, really).
dynamic fnResult = await db.ExecuteProcedureAsync("find_max", inParams: new { x = 6, y = 7 }, returnParams: new { returnValue = true });
Assert.AreEqual(7, fnResult.returnValue);
}
public async Task Function_Call_Bool()
{
var db = new SPTestsDatabase(ProviderName);
var result = await db.ExecuteProcedureAsync("inventory_in_stock",
inParams : new { p_inventory_id = 5 },
returnParams : new { retval = false });
Assert.AreEqual(true, result.retval);
}
public async Task PostgresAnonymousParametersA()
{
var db = new SPTestsDatabase();
// Only PostgreSQL supports anonymous parameters (AFAIK) - we treat object[] in the context of params differently from
// how it is treated when it appears in args in the standard Massive API, to provide support for this. (Note, object[]
// makes no sense in the context of named parameters otherwise, and will throw an exception on the other DBs.)
dynamic fnResultAnon = await db.ExecuteProcedureAsync("find_max", inParams: new object[] { 12, 7 }, returnParams: new { returnValue = 0 });
Assert.AreEqual(12, fnResultAnon.returnValue);
}
public async Task NonQueryFromMixedCursorOutput()
{
var db = new SPTestsDatabase();
// Following the Oracle pattern this will not dereference: we get a variable value and a cursor ref.
var itemCursorMix = await db.ExecuteProcedureAsync("cursor_mix", outParams: new { anyname = new Cursor(), othername = 0 });
Assert.AreEqual(42, itemCursorMix.othername);
Assert.AreEqual(typeof(Cursor), itemCursorMix.anyname.GetType());
Assert.AreEqual(typeof(string), ((Cursor)itemCursorMix.anyname).CursorRef.GetType()); // NB PostgreSql ref cursors return as string
}
public async Task InitialNullDateReturnParamMethod2()
{
var db = new SPTestsDatabase();
// NB This is PostgreSql specific; Npgsql completely ignores the output parameter type and sets it (sensibly) from the return type.
dynamic dParam = new ExpandoObject();
dParam.d = null;
dynamic dateResult = await db.ExecuteProcedureAsync("get_date", returnParams: dParam);
Assert.AreEqual(typeof(DateTime), dateResult.d.GetType());
}
public async Task ProvideValueToInputOutputParam()
{
var db = new SPTestsDatabase();
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("test_vars", ioParams: new { w = 2 }, outParams: new { v = 0, x = 0 });
Assert.AreEqual(3, testResult.v);
Assert.AreEqual(4, testResult.w);
Assert.AreEqual(5, testResult.x);
}
public async Task InitialNullIntegerOutputParam()
{
var db = new SPTestsDatabase();
// NB This is PostgreSql specific; Npgsql completely ignores the output parameter type and sets it (sensibly) from the return type.
dynamic z = new ExpandoObject();
z.z = null;
dynamic procResult = await db.ExecuteProcedureAsync("find_min", inParams: new { x = 5, y = 3 }, outParams: z);
Assert.AreEqual(typeof(int), procResult.z.GetType());
Assert.AreEqual(3, procResult.z);
}
public async Task InitialNullDateReturnParamMethod3()
{
var db = new SPTestsDatabase();
// Look - it REALLY ignores the parameter type. This would not work on other ADO.NET providers.
// (Look at per-DB method: `private static bool IgnoresOutputTypes(this DbParameter p);`)
dynamic dParam = new ExpandoObject();
dParam.d = false;
dynamic dateResult = await db.ExecuteProcedureAsync("get_date", returnParams: dParam);
Assert.AreEqual(typeof(DateTime), dateResult.d.GetType());
}
public async Task DefaultValueFromNullInputOutputParam_CrossDb()
{
// This is the cross-DB compatible way to do it.
var db = new SPTestsDatabase();
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("test_vars", ioParams: new { w = (int?)null }, outParams: new { v = 0, x = 0 });
Assert.AreEqual(1, testResult.v);
Assert.AreEqual(2, testResult.w);
Assert.AreEqual(3, testResult.x);
}
public async Task DateReturnParameter()
{
var db = new SPTestsDatabase();
dynamic d = new ExpandoObject();
d.d = true; // NB the type is ignored (by the underlying driver)
var dResult = await db.ExecuteProcedureAsync("ufnGetAccountingEndDate", returnParams : d);
Assert.AreEqual(typeof(DateTime), dResult.d.GetType());
}
public async Task NonQueryWithTwoOutputCursors()
{
var db = new SPTestsDatabase(ProviderName);
var twoSetDirect = await db.ExecuteProcedureAsync("tworesults", outParams : new { prc1 = new Cursor(), prc2 = new Cursor() });
Assert.AreEqual(typeof(Cursor), twoSetDirect.prc1.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)twoSetDirect.prc1).CursorRef.GetType().Name);
Assert.AreEqual(typeof(Cursor), twoSetDirect.prc2.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)twoSetDirect.prc2).CursorRef.GetType().Name);
}
public async Task Function_Call_SByte()
{
var db = new SPTestsDatabase(ProviderName);
var result = await db.ExecuteProcedureAsync("inventory_in_stock",
inParams : new { p_inventory_id = 5 },
returnParams : new { retval = (sbyte)1 });
Assert.AreEqual(typeof(sbyte), result.retval.GetType());
Assert.AreEqual(1, result.retval);
}
public async Task DefaultValueFromNullInputOutputParam()
{
var db = new SPTestsDatabase();
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("TestVars", ioParams : new { w = (int?)null }, outParams : new { v = 0, x = 0 });
Assert.AreEqual(1, testResult.v);
Assert.AreEqual(2, testResult.w);
Assert.AreEqual(3, testResult.x);
}
public async Task NonQueryFromMixedCursorOutput()
{
var db = new SPTestsDatabase(ProviderName);
var mixedDirect = await db.ExecuteProcedureAsync("mixedresults", outParams : new { prc1 = new Cursor(), prc2 = new Cursor(), num1 = 0, num2 = 0 });
Assert.AreEqual(typeof(Cursor), mixedDirect.prc1.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)mixedDirect.prc1).CursorRef.GetType().Name);
Assert.AreEqual(typeof(Cursor), mixedDirect.prc2.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)mixedDirect.prc2).CursorRef.GetType().Name);
Assert.AreEqual(1, mixedDirect.num1);
Assert.AreEqual(2, mixedDirect.num2);
}
public async Task DefaultValueFromNullInputOutputParam_Npgsql()
{
var db = new SPTestsDatabase();
// the two lines create a null w param with a no type; on most DB providers this only works
// for input params, where a null is a null is a null, but not on output params, where
// we need to know what type the output var should be; but some providers plain ignore
// the output type - in which case we do not insist that the user provide one
dynamic wArgs = new ExpandoObject();
wArgs.w = null;
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("test_vars", ioParams: wArgs, outParams: new { v = 0, x = 0 });
Assert.AreEqual(1, testResult.v);
Assert.AreEqual(2, testResult.w);
Assert.AreEqual(3, testResult.x);
}
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();
}
}
}