Welcome to Trong Phu's Blog: Làm việc với ADO.NET- Lập trình CSDL trong C#

Phần cuối cùng này sẽ cố gắng đưa ra nhưng kịch bản phổ biến khi phát triển các ứng dụng truy cập cơ sở dữ liệu với ADO.NET.

Phân tầng các ứng dụng

Việc sản xuất các phần mềm tương tác với dữ liệu thường chia ứng dụng thành nhiều tầng. Một mô hình phổ biến của một ứng dụng phân tầng là các dịch vụ dữ liệu phân tầng, và một cơ sở dữ liệu phân tầng.

Một trong những cái khó của mô hình này là việc phân tách dữ liệu giữa các tầng, và định dạng truyền giữa các tầng. ADO.NET đã giải quyết các vấn đề này và đã sớm hỗ trợ cho kiểu cấu trúc này.

Sao chép và trộn dữ liệu

Thật khó để copy một DB recordset? Trong In .NET thậy dễ dàng để sao chép một DataSet:

DataSet source = {some dataset};
DataSet dest = source.Copy();

Nó tạo một bản copy của DataSet nguồn – từng DataTable, DataColumn, DataRow, và Relation sẽ được sao chép y chan, và tất cả dữ liệu với các trạng thái trong file nguồn đều đươc sao chép. Nếu như bạn chỉ muốn sao chép sơ đồ của DataSet, bạn có thể làm như sau:

DataSet source = {some dataset};
DataSet dest = source.Clone();

Nó chỉ sao chép tất cả các table, relation, vân vân. Tất nhiên, DataTable sẽ rỗng.

Một thực tế phổ biến khi viết các hệ thống phân tầng, dựa trên Win32 hoặc web, là có truyền dữ liệu giữa các lớp càng ít càng tốt.

DataSet có phương thưc GetChanges() để giải quyết các yêu cầu này. Phương thức đơn giản này thực thi một loạt các công việc và trả về một DataSet với những dòng được cập nhật trong dataset nguồn. Đây là ý tưởng truyền dữ liệu giữa các tầng, chỉ một tập nhở dữ liệu được truyền.

Ví dụ sau chỉ ra cách tạo một "changes" DataSet:

DataSet source = {some dataset};
DataSet dest = source.GetChanges();

Bên dưới lớp vỏ bọc là rất nhiều thứ hấp dẫn. Có hai quá tải của phương thức GetChanges(). Một quá tải lấy giá trị của một DataRowState, và chỉ trả về các trạng thái tương ứng. GetChanges() đơn giản gọi GetChanges(Deleted | Modified | Added), và kiểm tra nếu để bảo đảm rằng có một vài thay đổi bằng cách gọi HasChanges(). Nếu không có thay đổi nào, một giá trị được trả về ngay lập tức.

Tiếp theo là sao chép DataSet. Trước tiên, một DataSet mới bỏ qua các ràng buộc (EnforceConstraints = false), sau đó mỗi dòng đã thay đổi được sao chép vào một DataSet mới.

Như vậy bạn có một DataSet chỉ chứa các thay đổi, sau đó bạn có thể truyền dữ liệu này qua các tầng để sử lí. Khi dữ liệu được cập nhật vào cơ sở dữ liệu, "changes" DataSet có thể trả về cho trình gọi (trong ví dụ này, một vài tham số xuât từ các stored procedure đã cập nhật trong các cột). Những thay đổi này có thể trộn vào bộ DataSet bằng cách dùng phương thức Merge(). Tiến trình này được mô tả như sau:

Tạo khoá với SQL Server

Stored procedure RegionInsert trong ví dụ ở phần trước đã từng tạo ra một giá trí khóa chính để chèn vào cơ sở dữ liệu. Phương thức tạo khoá đó còn thô sơ và không linh động, vì vậy một ứng dụng thực tế cần dùng đến các kĩ thật tạo khóa cao cấp hơn.

Đầu tiên có thể là định nghĩa một định dạng cột đơn giản, và trả về giá trị @@IDENTITY từ một stored procedure. Stored procedure dưới đây sử dụng bảng Categories trong cơ sở dữ liệu Northwind. Gõ stored procedure này vào SQL Query Analyzer, hoặc chạy the file StoredProcs.sql trong thư mục 13_SQLServerKeys:

CREATE PROCEDURE CategoryInsert(@CategoryName NVARCHAR(15),
                                  @Description NTEXT,
                                  @CategoryID INTEGER OUTPUT) AS
   SET NOCOUNT OFF
   INSERT INTO Categories (CategoryName, Description)
      VALUES(@CategoryName, @Description)
   SELECT @CategoryID = @@IDENTITY
GO

Nó chèn một dòng mới vào bảng Category, và trả về khóa chính cho trình gọi. Bạn có thể kiểm tra procedure này bằng cách gõ dòng SQL sau vào Query Analyzer:

DECLARE @CatID int;
EXECUTE CategoryInsert 'Pasties' , 'Heaven Sent Food' , @CatID OUTPUT;
PRINT @CatID;

Khi thực thi một bó lệnh, nó sẽ chèn mọt dòng mới vào bảng Categories, và trả về nhận dạng của dòng mới này, sau đó biểu diễn cho người dùng.

Giả sử rằng sau một vài tháng sử dụng, một ai đó muốn có một sổ theo dõi đơn giản, để báo cáo những cập nhật và sửa đổi trên category name. Bạn sẽ định nghĩa một bảng như sau, để chỉ ra các giá trị mới và cũ của category:

Mã sẵn có trong StoredProcs.sql. Cột AuditID được định nghĩa như một cột IDENTITY. Sau đó bạn cấu trúc mọt cặp trigger để báo cáo các thay đổi trên trường CategoryName:

CREATE TRIGGER CategoryInsertTrigger
   ON Categories
   AFTER UPDATE
AS
   INSERT INTO CategoryAudit(CategoryID , OldName , NewName )
      SELECT old.CategoryID, old.CategoryName, new.CategoryName
      FROM Deleted AS old,
           Categories AS new
      WHERE old.CategoryID = new.CategoryID;
GO

Bạn phải dùng Oracle stored procedure, SQL Server không hỗ trợ nội dung OLD và NEW của các dòng, thay vì chèn một trigger nó có một bộ bảng trong bộ nhớ gọi là Inserted, để xóa và cập nhật, các dòng cũ tồn tại trong bảng Deleted.

Trigger này nhận CategoryID cho các cột giả và lưu các giá trị cũ và mới của cột CategoryName.

Giờ đây, khi bạn gọi một stored procedure để chèn một CategoryID mới, bạn nhận mọt giá trị nhận dạng; Dĩ nhiên, nó không còn là giá trị nhận của dòng được chèn vào bảng Categories, nó là một giá trị mới được tạo trong bảng CategoryAudit. Ouch!

Để xem vấn đề, mở SQL Server Enterprise manager, xem nội dung của bảng Categories table.

Bảng này liệt kê tất cả categories tôi có trong thể hiện của cơ sở dữ liệu.

Giá trị nhận dạng tiếp theo cho bảng Categories có thể là 21, vì vậy chúng ta sẽ chèn một dòng mới bằng cách thực thi mã sau đây, và xem nó trả về ID nào:

DECLARE @CatID int;
EXECUTE CategoryInsert 'Pasties' , 'Heaven Sent Food' , @CatID OUTPUT;
PRINT @CatID;

Giá trị trả về trên máy của tôi là 17. Khi xem bảng CategoryAudit, tôi nhận ra rằng đó là nhận dạng của dòng mới chèn trong bảng audit, không phải của category.

Đó là vì @@IDENTITY trả về giá trị nhận dạng cuối.

Có hai nhận dạng cơ bản bạn có thể sử dụng thay cho @@IDENTITY, chúng cũng không thể giải quyết vấn đề trên. Đầu tiên là SCOPE_IDENTITY(), sẽ trả về giá trị nhận dạng cuối cùng trong tầm vực hiện tại. SQL Server định nghĩa tầm vực như như một stored procedure, trigger, hoặc hàm. Nếu vì một ai đó thêm một câu lệnh INSERT khác vào stored procedure, thì bạn sẽ nhận một giá trị không mong chờ.

IDENT_CURRENT() sẽ trả về giá trị nhận dạng cuối cùng được phát ra trên một bảng trong bất cứ tầm vực nào, trong trường hợp này, nếu hai người dùng đang truy cập SQL Server cùng một lúc, nó có thể nhận giá trị của người khác.

Chỉ có cách quản lí thủ công, bằng cách dùng cột IDENTITY trong SQL Server.

Qui tắt đặt tên

Trong nhiều năm làm việc với các ứng dụng cơ sở dữ liệu, Tôi nhận được một vài giới thiệu cho cách đặt tênđể tiện cho việc dùng chung. Tôi biết nó không liên quan đến .NET, nhưng những qui tắt này rất hữu ích khi đặt tên. Bỏ qua phần này nếu bạn có cách đặt tên riêng của mình.

Database Tables

Luôn là số ít – Product tốt hơn là Products. Nó sẽ tôt hơn về mặt ngữ pháp khi nói "The Product table contains products" hơn là "The Products table contains products". Hãy xem cơ sở dữ liệu Northwind để thấy được nhận xét này.
Chấp nhận một vài qui tắt đặt tên cho các cột trong một bảng – chẳng hạn <Table>_ID cho khóa chính của bảng (Ở đây khóa chính là một cột), tên của cột phải là một tên thân thiện, và giải thích chứa đựng thông tin về cột đó. Một qui tắc đặt tên tốt sẽ cho bạn một cái nhìn bao quát vè khả năng của các trường trong cơ sở dữ liệu.

Database Columns

Dùng danh từ số ít tốt hơn là danh từ số nhiều.
Bất kì cột nào liên kết với bảng khác nên được đặt cùng tên với khóa chính của bảng kia. Chẳng hạn, một liên kết với bảng Product là Product_ID, và đến bảng Sample là Sample_ID. Không phải lúc nào cũng vậy, chẳng hạn một bảng có nhiều liên kết với bảng khác. Trong trường hợp này tùy bạn sử dụng.
Các trường Date nên kết thúc bằng _On, chẳng hạn Modified_On, Created_On. Như vậy sẽ dễ hiểu hơn.
Các trường báo cao nêu kết thúc bằng _By, chẳng hạn Modified_By hay Created_By.

Constraints

Nếu có thể, nên bao gồm tên của bảng và cột của ràng buộc, chẳng hạn CK_<Table>_<Field>. Ví dụ CK_PERSON_SEX để kiểm tra ràng buộc trên cột SEX của bảng PERSON. Một khóa ngoại có thể là FK_Product_Supplier_ID, đây là khóa ngoại giữa product và supplier.
Chỉ ra kiểu của ràng buộc như một tiếp đầu ngữ, chẳng hạn CK cho một kiểm tra ràng buộc và FK cho một khóa ngoại. Chẳng hạn CK_PERSON_AGE_GT0 cho một ràng buộc trên cột age khai báo rằng age phải lớn hơn zero.
Nếu bạn muốn rút gọn tên của ràng buộc, nên làm điều đó trên tên của bảng hơn là tên của cột. Khi bạn có một ràng buộc vi phạm, nó sẽ dễ dàng nhận ra lỗi xảy ra trên bảng nào, nhưng không dễ kiểm tra xem trường nào đã sinh lỗi. Oracle giới hạn tên là 30-kí tự.

Stored Procedures

Nhiều nhà phát triển SQL Server nhận thấy dùng tiếp đầu ngữ 'sp_' là qui tắc tốt nhất để đặt tên cho các stored procedure.

SQL Server dùng tiếp đầu ngữ 'sp_' cho tât cả các stored procedure hệ thống. Vì vậy, có thể sẽ xảy ra tình trạng xung đột tên khi các stored procedure của bạn cũng bắt đầu là 'sp_' như 'sp_widget' giống với stored procedure chuẩn của SQL Server. Khi xem xét một stored procedure, SQL Server sẽ sửa các procedure với tiếp đầu ngữ 'sp_'.

Nêu bạn dùng tiếp đầu ngữ này, khi thực thi, SQL Server sẽ xem tầm vực hiện tại, và nhảy đến cơ sở dữ liệu chủ để tìm stored procedure đó. Nếu có tồn tại thì sẽ có một lỗi phát sinh sớm.

Performance

Bộ managed provider hiện tại của .NET có một vài giới hạn – bạn có thể chọn OleDb hoặc SqlClient; OleDb cho phép kết nối với bất kì nguồn dữ liệu nào nếu nó là một OLE DB driver (chẳng hạn như Oracle), còn SqlClient là một trình cung câp dùng riêng cho SqlServer.

Trình cung cấp SqlClient đã được viết hoàn toàn bằng mã có quản, và sử dụng một vài lớp để kết nối cơ sở dữ liệu. Trình cung cấp viết các gói TDS (Tabular Data Stream) trực tiếp từ SQL Server, về bản chất nó nhanh hơn OleDb provider, nó có thể duyệt qua các lớp trước khi tác động vào cơ sở dữ liệu.

Để kiểm tra điều đó, hãy chạy mã sau trên cùng cơ sở dữ liệu, khác biệt ở chỗ sử dụng SqlClient managed provider trên ADO provider:

SqlConnection conn = new SqlConnection(Login.Connection);
conn.Open();
SqlCommand cmd = new SqlCommand ( "update tempdata set AValue=1 Where ID=1" ,
                                  conn);

DateTime   initial, elapsed ;
initial = DateTime.Now ;
for(int i = 0; i < iterations; i++)
   cmd.ExecuteNonQuery();
elapsed = DateTime.Now ;

conn.Close();

OLE DB thường sử dụng OleDbCommand hơn là SqlCommand. Tôi đã tạo một bảng cơ sở dữ liệu nhỏ với hai côtj như dưới đây, và điền vào đó một dòng đơn:

Câu lệnh SQL được sử dụng là một câu lệnh UPDATE đơn giản:

UPDATE TempData SET AValue = 1 WHERE ID = 1.

SQL là đơn giản nhất trong các provider. Kết quả tính bằng giây cho được liệt kê trong bảng sau:

Provider	100	1000	10000	50000
OleDb	0.109	0.798	7.95	39.11
Sql	0.078	0.626	6.23	29.27

Nếu bạn chỉ hướng vào SQL Server thì dĩ nhiên bạn sẽ chọn Sql provider. Trong thực tế, nếu bạn sử dụng các cơ sở dữ liệu khác bạn sẽ sử dụng OleDb provider.

Microsoft đã tạo ra một giao thức tri cập chung cho các cơ sở dữ liệu khác nhau trong System.Data.Các lớp chung này sẽ dùng cơ sở dữ liệu thích hợp vào thời gian chạy. Nó là một lớp vở bọc giữa OleDb và Sql, nếu các nhà cung cấp cơ sở dữ liệu khác viết các trình quản lí cho các sản phẩm của họ, bạn có thể dùng ADO cho provider đó với một ít thay đổi ở mã. Ví dụ trong truy cập cơ sở dữ liệu .NET, "Scientific Data Center" trong "Data-Centric .NET Programming with C#" (Wrox

Thứ Hai, 5 tháng 8, 2013

Làm việc với ADO.NET- Lập trình CSDL trong C#