Checking in C# whether MySQL database exists

Nobidag297
November 25, 2022
230 views
0 votes
2 Answers

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.

Tags: c#console-application database-schema mysql toplevel-statement

Answers

- VivekNuna
- November 25, 2022 at 4:59 pm
- 0 votes
0
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';

Login or Signup to reply.

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:

Please signup or login to give your own answer.

Click here to cancel reply.