Работа с базой данных MySQL на C#

Введение
Подключить C# к MySql Database
MySqlCommand
Запрос данных
Вставить данные
Обновить данные
Удаление данных
Вызов процедур в C#
Вызов функции в C#
ExecuteScalar

1. Введение

В этой статье я покажу вам как работать с базой данных MySQL из С#, цели включают:

Запросить (Query)
Вставить (Insert)
Обновить (update)
Удалить (Delete)
Вызов функции, процедуры из С#,...

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

Пример базы данных

2. Подключить C# к MySql Database

Создание проекта CsMySQLTutorial:

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

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

Подключиться к базе данных MySQL в C#

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);
       }
     
   }
}

3. MySqlCommand

В С# чтобы работать с базой данных 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);

4. Запрос данных

Например запросить данные используя 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);
                    }
                }
            }

        }
    }

}

Запуск примера:

--------------------
empIdIndex:0
EmpId:7369
EmpNo:E7369
EmpName:SMITH
MngId:7902
--------------------
empIdIndex:0
EmpId:7499
EmpNo:E7499
EmpName:ALLEN
MngId:7698
--------------------
empIdIndex:0
EmpId:7521
EmpNo:E7521
EmpName:WARD
MngId:7698
--------------------
empIdIndex:0
EmpId:7566
EmpNo:E7566
EmpName:JONES
MngId:7839
.....

Примечание: Команда using используется для гарантии, что этот объект будет уничтожен (dispose) сразу после того как он выйдет за рамки, без необходимости написания кода визуально.
// Использовать ключевое слово 'using' для объектов вида IDispose.
// (Является объектом Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
   // Code использует reader
}

// Соответствует с традиционным способом написания.
DbDataReader reader = cmd.ExecuteReader();
try
{
   // Code использует reader
}
finally
{
	// Вызвать метод уничтожения объекта
	// Освободить ресурс.
    reader.Dispose();
}

5. Вставить данные

Это пример вставления (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();
  
         }
    }

}

Запуск примера:

Row Count affected = 1

6. Обновить данные

Пример обновления (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();

        }
    }

}

Запуск примера:

Row Count affected = 1

7. Удаление данных

Пример использования С# для удаления данных в 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();

        }
    }

}

8. Вызов процедур в C#

Вам нужно создать простую процедуру в 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();
        }
    }


}

Запуск примера:

Emp No: E100
First Name: Michael
Last Name: Smith
Hire Date: 2/11/2016 12:00:00 AM

9. Вызов функции в C#

Вам нужна простая функция и вызваеть ее в С#.

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();
        }
    }

}

Запуск примера:

Emp No: E100

10. ExecuteScalar

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();
        }
    }
}

Запуск примера:

Emp Count: 14

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

Работа с базой данных MySQL на C#

1. Введение

2. Подключить C# к MySql Database

3. MySqlCommand

4. Запрос данных

5. Вставить данные

6. Обновить данные

7. Удаление данных

8. Вызов процедур в C#

9. Вызов функции в C#

10. ExecuteScalar

Pуководства C#

Pуководства C#