Làm việc với cơ sở dữ liệu MySQL trong C#

Giới thiệu
Kết nối C# vào MySql Database
MySqlCommand
Truy vấn dữ liệu
Insert dữ liệu
Update dữ liệu
Xóa dữ liệu
Gọi thủ tục trong C#
Gọi hàm trong C#
ExecuteScalar

1. Giới thiệu

Trong tài liệu này tôi sẽ hướng dẫn bạn thao tác với MySQL Database từ C#, mục tiêu bao gồm:

Query
Insert
Update
Delete
Gọi hàm, thủ tục từ C#,...

Tài liệu sử dụng SIMPLEHR, một Database Schema ví dụ được sử dụng trong nhiều hướng dẫn trên o7planning.org, bạn có thể tạo Schema này trên Oracle, MySQL hoặc SQL Server. Bạn có thể xem hướng dẫn tại:

Cơ sở dữ liệu mẫu

2. Kết nối C# vào MySql Database

Tạo project CsMySQLTutorial:

Project đã được tạo ra.

Bạn cần khai báo thư viện giúp kết nối vào MySQL và cần một class tiện ích (DBUtils.cs) giúp kết nối vào Database. Với MySQL Database, bạn có thể xem hướng dẫn tại:

Kết nối cơ sở dữ liệu MySQL trong 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

Trong C# để thao tác với MySQL Database, chẳng hạn query, insert, update, delete bạn sử dụng một đối tượng MySqlCommand, MySqlCommand là một class mở rộng từ DbCommand. Trong trường hợp bạn cần query, insert,update hoặc delete trong Oracle Database bạn cần sử dụng OracleCommand, hoặc với SQL Server là SqlCommand. Thật đáng tiếc là bạn sẽ rất khó khăn nếu muốn sử dụng một mã nguồn cho các Database khác nhau.

Bạn có thể tạo đối tượng MySqlCommand để thao tác với MySQL Database:

MySqlConnection conn = DBUtils.GetDBConnection();

// Cách 1:

// Tạo một Command liên hợp với Connection.
MySqlCommand cmd = conn.CreateCommand();

// Sét Command Text
cmd.CommandText = sql;

// Cách 2:

// Tạo mới một Command
MySqlCommand cmd = new MySqlCommand(sql);

// Liên hợp Command với Connection.
cmd.Connection = conn;


// Cách 3:

// Tạo một đối tượng Command liên hợp với Connection

MySqlCommand cmd = new MySqlCommand(sql, conn);

4. Truy vấn dữ liệu

Ví dụ truy vấn dữ liệu sử dụng 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)
        {
            // Lấy ra đối tượng Connection kết nối vào DB.
            MySqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
                QueryEmployee(conn);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                // Đóng kết nối.
                conn.Close();
                // Tiêu hủy đối tượng, giải phóng tài nguyên.
                conn.Dispose();
            }       
            Console.Read();
        }

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

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

            // Liên hợp Command với Connection.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Chỉ số (index) của cột Emp_ID trong câu lệnh SQL.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // Cột Emp_No có index = 1.
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);

                        // Chỉ số (index) của cột Mng_Id trong câu lệnh SQL.
                        int mngIdIndex = reader.GetOrdinal("Mng_Id");

                        long? mngId = null;

                        // Kiểm tra giá trị của cột này có thể null hay không.
                        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);
                    }
                }
            }

        }
    }

}

Chạy ví dụ:

--------------------
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
.....

Chú ý: Câu lệnh using sử dụng để đảm bảo rằng đối tượng sẽ bị tiêu hủy (dispose) ngay sau khi nó ra khỏi phạm vi, mà không cần phải đòi hỏi phải viết code một cách trực quan.
// Sử dụng từ khóa 'using' đối với các đối tượng kiểu IDispose.
// (Là đối tượng của Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
   // Code sử dụng reader
}

// Tương đương với cách viết truyền thống.
DbDataReader reader = cmd.ExecuteReader();
try
{
   // Code sử dụng reader
}
finally
{
    // Gọi phương thức tiêu hủy đối tượng
    // Giải phóng tài nguyên.
    reader.Dispose();
}

5. Insert dữ liệu

Ví dụ sau trèn thêm một bản ghi (record) vào bảng 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) 
          {
 
            // Lấy ra kết nối tới cơ sở dữ liệu.
            MySqlConnection connection = DBUtils.GetDBConnection();
            connection.Open();
            try
            {    
                // Câu lệnh Insert.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

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

                // Tạo một đối tượng Parameter.
                MySqlParameter gradeParam = new MySqlParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Thêm tham số @highSalary (Viết ngắn hơn).
                MySqlParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Thêm tham số @lowSalary (Viết ngắn hơn nữa).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Thực thi Command (Dùng cho 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();
  
         }
    }

}

Chạy ví dụ:

Row Count affected = 1

6. Update dữ liệu

Ví dụ update trong C#.

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;

                // Thêm và sét đặt giá trị cho tham số.
                cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
                cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369; 

                // Thực thi Command (Dùng cho 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();

        }
    }

}

Chạy ví dụ:

Row Count affected = 1

7. Xóa dữ liệu

Ví dụ sử dụng C# xóa dữ liệu trong 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)
        {
            // Lấy ra một kết nối tới cơ sở dữ liệu.
            MySqlConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            {

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

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

              
                cmd.Connection = conn;
              
                cmd.CommandText = sql; 

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

                // Thực thi Command (Dùng cho 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. Gọi thủ tục trong C#

Bạn cần tạo ra một thủ tục đơn giản trong MySQL và gọi nó trong C#:

Get_Employee_Info

DELIMITER $$

-- Thủ tục này lấy ra thông tin của nhân viên
-- Đầu vào: p_Emp_ID (Integer)
-- Có 4 tham số đầu ra 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
            {

                // Tạo một đối tượng Command gọi thủ tục Get_Employee_Info.
                MySqlCommand cmd = new MySqlCommand("Get_Employee_Info", conn);

                // Sét đặt Kiểu của Command là StoredProcedure.
                cmd.CommandType = CommandType.StoredProcedure;

                // Thêm tham số @p_Emp_Id và sét giá trị cho nó = 100.
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value =100;
                
                // Thêm tham số @v_Emp_No kiểu 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)); 

                // Đăng ký tham số @v_Emp_No là 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;
 
                // Thực thi thủ tục.
                cmd.ExecuteNonQuery();

                // Lấy các giá trị đầu ra.
                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();
        }
    }


}

Chạy ví dụ:

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

9. Gọi hàm trong C#

Bạn cần một hàm đơn giản và gọi nó trong C#.

Get_Emp_No

DROP function  if Exists `Get_Emp_No`;

-- Khi lập trình hàm/thủ tục bạn cần sử dụng dấu chấm phẩy để
-- ngăn cách các câu lệnh khác nhau.
-- Sử dụng DELIMITER $$ để cho phép sử dụng dấu chấm phẩy.
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
            { 
                // Tạo một đối tượng Command để gọi hàm Get_Emp_No.
                MySqlCommand cmd = new MySqlCommand("Get_Emp_No", conn);

                // Kiểu của Command là StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Thêm tham số @p_Emp_Id và sét Value = 100.
                cmd.Parameters.AddWithValue("@p_Emp_Id", MySqlDbType.Int32).Value = 100;

                // Tạo đối tượng MySqlParameter, 
                // để lưu trữ giá trị trả về khi gọi hàm.  
                MySqlParameter resultParam = new MySqlParameter("@Result", MySqlDbType.VarChar);             

           
                resultParam.Direction = ParameterDirection.ReturnValue;

               
                cmd.Parameters.Add(resultParam);
                
                // Gọi hàm.
                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();
        }
    }

}

Chạy ví dụ:

Emp No: E100

10. ExecuteScalar

MySqlCommand.ExecuteScalar() là một phương thức sử dụng để thực thi câu lệnh SQL trả về giá trị của cột đầu tiên tại dòng đầu tiên.

-- Câu lệnh sau trả về duy nhất một giá trị.
Select count(*) from Employee;

-- Hoặc
Select Max(e.Salary) From Employee e;

Ví dụ:

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;

                // Phương thức ExecuteScalar trả về giá trị của cột đầu tiên trên dòng đầu tiên.
                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();
        }
    }
}

Chạy ví dụ:

Emp Count: 14

Bạn cũng có thể gọi một hàm của MySQL sử dụng phương thức ExecuteScalar, xem ví dụ sau:

Làm việc với cơ sở dữ liệu MySQL trong C#

1. Giới thiệu

2. Kết nối C# vào MySql Database

3. MySqlCommand

4. Truy vấn dữ liệu

5. Insert dữ liệu

6. Update dữ liệu

7. Xóa dữ liệu

8. Gọi thủ tục trong C#

9. Gọi hàm trong C#

10. ExecuteScalar

Các hướng dẫn lập trình C#

Các hướng dẫn lập trình C#

Các bài viết mới nhất