| Detailed description |
Composite type support
Example:
CREATE TYPE ct AS
(
i smallint,
t character varying,
b bytea,
p inet,
d time with time zone
);
CREATE TABLE tt
(
id integer NOT NULL,
"ctype" ct,
arrctype ct[],
CONSTRAINT pki PRIMARY KEY (id)
)
WITH ( OIDS=FALSE);
INSERT INTO tt (id, ctype, arrctype, ba, iarr, arr)
VALUES
(1, '(11,text,bbb,127.0.0.1,00:19:18.748265+03)', '{"(11,text,bbb,,00:19:18.748265+03)","(11,text,bbb,127.0.0.1,00:19:18.748265+03)"}'),
(2, '(11,text,bbb,127.0.0.1,00:19:18.748265+03)', '{"(11,text,bbb,,00:19:18.748265+03)","(11,text,bbb,127.0.0.1,00:19:18.748265+03)"}');
using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using NpgsqlTypes;
using System.Net;
using Npgsql;
namespace TestProject1
{
[TestClass]
public class CompositeTest
{
/// <summary>
/// Composite test.
/// Metood first writes the data into a database, then reads and compares them.
/// Shows compatibility with arrays.
/// </summary>
[TestMethod()]
public void CompositeTest()
{
// Init composite description
CompositeDescription description = new CompositeDescription("ct",
new ColumnDescription("i", NpgsqlDbType.Smallint),
new ColumnDescription("t", NpgsqlDbType.Varchar),
new ColumnDescription("b", NpgsqlDbType.Bytea),
new ColumnDescription("p", NpgsqlDbType.Inet),
new ColumnDescription("d", NpgsqlDbType.TimeTZ));
// First way to get composite
Composite composite = new Composite(description);
// set values
composite[0] = 11;
composite[1] = "k02\\2jj\",'jj";
composite["b"] = new byte[] { 0, 11, 22, 33, 44, 55, 66, 77 }; // it the same that composite[2] =...
composite[3] = IPAddress.Parse("88.45.88.44");
composite[4] = DateTime.Now;
// Gets array
Composite[] cmpArr = new Composite[2];
cmpArr[0] = composite;
// Second way to get composite
cmpArr[1] = new Composite(description, 11, "some text", new byte[1], null, DateTime.Now);
NpgsqlCommand cmd = new NpgsqlCommand("UPDATE tt SET ctype = @cty, arrctype = @ctyarr WHERE id = 2");
cmd.Parameters.AddWithValue("@cty", composite);
cmd.Parameters.AddWithValue("@ctyarr", cmpArr);
cmd.Connection = new NpgsqlConnection();
cmd.Connection.Open();
int i = cmd.ExecuteNonQuery();
if (i < 1) Assert.Fail("don't update");
cmd.CommandText = "SELECT ctype, arrctype FROM tt WHERE id = 2";
cmd.CommandType = System.Data.CommandType.Text;
using (NpgsqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
//gets composite
Composite c = rdr.GetComposite(0);
//gets composite[]
object obj = rdr.GetValue(1);
//gets first element
Composite cmp1 = (Composite)((object[])obj)[0];
// send and recived data must be equaled
Assert.IsTrue(
(string)composite["t"] == (string)c["t"] &&
(string)composite["t"] == (string)cmp1["t"]);
}
}
}
}
} |
|
