Hướng dẫn làm việc với Database SQL Server sử dụng C#
Công ty Vĩnh Cửu tuyển dụng lập trình viên Java

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 Database SQL Server sử dụng C#, mục tiêu bao gồm:
  1. Query
  2. Insert
  3. Update
  4. Delete
  5. Gọi hàm, thủ tục từ C#,...
Tài liệu này 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:

2- Kết nối C# vào SQL Server Database

Tạo project CsSQLServerTutorial:
Project đã được tạo ra.
Bạn cần một class tiện ích ( DBUtils.cs) giúp kết nối vào Database. Với SQL Server Database, bạn có thể xem hướng dẫn tại:
DBSQLServerUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;


namespace Tutorial.SqlConn
{
   class DBSQLServerUtils
   {
     
       public static SqlConnection
                GetDBConnection(string datasource, string database, string username, string password)
       {
           //
           // Data Source=TRAN-VMWARE\SQLEXPRESS;Initial Catalog=simplehr;Persist Security Info=True;User ID=sa;Password=12345
           //
           string connString = @"Data Source="+datasource+";Initial Catalog="
                       +database+";Persist Security Info=True;User ID="+username+";Password="+password;

           SqlConnection conn = new SqlConnection(connString);

           return conn;
       }
     

   }
}
DBUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;


namespace Tutorial.SqlConn
{
   class DBUtils
   {
       public static SqlConnection GetDBConnection()
       {
           string datasource = @"192.168.205.135\SQLEXPRESS";
           
           string database = "simplehr";
           string username = "sa";
           string password = "1234";

           return DBSQLServerUtils.GetDBConnection(datasource,  database, username, password);
       }
   }

}

3- SqlCommand

Trong C# để thao tác với SQL Server Database, chẳng hạn query, insert, update, delete bạn sử dụng một đối tượng SqlCommand, SqlCommand 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 MySQLMySQLCommand. 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.
Tạo đối tượng SqlCommand để thao tác với SQL Server Database:
SqlConnection conn = DBUtils.GetDBConnection();

// Cách 1:

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

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

// Cách 2:

// Tạo mới một Command
SqlCommand cmd = new SqlCommand(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

SqlCommand cmd = new SqlCommand(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.SqlClient;
using System.Data.Common;

namespace CsSQLServerTutorial
{
 class QueryDataExample
 {
     static void Main(string[] args)
     {

         // Lấy ra đối tượng Connection kết nối vào DB.
         SqlConnection 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();
             // Hủy đối tượng, giải phóng tài nguyên.
             conn.Dispose();
         }      
         Console.Read();
     }

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

         // Tạo một đối tượng Command.
         SqlCommand cmd = new SqlCommand();
         // Liên hợp Command với Connection.
         cmd.Connection = conn;
         cmd.CommandText = sql;

       
         using (DbDataReader reader = cmd.ExecuteReader())
         {
             if (reader.HasRows)
             {
                 // Read advances to the next row.
                 while (reader.Read())
                 {
                     // Vị trí của cột Emp_ID trong câu 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);

                     // Vị trí cả cột Mng_Id trong câu SQL.
                     int mngIdIndex = reader.GetOrdinal("Mng_Id");

                     long? mngId = null;

                     // Kiểm tra cột 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ụ:
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 using 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 viết một cách trực quan:

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 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 System.Data.SqlClient;

namespace CsSQLServerTutorial
{
  class InsertDataExample
  {
        static void Main(string[] args)
        {

          // Lấy ra kết nối tới cơ sở dữ liệu.
          SqlConnection 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) ";

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

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

              // Thêm tham số @highSalary (Viết ngắn hơn).
              SqlParameter 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 câu lệnh (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
          {
              // Đóng kết nối
              connection.Close();
              // Hủy đối tượng, giải phóng tài nguyên.
              connection.Dispose();
              connection = null;
          }
       

          Console.Read();

       }
  }

}
Chạy ví dụ:

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 System.Data.SqlClient;
using Tutorial.SqlConn;
using System.Data;

namespace CsSQLServerTutorial
{
  class UpdateExample
  {
      static void Main(string[] args)
      {

          // Lấy ra kết nối tới cơ sở dữ liệu.
          SqlConnection conn  = DBUtils.GetDBConnection();
          conn.Open();
          try
          {
              string sql = "Update Employee set Salary = @salary where Emp_Id = @empId";

              // Tạo đối tượng Command
              SqlCommand cmd = new SqlCommand();
              // Liên hợp với Connection
              cmd.Connection = conn;
              // Sét Command Text.
              cmd.CommandText = sql;

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

              // Thực thi câu lệnh (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
          {
              // Đóng kết nối
              conn.Close();
              // Hủy đối tượng, giải phóng tài nguyên.
              conn.Dispose();
              conn = null;
          }


          Console.Read();

      }
  }

}
Chạy ví dụ:

7- Xóa dữ liệu

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 System.Data.SqlClient;
using Tutorial.SqlConn;
using System.Data;

namespace CsSQLServerTutorial
{
  class DeleteExample
  {
      static void Main(string[] args)
      {

          // Lấy ra kết nối tới cơ sở dữ liệu.
          SqlConnection conn  = DBUtils.GetDBConnection();
          conn.Open();
          try
          {
              string sql = "Delete from Salary_Grade where Grade = @grade ";

              // Tạo đối tượng Command
              SqlCommand cmd = new SqlCommand();
              // Liên hợp với Connection
              cmd.Connection = conn;
              // Sét Command Text.
              cmd.CommandText = sql;

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

              // Thực thi câu lệnh (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
          {
              // Đóng kết nối
              conn.Close();
              // Hủy đối tượng, giải phóng tài nguyên.
              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 SQL Server và gọi nó trong C#:
Get_Employee_Info
-- Thủ tục lấy ra thông tin của một nhân viên,
-- Truyền vào tham số 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 ,
   @v_Emp_No      Varchar(50)   OUTPUT,
   @v_First_Name  Varchar(50)   OUTPUT,
   @v_Last_Name  Varchar(50)   OUTPUT,
   @v_Hire_Date    Date             OUTPUT
AS
BEGIN
   set @v_Emp_No  =   'E' + CAST( @p_Emp_Id as varchar)  ;
   --
   set @v_First_Name = 'Michael';
   set @v_Last_Name  = 'Smith';
   set @v_Hire_date  = getdate();
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 System.Data.SqlClient;

namespace CsSQLServerTutorial
{
  class CallProcedureExample
  {
      static void Main(string[] args)
      {
          SqlConnection 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

              // Tạo một đối tượng Command gọi thủ tục Get_Employee_Info.
              SqlCommand cmd = new SqlCommand("Get_Employee_Info", conn);
              // Kiểu của Command là StoredProcedure
              cmd.CommandType = CommandType.StoredProcedure;
              //
              // Thêm tham số @p_Emp_Id và sét giá trị của 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 SqlParameter("@v_Emp_No", SqlDbType.VarChar, 20));
              cmd.Parameters.Add(new SqlParameter("@v_First_Name", SqlDbType.VarChar, 50));
              cmd.Parameters.Add(new SqlParameter("@v_Last_Name", SqlDbType.VarChar, 50));
              cmd.Parameters.Add(new SqlParameter("@v_Hire_Date", SqlDbType.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ụ:

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
-- Thủ tục lấy ra thông tin của một nhân viên,
-- Truyền vào tham số p_Emp_ID  (Integer)
-- Trả về Emp_No

CREATE Function Get_Emp_No (@p_Emp_Id  Integer)
Returns Varchar(50)
AS
BEGIN    
   return 'E'+  CAST( @p_Emp_Id as varchar);
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 System.Data.SqlClient;


namespace CsSQLServerTutorial
{
  class CallFunctionExample
  {
      static void Main(string[] args)
      {
          SqlConnection conn = DBUtils.GetDBConnection();
          conn.Open();
          try
          {
              // Get_Emp_No                
              // @p_Emp_Id       Integer

              // Tạo một đối tượng Command gọi hàm Get_Emp_No.
              SqlCommand cmd = new SqlCommand("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 giá trị của nó = 100.
              cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value = 100;


              // Tạo một tham số kết quả trả về của hàm.
              SqlParameter resultParam = new SqlParameter("@Result", SqlDbType.VarChar);            

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

              // Thêm tham số trả về.
              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ụ:

10- ExecuteScalar

SqlCommand.ExecuteScalar() là một phương thức sử dụng để thực thi câu lệnh SQL, nó trả về giá trị của cột đầu tiên của dòng đầu tiên trong câu SQL.
-- 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 System.Data.SqlClient;
using System.Data;
using Tutorial.SqlConn;


namespace CsSQLServerTutorial
{
  class ExecuteScalarExample
  {
      static void Main(string[] args)
      {
          SqlConnection conn = DBUtils.GetDBConnection();
          conn.Open();
          try
          {
              SqlCommand cmd = new SqlCommand("Select count(*) From Employee", conn);
             
              cmd.CommandType = CommandType.Text;

 
              // ExecuteScalar trả về giá trị của dòng đầu tiên, cột đầu tiên trong câu sql.
              int count = (int) cmd.ExecuteScalar();

              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ụ: