Beispiel #1
1
        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();
        }
Beispiel #2
0
        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();
        }
Beispiel #3
0
        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();
        }
Beispiel #4
0
 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(); }
 }
Beispiel #5
0
        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();
        }
Beispiel #6
0
        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();
        }
Beispiel #7
0
 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();
 }
Beispiel #8
0
 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();
 }
Beispiel #9
0
        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;
        }
Beispiel #10
0
        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();
        }
Beispiel #11
0
 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();
 }
Beispiel #12
0
 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();
 }
Beispiel #13
0
 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();
 }
Beispiel #14
0
        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;
        }
Beispiel #15
0
Datei: etl.cs Projekt: srives/ETL
        /// <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");
        }
Beispiel #16
0
        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();
        }
Beispiel #17
0
 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();
     }
 }
Beispiel #18
0
        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();
        }
Beispiel #19
0
        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();
        }
Beispiel #20
0
        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();
        }
Beispiel #21
0
        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();
        }
Beispiel #22
0
 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();
     }
 }
Beispiel #23
0
        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();
        }
Beispiel #24
0
        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();
        }
Beispiel #25
0
        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();
        }
Beispiel #26
0
 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();
 }
Beispiel #27
0
 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();
 }
Beispiel #28
0
 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();
 }
Beispiel #29
0
 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();
 }
Beispiel #30
0
        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();
        }