Fastest Way To Delete Table (If Exists)

Question

I am using C# to connect to an access database and checking if a table exists in that database. This is the syntax I am using, but for a few databases that have lots of tables in them it takes quite some time to execute. Is their a faster way to do this as speed is the most important thing for this procedure?

string[] tableNames = new string[4] { "One", "Two", "Three", "Four" };
for (int q = tableNames.GetLowerBound(0); q <= tableNames.GetUpperBound(0); q++)
{
  foreach (DAO.TableDef tabledef in dd.TableDefs)
  {
    string strtable = tableNames[q];
    if (tabledef.Name == strtable) { found = true; }
    if (found) { dd.TableDefs.Delete(strtable); } 
  }
}

For any future travelers who might stumble upon this, this was my final syntax that I used --- Exponentially faster!!!

Last EDIT --- I changed the Execute statement to be encapsulated in a try/catch block as if the table name that is listed in the array does not actually exist it will throw an error.

System.Data.OleDb.OleDbConnection oleconn = new   OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + PathToDatabase" + ".mdb;");
oleconn.Open();
string[] tableNames = new string[4] { "One", "Two", "Three", "Four" };
for (int q = tableNames.GetLowerBound(0); q <= tableNames.GetUpperBound(0); q++)
{
  System.Data.OleDb.OleDbCommand cmd = new OleDbCommand("DROP TABLE " + tableNames[q], oleconn);
  try { cmd.ExecuteNonQuery(); }
  catch {}
}
oleconn.Close();

simply run `DROP TABLE [Tablename]` it will delete if exist otherwise does nothing — Franck, Apr 06 '15 at 13:41
why can't you write a simple query that checks if the sysobject exist — MethodMan, Apr 06 '15 at 13:44
@MethodMan the drop table already check if the entity exist so checking for it's existence before is double checking, hence not faster. Unless he plan to do something special if the table do or not exist the check is just slowing down the process. — Franck, Apr 06 '15 at 13:47
I am aware of the @Franck but based on the unnecessary iteration is why I was asking that.. — MethodMan, Apr 06 '15 at 13:49
Are you aware that you don't reset the variable found to false? After the first match any subsequent table will be deleted. — Steve, Apr 06 '15 at 13:53
@Steve you are correct, that was a good catch their. Thank you! — Big Pimpin, Apr 06 '15 at 14:13
Why did you accept an answer that uses T-SQL code and will not work with Access SQL? — Gord Thompson, Apr 06 '15 at 14:47
@GordThompson just deselected. I initially hit that one as that was the route I was going to go. Then it was compile error after compile error so I went another route. Unselected. — Big Pimpin, Apr 06 '15 at 14:49

score 2 · Answer 1 · answered Apr 06 '15 at 14:30

2

Reverse the loops (pseudo code):

foreach (DAO.TableDef tabledef in dd.TableDefs)
{
  if (tabledef.Name in tablearray )
  { 
    dd.TableDefs.Delete(strtable); 
  } 
}

answered Apr 06 '15 at 14:30

Gustav

53,498
7
29
55

score 1 · Answer 2 · answered Apr 06 '15 at 13:44

1

I think instead of checking in C# application run following query with your ado.net.

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
     DROP TABLE 'mytablename'

Because it will load meta data when you use ado.net instead of that execute simple query with execute non query.

answered Apr 06 '15 at 13:44

Jalpesh Vadgama

13,653
19
72
94

2

I was not aware that OBJECT_ID and that syntax could be used against an MS-Access database. But given the fact that the poster accepted your answer then I am probably wrong. – Steve Apr 06 '15 at 13:59
2

(cc: @Steve ) This is T-SQL code. It will not work with Access SQL. – Gord Thompson Apr 06 '15 at 14:47
@GordThompson Uhm as suspected – Steve Apr 06 '15 at 14:55

score -1 · Answer 3 · edited May 23 '17 at 11:58

-1

You can use Drop Table for deleting table.. and its syntax is

   if exists (select * from login where name = 'prishu' and pass='prishu')
    drop table login

for more information use this link

check this link also

edited May 23 '17 at 11:58

Community

1
1

answered Apr 06 '15 at 13:49

Pradnya Bolli

1,915
1
19
37

We are talking of an MS-Access database. Did you try that syntax? – Steve Apr 06 '15 at 14:11

Fastest Way To Delete Table (If Exists)

3 Answers3