C# + ADO.NET + SQL Server

[Guest]

What is the easiest / fastest way to check if a table <tablename> exists in
the database?

Thank you

 

[Jim Hughes]

There are several ways it could be done, this is one.

EXEC sp_tables @table_name='Customers'

 

[Guest]

Alex,

You can use...

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'tablenamegoeshere' AND TABLE_TYPE = 'BASE TABLE'


Hope this helps

 

[Guest]

Jim,

I am trying to follow your advice. It works fine in VB6, because ADO 2.6
Connection's Execute method (cn.Execute "exec sp_tables '...'") returns -1
if table exists and 0 if it doesn't.
But in ADO.NET, SqlCommand 's ExecuteNonQuery always returns -1 for any
command except UPDATE, INSERT or DELETE. In my case, it always returns -1.

Any ideas?

Thanks.

 

[Guest]

Alex,

I have posted a sample below of both proposed queries for you to use.
Please keep in mind that this is a sample only and you should implement
exception handling in the code if you decide to use it.

Good Luck!

-----------------------------------
bool firstResult = false, secondResult = false;
string strSql1, strSql2;
string tablename = "'employees'";

strSql1 = "EXEC sp_tables @table_name= " + tablename;
strSql2 = "SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = " +
tablename + " AND TABLE_TYPE = 'BASE TABLE'";

SqlConnection cn = new SqlConnection("Data Source=(local);Initial
Catalog=Northwind;User Id=sa;Password=verbatim;" );
cn.Open();
//first method
SqlCommand firstCmd = new SqlCommand(strSql1,cn);
Object oResult1 = firstCmd.ExecuteScalar();
if(oResult1 != null)
firstResult = true;
//second method
SqlCommand secondCmd = new SqlCommand(strSql2,cn);
Object oResult2 = secondCmd.ExecuteScalar();
if(Convert.ToInt32(oResult2)!= 0)
secondResult = true;

cn.Close();