Stored Procedure

Các bài viết trong: Quan hệ C# và Database
Phần 1: Kết nối C# với Database
Phần 2: Sql Command
Phần 3: SqlDataReader & Dataset
Phần 4: Stored Procedure
Phần 5: Function và Trigger

Trong những bài trước của loạt bài Quan hệ C# và Database mình đã giới thiệu về cách thực hiện kết nối C# với SQL server, SqlCommand, và DataReaders và Dataset, ưu và khuyết điềm của chúng. Trong bài tiếp theo này mình sẽ giới thiệu về Store Procedure - một phần khá quan trong trong lập trình với cơ sở dữ liệu

Stored Procedure (SP) là gì ?


Stored Procedure (Thủ tục lưu trữ) là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm với những khả năng sau:

  • Các cấu trúc điều khiển (IF, WHILE, FOR) có thể được sử dụng trong thủ tục.
  • Bên trong thủ tục lưu trữ có thể sử dụng các biến như trong ngôn ngữ lập trình nhằm lưu giữ các giá trị tính toán được, các giá trị được truy xuất được từ cơ sở dữ liệu.
  • Một tập các câu lệnh SQL được kết hợp lại với nhau thành một khối lệnh bên trong một thủ tục. Một thủ tục có thể nhận các tham số truyền vào cũng như có thể trả về các giá trị thông qua các tham số (như trong các ngôn ngữ lập trình). Khi một thủ tục lưu trữ đã được định nghĩa, nó có thể được gọi thông qua tên thủ tục, nhận các tham số truyền vào, thực thi các câu lệnh SQL bên trong thủ tục và có thể trả về các giá trị sau khi thực hiện xong.

Lợi ích khi sử dụng SP

  • Khả năng module hoá: Bạn có thể tạo một SP một lần và tái sử dụng nó bao nhiêu lần tùy thích trong chương trình của bạn. Điều này làm tăng khả năng bảo trì ứng dụng của bạn và cho phép các ứng dụng truy cập vào các cơ sở dữ liệu trong một cách thức thống nhất và tối ưu hóa.
  • Đơn giản hoá các thao tác trên cơ sở dữ liệu nhờ vào khả năng module hoá các thao tác này.
  • Thủ tục lưu trữ được phân tích, tối ưu khi tạo ra nên việc thực thi chúng nhanh hơn nhiều so với việc phải thực hiện một tập rời rạc các câu lệnh SQL tương đương theo cách thông thường.
  • Thủ tục lưu trữ cho phép chúng ta thực hiện cùng một yêu cầu bằng một câu lệnh đơn giản thay vì phải sử dụng nhiều dòng lệnh SQL. Điều này sẽ làm giảm thiểu sự lưu thông trên mạng.
  • Thay vì cấp phát quyền trực tiếp cho người sử dụng trên các câu lệnh SQL và trên các đối tượng cơ sở dữ liệu, ta có thể cấp phát quyền cho người sử dụng thông qua các thủ tục lưu trữ, nhờ đó tăng khả năng bảo mật đối với hệ thống.
  • SP có thể được gọi từ các chương trình phía client hoặc từ các SP khác do đó SP được ưa chuộng trong lập trình CSDL, đặc biệt là trong các ứng dụng theo mô hình N-tier và các Web services.

Tạo thủ tục lưu trữ
Thủ tục lưu trữ được tạo bởi câu lệnh CREATE PROCEDURE với cú pháp như sau:

CREATE PROCEDURE Tên_thủ_tục [(Danh_sách_tham_số)]
[WITH RECOMPILE  |  ENCRYPTION  |  RECOMPILE, ENCRYPTION]
AS
     Các_câu_lệnh_của_thủ_tục


Trong đó:

- Tên_thủ_tục: Tên của thủ tục cần tạo. Tên phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự.
- Danh_sách_tham_số: Các tham số của thủ tục được khai báo ngay sau tên thủ tục và nếu thủ tục có nhiều tham s��� thì các khai báo phân cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần: Tên tham số được bắt đầu bởi dấu @, Kiểu dữ liệu của tham số
-  RECOMPILE: Thông thường, thủ tục sẽ được phân tích, tối ưu và dịch sẵn ở lần gọi đầu tiên. Nếu tuỳ chọn WITH RECOMPILE được chỉ định, thủ tục sẽ được dịch lại mỗi khi được gọi.
- ENCRYPTION: Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung của thủ tục.
Các_câu_lệnh_của_thủ_tục: Tập hợp các câu lệnh sử dụng trong nội dung thủ tục. Các câu lệnh này có thể đặt trong cặp từ khoá BEGIN...END hoặc có thể không

+ Để thực thi một thủ tục ta thực hiện: Execute Ten_Store_Procedure Danh_sách_các_tham_số (Hoặc EXEC  Ten_Store_Procedure Danh_sách_các_tham_số)
+ Để chỉnh sửa một Stored Procedure có sẵn: sử dụng lệnh Alter Procedure
+ Để xóa một Stored Procedure: sử dụng lệnh Drop Procedure  Ten_Store_Procedure
+ Để đổi tên một Stored Procedure ta dùng lệnh: sp_rename ‘tên_sp_cũ’ ‘tên_sp_mới’
+ Để xem nội dung Stored Procedure ta dùng sp_helptext tên_sp

Ví dụ 1:
Tạo Store Procedure không có tham số
Trong định nghĩa chúng ta có [(Danh_sách_tham_số)] Nhưng đôi khi Store có thể không có Danh sách các tham số. Bạn tham khảo ví dụ sau

CREATE PROCEDURE spCategories_GetCate_ViDu1
AS
BEGIN
    SELECT c.CategoryID, 
        c.CategoryName, 
        c.Description 
    FROM Categories c
END

Trong ví dụ trên mình đã tạo một SP là spCategories_GetCate để truy vấn các trường CategoryID, CategoryName, Description của bảng Categories, Bạn có thể chú ý đến cách đặt tên tuy tên của SP là tùy chọn nhưng Theo mình để cho dễ kiểm soát khi Database của bạn lớn Bạn nên đặt cho mình một quy tắc đặt tên cho SP. Mình thường đặt là spTênBảng_ChứcnăngcủaSP vì khi database của bạn có thể sẽ có rất nhiều bảng và rất nhiều store nêu không theo quy tắc khi cần Alter một SP bạn sẽ khó tìm SP đó. (Trong SQL server có hỗ trợ chức năng Filter - Phải chuột vào Store Procedure/Filter/Filter setting sau đó nhập từ khóa cần Filter)

Ví dụ 2: Tạo Store Procedure  có tham số đầu vào.
Trong ví dụ sau ta sẽ tạo 1 SP có tham số đầu vào và truy vấn theo điều kiện của tham số

CREATE PROCEDURE spCategories_GetCate_ViDu2
@CategoryID int
AS
BEGIN
    IF @CategoryID>0
        SELECT c.CategoryID, 
            c.CategoryName, 
            c.Description 
        FROM Categories c 
        WHERE c.CategoryID=@CategoryID
    ELSE
        SELECT c.CategoryID, 
            c.CategoryName, 
            c.Description 
        FROM Categories c 
        ORDER BY c.CategoryID DESC 
END

Trong ví dụ trên bạn thấy có tham số đầu vào là @CategoryID khi @CategoryID =0 thì sẽ truy vấn All Còn khi @CategoryID >0 thì truy vấn theo @CategoryID
Đây cũng thêm một cách mà mình hay dùng để giảm bớt số lượng SP trong database. (Thường thì bạn sẽ phải viết 2 SP một là Select One - @CategoryID >0, 2 là Select All @CategoryID =0). Bạn thấy rằng trong SP trên mình có sử dụng cấu trúc điều khiển IF. Bạn có thể thấy là SQL cũng có thể lập trình và có thể nó sẽ mang lại nhiều lợi ích khi lập trình trên SQL. Tham khảo thêm bài viết sau: Hướng dẫn xây dựng website

Ví dụ 3: Store Procedure có tham số đầu vào - thực hiện Insert, Delete, Update.
Trong Store Procedure sau mình sẽ minh họa việc tạo một SP để thực hiện Thêm, Sửa, Xóa một bản ghi phụ thuộc vào điều kiện của tham số:

CREATE PROCEDURE spCategories_Edit_ViDu3
@Action int,
@CategoryID int, 
@CategoryName nvarchar(50), 
@Description nvarchar(400), 
@Picture image 
AS
BEGIN
IF @Action=0
    DELETE FROM Categories 
    WHERE CategoryID=@CategoryID
ELSE
    BEGIN
        IF @CategoryID=0
        INSERT INTO Categories(
            [CategoryName], 
            [Description], 
            [Picture])
            VALUES (
            @CategoryName,
            @Description,
            @Picture    
            )
        ELSE
            UPDATE Categories
            SET
                CategoryName = @CategoryName,
                Description = @Description,
                Picture = @Picture
            WHERE CategoryID=@CategoryID
    END
END

Trong ví dụ trên bạn thấy mình sử dụng 1 Store để thực hiện 3 hành động thêm, sửa, xóa một bản ghi trong bảng Categorys. Cũng là một cách mình muốn giới thiệu để giảm bớt số lượng SP trong cơ sở dữ liệu của bạn.

Ví dụ 4: Store Procedure có tham số đầu vào, Tham số đầu ra và trả về giá trị

CREATE PROCEDURE spCategories_Edit_ViDu4
@Action int,
@CategoryID int, 
@CategoryName nvarchar(50), 
@Description nvarchar(400), 
@Picture image,
@error nvarchar(1000) output 
AS
BEGIN TRAN 
IF @Action=0
    BEGIN
        DELETE FROM Categories 
        WHERE CategoryID=@CategoryID
        IF @@ERROR<>0
        BEGIN
            SET @error = N'Lỗi: Chưa xóa được'
            IF @@TRANCOUNT>0
                ROLLBACK tran
            GOTO Error
        END
        ELSE
            SET @error = N'Đã xóa bản ghi'
    END

ELSE
    IF @CategoryID=0
    BEGIN
        INSERT INTO Categories(
        [CategoryName],[Description],[Picture])
        VALUES (
        @CategoryName,@Description,@Picture 
        )
        IF @@ERROR<>0
        BEGIN
            SET @error = N'Lỗi: Lỗi khi thêm dữ liệu'
            IF @@TRANCOUNT>0
                ROLLBACK tran
            GOTO Error
        END
        ELSE
            SET @error = N'Đã thêm mới bản ghi'
    END
    ELSE
        BEGIN
            UPDATE Categories
            SET
                [CategoryName] = @CategoryName,
                [Description] = @Description,
                [Picture] = @Picture
            WHERE CategoryID=@CategoryID
            IF @@ERROR<>0
            BEGIN
                SET @error = N'Lỗi: Lỗi khi cập nhật dữ liệu'
                IF @@TRANCOUNT>0
                    ROLLBACK tran
                GOTO Error
            END 
            ELSE
            SET @error = N'Đã cập nhật dữ liệu'
        END
    RETURN @error -- Select @error as ThongBao

COMMIT TRAN

Bạn để ý thấy rằng trong khi thực hiện SP trên thì ở trường hợp nào bạn cũng sẽ nhận được giá trị trả về tương ứng thông báo cho bạn biết trạng thái thực thi. Bạn cũng nên lưu ý sử dụng biên Output trong SP và dùng cách dùng ROLLBACK tran. Khi trong SP của bạn thực hiện nhiều hành động (Có thể là thực hiện Edit dữ liệu nhiều bảng khác nhau trong 1 SP khi đã thực hiện được 1 lệnh nào đó và đến lệnh tiếp theo xảy ra lỗi khi đó nếu  bạn không dùng dùng ROLLBACK thì sẽ xảy ra sai dữ liệu mà lại khó tím lỗi). Tham khảo thêm bài viết: Những thói quen tốt khi viết câu lệnh SQL. Trong những bài viết sau của loạt bài hướng dẫn này mình sẽ giới thiệu cách dùng C# để thực thi một Store Procedure

Trên đây mình giới thiệu qua về Store Procedure, hy vọng qua đây bạn đã hiểu hơn về SP và biết cách tạo SP cho Database của bạn. Trong bào viết sau của loạt bài này mình sẽ giới thiệu về Function và Trigger trong SQL server. Tham khảo thêm bài viết Hướng dẫn xây dựng website :: Xây dựng cơ sở dữ liệu - TRIGGER

Một số tài liệu cho bạn tham khảo:

1. Lập trình cơ sở dữ liệu SQL server

GTSQLServer.doc (861,00 kb)

2. Câu lệnh truy vấn SQL server

Cau lenh truy van SQL.pdf (379,56 kb)

3. Giáo trình thực hành SQL server

Giao trinh thuc hanh SQL.pdf (430,24 kb)

SqlDataReader & Dataset

Các bài viết trong: Quan hệ C# và Database
Phần 1: Kết nối C# với Database
Phần 2: Sql Command
Phần 3: SqlDataReader & Dataset
Phần 4: Stored Procedure
Phần 5: Function và Trigger

Trong bài tiếp theo này sẽ tập trung trình bày về DataReaders và Dataset, ưu và khuyết điềm của chúng.

1.Data Readers

Khi bạn kết nối tới cơ sở dữ liệu và thi hành các query, dữ liệu thu nhận được cần phải có một ai đó xử lý, chứ không lẽ kết nối đến SQL thêm, xóa, sửa.. xong rồi lại không biết được kết quả đúng hay sai, có thực hiện được hay không, ít ra chúng ta phải xuất kết quả ra để theo dõi đúng không. Như vậy cần phải có một đối tượng thu nhận các thông tin, đó chính là DataReaders

Trong bài trước mình có trình bày 1 ví dụ về có liên quan đến DataReaders nội dung ví dụ như sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strHTML = "";
        strHTML+="<table border=\"0\" width=\"550\" cellspacing=\"1\" cellpadding=\"0\" bgcolor=\"#999966\" id=\"table2\">";
        strHTML+="  <tr>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CustomerID</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CompanyName</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">ContactName</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">Country</font></td>";
        strHTML+="  </tr>";
        // Khai báo chuỗi kết nối
        string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        // Khai báo câu truy vấn
        string sql = @"SELECT TOP 10 CustomerID, CompanyName, ContactName, Country FROM Customers ";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                strHTML += "    <tr>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[0]+ "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[1] + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[2] + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[3] + "</td>";
                strHTML += "    </tr>";
            }
            reader.Close();//Đóng SqlDataReader
        }

        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
        strHTML += "</table>";
        Literal1.Text = strHTML;
    }
}

Kết quả thu được là các thông tin của trong câu lệnh

SELECT TOP 10 CustomerID, CompanyName, ContactName, Country FROM Customers

 Với câu lệnh truy vấn trên nó sẽ trả về 10 bản ghi trong bảng Customers bạn có thể thấy muốn lấy dữ liệu là cột  CustomerID bạn dùng reader[0];  0 ở đây là vị trí Index theo thứ tự (Index được bắt đầu từ 0) trong thứ tự các cột của câu truy vấn, Trong ví dụ của bài trước bạn thấy muốn lấy dữ liệu của cột CustomerID  Mình dùng reader[0].GetValue(0) Ngoài cách dùng index để hiển thị dữ liệu bạn cũng có thể dùng trực tiếp tên cột như sau: reader["CustomerID"]. Tới đây chắc các bạn cũng đã nắm được vị trí index trong reader rồi nhỉ. Bạn sẽ thấy rằng hai cách hiển thị sẽ cho kết qua như nhau nhưng theo mình bạn nên dùng trực tiếp tên cột vì đôi khi trong lập trình bạn thay đổi thứ tự hiển thị trong câu truy vấn SQL.

 Lưu ý:
- Thông thường data reader mà phải xử lý thông qua phương thức ‘ExecuteReader’ của một đối tượng command.
- Một DataReader có thể gọi là một stream đã kết nối tới cơ sở dữ liệu đọc dữ liệu hiệu quả, theo một chiều và thu nhận dữ liệu theo từng dòng (row). Quy tắc chung khi sử dụng data reader dơn giản là chỉ thu nhận và trình bày kết quả thu được. Đây là điểm khác biệt lớn nhất giữa dataReader và DataSet.

2.DataSet và DataAdapter

2.1.Dataset
Trong lúc làm việc ta không thể nào kết nối liên tục đến DataSource việc làm này rất tốn tài nguyên của máy, chính vì lẽ đó mà .Net cung cấp cho bạn DataSet mục đích là lưu trữ dữ liệu và chỉnh sửa cục bộ. Tức là toàn bộ thông tin từ DataSource vẫn còn được lưu trữ trên DataSet khi ta đã ngắt kết nối. Bạn hoàn toàn có thể thao tác trên DataSet như xem, chỉnh sửa sau đó update dữ liệu lại cho DataSource. Nếu khi bạn không update thì việc thao tác trên Dataset sẽ không ảnh hưởng gì đến DataSource cả!
DataSet được lưu trữ dưới dạng tập hợp các Tables và bạn cần xử lý thông qua các lớp của Tables là DataRow, DataColumn.
Có nhiều cách xử lý DataSet nhưng nổi bậc hơn hẳn là 2 cách sử dụng:
- Sử dụng DataAdapter
- Sử dụng XML (cái này sẽ được trình bày trong bài viết sau)

2.2. DataAdapter:
DataAdapter được hiểu nôm na, như là cầu nối và  truyền tải dữ liệu giữa dataSet và dataSource. Mối quan hệ này được biểu diễn như sau: DataSource ~ DataAdapter ~ DataSet

Bạn xem ví dụ sau:
Trong trang aspx bạn cần tạo 1 gridview để hiển thị dữ liệu:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataSetDemo.aspx.cs" Inherits="DataSetDemo" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>hmweb.com.vn</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC"
            BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <RowStyle ForeColor="#000066" />
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
        </asp:GridView>

    </div>
    </form>
</body>
</html>

Trong CodeFile (Code behind) bạn viết như sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class DataSetDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtb = new DataTable();
        dtb = DemoDataSet();
        GridView1.DataSource = dtb;
        GridView1.DataBind();
    }
    private DataTable DemoDataSet()
    {
        DataTable dtbTmp = new DataTable();
        // Tạo connection string
        string connString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        // Tạo SQL query
        string sql = @"SELECT TOP 8 CustomerID, CompanyName, ContactName, Country FROM Customers ";
        // Tạo connection
        SqlConnection conn = new SqlConnection(connString);
        try
        {
            // Mở kết nối
            conn.Open();
            // Tạo một Adapter
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            // Tạo DataSet
            DataSet ds = new DataSet();
            // Đổ dữ liệu DataSet
            da.Fill(ds, "Customers");
            // Tạo DataTable từ dataSet
            dtbTmp = ds.Tables[0];
        }
        catch (Exception e)
        {
            // Bắt lỗi
            Console.WriteLine(e.Message);
        }
        finally
        {
            // Đóng kết nối
            conn.Close();
        }
        return dtbTmp;
    }
}

Trong ví dụ trên mình dùng chỉ số index (ds.Tables[0]) để bạn hiểu rằng DataSet là một tập hợp nhiều DataTable. Khi bạn dùng da.Fill(ds, "Customers") Như vậy ngoài cách sử dụng index bạn cũng có thể sử dụng tên đã khai báo như sau: dtbTmp = ds.Tables["Customers"];

Chạy chương trình xong ta thấy, kết quả xuất ra như sau:  (Bạn có thể nhấn vào dưới đây để down mã nguồn ví dụ trên để tham khảo)

 

DataSetDemo.rar (1,93 kb)

Như vậy kết quả không khác mấy khi dùng SqlDataReader, nên nhớ rằng khi bạn thực hiện với mục đích xuất thông tin ra thôi thì nên dùng SqlDataReader để thay thế cho DataSet, vì sẽ tiết kiệm được thời gian và tài nguyên. Ví dụ trên nhằm mình họa một trong những tính năng của DataSet. Như vậy DataSet có điểm gì nổi bậc hơn SqlDataReader, điểm nổi bậc ở đây chính là khả năng tùy biến cao, có thể chỉnh sửa CSDL và lưu lại vào DataSource.
Bạn hãy xem ví dụ sau:

private void DataSetInsertAndView()
{
    // Tạo connection strin
    string connString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
    // Tạo SQL query        
    string ins = @"INSERT INTO employees (firstname, lastname, titleofcourtesy, city, country) VALUES
                       (@firstname, @lastname, @titleofcourtesy, @city, @country) ";
    // Tạo connection
    SqlConnection conn = new SqlConnection(connString);
    try
    {
        // Tạo Adapter
        SqlDataAdapter da = new SqlDataAdapter(qry, conn);
        // Tạo và lấp đầy DataSet
        DataSet ds = new DataSet();
        da.Fill(ds, "employees");
        // Lấy thông tin Table vào DataTable
        DataTable dt = ds.Tables["employees"];
        // Tạo thêm row mới
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = "Bui";
        newRow["lastname"] = "Hung";
        newRow["titleofcourtesy"] = "AND";
        newRow["city"] = "HaNoi";
        newRow["country"] = "Viet Nam";
        dt.Rows.Add(newRow);
        // Hiển thị thông tin các rows trong DataSet sau khi thêm vào
        foreach (DataRow row in dt.Rows)
        {
            Literal1.Text = row["firstname"].ToString().PadRight(15) + "|";
            Literal1.Text += row["lastname"].ToString().PadLeft(15) + "|";
            Literal1.Text += row["city"];
        }
        // Làm việc với Insert
        SqlCommand cmd = new SqlCommand(ins, conn);
        cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 10, "firstname");
        cmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 20, "lastname");
        cmd.Parameters.Add("@titleofcourtesy", SqlDbType.NVarChar, 25, "titleofcourtesy");
        cmd.Parameters.Add("@city", SqlDbType.NVarChar, 15, "city");
        cmd.Parameters.Add("@country", SqlDbType.NVarChar, 15, "country");
        // Tiến hành insert vào database Source
        da.InsertCommand = cmd;
        da.Update(ds, "employees");
    }
    catch (Exception e)
    {
        // Bắt lỗi
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Đóng kết nối
        conn.Close();
    }
}

Qua ví dụ trên bạn có thể nhận thấy DataSet khá linh hoạt, thông qua việc đổ dữ liệu từ dataSet đến các Tables, chúng ta có thể xem thông tin, chỉnh sửa thông tin và khi chỉnh sửa xong có thể update cho DataSource. Nếu các bạn nào tinh ý sẽ thấy quá trình trên thêm vào thực hiện quá dài cứ lập đi lặp lại việc cmd.Parameters.Add,để giải quyết vấn để này .NET cung cấp cho ta SqlCommandBuilder
Bạn xem ví dụ sau:

private void DataSetInsertAndView()
{
    // Tạo connection string
    string connString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
    // Tạo SQL query        
    string ins = @"INSERT INTO employees (firstname, lastname, titleofcourtesy, city, country) VALUES
                       (@firstname, @lastname, @titleofcourtesy, @city, @country) ";
    // Tạo connection
    SqlConnection conn = new SqlConnection(connString);
    try
    {
        // Tạo Adapter
        SqlDataAdapter da = new SqlDataAdapter(qry, conn);
        // Tạo commandbuider
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        // Tạo và lấp đầy DataSet
        DataSet ds = new DataSet();
        da.Fill(ds, "employees");
        // Lấy thông tin Table vào DataTable
        DataTable dt = ds.Tables["employees"];
        // Tạo thêm row mới
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = "Pi";
        newRow["lastname"] = "Pi_Pi";
        newRow["titleofcourtesy"] = "AND";
        newRow["city"] = "UITS";
        newRow["country"] = "Viet Nam";
        dt.Rows.Add(newRow);
        // Hiển thị thông tin các rows trong DataSet sau khi thêm vào
        foreach (DataRow row in dt.Rows)
        {
            Literal1.Text = row["firstname"].ToString().PadRight(15) + "|";
            Literal1.Text += row["lastname"].ToString().PadLeft(15) + "|";
            Literal1.Text += row["city"];
        }
        //Nhờ sử dụng SqlCommandBuilder mà ta update rất nhanh
        da.Update(ds, "employees");
    }
    catch (Exception e)
    {
        // Bắt lỗi
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Đóng kết nối
        conn.Close();
    }
}

Trong hai ví dụ trên mình có tạo 1 <asp:Literal ID="Literal1" runat="server" /> để khi chạy sẽ hiển thị dữ liệu. Để chạy thử hàm trong ví dụ trên bạn chỉ cần đưa nó lên hàm Page_Load: DataSetInsertAndView();


3.Tổng kết và So sánh giữa DataSet và DataReader

3.1. DataReader:

-Ưu điểm: Thực thi nhanh, ít tốn tài nguyên hơn so với DataSet rất nhiều
-Khuyết Điểm: Tùy biến không cao, thường dùng để lấy dữ liệu và trình bày, hạn chế chỉnh sửa CSDL

3.2. DataSet

- Ưu điểm: Tùy biến cao, có thể chỉnh sửa CSDL rất tiện lợi
-Khuyết điểm: Như đã trình bày, hơi tốn tài nguyên

Lời khuyên: Tùy vào từng trườn hợp, từng mục đích mà ta có thể quyết định chọn cái nào, tránh lạm dụng quá khả năng một cái, phải biết kết hợp hài hòa. Thông thường khi bạn xuất CSDL viết dưới dạng XML, sau đó thao tác trên XML thì bạn nên sử dụng DataReader để phát huy sức mạnh của nó.

 

SqlCommand

Các bài viết trong: Quan hệ C# và Database
Phần 1: Kết nối C# với Database
Phần 2: Sql Command
Phần 3: SqlDataReader & Dataset
Phần 4: Stored Procedure
Phần 5: Function và Trigger

Trong bài tiếp theo này sẽ giới thiệu về SqlCommand tầm quan trong và cách sử dụng SqlCommand

Có thể hiểu ngắn gọn là Lớp SqlCommand tạo một đối tượng để nắm giữ thông tin về mệnh lệnh. Nó phải đi kèm với một kết nối tồn tại để tạo và thi hành mệnh lệnh được viết ra. Xem ví dụ sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Khai báo chuỗi kết nối
        string connectString = @"Server =.\SQL2005;Initial Catalog=BaiTap;User ID=sa;Password=******";
        // Khai báo câu truy vấn
        string sql = @"SELECT  count(*) FROM Customers";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            Label1.Text="Số bản ghi:= "+cmd.ExecuteScalar();

        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
    }
}

Trong trang aspx bạn tạo một Label (<asp:Label ID="Label1" runat="server" />) để hiển thị kết quả. Khi chạy sẽ cho kết quả là: Số bản ghi:= 91 ( 91 là tổng số bản ghi trong bảng Customers)
Lưu ý:
- Một SqlCommand thông thường được khởi tạo như trên nó phải đi với một query và một connection, cụ thể ở đây là sql và conn. Nó nên đặt trong khối lệnh try vì nếu đặt ở ngoài khi kết nối không thành công thì chương trình ngưng hoạt động, ở tình trạng treo mà vẫn tiêu hao tài nguyên.
- Các cách thực thi của SqlCommand

Phương thức (Method) Giá trị trả về (Return Value)
ExecuteNonQuery() Trả về số hàng bị ảnh hưởng bởi câu lệnh SQL. Thường được sử dụng với các câu lệnh không trả về dữ liệu như Insert, delete, update,...
ExecuteScalar() Trả về hàng đầu tiên, cột đầu tiên của (một tập hợp) kết quả, các hàng/cột còn lại (nếu có) sẽ bị bỏ qua. Thường được sử dụng với các câu lệnh chỉ trả về 1 hàng, 1 cột kết quả (vd đếm số lượng nhân viên trong công ty).
ExecuteReader() Trả về đối tượng SqlDataReader - thường dùng cho việc đọc kết quả trả về của câu lệnh SQL là 1 tập hợp gồm nhiều hàng, nhiều cột - đối tượng này sẽ được giới thiệu kỹ hơn trong phần sau.
ExecuteXmlReader() Trả về đối tượng XmlReader - thường dùng để đọc kết quả trả về của câu lệnh SQL được lưu trữ ở dạng XML.

Trong ví dụ trên câu lệnh truy vấn chỉ trả về một giá trị nên ta sử dụng ExecuteScalar() Trường hợp câu truy vấn trả về nhiều giá trị (Dữ liệu dạng bảng) khi đó ta sẽ sử dụng ExecuteReader() xem ví dụ sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strHTML = "";
        strHTML+="<table border=\"0\" width=\"550\" cellspacing=\"1\" cellpadding=\"0\" bgcolor=\"#999966\" id=\"table2\">";
        strHTML+="  <tr>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CustomerID</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CompanyName</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">ContactName</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">Country</font></td>";
        strHTML+="  </tr>";
        // Khai báo chuỗi kết nối
        string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        // Khai báo câu truy vấn
        string sql = @"SELECT TOP 10 CustomerID, CompanyName, ContactName, Country FROM Customers ";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                strHTML += "    <tr>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader.GetValue(0) + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader.GetValue(1) + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader.GetValue(2) + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader.GetValue(3) + "</td>";
                strHTML += "    </tr>";
            }
            reader.Close();//Đóng SqlDataReader
        }

        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
        strHTML += "</table>";
        Literal1.Text = strHTML;
    }
}

 

Trong trang aspx bạn cần tạo <asp:Literal ID="Literal1" runat="server" /> để hiển thị dữ liệu
Kết quả hiển thị như minh họa sau:

 

protected void Page_Load(object sender, EventArgs e)
    {
        string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        string queryInsert = @"INSERT INTO Employees (firstname,lastname) VALUES ('Pete','Houston')";
        string queryDelete = @"DELETE FROM Employees WHERE firstname = 'Pete' AND lastname = 'Houston'";
        // Tạo một kết nối tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            // Thực thi câu lệnh Insert
            SqlCommand cmdInsert = new SqlCommand(queryInsert, conn); 
            cmdInsert.ExecuteNonQuery();
            // Thực thi lệnh Delete
            SqlCommand cmdDelete = new SqlCommand(queryDelete, conn);
            cmdDelete.ExecuteNonQuery();

        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
    }

 

 

Khi thực thi code như ví dụ trên nó sẽ lần lượt thêm và xóa một bản ghi trong bảng Employees bằng 2 lệnh
cmdInsert.ExecuteNonQuery();
cmdDelete.ExecuteNonQuery();
Tương tự như vậy bạn có thể viết câu lệnh cho việc update 1 bản ghi

Tiếp theo chúng ta sẽ tìm hiều về cách chuyền tham số cho PARAMETERS (khá quan trọng): Không lẽ mỗi lần thay đổi lệnh ta lại thay đổi các query? Từ bất cập mà parameters ra đời, nó cho phép ta truyền vào các tham số một cách linh động và hiệu quả. Bạn xem ví dụ sau để hiểu về  PARAMETERS:

 

string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        string queryInsert = @"INSERT INTO Employees (firstname,lastname) VALUES (@firstname,@lastname)";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmdInsert = new SqlCommand(queryInsert, conn);
            //Khởi tạo
            cmdInsert.Parameters.Add("@firstname", SqlDbType.NVarChar, 10);
            cmdInsert.Parameters.Add("@lastname ", SqlDbType.NVarChar, 20);
            //Truyền giá trị
            cmdInsert.Parameters["@firstname"].Value = "Bui ";
            cmdInsert.Parameters["@lastname "].Value = "Hung";
            //Thực thi lệnh
            cmdInsert.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }

Như vậy là trong bảng Employees bạn đã có thêm 1 Employee với fristname = "Bui " và lastname ="Hung"

 

Xử lý từ khóa tìm kiếm cho bài viết bằng SQL

Trong bài viết thương yêu cầu có tìm kiếm từ khóa và mình cần hiển thị cho người dùng biết các từ khóa có liên quan đến bài viết. Bài viết này mình sẽ giới thiệu một cách để thực hiện.

Tuy chưa thực sự tối ưu vì trong thuật toán có dùng con trỏ để tìm kiếm. Nếu có thời gian chịu khó phân tích lại thì sẽ tối ưu thuật toán hơn.

Ví dụ bạn có bài viết được mô tả như sau:

Bạn sẽ thấy bên dưới phần diễn giải của bài viết có các từ khóa liên quan.
Để làm được điều đó bạn cần tạo một bảng (TB_TuKhoa) để lưu trữ những từ khóa (Gọi nôm na là kho từ khóa).

 

CREATE TABLE [dbo].[TB_TuKhoa](
    [idKey] [int] IDENTITY(1,1) PRIMARY KEY  NOT NULL,
    [TuKhoa] [nvarchar](250) NOT NULL,
    [GhiChu] [nvarchar](250) NULL,
)

 Ví dụ dữ liệu của bạn như sau:

 

Giờ chúng ta sẽ viết thuật toán để lấy ra các từ khóa.

 Bạn viết 1 hảm để trả về kết quả là từ khóa của bài viết như sau

* Input NoiDung bài viết (Nvarchar)
* Output: Từ khóa liên quan

CREATE FUNCTION GetKeyword(
    @NoiDung nvarchar(4000)
)
RETURNS NVarChar(4000) 
AS
BEGIN
    DECLARE @KetQua nvarchar(4000) SET @KetQua='Từ khóa: '
    -- Khai báo 1 bảng tạm
    DECLARE @_Table TABLE  (Noidung nvarchar(4000) null)
    INSERT INTO @_Table(Noidung) VALUES (@NoiDung)
    DECLARE @Count int
    --Dùng con trỏ (vòng lặp) để tìm kiếm từ khóa
    DECLARE @TuKhoa Nvarchar(250) 
    DECLARE TuKhoa_Cursor CURSOR FOR 
    SELECT TuKhoa  FROM TB_TuKhoa order by newid()
    OPEN TuKhoa_Cursor 
    FETCH NEXT FROM TuKhoa_Cursor INTO @TuKhoa 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    -- Nếu tìm thấy trong nội dung có từ khóa thì cộng thêm kết quả
     SELECT @Count =Count(*) FROM @_Table 
        WHERE Noidung LIKE N'%'+@TuKhoa+'%'
     IF @Count>0
        BEGIN
            SET @KetQua=@KetQua+@TuKhoa+', '
        END
    FETCH NEXT FROM TuKhoa_Cursor INTO @TuKhoa 
    END
    CLOSE TuKhoa_Cursor
    DEALLOCATE TuKhoa_Cursor

RETURN @KetQua
END
 Để sử dụng hàm trên và tránh việc mỗi lần view bài viết bạn lại yêu cầu thực hiện hàm đó thì  trong bảng bài viết (TB_BaiViet) của bạn tạo thêm trường Từ khóa
Dữ liệu trường này = dbo.GetKeyword(NoiDung)
 
Ví dụ:
SELECT dbo.GetKeyword(N'Bài viết này giới thiệu một số thủ thuật 
và lời khuyên liên quan đến những thắc mắc mà người dùng văn phòng 
thường gặp phải trong quá trình sử dụng Excel, Word.')
Sẽ cho kết quả là:  Từ khóa: WORD, Thủ thuật, EXCEL, lời khuyên, thắc mắc, người dùng , người dùng văn phòng , sử dụng Excel, 

Cách Insert và tạo bảng dữ liệu "động" trong SQL Server

Như chúng ta đã biết đối với các hệ thống Logging thì dữ liệu luôn được cập nhật hàng tháng, hàng ngày vì vậy đòi hỏi CSDL phải tương thích có nghĩa là Log trong tháng nào thì hệ thống sẽ Insert vào tháng đó tương ứng tráng việc Insert nhiều trong cùng 1 bảng dẫn đến việc Select chậm khi trả về kết quả.

Vì vậy đối với Log của mỗi tháng chúng ta sẽ tạo ra các bảng vd:

Tháng 1 năm 2010 hệ thống sẽ tự động tạo ra bảng tbl_Month_1_2010
Tháng 2 năm 2010 hệ thống sẽ tự động tạo ra bảng tbl_Month_2_2010
...
Tháng 12 năm 2010 hệ thống sẽ tự động tạo ra bảng tbl_Month_12_2010

Như vậy làm sao để hệ thống có thể tự tạo ra các bảng tương ứng với năm và tháng như vậy ?

Giải pháp của tôi là sử dụng SQL Job trong SQL Server, cái này bắt buộc bạn phải có Full quyền Administrator trên Server Database

Các bước như sau:

B1: Trong SQL Server bạn kéo xuống dưới có phần SQL Server Agent bạn phải Start nó lên nếu nó đang Stop

Bạn click chuột phải vào Jobs chọn New Job

B2: Tại đây bạn được yêu cầu nhập Name: Tên của Job ở đây tôi chọn là CreateTableLogging và owner là sa

B3: Sau khi ấn OK bạn chọn tiếp phần Steps và bạn chọn New Step, tại đây bạn được yêu cầu nhập Step Name tôi chọn là StepCreateTable và Type là Transact-SQL script (T-SQL) có nghĩa là chạy câu lệnh SQL.

Bạn chọn tiếp Database là tên Database bạn muốn thực thi
Phần Command bạn gõ câu lệnh sau để tạo bảng động

DECLARE @strCreateTable AS VARCHAR(1000)
SET @strCreateTable = 'CREATE TABLE tbl_Month_'
SET @strCreateTable += Cast(MONTH(GETDATE()) as varchar)
SET @strCreateTable += '_'
SET @strCreateTable += Cast(YEAR(GETDATE()) as varchar)
SET @strCreateTable +='('
SET @strCreateTable +='ID int PRIMARY KEY IDENTITY,'
SET @strCreateTable +='Name nvarchar(50)'
SET @strCreateTable +=')'
--PRINT(@strCreateTable)
EXEC(@strCreateTable)

Sau đó nhấn OK

B4: Tiếp đến bạn chọn phần Schedules (để đặt lịch chạy cho Job) ban chọn New Schedule ở đây bạn được yêu cầu nhập Schedule Name cho Job tôi chọn là ScheduleCreateTable, Phần tần suất (Frequency) tôi chọn Occurs chạy vào ngày 15 hàng tháng (Monthly) lúc 12h đêm.

B5: sau đó bạn ấn OK để hoàn tất việc đặt lịch chạy cho Job và OK để hoàn tất Job
và đây là kết quả

B6: Để chắc là Job của bạn có chạy đúng hay không thì bạn Click chuột phải vào Job vừa tạo chọn Start Job at Step...

B7: Nếu nó báo thế này thì có nghĩa là Job của bạn đã chạy chính xác

B8: Kiểm tra

Như vậy mới hoàn thành xong phần tạo bảng bước tiếp chúng ta phải insert vào bảng đó thế nào

DECLARE @strValue AS NVARCHAR(50)
DECLARE @strSQL AS VARCHAR(1000)
SET @strValue = 'Justin___'
SET @strValue += Cast(GETDATE() as varchar)

SET @strSQL = 'INSERT INTO tbl_Month_'
SET @strSQL += Cast(MONTH(GETDATE()) as varchar)
SET @strSQL += '_'
SET @strSQL += Cast(YEAR(GETDATE()) as varchar)
SET @strSQL += '([Name]) VALUES('''
SET @strSQL += @strValue
SET @strSQL += ''')'
--PRINT(@strSQL)
EXEC(@strSQL)

Trong bảng tbl_Month_xx_xxxx  này tôi có 2 trường là ID (tự tăng) và Name
sau đó tôi insert test các giá trị mà gán trong biến @strValue là "Justin___" + ngày hiện tại
sau khi insert xong dữ liệu của tôi sẽ là thế này

ID   Name
6    Justin___Dec 12 2009  9:25AM
7    Justin___Dec 12 2009  9:25AM
8    Justin___Dec 12 2009  9:25AM
9    Justin___Dec 12 2009  9:25AM