C# & MS Access - SQL for Create Table, Column and add values at the start of the app

Question

I am trying to develop a project with C# and a MS Access database, the project has a table named Employee. I created all the DB, table, attributes on MS Access but I want to check the database if exist or not and check the tables if exist or not and also check the attributes if exist or not and create the DB, table and add the values if lack of anyone.

So I created a method and try to check this situations. But, in my code there is some method to get the values to datagridview from the database, if I delete my database file, I get errors in the datagridview or any of this type can not find the db. I called my method at the very top.

Table name --> Employee - columns (Name, Surname). 
Second Table --> Department - column (Department_Name) This table has to come with two values; Engineering and Accountant.

Calling the method to check:

public Form1()
{
    checkDB();
    InitializeComponent();
}

My code to check the DB:

static void checkDB()
{
    try
    {
        OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= Employee.accdb");
        myConnection.Open();

        OleDbCommand myCommand = new OleDbCommand();
        myCommand.Connection = myConnection;

        myCommand.CommandText = "CREATE DATABASE IF NOT EXIST [Employee] + 
                                 CREATE TABLE IF NOT EXIST 'Employee' [ID] Counter Primary Key,
                        [FirstName] Text, [LastName] Text +  CREATE TABLE IF NOT EXIST 'Department' 
                        [ID] Counter Primary Key, [Department_Name] Text + insert if not exist into 
                        Department(Department_Name) values('Engineer , Accountant ') ";
        // Added new lines to read it easily

        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();
    }
    catch(OleDbException e)
    {  
        // err.message
    }
}

user_3527297 · Answer

An Access database file may be well suited for this and doesn't require any purchased license.

The, by a factor, simplest method, is to include an empty database file in your project. Then, if none is present, copy this to the user's folder and establish the connection.

user_2144390 · Answer

Access SQL does not support CREATE TABLE IF NOT EXISTS so we need to check for the existence of the table ourselves:

string tableName = "team";
DataTable dt = myConnection.GetSchema("Tables", new string[] { null, null, tableName });
if (dt.Rows.Count > 0)
{
    Console.WriteLine("Table already exists.");
} 
else
{
    Console.WriteLine("We need to CREATE TABLE");
}

Also, as mentioned in the comments to the question, Access SQL does not support CREATE DATABASE at all, so we need to use ADOX

var myConnection = new OleDbConnection(connectionString);
try
{
    myConnection.Open();
    Console.WriteLine("Database already existed.");
}
catch
{
    var cat = new ADOX.Catalog();
    cat.Create(connectionString);
    Console.WriteLine("Database created.");
    myConnection.Open();
}

C# & MS Access - SQL for Create Table, Column and add values at the start of the app

2 Answers