determining if an SQL database exists, etc.

[BobLewiston]

Could someone please give me the simplest possible C# code snippets / SQL queries to determine whether:

* a given SQL database exists,
* a given existing SQL database is accessible, and
* a given table exists within a given existing SQL database?

Thanks for whatever help anyone can provide.

 

[Daeva]

You should use these namespaces:
System.Data
System.Data.SqlClient

Database getData function that returns a datatable object. You could change this to return int, String, or whatever info you wanted from the database really.

  public DataTable getData(String sqlCmd) {
    String connStr = My.MySettings.Default.ConnectionStringNameHere;
    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(sqlCmd,conn);
    conn.Open();
    try {
      SqlDataReader rdr;
      rdr = cmd.ExecuteReader();
      DataTable dt = new DataTable();
      dt.Load(rdr,LoadOption.Upsert);
      conn.Close();
    } catch (SqlException Sqle) {
      //TODO: Log Error
    } catch (Exception ex) {
      //TODO: Log Error
    } finally {
      conn.Close(); // make sure the connection gets closed, regardless of whether or not the query was successfull.
    } // end try/catch/finally
    // setup the old variables for garbage collection
    rdr = null;
    cmd = null;
    conn = null;
    connStr = null;
    // end setup for GC
    return dt;
} // end function getData

I left out some error checking, but you get the idea.

code to check for a databases existence.

  String dbName = ""; // set here or pass this in from somewhere.
  DataTable dt = getData("SELECT db_id("' + dbName + "')");
  if (dt.rows.count() > 0) {
    //the above if checks to make sure that the query executed successfully and didn't encounter any errors
    if(dt.rows[0].item[0] > 0) { // this is unique "ID" of the database in sysobjects
      //TODO: we found our database
    } else {
      // the query ran, but no database was found
    } // end if/else
  } else {
    //no database was found with that name or the query failed, print error or continue
  } // end if/else
  dt = null; // if you aren't going to use it later

Here is a link to the article on db_id()
http://msdn.microsoft.com/en-us/library/ms186274.aspx

Check for a table's existence:

String dbName = "";
DataTable dt = getData("SELECT name FROM sys.tables WHERE name = '" + dbName +"'");
if (dt.rows.count() > 0) { //the above if checks to make sure that the query executed successfully and didn't encounter any errors 
  if(dt.rows[0].item[0] > 0) { // In this case, this will return the name of a valid database table if it exists
    //TODO: we found our table
  } else { 
    // the query ran, but no table was found
  } // end if/else 
} else { 
  //no table was found with that name or the query failed, print error or continue 
} // end if/else

That should help. Let me know if you need clarification on anything I did. Also, please forgive capitalization errors as I don't work in C# very much anymore and I didn't compile this.