Работа с базой данных MySQL на C#
View more Tutorials:
В этой статье я покажу вам как работать с базой данных MySQL из С#, цели включают:
- Запросить (Query)
- Вставить (Insert)
- Обновить (update)
- Удалить (Delete)
- Вызов функции, процедуры из С#,...

Документ использующий SIMPLEHR, Database Schema часто используется для примеров в руководствах на o7planning.org, вы можете создать такую schema в Oracle, MySQL или SQL Server. Посмотрите инструкцию здесь:
Создание проекта CsMySQLTutorial:

Проект создан:


Вам нужно объявить библиоткеи, помогающие подключиться к MySQL и нужен один утилитарный класс (DBUtils.cs) для подключения к Database. С MySQL Database, вы можете посмотреть инструкцию по ссылке:

DBMySQLUtils.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using MySql.Data.MySqlClient; namespace Tutorial.SqlConn { class DBMySQLUtils { public static MySqlConnection GetDBConnection(string host, int port, string database, string username, string password) { // Connection String. String connString = "Server=" + host + ";Database=" + database + ";port=" + port + ";User Id=" + username + ";password=" + password; MySqlConnection conn = new MySqlConnection(connString); return conn; } } }
DBUtils.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using MySql.Data.MySqlClient; namespace Tutorial.SqlConn { class DBUtils { public static MySqlConnection GetDBConnection() { string host = "192.168.205.130"; int port = 3306; string database = "simplehr"; string username = "root"; string password = "1234"; return DBMySQLUtils.GetDBConnection(host, port, database, username, password); } } }

В С# чтобы работать с базой данных MySQL, например query, insert, update, delete используйте объект MySqlCommand, MySqlCommand - это расширенный класс из DbCommand. В случае если вам нужно query, insert, update или delete в базе данных Oracle используйте OracleCommand, или с SQL Server это SqlCommand.. К сожалению у вас будут большие трудности если хотите использовать один кодовый ресурс для разных баз данных.
Вы можете создать объект MySqlCommand, чтобы работать с базой данных MySQL:
MySqlConnection conn = DBUtils.GetDBConnection(); // Way 1: // Create a command associated with the Connection. MySqlCommand cmd = conn.CreateCommand(); // Set Command Text cmd.CommandText = sql; // Way 2 : // Create a Command MySqlCommand cmd = new MySqlCommand(sql); // Set connection for command. cmd.Connection = conn; // Way 3: // Create a command associated with the Connection. MySqlCommand cmd = new MySqlCommand(sql, conn);
Например запросить данные используя C#.

QueryDataExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Tutorial.SqlConn; using System.Data.Common; using MySql.Data.MySqlClient; namespace CsMySQLTutorial { class QueryDataExample { static void Main(string[] args) { // Получить объект Connection подключенный к DB. MySqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { QueryEmployee(conn); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { // Закрыть соединение. conn.Close(); // Уничтожить объект, освободить ресурс. conn.Dispose(); } Console.Read(); } private static void QueryEmployee(MySqlConnection conn) { string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee"; // Создать объект Command. MySqlCommand cmd = new MySqlCommand(); // Сочетать Command с Connection. cmd.Connection = conn; cmd.CommandText = sql; using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { // Индекс (index) столбца Emp_ID в команде SQL. int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0 long empId = Convert.ToInt64(reader.GetValue(0)); // Столбец Emp_No имеет index = 1. string empNo = reader.GetString(1); int empNameIndex = reader.GetOrdinal("Emp_Name");// 2 string empName = reader.GetString(empNameIndex); // Индекс (index) столбца Mng_Id в команде SQL. int mngIdIndex = reader.GetOrdinal("Mng_Id"); long? mngId = null; // Проверить значение данного столбца может являться null или нет. if (!reader.IsDBNull(mngIdIndex)) { mngId = Convert.ToInt64(reader.GetValue(mngIdIndex)); } Console.WriteLine("--------------------"); Console.WriteLine("empIdIndex:" + empIdIndex); Console.WriteLine("EmpId:" + empId); Console.WriteLine("EmpNo:" + empNo); Console.WriteLine("EmpName:" + empName); Console.WriteLine("MngId:" + mngId); } } } } } }
Запуск примера:

Примечание: Команда using используется для гарантии, что этот объект будет уничтожен (dispose) сразу после того как он выйдет за рамки, без необходимости написания кода визуально.// Использовать ключевое слово 'using' для объектов вида IDispose. // (Является объектом Interface IDispose). using (DbDataReader reader = cmd.ExecuteReader()) { // Code использует reader } // Соответствует с традиционным способом написания. DbDataReader reader = cmd.ExecuteReader(); try { // Code использует reader } finally { // Вызвать метод уничтожения объекта // Освободить ресурс. reader.Dispose(); }
Это пример вставления (insert) записи (record) в Salary_Grade.
InsertDataExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Tutorial.SqlConn; using System.Data.Common; using System.Data; using MySql.Data.MySqlClient; namespace CsMySQLTutorial { class InsertDataExample { static void Main(string[] args) { // Получить соединение к базе данных. MySqlConnection connection = DBUtils.GetDBConnection(); connection.Open(); try { // Команда Insert. string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) " + " values (@grade, @highSalary, @lowSalary) "; MySqlCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; // Создать объект Parameter. MySqlParameter gradeParam = new MySqlParameter("@grade",SqlDbType.Int); gradeParam.Value = 3; cmd.Parameters.Add(gradeParam); // Добавить параметр @highSalary (Написать кратко). MySqlParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float); highSalaryParam.Value = 20000; // Добавить параметр @lowSalary (Написать кратко). cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; // Выполнить Command (использованная для delete, insert, update). int rowCount = cmd.ExecuteNonQuery(); Console.WriteLine("Row Count affected = " + rowCount); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { connection.Close(); connection.Dispose(); connection = null; } Console.Read(); } } }
Запуск примера:


Пример обновления (update) в С#.
UpdateExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using MySql.Data.MySqlClient; using Tutorial.SqlConn; using System.Data; namespace CsMySQLTutorial { class UpdateExample { static void Main(string[] args) { MySqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string sql = "Update Employee set Salary = @salary where Emp_Id = @empId"; MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; // Добавить и настроить значение для параметра. cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850; cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369; // Выполнить Command (Использованная для delete, insert, update). int rowCount = cmd.ExecuteNonQuery(); Console.WriteLine("Row Count affected = " + rowCount); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); conn = null; } Console.Read(); } } }
Запуск примера:

Пример использования С# для удаления данных в MySQL.
DeleteExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using MySql.Data.MySqlClient; using Tutorial.SqlConn; using System.Data; namespace CsMySQLTutorial { class DeleteExample { static void Main(string[] args) { // Получить подключение к базе данных. MySqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string sql = "Delete from Salary_Grade where Grade = @grade "; // Создать объект Command. MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.Add("@grade", SqlDbType.Int).Value = 3; // Выполнить команду Command (Использованная для delete,insert, update). int rowCount = cmd.ExecuteNonQuery(); Console.WriteLine("Row Count affected = " + rowCount); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); conn = null; } Console.Read(); } } }
Вам нужно создать простую процедуру в MySQL и вызвать его в С#:
Get_Employee_Info
DELIMITER $$ -- This procedure retrieves information of an employee, -- Input parameter: p_Emp_ID (Integer) -- There are four output parameters v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date CREATE PROCEDURE get_Employee_Info(p_Emp_ID Integer, out v_Emp_No Varchar(50) , out v_First_Name Varchar(50) , Out v_Last_name Varchar(50) , Out v_Hire_date Date) BEGIN set v_Emp_No = concat( 'E' , Cast(p_Emp_Id as char(15)) ); -- set v_First_Name = 'Michael'; set v_Last_Name = 'Smith'; set v_Hire_date = curdate(); END
CallProcedureExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Tutorial.SqlConn; using System.Data; using MySql.Data.MySqlClient; namespace CsMySQLTutorial { class CallProcedureExample { // Get_Employee_Info // @p_Emp_Id Integer , // @v_Emp_No Varchar(50) OUTPUT // @v_First_Name Varchar(50) OUTPUT // @v_Last_Name Varchar(50) OUTPUT // @v_Hire_Date Date OUTPUT static void Main(string[] args) { MySqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { // Создать объект Command, чтобы вызвать процедуру Get_Employee_Info. MySqlCommand cmd = new MySqlCommand("Get_Employee_Info", conn); // Настроить вид у Command как StoredProcedure. cmd.CommandType = CommandType.StoredProcedure; // Добавить параметр @p_Emp_Id и настроить для него значение = 100. cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value =100; // Добавить параметр @v_Emp_No вида Varchar(20). cmd.Parameters.Add(new MySqlParameter("@v_Emp_No", MySqlDbType.VarChar, 20)); cmd.Parameters.Add(new MySqlParameter("@v_First_Name", MySqlDbType.VarChar, 50)); cmd.Parameters.Add(new MySqlParameter("@v_Last_Name", MySqlDbType.VarChar, 50)); cmd.Parameters.Add(new MySqlParameter("@v_Hire_Date", MySqlDbType.Date)); // Зарегистрировать параметр @v_Emp_No как OUTPUT. cmd.Parameters["@v_Emp_No"].Direction = ParameterDirection.Output; cmd.Parameters["@v_First_Name"].Direction = ParameterDirection.Output; cmd.Parameters["@v_Last_Name"].Direction = ParameterDirection.Output; cmd.Parameters["@v_Hire_Date"].Direction = ParameterDirection.Output; // Выполнить процедуру. cmd.ExecuteNonQuery(); // Получить выходные значения. string empNo = cmd.Parameters["@v_Emp_No"].Value.ToString(); string firstName = cmd.Parameters["@v_First_Name"].Value.ToString(); string lastName = cmd.Parameters["@v_Last_Name"].Value.ToString(); DateTime hireDate = (DateTime)cmd.Parameters["@v_Hire_Date"].Value; Console.WriteLine("Emp No: " + empNo); Console.WriteLine("First Name: " + firstName); Console.WriteLine("Last Name: " + lastName); Console.WriteLine("Hire Date: " + hireDate); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } Console.Read(); } } }
Запуск примера:

Вам нужна простая функция и вызваеть ее в С#.
Get_Emp_No
DROP function if Exists `Get_Emp_No`; -- When programming the function / procedure you need to use semicolon -- to separate the different commands. -- Use DELIMITER $$ to allow use of semicolons. DELIMITER $$ CREATE Function Get_Emp_No (p_Emp_Id Integer) Returns Varchar(50) Begin return concat('E', CAST(p_Emp_Id as char)) ; END;
CallFunctionExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Tutorial.SqlConn; using System.Data; using MySql.Data.MySqlClient; namespace CsMySQLTutorial { class CallFunctionExample { // Function: Get_Emp_No // Parameter: @p_Emp_Id Integer static void Main(string[] args) { MySqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { // Создать объект Command для вызова функции Get_Emp_No. MySqlCommand cmd = new MySqlCommand("Get_Emp_No", conn); // Видом Command является StoredProcedure cmd.CommandType = CommandType.StoredProcedure; // Добавить параметр @p_Emp_Id и настроить Value = 100. cmd.Parameters.AddWithValue("@p_Emp_Id", MySqlDbType.Int32).Value = 100; // Создать объект MySqlParameter, // чтобы сохранить возвращенное значение при вызове функции. MySqlParameter resultParam = new MySqlParameter("@Result", MySqlDbType.VarChar); resultParam.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(resultParam); // Вызов функции. cmd.ExecuteNonQuery(); string empNo = null; if (resultParam.Value != DBNull.Value) { empNo = (string)resultParam.Value; } Console.WriteLine("Emp No: " + empNo); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } Console.Read(); } } }
Запуск примера:

MySqlCommand.ExecuteScalar() представляет собой метод, используемый для выполнения команды SQL, он возвращает значение первого столбца первой строки.
-- Следующая команда возвращает единственное значение Select count(*) from Employee; -- Или Select Max(e.Salary) From Employee e;
Пример:
ExecuteScalarExample.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using MySql.Data.MySqlClient; using System.Data; using Tutorial.SqlConn; namespace CsMySQLTutorial { class ExecuteScalarExample { static void Main(string[] args) { MySqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { MySqlCommand cmd = new MySqlCommand("Select count(*) From Employee", conn); cmd.CommandType = CommandType.Text; // Метод ExecuteScalar возвращает значение первого столбца в первой строке. object countObj = cmd.ExecuteScalar(); int count = 0; if (countObj != null) { count = Convert.ToInt32(countObj); } Console.WriteLine("Emp Count: " + count); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } Console.Read(); } } }
Запуск примера:

Вы так же можете вызвать функцию MySQL используя метод ExecuteScalar, смотрите пример ниже:
- TODO