public void PqsqlCommandBuilderTest2() { using (PqsqlConnection connection = new PqsqlConnection(connectionString)) using (PqsqlCommand command = connection.CreateCommand()) { PqsqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)"; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); transaction.Commit(); // temp table must be visible in the next transaction transaction = connection.BeginTransaction(); PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection) { SelectCommand = { Transaction = transaction }, }; adapter.RowUpdated += Adapter_RowUpdated; PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(ds, "temptab"); DataTable temptab = ds.Tables["temptab"]; DataRow row = temptab.NewRow(); row["c0"] = 123; row["c1"] = 1.23; temptab.Rows.Add(row); adapter.Update(ds, "temptab"); command.CommandText = "select * from temptab"; command.CommandType = CommandType.Text; using (PqsqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Assert.AreEqual(123, reader.GetInt32(0)); Assert.AreEqual(1.23, reader.GetDouble(1)); } } transaction.Rollback(); } }
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(); }