public void PqsqlDataReaderTest10() { mCmd.CommandText = @"select timestamp 'now', date 'now', time 'now';"; TimeSpan time = DateTime.UtcNow.TimeOfDay; using (PqsqlDataReader reader = mCmd.ExecuteReader()) { Assert.IsTrue(reader.HasRows); reader.Read(); DateTime c0 = reader.GetDateTime(0); DateTime c1 = reader.GetDateTime(1); DateTime c2 = reader.GetDateTime(2); Assert.AreEqual(DateTime.Today, c0 - c0.TimeOfDay); Assert.AreEqual(DateTime.Today, c1); // only compare hours and minutes Assert.AreEqual(time.Hours, c2.TimeOfDay.Hours); Assert.AreEqual(time.Minutes, c2.TimeOfDay.Minutes); reader.Close(); } }
public void PqsqlCommandTest2() { PqsqlCommand cmd = mConnection.CreateCommand(); cmd.CommandText = "bit_length"; cmd.CommandType = CommandType.StoredProcedure; IDbDataParameter i = cmd.CreateParameter(); i.ParameterName = "i"; i.Value = "postgres"; cmd.Parameters.Add(i); IDbDataParameter o = cmd.CreateParameter(); o.ParameterName = "bit_length"; o.Direction = ParameterDirection.Output; cmd.Parameters.Add(o); PqsqlDataReader r = cmd.ExecuteReader(); bool b = r.Read(); Assert.AreEqual(true, b); Assert.AreEqual(64, o.Value); }
// // Summary: // Executes a SQL statement against a connection object. // // Returns: // The number of rows affected. public override int ExecuteNonQuery() { PqsqlDataReader r = ExecuteReader(CommandBehavior.Default); // fill OUT and INOUT parameters with result tuple from the first row if (CommandType == CommandType.StoredProcedure) { r.Read(); // reading the first row will fill output parameters } int ra = Math.Max(-1, r.RecordsAffected); r.Consume(); // sync protocol: consume remaining rows if ((mCmdBehavior & CommandBehavior.SingleResult) == CommandBehavior.SingleResult) // only one statement available { return(ra); } // we have more than one statement while (r.NextResult()) { int n = r.RecordsAffected; // accumulate positive RecordsAffected for each UPDATE / DELETE / INSERT / CREATE * / ... statement if (n >= 0) { if (ra < 0) { ra = n; } else // ra >= 0 { ra += n; } } r.Consume(); // sync protocol: consume remaining rows } int last = r.RecordsAffected; // accumulate positive RecordsAffected for each UPDATE / DELETE / INSERT / CREATE * / ... statement if (last >= 0) { if (ra < 0) { ra = last; } else // ra >= 0 { ra += last; } } r.Consume(); // sync protocol: consume remaining rows return(ra); }
public void PqsqlTypeRegistryTest1() { PqsqlTransaction tran = mConnection.BeginTransaction(); string old_tz = mConnection.TimeZone; mCmd.CommandText = "set timezone to 'Asia/Hong_Kong';" + "select localtimestamp, now(), now() at time zone 'Europe/Vienna', '2016-10-01 12:00:00' at time zone 'UTC', '2016-10-01 12:00:00' at time zone 'Europe/Vienna', utc_offset from pg_timezone_names where name = current_setting('TIMEZONE');"; mCmd.CommandType = CommandType.Text; PqsqlDataReader reader = mCmd.ExecuteReader(); bool read = reader.Read(); Assert.IsFalse(read); string new_tz = mConnection.TimeZone; Assert.AreNotEqual(old_tz, new_tz); bool hasnext = reader.NextResult(); Assert.IsTrue(hasnext); read = reader.Read(); Assert.IsTrue(read); DateTime now0 = reader.GetDateTime(0); DateTime now1 = reader.GetDateTime(1); DateTime now2 = reader.GetDateTime(2); DateTime now3 = reader.GetDateTime(3); DateTime now4 = reader.GetDateTime(4); TimeSpan ts = reader.GetTimeSpan(5); reader.Close(); DateTime nowutc0 = now0.ToUniversalTime(); TimeZoneInfo tzi_from_china_pgsql = TimeZoneInfo.CreateCustomTimeZone(new_tz, ts, new_tz, new_tz); #if WIN32 var tz = "China Standard Time"; #else var tz = "Asia/Shanghai"; #endif TimeZoneInfo tzi_from_china_sys = TimeZoneInfo.FindSystemTimeZoneById(tz); TimeSpan china_off = tzi_from_china_sys.GetUtcOffset(nowutc0); TimeSpan local_off = TimeZoneInfo.Local.GetUtcOffset(nowutc0); DateTimeOffset nowlocal0 = (nowutc0 + local_off - china_off).ToLocalTime(); DateTimeOffset dto_from_pgsql_to_sys = TimeZoneInfo.ConvertTime(now0, tzi_from_china_pgsql, tzi_from_china_sys); DateTimeOffset dto_from_pgsql_to_localtime = TimeZoneInfo.ConvertTime(now0, tzi_from_china_pgsql, TimeZoneInfo.Local); Assert.AreEqual(nowutc0, dto_from_pgsql_to_sys); Assert.AreEqual(nowlocal0, dto_from_pgsql_to_localtime); tran.Rollback(); }
public void PqsqlTypeRegistryTest6() { Action[] actions = new Action[20]; // stress test user-defined type setup for (int i = 0; i < 20; i++) { actions[i] = () => { using (PqsqlConnection conn = new PqsqlConnection(mConnection.ConnectionString)) using (PqsqlCommand cmd = new PqsqlCommand("select 'hello world'::citext", conn)) using (PqsqlDataReader reader = cmd.ExecuteReader()) { bool read = reader.Read(); Assert.IsTrue(read); object helloWorld = reader.GetValue(0); // must access by GetValue, GetString verifies typoid Assert.AreEqual("hello world", helloWorld); read = reader.Read(); Assert.IsFalse(read); } }; } using (PqsqlCommand check = new PqsqlCommand("select oid from pg_extension where extname='citext'", mConnection)) using (PqsqlCommand create = new PqsqlCommand("create extension citext", mConnection)) using (PqsqlTransaction t = mConnection.BeginTransaction()) { object o = null; try { check.Transaction = t; o = check.ExecuteScalar(); if (o == null) { create.Transaction = t; int aff = create.ExecuteNonQuery(); Assert.AreEqual(0, aff); } t.Commit(); Parallel.Invoke(actions); } finally { if (o == null) { using (PqsqlCommand drop = new PqsqlCommand("drop extension if exists citext", mConnection)) { int aff = drop.ExecuteNonQuery(); Assert.AreEqual(0, aff); } } } } }
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 PqsqlConnectionStringBuilderTest1() { PqsqlConnectionStringBuilder builder = new PqsqlConnectionStringBuilder(connectionString); builder[PqsqlConnectionStringBuilder.keepalives] = "1"; builder[PqsqlConnectionStringBuilder.keepalives_idle] = "23"; builder[PqsqlConnectionStringBuilder.keepalives_count] = "3"; builder[PqsqlConnectionStringBuilder.keepalives_interval] = "3"; string dataSource; using (PqsqlConnection connection = new PqsqlConnection(builder)) { // closed connection with service file should give us empty data source Assert.IsTrue(string.IsNullOrEmpty(connection.DataSource)); using (PqsqlCommand cmd = new PqsqlCommand("show all", connection)) using (PqsqlDataReader r = cmd.ExecuteReader()) { object value; if (builder.TryGetValue(PqsqlConnectionStringBuilder.host, out value)) { Assert.AreEqual(connection.DataSource, value); dataSource = value.ToString(); } else // no datasource specified { dataSource = connection.DataSource; } cmd.Cancel(); } } builder[PqsqlConnectionStringBuilder.host] = dataSource; using (PqsqlConnection connection = new PqsqlConnection(builder)) using (PqsqlCommand cmd = new PqsqlCommand("show all", connection)) using (PqsqlDataReader r = cmd.ExecuteReader()) { object value; if (builder.TryGetValue(PqsqlConnectionStringBuilder.host, out value)) { Assert.AreEqual(connection.DataSource, value); } else { Assert.Fail("host part is not available"); } cmd.Cancel(); } }
public void PqsqlCommandTest1() { PqsqlCommand cmd = mConnection.CreateCommand(); cmd.CommandText = "select pg_sleep(1);"; PqsqlDataReader r = cmd.ExecuteReader(); bool b = r.Read(); Assert.AreEqual(true, b); object v = r.GetValue(0); Assert.AreEqual("", v); }
public void PqsqlCommandTest13() { PqsqlCommand cmd = new PqsqlCommand("select :貓1;", mConnection) { CommandType = CommandType.Text }; // add non-standard parametername cmd.Parameters.AddWithValue(":貓1", "喵"); using (PqsqlDataReader r = cmd.ExecuteReader()) { Assert.Fail("ExecuteReader() must fail"); } }
public void PqsqlCommandTest17() { using (PqsqlCommand cmd = new PqsqlCommand("select /*state*/'x' from /* xxx */ pg_stat_activity ", mConnection) { CommandType = CommandType.Text }) { PqsqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { string s = !r.IsDBNull(0) ? r.GetString(0) : null; } } }
public void PqsqlCommandTest19() { using (PqsqlCommand cmd = new PqsqlCommand("select state from pg_stat_activity -- no semicolon", mConnection) { CommandType = CommandType.Text }) { PqsqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { string s = !r.IsDBNull(0) ? r.GetString(0) : null; } } }
public void PqsqlDataReaderTest7() { // test UTF-8 column names roundtrip mCmd.CommandText = "select 12345 as \"€₺£$¥\", 67890 as \"⣿⣶⣤⣀⠀\";"; using (PqsqlDataReader reader = mCmd.ExecuteReader()) { reader.Read(); string c0 = reader.GetName(0); string c1 = reader.GetName(1); Assert.AreEqual("€₺£$¥", c0); Assert.AreEqual("⣿⣶⣤⣀⠀", c1); } // test lower/upper parameter names roundtrip mCmd.CommandText = "select :P0 as \"零\", :P1 as \"一\", :p2 as \"二\", :p3 as \"三\" ;"; mCmd.Parameters.AddWithValue("p0", 0); mCmd.Parameters.AddWithValue("p1", 1); mCmd.Parameters.AddWithValue("P2", 2); mCmd.Parameters.AddWithValue("P3", 3); using (PqsqlDataReader reader = mCmd.ExecuteReader()) { reader.Read(); string c0 = reader.GetName(0); string c1 = reader.GetName(1); string c2 = reader.GetName(2); string c3 = reader.GetName(3); int i0 = reader.GetInt32(0); int i1 = reader.GetInt32(1); int i2 = reader.GetInt32(2); int i3 = reader.GetInt32(3); Assert.AreEqual(0, i0); Assert.AreEqual(1, i1); Assert.AreEqual(2, i2); Assert.AreEqual(3, i3); Assert.AreEqual("零", c0); Assert.AreEqual("一", c1); Assert.AreEqual("二", c2); Assert.AreEqual("三", c3); } }
public void PqsqlDataReaderTest12() { mCmd.CommandText = @"select interval '23 days', timestamp '2038-01-01', timestamptz '2038-01-01 00:00:00+02', date '2038-01-01', time '00:00:00', timetz '23:23:23+02';"; using (PqsqlDataReader reader = mCmd.ExecuteReader()) { Assert.IsTrue(reader.HasRows); reader.Read(); TimeSpan ts0 = reader.GetTimeSpan(0); Assert.AreEqual(TimeSpan.FromDays(23), ts0); TimeSpan ts1 = reader.GetTimeSpan(1); DateTime dt1 = reader.GetDateTime(1); Assert.AreEqual(new DateTime(2038, 01, 01, 0, 0, 0), dt1); DateTimeOffset dto1 = reader.GetDateTimeOffset(1); Assert.AreEqual(new DateTimeOffset(2038, 01, 01, 0, 0, 0, TimeSpan.Zero), dto1); TimeSpan ts2 = reader.GetTimeSpan(2); DateTime dt2 = reader.GetDateTime(2); Assert.AreEqual(new DateTime(2037, 12, 31, 22, 0, 0), dt2); DateTimeOffset dto2 = reader.GetDateTimeOffset(2); Assert.AreEqual(new DateTimeOffset(2038, 01, 01, 0, 0, 0, TimeSpan.FromHours(2)), dto2); TimeSpan ts3 = reader.GetTimeSpan(3); DateTime dt3 = reader.GetDateTime(3); Assert.AreEqual(new DateTime(2038, 01, 01, 0, 0, 0), dt3); DateTimeOffset dto3 = reader.GetDateTimeOffset(3); Assert.AreEqual(new DateTimeOffset(2038, 01, 01, 0, 0, 0, TimeSpan.Zero), dto3); TimeSpan ts4 = reader.GetTimeSpan(4); Assert.AreEqual(TimeSpan.FromSeconds(0), ts4); DateTime dt4 = reader.GetDateTime(4); Assert.AreEqual(DateTime.MinValue, dt4); DateTimeOffset dto4 = reader.GetDateTimeOffset(4); Assert.AreEqual(DateTimeOffset.MinValue, dto4); TimeSpan ts5 = reader.GetTimeSpan(5); //Assert.AreEqual(TimeSpan.FromHours(21).Add(TimeSpan.FromMinutes(23)).Add(TimeSpan.FromSeconds(23)), ts5); DateTime dt5 = reader.GetDateTime(5); //Assert.AreEqual(DateTime.MinValue.AddHours(21).AddMinutes(23).AddSeconds(23), dt5); DateTimeOffset dto5 = reader.GetDateTimeOffset(5); //Assert.AreEqual(new DateTimeOffset(DateTime.MinValue.AddHours(23).AddMinutes(23).AddSeconds(23), TimeSpan.FromHours(2)), dto5); reader.Close(); } }
public void PqsqlTypeRegistryTest2() { mCmd.CommandText = "select localtimestamp, now()::timestamp, now(), now() at time zone 'UTC', '1999-01-01 00:00:00'::timestamp at time zone 'UTC', '2000-07-01 00:00:00'::timestamp at time zone 'Europe/Vienna'"; mCmd.CommandType = CommandType.Text; PqsqlDataReader reader = mCmd.ExecuteReader(); bool read = reader.Read(); Assert.IsTrue(read); DateTime localtimestamp0 = reader.GetDateTime(0); DateTimeOffset localtimestampoff0 = reader.GetDateTimeOffset(0); DateTime nownotz1 = reader.GetDateTime(1); DateTimeOffset nownotzoff1 = reader.GetDateTimeOffset(1); DateTime nowtz2 = reader.GetDateTime(2); DateTimeOffset nowtzoff2 = reader.GetDateTimeOffset(2); DateTime nowutc3 = reader.GetDateTime(3); DateTimeOffset nowutcoff3 = reader.GetDateTimeOffset(3); DateTime ts19990101000000_4 = reader.GetDateTime(4); DateTimeOffset ts19990101000000_off4 = reader.GetDateTimeOffset(4); DateTime ts20000701000000_5 = reader.GetDateTime(5); DateTimeOffset ts20000701000000_off5 = reader.GetDateTimeOffset(5); DateTimeOffset off0 = TimeZoneInfo.ConvertTime(new DateTimeOffset(localtimestamp0.Ticks, TimeSpan.Zero), TimeZoneInfo.Local); DateTimeOffset off1 = TimeZoneInfo.ConvertTime(new DateTimeOffset(nownotz1.Ticks, TimeSpan.Zero), TimeZoneInfo.Local); DateTimeOffset off2 = TimeZoneInfo.ConvertTime(new DateTimeOffset(nowtz2.Ticks, TimeSpan.Zero), TimeZoneInfo.Local); DateTimeOffset off3 = TimeZoneInfo.ConvertTime(new DateTimeOffset(nowutc3.Ticks, TimeSpan.Zero), TimeZoneInfo.Local); DateTimeOffset off4 = TimeZoneInfo.ConvertTime(new DateTimeOffset(ts19990101000000_4.Ticks, TimeSpan.Zero), TimeZoneInfo.Local); DateTimeOffset off5 = TimeZoneInfo.ConvertTime(new DateTimeOffset(ts20000701000000_5.Ticks, TimeSpan.Zero), TimeZoneInfo.Local); Assert.AreEqual(localtimestampoff0, off0); Assert.AreEqual(nownotzoff1, off1); Assert.AreEqual(nowtzoff2, off2); Assert.AreEqual(nowutcoff3, off3); Assert.AreEqual(ts19990101000000_off4, off4); Assert.AreEqual(ts20000701000000_off5, off5); read = reader.Read(); Assert.IsFalse(read); }
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); } }
// // Summary: // Executes the query and returns the first column of the first row in the result // set returned by the query. All other columns and rows are ignored. // // Returns: // The first column of the first row in the result set. public override object ExecuteScalar() { PqsqlDataReader r = ExecuteReader(CommandBehavior.Default); object o; if (r.Read()) { o = r.GetValue(0); } else { o = null; } r.Consume(); // sync protocol: consume remaining rows return(o); }
public void PqsqlDataReaderTest3() { mCmd.CommandText = "select datid,datname,pid,application_name,backend_start,query from pg_stat_activity"; PqsqlDataReader reader = mCmd.ExecuteReader(CommandBehavior.CloseConnection); Assert.AreEqual(false, reader.IsClosed); Assert.AreEqual(-1, reader.RecordsAffected); int ordinal = reader.GetOrdinal("application_name"); // application_name is the 4th column Assert.AreEqual(3, ordinal); reader.Close(); Assert.AreEqual(ConnectionState.Closed, mConnection.State); Assert.AreEqual(true, reader.IsClosed); }
public void PqsqlCommandTest12() { PqsqlCommand cmd = new PqsqlCommand("select :p1;", mConnection) { CommandType = CommandType.Text, Parameters = { // do not add parameter :p1, add p0 and p2 instead new PqsqlParameter(":p0", DbType.String, "p0value"), new PqsqlParameter(":p2", DbType.String, "p2value") } }; using (PqsqlDataReader r = cmd.ExecuteReader()) { Assert.Fail("ExecuteReader() must fail"); } }
public void PqsqlTypeRegistryTest3() { mCmd.CommandText = "select 'YES'::information_schema.yes_or_no;"; mCmd.CommandType = CommandType.Text; PqsqlDataReader reader = mCmd.ExecuteReader(); bool read = reader.Read(); Assert.IsTrue(read); string yes = reader.GetString(0); Assert.AreEqual("YES", yes); read = reader.Read(); Assert.IsFalse(read); }
public void PqsqlTypeRegistryTest4() { mCmd.CommandText = "select 'YES'::citext"; mCmd.CommandType = CommandType.Text; using (PqsqlCommand check = new PqsqlCommand("select oid from pg_extension where extname='citext'", mConnection)) using (PqsqlCommand create = new PqsqlCommand("create extension citext", mConnection)) using (PqsqlCommand drop = new PqsqlCommand("drop extension if exists citext", mConnection)) { object o = null; try { o = check.ExecuteScalar(); if (o == null) { int aff = create.ExecuteNonQuery(); Assert.AreEqual(0, aff); } PqsqlDataReader reader = mCmd.ExecuteReader(); bool read = reader.Read(); Assert.IsTrue(read); object yes = reader.GetValue(0); // must access by GetValue, GetString verifies typoid Assert.AreEqual("YES", yes); reader.Close(); } finally { if (o == null) { int aff = drop.ExecuteNonQuery(); Assert.AreEqual(0, aff); } } } }
public void PqsqlDataReaderTest9() { mCmd.CommandText = @"select 1234567890123456789::int8, 0::oid, '00000000-0000-0000-0000-000000000000'::uuid, 47.11::float4, E'\\000\\001\\002\\003'::bytea, null as ""lastcol"""; using (PqsqlDataReader reader = mCmd.ExecuteReader()) { Assert.IsTrue(reader.HasRows); reader.Read(); long c0 = reader.GetInt64(0); uint c1 = reader.GetOid(1); Guid c2 = reader.GetGuid(2); float c3 = reader.GetFloat(3); long len = reader.GetBytes(4, 0, null, 0, 0); Assert.AreEqual((long)4, len); byte[] c4 = new byte[len]; long read = reader.GetBytes(4, 0, c4, 0, (int)len); Assert.AreEqual(read, len); Assert.AreEqual(1234567890123456789, c0); Assert.AreEqual((uint)0, c1); Assert.AreEqual(Guid.Empty, c2); Assert.AreEqual((float)47.11, c3); byte[] buf = { 0, 1, 2, 3 }; Assert.AreEqual(buf[0], c4[0]); Assert.AreEqual(buf[1], c4[1]); Assert.AreEqual(buf[2], c4[2]); Assert.AreEqual(buf[3], c4[3]); object last = reader[5]; Assert.AreEqual(DBNull.Value, last); last = reader["lastcol"]; Assert.AreEqual(DBNull.Value, last); reader.Close(); } }
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 PqsqlDataReaderTest5() { mConnection.Open(); // get dbname from connection object dbname = mConnection.Database; PqsqlDataReader reader = new PqsqlDataReader(mCmd, CommandBehavior.SchemaOnly, new[] { "select oid as ObjectId from pg_class" }); DataTable schematable = reader.GetSchemaTable(); Assert.IsNotNull(schematable); Assert.AreEqual(13, schematable.Columns.Count); DataRowCollection rows = schematable.Rows; Assert.AreEqual(1, rows.Count); DataRow row = rows[0]; Assert.AreEqual(PqsqlDbType.Oid, (PqsqlDbType)row[PqsqlSchemaTableColumn.TypeOid]); Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]); Assert.AreEqual("objectid", row[SchemaTableColumn.BaseColumnName]); Assert.AreEqual(dbname, row[SchemaTableOptionalColumn.BaseCatalogName]); Assert.AreEqual("pg_catalog", row[SchemaTableColumn.BaseSchemaName]); Assert.AreEqual("pg_class", row[SchemaTableColumn.BaseTableName]); Assert.AreEqual("objectid", row[SchemaTableColumn.ColumnName]); Assert.AreEqual(1, row[SchemaTableColumn.ColumnOrdinal]); Assert.AreEqual(4, row[SchemaTableColumn.ColumnSize]); Assert.AreEqual(0, row[SchemaTableColumn.NumericPrecision]); Assert.AreEqual(0, row[SchemaTableColumn.NumericScale]); Assert.AreEqual("oid", row[SchemaTableColumn.ProviderType]); Assert.AreEqual(typeof(uint), row[SchemaTableColumn.DataType]); }
public void PqsqlDataReaderTest1() { // TODO we don't support xid and inet datatypes yet mCmd.CommandText = "select datid,datname,pid,application_name,backend_start,query from pg_stat_activity"; PqsqlDataReader reader = mCmd.ExecuteReader(); Assert.AreEqual(false, reader.IsClosed); Assert.AreEqual(6, reader.FieldCount); DataTable dt = reader.GetSchemaTable(); Assert.AreNotEqual(null, dt); Assert.AreEqual(6, dt.Rows.Count); Assert.AreEqual("datid", dt.Rows[0][SchemaTableColumn.ColumnName]); Assert.AreEqual(1, dt.Rows[0][SchemaTableColumn.ColumnOrdinal]); Assert.AreEqual("datname", dt.Rows[1][SchemaTableColumn.ColumnName]); Assert.AreEqual(2, dt.Rows[1][SchemaTableColumn.ColumnOrdinal]); Assert.AreEqual("pid", dt.Rows[2][SchemaTableColumn.ColumnName]); Assert.AreEqual(3, dt.Rows[2][SchemaTableColumn.ColumnOrdinal]); Assert.AreEqual("application_name", dt.Rows[3][SchemaTableColumn.ColumnName]); Assert.AreEqual(4, dt.Rows[3][SchemaTableColumn.ColumnOrdinal]); Assert.AreEqual("backend_start", dt.Rows[4][SchemaTableColumn.ColumnName]); Assert.AreEqual(5, dt.Rows[4][SchemaTableColumn.ColumnOrdinal]); Assert.AreEqual("query", dt.Rows[5][SchemaTableColumn.ColumnName]); Assert.AreEqual(6, dt.Rows[5][SchemaTableColumn.ColumnOrdinal]); reader.Close(); Assert.AreEqual(ConnectionState.Open, mConnection.State); Assert.AreEqual(true, reader.IsClosed); }
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 PqsqlDataReaderTest11() { mCmd.CommandText = @"select null, ''::varchar, '你', '好'::text, '吗'::char(1), 'x'::""char"" ;"; using (PqsqlDataReader reader = mCmd.ExecuteReader()) { Assert.IsTrue(reader.HasRows); reader.Read(); char c1 = reader.GetChar(1); string s1 = reader.GetString(1); char c2 = reader.GetChar(2); string s2 = reader.GetString(2); char c3 = reader.GetChar(3); string s3 = reader.GetString(3); char c4 = reader.GetChar(4); string s4 = reader.GetString(4); char c5 = reader.GetChar(5); string s5 = reader.GetString(5); Assert.IsTrue(reader.IsDBNull(0)); Assert.AreEqual(default(char), c1); Assert.AreEqual(string.Empty, s1); Assert.AreEqual('你', c2); Assert.AreEqual("你", s2); Assert.AreEqual('好', c3); Assert.AreEqual("好", s3); Assert.AreEqual('吗', c4); Assert.AreEqual("吗", s4); Assert.AreEqual('x', c5); Assert.AreEqual("x", s5); reader.Close(); } }
public void PqsqlDataReaderTest2() { // TODO we don't support xid and inet datatypes yet mCmd.CommandText = "select datid,datname,pid,application_name,backend_start,query from pg_stat_activity"; PqsqlDataReader reader = mCmd.ExecuteReader(CommandBehavior.CloseConnection); Assert.AreEqual(false, reader.IsClosed); Assert.AreEqual(-1, reader.RecordsAffected); int read = 0; foreach (object o in reader) { read++; } // we must have at least one connection open (our one) Assert.AreNotEqual(0, read); reader.Close(); Assert.AreEqual(ConnectionState.Closed, mConnection.State); Assert.AreEqual(true, reader.IsClosed); }
// // Summary: // Executes the System.Data.Common.DbCommand.CommandText against the System.Data.Common.DbCommand.Connection, // and returns an System.Data.Common.DbDataReader using one of the System.Data.CommandBehavior // values. // // Parameters: // behavior: // One of the System.Data.CommandBehavior values. // // Returns: // An System.Data.Common.DbDataReader object. public new PqsqlDataReader ExecuteReader(CommandBehavior behavior) { #if CODECONTRACTS Contract.Ensures(Contract.Result <PqsqlDataReader>() != null); #endif string[] statements; switch (CommandType) { case CommandType.Text: statements = ParseStatements().ToArray(); break; case CommandType.StoredProcedure: statements = BuildStoredProcStatement(); break; case CommandType.TableDirect: statements = BuildTableStatement(); break; default: throw new InvalidEnumArgumentException("unknown CommandType"); } #if CODECONTRACTS Contract.Assert(statements != null); #endif if (statements.Length < 2) { behavior |= CommandBehavior.SingleResult; } CheckOpen(); #if CODECONTRACTS Contract.Assert(mConn != null); #endif // always try to set statement_timeout, the session started // with the PqsqlDataReader below will then have this timeout // until we issue the next CommandTimeout if (mCmdTimeout > 0) { mConn.SetSessionParameter(PqsqlClientConfiguration.StatementTimeout, CommandTimeout); } // save behavior mCmdBehavior = behavior; PqsqlDataReader r = null; PqsqlDataReader reader; try { r = new PqsqlDataReader(this, behavior, statements); r.NextResult(); // always execute first command // swap r with reader reader = r; r = null; } finally { if (r != null) // only dispose PqsqlDataReader if r.NextResult() throwed an exception { r.Close(); r.Dispose(); } } return(reader); }
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 PqsqlCommandTest9() { PqsqlTransaction t = mConnection.BeginTransaction(); PqsqlCommand cmd = mConnection.CreateCommand(); cmd.Transaction = t; cmd.CommandText = @"create or replace function ""pg_temp"".""test me""(i int) returns int as $code$ begin return $1 * i; end; $code$ language plpgsql; select ""pg_temp"".""test me""(:p1); select ""pg_temp"".""test me""($1)" ; cmd.CommandTimeout = 2; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("p1", 4711); using (PqsqlDataReader r = cmd.ExecuteReader()) { bool good = r.Read(); Assert.IsFalse(good); good = r.NextResult(); Assert.IsTrue(good); good = r.Read(); Assert.IsTrue(good); int n = r.GetInt32(0); Assert.AreEqual(4711 * 4711, n); good = r.Read(); Assert.IsFalse(good); good = r.NextResult(); Assert.IsTrue(good); good = r.Read(); Assert.IsTrue(good); n = r.GetInt32(0); Assert.AreEqual(4711 * 4711, n); good = r.Read(); Assert.IsFalse(good); } cmd.CommandText = "\"pg_temp\".\"test me\""; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("i", 4711); cmd.Parameters.Add(new PqsqlParameter { ParameterName = "\"pg_temp\".\"test me\"", DbType = DbType.Int32, Direction = ParameterDirection.Output }); object x = cmd.ExecuteScalar(); Assert.AreEqual(4711 * 4711, x); t.Rollback(); }