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