public void InputCursors_BeginTransaction() { var db = new SPTestsDatabase(); using (var conn = db.OpenConnection()) { // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle) using (var trans = conn.BeginTransaction()) { var cursors = db.ExecuteProcedure("cursorNByOne", outParams: new { c1 = new Cursor(), c2 = new Cursor() }, connection: conn); var cursor1 = db.QueryFromProcedure("fetch_next_ints_from_cursor", new { mycursor = cursors.c1 }, connection: conn); int count1 = 0; foreach (var item in cursor1) { Assert.AreEqual(11, item.myint1); Assert.AreEqual(22, item.myint2); count1++; } Assert.AreEqual(1, count1); var cursor2 = db.QueryFromProcedure("fetch_next_ints_from_cursor", new { mycursor = cursors.c2 }, connection: conn); int count2 = 0; foreach (var item in cursor2) { Assert.AreEqual(33, item.myint1); Assert.AreEqual(44, item.myint2); count2++; } Assert.AreEqual(1, count2); trans.Commit(); } } }
public void Devart_ParameterCheck(bool explicitConnection) { var db = new SPTestsDatabase(ProviderName, explicitConnection); if (explicitConnection) { MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnection(db); } dynamic result; using (var connection = db.OpenConnection( explicitConnection ? 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; db.Execute(command, connection: connection); result = db.ResultsAsExpando(command); } } Assert.AreEqual(20, result.param2); }
public void PassingCursorInputParameter(bool explicitConnection) { var db = new SPTestsDatabase(ProviderName, explicitConnection); if (explicitConnection) { MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnection(db); } // To share cursors between commands in Oracle the commands must use the same connection using (var conn = db.OpenConnection( explicitConnection ? MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, ProviderName) : null )) { var res1 = db.ExecuteWithParams("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); db.Execute("delete from processing_result", connection: conn); // oracle demo code takes the input cursor and writes the results to `processing_result` table var res2 = db.ExecuteProcedure("cursor_in_out.process_cursor", inParams: new { p_cursor = res1.p_rc }, connection: conn); Assert.AreEqual(0, ((IDictionary <string, object>)res2).Count); var processedRows = db.Query("select * from processing_result", connection: conn).ToList(); Assert.AreEqual(3, processedRows.Count); } }
public void Devart_ParameterCheck() { var db = new SPTestsDatabase(ProviderName); dynamic result; using (var connection = db.OpenConnection()) { 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; db.Execute(command); result = db.ResultsAsExpando(command); } } Assert.AreEqual(20, result.param2); }
public void InputCursors_BeginTransaction(bool explicitConnection) { var db = new SPTestsDatabase(explicitConnection); if (explicitConnection) { MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnection(db); } using (var conn = db.OpenConnection( explicitConnection ? MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, TestConstants.ProviderName) : null )) { // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle) using (var trans = conn.BeginTransaction()) { var cursors = db.ExecuteProcedure("cursorNByOne", outParams: new { c1 = new Cursor(), c2 = new Cursor() }, connection: conn); var cursor1 = db.QueryFromProcedure("fetch_next_ints_from_cursor", new { mycursor = cursors.c1 }, connection: conn); int count1 = 0; foreach (var item in cursor1) { Assert.AreEqual(11, item.myint1); Assert.AreEqual(22, item.myint2); count1++; } Assert.AreEqual(1, count1); var cursor2 = db.QueryFromProcedure("fetch_next_ints_from_cursor", new { mycursor = cursors.c2 }, connection: conn); int count2 = 0; foreach (var item in cursor2) { Assert.AreEqual(33, item.myint1); Assert.AreEqual(44, item.myint2); count2++; } Assert.AreEqual(1, count2); trans.Commit(); } } }
public void LargeCursor_ExplicitFetch() { int FetchSize = 20000; int count = 0; int batchCount = 0; var db = new SPTestsDatabase(); using (var conn = db.OpenConnection()) { // cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle) using (var trans = conn.BeginTransaction()) { var result = db.ExecuteProcedure("lump", returnParams: new { cursor = new Cursor() }, connection: conn); while (true) { var fetchTest = db.Query($@"FETCH {FetchSize} FROM ""{result.cursor.CursorRef}""", connection: conn); int subcount = 0; foreach (var item in fetchTest) { 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++; } db.Execute($@"CLOSE ""{result.cursor.CursorRef}""", connection: conn); trans.Commit(); } } Assert.AreEqual((LargeCursorSize + FetchSize - 1) / FetchSize, batchCount); Assert.AreEqual(LargeCursorSize, count); }