Checking for table existence

[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

 

[Rob Blackmore]

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

 

[Guest]

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?

A better way would be to use ADO.NET's GetSchema or query the metadata
table directly to check if a table exists.

 

[\(O\)enone]

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?

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,

 

[Cor Ligthert[MVP]]

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

 

[\(O\)enone]

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?

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.

 

[John]

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

 

[Cor Ligthert[MVP]]

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

 

[kimiraikkonen]

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

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.

 

[Miha Markic]

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

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.

 

[kimiraikkonen]

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,

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

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.

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

 

[Cor Ligthert[MVP]]

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

 

[Guru]

And don't forget that exceptions are for exceptional situations, not for
known situations

This statement is false.

 

[Miha Markic]

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)

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]

Care to explain?

 

[Herfried K. Wagner [MVP]]

Care to explain?

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
///

 

[Guru]

Care to explain?

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/

This statement is false.

 

[Guru]

Guru seems to be a troll.

You are not completely wrong.

However, he is not completely wrong.

I am completely right.

 

[Miha Markic]

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

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]

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?

Why should I dispute you? Seems that you've figured out everything.

 

[Miha Markic]

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.