How to correctly check database connection with mysql in a .NET application?

Question

First I'd like to precise that I've been searching on the internet for a solution to my problem and all I find is the classic

if(connection.open) return true;

solution that is in my case not working for my needs.

I'm working on an application. Before it runs I need to check connectivity to the MySQL database, if the connection isn't done, a new window is opened prompting the user for connection settings(username, server address, password...). In this window I have a test connection button to see if the connection is established, this is the code of the button_click event :

private void Button_Click_1(object sender, RoutedEventArgs e)
{
    ApplicationSettings applicationSettings = new ApplicationSettings();

    applicationSettings.ServerDatabase = tbdbName.Text;
    applicationSettings.ServerIp = tbServer.Text;
    applicationSettings.ServerUserName = tbUsername.Text;
    //applicationSettings.ServerPassword = pbPassword.SecurePassword;
    applicationSettings.MakeConnectionString();
    MySqlConnection connection = new MySqlConnection(applicationSettings.ConnectionString);
    try
    {
        connection.Open();

        MessageBox.Show(this, "connection string: "+applicationSettings.ConnectionString+"connection OK!", "OK !", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    catch (Exception ee)
    {
        MessageBox.Show(this, "connection string : "+applicationSettings.ConnectionString+"error : " + ee.Message, "connection ERROOOOR", MessageBoxButton.OK,
            MessageBoxImage.Error);

    }
    finally
    {
        if(connection.State == ConnectionState.Open)
            connection.Close();
    }

}

If I enter only the server address "applicationSettings.ServerIp" and click the test connection button, the messageBox OK is shown and the connection string is :

Server=127.0.0.1;Database=;Password=;

I guess this is perfectly logical, but I need to test if the connection is established to the database, also if I fill the login text box with any random value, the connection is established.

question : How can I use this to test if the connection is established with the database ?

Babak Naffas · Accepted Answer · 2014-07-31 22:46:01Z

3

Connect to the database, issue a simple query like SELECT 1 from <KnownTable> and make sure you successfully receive the '1' back. This confirm that you have

Connected to the physical server (no network issues)
Authenticated with the database server (database is running and you have correct credentials)
Connected to the correct database

edited Jul 31, 2014 at 22:46

answered Jul 31, 2014 at 22:27

Babak Naffas

12.6k3 gold badges36 silver badges51 bronze badges

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

1 Comment

Redaa Over a year ago

thnks @Babak , that helps

Lamloumi Afif · Accepted Answer · 2014-07-31 22:54:18Z

try this :

private void Button_Click_1(object sender, RoutedEventArgs e)
{
    ApplicationSettings applicationSettings = new ApplicationSettings();
    applicationSettings.ServerIp = tbServer.Text;
    applicationSettings.MakeConnectionString();
   try
    {
         MySqlConnection connection = new MySqlConnection(applicationSettings.ConnectionString);
        connection.Open();
        MessageBox.Show(this, "server found", "OK !", MessageBoxButton.OK, MessageBoxImage.Information);
        connection.Close();
        applicationSettings.ServerDatabase = tbdbName.Text;
            try{
                applicationSettings.MakeConnectionString();
                connection = new MySqlConnection(applicationSettings.ConnectionString);
                connection.Open();
                MessageBox.Show(this, "Database found", "OK !", MessageBoxButton.OK, MessageBoxImage.Information);
                connection.Close();
                applicationSettings.ServerUserName = tbUsername.Text;
                applicationSettings.ServerPassword = pbPassword.SecurePassword;
                        try{
                            applicationSettings.MakeConnectionString();
                            connection = new MySqlConnection(applicationSettings.ConnectionString);
                            connection.Open();
                            MessageBox.Show(this, "Server,database and account are valid", "OK !", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        catch(Exception ee){
                                           MessageBox.Show(this, "Error: Account  parameters not valid!! " + ee.Message, "connection ERROOOOR", MessageBoxButton.OK,
                                           MessageBoxImage.Error);
                                           }
                           }
             catch(Exception ee){
                            MessageBox.Show(this, "Error: server is connected but the database not found!! " + ee.Message, "connection ERROOOOR", MessageBoxButton.OK,
                            MessageBoxImage.Error);
                                 }
    }
    catch (Exception ee)
    {
        MessageBox.Show(this, "Error: server not found " + ee.Message, "connection ERROOOOR", MessageBoxButton.OK,
            MessageBoxImage.Error);

    }
    finally
    {
        if(connection.State == ConnectionState.Open)
            connection.Close();
    }

}

Maybe it helps you ;)

Collectives™ on Stack Overflow

How to correctly check database connection with mysql in a .NET application?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related