Connecting C# with mySQL Database

Hello everyone,

This is saif, coming up with another COOL tutorial and welcome you all from Karachi where it is too hot approaching 40 Degrees right now.

This article is for beginners who want to develop a GUI based application in C# that requires a Database to store application’s data.

This article will guide step by step how connect a C# Application with MySQL. Let’s Begin.

If you don’t have any prior knowledge of creating database in MySQL using XAMPP read this article once to have a flavor.

  1. First of all Create mySQL database using XAMPP. You can follow the steps mentioned in my this tutorial.
  2. Open VisualStudio and create a New Projectcsharp1
  3. Select Visual C# and Windows Form Application then press OK.csharp2
  4. You will see a blank Form like this.csharp3
  5. Design a simple login panel using text boxes, labels and a button as shown below.csharp5
  6. Now right-click on reference in the Solution Explorer and select add references.
  7. Search for MYSql.Data and MySql.Web and select them.csharp4
  8. After you have successfully added references for MySql client, create a new connection class named as “CRUD” and paste the code given below in it. You can replace “testdb” with your database name.
  9. using MySql.Data;
    using MySql.Data.MySqlClient;
    using System;
    
    namespace MyFirstGUIApp
    {
        public class CRUD
        {
            private CRUD()
            {
            }
    
            private string databaseName = "testdb";
            public string DatabaseName
            {
                get { return databaseName; }
                set { databaseName = value; }
            }
    
            public string Password { get; set; }
            private MySqlConnection connection = null;
            public MySqlConnection Connection
            {
                get { return connection; }
            }
    
            private static CRUD _instance = null;
            public static CRUD Instance()
            {
                if (_instance == null)
                {
                    _instance = new CRUD();
                }
                return _instance;
            }
    
            public bool IsConnect()
            {
                bool result = true;
                if (Connection == null)
                {
                    if (String.IsNullOrEmpty(databaseName))
                        result = false;
                    string connstring = string.Format("Server=localhost; database={0}; UID=root; password='' ", databaseName);
                    connection = new MySqlConnection(connstring);
                    connection.Open();
                    result = true;
                }
    
                return result;
            }
    
            public void Close()
            {
                connection.Close();
            }
        }
    }
  10. Create a global instance of CRUD class just above the class constructor.csharp6
  11. Double click submit button on the form to create button_click event. Paste the below code there.
  12. try
                {
                    if (con.IsConnect())
                    {
                        string query = "SELECT Name from student where ID="+id.Text+" and Password ='"+pass.Text.ToString()+"'";
                        MySqlCommand cmd = new MySqlCommand(query, con.Connection);
                        MySqlDataReader reader = cmd.ExecuteReader();
    
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                MessageBox.Show("Access Guaranteed! Welcome "+reader.GetString(0));
                            }
                            reader.Close();
                        }
                        else
                        {
                            MessageBox.Show("Invalid Username or Password");
                            reader.Close();
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
  13. Now Run the Project and test it. The given code will provide the functionality of a login panel. It will check whether a student with the provided ID and Password exists in the database? It will display the messages in either of the cases.csharp7csharp8

Congratulations 🙂
We are done with connecting C# with mySQL database.

Stay with Protorials for more tutorials.
Have a nice day!

Advertisements

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