Checking for table existence

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

How can I check for existence of a certain table by name in and access
database and if the table exists find the number of records in it?

I guess I can access this information by setting up a dataset for the
database but I am wondering if there is a quick way to find this.

Thanks

Regards
 
Issuing a count select statement within a try catch loop would handle both?

It would give you the total records if the table exists and error and enter
the catch code if it doesn't?


Rob
 
Issuing a count select statement within a try catch loop would handle
both?

It would give you the total records if the table exists and error and
enter the catch code if it doesn't?
Click to expand...

A better way would be to use ADO.NET's GetSchema or query the metadata
table directly to check if a table exists.
 
John said:
How can I check for existence of a certain table by name in and access
database and if the table exists find the number of records in it?
Click to expand...

Rob Blackmore's approach will certainly work, but if you prefer to check for
the existence of the table without getting an exception if it doesn't exist
(which is definitely my preferred approach), here's a way that will work:

\\\
Dim tableInfo As DataTable
tableInfo = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
String() {Nothing, Nothing, "TableName", "TABLE"})
///

The GetOleDbSchemaTable method will return a datatable containing schema
information. This set of parameters will return information about a table
called "TableName" if one exists. Check the tableInfo.Rows.Count after
executing this; if it contains zero then the table doesn't exist, if it
contains one then it does exist.

Then if it exists, execute the SELECT COUNT statement as mentioned in Rob's
email.

HTH,
 
Hi John,

I never understand this kind of questions. You are making the program and
you don't know the name of the table which you (or a cowerker) would have
given a name.

How can this happen?

Cor
 
Cor said:
I never understand this kind of questions. You are making the program
and you don't know the name of the table which you (or a cowerker)
would have given a name. How can this happen?
Click to expand...

There are lots of systems that don't have fixed, known database structures.

The system I work on has dozens of different databases working for different
clients. We then offer a range of plug-ins to perform various tasks, each of
which will create tables for its own storage needs. The set of tables
therefore is not fixed, and it can be necessary for one part of the system
to check a table's presence because another part of the system may or may
not be present.
 
Hi Cor

I know the name of the table but it may or may not exist and I need to check
for the existence of the table.

Regards
 
John,

Then I would just create a simple SQL command with

"Select * from MyTable"

Execute that with an executescalar and when I get an error then I know that
the table does not exist.

Cor
 
John,

Then I would just create a simple SQL command with

"Select * from MyTable"

Execute that with an executescalar and when I get an error then I know that
the table does not exist.

Cor
Click to expand...

Cor's tip is the most basic and comprehensive one. When you call
something from a non-existed database, an error will occur and you
won't be able to access then you can use try-catch or kinda things to
display a custom error message.
 
No, that's an improper and silly way to check for a table existence.
What exception will you get? Will it be due to the lack of table existance
or due to some other error?
And don't forget that exceptions are for exceptional situations, not for
known situations: in your case, table might or might not exist. And if it
doesn't then this isn't an exception.
The proper way is to use GetSchema method - as (O)enone suggested and if
available or execute a DDL statement - almost each database will let you
query for a table using some sort of query statement.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


John,

Then I would just create a simple SQL command with

"Select * from MyTable"

Execute that with an executescalar and when I get an error then I know
that
the table does not exist.

Cor
Click to expand...

Cor's tip is the most basic and comprehensive one. When you call
something from a non-existed database, an error will occur and you
won't be able to access then you can use try-catch or kinda things to
display a custom error message.
 
No, that's an improper and silly way to check for a table existence.
What exception will you get? Will it be due to the lack of table existance
or due to some other error?
And don't forget that exceptions are for exceptional situations, not for
known situations: in your case, table might or might not exist. And if it
doesn't then this isn't an exception.
The proper way is to use GetSchema method - as (O)enone suggested and if
available or execute a DDL statement - almost each database will let you
query for a table using some sort of query statement.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & developmentwww.rthand.com
Blog:http://cs.rthand.com/blogs/blog_with_righthand/


John,
Click to expand...
Then I would just create a simple SQL command with
Click to expand...
"Select * from MyTable"
Click to expand...
Execute that with an executescalar and when I get an error then I know
that
the table does not exist.
Click to expand...
Cor
Click to expand...

Cor's tip is the most basic and comprehensive one. When you call
something from a non-existed database, an error will occur and you
won't be able to access then you can use try-catch or kinda things to
display a custom error message.
Click to expand...

Doesn't executescalar method return an exception if it fails to
retrieve data from a specific data table due to absence of it?
 
Miha,

There is no question what error you will get, there can only be one error on
this, the table does not exist.
(It can be as well a connection problem or a complete absence of the
database, however then you don"t get a schema either)

Cor
 
Cor Ligthert said:
Miha,

There is no question what error you will get, there can only be one error
on this, the table does not exist.
(It can be as well a connection problem or a complete absence of the
database, however then you don"t get a schema either)
Click to expand...

Yes, but how do you tell if the table exists or not? Was it a connection
problem? Was there a timeout? Or is just table missing?
There is also a performance hit doing it that way.
If you do it properly, you get a true/false or an exception telling you that
query failed.
 
Miha Markic said:
Care to explain?
Click to expand...

Guru seems to be a troll. However, he is not completely wrong. Even
exceptions are very often for known situations. Imagine a connection to a
database which often (90 %) cannot be established due to a bad network
connection. In this situation you would set up an exception handler because
I know the problem at design time:

\\\
Try
<Attempt to connect>
Catch ex As ...
...
End Try
///
 
Care to explain?
Click to expand...

See:
A not exceptional situation that is fully known and epected to occur in the
scenario given.

And also see:
"...you are utterly oblivious to the concept of using Try/Catch as a
versatile, robust, and flexible programming tool that has grand purposes
well beyond merely trapping the woe-begotten side-effects of your miserably
inept coding style..."

Care to dispute?
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Guru said:
This statement is false.
Click to expand...
Click to expand...
 
Guru said:
See:
A not exceptional situation that is fully known and epected to occur in
the scenario given.
Click to expand...

You should simply test for the length of Item.SomeProperty beforehand - it
is way cheaper than using try/catch.
Note: this is a known situation you can deal with without exceptions.
The only situation when exceptions might come handy are recursions.
And also see: news:uve0XH%[email protected]
Click to expand...

Can't find this one.
"...you are utterly oblivious to the concept of using Try/Catch as a
versatile, robust, and flexible programming tool that has grand purposes
well beyond merely trapping the woe-begotten side-effects of your
miserably inept coding style..."
Care to dispute?
Click to expand...

Why should I dispute you? Seems that you've figured out everything.
 
Sure, but that's an exception :-)
I mean, you can't test whether the command will execute properly due to
parameters out of your control. Although you know that connection might
drop, you can't know in advance. You are assuming there is a valid
connection which won't fail.
That's why there are exceptions.
BTW, now that I think of, in normal flow exceptions might come handy in
recursions.
 
Back
Top