How to connect to MySQL using ADO.NET
When I began my acquaintance with ADO.NET technology, I immediately became interested in the question: "How can I connect to MySQL using 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 understand what we need to implement this venture.
I hope you already have the MySQL database server and Visual Studio installed. If not, then do the installation before you get started. All is ready. Visual Studio is installed, the MySQL database server is installed. First of all, we will need the MySQL dll library, which will help to work 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 downloading on the site: the first is the installer, the second is the archive, the first option will be considered in the example. And so, downloaded? Installed? Great, move on, then we need to look at the folder in which 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 we find the folder and copy the file MySql.Date.dll to the buffer.
We create a console application in Visual Studio via File -> New -> Project (File -> New -> Project) or Ctrl + Shift + N. Select the language Visual C # the console application clicks OK. We turn to the project folder where all the files lie, just created console application {project name} / bin / Debug / copy the court file MySql.Date.dll. In the solution explorer in the “references” menu, you need to “add a link”.
As a result, a link to the dll library MySql.Data will appear in the solution explorer in the "references" menu. Very good, now it remains to connect this very library to our project, this is done very simply:
We have already done half the work, it remains to write program code that will make a connection to the MySQL database and execute queries. The first thing we need is the database connection settings:
We created 5 string variables in 4 of which we registered the settings for connecting to the database - the name of the local computer, the database name, username and password of the user; all these settings were assigned to the Connect variable in the string; the full connection to the database is stored in it. We create a MySqlConnection object with the name mysql_connection and pass it a line to connect Connect:
The MySqlConnection object is a database connection. The next step, we create a MySqlCommand object with the name mysql_query using the current connection and create an SQL query that will be stored in mysql_query. MySqlCommand object - executes SQL commands.
Next, to connect and connect to the database, you need to call the .Open () method:
Now, to see the processed request, you need to create the MySqlDataReader object:
To execute the SQL query on the console window, we need the ExecuteReader (), Read (), GetString () method and the 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 be returned.
At the end, when the request is executed, you must close the database connection using the .Close () method:
As a result, the program should show us a list of users that exist in the database. In my case, this is one root user, you can have several of them. To consolidate this topic, let's implement a functional that will display us a little information about the user, namely username, user password and local connection name. The full program code and a link where you can download the project are presented below.
Download the project source here.
- MySQL database server
- Visual Studio (In my example, this is Visual Studio 2010)
- Library for working with MySQL
I hope you already have the MySQL database server and Visual Studio installed. If not, then do the installation before you get started. All is ready. Visual Studio is installed, the MySQL database server is installed. First of all, we will need the MySQL dll library, which will help to work 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 downloading on the site: the first is the installer, the second is the archive, the first option will be considered in the example. And so, downloaded? Installed? Great, move on, then we need to look at the folder in which 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 we find the folder and copy the file MySql.Date.dll to the buffer.
We create a console application in Visual Studio via File -> New -> Project (File -> New -> Project) or Ctrl + Shift + N. Select the language Visual C # the console application clicks OK. We turn to the project folder where all the files lie, just created console application {project name} / bin / Debug / copy the court file MySql.Date.dll. In the solution explorer in the “references” menu, you need to “add a link”.
As a result, a link to the dll library MySql.Data will appear in the solution explorer in the "references" menu. Very good, now it 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 the work, it remains to write program code that will make a connection to the MySQL database and execute queries. The first thing we need is the database connection settings:
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 connecting to the database - the name of the local computer, the database name, username and password of the user; all these settings were assigned to the Connect variable in the string; the full connection to the database is stored in it. We create a MySqlConnection object with the name mysql_connection and pass it a line to connect Connect:
MySqlConnection mysql_connection = new MySqlConnection(Connect);
The MySqlConnection object is a database connection. The next step, we create a MySqlCommand object with the name mysql_query using the current connection and create an SQL query that will be stored in mysql_query. MySqlCommand object - 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 the MySqlDataReader object:
MySqlDataReader mysql_result;
To execute the SQL query on the console window, we need the ExecuteReader (), Read (), GetString () method and the 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 be returned.
mysql_result = mysql_query.ExecuteReader();
while (mysql_result.Read())
{
Console.WriteLine("{0}", mysql_result.GetString(0));
}
At the end, when the request is executed, you must close the database connection using the .Close () method:
mysql_connection.Close();
As a result, the program should show us a list of users that exist in the database. In my case, this is one root user, you can have several of them. To consolidate this topic, let's implement a functional that will display us a little information about the user, namely username, user password and local connection name. The full program code and a link where you can download the project are 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.