Truy vấn Cơ sở dữ liệu (LINQ to SQL phần 3)


Tháng trước tôi bắt đầu viết loạt bài về LINQ to SQL. LINQ to SQL là một bộ khung (framework) có sẵn cho O/RM (object relational mapping) trong .NET 3.5, nó cho phép bạn dễ dàng mô hình hóa các CSDL quan hệ dùng các lớp .NET. Bạn có thể dùng các biểu thức LINQ để truy vấn CSDL, cũng như có thể cập nhật/thêm/xóa dữ liệu từ đó.

Dưới đây là hai bài đầu tiên trong loạt bài LINQ to SQL:

Trong bài viết này, tôi sẽ đi sâu hơn vào cách chúng ta dùng mô hình dữ liệu đã tạo trong phần 2, và cách dùng nó để truy vấn dữ liệu bên trong một dự án ASP.NET.

Mô hình hóa CSDL Northwind dùng LINQ to SQL

Trong phần 2 của loạt bài này, tôi đã đi qua các bước để tạo một mô hình các lớp LINQ to SQL bằng cách dùng trình LINQ to SQL có sẵn trong VS 2008. Dưới đây là một hình mà tôi đã tạo dùng CSDL mẫu Northwind:

Lấy các sản phẩm

Một khi đã định nghĩa mô hình dữ liệu như trên, chúng ta có thể dễ dàng truy vấn và lấy dữ liệu từ CSDL. LINQ to SQL cho phép bạn làm điều này bằng cách viết các câu truy vấn dùng cú pháp LINQ với lớp NorthwindDataContext mà chúng ta đã tạo dùng trình thiết kế LINQ to SQL designer ở trên.

Ví dụ, để lấy và duyệt qua một tập các đối tượng Product, tôi có thể viết code như dưới đây:

Trong câu truy vấn trên, tôi đã dùng một mệnh đề “where” trong cú pháp LINQ để chỉ trả về các sản phẩm trong một category cho trước. Tôi hiện đang dùng CategoryID của Product để thực hiện lọc ra các dùng mong muốn.

Một trong những điểm hay là tôi có rất nhiều lựa chọn, rất nhiều cách để tùy biến câu lệnh, và tôi có thể nắm bắt ưu điểm của mối quan hệ giữa các thực thể mà tôi đã tạo khi mô hình hóa các lớp để làm cho câu lệnh phong phú và tự nhiên hơn. Ví dụ, tôi có thể sửa lại câu truy vấn để lọc ra các dòng theo CategoryName thay vì CategoryID bằng cách viết câu lệnh LINQ như sau:

Chú ý cách tôi dùng thuộc tính “Category” trên mỗi đối tượng Product để lọc theo CategoryName của Category chứa Product đó. Thuộc tính này được tự động tạo ra bởi LINQ to SQL vì chúng ta đã mô hình hóa các lớp Category và Product như một mối quan hệ một-nhiều.

Một ví dụ khác về cách dùng quan hệ trong mô hình dữ liệu bên trong các câu truy vấn, chúng ta có thể viết câu lệnh LINQ như dưới đây để lấy về chỉ những Product có 5 hoặc hơn đơn đặt hàng:

Chú ý cách chúng ta đã dùng tập hợp “OrderDetails” mà LINQ to SQL đã tạo trên mỗi lớp Product (nhờ vào mối quan hệ một-nhiều mà chúng ta đã mô hình hóa trong trình thiết kế LINQ to SQL).

Trực quan hóa các câu truy vấn LINQ to SQL trong trình gỡ lỗi

Các trình ánh xạ O/R (Object relational mapper) như LINQ to SQL tạo ra và thực thi các câu lệnh SQL một cách tự động mỗi khi bạn thực hiện một câu truy vấn hay cập nhật mô hình đối tượng của nó.

Một trong những điều quan tâm lớn nhất mà các lập trình viên mới quen với ORM là: “Câu lệnh SQL thực sự được thực thi là gì?”. Một điều thực sự thú vị về LINQ to SQL là nó cho phép xem rất dễ dàng câu lệnh SQL được thực thi thực sự khi bạn chạy ứng dụng trong chế độ gỡ lỗi.

Bắt đầu từ bản Beta2 của VS 2008, bạn có thể dùng một LINQ to SQL visualizer plug-in để xem một cách dễ dàng (và kiểm tra) bất kỳ câu lệnh truy vấn LINQ to SQL nào. Chỉ cần đặt một breakpoint và di chuột lên trên một câu lệnh LINQ to SQL, sau đó nhấn vào biểu tượng chiếc kính lúp để xem giá trị của câu lệnh một cách trực quan:

Một cửa sổ sẽ hiện lên cho phép bạn xem một cách chính xác câu lệnh LINQ to SQL mà LINQ to SQL sẽ dùng để lấy về các đối tượng Product:

Nếu bạn nhấn nút “Execute” trên cửa sổ này, nó sẽ cho phép bạn chạy câu lệnh SQL trực tiếp trong trình debugger và xem một cách chính xác dữ liệu được trả về:

Điều này rõ ràng làm cho việc xem những gì LINQ to SQL làm cho bạn trở thành cực kỳ dễ dàng. Nhớ rằng bạn có thể dễ dàng thay thế câu SQL mà LINQ to SQL thực thi nếu muốn –  mặc dù trong 98% trường hợp tôi nghĩ bạn sẽ thấy rằng câu lệnh mà LINQ to SQL thực thi là thực sự, thực sự tốt.

Gắn nối các câu truy vấn LINQ to SQL vào các control LINQ to SQL

Các câu truy vấn LINQ trả về kết quả mà nó sẽ implement interrface IEnumerable – đây cũng là interface mà các control ASP.NET dùng để hỗ trợ gắn nối các đối tượng. Điều này có nghĩa là bạn có thể gắn nối kết quả của bất kỳ câu lệnh LINQ, LINQ to SQL hay LINQ to XML vào bất kỳ control ASP.NET nào.

Lấy ví dụ, bạn có thể khai báo một control <asp:gridview> trong một trang .aspx giống như sau:

Tôi cũng có thể gắn nối kết quả của câu LINQ to SQL đã viết trước đây vào GridView giống như sau:

Nó sẽ sinh ra một trang trông như sau:

Data Sharping

Hiện tại, mỗi khi xác định kết quả truy vấn, chúng ta lấy toàn bộ các cột dữ liệu cần thiết cho các đối tượng thuộc lớp Product:

Ví dụ, câu truy vấn sau lấy về các sản phẩm:

Và toàn bộ kết quả được trả về:

Thường thì chúng ta chỉ muốn trả về một tập con của dữ liệu về mỗi sản phẩm. Chúng ta có thể dùng tính năng data shaping mà LINQ và các trình dich C#, VB mới hỗ trợ để chỉ ra rằng chúng ta chỉ muốn một tập con bằng cách chỉnh sửa lại câu truy vấn như sau:

Điều này sẽ trả về chỉ một tập con dữ liệu được trả về từ CSDL:

Một điều thực sự thú vị về LINQ to SQL là tôi có thể tận dụng tất cả ưu điểm của các quan hệ trong mô hình dữ liệu khi muốn gọt giũa lại dữ liệu. Nó cho phép tôi biểu diễn đầy đủ và hiệu quả các câu truy vấn. Lấy ví dụ, câu truy vấn dưới đây lấy về ID và Name từ thực thể Product, tổng số đơn hàng đã được đặt cho sản phẩm đó, và rồi lấy tổng giá trị của từng đơn hàng:

LINQ to SQL đủ thông minh để có thể chuyển biểu thức LINQ ở trên thành câu SQL dưới đây khi nó được thực thi:

Câu SQL ở trên cho phép tính toán tất cả các giá trị của NumOrders và Revenue từ ngay trên SQL server, và trả về chỉ những dữ liệu như dưới đây (làm cho việc thực thi được nhanh chóng):

Chúng ta có thể gắn nối tập kết quả vào control GridView để tạo ra một giao diện đẹp hơn:

Bạn cũng có thể được hỗ trợ đầy đủ bởi tính năng intellisense bên trong VS 2008 khi viết các câu truy vấn LINQ:

Trong ví dụ trên, tôi đang sử dụng một kiểu vô danh (anonymous type) và dùng object initialization để gọt giũa và định nghĩa cấu trúc trả về. Một điều thực sự tuyệt vời là VS 2008 cung cấp intellisense đầy đủ, kiểm tra lúc dịch và cả refactoring khi làm việc cả với các tập kết quả có kiểu vô danh:

Phân trang kết quả truy vấn

Một trong những yêu cầu chung khi viết các trang web là bạn phải có khả năng phân trang một các hiệu quả. LINQ cung cấp sẵn hai hàm mở rộng cho phép bạn có thể làm điều đó một cách dễ dàng và hiệu quả – hàm Skip() và Take().

Bạn có thể dùng Skip() và Take() như dưới đây đê chỉ ra rằng bạn chỉ muốn lấy về 10 đối tượng sản phẩm – bắt đầu từ một sản phẩm cho trước mà chúng ta chi ra trong tham số truyền vào:

Chú ý ở trên tôi đã không dùng Skip() và Take() trong câu khai báo truy vấn các sản phẩm – mà chỉ dùng tới khi gắn kết dữ liệu vào GridView. Mọi người hay hỏi “Có phải làm như vậy thì câu lệnh đầu tiên sẽ lấy toàn bộ dữ liệu từ CSDL về lớp giữa, rồi sau đó mới thực hiện việc phân trang ?”. Câu trả lời là “Không”. Lý do là vì LINQ chỉ thực sự thực thi các câu truy vấn khi bạn lấy kết quả từ nó mà thôi.

Một trong những ưu điểm của mô hình này là nó cho phép bạn có thể viết các câu lệnh phức tạp bằng nhiều bước, thay vì phải viết trong một câu lệnh đơn (giúp dễ đọc hơn). Nó cũng cho phép bạn tạo ra các câu truy vấn từ các câu khác, giúp bạn có thể xây dựng các câu truy vấn rất phức tạp cũng như có thể dùng lại được các câu truy vấn khác.

Một khi tôi đã có phương thức BindProduct() định nghĩa ở trên, tôi có thể viết lệnh như dưới đây để lấy về chỉ số đầu từ query string, và cho phép danh sách sản phẩm có thể được hiện phân trang và hiển thị:

Nó sẽ cho chúng ta một trang hiển thị các sản phẩm có nhiều hơn 5 đơn đặt hàng, cùng với doanh thu tương ứng, và được phân trang dựa trên tham số truyền vào qua query string:

Ghi chú: Khi làm việc với SQL 2005, LINQ to SQL sẽ dùng hàm ROW_NUMBER() để thực hiện việc phân trang logic trong CSDL. Nó đảm bảo rằng chỉ 10 dòng dữ liệu được trả về khi chúng ta thực hiện các câu lệnh trên:

Nó làm cho việc phân trang hiệu quả và dễ dàng hơn, đặc biệt là với các tập dữ liệu lớn.

Tổng kết

Hi vọng các bước trên đã cung cấp một cái nhìn đầy đủ  về những đặc tính mà LINQ to SQL cung cấp, để tìm hiểu thêm về các biểu thức LINQ và cú pháp mới được dùng trong C# và VB.NET trong VS 2008, xin hãy tham khảo thêm các bài viết sau:

Trong bài viết tiếp theo trong loạt bài này, tôi sẽ cho thấy cách thêm các phép kiểm tra vào mô hình dữ liệu của chúng ta, và biểu diễn cách chúng ta có thể dùng để đưa logic chương trình vào mỗi lần thực thi các câu lệnh update, insert, hay delete dữ liệu. Tôi cũng sẽ cho các bạn thấy các tính năng cao cấp hơn của lazy loading và eager loading, cách dùng control mới <asp:LINQDataSource> để hỗ trợ việc khai báo databinding trong ASP.NET, cách giải quyết xung đột…

Các bạn đang xem bài viết trong loạt bài “LINQ to SQL”, loạt bài này được dịch bởi Đào Hải Nam từ blog ScottGu http://weblogs.asp.net/scottgu/

34 thoughts on “Truy vấn Cơ sở dữ liệu (LINQ to SQL phần 3)

  1. SELECT dbo.Books.Book_Name, dbo.OrderDetai.Order_ID, dbo.OrderDetai.Reduce_Price, dbo.OrderDetai.Buy_Quantity, dbo.Orders.Date_shell
    FROM dbo.Books INNER JOIN
    dbo.OrderDetai ON dbo.Books.Book_ID = dbo.OrderDetai.Book_ID INNER JOIN
    dbo.Orders ON dbo.OrderDetai.Order_ID = dbo.Orders.Order_ID

  2. Anh Hai Nam oi cho em hoi voi LINQ thi viec minh tu Visual basic xuat du lieu ra excel nhu the nao a? cach em dua tung doi tuong thanh mot worksheet va dua thuoc tinh cua no vao cac column cua sheet.
    Em cam on anh nhieu!

  3. Em không thể dùng LINQ to SQL để làm việc với Excel được, vì LINQ to SQL chỉ được dùng với SQL Server.
    Nếu muốn xuất vào file Excel, em phải dùng OleDb ADO.NET để xuất dữ liệu vào một Jet database kiểu như dưới đây:

    connString = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & excelPathFile & “;” & _
    “Extended Properties=””Excel 8.0;HDR=YES”””
    conn = New OleDbConnection(connString)

    Dùng connection ở trên để INSERT dữ liệu vào file Excel.

  4. Anh Nam oi khi em truy van select duoc mot tap hop ca san pham dung LINQ roi, em muon chuyen tap hop do vao DataTable thi lam sao a?

  5. Bạn giúp mình thực hiện câu truy vấn này trong LINQ với
    Select * From Media
    Where CategoryID=5 And FileName Like ‘%Audio%’
    Mình đã dùng Linqer để convert mà nó không convert được. Liệu có cách nào thực hiện việc này được không.

  6. Bạn có thể viết như sau (vb):
    Dim r = from m in dbcontext.Medias where m.CategoryID = 5 And m.FileName Like “*Audio*”

  7. ớ bên trên có 1 cau hỏi về việc xuất csdl ra excel, phần này mình ko rõ lắm Nam có thể giải thích rõ hơn được ko. nhân tiện cho minh hỏi luon, trong linq mình có thế serialize 1 thông tin của 1 table thành file excel duoc ko, va nếu được thì làm như thế nào. cái này phiền Nam giúp dum

  8. Anh Nam ơi anh có thể cho em biết các biết thực hiện quá trình tìm kiếm record trong LinQ không anh.

    Cám ơn anh!
    anh có thể gửi qua mail cho em.

  9. anh không rõ ý em nói về cái gì, nếu chỉ tìm một record nào đó thì em dùng mệnh đề Where, LINQ to SQL sẽ chuyển thành mệnh đề Where của SQL Server, việc tìm thế nào sẽ do SQL Server đảm nhận.

  10. Anh Nam có thể cho em hỏi một chút bên LINQ với ah ?

    Em dùng LINQ thực thi một Proc (View) để lấy dữ liệu về. Nhưng em không biết làm cách nào để fill dữ liệu đó lên DataTable.
    Anh chỉ giúp em với nhé.

  11. anh ơi, cho em hỏi, em muốn tạo một form gồm các textbox, sau đó dùng form đó để cập nhập csdl thì làm thế nào ạ.

  12. cho hỏi kết quả câu truy vấn LINQ giờ tui muốn chuyển thành 1 datatable thì sao?
    ví dụ:

      DataClassesDataContext db = new DataClassesDataContext();
            var data= from p in db.tblNhanVien
                       where p.id > 5
                       select p;
            GridView1.DataSource = data;
            GridView1.DataBind();
    

    chạy bình thường
    nhưng tôi muốn trả cái data thành 1 datatable thì làm sao?
    nhiều câu truy vấn LINQ chỉ trả về 1 kết quả( ví dụ là 5)
    tôi khai báo int ketqua= ketquaLINQ(kết quả của câu truy vấn LINQ) thì báo lỗi
    mong bản chỉ thêm

  13. Anh hai nam cho em hoi ty:Ham ClassObjects.Database.GetData(sql, “@email”, txtEmail.Text) trong LINQ minh chuyen lam sao ah!Em cam on anh nhieu!

  14. ANH NAM OI!em co doan code nay em muon chuyen sang LINQ thi lam sao anh em dang bi ma suy nghi hoai khong ra! mong anh giup em ti!Co gi anh goi qua mail giup em(nguyenthanhhoangbd@gmail.com).Em cam on anh!

    private void btDangNhap_Click(object sender, EventArgs e)
    {

    String sql = “Select * from Account where email=@email”;
    DataTable Acc = ClassObjects.Database.GetData(sql, “@email”, txtEmail.Text);

    //try
    //{
    String email = txtEmail.Text;
    String Sql = “SELECT * FROM Account ” +
    “WHERE email=@email”;
    DataTable user = ClassObjects.Database.GetData(Sql,
    “@email”, email);
    if (passData != null)
    {
    passData(txtEmail.Text);
    }
    if (user.Rows.Count > 0)
    {
    String strPass = Acc.Rows[0][“passphrase”].ToString();
    String strPass1 = getMd5Hash(txtPassPhrase.Text, “12345”);
    StringComparer comparer = StringComparer.OrdinalIgnoreCase;
    if (0 == comparer.Compare(strPass, strPass1))
    {
    MessageBox.Show(“Congratulations ! Login Succeeded !”);
    RequestControl ctr = new RequestControl();
    ctr.ExitForm(this);
    ctr.Request1(“ManHinhQuanLy”);
    //QuanLy main = new QuanLy();
    //main.GetFirstValue = txtEmail.Text;
    //main.ShowDialog();
    }
    else
    {
    MessageBox.Show(“Password không chính xác !!”);
    }
    }
    else
    {
    MessageBox.Show(“Email hoặc Password không chính xác !!”);
    }

    //}
    //catch (Exception)
    //{
    // MessageBox.Show(“Sorry , Please try again !!”, “Thông báo”);
    // //}

    //}
    }

  15. ClassObjects.Database.GetData là hàm riêng của em nên anh không rõ nó làm gì để chuyển.
    Anh đoán có lẽ em chưa rành lập trình .NET lắm, và đoạn code trên là cut & paste😦
    Anh nghĩ em nên tự viết từ đầu thì học sẽ hiểu hơn

  16. Xin loi anh em quen ClassObjects.Database.GetData c em viet de ket noi du lieu va truy van trong 1 folder rieng roi qua ham tren goi lai thoi no la o day.E khong biet chuyen qua LINQ ra sao nua:

    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Data.SqlClient;

    namespace DeTaiCuoiKi.ClassObjects
    {
    class Database
    {
    public static String ConnectionString = “Data Source=FEEL;Initial Catalog=DeTaiCuoiKi;Integrated Security=True”;
    //public static String ConnectionString = @”Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Users\My Documents\Visual Studio 2005\WebSites\HVeStore\App_Data\bobo.mdf;Integrated Security=True;User Instance=True”;

    public static SqlConnection GetConnection()
    {
    return new SqlConnection(ConnectionString);
    }

    public static DataTable Fill(DataTable table, String sql, params Object[] parameters)
    {
    SqlCommand command = Database.CreateCommand(sql, parameters);
    new SqlDataAdapter(command).Fill(table);
    command.Connection.Close();

    return table;
    }

    public static DataTable GetData(String sql, params Object[] parameters)
    {
    return Database.Fill(new DataTable(), sql, parameters);
    }

    public static int ExecuteNonQuery(String sql, params Object[] parameters)
    {
    SqlCommand command = Database.CreateCommand(sql, parameters);

    command.Connection.Open();
    int rows = command.ExecuteNonQuery();
    command.Connection.Close();

    return rows;
    }

    public static object ExecuteScalar(String sql, params Object[] parameters)
    {
    SqlCommand command = Database.CreateCommand(sql, parameters);

    command.Connection.Open();
    object value = command.ExecuteScalar();
    command.Connection.Close();

    return value;
    }

    private static SqlCommand CreateCommand(String sql, params Object[] parameters)
    {
    SqlCommand command = new SqlCommand(sql, Database.GetConnection());
    for (int i = 0; i < parameters.Length; i += 2)
    {
    command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
    }
    return command;
    }
    }

    }

  17. Anh chi em cach lam Ham nay trong LINQ thoi.

    //DataBase la em lay tu DeTaiCuoiKi.ClassObjects.DataBase
    public static DataTable GetData(String sql, params Object[] parameters)
    {
    return Database.Fill(new DataTable(), sql, parameters);
    }

  18. san day cho em hoi ty(em hoi tham 1 ti) ,dung la em moi tap to ve LINQ thoi:.Ham ham comparer trong lINQ thi lam sao anh.Em cam on anh nhieu

  19. Em da lam duoc roi!em cam on nhung loi dong gop chan thanh cua a nam.Chuc anh suc khoe va thanh dat!

  20. Anh Nam cho Hương hỏi: Khi viết store xong mình sử dụng trong report viết bằng ngôn ngữ VB.Net – linq như thế nào không ạ, anh có tài liệu hướng dẫn phần này ko?

  21. Pingback: Sử dụng asp:LinqDataSource (phần 5) | Yoshikuni

  22. A Nam cho em hoi em muon dung t LINQ to SQL visualizer plug-in trong VS 10 thi lam ntn.

  23. Chào em,

    Tất cả mọi thứ về LINQ to SQL đều có sẵn trong VS2010, em không cần cài gì thêm.
    Em chỉ cần “Add New Item” và chọn “LINQ to SQL Classes” , sau đó theo các bước của nó để tạo ra file dbml.
    Mở file dbml ra thì sẽ có cửa sổ designer.

  24. Em mới Học asp.net Chưa hiểu rỏ nhìu xin mọi người giúp đỡ!!
    Code em viết thế này:
    public string LayTenDangNhap(string email)
    {
    var tDN = (from p in data.KhachHangs
    where p.Email.ToString() == email
    select p).SingleOrDefault();
    return tDN.tenkhachHang.ToString();
    }
    với mong muốn của em là lấy về giá trị “tenkhachHang” nhưng mà khi chạy thì nó báo lỗi:
    ” An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. “

  25. Anh cho em hỏi vì sao có lúc anh khai báo là Product products=… , có lúc anh khai báo là var product=… vậy?

  26. Bài viết rất chi tiết, và đường link video rất tuyệt.
    Cám ơn bạn rất nhiều

  27. Pingback: Sử dụng asp:LinqDataSource | BUILD AND RUN

  28. Pingback: (LINQ TO SQL PHẦN 5) LINQDATASOURCE

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s