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();
}
