PgSqlArray Structure

In This Topic

Represents a PostgreSQL ARRAY value.

Syntax

Visual Basic
C#

'Declaration
 
Public Structure PgSqlArray 
   Inherits System.ValueType
   Implements System.Collections.IEnumerable, System.Data.SqlTypes.INullable, System.Xml.Serialization.IXmlSerializable

public struct PgSqlArray : System.ValueType, 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.

csharp

/* 
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();
}

Visual Basic

' 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

Reference

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

Send Feedback