CUBRID Collections

Collections are a specific CUBRID data type(s). If you are not familiar with them, you can read more in the Collection Data Types manual page.

Because collections are not common to any database, but only to CUBRID, the support for them is implemented in some specific CUBRID Collection methods:

public void AddElementToSet(CUBRIDOid oid, String attributeName, Object value)
public void DropElementInSet(CUBRIDOid oid, String attributeName, Object value)
public void UpdateElementInSequence(CUBRIDOid oid, String attributeName, int index, Object value)
public void InsertElementInSequence(CUBRIDOid oid, String attributeName, int index, Object value)
public void DropElementInSequence(CUBRIDOid oid, String attributeName, int index)
public int GetCollectionSize(CUBRIDOid oid, String attributeName)

Here below are two examples of using these CUBRID extensions.

Reading values from a Collection data type:

using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
{
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            object[] o = (object[])reader[0];
            for (int i = 0; i < SeqSize; i++)
            {
                //...
            }
        }
    }
}

Updating a Collection data type:

conn.InsertElementInSequence(oid, attributeName, 5, value);
SeqSize = conn.GetCollectionSize(oid, attributeName);
using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
{
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int[] expected = { 7, 1, 2, 3, 7, 4, 5, 6 };
            object[] o = (object[])reader[0];
            for (int i = 0; i < SeqSize; i++)
            {
                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
            }
        }
    }
}
conn.DropElementInSequence(oid, attributeName, 5);
SeqSize = conn.GetCollectionSize(oid, attributeName);

Here is a complete example:

using System;
using CUBRID.Data.CUBRIDClient;
using System.Data.Common;
using System.Diagnostics;

namespace SequenceExample
{
    class Program
    {
        private static void ExecuteSQL(string sql, CUBRIDConnection conn)
        {
            using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000");
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();

                ExecuteSQL("DROP TABLE IF EXISTS t", conn);

                //Create a new table with a sequence
                ExecuteSQL("CREATE TABLE t(seq SEQUENCE(int))", conn);
                //Insert some data in the sequence column
                ExecuteSQL("INSERT INTO t(seq) VALUES({0,1,2,3,4,5,6})", conn);

                CUBRIDOid oid = new CUBRIDOid("@0|0|0");
                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT t FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            oid = (CUBRIDOid)reader[0];
                        }
                    }
                }

                String attributeName = "seq";
                int value = 7;

                int SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 7);

                conn.UpdateElementInSequence(oid, attributeName, 1, value);
                SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 7);

                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int[] expected = { 7, 1, 2, 3, 4, 5, 6 };
                            object[] o = (object[])reader[0];
                            for (int i = 0; i < SeqSize; i++)
                            {
                                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
                            }
                        }
                    }
                }

                conn.InsertElementInSequence(oid, attributeName, 5, value);
                SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 8);

                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int[] expected = { 7, 1, 2, 3, 7, 4, 5, 6 };
                            object[] o = (object[])reader[0];
                            for (int i = 0; i < SeqSize; i++)
                            {
                                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
                            }
                        }
                    }
                }

                conn.DropElementInSequence(oid, attributeName, 5);
                SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 7);

                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int[] expected = { 7, 1, 2, 3, 4, 5, 6 };
                            object[] o = (object[])reader[0];
                            for (int i = 0; i < SeqSize; i++)
                            {
                                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
                            }
                        }
                    }
                }

                ExecuteSQL("DROP t", conn);
                conn.Close();
            }
        }
    }
}