TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Rob
NA
1
0
Check if Table Exist and create tables if they don't
Oct 14 2008 4:29 PM
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());
}
Reply
Answers (
1
)
WTF does this teacher want me 2 do can someone explain it to me pls thx
visual studio cannot start debugging because the debug target is missing