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.

Answer 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';

Answer 2

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:

• NullReferenceException trying to read connection string
• Looking inside ConfigurationManager in .NET 6
• ConfigurationBuilder Class
• ConfigurationBuilder.Build Method
• ConfigurationExtensions.GetConnectionString
• using statement (C# Reference)
• $ - string interpolation (C# reference)
• Nullable value types (C# reference)