Linking C# Applications with MySQL Databases on Visual Studio

Visual Studio as we know is a very powerful IDE from Microsoft. It has build in languages like C# C++ and Visual Basic. Development with Visual Studio can be pretty fun and successful. Visual Studio also has methods for creating and connecting to Databases like ADO.NET technology.

However for some personal reason you dont want to use ADO.NET technology for your Databases or SQLServer for the simple reason that they are expensive. Perhaps you want your applications data to live forever, and never be gone if your hard drive burns or dies.


As we know hosting companies provide us with already installed MySQL and configured settings to jump right in and create databases to store our data. Linking your local Application with a hosted MySQL Database can be very effective if you want to store your data on outside computer and make them be there forever. There is a tool called MySQL Connector for .NET which allows us not only to connect to MySQL Databases but also use the old fashion style way of creating and querying databases. So lets get to work and see how can we achieve this approach.

First of all go ahead and download MySQL Connector for .NET by clicking in this link http://dev.mysql.com/downloads/connector/net/

The setup will prompt you to install MySQL Connector and you can freely choose any directory you want. Now go ahead and open Visual Studio whatever version you have, and create new C# Windows Forms Project. After your project is created to use the MySQL Connector you need to add a reference to the DLL file into your Project.

On Visual Studio right click on your Solution and go to Add Reference. A dialog will appear for you to choose whether you want to include the already given references or other.

















Click on the Browse tab to manually choose the location of your reference you want to include on your project. Navigate to the folder where you installed the MySQL Connector then go to Assemblies and inside this folder select the MySql.Data.dll file. Then click OK to import this file into your project




























The file is now referenced into your project however it will not be copied into your projects directory when you compile your application. And if your app is using this DLL file then it must reside in the same folder as the .exe file is in order for your application to run. To copy this file into your project when you compile it on the Solution Explorer select the MySql.Data reference you just imported and down on the Properties pane set the Copy Local attribute to true. This will copy the reference you imported on your applications folder when you compile.

Now comes the funny part. To use the MySQL connector you need to import classes and namespaces by using the "using" command on C#. On my project i added new Class onto my project and i named it Database.cs. Here i created a class which deals with connecting and disconnecting from the MySQL database. This class has two functions. OpenConnection ()and CloseConnection(). Their job is obvious. THe first function opens a connection with return success if everything goes OK. If something falls meaning if your database name or password are incorrect this function will return false and the connection will drop.

Database name and password including the hosting url are stored on local member variables. The values of these variables should be edited to match yours. This project expects that you have a hosting and you already created a database on your host.








Database.cs


using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System;

namespace Database
{

    class Database
    {
        public MySqlConnection mConnection;
        private readonly string mServer;
        private readonly string mDatabase;
        private readonly string mUid;
        private readonly string mPassword;

        //Constructor
        public Database()
        {
            mServer = "your server ip (123.122.1)";
            mDatabase = "database_name";
            mUid = "database_user";
            mPassword = "database_pass";

            string mstr = String.Format("server={0};user={1};database={2};port=3307;password={3};", mServer, mUid, mDatabase, mPassword);

            mConnection = new MySqlConnection(mstr);
        }

        //open connection to database
        public bool Connect()
        {
            try
            {
                mConnection.Open();
                return true;
            }
            catch(MySqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
                return false;
            }
        }

        //Close connection
        public bool CloseConnection()
        {
            try
            {
                mConnection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
    };
}

The code is self explanatory however lets add some detail. On the beginning to use the MySQL Connector commands we must use namespaces and classes and thus we do that by the following line of code


using MySql.Data.MySqlClient;

Variables


mServer = "your server ip (123.122.1)";
mDatabase = "database_name";
mUid = "database_user";
mPassword = "database_pass";

should be initialized with your server and database informations. Later on this information is used and a connection string is created with the values on these variables do make the connection happen.

string mstr = String.Format("server={0};user={1};database={2};port=3307;password={3};", mServer, mUid, mDatabase, mPassword);

And then  mConnection = new MySqlConnection(mstr); connects and verifies the given information. Then we can use the mConnection object to connect or disconnect from out database like this.

//Connect
mConnection.Open();

//Disconnect
mConnection.Close();


MySQLConnector of course supports queries for SELECT UPDATE CREATE DROP and all other queries that we use when manipulating tables on our database.

NOTE - Almost all hosting companies restrict their acconts by allowing the connections to be made just locally meaning that if you try to connect from a source different rather than from your hosting account the connection will fail for security purposes. And this means that if you try to connect from your application and you fail it is for the reason that your hosting company does not allows outside connections. You can contact your providers and ask from them to allow public connections or if you a re a resseller then you can go to your WHM and enable connections from all IPs.

If you use XAMPP (local server which is installed on your computer) then you can use a connection like this to test if your application works.


mServer = "localhost";
mDatabase = "database_name";
mUid = "root";
mPassword = "";

Browse More Articles

Tweet

Written by Alias

Thank you for taking your time to browse through our blog. We try to build as much rich content base as we can. We focus in modern technologies, computer science design and business.