How to connect c# with mysql

In this tutorial I’m going to show you how you can connect your c# application with a mysql database. Were also going to create a simple CRUD application.

 

Requirements

 

Database

For this tutorial I’m going to use a sample database called users. You can also use it if you want. Just create a database in phpmyadmin and execute the following query.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(80) NOT NULL,
  `middlename` varchar(80) NOT NULL,
  `lastname` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

Once you have the table input some data into it since the first thing that were going to do is to select the data from the database.

 

MySQL Namespace

I won’t go ahead and discuss the whole namespace thing. I’ll just discuss it briefly so that you will have an idea what it is. Namespace is basically just a container for classes, interface, and all sorts of object oriented stuff that you can think of. Here’s a link if you want to learn more about namespaces.

Back to our main topic, the MySQL Namespace. First you have to import it into your application, make sure that already installed the mysql .net connector on your computer before doing anything else. To import the MySQL Namespace into your application, just go to Project->Add Reference and then click on the Browse tab and browse to the following location:

C:\Program Files\MySQL\MySQL Connector Net 6.4.4\Assemblies\v2.0

Once you are there just select the MySql.Data.dll file. To make sure that you have really added the namespace you can go to your solution explorer, expand the references folder and make sure that the MySql.Data namespace is in there.

image

We still can’t go ahead and connect to a mysql database with what we just did. We still have to include it on our application. Just line it up with all the using stuff that you see on your code by default. This enables you to use all the classes and methods on the MySql.Data namespace. 

using MySql.Data.MySqlClient;

 

Database Class

Were here at the exciting part. For this tutorial I’m going to use a class to connect and do some operations on the mysql database. 

To create a new class, just go to Project->Add new item and then choose class.

Include the MySql.Data namespace.

using MySql.Data.MySqlClient;

Declare the connection string. For this tutorial I’m using the cake_beginner database. The default user for mysql is root, and the default password is none but in here I have 1234 as my password. For security sake don’t use these kinds of password on a live server.

string con_string = "Server=localhost;Database=cake_beginner;

Uid=root;Pwd=1234";

Next is the mysqlconnection class. I’m declaring it in a global scope so any method on our class can just use it. Its basically used for representing mysql database connections.

MySqlConnection con;

Lastly the mysqlcommand class. It’s a class whose methods are used for executing queries in a mysql database. More like a container for a bunch of methods which you can use to execute different queries.

MySqlCommand command;

Next we create our constructor. If you’re new to programming, the constructor is a special type of method which is automatically executed once we declare an object of the class. Sounds like a good place to define the mysqlconnection, all we have to do is to supply the connection string as an argument once we declare an object of the MySqlConnection class.

public database_class() {
            con = new MySqlConnection(con_string);
}

Next we define a method which reads a single row from our table. It has a single parameter which is the user_id and we will use it on the select query since we only want a single record to be returned.

 public string[] read_row(string user_id) {

}

Inside of the read_row() method we define an array which will store the firstname, middlename, and lastname:

string[] name =  new string[3];

Create a new command:

command = con.CreateCommand(); command.CommandText = "SELECT * FROM users

WHERE id='" + user_id + "'";

Here’s what you have to remember before or after you execute any command or query. Remember to always open the connection before you execute any query and close it after execution.
In the code below we used the MySqlDataReader to read the rows returned from our select query. Then we used a while statement together with the read() method to go through the stream of rows as long as there is something to read. In this case we are only selecting one record so this loop only executes once and then return the array which contains the firstname, middlename and lastname.

con.Open(); MySqlDataReader dr = command.ExecuteReader(); //execute select query while (dr.Read()) //continue reading while there is something to select {

//assign each field with a different index in the name array name[0] = dr["firstname"].ToString(); name[1] = dr["middlename"].ToString(); name[2] = dr["lastname"].ToString(); } con.Close();

return name;

Next is updating a single row. Define a new method and call it update_row() which has 4 parameters as you can see from the code below.

public void update_row(string fname, string mname, string lname, string user_id) {


}

Inside it we execute the update query.

string update_command = "UPDATE users SET firstname='" + fname + "', middlename='" + mname + "', lastname='" + lname + "' WHERE id='" + user_id + "'";
command.CommandText = update_command;
con.Open();
command.ExecuteNonQuery();
con.Close();

Lastly we need to create the method for creating new rows.

 public void create_row(string fname, string mname, string lname) {

}

Inside it we just execute an insert query.

command = con.CreateCommand();
command.CommandText = "INSERT INTO users SET firstname='" + fname + "', middlename='" + mname + "', lastname='" + lname + "'";
con.Open();
command.ExecuteNonQuery();
con.Close();

You might have noticed that I didn’t include a method which will delete a record from our database. Yes you’re right, and that’s because I’m lazy and I just embedded the code on the form itself so please bear with me.

 

Creating the forms

For you to have an idea what the form might look like, here’s a screenshot. The big blank space is for the datagrid, the refresh button is used for fetching the data from the mysql database and loading it into the datagrid, the create button pops out a new form which allows the user to create a new record, the edit button pops out a new form which will allow the user to update an existing row, and the delete button is used to delete a selected row from the datagrid.

image

Note that the form above is our main form so you might want to change the startup form if you don’t already have it as the startup form. To set a form as the startup form you just have to open up the Program.cs file from the solution explorer and under the Main() method just change the name of the Form supplied as the argument. In this case my default form is Form2 so just change it to whatever the name of the form you want to become the startup form.

static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form2()); //set startup form here
        }

 

Calling new forms

Calling forms is on its full object-oriented syntax on c#. You first need to create an object of the form that you’re trying to call and then use the show() method to show it. The example below calls for a form called Form1.

Form1 form1 = new Form1();
 form1.Show();

 

Populating the Datagrid

For populating the datagrid I’m not going to use the class that we built earlier. I’m going to paste the code for mysql connection on the form. Why? because I’m just a beginner in c#.

string con_string = "Server=localhost;Database=cake_beginner;Uid=root;Pwd=1234";
MySqlConnection con;
MySqlCommand command;
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter();
int current_id = 0; //changes when the user clicks on the cell

Then create a function that will load the data into the datagridview. First we clear the contents of the dataset. In simple terms the dataset is buffered data similar to that of the buffered video when you’re watching on youtube. It’s a temporary storage of data from the mysql database. Next we specify the query to be used by the data  adapter, the data adapter as defined by msdn serves as a bridge between a dataset and a data source for retrieving and saving data.
Next we filled the dataset with the results returned from the mysql command. Then finally using the data from the dataset as a data source for the datagridview.

 public void load_data() {
            ds.Clear(); //clear the dataset
            
            da.SelectCommand = new MySqlCommand("SELECT * FROM users", con);
            da.Fill(ds); //fill the dataset 
            dataGridView1.DataSource = ds.Tables[0]; //specify the data source for the datagridview
        }

On form load you just need to create an object of the MySqlConnection class and then call the method that we created earlier. This will load the data into the datagridview when the form loads.

con = new MySqlConnection(con_string); //initialize mysqlconnection
load_data(); //load data into datagridview

The refresh button just calls the load_data() method. This effectively updates the data shown in the datagridview.

 

Creating a new row

For creating a new row were also going to use the database class that we created earlier. I just had a little difficulty on passing the data from the dataset into the datagridview that’s why I showed you the evil lazy way of doing things. Having your connection strings and database operations all over the place is not a good practice, always remember that.

Here’s a screenshot of the form for creating new rows. We just have to call the create_row() method when the user clicks on the save button.

image

Here’s the code for the save button:

//housekeeping

string fname = str_cleaner(textBox1.Text);
string mname = str_cleaner(textBox2.Text);
string lname = str_cleaner(textBox3.Text);

 

db_class.create_row(fname, mname, lname); //insert data
cleaner(); //clear textboxes
this.Close(); //close the form

 

For the str_cleaner() method I just used some built in methods for processing a string in c#. I combined the ToUpper() and the Trim() method. This method returns the trimmed UPPERCASE version of the string.

public string str_cleaner(string raw_str) {
    return raw_str.ToUpper().Trim(); //remove whitespaces, all caps
}

You probably know what the cleaner() method body is but here it is anyway:

public void cleaner() {
    textBox1.Clear();
    textBox2.Clear();
    textBox3.Clear();
}

I’m interested to know if there is a better way of clearing multiple textboxes in a single execution. Please comment if you have a way to do this on one line of code.

 

Cell Click

Before we proceed with the methods for updating and deleting the records you first need to know how to track the currently selected record from the datagridview. Remember that earlier we created a global variable called user id, I made it global so that the methods for updating and deleting records will also be able to access its value. The user_id variable will store the unique user id which we will get once the user clicks on any row for the user id.
I enclosed it on a try catch statement so that the program won’t die on us once we try to convert the corresponding cell value into a string. As you can see from the code below I used Convert.ToInt32() to convert the user id from a string to an integer. If the user clicks on any of the rows under the names fields, we will get an error for trying to convert that string to an integer. Because common sense tells us that “Name” has no integer equivalent. Only “5” or “8” or any number being treated as a string can be converted to an integer.

 String current_cell = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].FormattedValue.ToString();
    try
    {
       int int_val = Convert.ToInt32(current_cell); 
       if (int_val >= 0) //checks if value is a valid integer
        {
          current_id = int_val; //assigns the user id from datagrid view into the current id
         }
     }
     catch (Exception es) { //this pops out if user clicks on a name
         MessageBox.Show("You can only click on the id if you want to update or delete the current record!");
     }

 

Deleting a row

Now that we have the current user id we can just use it on our query. Yup I got lazy again so please bear with me. In the code below its mandatory that the user has already selected something from the datagridview. To implement this I just checked if the current_id is equal to 0 since row count in any database starts with 1 and we initially gave the current_id a value of 0 so if its still 0 then it means that the user hasn’t selected anything yet. On the other hand if the current_id is not 0 then we just output a confirmation message box, if the user clicks on yes then the delete query executes and then we reload the data on the datagridview this effectively removes any traces of the deleted data.

   if (current_id == 0)
            {
                MessageBox.Show("Please select a record before clicking on delete");
            }
            else {
                if (MessageBox.Show("Are you sure you want to delete ' "+ current_id +"'", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    MySqlConnection con = new MySqlConnection(con_string );
                    MySqlCommand command = con.CreateCommand();

                    command.CommandText = "DELETE FROM users WHERE id='" + current_id + "'";
                    con.Open(); 
                    command.ExecuteNonQuery();
                    con.Close();
                    load_data();
                }
            }

 

Updating a row

Updating a row is a 2-step process, first we call up the form for updating a single row then we perform the update query from that form. As you might expect this is what the update form will look like.

image

Here’s the code for calling up the update form if the current_id is not equal to 0.

if (current_id == 0)
  {
    MessageBox.Show("Please select an id before clicking edit!");
  }
  else {
    Form3 update_form = new Form3(current_id );
    update_form.Show();
  }

If you have a good eyesight you might have noticed that I used the current_id as an argument when I created an object of Form3. And for that you need to do a little object-oriented trick on the form for updating a row.

First you need to declare a private variable and call it user_id.

private int user_id;

On the constructor for the update form you just supply a parameter called user id. Then assign this to the private variable user_id. What were doing here is simply passing the current_id(form for datagridview) to the user_id(form for update). I don’t know if that’s clear so you can comment if that wasn’t clear to you. I’m glad to answer comments.

public Form3(int user_id)
{
   InitializeComponent();
   this.user_id = user_id;
}

So once the update form loads, you just use the user_id passed to this form and convert it to string so that it can be outputted on a textbox.
I suppose there are no longer problems here since we already created the read_row() method earlier in this tutorial. To refresh your mind the read_row() method is a method which takes up a single argument which is the user id. It then uses it as a value for the select query. If a result is returned the firstname, middlename, and lastname are assigned into a different index in the name array. The name array is then returned by the method which we access from this form.

 string str_user_id = user_id.ToString();
 textBox1.Text = str_user_id;
 textBox4.Focus(); //set focus to firstname
 string[] name = db_class.read_row(str_user_id ); 
 textBox4.Text = name[0];
 textBox2.Text = name[1];
 textBox3.Text = name[2];

Finally, the code for updating.

string fname = textBox4.Text;
string mname =  textBox2 .Text;
string lname = textBox3 .Text;
db_class.update_row(fname, mname, lname, user_id.ToString ());
cleaner(); //empty the textboxes
this.Close(); //close the form

One thought on “How to connect c# with mysql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s