How do you Drop a database after testing it does not exist

[kerrywales]

I am trying to write code that:
1. Tests if a DB Exists
2. If the DB exists then offer the option for the user to drop the database, and if "Yes" Drop the Db.

I test using

Code: Select all

try
{using (PgSqlConnection conn = new PgSqlConnection())
                    {
                        conn.ConnectionString = string.Format("... ...",
                            teDestinationHost.Text, teDestinationPort.Text, teDestinationDatabase.Text);
                        conn.Open();
                        conn.Close();
                        DbExists = true;
                    }
} ...

if (DbExists)
                    {
                        ...
using (PgSqlConnection conn = new PgSqlConnection())
            {
                conn.ConnectionString = string.Format("... ...",
                    teDestinationHost.Text, teDestinationPort.Text);
                conn.Open();
                string cmdstring = "DROP DATABASE IF EXISTS \"" + teDestinationDatabase.Text.Trim() + "\";";

                PgSqlCommand cmd = new PgSqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = cmdstring;
                try
                {
                    cmd.ExecuteNonQuery();
                    result = true;
                }
                catch(Exception ex)
                {
                    MessageBox.Show("Failed to drop Database\n"+ex.Message);
                };
            }

It never drops the database. The error says someone is using it. The only "using" of the DB is in the connection to test it exists.

I have obviously misunderstood the constructs and even though I close the connection pg still thinks it is active.

Can anyone give me an alternative approach.

Thanks

[Shalex]

Please try turning off pooling using the "Pooling=false;" connection string parameter: http://www.devart.com/dotconnect/postgr ... q.html#q52.