Check for MS Access database table if not exist create it

How do you programmatically check for MS Access data开发者_运维百科base table, if not exist then create it?

You could iterate though the table names to check for a specific table. See the below code to get the table names.

        string connectionstring = "Your connection string";
        string[] restrictionValues = new string[4]{null,null,null,"TABLE"};
        OleDbConnection oleDbCon = new OleDbConnection(connectionString);
        List<string> tableNames = new List<string>();

        try
        {
            oleDbCon.Open();
            DataTable schemaInformation = oleDbCon.GetSchema("Tables", restrictionValues);

            foreach (DataRow row in schemaInformation.Rows)
            {
               tableNames.Add(row.ItemArray[2].ToString());
            }
        }
        finally
        {
            oleDbCon.Close();
        }           

To check if a table exists you can extend DbConnection like this:

public static class DbConnectionExtensions
{
    public static bool TableExists(this DbConnection conn, string table)
    {
        conn.open();
        var exists = conn.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
        conn.close();
        return exists;
    }
}

Then you can call TableExists in any derived class like OleDbConnection, SQLiteConnection or SqlConnection.

Simply execute following code if table will exist it will return error other wise it will create a new one:

try
{
        OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + frmMain.strFilePath + "\\ConfigStructure.mdb");
        myConnection.Open();
        OleDbCommand myCommand = new OleDbCommand();
        myCommand.Connection = myConnection;
        myCommand.CommandText = "CREATE TABLE <yourtable name>(<columns>)";
        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();
}
catch(OleDbException e)
{  
    if(e.ErrorCode == 3010 || e.ErrorCode == 3012)
    // if error then table exist do processing as required
}

Those error codes are returned if a table already exists - check here for all.

an easy way to do this is

public bool CheckTableExistance(string TableName)
    {
        // Variable to return that defines if the table exists or not.
        bool TableExists = false;

        // Try the database logic
        try
        {
            // Make the Database Connection
            ConnectAt();

            // Get the datatable information
            DataTable dt = _cnn.GetSchema("Tables");

            // Loop throw the rows in the datatable
            foreach (DataRow row in dt.Rows)
            {
                // If we have a table name match, make our return true
                // and break the looop
                if (row.ItemArray[2].ToString() == TableName)
                {
                    TableExists = true;
                    break;
                }
            }

            //close database connections!
            Disconnect();
            return TableExists;
        }
        catch (Exception e)
        {
            // Handle your ERRORS!
            return false;
        }
    }

For completeness sake, I'll point out that a while back I posted 4 different ways of coding up a TableExists() function within Access. The version that runs a SQL SELECT on MSysObjects would work from outside Access, though in some contexts, you might get a security error (because you're not allowed to access the Jet/ACE system tables).