Lấy dữ liệu dùng Stored Procedure (LINQ to SQL phần 6)
Vài tuần 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à 5 phần đầu tiên của loạt bài này:
- Sử dụng LINQ to SQL (phần 1)
- Định nghĩa các lớp mô hình dữ liệu (phần 2)
- Truy vấn Cơ sở dữ liệu (phần 3)
- Cập nhật cơ sở dữ liệu (LINQ to SQL phần 4)
- Sử dụng asp:LinqDataSource (phần 5)
Trong các bài viết đó, tôi đã trình bài cách mà các bạn có thể dùng để lập trình lấy dữ liệu về từ CSDL.
Trong bài viết hôm nay, tôi sẽ cho thấy cách chúng ta có thể dùng các stored procedure (SPROCs) và các hàm do người dùng định nghĩa (UDFs) với mô hình dữ liệu LINQ to SQL. Bài viết này sẽ tập trung chủ yếu vào cách dùng SPROCs để truy vấn và lấy dữ liệu về từ CSDL. Trong bài viết kế tiếp, tôi sẽ hiển thị cách bạn có thể dùng các SPROCs để cập nhật, thêm, xóa dữ liệu từ CSDL.
Dùng SPROC hay không SPROC? Đó là một vấn đề….
Câu hỏi liệu nên dùng các câu SQL động được sinh ra bởi trình ORM hay dùng Stored Procedure khi xây dựng lớp dữ liệu là một chủ đề không bao giờ kết thúc tranh cãi giữa các nhà phát triển, kiến trúc sư phần mềm và các DBA. Rất nhiều người thông minh hơn tôi nhiều đã viết về chủ đề này, vì vậy tôi sẽ không nói thêm về vấn đề này ở đây nữa.
LINQ to SQL đi cùng với .NET 3.5 rất mềm dẻo, và có thể được dùng để tạo các lớp mô hình dữ liệu, trong đó các đối tượng không phụ thuộc vào cấu trúc CSDL phía dưới, và có thể xử lý các phép kiểm tra logic cũng như xác thực tính hợp lệ của dữ liệu mà không phụ thuộc vào việc dữ liệu sẽ được lưu nạp dùng các câu SQL động hay thông qua các SPROCs.
Trong bài Truy vấn Cơ sở dữ liệu (phần 3), tôi đã thảo luận cách bạn có thể viết các biểu thức truy vấn LINQ cho một mô hình dữ liệu LINQ to SQL dùng đoạn mã như sau:
Khi bạn viết các biểu thức LINQ kiểu như vậy, LINQ to SQL sẽ thực thi các câu lệnh SQL động để bạn có thể lấy về các đối tượng khớp với câu truy vấn của bạn.
Như bạn đã được học trong bài viết này, bạn cũng có thể dùng các SPROCs trong CSDL trong lớp DataContext, nó cung cấp một cách khác để lấy về các đối tượng Products bằng cách gọi thủ tục tương ứng:
Khả năng này cho phép bạn dùng cả các câu SQL động và các SPROCs với một mô hình dữ liệu rõ ràng, mạnh mẽ cũng như cung cấp sự mềm dẻo khi làm việc với các dự án.
Các bước ánh xạ và gọi SPROC dùng LINQ to SQL
Trong phần 2, tôi đã nói về cách dùng LINQ to SQL designer để tạo ra một mô hình dữ liệu LINQ to SQL như dưới đây:
Ở cửa sổ trên có chứa 2 cửa sổ con, cửa sổ bên trái cho phép chúng ta định nghĩa mô hình dữ liệu sẽ ánh xạ vào CSDL, cửa sổ bên phải cho phép ánh xạ các thủ tục và hàm vào đối tượng DataContext, điều này cho phép chúng ta có thể thay thế các câu SQL động trong việc lấy dữ liệu về.
Cách ánh xạ một SPROC vào một DataContext của LINQ
Để ánh xạ một SPROC vào lớp DataContext, trước tiên hãy mở cửa sổ Server Explorer trong VS 2008 và mở danh sách các SPROC trong CSDL:
Bạn có thể nháy đúp vào bất kỳ thủ tục SPROC nào ở trên để mở và chỉnh sửa chúng, ví dụ như “CustOrderHist” trong Northwind như dưới đây:
Để ánh xạ vào SPROC ở trên vào DataContext, bạn có thể kéo/thả nó từ cửa sổ Server Explorer lên trên cửa sổ LINQ to SQL designer. Việc này sẽ làm tự động sinh ra một thủ tục trong lớp DataContext của LINQ to SQL như dưới đây:
Mặc nhiên tên của phương thức được tạo trong lớp DataContext sẽ chính là tên của SPROC, và kiểu trả về của phương thức sẽ là một kiểu được tạo tự động với cách đặt tên theo dạng “[SprocName]Result”. Ví dụ: SPROC ở trên sẽ trả vef một dãy các đối tượng có kiểu “CustOrderHistResult”. Chúng ta có thể đổi tên của phương thức nếu muốn bằng cách chọn nó rồi dùng Property Grid để đặt lại tên khác.
Cách gọi SPROC mới được tạo
Khi đã hoàn thành các bước trên để ánh xạ một SPROC vào lớp DataContext của chúng ta, bạn có thể gọi nó một cách dễ dàng để lấy dữ liệu về. Tất cả những gì chúng ta cần làm là gọi phương thức mà chúng ta đã ánh xạ trong DataContext để lấy về một chuỗi các đối tượng về từ SPROC:
VB:
C#:
Thêm nữa, thay vì lặp qua tập kết quả như ở trên, tôi cũng có thể gắn nối nó vào cho một control để hiển thị ra màn hình, ví dụ như tôi có thể dùng <asp:gridview>:
Khi đó danh sách các sản phẩm được mua bở khách hàng sẽ được hiển thị như sau:
Ánh xạ kiểu trả về của phương thức SPROC vào một lớp trong mô hình dữ liệu
Trong thủ tục CustOrderHist ở trên, thủ tục trả về một danh sách dữ liệu bao gồm 2 cột: ProductName chứa tên và TotalNumber chứa số sản phẩm đã được đặt hàng trong quá khứ. LINQ to SQL designer sẽ tự động tạo ra một lớp có tên CustOrderHistResult để biểu diễn kết quả này.
Chúng ta cũng có thể chọn cách gán kiểu trả về của thủ tục cho một lớp có sắn trong mô hình dữ liệu, ví dụ một lớp thực thể Product hay Order.
Ví dụ, cho là chúng ta có một thủ tục tênGetProductsByCategory trong CSDL trả về thông tin sản phẩm giống như sau:
Cũng như trước đây, ta có thể tạo một phương thức GetProductsByCategory ở bên trong lớp DataContext mà nó sẽ gọi thủ tục này bằng cách kéo nó vào cửa sổ LINQ to SQL designer. Thay vì thả nó vào một vị trí bất kỳ, chúng ta sẽ thả nó lên trên lớp Product mà ta đã tạo ra sẵn trên sửa sổ này:
Việc kéo một SPROC và thả lên trên một lớp Product sẽ làm cho LINQ to SQL Designer tạo ra phương thức GetProductsByCategory trả về một danh sách các đối tượng có kiểu Product:
Một ưu điểm của việc sử dụng lớp Product như kiểu trả về là LINQ to SQL sẽ tự động quản lý các thay đổi được tạo ra trên đối tượng được trả về này, giống như được làm với các đối tượng được trả về thông qua các câu truy vấn LINQ. Khi gọi “SubmitChanges()” trên DataContext, những thay đổi này cũng sẽ được cập nhật trở lại CSDL.
Ví dụ, bạn có thể viết đoạn code giống như dưới đây (dùng một SPROC) và thay đổi giá của các sản phẩm bên trong một Category nào đó thành 90% giá trị cũ:
Khi gọi SubmitChanges, nó sẽ cập nhật lại giá của tất cả các sản phẩm. Để hiểu thêm về cách quản lý các thay đổi và cách phương thức SubmitChanges() làm việc, cũng như các thêm các phương thức xác thực logic dữ liệu, xin mời đọc lại bài 4 trong cùng loạt bài này.
Trong bài viết tiếp theo của loạt bài về LINQ to SQL, tôi sẽ hướng dẫn các bạn cách thay thế các câu lệnh SQL động cho việc INSERT/UPDATE/DELETE bằng các thủ tục SPROC. Và khi thay thế như vậy, bạn hoàn toàn không phải thay đổi gì trên các đoạn lệnh trên – việc thay đổi này hoàn toàn xảy ra trên mô hình dữ liệu và hoàn toàn trong suốt với các chương trình dùng nó.
Xử lý các tham số thủ tục dạng OUTPUT
LINQ to SQL ánh xạ các tham số dạng “OUTPUT” của các SPROC thành các tham biến (dùng từ khóa ref trong C# hoặc ByRef trong VB.NET), và với các tham trị, LINQ to SQL dùng các biến kiểu nullable (dùng ? trong C# hay <Nullable> trong VB.NET).
Ví dụ, thủ tục”GetCustomerDetails” sau sẽ nhận vào mộtCustomerID như tham số đầu vào, và trả về tên công ty như một tham số dạng OUTPUT và lịch sử giao dịch như kết quả truy vấn:
Nếu bạn kéo thủ tục trên để thả vào lớp Order trong LINQ to SQL designer, chúng ta có thể viết lệnh như sau để gọi nó:
VB:
C#:
Chú ý thủ tục trên vừa trả về một tập các đối tượng Order, đồng thời trả về CompanyName thông qua một tham số output.
Xử lý các thủ tục trả về nhiều kiểu kết quả khác nhau
Khi một thủ tục trả về nhiều kiểu kết quả khác nhau, kiểu trả về của phương thức trên lớp DataContext không thể được ép về một kiểu cụ thể nào đó. Ví dụ, thủ tục dưới đây có thể trả về một tập các sảm phẩm hay lệnh đặt hàng tùy thuộc vào tham số đầu vào:
LINQ to SQL hỗ trợ việc tạo các phương thức trợ giúp cho phép trả về Product hay Order bằng cách thêm một lớp partial NorthwindDataContext vào dự án và định nghĩa một phương thức trong lớp này (trong ví dụ này chúng ta gọi là VariablesShapeSample) để gọi thủ tục và trả về một đối tượng có kiểu IMultipleResult như trong ví dụ sau:
VB:
C#:
Một khi đã thêm phương thức này vào dự án, bạn có thể gọi và chuyển về kiểu thích hợp là Product hoặc Order:
VB:
C#:
Hỗ trợ các hàm do người dùng tự định nghĩa (UDF)
Thêm vào việc hỗ trợ các các thủ tục, LINQ to SQL còn hỗ trợ các hàm trả về các giá trị vô hướng hoặc các bảng kết quả. Một khi đã được thêm vào lớp DataContext như một phương thức, bạn có thể dùng các hàm UDF này trong câu trong các câu lệnh LINQ.
Ví dụ, hãy xem các hàm UDF đơn giản có tên MyUpperFunction sau đây:
Chúng ta có thể kéo và thả nó từ cửa sổ Server Explorer lên cửa sổ LINQ to SQL Designer để thêm nó vào lớp DataContext như một phương thức.
Chúng ta sau đó có thể dùng hàm UDF này ngay bên trong các biểu thức LINQ khi viết các câu truy vấn (giống như chúng ta đang dùng trong biểu thức Where như dưới đây):
VB:
C#:
Nếu bạn dùng LINQ to SQL Debug Visualizer mà tôi đã viết tại đây, bạn có thể thấy các LINQ to SQL chuyển đổi câu truy vấn ở trên thành câu lệnh SQL để thực thi hàm UDF khi chạy:
Tổng kết
LINQ to SQL supports the ability to call Stored Procedures and UDFs within the database and nicely integrate them into our data model. In this blog post I demonstrated how you can use SPROCs to easily retrieve data and populate our data model classes. In my next blog post in this series I’ll cover how you can also use SPROCs to override the update/insert/delete logic when you SubmitChanges() on your DataContext to persist back to the database.
LINQ to SQL hỗ trợ khả năng gọi các thủ tục và hàm trong CSDL và có khả năng tích hợp dễ dàng vào trong mô hình dữ liệu. Trong bài viết này tôi đã trình diễn cách dùng các thủ tục SPROC để dễ dàng truy xuất cũng như cập nhật các lớp mô hình dữ liệu. Trong bài kế tiếp tôi sẽ biểu diễn cách dùng SPROC để thực hiện việc cập nhật/thêm/xóa khi gọi SubmitChanges để cập nhật lại dữ liệu vào CSDL.
Hope this helps,
Scott
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 từ blog ScottGu http://weblogs.asp.net/scottgu/
Rất cảm ơn bạn vì những bài viết hữu ích .
Bạn có thể tổng hợp và chỉ rõ những ưu điểm và nhược điểm của LINQ
( LINQ to SQL ) giúp mình được ko ?
thanks nhiều !!
Bạn có thể tham khảo tại đây: http://namdh.wordpress.com/2008/12/08/linq-faq/
Chào anh !
Tình cờ em tìm thấy trang web này của anh, nên ko biết rành rẽ mọi thứ lắm. Em cũng chưa đọc cặn kẽ bài viết của anh vì vấn đề em gặp lại ko được đề cập đến.
Ví dụ như khi em cần gọi đến 1 Store Procedure, từ LinQ làm thế nào để em có thể lấy tên của Sproc và đối của nó như 1 tham số.
Prototype:
[CODE]
class DataProvider
{
public Array CallSproc(String Name,params object[] param)
{
// here.
}
}
[/CODE]
Để giải quyết vấn đề này, tạm thời em dùng Switch Case ( If else ) để chọn Sproc cần gọi .
Em cũng đã cố hỏi người khác ( ScottGu) những vẫn chưa nhận được câu trả lời. Tiếc quá. Hi vọng anh có thể reply cho em ( Dù được hay ko )
btw, Thanks.
Anh thực sự cũng chưa hiểu rõ yêu cầu của em, em có thể nói rõ hơn không?
À mà em cũng ở bên gamedev à ?
Yeah. Em thích trang đó thôi. Chứ gà lắm
hì hì.
Câu hỏi của em thế này, bình thường để gọi 1 sproc thì anh dung sqlCommand.
với tên và các đối số truyền vào.
Bây giờ trong LinQ, em cũng muốn gọi 1 sproc theo tên là đối số của nó.
Vd Trong csdl em có 2 sproc là :
sproc: GetCustomerByCity(string city);
sproc: GetCustomerByDate(DateTime from,DateTime end);
Giờ em viết hàm gọi các Sproc này trong linQ :
public Array CallSprocByName(String NameOfSproc,params object[] param)
{
/* Do em ko biết cách gọi do đó em gọi thế này :*/
if(NameOfSproc == "GetCustomerByCity")
{
var result = dbDataContext.GetCustomerByCity((string) param[0])
return result.ToArray();
}
else if( NameOfSproc == "GetCustomerByDate")
{
var result = dbDataContext.GetCustomerDate((DateTime)param[0],(DâteTime) param[1]);
return result.ToArray();
}
return null;
}
Hi vọng là anh hiểu
Vâng. Vấn đề ko nằm chỗ của em, mà vấn đề nằm ở chỗ Thầy của em .
Trong LinQ ( DataContext) có ExcuteQuery ( ExcuteCommand ) để em chạy đoạn query trực tiếp.
Tiếp tục nữa là ở DAL. Ông thầy bảo là ko có nhiều hàm mà chỉ có 2 hàm. Hàm GetData(…) và Hàm SetData()
Do đó phải giải quyết chuyện này.
Còn em thì vẫn thích cách có bao nhiều sproc thì có tương ứng bấy nhiều method gọi.
Hic, thế thì chia buồn với em
Chính bản thân LINQ đã là một lớp DAL rất tốt rồi, anh không hiểu tại làm sao lại thêm 1 cái lớp DAL nào đó, mà lại chỉ có SetData và GetData. Xét về kiến trúc, khả năng đơn giản hóa, và cả tư duy thì đều … bad :p
Anh không hiểu nếu em có 1 CSDL phức tạp, chẳng hạn 50 bảng, thì cái hàm Set và GetData đó nó sẽ phức tạp cỡ nào.
Hi anh,
Mấy hôm nay em relax bằng cách đọc Tru Tiên, mệt cả người. Ko vào thăm blog được
Có mấy chỗ về LinQ cũng hay quá mà chưa thảo luận với anh được. Hi vọng 2 tuần nữa ( thi + đồ án xong xuôi ) thì có thể “đàm đạo” với anh tiếp.
Còn về cái này thì do Thầy dạy em mang khuynh hướng cũ, tức là các lớp ở tầng Business giao tiếp với Persistance ( ko biết viết thế đúng ko ) thì giao tiếp qua 2 cách, 1 là đoạn sql, 2 là sproc với tên và tham số ( dùng sqlCommand) .
Do đó thường chia ra rất ít hàm
Ví dụ GetData() thì thường là thế này :
GetData(string sprocName,object[] sprocParam)
{}
rồi excute cái proc đấy
Hơ hơ, chỉ có GetData(string sprocName,object[] sprocParam) và SetData (chắc cũng khai báo tương tự) mà cũng gọi là một “tầng” =))
Thế thì ta còn có cả tầng “ngày giờ hệ thống”, tầng “xử lý chuỗi”
he he.
Lay’ Lam` Xau’ Ho Vay.
Anh ơi em mới bắt đầu học Linq to sql. Em không biết làm thế nào để tạo ra một trigger giống như trong sql. Anh có thể nói về vấn đề này được không ạ
LINQ to SQL chỉ cung cấp khả năng truy xuất CSDL, chứ bản thân nó không thể được dùng để tạo các đối tượng CSDL, do vây muốn tạo Trigger, em vẫn phải dùng SQL hay các thủ tục CLR.
anh Nam cho em nick dc ko?
Nick anh có post trên blog này, chịu khó search nhé
Thực ra thì anh rất ít chat, trừ khi liên quan đến công việc, vậy nên cũng có nhiều người add vào nhưng hiếm khi chat.
À, vậy thôi cũng được !
Có mấy thứ trao đổi mà anh bận thì thôi vậy.
Khi dùng LinQ anh trả về 1 Array thì ok.
Nhưng nếu bên Design nó trả về 1 DataTable thì biết làm sao ?
1 DataTable thì cũng là 1 tập hợp dòng thôi mà, em cũng có thể dùng giống như 1 array.
may quá, chào namdh. đọc cái này trên blog cá nhân của người nước ngoài mà dịch k rõ lắm, chỉ coi code roài hiểu. bi jo đọc lại thấy dễ hiểu hơn. Tiếng Việt muôn năm, hihi. Có điều mình có ý kiến thế này, có lẽ bạn dịch từ đó ra phải không, nếu vậy nên ghi lại link của trang đó để mọi người bít (tôn trọng tác giả chút ấy mà). Có gì sai bỏ quá cho nhá. Thanks bạn nhiều
Thanks bạn,
, thậm chí có bài mình còn giới thiệu ScottGu là ai, đang làm gì 
Có lẽ do bạn không theo dõi blog này lâu nên không biết, các tutorial trên blog này đều được dịch từ blog ScottGu, và mình nói điều này RẤT nhiều trên blog này
Hay như trong bài này, ngay cả tên Scott ở cuối mình cũng giữ nguyên, (mà không sửa thành ĐHN, he he).
Tất nhiên vì quá trình khá lâu vì mình cũng bận, mỗi ngày chỉ dành khoảng 30 phút để dịch hoặc viết bài (một bài có thể mất mấy tuần mới xong) nên có một số bài không nói đến tác giả, chỉ khi review định kỳ lại thì mình mới sửa. Nhưng chắc chắn 100% là trong suốt 1 loạt bài, mình sẽ có nói đến điều này
Dù sao cũng chân thành cảm ơn bạn
uh, tại đang cần làm bài nên search ra cái tutorial này và chỉ xem phần part6 này thôi. Mình bít là dịch cái này để mọi người hiểu thì khá oải, còn nếu là bạn (hay mình coi) thì chỉ cần xem code là chính thì cũng hiểu được nhiều roài. Thanks bạn đã chia sẽ kiến thức cho mọi người
Đọc cái discussion giữa kidkid với namdh mình thấy kidkid đúng là kid thật. LINQ là một kỹ thuật làm đơn giản cho chúng ta rất nhiều trong lập trình với CSDL. Nó bắc 1 “cây cầu” gọi là Object – Relation, tức là thường phía trên chúng ta coding quen với kiểu object nhưng CSDL thì lại nặng về quan hệ. LINQ đã mapping chúng lại với nhau để rút ngắn thời gian coding, dễ coding hơn, dễ thao tác với CSDL hơn. Đằng này kid thì lại muốn lấy cái mà nó muốn làm đơn giản đi làm cái phức tạp. Thật là nhiêu khê :d.
Mình cũng hay đọc Scott’s Gruthie. Còn Đào Hải Nam thấy tên này cũng quen quen – giống tên học đại học với mình – đoán thôi
hi, anh Nam
Các kiểu trả về của các phương thức phụ thuộc vào cách mình đặt tên StoreProcedure hả anh?
Em muốn trả về là “Tolist” thì phải đặt tên như thế nào vậy anh.
Không hiểu cách đặt tên như thế nào mà em toàn trả về kiểu int, khi kéo Store í vào.
Cảm ơn anh.
Thầy ơi, theo em biết, thì LInQ thay thế rất tốt cho DAL, em có làm BL nhưng khi gọi 1 Store Proc thì kiểu trả về của nó là dạng ISingleResult với GetCategoriesByLevelWidthPos là tên của Proc. Và trên trang ascx, em phải casting từ Model (Em dùng mô hình MVC). Có cách nào khác để casting sang List không ạ. Em cast toàn báo lỗi là ko thể convert.
Kiểu ISingleResult có implement từ IEnumerable mà em, sao lại phải cast làm gì cho mệt?
Lấy về rồi thì cứ foreach thôi.
Em đã duyệt đc rồi, nhưng muốn cast sang List vì em muốn dùng List cho nhiều trường hợp khác.
Anh Nam thân mến.
Em đang tìm hiểu về silverlight.Bây jo em muốn làm một website đơn giản.cụ thể là trang đó bao gồm 1 trang default.apx, và một control menungang.sau đó gọi control này từ trang default.apx.trong đó menungang dữ liệu dc lấy từ cơ sở dữ liệu sql server 2005.
Em dốt lắm.Anh Nam chỉ dùm nhé.