dotConnect for PostgreSQL Documentation
Devart.Data.PostgreSql Namespace / PgSqlArray Structure
Members Example

In This Topic
PgSqlArray Structure
In This Topic
Represents a PostgreSQL ARRAY value.
Syntax
'Declaration
 
Public Structure PgSqlArray 
   Inherits System.ValueType
   Implements System.Collections.IEnumerable, System.Data.SqlTypes.INullable, System.Xml.Serialization.IXmlSerializable 
 
Remarks

The PgSqlArray class provides methods to access elements of the multidimensional array. Items values are accessible with GetValue and SetValue methods.

Example
The following example shows how to handle PgSqlArray objects. 
/* 
CREATE TABLE test.array_test
(
  f_array text[][]
)
WITH (OIDS=FALSE);
ALTER TABLE test.array_test OWNER TO postgres;

INSERT INTO test.array_test VALUES ('{{"elem11", "elem12", "elem13"},{"elem21","elem22", "elem23"}}')
*/

public void PgSqlArrayManipulation() {

        object[] elements = new object[6] { 2, 43, 4, 5, 6, 10 };
        int rank = 2;
        int dimension1 = 3;
        int dimension2 = 2;

        // One way to initialize PgSqlArray
        PgSqlArray array1 = new PgSqlArray(elements, PgSqlType.Int, rank, dimension1, dimension2);
        Console.WriteLine(array1.ToString()); // {{2,43},{4,5},{6,2}}

        // Another way to initialize PgSqlArray
        PgSqlArray array2 = new PgSqlArray(PgSqlType.Int, rank, dimension1, dimension2);
        int inc = 0;
        // Set elements of the array
        for (int i = 1; i < array2.GetLength(1) + 1; i++)
        for (int j = 1; j < array2.GetLength(2) + 1; j++) {
        array2.SetValue(elements[inc++], i, j);
}
        Console.WriteLine(array2); // {{2,43},{4,5},{6,2}}

        // Get all elements
        foreach (PgSqlArray subArr in array1)
                foreach (int elem in subArr)
                        Console.Write(elem + ",");

        Console.WriteLine();
        // Get one element
        int number = (int)array1[3, 2];
        Console.WriteLine(number); // Outputs 10
        }

public void GetPgSqlArray(PgSqlConnection connection) {

        PgSqlCommand cmd = new PgSqlCommand();
        cmd.Connection = connection;
        cmd.CommandText = "SELECT f_array FROM test.array_test";

        using (PgSqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                        PgSqlArray array = reader.GetPgSqlArray(0);
                        Console.WriteLine(array);
                }
        }
}

public void UsingPgSqlArrayAsParameter(PgSqlConnection connection) {

        PgSqlCommand cmd = new PgSqlCommand();
        cmd.Connection = connection;
        cmd.CommandText = "INSERT INTO test.array_test(f_array) VALUES(:p)";

        // creates array {{elem11,elem12,elem13},{elem21,elem22,elem23}}
        // rank = 2, dimensions = {2,3}
        object[] elements = new object[] { "elem11","elem12","elem13","elem21","elem22","elem23" };
        PgSqlArray array = new PgSqlArray(elements, PgSqlType.Text, 2, 2, 3);

        PgSqlParameter param = new PgSqlParameter("p", PgSqlType.Array);
        param.Value = array;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
}
' CREATE TABLE test.array_test
' (
'   f_array text[][]
' )
' WITH (OIDS=FALSE);
' ALTER TABLE test.array_test OWNER TO postgres;'
'
' INSERT INTO test.array_test VALUES ('{{"elem11", "elem12", "elem13"},{"elem21","elem22", "elem23"}}')

Public Sub PgSqlArrayManipulation()
        Dim elements As Object() = New Object() {2, 43, 4, 5, 6, 10}
        Dim rank As Integer = 2
        Dim dimension1 As Integer = 3
        Dim dimension2 As Integer = 2
        ' One way to initialize PgSqlArray
        Dim array1 As New PgSqlArray(elements, PgSqlType.Int, rank, dimension1, dimension2)
        Console.WriteLine(array1.ToString)
        ' Another way to initialize PgSqlArray
        Dim array2 As New PgSqlArray(PgSqlType.Int, rank, New Integer() {dimension1, dimension2})
        Dim inc As Integer = 0
        ' Set elements of the array
        For i As Integer = 1 To (array2.GetLength(1) + 1) - 1
                For j As Integer = 1 To (array2.GetLength(2) + 1) - 1
                        array2.SetValue(elements(inc), i, j)
                        inc = inc + 1
                Next j
        Next i
        Console.WriteLine(array2)
        ' Get all elements
        Dim subArr As PgSqlArray
        For Each subArr In array1
                Dim elem As Integer
                For Each elem In subArr
                        Console.Write((elem & ","))
        Next
        Next
        Console.WriteLine()
        ' Get one element
        Dim number As Integer = CInt(array1.Item(New Integer() {3, 2}))
        Console.WriteLine(number)
End Sub

Public Sub GetPgSqlArray(ByVal connection As PgSqlConnection)
        Dim cmd As New PgSqlCommand
        cmd.Connection = connection
        cmd.CommandText = "SELECT f_array FROM test.array_test"

        Using reader As PgSqlDataReader = cmd.ExecuteReader
        Do While reader.Read
                Console.WriteLine(reader.GetPgSqlArray(0))
        Loop
End Using
End Sub

Public Sub UsingPgSqlArrayAsParameter(ByVal connection As PgSqlConnection)
        Dim cmd As New PgSqlCommand
        cmd.Connection = connection
        cmd.CommandText = "INSERT INTO test.array_test(f_array) VALUES(:p)"

        ' creates array {{elem11,elem12,elem13},{elem21,elem22,elem23}}
        ' rank = 2, dimensions = {2,3}
        Dim elements As Object() = New Object() {"elem11", "elem12", "elem13", "elem21", "elem22", "elem23"}
        Dim array As New PgSqlArray(elements, PgSqlType.Text, 2, 2, 3)

        Dim param As New PgSqlParameter("p", PgSqlType.Array)
        param.Value = array
        cmd.Parameters.Add(param)

        cmd.ExecuteNonQuery()
End Sub
Inheritance Hierarchy

System.Object
   System.ValueType
      Devart.Data.PostgreSql.PgSqlArray

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

See Also

Reference

PgSqlArray Members
Devart.Data.PostgreSql Namespace
GetValue Method
SetValue Method

 

 

© 2003 - 2025 Devart. All Rights Reserved.

Send Feedback