Checking in C# whether MySQL database exists

Question

I'm writing a simple console application in C# using top-level statements, and I want to check at the beginning whethere there exists a database. Here's my code:

using MySql.Data.MySqlClient;

using (MySqlConnection connection = new MySqlConnection("Server=localhost;Uid=root;Pwd=password;"))
{
    connection.Open();
    if (CheckDatabaseExistence(connection)) Console.WriteLine("Database Exists.");
}

bool CheckDatabaseExistence(MySqlConnection connection)
{
    MySqlCommand myCommand = connection.CreateCommand();
    myCommand.CommandText = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA" +
        "WHERE SCHEMA_NAME LIKE 'database_name';";
    return Convert.ToInt32(myCommand.ExecuteScalar()) == 1;
}

After executing this code, I get the following error message:

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE 'sql_store'' at line 1'

The SQL query syntax for checking database existence is from MySQL Documentation, Section 24.3.22

SELECT SCHEMA_NAME AS `Database`
  FROM INFORMATION_SCHEMA.SCHEMATA
  [WHERE SCHEMA_NAME LIKE 'wild']

I've tried replacing LIKE with =, but I get the same error.

Concatenating strings is prone to this kind of errors. You missed a space between the two strings — Steve
– Steve, Commented Nov 25, 2022 at 15:55
However I doubt that this code will work even after fixing the typo. SCHEMA_NAME is not an integer. — Steve
– Steve, Commented Nov 25, 2022 at 16:02
What type is it exactly? I worked around that with this code return myCommand.ExecuteScalar() != null; — Nobidag297
– Nobidag297, Commented Nov 25, 2022 at 16:04

Vivek Nuna · Accepted Answer · 2022-11-25 16:05:18Z

1

you command is SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATAWHERE SCHEMA_NAME LIKE 'database_name'; and it clearly says that your SCHEMATAWHERE should have a space between, so the correct command will be SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'database_name';

Also, please define your connection string in the config file.

And you are checking the return value against an integer 1. so I think you are making mistake here. you need to compare it with your database name.

There is a simple command to check for the same SHOW DATABASES LIKE 'database_name';

edited Nov 25, 2022 at 16:05

answered Nov 25, 2022 at 15:59

Vivek Nuna

30.6k34 gold badges162 silver badges259 bronze badges

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

6 Comments

Nobidag297 Over a year ago

I compared return value with database name, and it didn't work, i.e. return myCommand.ExecuteScalar() == "database_name";. What did work is return myCommand.ExecuteScalar() != null;.

Nobidag297 Over a year ago

What is the return value of myCommand.ExecuteScalar() if it's not a string?

Vivek Nuna Over a year ago

it returns object, you need to cast it to string

Vivek Nuna Over a year ago

I think your database name is different in case. string comparison is case-sensitive. so "x" != "X". or it's really not present in your server. so you could verify by running the command first directly in your workbench

Nobidag297 Over a year ago

Casting it to string worked, as well doing a ToString() on the object. Thanks!!

|

It all makes cents · Accepted Answer · 2022-11-26 16:12:49Z

0

To check if a database exists in MySQL using a Console App, try the following:

Create a Console App

VS 2022:

Open VS 2022
Click
Click File
Select New
Select Project
For filter, choose:
Select Console App
Click Next
Enter desired project name (ex: DatabaseMySqlTest) and select desired location.
Click Next
For Framework, select .NET 6.0 (Long-term support)
Click Create

Open Solution Explorer:

In VS menu, click View
Select Solution Explorer

Open Properties Window

In VS menu, click View
Select Properties Window

Install/Download NuGet package: MySql.Data

In Solution Explorer, expand <project name>
Right-click <project name> and select Manage NuGet Packages...
Click Browse tab
In the search box, type: MySql.Data
Scroll to the top, and select MySql.Data
Select desired version (ex: 8.0.31), and click Install
If prompted Visual Studio is about to make changes to this solution. Click OK to proceed with the changes listed below..., click OK
If a License Acceptance prompt appears, click the appropriate button.

Option 1 (Application Configuration File)

Add an Application Configuration File to your project (name: App.config)

In VS menu, click Project
Select Add New Item...
Select Application Configuration File (name: App.config)
Click Add

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="MySqlConnectionAdmin" connectionString="Server=localhost;Database=information_schema;Uid=test;Pwd=mySuperSecretPassword;" />
    </connectionStrings>
</configuration>

Add a class (name: HelperMySql.cs)

In VS menu, click Project
Select Add Class... (name: HelperMySql.cs)

HelperMySql.cs:

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Diagnostics;

namespace DatabaseMySqlTest
{
    public class HelperMySql
    {
        public static bool CheckDatabaseExistence(string dbName)
        {
            //get connection string
            string connectionStrAdmin = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location).ConnectionStrings.ConnectionStrings["MySqlConnectionAdmin"].ConnectionString;

            using (MySqlConnection conn = new MySqlConnection(connectionStrAdmin))
            {
                //open
                conn.Open();

                using (MySqlCommand cmd = new MySqlCommand("SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE @dbName", conn))
                {
                    cmd.Parameters.Add("@dbName", MySqlDbType.VarChar).Value = dbName;

                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    Debug.WriteLine($"count: {count}");

                    if (count > 0)
                        return true;
                }
            }

            return false;
        }
    }
}

Option 2

Add a JavaScript JSON Configuration File to your project (name: appsettings.json)

In VS menu, click Project
Select Add New Item...
Select JavaScript JSON Configuration File (name: appsettings.json)
Click Add

appsettings.json:

{
  "ConnectionStrings": {
    "MySqlConnectionAdmin": "Server=localhost;Database=information_schema;Uid=test;Pwd=mySuperSecretPassword;"
  }
}

Set File Properties:

In Solution Explorer, select appsettings.json
In Properties Window, for Copy to Output Directory select Copy Always

Install/Download NuGet package: Microsoft.Extensions.Configuration.Json

In Solution Explorer, expand <project name>
Right-click <project name> and select Manage NuGet Packages...
Click Browse tab
In the search box, type: Microsoft.Extensions.Configuration.Json
Scroll to the top, and select Microsoft.Extensions.Configuration.Json
Select desired version (ex: 7.0.0), and click Install
If prompted Visual Studio is about to make changes to this solution. Click OK to proceed with the changes listed below..., click OK

Add a class (name: HelperMySql.cs)

In VS menu, click Project
Select Add Class... (name: HelperMySql.cs)

HelperMySql.cs:

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Diagnostics;
using Microsoft.Extensions.Configuration;

namespace DatabaseMySqlTest
{
    public class HelperMySql
    {
        public static bool CheckDatabaseExistence(string dbName)
        {
            //create new instance
            Microsoft.Extensions.Configuration.ConfigurationBuilder builder = new ConfigurationBuilder();
            builder.SetBasePath(Directory.GetCurrentDirectory());
            builder.AddJsonFile("appsettings.json");

            IConfigurationRoot configuration = builder.Build();
            string? connectionStrAdmin = configuration.GetConnectionString("MySqlConnectionAdmin");
            System.Diagnostics.Debug.WriteLine($"connectionStrAdmin: {connectionStrAdmin}");

            using (MySqlConnection conn = new MySqlConnection(connectionStrAdmin))
            {
                //open
                conn.Open();

                using (MySqlCommand cmd = new MySqlCommand("SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE @dbName", conn))
                {
                    cmd.Parameters.Add("@dbName", MySqlDbType.VarChar).Value = dbName;

                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    Debug.WriteLine($"count: {count}");

                    if (count > 0)
                        return true;
                }

            }

            return false;
        }
    }
}

Program.cs

using System;

namespace DatabaseMySqlTest // Note: actual namespace depends on the project name.
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //ToDo: change to desired database name
            string dbName = "testdb";
            Console.WriteLine($"{dbName} exists? {HelperMySql.CheckDatabaseExistence(dbName)}");
        }
    }
}

Resources:

edited Nov 26, 2022 at 16:12

answered Nov 25, 2022 at 18:45

It all makes cents

5,1493 gold badges16 silver badges36 bronze badges

6 Comments

Nobidag297 Over a year ago

Thank you for your time trying to help me. I have a few [n00b] questions: (1) In the configuration file I have to provide the database name, which existence I'm not sure of? This seems illogical to me. Please correct me if I'm wrong. (2) Is this the safest and optimal way to check for database existence? (3) Why GetDatabases() if it's not being used anywhere? (4) I don't understand some portions of the code, so can you point me to some good book or a course on this subject, so I can learn?

Nobidag297 Over a year ago

Also, I've read on StackOverflow that appsettings.json is now preferred method instead of app.config?

It all makes cents Over a year ago

1) MySQL has a number of default databases which one can see if one opens MySQL 8.0 Command Line Client, logs in, and types: show databases;. 2) The "safest" thing is to not check for database existence because checking for database existence requires certain permissions. 3) GetDatabases was a "bonus" method. I've removed it. 4) It's unclear what portions of the code you don't understand. As general guidance, use your favorite search engine to search for any terms you're unfamiliar with (ex: c# using).

It all makes cents Over a year ago

I've read on StackOverflow that appsettings.json is now preferred method instead of app.config: According to Connection Strings: In ASP.NET Core the configuration system is very flexible, and the connection string could be stored in appsettings.json, an environment variable, the user secret store, or another configuration source.

It all makes cents Over a year ago

....WinForms, WPF, and ASP.NET 4 applications have a tried and tested connection string pattern. The connection string should be added to your application's App.config file (Web.config if you are using ASP.NET). If your connection string contains sensitive information, such as username and password, you can protect the contents of the configuration file using Protected Configuration

|

Collectives™ on Stack Overflow

Checking in C# whether MySQL database exists

2 Answers 2

6 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related