コード例 #1
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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);
        }
コード例 #2
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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
        }
コード例 #3
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        public async Task ReadOutputParamsUsingQuery()
        {
            var db = new SPTestsDatabase();
            // Again this is Postgres specific: output params are really part of data row and can be read that way
            var record = await db.SingleFromProcedureAsync("test_vars", new { w = 2 });

            Assert.AreEqual(3, record.v);
            Assert.AreEqual(4, record.w);
            Assert.AreEqual(5, record.x);
        }
コード例 #4
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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);
        }
コード例 #5
0
ファイル: SPTests.cs プロジェクト: brgrz/Mighty
        public void InitialNullInputOutputParam()
        {
            var     db     = new SPTestsDatabase();
            dynamic xParam = new ExpandoObject();

            xParam.x = null;
            dynamic squareResult = db.ExecuteProcedure("square_num", ioParams: xParam);

            Assert.AreEqual(null, squareResult.x);
        }
コード例 #6
0
ファイル: SPTests.cs プロジェクト: brgrz/Mighty
        public void QueryMultipleWithBreaks()
        {
            var db = new SPTestsDatabase();
            var resultCTestFull = db.QueryMultipleFromProcedure("cbreaktest", outParams: new { c1 = new Cursor(), c2 = new Cursor() });

            CheckMultiResultSetStructure(resultCTestFull, 10, 11);
            var resultCTestToBreak = db.QueryMultipleFromProcedure("cbreaktest", ioParams: new { c1 = new Cursor(), c2 = new Cursor() });

            CheckMultiResultSetStructure(resultCTestToBreak, breakTest: true);
        }
コード例 #7
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        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);
        }
コード例 #8
0
 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);
 }
コード例 #9
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        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());
        }
コード例 #10
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        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);
        }
コード例 #11
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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);
        }
コード例 #12
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        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());
        }
コード例 #13
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        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);
        }
コード例 #14
0
 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);
 }
コード例 #15
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void QueryFromStoredProcedure()
        {
            var db     = new SPTestsDatabase();
            var people = db.QueryFromProcedure("uspGetEmployeeManagers", new { BusinessEntityID = 35 });
            int count  = 0;

            foreach (var person in people)
            {
                Console.WriteLine(person.FirstName + " " + person.LastName);
                count++;
            }
            Assert.AreEqual(3, count);
        }
コード例 #16
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void QuerySetOfRecordsFromFunction()
        {
            var db           = new SPTestsDatabase();
            var setOfRecords = db.QueryFromProcedure("sum_n_product_with_tab", new { x = 10 });
            int count        = 0;

            foreach (var innerRecord in setOfRecords)
            {
                Console.WriteLine(innerRecord.sum + "\t|\t" + innerRecord.product);
                count++;
            }
            Assert.AreEqual(4, count);
        }
コード例 #17
0
        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);
        }
コード例 #18
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void DereferenceCursorValuedFunction()
        {
            var db = new SPTestsDatabase(ProviderName);
            // Oracle function one cursor return value
            var employees = db.QueryFromProcedure("get_dept_emps", inParams: new { p_DeptNo = 10 }, returnParams: new { v_rc = new Cursor() });
            int count     = 0;

            foreach (var employee in employees)
            {
                Console.WriteLine(employee.EMPNO + " " + employee.ENAME);
                count++;
            }
            Assert.AreEqual(3, count);
        }
コード例 #19
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void DereferenceCursorOutputParameter()
        {
            var db = new SPTestsDatabase(ProviderName);
            // Oracle procedure one cursor output variables
            var moreEmployees = db.QueryFromProcedure("myproc", outParams: new { prc = new Cursor() });
            int count         = 0;

            foreach (var employee in moreEmployees)
            {
                Console.WriteLine(employee.EMPNO + " " + employee.ENAME);
                count++;
            }
            Assert.AreEqual(14, count);
        }
コード例 #20
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void DereferenceFromQuery_AutoWrapping()
        {
            var db = new SPTestsDatabase();
            // use dummy cursor to trigger wrapping transaction support in Massive
            var employees = db.QueryWithParams("SELECT * FROM cursor_employees()", outParams: new { abc = new Cursor() });
            int count     = 0;

            foreach (var employee in employees)
            {
                Console.WriteLine(employee.firstname + " " + employee.lastname);
                count++;
            }
            Assert.AreEqual(9, count);
        }
コード例 #21
0
 public async Task DereferenceFromQuery_AutoWrapping()
 {
     var db = new SPTestsDatabase();
     // use dummy cursor to trigger wrapping transaction support in Massive
     // TO DO: document this (the idea is that this requires a wrapping transaction, but Mighty will do this for us if we do this;
     // still to remind exactly why and document)
     var employees = await db.QueryWithParamsAsync("SELECT * FROM cursor_employees()", outParams: new { abc = new Cursor() });
     int count = 0;
     await employees.ForEachAsync(employee => {
         Console.WriteLine(employee.firstname + " " + employee.lastname);
         count++;
     });
     Assert.AreEqual(9, count);
 }
コード例 #22
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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);
        }
コード例 #23
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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);
        }
コード例 #24
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        public async Task InputCursors_1XN()
        {
            var db = new SPTestsDatabase();

            db.NpgsqlAutoDereferenceCursors = false; // for this instance only

            // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle)
            using (var conn = await db.OpenConnectionAsync())
            {
                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();
                }
            }
        }
コード例 #25
0
 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);
 }
コード例 #26
0
 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);
 }
コード例 #27
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void In_Out_Params_SQL()
        {
            var _providerName = ProviderName;

            if (ProviderName == "MySql.Data.MySqlClient")
            {
                // this must be added to access user variables on the Oracle/MySQL driver
                _providerName += ";AllowUserVariables=true";
            }
            var db = new SPTestsDatabase(_providerName);
            // old skool SQL
            // this approach only works on the Oracle/MySQL driver if "AllowUserVariables=true" is included in the connection string
            var result = db.Scalar("CALL testproc_in_out(10, @param2); SELECT @param2");

            Assert.AreEqual((long)20, result);
        }
コード例 #28
0
ファイル: AsyncSPTests.cs プロジェクト: himel951/Mighty
        public void HugeCursorTest()
        {
            var db = new SPTestsDatabase();

            //// Huge cursor tests....
            var config = db.SingleFromQuery("SELECT current_setting('work_mem') work_mem, current_setting('log_temp_files') log_temp_files");

            //// huge data from SELECT *
            //var resultLargeSelectTest = await db.QueryWithParamsAsync("SELECT * FROM large");
            //foreach(var item in resultLargeSelectTest)
            //{
            //    int a = 1;
            //}

            //// huge data from (implicit) FETCH ALL
            //// AUTO-DEREFERENCE TWO HUGE, ONLY FETCH FROM ONE
            //var resultLargeProcTest = await db.QueryFromProcedureAsync("lump2", returnParams: new { abc = new Cursor() });
            //foreach (var item in resultLargeProcTest)
            //{
            //    Console.WriteLine(item.id);
            //    break;
            //}

            var results = await db.QueryMultipleFromProcedureAsync("lump2", returnParams : new { abc = new Cursor() });

            db.NpgsqlAutoDereferenceFetchSize = 4000000;
            await CheckMultiResultSetStructureAsync(results, 10000000, 10000000, true, true);

            // one item from cursor
            //using (var conn = db.OpenConnection())
            //{
            //    using (var trans = conn.BeginTransaction())
            //    {
            //        var result = db.ExecuteAsProcedure("lump2", returnParams: new { abc = new Cursor(), def = new Cursor() }, connection: conn);
            //        var singleItemTest = await db.QueryWithParamsAsync($@"FETCH 5000000 FROM ""{result.abc}"";", connection: conn);
            //        foreach (var item in singleItemTest)
            //        {
            //            Console.WriteLine(item.id);
            //            break;
            //        }
            //         NB plain Execute() did NOT take a connection, and changing this MIGHT be an API breaking change??? TEST...!
            //         (This is the first, and so far only, really unwanted side effect of trying to stay 100% non-breaking.)
            //        db.Execute($@"CLOSE ""{result.abc}"";", conn);
            //        trans.Commit();
            //    }
            //}
        }
コード例 #29
0
ファイル: AsyncSPTests.cs プロジェクト: brgrz/Mighty
        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();
                }
            }
        }
コード例 #30
0
ファイル: SPTests.cs プロジェクト: uolton/Mighty
        public void Function_Call_Byte()
        {
            var db     = new SPTestsDatabase(ProviderName);
            var result = db.ExecuteProcedure("inventory_in_stock",
                                             inParams: new { p_inventory_id = 5 },
                                             returnParams: new { retval = (byte)1 });

            if (ProviderName == "Devart.Data.MySql")
            {
                Assert.AreEqual(typeof(short), result.retval.GetType());
            }
            else
            {
                Assert.AreEqual(typeof(byte), result.retval.GetType());
            }
            Assert.AreEqual(1, result.retval);
        }