Check if C # table exists

Question

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

I saw a few snippets of code on NET that claim this. However, they all work only for the SQL server, or for mysql, or for some other implementation. Is there no general way to do this?

(I already check separately if I can connect to the supplied database, so I'm sure that the connection can be opened in the database.)

Answer 1

- . DDL ( , ..) , .

, - - :

SELECT * FROM <table> WHERE 1 = 0

, . ( 0 ), .

, . "sysusers" ( SQL Server, )

Answer 2

DbConnection.GetSchema . DataTable . , , , .

GetSchema, , " " " ". . ​​

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

" " . , , - " " , .

If you really support and access many different types of databases without a Stategy design template or a similar approach, I would be very surprised.

As the saying goes, 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 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!");
                }