Work with MySQL database in C#

Introduction
Connect to MySQL Database with C#
MySqlCommand
Query
Insert
Update
Delete
Call procedures in C#
Call procedures in C#
ExecuteScalar

1. Introduction

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:

Sample Database

2. Connect to MySQL Database with C#

Create project CsMySQLTutorial:

Project is created.

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:

Connect to MySQL database in CSharp

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

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

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)
        {
            // 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();
                // Dispose object, 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.
            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);
                    }
                }
            }

        }
    }

}

Running the example:

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

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 the keyword 'using' for IDispose objects.
using (DbDataReader reader = cmd.ExecuteReader())
{
   // The code uses a reader
}

// Equivalent to traditional writing:
DbDataReader reader = cmd.ExecuteReader();
try
{
   // The code uses a reader
}
finally
{
    // Call method to dispose object
    // Freeing resources.
    reader.Dispose();
}

5. Insert

Ví dụ sau insert thêm một bản ghi 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) 
          {
 
            // 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 Command (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
            {  
                connection.Close(); 
                connection.Dispose();
                connection = null;
            }
           

            Console.Read();
  
         }
    }

}

Running the example:

Row Count affected = 1

6. Update

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;

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

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


            Console.Read();

        }
    }

}

Running the example:

Row Count affected = 1

7. Delete

Ví dụ sử dụng C# xóa dữ liệu trong 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();

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

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

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


            Console.Read();

        }
    }

}

8. Call procedures in 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 $$

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

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

                // Set Command Type to 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();
        }
    }


}

Running the example:

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

9. Call procedures in 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`;
 
-- 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
            { 
                // 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 the MySqlParameter object, 
                // to store the return value when calling the function.
                MySqlParameter resultParam = new MySqlParameter("@Result", MySqlDbType.VarChar);             

           
                resultParam.Direction = ParameterDirection.ReturnValue;

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

}

Running the example:

Emp No: E100

10. ExecuteScalar

MySqlCommand.ExecuteScalar() is a method used to execute SQL statements, it returns the value of the first column of the first row in results of SQL statements.

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

                // The ExecuteScalar method returns the value of the first column on the 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();
        }
    }
}

Running the example:

Emp Count: 14

You can also call a MySQL function using the ExecuteScalar method, see the following example:

Work with MySQL database in C#

1. Introduction

2. Connect to MySQL Database with C#

3. MySqlCommand

4. Query

5. Insert

6. Update

7. Delete

8. Call procedures in C#

9. Call procedures in C#

10. ExecuteScalar

C# Programming Tutorials

C# Programming Tutorials

Newest Articles