How to connect to MySQL using ADO.NET

When I began my acquaintance with the ADO.NET technology I was immediately interested in the question: “How can I connect to MySQL using the ADO.NET technology”. I started looking for solutions. Now that I have implemented all this on my computer, I want to share my experience and skills with you. Let's first analyze what we need to implement this venture.

1. MySQL database server
2. Visual Studio (In my example, this is Visual Studio 2010)
3. Library for working with MySQL

I hope that you already have a MySQL database server and Visual Studio program installed. If not, then do the installation before proceeding to work. All is ready. Visual Studio is installed, the MySQL database server is installed. First of all, we need the MySQL dll library, which will help working with ADO.NET in the .NET Framework. You can download the library on the official MySQL website at: dev.mysql.com .
')
Two options are available for download on the site: the first is the installer, the second is an archive, the first option will be considered in the example. And so, downloaded? Installed? Fine, we go further, then we need to refer to the folder where we installed the MySQL dll library, my path to the library looks like this: C: \ Program Files \ MySQL \ MySQL Connector Net 6.4.4 \ Assemblies \ v2.0 in this folder, find and copy the file MySql.Date.dll to the buffer.

Create a console application in Visual Studio via File -> New -> Project (File -> New -> Project) or Ctrl + Shift + N. Select the language Visual C # console application clicks OK. We refer to the project folder where all the files of the newly created console application {project name} / bin / Debug / copy the court file MySql.Date.dll lie. In the solution browser (solution Explorer) in the menu “References (references)” you need to “Add a link (add a link)”.

As a result, in the solution browser (solution Explorer) in the menu “References (references)” a link to the dll library MySql.Data will appear. Very well, it now remains to connect this very library to our project; this is done very simply:

//    MySql.Date using MySql.Data.MySqlClient; 

We have already done half of the work; it remains to write the program code that will make the connection to the MySQL database and execute the queries. The first thing we need is setting the connection to the database:

  string host = "localhost"; //   string database = "mysql"; //    string user = "root"; //   string password = "password"; //   string Connect = "Database=" + database + ";Datasource=" + host + ";User=" + user + ";Password=" + password; 

We created 5 string variables in 4 of which we registered the settings for the database connection - the name of the local computer, the database name, user name and user password. All these settings were assigned to the Connect string variable. It stores the full connection to the database. Create a MySqlConnection object named mysql_connection and pass it a string to connect to Connect:

 MySqlConnection mysql_connection = new MySqlConnection(Connect); 

The MySqlConnection object is a database connection. The next step is to create a MySqlCommand object named mysql_query using the current connection, create a SQL query that will be stored in mysql_query. Object MySqlCommand - executes SQL commands.

 MySqlCommand mysql_query = mysql_connection.CreateCommand(); mysql_query.CommandText = "SELECT user FROM user;"; 

Next to connect and connect to the database, you need to call the .Open () method:

 mysql_connection.Open(); 

Now, to see the processed request, you need to create a MySqlDataReader object:

 MySqlDataReader mysql_result; 

To execute the SQL request for the console window, we need the ExecuteReader (), Read (), GetString () method and a while loop. The ExecuteReader () method executes the query and returns 0 or more rows of the result. Read () method - moves from one line to another until the end of the data is reached. The GetString () method retrieves the specific value to return.

 mysql_result = mysql_query.ExecuteReader(); while (mysql_result.Read()) { Console.WriteLine("{0}", mysql_result.GetString(0)); } 

At the end, when the request is executed, it is necessary to close the connection to the database using the .Close () method:

 mysql_connection.Close(); 

As a result, the program must show us the list of users that exist in the database. In my case, this is one root user you may have several. To consolidate this topic, let's implement a functionality that will display a little information about the user, namely, the user name, user password and local connection name. The full code of the program and the link where you can download the project is presented below.

 using System; using System.Collections.Generic; using System.Linq; using System.Text; //    MySql.Date using MySql.Data.MySqlClient; namespace MySQLConsole { class Program { static void Main(string[] args) { string host = "localhost"; //    string database = "mysql"; //    string user = "root"; //   string password = "password"; //   string Connect = "Database=" + database + ";Datasource=" + host + ";User=" + user + ";Password=" + password; //      MySqlConnection mysql_connection = new MySqlConnection(Connect); //  SQL  MySqlCommand mysql_query = mysql_connection.CreateCommand(); mysql_query.CommandText = "SELECT user FROM user;"; try { mysql_connection.Open(); MySqlDataReader mysql_result; mysql_result = mysql_query.ExecuteReader(); while (mysql_result.Read()) { Console.WriteLine("{0}", mysql_result.GetString(0)); } mysql_connection.Close(); string command; do { command = Console.ReadLine(); if (command != "quit") { mysql_query = new MySqlCommand("SELECT host, user, password FROM user WHERE user =\"" + command + "\";", mysql_connection); mysql_connection.Open(); mysql_result = mysql_query.ExecuteReader(); if (mysql_result.Read()) { Console.WriteLine("\n  : {0}", command); Console.WriteLine("HOST: {0}", mysql_result.GetString(0)); Console.WriteLine("USER: {0}", mysql_result.GetString(1)); Console.WriteLine("PASSWORD: {0}", mysql_result.GetString(2)); } else { Console.WriteLine(" {0}    !", command); } mysql_connection.Close(); } } while (command != "quit"); } catch { Console.WriteLine(" MySQL"); } } } 

Download the project source here.