Checking in C# whether MySQL database exists-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

To check if a database exists in MySQL using a Console App (.NET 6), try the following:

Create a Console App (.NET 6)

Install/Download NuGet package: MySql.Data

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=mysql;Uid=test;Pwd=mySuperSecretPassword;" />
    </connectionStrings>
</configuration>

Create a class (name: 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;
        }

        public static List<string> GetDatabases()
        {
            List<string> databases = new List<string>();

            //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 SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA", conn))
                {
                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                //set value
                                string? schemaName = dr[0]?.ToString();

                                if (!String.IsNullOrEmpty(schemaName))
                                    databases.Add(schemaName); //add
                            }
                        }
                    }
                }
            }

            return databases;
        }
    }
}

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)}");

            Console.WriteLine("Press any key to exit.");
            Console.ReadKey();
        }
    }
}

Resources:

NullReferenceException trying to read connection string