Chúng ta lại nói lại về commands. Một command là một một
kiểu đơn giản, một chuỗi lệnh SQL được dùng để truy xuất dữ liệu.
Một command có
thể là một stored procedure, hoặc là tên của một bảng sẽ trả về: string source = "server=(local)\\NetSDK;" +
"uid=QSUser;pwd=QSPassword;" +
"database=Northwind";
string select = "SELECT ContactName,CompanyName FROM Customers";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
Các mệnh đề SqlCommand và OleDbCommand thường được gọi là CommandType, chúng được dùng để định nghĩa các mệnh đề SQL,
một stored procedure, hoặc một câu lệnh SQL. Sau đây là một bảng liệt kê đơn
giản về CommandType:
|
CommandType
|
Example
|
|---|---|
|
Text
(default)
|
String select = "SELECT ContactName FROM Customers"; SqlCommand cmd = new SqlCommand(select , conn); |
|
StoredProcedure
|
SqlCommand cmd = new SqlCommand("CustOrderHist", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CustomerID", "QUICK");
|
|
TableDirect
|
OleDbCommand cmd = new OleDbCommand("Categories", conn);
cmd.CommandType = CommandType.TableDirect;
|
Khi thực thi một stored procedure, cần truyền các tham số cho
procedure. Ví dụ trên cài đặt trực tiếp tham số @CustomerID, dù vậy có nhiều cách để cài giá trị tham số,
chúng ta sẽbàn kĩ trong phần sau của chương này.
| chú ý |
Kiểu TableDirect command không chỉ
đúng cho OleDb provider – có một ngoại lệ xảy ra khi
bạn cố dùng command này trong Sql
provider.
|
Executing Commands
Bạn đã định nghĩa các command, và bạn muốn thực thi chúng.
Có một số cách để phát ra các statement, dựa vào kết quả mà bạn muốn command đó
muốn trả về. Các mệnh đề SqlCommand và OleDbCommand cung cấp các phương thức thực thi sau:
-
ExecuteNonQuery() – Thực thi các command không trả về kết quả gì cả
-
ExecuteReader() – Thực thi các command và trả về kiểu IDataReader
-
ExecuteScalar() – Thực thi các command và trả về một giá trị đơn
Lớp SqlCommand cung cấp thêm
một số phương thức sau
-
ExecuteXmlReader() – Thực thi các command trả về một đối tượng XmlReader, các đối tượng được dùng đề xem xét các XML được trả về từ cơ sở dữ liệu.
Mã ví dụ cho chương này có thể được tìm thấy trong thư mục con
Chapter 09\01_ExecutingCommands subdirectory của
phần code down về.
ExecuteNonQuery()
Phương thức này thường được dùng cho các câu lệnh UPDATE, INSERT, hoặc DELETE, để trả về số các mẫu tin bị tác động. Phương thức này
có thể trả về các kết quả thông qua các tham số được truyền vào stored
procedure.
using System; using System.Data.SqlClient; public class ExecuteNonQueryExample { public static void Main(string[] args) { string source = "server=(local)\\NetSDK;" + "uid=QSUser;pwd=QSPassword;" + "database=Northwind"; string select = "UPDATE Customers " + "SET ContactName = 'Bob' " + "WHERE ContactName = 'Bill'"; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); int rowsReturned = cmd.ExecuteNonQuery(); Console.WriteLine("{0} rows returned.", rowsReturned); conn.Close(); } }
ExecuteNonQuery() trả về một số kiểu
int cho biết số dòng bị tác động command.
ExecuteReader()
Phương thức này thực hiện các lệnh trả về một đối tượng
SqlDataReader hoặc OleDbDataReader. Đối tượng này có thể dùng để tạo ra các mẫu
tin như mã sau đây:
using System; using System.Data.SqlClient; public class ExecuteReaderExample { public static void Main(string[] args) { string source = "server=(local)\\NetSDK;" + "uid=QSUser;pwd=QSPassword;" + "database=Northwind"; string select = "SELECT ContactName,CompanyName FROM Customers"; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); SqlDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { Console.WriteLine("Contact : {0,-20} Company : {1}" , reader[0] , reader[1]); } } }
Các đối tượng SqlDataReader và OleDbDataReader sẽ được trình bày trong chương sau.
ExecuteScalar()
Trong nhiều trường hợp một câu lệnh SQL cần phải trả về một
kết quả đơn, chẳng hạn như số các record của một bảng, hoặc ngày giờ hiện tại
của server. Phương thức ExecuteScalar có thể dùng cho
những trường hợp này:
using System; using System.Data.SqlClient; public class ExecuteScalarExample { public static void Main(string[] args) { string source = "server=(local)\\NetSDK;" + "uid=QSUser;pwd=QSPassword;" + "database=Northwind"; string select = "SELECT COUNT(*) FROM Customers"; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); object o = cmd.ExecuteScalar(); Console.WriteLine ( o ) ; } }
Phương thức trả về một đối tượng, Bạn có thể chuyển sang kiểu
thích hợp.
ExecuteXmlReader() (SqlClient Provider Only)
Giống như tên đã gọi, nó có thể thực thí command và trả về
một đôi tượng XmlReader. SQL Server cho phép câu lệnh
SQL SELECT dùng cho kiểu FOR
XML. Mệnh đề này có thể có ba kiểu tùy chọn sau:
-
FOR XML AUTO – tạo một cây cơ sở cho các bảng trong mệnh đề FROM
-
FOR XML RAW – trả về một bộ các mẫu tin ánh xạ đệnh các nhân tố, với các cột được ánh xạ đến các thuộc tính
-
FOR XML EXPLICIT – bạn cần phải chỉ định hình dạng của cây XML trả về
Professional SQL Server 2000 XML (Wrox
Press, ISBN 1-861005-46-6) diễn tả đầy đủ các thuộc tính này:
using System; using System.Data.SqlClient; using System.Xml; public class ExecuteXmlReaderExample { public static void Main(string[] args) { string source = "server=(local)\\NetSDK;" + "uid=QSUser;pwd=QSPassword;" + "database=Northwind"; string select = "SELECT ContactName,CompanyName " + "FROM Customers FOR XML AUTO"; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); XmlReader xr = cmd.ExecuteXmlReader(); xr.Read(); string s; do { s = xr.ReadOuterXml(); if (s!="") Console.WriteLine(s); } while (s!= ""); conn.Close(); } }
Chú ý rằng chúng ta có thể nhập không gian tên System.Xml namespace cho các kiểu trả về XML. Không gian này
được dùng cho những khả năng của XML trong .NET Framework trong tương lại được
trình bày kĩ trong chương 11.
Ở đay chúng bao gồm các mệnh đề FOR XML AUTO trong mệnh đề SQL, sau đó
gọi phương thức ExecuteXmlReader(). Sau đây là kết quả
của các mã lệnh trên:
Trong mệnh đề SQL, chúng ta có thể chỉ định, để các thành
phần của kiểu Customers được hiển thị trong phần kết
xuất. Để làm điều đó ta phải thêm các thuộc tính cho mỗi một cột trong cơ sở dữ
liệu. Điều này sẽ tạo ra một sự phân mảnh trong việc chọn các mẫu tin từ cơ sở
dữ liệu.
Gọi các Stored Procedure
Việc gọi một stored procedure với một đối tượng command đơn
giản là định nghĩa tên của stored procedure cần dùng, thêm vào các tham số của
procedure đó, thực thi command với một trong các phương thức đã giới thiệu ở
phần trên.
Để dễ dàng cho việc lấy ví dụ trong phần này, Tôi đã định nghĩa
một bộ các stored procedures dùng để chèn, cập nhât, và xoá các mẫu tin từ bảng
Region trong cơ sở dữ liệu Northwind. Tôi đã chọn bảng này vì nó đủ nhỏ để có thẻ áp
dụng các ví vị cho mỗi kiểu của storeprocedure.
Gọi một Stored Procedure không trả lại kết quả
Ví dụ này sẽ mô tả cách gọi một stored procedure không trả
lại kết quả. Có hai procedure được định nghĩa dưới đây, một dùng cho việc cập
nhật các mẫu Region sẵn có, và một dùng để xóa các mẫu
tin trong Region.
Record Update
Cập nhật một mẫu Region là một công
việc khá đơn giản, chỉ thay đổi một trường duy nhất (vì không thể cập nhật khóa
chính). Bạn có thể gõ trực tiếp các ví dụ này trong SQL Server Query Analyzer,
hoặc chạy file StoredProcs.sql trong thư mục con Chapter 09\02_StoredProcs, để cài đặt các stored
procedure dùng cho phần này:
CREATE PROCEDURE RegionUpdate (@RegionID INTEGER, @RegionDescription NCHAR(50)) AS SET NOCOUNT OFF UPDATE Region SET RegionDescription = @RegionDescription WHERE RegionID = @RegionID GO
Một lệnh cập nhật trong một bảng thực tế hơn cần phải chon lại và
trả về trạng thái của các mẫu được cập nhật. Stored procedure này cần nhập vào
hai tham số (@RegionID và @RegionDescription, và phát ra một câu lệnh UPDATE để thao tác trên cơ sở dữ liệu.
Để chạy stored procedure này trong mã .NET, bạn cần phải định
nghĩa một lệnh SQL và thực thi nó:
SqlCommand aCommand = new SqlCommand("RegionUpdate", conn); aCommand.CommandType = CommandType.StoredProcedure; aCommand.Parameters.Add(new SqlParameter ("@RegionID", SqlDbType.Int, 0, "RegionID")); aCommand.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription")); aCommand.UpdatedRowSource = UpdateRowSource.None;
Đoạn mã này tạo một đối tượng SqlCommand
mới tên là aCommand, và định nghĩa nó là một stored
procedure. Sau đó chúng ta thêm vào các tham số nhập, cũng như các tham số chứa
giá trị mong muốn trả về từ stored procedure để biết được các giá trị trong các
dòng UpdateRowSource được liệt kê, chúng ta sẽ bàn kĩ
vấn đề ở các phần sau của chương này.
Một command được tạo ra, có thể được thực thi bởi việc phát ra các
lệnh sau:
aCommand.Parameters[0].Value = 999; aCommand.Parameters[1].Value = "South Western England"; aCommand.ExecuteNonQuery();
Ở đây chúng ta đang cài đặt giá trị cho các tham số, sau đó thực
thi stored procedure.
Các Command parameters có thể được cài đặt bằng chỉ số như đã
trình bày ở trên, hoặc dùng tên.
Record Deletion
Stored procedure tiếp theo dùng để xóa một mẫu tin trong
bảng Region:
CREATE PROCEDURE RegionDelete (@RegionID INTEGER) AS SET NOCOUNT OFF DELETE FROM Region WHERE RegionID = @RegionID GO
Procedure này chỉ yêu cầu khóa chính của mẫu tin. Mã sử dụng một
đối tượng SqlCommand để gọi stored procedure này như
sau:
SqlCommand aCommand = new SqlCommand("RegionDelete" , conn); aCommand.CommandType = CommandType.StoredProcedure; aCommand.Parameters.Add(new SqlParameter("@RegionID" , SqlDbType.Int , 0 , "RegionID")); aCommand.UpdatedRowSource = UpdateRowSource.None;
Lệnh này chỉ chấp nhận một tham số đơn để thực thi RegionDelete stored procedure; đây là ví dụ cho việc cài đặt
tham số theo tên:
aCommand.Parameters["@RegionID"].Value= 999; aCommand.ExecuteNonQuery();
Gọi một Stored Procedure trả về các tham số trả về
Cả hai ví dụ về stored procedures ở trên đều không có giá
trị trả về. Nếu một stored procedure bao gồm các tham số trả về, sau đó những
phương thức này cần được định nghĩa trong .NET client rằng chúng có thể lấy giá
trị trả về từ procedure.
Ví dụ sau chỉ ra cách chền một mẫu tin vào cơ sở dữ liệu, và trả
về khoá chính của mẫu tin đó.
Record Insertion
Bảng Region chỉ chứa khóa chính
(RegionID) và một trường diễn giải (RegionDescription). Để chèn một mẫu tin, cần phải cung cấp
khóa chính, và sau đó một mẫu tinh mới sẽ được chèn vào cơ sở dữ liệu. Tôi đã
chọn cách tạo khóa chính đơn giản nhất trong ví dụ này bằng cách tạo ra một số
mới trong stored procedure. Phương thức được dùng hết sức thô sơ, tôi sẽ bàn kĩ
về cách tạo khóa chính ở phần sau của chương. Và đây là ví dụ thô sơ của chúng
ta:
CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50), @RegionID INTEGER OUTPUT)AS SET NOCOUNT OFF SELECT @RegionID = MAX(RegionID)+ 1 FROM Region INSERT INTO Region(RegionID, RegionDescription) VALUES(@RegionID, @RegionDescription) GO
Insert procedure này tạo ra một mẫu tin Region mới. Khóa chính được phát ra bởi chính cơ sở dữ liệu,
giá trị này được tra về như một tham số của procedure (@RegionID). Đây là một ví dụ đơn giản, nhưng đối với các bảng
phức tạp hơn, nó thường không sử dụng các tham số trả về mà thay vào đó nó chọn
các dòng được cập nhật và trả nó về cho trình gọi.
SqlCommand aCommand = new SqlCommand("RegionInsert" , conn); aCommand.CommandType = CommandType.StoredProcedure; aCommand.Parameters.Add(new SqlParameter("@RegionDescription" , SqlDbType.NChar , 50 , "RegionDescription")); aCommand.Parameters.Add(new SqlParameter("@RegionID" , SqlDbType.Int, 0 , ParameterDirection.Output , false , 0 , 0 , "RegionID" , DataRowVersion.Default , null)); aCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
Đây là phần định nghĩa phức tạp hơn cho các tham số. Tham số thứ
hai, @RegionID, được định nghĩa để bao gồm các tham số
trực tiếp của nó, trong ví dụ này nó là Output. Chúng
ta sử dụng tập hợp UpdateRowSource để thêm cờ
OutputParameters trên dòng cuối của mã, cờ này cho phép chúng ta trả dữ liệu từ
stored procedure này vào các tham số. Cờ này được dùng chủ yếu cho việc gọi các
stored procedure từ một DataTable (được giải thích
trong chương sau).
Việc gọi stored procedure này giống như các ví dụ trước, ngoại trừ
ở đây chúng ta cần đọc tham số xuất sau khi thực thi procedure:
aCommand.Parameters["@RegionDescription"].Value = "South West"; aCommand.ExecuteNonQuery(); int newRegionID = (int) aCommand.Parameters["@RegionID"].Value;
Sau khi thực thi lệnh, chúng ta đọc giá trị tham số @RegionID và ép nó vào một integer.
Có thể bạn sẽ hỏi phải làm gì nếu stored procedure mà bạn gọi
trả về các tham số xuất và một tập các dòng. Trong trường hợp này, định nghĩa
các tham số tương ứng, sau đó gọi phương thức ExecuteNonQuery(), cũng có thể gọi một trong những phương
thức khác (chẳng hạn như ExecuteReader()) nó cho phép
bạn lấy các mẫu tin trả về.
