public void ReadTime()
{
// TODO: Decide on the DateTime kind (#346)
var expectedNpgsqlTime = new NpgsqlTime(10, 3, 45, 345000);
var expectedDateTime = new DateTime(expectedNpgsqlTime.Ticks, DateTimeKind.Unspecified);
var cmd = new NpgsqlCommand("SELECT '10:03:45.345'::TIME", Conn);
var reader = cmd.ExecuteReader();
reader.Read();
// Regular type (DateTime)
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(DateTime)));
Assert.That(reader.GetDateTime(0), Is.EqualTo(expectedDateTime));
Assert.That(reader.GetFieldValue<DateTime>(0), Is.EqualTo(expectedDateTime));
Assert.That(reader[0], Is.EqualTo(expectedDateTime));
Assert.That(reader.GetValue(0), Is.EqualTo(expectedDateTime));
// Provider-specific type (NpgsqlTime)
Assert.That(reader.GetTime(0), Is.EqualTo(expectedNpgsqlTime));
Assert.That(reader.GetProviderSpecificFieldType(0), Is.EqualTo(typeof(NpgsqlTime)));
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(expectedNpgsqlTime));
Assert.That(reader.GetFieldValue<NpgsqlTime>(0), Is.EqualTo(expectedNpgsqlTime));
reader.Close();
cmd.Dispose();
}
public void Roundtrip()
{
const string expected = "Something";
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Text);
var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Varchar);
var p3 = new NpgsqlParameter("p3", DbType.String);
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expected };
Assert.That(p2.DbType, Is.EqualTo(DbType.String));
Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
Assert.That(p3.DbType, Is.EqualTo(DbType.String));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
p1.Value = p2.Value = p3.Value = expected;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(string)));
Assert.That(reader.GetString(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(i), Is.EqualTo(expected));
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(i), Is.EqualTo(expected.ToCharArray()));
}
reader.Close();
cmd.Dispose();
}
public void Roundtrip()
{
byte[] expected = { 1, 2, 3, 4, 5 };
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Bytea);
var p2 = new NpgsqlParameter("p2", DbType.Binary);
var p3 = new NpgsqlParameter { ParameterName = "p3", Value = expected };
Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bytea));
Assert.That(p3.DbType, Is.EqualTo(DbType.Binary));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
p1.Value = p2.Value = expected;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof (byte[])));
Assert.That(reader.GetFieldValue<byte[]>(i), Is.EqualTo(expected));
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
}
reader.Close();
cmd.Dispose();
}
public static void CreateTables(NpgsqlConnection conn)
{
using (var cmdRename1 = new NpgsqlCommand("select table_name from information_schema.tables where table_name = 'rn_results'", conn))
{
var tblExists = false;
using (var rdr1 = cmdRename1.ExecuteReader()) { tblExists = rdr1.Read(); }
cmdRename1.Dispose();
if (tblExists)
{
var curCount = 0;
using (var cmdRename2 = new NpgsqlCommand("select count(*) from rn_results", conn))
{
curCount = Convert.ToInt32(cmdRename2.ExecuteScalar());
}
if (curCount == 0)
{
using (var cmdRename3 = new NpgsqlCommand("drop table rn_results", conn)) { cmdRename3.ExecuteNonQuery(); }
}
else
{
using (var cmdRename2 = new NpgsqlCommand("alter table rn_results rename to rn_results_old" + (curDBVersion - 1).ToString(), conn)) { cmdRename2.ExecuteNonQuery(); }
}
}
}
using (var cmd1 = new NpgsqlCommand("drop table rn_version;", conn)) { cmd1.ExecuteNonQuery(); }
using (var cmd2 = new NpgsqlCommand("create table rn_version (ver integer);", conn)) { cmd2.ExecuteNonQuery(); }
using (var cmd3 = new NpgsqlCommand("insert into rn_version (ver) values (" + curDBVersion.ToString() + ")", conn)) { cmd3.ExecuteScalar(); }
using (var cmd4 = new NpgsqlCommand("create table rn_results (email varchar(100), name varchar(100), phone_no varchar(100), daum_id varchar(100), work_place varchar(200), client_ip varchar(50), sign_image1 text, sign_image2 text, insert_dt timestamp, sign_radio1 integer, sign_radio2 integer, PRIMARY KEY(email));", conn))
{
cmd4.ExecuteNonQuery();
}
using (var cmd5 = new NpgsqlCommand("create index rn_results_idx_insert_dt on rn_results (insert_dt)", conn)) { cmd5.ExecuteNonQuery(); }
}
public void ReadDate()
{
// TODO: Decide on the DateTime kind (#346)
var expectedDateTime = new DateTime(2002, 3, 4, 0, 0, 0, 0, DateTimeKind.Unspecified);
var expectedNpgsqlDate = new NpgsqlDate(expectedDateTime);
ExecuteNonQuery("INSERT INTO data (field_date) VALUES ('2002-03-04')");
var cmd = new NpgsqlCommand("SELECT '2002-03-04'::DATE", Conn);
var reader = cmd.ExecuteReader();
reader.Read();
// Regular type (DateTime)
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof (DateTime)));
Assert.That(reader.GetDateTime(0), Is.EqualTo(expectedDateTime));
Assert.That(reader.GetFieldValue<DateTime>(0), Is.EqualTo(expectedDateTime));
Assert.That(reader[0], Is.EqualTo(expectedDateTime));
Assert.That(reader.GetValue(0), Is.EqualTo(expectedDateTime));
// Provider-specific type (NpgsqlDate)
Assert.That(reader.GetDate(0), Is.EqualTo(expectedNpgsqlDate));
Assert.That(reader.GetProviderSpecificFieldType(0), Is.EqualTo(typeof(NpgsqlDate)));
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(expectedNpgsqlDate));
Assert.That(reader.GetFieldValue<NpgsqlDate>(0), Is.EqualTo(expectedNpgsqlDate));
cmd.Dispose();
}
public void Bool()
{
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Boolean);
var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Boolean);
var p3 = new NpgsqlParameter("p3", DbType.Boolean);
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = true };
Assert.That(p4.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Boolean));
Assert.That(p4.DbType, Is.EqualTo(DbType.Boolean));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
p1.Value = false;
p2.Value = p3.Value = true;
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader.GetBoolean(0), Is.False);
for (var i = 1; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetBoolean(i), Is.True);
Assert.That(reader.GetValue(i), Is.True);
Assert.That(reader.GetProviderSpecificValue(i), Is.True);
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof (bool)));
Assert.That(reader.GetDataTypeName(i), Is.EqualTo("bool"));
}
reader.Close();
cmd.Dispose();
}
public void EmptyResultSet()
{
var cmd = new NpgsqlCommand("SELECT 1 WHERE FALSE", Conn);
var reader = cmd.ExecuteReader();
Assert.That(reader.Read(), Is.False);
Assert.That(reader.FieldCount, Is.EqualTo(1));
Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>());
cmd.Dispose();
}
public void LongOneDimensional()
{
var expected = new int[Conn.BufferSize / 4 + 100];
for (var i = 0; i < expected.Length; i++)
expected[i] = i;
var cmd = new NpgsqlCommand("SELECT @p", Conn);
var p = new NpgsqlParameter { ParameterName = "p", Value = expected };
cmd.Parameters.Add(p);
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(expected));
cmd.Dispose();
}
public static int UPDATE(string query)
{
NpgsqlConnection conexion = new NpgsqlConnection(DBConector.conectionString);
conexion.Open();
NpgsqlCommand comando = new NpgsqlCommand(query, conexion);
int filasCambiadas = comando.ExecuteNonQuery();
comando.Dispose();
conexion.Close();
return filasCambiadas;
}
public void InetV4()
{
var expectedIp = IPAddress.Parse("192.168.1.1");
var expectedInet = new NpgsqlInet(expectedIp, 24);
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Inet) { Value = expectedInet };
var p2 = new NpgsqlParameter { ParameterName = "p2", Value = expectedInet };
var p3 = new NpgsqlParameter("p3", NpgsqlDbType.Inet) { Value = expectedIp };
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expectedIp };
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < 2; i++)
{
// Regular type (IPAddress)
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(IPAddress)));
Assert.That(reader.GetFieldValue<IPAddress>(i), Is.EqualTo(expectedIp));
Assert.That(reader[i], Is.EqualTo(expectedIp));
Assert.That(reader.GetValue(i), Is.EqualTo(expectedIp));
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(IPAddress)));
// Provider-specific type (NpgsqlInet)
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlInet)));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(expectedInet));
Assert.That(reader.GetFieldValue<NpgsqlInet>(i), Is.EqualTo(expectedInet));
Assert.That(reader.GetString(i), Is.EqualTo(expectedInet.ToString()));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlInet)));
}
for (var i = 2; i < 4; i++)
{
// Regular type (IPAddress)
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(IPAddress)));
Assert.That(reader.GetFieldValue<IPAddress>(i), Is.EqualTo(expectedIp));
Assert.That(reader[i], Is.EqualTo(expectedIp));
Assert.That(reader.GetValue(i), Is.EqualTo(expectedIp));
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(IPAddress)));
// Provider-specific type (NpgsqlInet)
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlInet)));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(new NpgsqlInet(expectedIp)));
Assert.That(reader.GetFieldValue<NpgsqlInet>(i), Is.EqualTo(new NpgsqlInet(expectedIp)));
Assert.That(reader.GetString(i), Is.EqualTo(new NpgsqlInet(expectedIp).ToString()));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlInet)));
}
reader.Dispose();
cmd.Dispose();
}
public void ReadMoney()
{
var cmd = new NpgsqlCommand("SELECT '12345.12'::MONEY, '-10.5'::MONEY", Conn);
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader.GetDecimal(0), Is.EqualTo(12345.12m));
Assert.That(reader.GetValue(0), Is.EqualTo(12345.12m));
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(12345.12m));
Assert.That(reader.GetDecimal(1), Is.EqualTo(-10.5m));
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(decimal)));
reader.Close();
cmd.Dispose();
}
public void RoundtripLarge()
{
var expected = new byte[Conn.BufferSize + 100];
for (int i = 0; i < expected.Length; i++)
expected[i] = 8;
var cmd = new NpgsqlCommand("SELECT @p::BYTEA", Conn);
cmd.Parameters.Add(new NpgsqlParameter("p", NpgsqlDbType.Bytea) { Value = expected });
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(byte[])));
Assert.That(reader.GetFieldValue<byte[]>(0), Is.EqualTo(expected));
reader.Close();
cmd.Dispose();
}
public void LongTwoDimensional()
{
var len = Conn.BufferSize / 2 + 100;
var expected = new int[2, len];
for (var i = 0; i < len; i++)
expected[0,i] = i;
for (var i = 0; i < len; i++)
expected[1,i] = i;
var cmd = new NpgsqlCommand("SELECT @p", Conn);
var p = new NpgsqlParameter { ParameterName = "p", Value = expected };
cmd.Parameters.Add(p);
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(expected));
cmd.Dispose();
}
public static NpgsqlDataReaderWithConection SELECT(string query)
{
NpgsqlConnection conexion = new NpgsqlConnection(DBConector.conectionString);
conexion.Open();
//Un select
NpgsqlCommand comando = new NpgsqlCommand(query, conexion);
NpgsqlDataReader resultQuery = comando.ExecuteReader();
NpgsqlDataReaderWithConection resultado = new NpgsqlDataReaderWithConection();
resultado.conexion = conexion;
resultado.lector = resultQuery;
comando.Dispose();
return resultado;
}
/// <summary>
/// This function will walk all the current tables and assign a number to each word as per its root word.
/// </summary>
static void AssignRootNumbers()
{
Console.WriteLine("Updating database with frequency counts.");
int rivesNum = 1;
Dictionary<string, int> rootWords = new Dictionary<string, int>();
NpgsqlConnection conn = new NpgsqlConnection(connection);
if (conn != null)
{
conn.Open();
string sql = "select distinct root from greek order by root";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader reader = cmd.ExecuteReader();
Console.WriteLine("Generating unique number for each unqiue root word.");
while (reader.Read())
rootWords[reader["root"].ToString()] = rivesNum++;
reader.Close();
cmd.Dispose();
Console.WriteLine("Number of unique NT words = " + rootWords.Count);
int hapax = 0;
// For each root word in our Dictionary, update the database with the number of times
// the root word occurs.
foreach(var pair in rootWords)
{
int root_num = pair.Value;
sql = "update greek set root_num="+ root_num + " where root='" + pair.Key + "'";
cmd = new NpgsqlCommand(sql, conn);
int frequence = cmd.ExecuteNonQuery();
if (frequence == 1)
hapax++;
if (frequence <= 0)
Console.WriteLine("Error writing out data for " + pair.Key);
cmd.Dispose();
sql = "update greek set root_freq_nt=" + frequence + " where root='" + pair.Key + "'";
cmd = new NpgsqlCommand(sql, conn);
int written = cmd.ExecuteNonQuery();
if (written != frequence)
Console.WriteLine("Error saving frequency count for " + pair.Key);
cmd.Dispose();
}
Console.WriteLine("Updated databae.");
Console.WriteLine("Number of NT words that are hapax lagomenon: " + hapax);
}
else
Console.WriteLine("Could not connect to Postgres database");
}
public void GlobalRegistration()
{
NpgsqlConnection.RegisterEnumGlobally<Mood>();
var myconn = new NpgsqlConnection(ConnectionString);
myconn.Open();
const Mood expected = Mood.Ok;
var cmd = new NpgsqlCommand("SELECT @p::MOOD", myconn);
var p = new NpgsqlParameter { ParameterName = "p", Value = expected };
cmd.Parameters.Add(p);
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(Mood)));
Assert.That(reader.GetFieldValue<Mood>(0), Is.EqualTo(expected));
Assert.That(reader.GetValue(0), Is.EqualTo(expected));
reader.Close();
cmd.Dispose();
myconn.Dispose();
}
public void Multiqueries(bool[] queries)
{
ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var sb = new StringBuilder();
foreach (var query in queries)
sb.Append(query ? "SELECT 1;" : "UPDATE data SET name='yo' WHERE 1=0;");
var sql = sb.ToString();
foreach (var prepare in new[] { false, true }) {
var cmd = new NpgsqlCommand(sql, Conn);
if (prepare)
cmd.Prepare();
var reader = cmd.ExecuteReader();
var numResultSets = queries.Count(q => q);
for (var i = 0; i < numResultSets; i++) {
Assert.That(reader.Read(), Is.True);
Assert.That(reader[0], Is.EqualTo(1));
Assert.That(reader.NextResult(), Is.EqualTo(i != numResultSets - 1));
}
reader.Close();
cmd.Dispose();
}
}
public void LateRegistration()
{
Conn.RegisterEnum<Mood>("mood");
const Mood expected = Mood.Ok;
var cmd = new NpgsqlCommand("SELECT @p1::MOOD, @p2::MOOD", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Enum) { EnumType = typeof(Mood), Value = expected };
var p2 = new NpgsqlParameter { ParameterName = "p2", Value = expected };
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof (Mood)));
Assert.That(reader.GetFieldValue<Mood>(i), Is.EqualTo(expected));
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
}
reader.Close();
cmd.Dispose();
}
public void FieldCount()
{
var cmd = new NpgsqlCommand("SELECT 1; SELECT 2,3", Conn);
var reader = cmd.ExecuteReader();
Assert.That(reader.FieldCount, Is.EqualTo(1));
Assert.That(reader.Read(), Is.True);
Assert.That(reader.FieldCount, Is.EqualTo(1));
Assert.That(reader.Read(), Is.False);
Assert.That(reader.FieldCount, Is.EqualTo(1));
Assert.That(reader.NextResult(), Is.True);
Assert.That(reader.FieldCount, Is.EqualTo(2));
Assert.That(reader.NextResult(), Is.False);
Assert.That(reader.FieldCount, Is.EqualTo(0));
reader.Close();
cmd.CommandText = "INSERT INTO data (field_int4) VALUES (1)";
reader = cmd.ExecuteReader();
// Note MSDN docs that seem to say we should case -1 in this case: http://msdn.microsoft.com/en-us/library/system.data.idatarecord.fieldcount(v=vs.110).aspx
// But SqlClient returns 0
Assert.That(() => reader.FieldCount, Is.EqualTo(0));
cmd.Dispose();
}
public void Ints()
{
var expected = new[] { 1, 5, 9 };
var cmd = new NpgsqlCommand("SELECT @p1, @p2", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Array | NpgsqlDbType.Integer);
var p2 = new NpgsqlParameter { ParameterName = "p2", Value = expected };
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
p1.Value = expected;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<int[]>(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(Array)));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(Array)));
}
cmd.Dispose();
}
public void Range()
{
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Range | NpgsqlDbType.Integer) { Value = NpgsqlRange<int>.Empty() };
var p2 = new NpgsqlParameter { ParameterName = "p2", Value = new NpgsqlRange<int>(1, 10) };
var p3 = new NpgsqlParameter { ParameterName = "p3", Value = new NpgsqlRange<int>(1, false, 10, false) };
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = new NpgsqlRange<int>(0, false, true, 10, false, false) };
Assert.That(p2.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Range | NpgsqlDbType.Integer));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader[0].ToString(), Is.EqualTo("empty"));
Assert.That(reader[1].ToString(), Is.EqualTo("[1,11)"));
Assert.That(reader[2].ToString(), Is.EqualTo("[2,10)"));
Assert.That(reader[3].ToString(), Is.EqualTo("(,10)"));
reader.Dispose();
cmd.Dispose();
}
public void deleteAngebot(Angebot a)
{
buildconnection();
NpgsqlCommand comm = null;
try
{
string sql = "Delete from angebote where angebotid = @angebotid";
comm = new NpgsqlCommand(sql, conn);
comm.Parameters.AddWithValue("@angebotid", a.Angebotid);
comm.Prepare();
comm.ExecuteNonQuery();
a.Status = ObjectStates.Deleted;
}
catch (NpgsqlException exp)
{
throw new DALException("DAL: Angebot konnte nicht gelöscht werden!", exp);
}
finally
{
comm.Dispose();
conn.Close();
}
}
public void Int16()
{
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4, @p5", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Smallint);
var p2 = new NpgsqlParameter("p2", DbType.Int16);
var p3 = new NpgsqlParameter("p3", DbType.Byte);
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = (short)8 };
var p5 = new NpgsqlParameter { ParameterName = "p5", Value = (byte)8 };
Assert.That(p4.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(p4.DbType, Is.EqualTo(DbType.Int16));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
p1.Value = p2.Value = p3.Value = (long)8;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetInt16(i), Is.EqualTo(8));
Assert.That(reader.GetInt32(i), Is.EqualTo(8));
Assert.That(reader.GetInt64(i), Is.EqualTo(8));
Assert.That(reader.GetByte(i), Is.EqualTo(8));
Assert.That(reader.GetFloat(i), Is.EqualTo(8.0f));
Assert.That(reader.GetDouble(i), Is.EqualTo(8.0d));
Assert.That(reader.GetDecimal(i), Is.EqualTo(8.0m));
Assert.That(reader.GetValue(i), Is.EqualTo(8));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(8));
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(short)));
Assert.That(reader.GetDataTypeName(i), Is.EqualTo("int2"));
}
reader.Dispose();
cmd.Dispose();
}
public void Roundtrip(
[Values(
"1011011000101111010110101101011011", // 34 bits
"10110110",
""
)]
string bits
)
{
var expected = new BitArray(bits.Length);
for (var i = 0; i < bits.Length; i++)
expected[i] = bits[i] == '1';
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Varbit);
var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Bit);
var p3 = new NpgsqlParameter("p3", NpgsqlDbType.Varbit) { Value = bits };
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expected };
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
p1.Value = p2.Value = expected;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetFieldValue<BitArray>(i), Is.EqualTo(expected));
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
Assert.That(() => reader.GetFieldValue<bool>(i), Throws.Exception.TypeOf<InvalidCastException>());
}
reader.Dispose();
cmd.Dispose();
}
protected override void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
if (insert_command != null)
{
insert_command.Dispose();
}
if (update_command != null)
{
update_command.Dispose();
}
if (delete_command != null)
{
delete_command.Dispose();
}
data_adapter.RowUpdating -= new NpgsqlRowUpdatingEventHandler(OnRowUpdating);
}
}
base.Dispose(disposing);
}
public void GetChars([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
// TODO: This is too small to actually test any interesting sequential behavior
const string str = "ABCDE";
var expected = str.ToCharArray();
var actual = new char[expected.Length];
ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
ExecuteNonQuery(String.Format(@"INSERT INTO data (name) VALUES ('{0}')", str));
const string queryText = @"SELECT name, 3, name, 4, name, name, name FROM data";
var cmd = new NpgsqlCommand(queryText, Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
Assert.That(reader.GetChars(0, 0, actual, 0, 2), Is.EqualTo(2));
Assert.That(actual[0], Is.EqualTo(expected[0]));
Assert.That(actual[1], Is.EqualTo(expected[1]));
Assert.That(reader.GetChars(0, 0, null, 0, 0), Is.EqualTo(expected.Length), "Bad column length");
// Note: Unlike with bytea, finding out the length of the column consumes it (variable-width
// UTF8 encoding)
Assert.That(reader.GetChars(2, 0, actual, 0, 2), Is.EqualTo(2));
if (IsSequential(behavior))
Assert.That(() => reader.GetChars(2, 0, actual, 4, 1), Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
else
{
Assert.That(reader.GetChars(2, 0, actual, 4, 1), Is.EqualTo(1));
Assert.That(actual[4], Is.EqualTo(expected[0]));
}
Assert.That(reader.GetChars(2, 2, actual, 2, 3), Is.EqualTo(3));
Assert.That(actual, Is.EqualTo(expected));
//Assert.That(reader.GetChars(2, 0, null, 0, 0), Is.EqualTo(expected.Length), "Bad column length");
Assert.That(() => reader.GetChars(3, 0, null, 0, 0), Throws.Exception.TypeOf<InvalidCastException>(), "GetChars on non-text");
Assert.That(() => reader.GetChars(3, 0, actual, 0, 1), Throws.Exception.TypeOf<InvalidCastException>(), "GetChars on non-text");
Assert.That(reader.GetInt32(3), Is.EqualTo(4));
reader.GetChars(4, 0, actual, 0, 2);
// Jump to another column from the middle of the column
reader.GetChars(5, 0, actual, 0, 2);
Assert.That(reader.GetChars(5, expected.Length - 1, actual, 0, 2), Is.EqualTo(1), "Length greater than data length");
Assert.That(actual[0], Is.EqualTo(expected[expected.Length - 1]), "Length greater than data length");
Assert.That(() => reader.GetChars(5, 0, actual, 0, actual.Length + 1), Throws.Exception.TypeOf<IndexOutOfRangeException>(), "Length great than output buffer length");
// Close in the middle of a column
reader.GetChars(6, 0, actual, 0, 2);
reader.Close();
cmd.Dispose();
}
public void AliasedPgTypes(string typename)
{
const string expected = "some_text";
var cmd = new NpgsqlCommand(String.Format("SELECT '{0}'::{1}", expected, typename), Conn);
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader.GetString(0), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(0), Is.EqualTo(expected.ToCharArray()));
reader.Dispose();
cmd.Dispose();
}
public void Null([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
var buf = new char[8];
var cmd = new NpgsqlCommand("SELECT NULL::TEXT", Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
Assert.That(reader.IsDBNull(0), Is.True);
Assert.That(() => reader.GetChars(0, 0, buf, 0, 1), Throws.Exception, "GetChars");
Assert.That(() => reader.GetTextReader(0), Throws.Exception, "GetTextReader");
Assert.That(() => reader.GetChars(0, 0, null, 0, 0), Throws.Exception, "GetChars with null buffer");
reader.Close();
cmd.Dispose();
}
public void TextReaderDisposeOnNonSequentialRow()
{
var cmd = new NpgsqlCommand(@"SELECT 'some_text', 'some_text'", Conn);
var reader = cmd.ExecuteReader();
reader.Read();
var tr1 = reader.GetTextReader(0);
var tr2 = reader.GetTextReader(0);
reader.Read();
Assert.That(() => tr1.Peek(), Throws.Exception.TypeOf<ObjectDisposedException>());
Assert.That(() => tr2.Peek(), Throws.Exception.TypeOf<ObjectDisposedException>());
reader.Close();
cmd.Dispose();
}
public void TextReaderDisposeOnSequentialColumn()
{
var cmd = new NpgsqlCommand(@"SELECT 'some_text', 'some_text'", Conn);
var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
reader.Read();
var textReader = reader.GetTextReader(0);
// ReSharper disable once UnusedVariable
var v = reader.GetValue(1);
Assert.That(() => textReader.Peek(), Throws.Exception.TypeOf<ObjectDisposedException>());
reader.Close();
cmd.Dispose();
}
public void GetTextReader([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
// TODO: This is too small to actually test any interesting sequential behavior
const string str = "ABCDE";
var expected = str.ToCharArray();
var actual = new char[expected.Length];
//ExecuteNonQuery(String.Format(@"INSERT INTO data (field_text) VALUES ('{0}')", str));
string queryText = string.Format(@"SELECT '{0}', 'foo'", str);
var cmd = new NpgsqlCommand(queryText, Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
var textReader = reader.GetTextReader(0);
textReader.Read(actual, 0, 2);
Assert.That(actual[0], Is.EqualTo(expected[0]));
Assert.That(actual[1], Is.EqualTo(expected[1]));
if (behavior == CommandBehavior.Default) {
var textReader2 = reader.GetTextReader(0);
var actual2 = new char[2];
textReader2.Read(actual2, 0, 2);
Assert.That(actual2[0], Is.EqualTo(expected[0]));
Assert.That(actual2[1], Is.EqualTo(expected[1]));
} else {
Assert.That(() => reader.GetTextReader(0), Throws.Exception.TypeOf<InvalidOperationException>(), "Sequential text reader twice on same column");
}
textReader.Read(actual, 2, 1);
Assert.That(actual[2], Is.EqualTo(expected[2]));
textReader.Close();
if (IsSequential(behavior))
Assert.That(() => reader.GetChars(0, 0, actual, 4, 1), Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
else
{
Assert.That(reader.GetChars(0, 0, actual, 4, 1), Is.EqualTo(1));
Assert.That(actual[4], Is.EqualTo(expected[0]));
}
Assert.That(reader.GetString(1), Is.EqualTo("foo"));
reader.Close();
cmd.Dispose();
}