public void PqsqlParameterCollectionTest4()
{
PqsqlParameterCollection coll = new PqsqlParameterCollection
{
new PqsqlParameter("p1", DbType.Int32)
};
Assert.AreEqual(1, coll.Count);
Assert.AreEqual(":p1", coll[0].ParameterName);
Assert.AreEqual(DbType.Int32, coll[0].DbType);
Assert.AreEqual(ParameterDirection.Input, coll[0].Direction);
coll["p1"] = new PqsqlParameter()
{
ParameterName = "p2", DbType = DbType.Int32, Direction = ParameterDirection.InputOutput
};
Assert.AreEqual(1, coll.Count);
Assert.AreEqual(":p2", coll[0].ParameterName);
Assert.AreEqual(DbType.Int32, coll[0].DbType);
Assert.AreEqual(ParameterDirection.InputOutput, coll[0].Direction);
coll["p2"] = new PqsqlParameter()
{
ParameterName = "p3", DbType = DbType.String
};
Assert.AreEqual(1, coll.Count);
Assert.AreEqual(":p3", coll[0].ParameterName);
Assert.AreEqual(DbType.String, coll[0].DbType);
Assert.AreEqual(ParameterDirection.Input, coll[0].Direction);
}
public void PqsqlParameterCollectionTest3()
{
PqsqlParameterCollection coll = new PqsqlParameterCollection();
Array parameters = new PqsqlParameter[]
{
new PqsqlParameter("p1", DbType.Int32),
new PqsqlParameter("p2", DbType.Boolean)
{
Direction = ParameterDirection.InputOutput
},
new PqsqlParameter("p3", DbType.Double)
{
Direction = ParameterDirection.Output
},
};
coll.AddRange(parameters);
Assert.AreEqual(3, coll.Count);
Array copy = new PqsqlParameter[3];
coll.CopyTo(copy, 0);
Assert.AreEqual(parameters.Length, copy.Length);
coll.Remove(copy.GetValue(1));
Assert.AreEqual(2, coll.Count);
bool keynotfound = false;
try
{
coll.RemoveAt("p2");
}
catch (KeyNotFoundException)
{
keynotfound = true;
}
finally
{
Assert.IsTrue(keynotfound);
}
coll.Insert(coll.IndexOf("p3"), new PqsqlParameter("p4", DbType.Int64));
Assert.AreEqual(3, coll.Count);
coll.RemoveAt("p3");
Assert.AreEqual(2, coll.Count);
coll.Clear();
Assert.AreEqual(0, coll.Count);
}
public void PqsqlParameterCollectionTest5()
{
PqsqlParameterCollection coll = new PqsqlParameterCollection();
PqsqlParameter p1 = coll.AddWithValue("p1", DBNull.Value);
PqsqlParameter p2 = coll.AddWithValue("p2", 123);
PqsqlParameter p0 = coll.AddWithValue("p0", "text");
Assert.AreEqual(3, coll.Count);
Assert.AreEqual(p1.ParameterName, coll[0].ParameterName);
Assert.AreEqual(p1.DbType, coll[0].DbType);
Assert.AreEqual(p1.Direction, coll[0].Direction);
Assert.AreEqual(p1.Value, coll[0].Value);
Assert.AreEqual(p2.ParameterName, coll[1].ParameterName);
Assert.AreEqual(p2.DbType, coll[1].DbType);
Assert.AreEqual(p2.Direction, coll[1].Direction);
Assert.AreEqual(p2.Value, coll[1].Value);
Assert.AreEqual(p0.ParameterName, coll[2].ParameterName);
Assert.AreEqual(p0.DbType, coll[2].DbType);
Assert.AreEqual(p0.Direction, coll[2].Direction);
Assert.AreEqual(p0.Value, coll[2].Value);
Assert.AreEqual(p0.ParameterName, ":p0");
Assert.AreEqual(p0.DbType, DbType.Object);
Assert.AreEqual(p0.Direction, ParameterDirection.Input);
Assert.AreEqual(p0.Value, "text");
Assert.AreEqual(p1.ParameterName, ":p1");
Assert.AreEqual(p1.DbType, DbType.Object);
Assert.AreEqual(p1.Direction, ParameterDirection.Input);
Assert.AreEqual(p1.Value, DBNull.Value);
Assert.AreEqual(p2.ParameterName, ":p2");
Assert.AreEqual(p2.DbType, DbType.Object);
Assert.AreEqual(p2.Direction, ParameterDirection.Input);
Assert.AreEqual(p2.Value, 123);
PqsqlParameter p2_new = coll.AddWithValue("p2", 74.11);
Assert.AreEqual(p2_new.ParameterName, coll[1].ParameterName);
Assert.AreEqual(p2_new.DbType, coll[1].DbType);
Assert.AreEqual(p2_new.Direction, coll[1].Direction);
Assert.AreEqual(p2_new.Value, coll[1].Value);
Assert.AreEqual(p2_new.ParameterName, ":p2");
Assert.AreEqual(p2_new.DbType, DbType.Object);
Assert.AreEqual(p2_new.Direction, ParameterDirection.Input);
Assert.AreEqual(p2_new.Value, 74.11);
}
public void PqsqlCommandTest11()
{
PqsqlCommand cmd = new PqsqlCommand("select :p1;", mConnection);
cmd.CommandType = CommandType.Text;
// recursive parameters
PqsqlParameter p1 = cmd.Parameters.AddWithValue(":p1", ":p1");
PqsqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string s = r.GetString(0);
Assert.AreEqual(p1.Value, s);
}
}
public void PqsqlCommandTest4()
{
PqsqlCommand cmd = new PqsqlCommand("generate_series", mConnection);
const int p1_val = -1;
const int p2_val = 2;
PqsqlParameter p1 = cmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = p1_val;
p1.DbType = DbType.Int32;
PqsqlParameter p2 = cmd.CreateParameter();
p2.ParameterName = "p2";
p2.Value = p2_val;
p2.DbType = DbType.Int32;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(new PqsqlParameter("generate_series", DbType.Int32)
{
Direction = ParameterDirection.Output
});
PqsqlDataReader r = cmd.ExecuteReader();
int n = p1_val - 1;
int j = p1_val;
while (r.Read())
{
int i = r.GetInt32(0);
Assert.AreEqual(j++, i);
n++;
}
Assert.AreEqual(p2_val, n);
}
public void PqsqlDataReaderTest4()
{
mCmd.CommandText = "select :arr";
PqsqlParameter arr = new PqsqlParameter
{
ParameterName = ":arr",
PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Boolean,
Value = new bool[] { true, true, false, false }
};
mCmd.Parameters.Add(arr);
using (PqsqlDataReader reader = mCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
bool read = reader.Read();
Assert.IsTrue(read);
object o = reader.GetValue(0);
// postgres returns 1-based array bool[1..4] in o
// whereas arr.Value is 0-based array bool[]
CollectionAssert.AreEqual((ICollection)arr.Value, (ICollection)o); // round trip succeeded
}
}
public void PqsqlCommandTest10()
{
StringBuilder sb = new StringBuilder();
const int N = 1664; // postgres can handle at most 1664 columns in a select
const int K = 5; // create K*(K+1) / 2 queries
PqsqlParameter[] pars = new PqsqlParameter[0];
for (int k = 1; k <= K; k++)
{
if (k > 1)
{
sb.Append(';');
}
sb.Append("select ");
Array.Resize(ref pars, k * N);
using (PqsqlTransaction t = mConnection.BeginTransaction())
{
for (int i = 1; i <= N; i++)
{
int j = (k - 1) * N + i - 1;
if (i > 1)
{
sb.Append(',');
}
sb.Append("generate_series(:p" + j + ",:p" + j + ")");
PqsqlParameter p = new PqsqlParameter
{
ParameterName = "p" + j,
DbType = DbType.Int32,
Value = j
};
pars[j] = p;
}
sb.Append(';');
using (PqsqlCommand cmd = mConnection.CreateCommand())
{
cmd.Transaction = t;
cmd.CommandText = sb.ToString();
cmd.CommandTimeout = 20;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(pars.Take(k * N).ToArray());
using (PqsqlDataReader reader = cmd.ExecuteReader())
{
for (int n = 0; n < k; n++)
{
while (reader.Read())
{
for (int m = 0; m < N; m++)
{
int o = reader.GetInt32(m);
Assert.AreEqual(n * N + m, o);
}
}
reader.NextResult();
}
}
}
}
}
}
public void PqsqlCommandTest6()
{
PqsqlTransaction t = mConnection.BeginTransaction();
PqsqlCommand cmd = mConnection.CreateCommand();
cmd.Transaction = t;
cmd.CommandText = "create or replace function pg_temp.test_out(p1 out text, i1 inout int, p2 out int, r inout refcursor) as $$begin $1 := 'p1 text'; $2:=$2*-4711; $3:=12345; open r for select * from ( values (1,2,3),(4,5,6),(7,8,9) ) X; end;$$ LANGUAGE plpgsql;";
cmd.CommandTimeout = 10;
cmd.CommandType = CommandType.Text;
int n = cmd.ExecuteNonQuery();
Assert.AreEqual(0, n);
PqsqlParameter p1 = new PqsqlParameter("p1", DbType.String)
{
Direction = ParameterDirection.Output,
Value = "p1_val"
};
const int p2_val = 4711;
PqsqlParameter p2 = new PqsqlParameter("i1", DbType.Int32, (object)p2_val)
{
Direction = ParameterDirection.InputOutput,
};
PqsqlParameter p3 = new PqsqlParameter("p2", DbType.Int32)
{
Direction = ParameterDirection.Output,
Value = 42
};
const string p4_val = "portal_name";
PqsqlParameter p4 = new PqsqlParameter
{
ParameterName = "r",
PqsqlDbType = PqsqlDbType.Refcursor,
Direction = ParameterDirection.InputOutput,
Value = p4_val
};
cmd.CommandText = "pg_temp.test_out";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
n = cmd.ExecuteNonQuery();
Assert.AreEqual(-1, n);
Assert.AreEqual("p1 text", p1.Value);
Assert.AreEqual(p2_val * -p2_val, p2.Value);
Assert.AreEqual(12345, p3.Value);
Assert.AreEqual(p4_val, p4.Value);
cmd.CommandText = string.Format("fetch all from {0}", p4.Value);
cmd.CommandType = CommandType.Text;
PqsqlDataReader r = cmd.ExecuteReader();
int i = 1;
while (r.Read())
{
Assert.AreEqual(i++, r.GetValue(0));
Assert.AreEqual(i++, r.GetValue(1));
Assert.AreEqual(i++, r.GetValue(2));
}
t.Rollback();
}
public void PqsqlDataAdapterTest1()
{
// get current_database() name
mCmd.CommandText = "current_database";
mCmd.CommandType = CommandType.StoredProcedure;
object curdb = mCmd.ExecuteScalar();
// get dbname from connection
object dbname = mConnection.Database;
// fetch pg_database tuple
mCmd.CommandText = "select datname,pg_encoding_to_char(encoding),datcollate,datctype,datallowconn from pg_database where datname=:dat";
mCmd.CommandType = CommandType.Text;
PqsqlParameter datpar = mCmd.CreateParameter();
datpar.ParameterName = "dat";
datpar.DbType = DbType.String;
datpar.Value = curdb;
mCmd.Parameters.Add(datpar);
DataSet ds = new DataSet();
using (PqsqlDataAdapter adapter = new PqsqlDataAdapter(mCmd))
{
adapter.Fill(ds);
}
Assert.AreEqual(1, ds.Tables.Count, "wrong table count");
int tables = 0;
int rows = 0;
int columns = 0;
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
foreach (object item in row.ItemArray)
{
// read item
switch (columns)
{
case 0: // datname
Assert.AreEqual(dbname, item, "wrong database name");
break;
case 1: // encoding
Assert.AreEqual("UTF8", item, "wrong encoding id");
break;
case 2: // datcollate
case 3: // datctype
Assert.IsNotNull(item);
break;
case 4: // datallowconn
Assert.AreEqual(true, item, "we must be allowed to connect");
break;
}
columns++;
}
rows++;
}
tables++;
}
Assert.AreEqual(1, tables, "wrong table count");
Assert.AreEqual(1, rows, "wrong row count");
Assert.AreEqual(5, columns, "wrong column count");
}
public void PqsqlDataReaderTest8()
{
const string qs = "select ARRAY[0,1,2,3,42,null,4711,-1]::int2[], :p1";
short?[] p1_arr = { 0, 1, 2, 3, 42, null, 4711, -1 };
mCmd.CommandText = qs;
mCmd.CommandTimeout = 5;
PqsqlParameter p1 = mCmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = p1_arr;
p1.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Int2;
mCmd.Parameters.Add(p1);
using (PqsqlDataReader r = mCmd.ExecuteReader())
{
// select ARRAY[0,1,2,3,42,null,4711,-1]::_int2, :p1
bool read = r.Read();
Assert.IsTrue(read);
Array a0 = (Array)r.GetValue(0);
Array a1 = (Array)r.GetValue(1);
Assert.AreEqual(a0.Length, a1.Length);
Assert.AreEqual(a0.Rank, a1.Rank);
IEnumerator e1 = a1.GetEnumerator();
foreach (object o0 in a0)
{
if (e1.MoveNext())
{
short?s0 = (short?)o0;
short?s1 = (short?)e1.Current;
Assert.AreEqual(s0, s1);
}
else
{
Assert.Fail("cannot advance a1");
}
}
Assert.AreEqual((short)0, a0.GetValue(1));
Assert.AreEqual((short)1, a0.GetValue(2));
Assert.AreEqual((short)2, a0.GetValue(3));
Assert.AreEqual((short)3, a0.GetValue(4));
Assert.AreEqual((short)42, a0.GetValue(5));
Assert.AreEqual(null, a0.GetValue(6));
Assert.AreEqual((short)4711, a0.GetValue(7));
Assert.AreEqual((short)-1, a0.GetValue(8));
Assert.AreEqual((short)0, a1.GetValue(1));
Assert.AreEqual((short)1, a1.GetValue(2));
Assert.AreEqual((short)2, a1.GetValue(3));
Assert.AreEqual((short)3, a1.GetValue(4));
Assert.AreEqual((short)42, a1.GetValue(5));
Assert.AreEqual(null, a1.GetValue(6));
Assert.AreEqual((short)4711, a1.GetValue(7));
Assert.AreEqual((short)-1, a1.GetValue(8));
read = r.Read();
Assert.IsFalse(read);
}
}
public void PqsqlDataReaderTest6()
{
const string qs = @"select ARRAY[0,1,2,3,42,null,4711]::_text, :p7, unnest(:p6), unnest(:p8) ;
select interval '20 days', interval '123 secs', interval '20 years 10 months', now(), :p4, timestamp 'infinity', timestamp '-infinity', date 'infinity', date '-infinity';
select 123.456::numeric, :p3::text ;
select 'NaN'::numeric, '-1234567898765432123456789.87654321'::numeric ;
select generate_series(1,1000000),generate_series(1,1000000);
select generate_series(1,1000000),generate_series(1,1000000);
select :p1,:p2::text ;
select extract(epoch from date_trunc('day',current_date - :p9 ))::integer " ;
const int p1_val = -1;
const int p2_val = 2;
const int p3_val = -3;
const double p4_val = 3.1415925;
decimal p5_val = decimal.Parse("123456789,87654321");
int[] p6_arr = { 1, 42, 4711, 0815 };
const string p7_0 = "1 string mit ü and ä sowie 0";
const string p7_1 = "2 string mit ü and ä sowie 🐨";
const string p7_2 = "42 string mit ü and ä sowie е́";
const string p7_3 = "0815 string mit ü and ä sowie П";
decimal[] p8_arr = { decimal.MinValue, 1.23M, 12.345M, -123.4567M, decimal.MaxValue };
const int p9_val = 47;
mCmd.CommandText = qs;
mCmd.CommandTimeout = 20;
PqsqlParameter p1 = mCmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = p1_val;
p1.DbType = DbType.Int32;
PqsqlParameter p2 = mCmd.CreateParameter();
p2.ParameterName = "p2";
p2.Value = p2_val;
p2.DbType = DbType.Int32;
PqsqlParameter p3 = mCmd.CreateParameter();
p3.ParameterName = "p3";
p3.Value = p3_val;
p3.DbType = DbType.Int32;
PqsqlParameter p4 = mCmd.CreateParameter();
p4.ParameterName = "p4";
p4.Value = p4_val;
p4.DbType = DbType.Double;
PqsqlParameter p5 = mCmd.CreateParameter();
p5.ParameterName = "p5";
p5.Value = p5_val;
p5.DbType = DbType.Decimal;
PqsqlParameter p6 = mCmd.CreateParameter();
p6.ParameterName = "p6";
p6.Value = p6_arr;
p6.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Int4;
PqsqlParameter p7 = mCmd.CreateParameter();
p7.ParameterName = "p7";
Array b = Array.CreateInstance(typeof(string), new int[] { 4 }, new int[] { -1 });
b.SetValue(p7_0, -1);
b.SetValue(p7_1, 0);
b.SetValue(p7_2, 1);
b.SetValue(p7_3, 2);
p7.Value = b;
p7.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Text;
PqsqlParameter p8 = mCmd.CreateParameter();
p8.ParameterName = "p8";
Array c = Array.CreateInstance(typeof(decimal), new int[] { 5 }, new int[] { -2 });
c.SetValue(p8_arr[0], -2);
c.SetValue(p8_arr[1], -1);
c.SetValue(p8_arr[2], 0);
c.SetValue(p8_arr[3], 1);
c.SetValue(p8_arr[4], 2);
p8.Value = c;
p8.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Numeric;
PqsqlParameter p9 = mCmd.CreateParameter();
p9.ParameterName = "p9";
p9.Value = p9_val;
// let Pqsql guess the parameter type
mCmd.Parameters.Add(p1);
mCmd.Parameters.Add(p2);
mCmd.Parameters.Add(p3);
mCmd.Parameters.Add(p4);
mCmd.Parameters.Add(p5);
mCmd.Parameters.Add(p6);
mCmd.Parameters.Add(p7);
mCmd.Parameters.Add(p8);
mCmd.Parameters.Add(p9);
PqsqlTransaction t = mConnection.BeginTransaction();
mCmd.Transaction = t;
PqsqlDataReader r = mCmd.ExecuteReader();
int p6_rt = 0;
//int p8_rt = 0;
// select ARRAY[0,1,2,3,42,null,4711]::_text, :p7, unnest(:p6), unnest(:p8)
while (r.Read())
{
object o0 = r.GetValue(0);
object o1 = r.GetValue(1);
object o2 = r.GetValue(2);
object o3 = r.GetValue(3);
Array arr = (Array)o0;
Assert.AreEqual("0", arr.GetValue(1));
Assert.AreEqual("1", arr.GetValue(2));
Assert.AreEqual("2", arr.GetValue(3));
Assert.AreEqual("3", arr.GetValue(4));
Assert.AreEqual("42", arr.GetValue(5));
Assert.AreEqual(null, arr.GetValue(6));
Assert.AreEqual("4711", arr.GetValue(7));
arr = (Array)o1;
Assert.AreEqual(p7_0, arr.GetValue(1));
Assert.AreEqual(p7_1, arr.GetValue(2));
Assert.AreEqual(p7_2, arr.GetValue(3));
Assert.AreEqual(p7_3, arr.GetValue(4));
Assert.AreEqual(p6_arr[p6_rt++ % 4], o2);
// TODO decimal vs. double: decimal.MinValue cast to double does not work here
//Assert.AreEqual(p8_arr[p8_rt++], o3);
}
bool next = r.NextResult();
Assert.IsTrue(next);
// select interval '20 days', interval '123 secs', interval '20 years 10 months', now(), :p4, timestamp 'infinity', timestamp '-infinity', date 'infinity', date '-infinity'
while (r.Read())
{
TimeSpan s = r.GetTimeSpan(0);
Assert.AreEqual(TimeSpan.FromDays(20), s);
s = r.GetTimeSpan(1);
Assert.AreEqual(TimeSpan.FromSeconds(123), s);
s = r.GetTimeSpan(2);
Assert.AreEqual(TimeSpan.FromDays(7609), s);
DateTime d = r.GetDateTime(3);
TimeSpan diff = DateTime.UtcNow - d;
Assert.IsTrue(diff < TimeSpan.FromHours(1));
Assert.IsTrue(-diff < TimeSpan.FromHours(1));
double db = r.GetDouble(4);
Assert.AreEqual(p4_val, db);
d = r.GetDateTime(5);
Assert.AreEqual(DateTime.MaxValue, d);
d = r.GetDateTime(6);
Assert.AreEqual(DateTime.MinValue, d);
d = r.GetDateTime(7);
Assert.AreEqual(DateTime.MaxValue, d);
d = r.GetDateTime(8);
Assert.AreEqual(DateTime.MinValue, d);
}
next = r.NextResult();
Assert.IsTrue(next);
// select 123.456::numeric, :p3::text
while (r.Read())
{
decimal dec = r.GetDecimal(0);
Assert.AreEqual(123.456M, dec);
string str = r.GetString(1);
Assert.AreEqual(p3_val.ToString(), str);
}
t.Commit();
next = r.NextResult();
Assert.IsTrue(next);
// select 'NaN'::numeric, '-1234567898765432123456789.87654321'::numeric
while (r.Read())
{
double dou = r.GetDouble(0);
Assert.AreEqual(Double.NaN, dou);
dou = r.GetDouble(1);
Assert.AreEqual(decimal.ToDouble(-1234567898765432123456789.87654321M), dou);
}
next = r.NextResult();
Assert.IsTrue(next);
int comp = 1;
// select generate_series(1,1000000),generate_series(1,1000000)
while (r.Read())
{
int gen = r.GetInt32(0);
Assert.AreEqual(comp++, gen);
}
next = r.NextResult();
Assert.IsTrue(next);
comp = 1;
// select generate_series(1,1000000),generate_series(1,1000000)
while (r.Read())
{
int gen = r.GetInt32(0);
Assert.AreEqual(comp++, gen);
}
Assert.AreEqual(1000001, comp);
next = r.NextResult();
Assert.IsTrue(next);
comp = 0;
// select :p1,:p2::text
while (r.Read())
{
int i = r.GetInt32(0);
string st = r.GetString(1);
Assert.AreEqual(p1_val, i);
string p2s = Convert.ToString(p2_val);
Assert.AreEqual(p2s, st);
comp++;
}
Assert.AreEqual(1, comp);
next = r.NextResult();
Assert.IsTrue(next);
comp = 0;
// select extract(epoch from date_trunc('day',current_date - :p9 ))::integer
while (r.Read())
{
int i = r.GetInt32(0);
int unix = (int)DateTime.UtcNow.Subtract(new TimeSpan(p9_val, 0, 0, 0)).Date.Subtract(new DateTime(1970, 1, 1)).TotalSeconds;
Assert.AreEqual(unix, i);
comp++;
}
next = r.NextResult();
Assert.IsFalse(next);
r.Close();
r.Dispose();
}
public void PqsqlTypeRegistryTest5()
{
mCmd.CommandText = "select :ts0,:ts1,:ts2,:ts3,:ts4";
mCmd.CommandType = CommandType.Text;
PqsqlParameter p0 = new PqsqlParameter("ts0", DbType.DateTimeOffset, new DateTimeOffset(2016, 1, 1, 0, 0, 0, 0, TimeSpan.Zero));
mCmd.Parameters.Add(p0);
PqsqlParameter p1 = new PqsqlParameter
{
ParameterName = "ts1",
PqsqlDbType = PqsqlDbType.Unknown, // let PqsqlParameterCollection guess the type
Value = new DateTimeOffset(new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Local))
};
mCmd.Parameters.Add(p1);
PqsqlParameter p2 = new PqsqlParameter("ts2", DbType.DateTime, new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Local));
mCmd.Parameters.Add(p2);
PqsqlParameter p3 = new PqsqlParameter("ts3", DbType.DateTime, new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc));
mCmd.Parameters.Add(p3);
PqsqlParameter p4 = new PqsqlParameter("ts4", DbType.DateTime, new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
mCmd.Parameters.Add(p4);
PqsqlDataReader reader = mCmd.ExecuteReader();
bool read = reader.Read();
Assert.IsTrue(read);
DateTime ts0 = reader.GetDateTime(0);
DateTimeOffset tstz0 = reader.GetDateTimeOffset(0);
DateTimeOffset tsutc0 = tstz0.ToUniversalTime();
DateTimeOffset tslocal0 = tstz0.ToLocalTime();
Assert.AreEqual(ts0, tsutc0.DateTime);
DateTime ts1 = reader.GetDateTime(1);
DateTimeOffset tstz1 = reader.GetDateTimeOffset(1);
DateTimeOffset tsutc1 = tstz1.ToUniversalTime();
DateTimeOffset tslocal1 = tstz1.ToLocalTime();
DateTime ts2 = reader.GetDateTime(2);
DateTimeOffset tstz2 = reader.GetDateTimeOffset(2);
DateTimeOffset tsutc2 = tstz2.ToUniversalTime();
DateTimeOffset tslocal2 = tstz2.ToLocalTime();
Assert.AreEqual(ts2, tsutc2.UtcDateTime);
Assert.AreEqual(ts2.ToLocalTime(), tstz2.LocalDateTime);
Assert.AreEqual(ts2.ToLocalTime(), tsutc2.LocalDateTime);
DateTime ts3 = reader.GetDateTime(3);
DateTimeOffset tstz3 = reader.GetDateTimeOffset(3);
DateTimeOffset tsutc3 = tstz3.ToUniversalTime();
DateTimeOffset tslocal3 = tstz3.ToLocalTime();
Assert.AreEqual(ts3, tstz3.UtcDateTime);
Assert.AreEqual(ts3, tsutc3.DateTime);
DateTime ts4 = reader.GetDateTime(4);
DateTimeOffset tstz4 = reader.GetDateTimeOffset(4);
DateTimeOffset tsutc4 = tstz4.ToUniversalTime();
DateTimeOffset tslocal4 = tstz4.ToLocalTime();
Assert.AreEqual(ts4, tstz4.UtcDateTime);
Assert.AreEqual(ts4, tsutc4.DateTime);
read = reader.Read();
Assert.IsFalse(read);
}
