How To Check MySQL Database Connection With Csharp - Gudio Tutorial

    Social Items


MySQL_Database_Connection_With_Csharp

Hello blogger, wherever you are, see me again in Gudio Blog. Maybe you guys who have visited this blog for the first time don't know about this blog. So, Gudio Blog is a blog that discusses programming both web, desktop and mobile, tips and tricks on bloggers, android and more about technology.

As in the title above, in this article, I will discuss the csharp tutorial, which is about "How To Check MySQL Database Connection With Csharp". To connect Chsarp with MySQL we need an additional application called MySQL Connector. To download the application and install it and add the mysql connector to visual studio I will not explain it again here, you can read my previous post about "Make a Connection From VB.Net to MySQL". So I assume all of you have installed the mysql connector in Visual Studio.

Okay, we will go straight to the topic of discussion because in this article there are no more theories that I need to explain. If you are still confused with Csharp, you can read my tutorial about the basics of Csharp in this article "Csharp For Beginners (Introduction)"



Create Database in MySQL

Before we start into creating applications in chsarp. We need to prepare a database that we will make an experiment on our application later. But if you already have a database, you can miss reading this, you can continue to make the application. But, if you don't have a database, here's how:

  1. Open your browser and in the address bar type: localhost / phpmyadmin
  2. Create a new database, you can name the database with the name you want and it doesn't have to be the same as this tutorial.
  3. After completing the database, continue by creating the table. Because in this tutorial we do not need a table because we only check the database, whether the application we created successfully connected to the database or not. But, because in phpmyadmin requires to create a table when creating a database, then we create a simple table with two columns, as below:

CREATE TABLE `checkDatabase`.`tbCheck` ( `no` INT NOT NULL AUTO_INCREMENT , `id` INT NOT NULL , PRIMARY KEY (`no`)) ENGINE = InnoDB;

If you have successfully created a database, now we will make the application. Please open your Visual Studio and we will make the application design first.



Application Design

In this step, we will make the application design first before we step for coding. You can follow the design like below or you can create your own design.
Component Object Name Qty Function
Group Box groupBox1
groupBox2
2 -
-
Picture Box pictureBox1
pictureBox2
pictureBox3
3 pictureBox3 for logo
pictureBox1 for button reset
pictureBox2 for button connection
Label lblServer
lblDatabase
lblUsername
lblPassword
lblStatus
5 lblServer as a label for Server combobox
lblDatabase as a label for Combobox Database
lblUsername as a label for textboxt username
lblPassword as a label for textboxt password
lblStatus as information whether the connection was successful or not
Button btnReset
btnConnect
2 btn Reset to clean the combobox and textbox from user input
btnConnect to connect to the database
Combo Box cmdServer
cmdDatabase
2 cmd Server to display the name of the server that will be used to connect to the database
cmdDatabase to display the database name on the combobox
Textbox txtUsername
txtPass
2 txtUsername as input for the user to specify the username used to connect to the database
txtPass as input for the user to specify the password that is used to connect to the database

The table above is the components used to design the application. And below is the design of the application.

Design App

When you are done making the application design, now we go to the next step, which is coding.



Coding

Double-click on the form to go to the coding page. The first thing we create is to input the database name in MySQL. So all the names of the databases in the table will be displayed in the cmdDatabase component.


Import MySQL Data
First you must first import MySQL Data by typing the following code:

using MySql.Data.MySqlClient;

If MySQL Data is not displayed, then it's possible you haven't added the reference MySQL data to the preference. To add a reference:
  • In the Project Menu, select "Add Reference"
  • Then the menu will appear, please find MySQL Data, you can also search by using the search column, and if you have found it, just click OK.
That's the way to Add References in Csharp.



Show MySQL Database
As I explained above, the code below is to display all databases in MySQL. Type the code below in the Load Form section:

private void Form1_Load(object sender, System.EventArgs e)
{
  string myConnectionString = "SERVER=localhost;UID='root';" + "PASSWORD=;";
  MySqlConnection connection = new MySqlConnection(myConnectionString);
  MySqlCommand command = connection.CreateCommand();
  command.CommandText = "SHOW DATABASES;";
  MySqlDataReader Reader;
  connection.Open();
  Reader = command.ExecuteReader();
  while (Reader.Read())
  {
    tring row = "";
    for (int i = 0; i < Reader.FieldCount; i++)
       row += Reader.GetValue(i).ToString();
       cmdDatabase.Items.Add(row);
   }
  connection.Close();
}

explanation of the code above:

string myConnectionString = "SERVER=localhost;UID='root';" + "PASSWORD=;";
MySqlConnection connection = new MySqlConnection(myConnectionString);

The purpose of the code above is that we first create a variable string to hold data from our connection to MySQL.

MySqlCommand command = connection.CreateCommand();
command.CommandText = "SHOW DATABASES;";

The above code is used to make the command display the database that is in MySQL with the "SHOW DATABASE" command.

MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();

explanation of the code above, we first create a DataReader variable. Then the connection will be opened. When the connection is open, the reader variable will execute the "SHOW DATABASE" command.

while (Reader.Read())
{
  tring row = "";
  for (int i = 0; i < Reader.FieldCount; i++)
    row += Reader.GetValue(i).ToString();
    cmdDatabase.Items.Add(row);
}
connection.Close();

when the command run by "Reader" is successful and gets database names in MySQL, then the names of the MySQL database will be displayed in cmdDatabase using the "For" function. and if the data has been displayed, the connection to the database will be closed




Reset all columns
Reset all user input in the column when clicking the reset button. Double click on btnReset then type the code below:

cmdDatabase.SelectedIndex = -1;
cmdServer.SelectedIndex = -1;
txtPass.Text = "";
txtUsername.Text = "";
lblStatus.Text = "CONNECTION STATUS :";

explanation of the code above:
the code above to clear the column of all input filled by the user



Check Connection
Then now we continue to add code to the connection button, for that double click on the connection button and type the code below:

private void btnConnect_Click(object sender, EventArgs e)
{
  if (cmdServer.SelectedIndex == -1 || cmdDatabase.SelectedIndex ==-1)
  {
    MessageBox.Show("fill in all the data first","Gudio Blog - C# Tutorial",MessageBoxButtons.OK,MessageBoxIcon.Warning);
    cmdServer.Focus();
  }
  else
  {
    string server = cmdServer.Text;
    string database = cmdDatabase.Text;
    string user_id;
    string password = txtPass.Text;

    if (txtUsername.Text == "")
    {
      user_id = "root";
    }else
    {
      user_id = txtUsername.Text;
    }

    try
    {
     MySqlConnection connect = new MySqlConnection("server=" + server + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";sslMode=none");
     connect.Open();
     lblStatus.Text = "CONNECTION STATUS : Connection Successfull"; 
    }
    catch (Exception ex)
    {
      lblStatus.Text = "CONNECTION STATUS :" + ex.ToString();
    }

}

explanation of the code above:

if (cmdServer.SelectedIndex == -1 || cmdDatabase.SelectedIndex ==-1)
  {
    MessageBox.Show("fill in all the data first","Gudio Blog - C# Tutorial",MessageBoxButtons.OK,MessageBoxIcon.Warning);
    cmdServer.Focus();
  }

The code above functions to check and validate if the combobox input has not been selected by the user, and will display a warning message box to the user.


string server = cmdServer.Text;
string database = cmdDatabase.Text;
string user_id;
string password = txtPass.Text;

if (txtUsername.Text == "")
{
  user_id = "root";
}else
{
  user_id = txtUsername.Text;
}

In the code above we create a string type variable first to hold data from input such as a combo box and textbox. And if the username textbox is left blank by the user, the application automatically adds the username "root" (username = 'root' is the default username of mysql). But if the textbox is filled in by the user, the username used is the username entered by the user.


try
{
  MySqlConnection connect = new MySqlConnection("server=" + server + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";sslMode=none");
  connect.Open();
  lblStatus.Text = "CONNECTION STATUS : Connection Successfull"; 
}
catch (Exception ex)
{
  lblStatus.Text = "CONNECTION STATUS :" + ex.ToString();
}

In the code above it is used to connect to the database chosen by the user and with data entered by the user.

The above code contains a "try" "catch" wrapper. Its function is if the code that is in the "try" doesn't work or a problem occurs, then the problem will be displayed in the "catch" section and the problem will be changed to the string type, making it easier for the user to read.

In the code above, if the connection is successful, a message will appear on the "Connection Status", as well as a problem.

And the following below is the full source code that we have made.

 private void Form1_Load(object sender, System.EventArgs e)
        {
            string myConnectionString = "SERVER=localhost;UID='root';" + "PASSWORD=;";
            MySqlConnection connection = new MySqlConnection(myConnectionString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SHOW DATABASES;";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string row = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    row += Reader.GetValue(i).ToString();
                cmdDatabase.Items.Add(row);
            }
            connection.Close();
        }

        private string ToString(MySqlCommand sqlcommand)
        {
            throw new NotImplementedException();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            if (cmdServer.SelectedIndex == -1 || cmdDatabase.SelectedIndex ==-1)
            {
                MessageBox.Show("fill in all the data first","Gudio Blog - C# Tutorial",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                cmdServer.Focus();
            }
            else
            {
                string server = cmdServer.Text;
                string database = cmdDatabase.Text;
                string user_id;
                string password = txtPass.Text;

                if (txtUsername.Text == "")
                {
                    user_id = "root";
                }else
                {
                    user_id = txtUsername.Text;
                }

                try
                {
                    MySqlConnection connect = new MySqlConnection("server=" + server + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";sslMode=none");
                    connect.Open();
                    lblStatus.Text = "CONNECTION STATUS : Connection Successfull"; 
                }
                catch (Exception ex)
                {
                    lblStatus.Text = "CONNECTION STATUS :" + ex.ToString();
                }

            }
            
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            cmdDatabase.SelectedIndex = -1;
            cmdServer.SelectedIndex = -1;
            txtPass.Text = "";
            txtUsername.Text = "";
            lblStatus.Text = "CONNECTION STATUS :";
        }
    }




Conclusion

Okay, that's all, the article about the csharp tutorial "How To Check MySQL Database Connection With Csharp". Hopefully the tutorial I gave can be useful for you and can provide solutions to problems and inspire you to create applications using Csharp.

If if there is an error in writing my article above, I apologize. And if you want to download the source code from the tutorial above, please click the download button below.




We meet again at other times and with other interesting articles.


Regards,
Gudio Blog

How To Check MySQL Database Connection With Csharp


MySQL_Database_Connection_With_Csharp

Hello blogger, wherever you are, see me again in Gudio Blog. Maybe you guys who have visited this blog for the first time don't know about this blog. So, Gudio Blog is a blog that discusses programming both web, desktop and mobile, tips and tricks on bloggers, android and more about technology.

As in the title above, in this article, I will discuss the csharp tutorial, which is about "How To Check MySQL Database Connection With Csharp". To connect Chsarp with MySQL we need an additional application called MySQL Connector. To download the application and install it and add the mysql connector to visual studio I will not explain it again here, you can read my previous post about "Make a Connection From VB.Net to MySQL". So I assume all of you have installed the mysql connector in Visual Studio.

Okay, we will go straight to the topic of discussion because in this article there are no more theories that I need to explain. If you are still confused with Csharp, you can read my tutorial about the basics of Csharp in this article "Csharp For Beginners (Introduction)"



Create Database in MySQL

Before we start into creating applications in chsarp. We need to prepare a database that we will make an experiment on our application later. But if you already have a database, you can miss reading this, you can continue to make the application. But, if you don't have a database, here's how:

  1. Open your browser and in the address bar type: localhost / phpmyadmin
  2. Create a new database, you can name the database with the name you want and it doesn't have to be the same as this tutorial.
  3. After completing the database, continue by creating the table. Because in this tutorial we do not need a table because we only check the database, whether the application we created successfully connected to the database or not. But, because in phpmyadmin requires to create a table when creating a database, then we create a simple table with two columns, as below:

CREATE TABLE `checkDatabase`.`tbCheck` ( `no` INT NOT NULL AUTO_INCREMENT , `id` INT NOT NULL , PRIMARY KEY (`no`)) ENGINE = InnoDB;

If you have successfully created a database, now we will make the application. Please open your Visual Studio and we will make the application design first.



Application Design

In this step, we will make the application design first before we step for coding. You can follow the design like below or you can create your own design.
Component Object Name Qty Function
Group Box groupBox1
groupBox2
2 -
-
Picture Box pictureBox1
pictureBox2
pictureBox3
3 pictureBox3 for logo
pictureBox1 for button reset
pictureBox2 for button connection
Label lblServer
lblDatabase
lblUsername
lblPassword
lblStatus
5 lblServer as a label for Server combobox
lblDatabase as a label for Combobox Database
lblUsername as a label for textboxt username
lblPassword as a label for textboxt password
lblStatus as information whether the connection was successful or not
Button btnReset
btnConnect
2 btn Reset to clean the combobox and textbox from user input
btnConnect to connect to the database
Combo Box cmdServer
cmdDatabase
2 cmd Server to display the name of the server that will be used to connect to the database
cmdDatabase to display the database name on the combobox
Textbox txtUsername
txtPass
2 txtUsername as input for the user to specify the username used to connect to the database
txtPass as input for the user to specify the password that is used to connect to the database

The table above is the components used to design the application. And below is the design of the application.

Design App

When you are done making the application design, now we go to the next step, which is coding.



Coding

Double-click on the form to go to the coding page. The first thing we create is to input the database name in MySQL. So all the names of the databases in the table will be displayed in the cmdDatabase component.


Import MySQL Data
First you must first import MySQL Data by typing the following code:

using MySql.Data.MySqlClient;

If MySQL Data is not displayed, then it's possible you haven't added the reference MySQL data to the preference. To add a reference:
  • In the Project Menu, select "Add Reference"
  • Then the menu will appear, please find MySQL Data, you can also search by using the search column, and if you have found it, just click OK.
That's the way to Add References in Csharp.



Show MySQL Database
As I explained above, the code below is to display all databases in MySQL. Type the code below in the Load Form section:

private void Form1_Load(object sender, System.EventArgs e)
{
  string myConnectionString = "SERVER=localhost;UID='root';" + "PASSWORD=;";
  MySqlConnection connection = new MySqlConnection(myConnectionString);
  MySqlCommand command = connection.CreateCommand();
  command.CommandText = "SHOW DATABASES;";
  MySqlDataReader Reader;
  connection.Open();
  Reader = command.ExecuteReader();
  while (Reader.Read())
  {
    tring row = "";
    for (int i = 0; i < Reader.FieldCount; i++)
       row += Reader.GetValue(i).ToString();
       cmdDatabase.Items.Add(row);
   }
  connection.Close();
}

explanation of the code above:

string myConnectionString = "SERVER=localhost;UID='root';" + "PASSWORD=;";
MySqlConnection connection = new MySqlConnection(myConnectionString);

The purpose of the code above is that we first create a variable string to hold data from our connection to MySQL.

MySqlCommand command = connection.CreateCommand();
command.CommandText = "SHOW DATABASES;";

The above code is used to make the command display the database that is in MySQL with the "SHOW DATABASE" command.

MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();

explanation of the code above, we first create a DataReader variable. Then the connection will be opened. When the connection is open, the reader variable will execute the "SHOW DATABASE" command.

while (Reader.Read())
{
  tring row = "";
  for (int i = 0; i < Reader.FieldCount; i++)
    row += Reader.GetValue(i).ToString();
    cmdDatabase.Items.Add(row);
}
connection.Close();

when the command run by "Reader" is successful and gets database names in MySQL, then the names of the MySQL database will be displayed in cmdDatabase using the "For" function. and if the data has been displayed, the connection to the database will be closed




Reset all columns
Reset all user input in the column when clicking the reset button. Double click on btnReset then type the code below:

cmdDatabase.SelectedIndex = -1;
cmdServer.SelectedIndex = -1;
txtPass.Text = "";
txtUsername.Text = "";
lblStatus.Text = "CONNECTION STATUS :";

explanation of the code above:
the code above to clear the column of all input filled by the user



Check Connection
Then now we continue to add code to the connection button, for that double click on the connection button and type the code below:

private void btnConnect_Click(object sender, EventArgs e)
{
  if (cmdServer.SelectedIndex == -1 || cmdDatabase.SelectedIndex ==-1)
  {
    MessageBox.Show("fill in all the data first","Gudio Blog - C# Tutorial",MessageBoxButtons.OK,MessageBoxIcon.Warning);
    cmdServer.Focus();
  }
  else
  {
    string server = cmdServer.Text;
    string database = cmdDatabase.Text;
    string user_id;
    string password = txtPass.Text;

    if (txtUsername.Text == "")
    {
      user_id = "root";
    }else
    {
      user_id = txtUsername.Text;
    }

    try
    {
     MySqlConnection connect = new MySqlConnection("server=" + server + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";sslMode=none");
     connect.Open();
     lblStatus.Text = "CONNECTION STATUS : Connection Successfull"; 
    }
    catch (Exception ex)
    {
      lblStatus.Text = "CONNECTION STATUS :" + ex.ToString();
    }

}

explanation of the code above:

if (cmdServer.SelectedIndex == -1 || cmdDatabase.SelectedIndex ==-1)
  {
    MessageBox.Show("fill in all the data first","Gudio Blog - C# Tutorial",MessageBoxButtons.OK,MessageBoxIcon.Warning);
    cmdServer.Focus();
  }

The code above functions to check and validate if the combobox input has not been selected by the user, and will display a warning message box to the user.


string server = cmdServer.Text;
string database = cmdDatabase.Text;
string user_id;
string password = txtPass.Text;

if (txtUsername.Text == "")
{
  user_id = "root";
}else
{
  user_id = txtUsername.Text;
}

In the code above we create a string type variable first to hold data from input such as a combo box and textbox. And if the username textbox is left blank by the user, the application automatically adds the username "root" (username = 'root' is the default username of mysql). But if the textbox is filled in by the user, the username used is the username entered by the user.


try
{
  MySqlConnection connect = new MySqlConnection("server=" + server + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";sslMode=none");
  connect.Open();
  lblStatus.Text = "CONNECTION STATUS : Connection Successfull"; 
}
catch (Exception ex)
{
  lblStatus.Text = "CONNECTION STATUS :" + ex.ToString();
}

In the code above it is used to connect to the database chosen by the user and with data entered by the user.

The above code contains a "try" "catch" wrapper. Its function is if the code that is in the "try" doesn't work or a problem occurs, then the problem will be displayed in the "catch" section and the problem will be changed to the string type, making it easier for the user to read.

In the code above, if the connection is successful, a message will appear on the "Connection Status", as well as a problem.

And the following below is the full source code that we have made.

 private void Form1_Load(object sender, System.EventArgs e)
        {
            string myConnectionString = "SERVER=localhost;UID='root';" + "PASSWORD=;";
            MySqlConnection connection = new MySqlConnection(myConnectionString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SHOW DATABASES;";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string row = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    row += Reader.GetValue(i).ToString();
                cmdDatabase.Items.Add(row);
            }
            connection.Close();
        }

        private string ToString(MySqlCommand sqlcommand)
        {
            throw new NotImplementedException();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            if (cmdServer.SelectedIndex == -1 || cmdDatabase.SelectedIndex ==-1)
            {
                MessageBox.Show("fill in all the data first","Gudio Blog - C# Tutorial",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                cmdServer.Focus();
            }
            else
            {
                string server = cmdServer.Text;
                string database = cmdDatabase.Text;
                string user_id;
                string password = txtPass.Text;

                if (txtUsername.Text == "")
                {
                    user_id = "root";
                }else
                {
                    user_id = txtUsername.Text;
                }

                try
                {
                    MySqlConnection connect = new MySqlConnection("server=" + server + ";user id=" + user_id + ";password=" + password + ";database=" + database + ";sslMode=none");
                    connect.Open();
                    lblStatus.Text = "CONNECTION STATUS : Connection Successfull"; 
                }
                catch (Exception ex)
                {
                    lblStatus.Text = "CONNECTION STATUS :" + ex.ToString();
                }

            }
            
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            cmdDatabase.SelectedIndex = -1;
            cmdServer.SelectedIndex = -1;
            txtPass.Text = "";
            txtUsername.Text = "";
            lblStatus.Text = "CONNECTION STATUS :";
        }
    }




Conclusion

Okay, that's all, the article about the csharp tutorial "How To Check MySQL Database Connection With Csharp". Hopefully the tutorial I gave can be useful for you and can provide solutions to problems and inspire you to create applications using Csharp.

If if there is an error in writing my article above, I apologize. And if you want to download the source code from the tutorial above, please click the download button below.




We meet again at other times and with other interesting articles.


Regards,
Gudio Blog
iklan related post
Load comments