Работа с базой данных MySQL с использованием C#

1- Introduction

В этой статье я покажу вам как работать с базой данных MySQL из С#, цели включают:
  1. Запросить (Query)
  2. Вставить (Insert)
  3. Обновить (update)
  4. Удалить (Delete)
  5. Вызов функций, процедуры С#,...
Документ использующий SIMPLEHR, является базой данных Schema ( Database Schema), часто используемая для примеров в руководствах на o7planning. org, вы можете создать такую schema в Oracle, MySQL или SQL Server. Посмотрите инструкцию здесь:

2- Connect to MySQL Database with C#

Создание проекта CsMySQLTutorial:
Проект создан:
You need to declare the libraries, and need a utility class ( DBUtils.cs) which help to connect to the database. With SQL Server database, you can see the instructions at:
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, или с SqlCommand - сервером SQL. К сожалению у вас будут большие трудности если хотите использовать один кодовый ресурс для разных баз данных.
Вы можете создать объект 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- Query

Например запросить данные используя 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)
      {
          // Get connection.
          MySqlConnection conn = DBUtils.GetDBConnection();
          conn.Open();
          try
          {
              QueryEmployee(conn);
          }
          catch (Exception e)
          {
              Console.WriteLine("Error: " + e);
              Console.WriteLine(e.StackTrace);
          }
          finally
          {
              // Close connection.
              conn.Close();
              // Freeing Resources.
              conn.Dispose();
          }      
          Console.Read();
      }

      private static void QueryEmployee(MySqlConnection conn)
      {
          string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee";

          // Create command.
          // Tạo một đối tượng Command.
          MySqlCommand cmd = new MySqlCommand();

          // Set connection for command.
          cmd.Connection = conn;
          cmd.CommandText = sql;

         
          using (DbDataReader reader = cmd.ExecuteReader())
          {
              if (reader.HasRows)
              {
                 
                  while (reader.Read())
                  {
                      // Get index of Column Emp_ID in query statement.
                      int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                     

                      long empId =  Convert.ToInt64(reader.GetValue(0));
                     
                      // Index of Emp_ID = 1
                      string empNo = reader.GetString(1);
                      int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                      string empName = reader.GetString(empNameIndex);

                      // Index of column Mng_Id.
                      int mngIdIndex = reader.GetOrdinal("Mng_Id");

                      long? mngId = null;

                      // If a column is nullable always check for DBNull...
                      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);
                  }
              }
          }

      }
  }

}
Запуск примера:
Node: The reason for the "using" statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn't require explicit code to ensure that this happens.
// Use 'using' with IDispose objects.

using (DbDataReader reader = cmd.ExecuteReader())
{
   // Code 
}


// Equivalent to visual writing:

DbDataReader reader = cmd.ExecuteReader();
try
{
   // Code 
}
finally
{
    reader.Dispose();
}

5- Insert

Это пример вставления (insert) дополнительное заявление в 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)
         {

           // Get Connection
           MySqlConnection connection = DBUtils.GetDBConnection();
           connection.Open();
           try
           {    
               // Insert statement.
               string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                + " values (@grade, @highSalary, @lowSalary) ";

               MySqlCommand cmd = connection.CreateCommand();
               cmd.CommandText = sql;  

               // Create Parameter.
               MySqlParameter gradeParam = new MySqlParameter("@grade",SqlDbType.Int);
               gradeParam.Value = 3;
               cmd.Parameters.Add(gradeParam);

               // Add parameter @highSalary (Write shorter)
               MySqlParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
               highSalaryParam.Value = 20000;

               // Add parameter @lowSalary (more shorter).
               cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000;

               // Execute (for Delete,Insert or Update).
               int rowCount = cmd.ExecuteNonQuery();

               Console.WriteLine("Row Count affected = " + rowCount);
           }
           catch (Exception e)
           {
               Console.WriteLine("Error: " + e);
               Console.WriteLine(e.StackTrace);
           }
           finally
           {
               // Close connection
               connection.Close();
               // Freeing Resources
               connection.Dispose();
               connection = null;
           }
         

           Console.Read();

        }
   }

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

6- Update

Пример обновления (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)
       {
           // Get connection.
           MySqlConnection conn  = DBUtils.GetDBConnection();
           conn.Open();
           try
           {
               string sql = "Update Employee set Salary = @salary where Emp_Id = @empId";

               // Create command.
               MySqlCommand cmd = new MySqlCommand();

               // Set connection for command.
               cmd.Connection = conn;
               // Set command text.
               cmd.CommandText = sql;

               // Add and set value for parameter.
               cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
               cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369;

               // Execute (for 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
           {
               // Close connection
               conn.Close();
               // Freeing resources.
               conn.Dispose();
               conn = null;
           }


           Console.Read();

       }
   }

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

7- Delete

Пример использования С# для удаления данных с SQL.
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)
       {
           // Get connection to database.
           MySqlConnection conn  = DBUtils.GetDBConnection();
           conn.Open();
           try
           {        

               string sql = "Delete from Salary_Grade where Grade = @grade ";

               // Create Command object.
               MySqlCommand cmd = new MySqlCommand();

               // Set Connection for command.
               cmd.Connection = conn;
               // Set Command Text.
               cmd.CommandText = sql;

               cmd.Parameters.Add("@grade", SqlDbType.Int).Value = 3;  

               // Execute (for 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
           {
               // Close connection.
               conn.Close();
               // Freeing resources.
               conn.Dispose();
               conn = null;
           }


           Console.Read();

       }
   }

}

8- Call procedures in 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
   {
       static void Main(string[] args)
       {
           MySqlConnection conn = DBUtils.GetDBConnection();
           conn.Open();
           try
           {
               // 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

               // Create a Command object to call procedure Get_Employee_Info
               MySqlCommand cmd = new MySqlCommand("Get_Employee_Info", conn);

               // Command Type is StoredProcedure
               cmd.CommandType = CommandType.StoredProcedure;

               // Add parameter @p_Emp_Id and set value = 100
               cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value =100;

               // Add parameter @v_Emp_No type of 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));

               // Register parameter @v_Emp_No is 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;

               // Execute procedure.
               cmd.ExecuteNonQuery();

               // Get output values.
               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();
       }
   }


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

9- Call procedures in 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
   {
       static void Main(string[] args)
       {
           MySqlConnection conn = DBUtils.GetDBConnection();
           conn.Open();
           try
           {
               // Get_Emp_No                
               // @p_Emp_Id       Integer

               // Create a Command object to call Get_Emp_No function.
               MySqlCommand cmd = new MySqlCommand("Get_Emp_No", conn);

               // CommandType is StoredProcedure
               cmd.CommandType = CommandType.StoredProcedure;

               // Add parameter @p_Emp_Id and set value = 100.
               cmd.Parameters.AddWithValue("@p_Emp_Id", MySqlDbType.Int32).Value = 100;

               // Create result Parameter.
               MySqlParameter resultParam = new MySqlParameter("@Result", MySqlDbType.VarChar);            

               //  
               // Sét nó là kiểu trả về (ParameterDirection.ReturnValue)
               resultParam.Direction = ParameterDirection.ReturnValue;

               // Add to parameters
               cmd.Parameters.Add(resultParam);
               
               // Call function.
               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();
       }
   }

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

10- ExecuteScalar

MySqlCommand.ExecuteScalar() представляет собой метод, используемый для выполнения операторов SQL, он возвращает значение первого столбца первой строки заявлений SQL в результатах.
-- The following statement returns only one value.

Select count(*) from Employee;

-- Or

Select Max(e.Salary) From Employee e;
Example:
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 return first column of first row.
               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