Go Back   Computer Forums > General Computing > Programming
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 04-12-2009, 05:34 PM   #1
Baseband Member
 
Join Date: Nov 2008
Posts: 91
Default determining if an SQL database exists, etc.

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.
__________________

BobLewiston is offline   Reply With Quote
Old 04-13-2009, 09:32 AM   #2
In Runtime
 
Daeva's Avatar
 
Join Date: Dec 2005
Posts: 407
Send a message via AIM to Daeva Send a message via MSN to Daeva Send a message via Yahoo to Daeva
Default Re: determining if an SQL database exists, etc.

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.
Code:
  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.
Code:
  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:
Code:
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.
__________________
**Official Self-proclaimed glorified excessive (insert additional adjectives here) post editor/modifier.
Edit = Best feature ever
http://www.twitter.com/xDaevax
Daeva is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 06:48 AM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0