I have the following code to check if a SQL table exists:

using (var conn = new SqlConnection(SqlServerConnectionString))
{
    conn.Open();
    var selectQuery = $"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'";
    var cmd = new SqlCommand(selectQuery, conn);
    var reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        var count = reader.GetInt32(0);
        return count > 0;
    }
    reader.Close();
    conn.Close();
}

And it works fine. Is there a way to update this line to something more easily readable or easy to understand?

var count = reader.GetInt32(0);

CodePudding user response：

There are several different ways to check if an table (or any other object) exists in the database.

This are very similar to search for every kind of object or just for tables:

SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]', N'U')

SELECT COUNT(*) FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[test]', N'U')

SELECT COUNT(*) FROM sys.objects WHERE name = N'test' AND schema_id = SCHEMA_ID(N'dbo') AND type = N'U'

or very short form this will return null if it does not exists or the object_id if it exists

SELECT OBJECT_ID(N'[dbo].[test]', N'U')

If you just want your c# code shorter/cleaner than you could do this:

using (var conn = new SqlConnection (SqlServerConnectionString))
{
  conn.Open ();
  var selectQuery = $"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName";
  var cmd = new SqlCommand (selectQuery, conn);
  cmd.Parameters.AddWithValue ("@tablename", tableName);
  var result = (int)cmd.ExecuteScalar ();
  conn.Close ();

  return result > 0;
}

CodePudding user response：

This should work, and also fixes the nasty sql injection issue:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        int result = (int?)cmd.ExecuteScalar() ?? 0;
        return result > 0;
    }
}

But you still have this line that is somewhat cryptic:

int result = (int?)cmd.ExecuteScalar() ?? 0;

You could expand it into easier code like this:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        object result = cmd.ExecuteScalar();
        if (result == null) return false;

        return ((int)result) > 0;
    }
}

Newer versions of C# can shorten this again with Pattern Matching:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        return (cmd.ExecuteScalar() is int result && result > 0);
    }
}

Or more code, but maybe a little simpler to understand:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        if (cmd.ExecuteScalar() is int result)
        {
           return result > 0;
        }
    }
    return false;
}

But I'm guessing if you don't like the other code you won't like pattern matching, either.