Check if table exists in c#

Question

I want to read data from a table whose name is supplied by a user. So before actually starting to read data, I want to check if the database exists or not.

I have seen several pieces of code on the NET which claim to do this. However, they all seem to be work only for SQL server, or for mysql, or some other implementation. Is there not a generic way to do this?

(I am already seperately checking if I can connect to the supplied database, so I'm fairly certain that a connection can be opened to the database.)

Answer 1

You cannot do this in a cross-database way. Generally DDL (that is, the code for creating tables, indexes and so on) is completely different from database to database and so the logic for checking whether tables exist is also different.

I would say the simplest answer, though, would simply be something like:

SELECT * FROM <table> WHERE 1 = 0

If that query gives an error, then the table doesn't exist. If it works (though it'll return 0 rows) then the table exists.

Be very careful with what you let the user input, though. What's to stop him from from specifying "sysusers" as the table name (in SQL Server, that'll be the list of all database users)

Answer 2

You can use the DbConnection.GetSchema family of methods to retreive metadata about the database. It will return a DataTable with schema objects. The exact object types and restriction values may vary from vendor to vendor, but I'm sure you can set up your check for a specific table in a way that will work in most databases.

Here's an example of using GetSchema that will print the name and owner of every table that is owned by "schema name" and called "table name". This is tested against oracle.

static void Main(string[] args)
{
    string providerName = @"System.Data.OracleClient";
    string connectionString = @"...";

    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;
        connection.Open();
        DataTable schemaDataTable = connection.GetSchema("Tables", new string[] { "schema name", "table name" });
        foreach (DataColumn column in schemaDataTable.Columns)
        {
            Console.Write(column.ColumnName + "\t");
        }
        Console.WriteLine();
        foreach (DataRow row in schemaDataTable.Rows)
        {
            foreach (object value in row.ItemArray)
            {
                Console.Write(value.ToString() + "\t");
            }
            Console.WriteLine();
        }
    }
}