C# NpgsqlCommand.Dispose方法代码示例

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