Check if PostgreSQL table exists with Npgsql

Question

Until now I don't see any difference in using Npgsql driver instead of ODBC when working with PostgreSQL database on windows. But now I find one.

I have such code to check if table exists with ODBC:

Public Function dbTableExists(ByVal dbTable As String, ByVal dbName As String) As Boolean

    Dim retval As Boolean = False
    Dim nCon As New OdbcConnection
    Dim btCommand As OdbcCommand = Nothing
    nCon.ConnectionString = "Dsn=" + dbDsn + _
                            ";database=" + dbName & _
                            ";server=" + dbServer + _
                            ";port=" + dbPort + _
                            ";uid=" + dbUser + _
                            ";pwd=" + dbPass
    Try
        nCon.Open()
        btCommand = New OdbcCommand("SELECT 1 FROM pg_tables WHERE tablename='" & dbTable + "'", nCon)
        retval = CBool(btCommand.ExecuteNonQuery())

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical)
        retval = False
    End Try

    Return retval
End Function

With this code I get True if specific table exists in determined database or False otherwise.
When I try to use Npgsql instead of ODBC function is very similar:

Public Function tExists(ByVal dbTable As String, ByVal dbName As String) As Boolean

    Dim retval As Boolean = False
    Dim btCommand As NpgsqlCommand = Nothing
    Dim nCon As New NpgsqlConnection(String.Format( _
                    "Server={0};Port={1};User Id={2};Password={3};Database={4};", _
                    dbServer, dbPort, dbUser, dbPass, dbName))
    Try
        nCon.Open()
        btCommand = New NpgsqlCommand("SELECT 1 FROM pg_tables WHERE tablename='" & dbTable + "'", nCon)
        retval = CBool(btCommand.ExecuteNonQuery())

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical)
        retval = False
    End Try

    Return retval
End Function

But this won't work as expected.
I always get True as result no matter if specific table is present or not.

Any idea how to get Npgsql function to work?

Why do you use executeNonQuery()? You are running a query, so shouldn't you be using executeQuery() (or something similar - I don't know .Net) — user330315
– user330315, Commented Feb 7, 2013 at 9:10
Hello "name". Your logic is excellent as usually. For some reason MS ODBC return's a value of query with executeNonQuery but Npgsql unexpectedly don't. I didn't know for that difference till now. But here is ExecuteScalar which return first row or value of a (such kind of) query. And such, with ExecuteScalar I get expected result of showed query and get my Npgsql function to work. Thank you for pointing me to right direction. — Wine Too
– Wine Too, Commented Feb 7, 2013 at 9:31
Could you include that as an example? I would be happy to upvote. — Chris Travers
– Chris Travers, Commented Apr 24, 2013 at 6:15

Tobia Zambon · Accepted Answer · 2013-07-15 09:32:20Z

I don't know if you don't want/can't change your query but I suggest you to use the information_schema table instead of using pg_tables. I personally use this method:

 public override bool TableExists(string tableName)
    {
        string sql = "SELECT * FROM information_schema.tables WHERE table_name = '" + tableName + "'";
        using (var con = new NpgsqlConnection(preparedConnectionString))
        {
            using (var cmd = new NpgsqlCommand(sql))
            {
                if (cmd.Connection == null)
                    cmd.Connection = con;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();

                lock (cmd)
                {
                    using (NpgsqlDataReader rdr = cmd.ExecuteReader())
                    {
                        try
                        {
                            if (rdr != null && rdr.HasRows)
                                return true;
                            return false;
                        }
                        catch (Exception)
                        {
                            return false;
                        }
                    }
                }
            }
        }
    }

(Don't care about injection or similar)

Collectives™ on Stack Overflow

Check if PostgreSQL table exists with Npgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related