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

Answer 3

That's like asking "is there a generic way to get related data" in databases. The answer is of course no - the only "generic way" is to have a data layer that hides the implementation details of your particular data source and queries it appropriately.

If you are really supporting and accessing many different types of databases without a Stategy design pattern or similar approach I would be quite surprised.

That being said, the best approach is something like this bit of code:

bool exists;

try
{
    // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
    var cmd = new OdbcCommand(
      "select case when exists((select * from information_schema.tables where table_name = '" + tableName + "')) then 1 else 0 end");

    exists = (int)cmd.ExecuteScalar() == 1;
}
catch
{
    try
    {
        // Other RDBMS.  Graceful degradation
        exists = true;
        var cmdOthers = new OdbcCommand("select 1 from " + tableName + " where 1 = 0");
        cmdOthers.ExecuteNonQuery();
    }
    catch
    {
        exists = false;
    }
}

Source: Check if a SQL table exists

Answer 4

You can do something like this:

string strCheck = "SHOW TABLES LIKE \'tableName\'";
                cmd = new MySqlCommand(strCheck, connection);
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                cmd.Prepare();
                var reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {                             
                  Console.WriteLine("Table Exist!");
                }
                else (reader.HasRows)
                {                             
                  Console.WriteLine("Table Exist!");
                }