LeeDumond.com

Need to check if a table exists in your SQL Server database before you do something with it? Here's a quick way to do that in both C# and VB.NET.

As I scramble to finish up my latest Wrox Blox e-Book on exception handling in ASP.NET, I haven't had much time to blog. However, I thought I'd toss up a neat little code snippet I came up with in the course of writing code for said book. (Necessity really is, as they say, the mother of invention.)

Part of the functionality I'm presenting is logging exceptions to a database. Of course, this requires the site administrator to set up the required table ahead of time. Then, I thought it might be cool to have a routine that checks if the table has been set up when an unhandled exception occurs, and then create it automatically if it doesn't exist.

Anyway, here's a little helper method I came up with to check if the table exists in the database:

static bool TableExists(string tableNameAndSchema)

{

   using (SqlConnection connection = new SqlConnection(connectionString))

   {

      string checkTable =

         String.Format(

            "IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'",

            tableNameAndSchema);

      SqlCommand command = new SqlCommand(checkTable, connection);

      command.CommandType = CommandType.Text;

      connection.Open();

      return Convert.ToBoolean(command.ExecuteScalar());

   }

}

And here is the equivalent in VB.NET:

Shared Function TableExists(tableNameAndSchema As String) As Boolean

   Using connection As New SqlConnection(connectionString)

      Dim checkTable As String = [String].Format("IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", tableNameAndSchema)

      Dim command As New SqlCommand(checkTable, connection)

      command.CommandType = CommandType.Text

      connection.Open()

      Return Convert.ToBoolean(command.ExecuteScalar())

   End Using

End Function

All you need to do is to pass in the name of the table as an argument, like "myschema.MyTable", where myschema is the schema the table belongs to (this would probably be dbo in most cases) and MyTable is the name of your table. If a table with that name exists in the database pointed to by connectionString, then the method returns true, otherwise false.

I'm still not sure if I'll be incorporating this logic into my final book version or not. But still, this is a good one I'll be tucking away for future use. Hope this helps someone out!

Subscribe to this blog for more cool content like this!

Bookmark / Share