How To Check MySQL Database Connection With Csharp

Hello blogger, wherever you are, see me again. Maybe you guys who have visited this blog for the first time don't know about this blog. So, this 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".

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:

Open your browser and in the address bar type: localhost / phpmyadmin
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.
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.

Check out this link: Create Custom Geofilters On Snapchat (Snapchat Update)

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.

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.

Check out this link: Top 5 Tips to Choose the Best WordPress Hosting

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.

Check out this link: 10 Tips To Reduce Your Sites Bounce Rate Effectively