skip to Main Content

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.

2

Answers


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

    Login or Signup to reply.
  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 enter image description here
    • Click File
    • Select New
    • Select Project
    • For filter, choose: enter image description here
    • 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:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search