MYSql with C# in Visual Studio. How to return a boolean if a database exists

Question

I'm working on a WPF MVVM Light application and I'd like to have a Boolean Method that uses a MYSql Query and C# to Progammatically determine if the Database exists. Any ideas would be greatly appreciated.

Maybe something with a query like :

SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'Mark'), 'Yes','No')

Or:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'

I've got methods that will query the database but I'd like to have a checksum that determines if the db exists. If I use the code below it will error out so I'd like to determine first if the db exists and if it does query it.

    static public Project.Project QueryProject(string projDatabaseName)
    {
        Project.Project proj = new Project.Project();
        string connStr = "server=localhost;database=" + projDatabaseName + ";user=******;port=3306;password=********;";
        string queryStr = "SELECT * FROM " + projDatabaseName + ".project";
        MySqlConnection myConnection = new MySqlConnection(connStr);
        MySqlCommand myCommand = new MySqlCommand(queryStr, myConnection);
        myConnection.Open();

        try
        {
            MySqlDataReader myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                proj.ProjectID = int.Parse(myReader["ProjectID"].ToString());
                proj.ProjectName = myReader["ProjectName"].ToString();
                proj.ProjectStartDate = Convert.ToDateTime(myReader["ProjectStartDate"]);
                proj.ProjectEndDate = Convert.ToDateTime(myReader["ProjectEndDate"]);
                proj.ProjectNotes = myReader["ProjectNotes"].ToString();
            }
            myReader.Close();

        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
            return null;
        }
        finally
        {
            myConnection.Close();
        }
        return proj;
    }

have you tried connecting to the server without selecting a database and run query show databases; — jbriggs
– jbriggs, Commented Jan 27, 2015 at 18:38
use INFORMATION_SCHEMA as the database. If the show databases doesn't work try SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA — jbriggs
– jbriggs, Commented Jan 27, 2015 at 18:58
Also if you use a using statement for your connection it creates a finally statement and closes the connection for you using (MySqlConnection myConnection = new MySqlConnection(connStr)) { — jbriggs
– jbriggs, Commented Jan 27, 2015 at 19:00
See my answer. It worked for me. It will only list databases you have access to. — jbriggs
– jbriggs, Commented Jan 27, 2015 at 20:32

jbriggs · Accepted Answer · 2015-01-27 20:25:50Z

2

use INFORMATION_SCHEMA as the database the use show databases.

public bool DatabaseExists(string dbname)
{
string connStr = "server=localhost;database=INFORMATION_SCHEMA;";

using (MySqlConnection myConnection = new MySqlConnection(connStr))
{
 string sql = "show databases";
 MySqlCommand myCommand = new MySqlCommand(sql, myConnection);
 myConnection.Open();
 MySqlDataReader myReader = myCommand.ExecuteReader();
 while (myReader.Read())
 {
   string db =  myReader["Database"].ToString();
   if (db == dbname)
     return true;
 }
}
return false;
}

answered Jan 27, 2015 at 20:25

jbriggs

3532 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yams Over a year ago

This did it. I really appreciate it. Good Stuff!

Igor · Accepted Answer · 2015-01-27 22:18:48Z

0

Try this:

SHOW DATABASES LIKE 'databaseName';

It returns an empty set if it doesn't exist.

edited Jan 27, 2015 at 22:18

answered Jan 27, 2015 at 19:11

Igor

1,6024 gold badges24 silver badges44 bronze badges

2 Comments

yams Over a year ago

That's what I'd like to do but I'd like to do it with out throwing an error.

jbriggs Over a year ago

It's bad practice to use try catch as a conditional statement

Collectives™ on Stack Overflow

MYSql with C# in Visual Studio. How to return a boolean if a database exists

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related