Check if Table Exist and create tables if they don't
I'm needing a little help figuring out the best way to check if a table exist in an access DB and then set a flag to create the Table. I want to set this up with reusable code.
right now i am setting the commandtext with the SQL query that I want to convert to varaibles but want to figure out how I want to do this first because it is most likely part of the overall solution for this.
I'm opening the Database reading the GetSchema and reading each table name and have an array of my tables that the program is going to create, I can compare these but i'm getting lost on how to keep three array's in sync with out putting in indivual checks for each table. yes the code below only has 5 tables but this application is going to grow in future versions and i don't want to have to go back and recode this or add checks for each table.
// array of tables that will be created
string[] NATables = new string[5];
NATables[0] = "Group Policy";
NATables[1] = "GPO Computer Settings";
NATables[2] = "GPO User Settings";
NATables[3] = "GPO Links";
NATables[4] = "GPO Delegation";
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Test.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
OleDbCommand cmd = con.CreateCommand();
//check for tables before creating
//Get Tables from Database
DataTable dt = con.GetSchema("tables");
//close Connection to database
con.Close();
// get all rows from the table
DataRow[] rows = dt.Select();
// loop through the records searching for tables and set flag for tables that need to be created
for (int i =0; i < rows.Length; i++ )
{
string r = rows[i]["TABLE_NAME"].ToString();
for (int j = 0; j < NATables.Length; j++)
{
if (r == NATables[j])
{
//don't create table
}
else
{
//set create table flag
}
}
}
// Run SQL commands to create tables
con.Open();
cmd.CommandText = "CREATE TABLE [Group Policy]([GPO GUID] Text(50) NOT NULL Unique PRIMARY KEY,[GPO NAME] TEXT(50) NOT NULL,[GPO Domain] TEXT(50) NOT NULL,Created datetime NOT NULL,Modified datetime NOT NULL,Status TEXT(10) NOT NULL)";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}