Using nested tables v8.0.5.1
EDB Postgres Advanced Server supports nested table collection types created with CREATE TYPE ... AS TABLE OF
statements. The EDB .NET Connector supports output parameters declared as nested tables out of the box, whether free-standing types or declared inside packages.
Nested table types mapping
Nested table types are mapped to List<object>
s in C#, as it is preferred over ArrayList
. These lists contain as many elements as the nested table type's rows. The nested table items are translated to be compatible with the C# application using the following rules:
The connector resolves all nested table rows into a
List<object>
in C# while maintaining and converting each column's underlying type. For example, a[text1, text2, num1]
row will be resolved as a[string, string, decimal]
item in the list.If the nested type
IS TABLE OF
a domain type (int, varchar, decimal, etc.), all the rows will be their C# counterpart according to the Supported Types and their Mappings.If the nested type
IS TABLE OF
a record or composite type not mapped to a C# class, all rows become a nested List containing as many elements as the record or composite fields, with proper type translation.If the nested type
IS TABLE OF
a record or composite type mapped to a C# class (for example,MyComposite
), all rows will beMyComposite
instances.
Example: Retrieving nested table output parameter
This program:
Creates a package with a nested
emp_tbl_typ
table type ofemp_rec_typ
. This package has a stored procedure that fills the nested table output parameter.Maps the nested table type to a C# class via
MapComposite<>
.Executes and displays the results.
Note
Always provide type names in lowercase.
Program example
Create an empty console program and paste the following code.
internal static class Program { const string ConnectionString = "your_connection_string"; // Composite type, will be mapped to the nested table type // This will work if field types are convertible from database types public class Employee { [PgName("empno")] public decimal Number; [PgName("ename")] public string? Name; } public static void Sample_NestedTableTypes(string ConnectionString) { var dataSourceBuilder = new EDBDataSourceBuilder(ConnectionString); dataSourceBuilder.MapComposite<Employee>("pkgextendtest.emp_rec_typ"); using (var dataSource = dataSourceBuilder.Build()) { using (var connection = dataSource.OpenConnection()) { try { CreatePackage(connection); var commandText = "pkgExtendTest.nestedTableExtendTest"; var cstmt = new EDBCommand(commandText, connection); cstmt.CommandType = CommandType.StoredProcedure; var tableOfParam = cstmt.Parameters.Add(new EDBParameter() { Direction = ParameterDirection.Output, DataTypeName = "pkgextendtest.emp_tbl_typ" }); cstmt.Prepare(); cstmt.ExecuteNonQuery(); List<object>? employees = tableOfParam.Value as List<object>; if (employees == null) { Console.WriteLine($"No employee found"); return; } foreach (var employeeRecord in employees) { var employee = employeeRecord as Employee; if (employee != null) { Console.WriteLine($"Employee {employee.Number}: {employee.Name}"); } } } catch (Exception ex) { Console.WriteLine($"Error: {ex.Message}"); } finally { Cleanup(connection); } } } } // helper methods to create package and cleaning up static void CreatePackage(EDBConnection connection) { var createPackage = " CREATE OR REPLACE PACKAGE pkgExtendTest IS \n" + " TYPE emp_rec_typ IS RECORD ( \n" + " empno NUMBER(4), \n" + " ename VARCHAR2(10) \n" + " ); \n" + " TYPE emp_tbl_typ IS TABLE OF emp_rec_typ; \n" + " PROCEDURE nestedTableExtendTest(emp_tbl OUT emp_tbl_typ); \n" + " END pkgExtendTest; \n"; using (var com = new EDBCommand(createPackage, connection) { CommandType = CommandType.Text }) { com.ExecuteNonQuery(); } var createPackageBody = " CREATE OR REPLACE PACKAGE BODY pkgExtendTest IS \n" + " PROCEDURE nestedTableExtendTest(emp_tbl OUT emp_tbl_typ) IS \n" + " DECLARE \n" + " CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10 order by empno; \n" + " i INTEGER := 0; \n" + " BEGIN \n" + " emp_tbl := emp_tbl_typ(); \n" + " FOR r_emp IN emp_cur LOOP \n" + " i := i + 1; \n" + " emp_tbl.EXTEND; \n" + " emp_tbl(i) := r_emp; \n" + " END LOOP; \n" + " END nestedTableExtendTest; \n" + " END pkgExtendTest; \n"; using (var com = new EDBCommand(createPackageBody, connection) { CommandType = CommandType.Text }) { com.ExecuteNonQuery(); } connection.ReloadTypes(); } static void Cleanup(EDBConnection connection) { var dropPackageBody = "DROP PACKAGE BODY pkgExtendTest"; var dropPackage = "DROP PACKAGE pkgExtendTest"; using (var com = new EDBCommand(dropPackageBody, connection) { CommandType = CommandType.Text }) { com.ExecuteNonQuery(); } using (var com = new EDBCommand(dropPackage, connection) { CommandType = CommandType.Text }) { com.ExecuteNonQuery(); } } }
The output should look like this:
Employee 7499: ALLEN Employee 7521: WARD Employee 7566: JONES Employee 7654: MARTIN Employee 7698: BLAKE Employee 7782: CLARK Employee 7788: SCOTT Employee 7839: KING Employee 7844: TURNER Employee 7876: ADAMS