[ADO.NET] Lesson 03: Đối tượng SqlCommand

Lesson này giới thiệu về đối tượng SqlCommand và cách sử dụng để thao tác với database. Qua bài viết này, bạn sẽ biết cách sử dụng các phương thức ExecuteReader(), ExecuteNonQuery() và ExecuteScalar() của đối tượng command.

Giới thiệu

Đối tượng SqlCommand cho phép bạn chọn kiểu tương tác mà bạn muốn thực hiện với database. Ví dụ, bạn có thể thực hiện các lệnh select, insert, modify, và delete các dòng trong một table của database. Đối tượng này có thể được dùng để hỗ trợ mô hình quản lý dữ liệu ngắt kết nối (disconnected), nhưng trong bài học này chúng ta chỉ dùng đối tượng SqlCommand làm việc độc lập. Bài học này cũng cho bạn thấy cách để lấy một giá trị đơn từ database, như là số dòng của một table.

Tạo một đối tượng SqlCommand

Tương tự như các đối tượng C# khác, bạn tạo đối tượng SqlCommand bằng cách khai báo một thể hiện của nó:

SqlCommand cmd = new SqlCommand(“select CategoryName from Categories”, conn);

Dòng trên là điển hình để tạo một đối tượng SqlCommand. Nó lấy một tham số là chuỗi lệnh bạn cần thực thi và một tham chiếu đến đối tượng SqlConnection. SqlCommand có một vài overload, mà bạn sẽ thấy trong các ví dụ của tutorial này.

Querying Data – Truy vấn dữ liệu

Khi dùng một lệnh SQL select, bạn lấy được một dữ liệu từ database để hiển thị. Để làm được điều này với SqlCommand, bạn cần dùng phương thức ExecuteReader() để trả về một đối tượng SqlDataReader. Chúng ta sẽ thảo luận về SqlDataReader trong bài tiếp theo. Ví dụ sau cho thấy cách dùng SqlCommand để lấy được một đối tượng SqlDataReader:

1	// 1. Instantiate a new command with a query and connection

2	SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

3

4	// 2. Call Execute reader to get query results

5	SqlDataReader rdr = cmd.ExecuteReader();

Trong ví dụ trên, chúng ta tạo một đối tượng SqlCommand, truyền chuỗi lệnh và đối tượng connection vào constructor. Sau đó chúng ta lấy về đối tượng SqlDataReader bằng cách gọi phương thức ExecuteReader() của đối tượng SqlCommand, cmd.

Inserting Data – Chèn dữ liệu

Để chèn dữ liệu vào database, dùng phương thức ExecuteNonQuery() của đối tượng SqlCommand. Đoạn code sau cho thấy cách chèn dữ liệu vào một bảng trong database:

01	// prepare command string

02	string insertString = @"

03	insert into Categories

04	(CategoryName, Description)

05	values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

06

07	// 1. Instantiate a new command with a query and connection

08	SqlCommand cmd = new SqlCommand(insertString, conn);

09

10	// 2. Call ExecuteNonQuery to send command

11	cmd.ExecuteNonQuery();

Lưu ý hai dấu nháy đơn (‘’) trong chuỗi insertString của từ “doesn’’t” (không phải dấu nháy kép). Đây là cách để bạn chèn một dấu nháy đơn vào chuỗi trong trường hợp chuỗi đó được bao bởi hai dấu nháy đơn (‘) thay vì nháy kép (“). SQL sẽ xem hai dấu nháy đơn đứng sát nhau là một dấu nháy đơn, tương tự như cách viết (\’) trong C#.

Một điểm cần chú ý nữa là chúng ta xác định rõ các cột CategoryName và Description. Bảng Categories có một primary key là CategoryID. Chúng ta không cần chèn dữ liệu vào cột này bởi vì SQL Server sẽ tự động thêm vào. Nếu bạn thử thêm giá trị vào cột primary key, như CategoryID, một ngoại lệ sẽ được tạo ra.

Để thực thi lệnh này, chỉ cần đơn giản gọi phương thức ExecuteNonQuery() của đối tượng SqlCommand, cmd.

Updating Data – Cập nhật dữ liệu

Phương thức ExecuteNonQuery cũng được dùng để cập nhật dữ liệu, như đoạn mã sau:

01	// prepare command string

02	string updateString = @"

03	update Categories

04	set CategoryName = 'Other'

05	where CategoryName = 'Miscellaneous'";

06

07	// 1. Instantiate a new command with command text only

08	SqlCommand cmd = new SqlCommand(updateString);

09

10	// 2. Set the Connection property

11	cmd.Connection = conn;

12

13	// 3. Call ExecuteNonQuery to send command

14	cmd.ExecuteNonQuery();

Một lần nữa, chúng ta đặt câu SQL trong một biến string, nhưng lần này chúng ta dùng một constructor khác của SqlCommand, chỉ có một tham số là câu lệnh SQL. Trong bước 2, chúng ta gán đối tượng SqlConnection, conn, cho property Connection của đối tượng SqlCommand, cmd.

Thay vì cách này, bạn có thể sử dụng constructor với hai tham số tương tự như cho đoạn mã insert phần trên. Nó cho thấy rằng bạn có thể thay đổi đối tượng connection gán cho một command bất kì lúc nào.

Bước cuối cùng, phương thức ExecuteNonQuery() thực thi lệnh update.

Deleting Data – Xóa dữ liệu

Bạn cũng có thể xóa dữ liệu bằng phương thức ExecuteNonQuery(). Ví dụ sau cho thấy cách xóa một dòng từ database với phương thức ExecuteNonQuery():

01	// prepare command string

02	string deleteString = @"

03	delete from Categories

04	where CategoryName = 'Other'";

05

06	// 1. Instantiate a new command

07	SqlCommand cmd = new SqlCommand();

08

09	// 2. Set the CommandText property

10	cmd.CommandText = deleteString;

11

12	// 3. Set the Connection property

13	cmd.Connection = conn;

14

15	// 4. Call ExecuteNonQuery to send command

16	cmd.ExecuteNonQuery();

Ví dụ này sử dụng constructor không có tham số của SqlCommand. Thay vào đó, nó gán cho hai property CommandText và Connection của đối tượng SqlCommand, cmd.

Chúng ta cũng có thể dùng hai overload constructor trước đó của SqlCommand, dùng để insert và update, với kết quả tương tự. Điều này cho thấy rằng bạn có thể thay đôi cả chuỗi lệnh và đối tượng connection bất kì lúc nào.

Lời gọi phương thức ExecuteNonQuery()  gửi lệnh đến database.

Lấy một giá trị đơn

Đôi lúc tất cả những gì bạn cần từ database chỉ là một giá trị đơn, đó có thể là một giá trị đếm, tổng, trung bình, hoặc các giá trị kết hợp khác từ dữ liệu. Thực thi phương thức ExecuteReader() để lấy về một đối tượng SqlDataReader và tính toán kết quả trong mã lệnh của bạn không phải cách tốt để làm điều này. Cách tốt nhất là để database làm công việc này và trả về giá trị bạn cần. Ví dụ sau cho thấy cách làm điều này với phương thức ExecuteScalar():

1	// 1. Instantiate a new command

2	SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

3

4	// 2. Call ExecuteNonQuery to send command

5	int count = (int)cmd.ExecuteScalar();

Câu truy vấn trong constructor của  SqlCommand sẽ lấy về giá trị đếm của tất cả dòng trong bảng Categories. Câu truy vấn này chỉ trả về một giá trị đơn. Phương thức ExecuteScalar() trong bước 2 trả về giá trị này. Bởi vì kiểu trả về của ExecuteScalar() là object, chúng ta cần ép kiểu để chuyển giá trị sang int.

Kết hợp tất cả với nhau

Để đơn giản, tôi cho thấy các đoạn mã ngắn trong những phần trước để minh họa các kĩ thuật tương ứng. Nó cũng rất hữu ích để xem cách mà mã lệnh thực hiện trong một đoạn mã hoàn chỉnh của chương trình. Listing 1 cho thấy tất cả mã lệnh được dùng trong ví dụ này, cùng với phương thức Main() để thực thi và hiển thị kết quả xuất ra:

Listing 1.  SqlConnection Demo

001	using System;

002	using System.Data;

003	using System.Data.SqlClient;

004

005

///

006	<summary> /// Demonstrates how to work with SqlCommand objects

007	/// </summary>

008	class SqlCommandDemo

009

{

010	SqlConnection conn;

011

012	public SqlCommandDemo()

013

{

014	// Instantiate the connection

015	conn = new SqlConnection(

016	"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

017

}

018

019	// call methods that demo SqlCommand capabilities

020	static void Main()

021

{

022	SqlCommandDemo scd = new SqlCommandDemo();

023

024	Console.WriteLine();

025	Console.WriteLine("Categories Before Insert");

026	Console.WriteLine("------------------------");

027

028	// use ExecuteReader method

029	scd.ReadData();

030

031	// use ExecuteNonQuery method for Insert

032	scd.Insertdata();

033	Console.WriteLine();

034	Console.WriteLine("Categories After Insert");

035	Console.WriteLine("------------------------------");

036

037	scd.ReadData();

038

039	// use ExecuteNonQuery method for Update

040	scd.UpdateData();

041

042	Console.WriteLine();

043	Console.WriteLine("Categories After Update");

044	Console.WriteLine("------------------------------");

045

046	scd.ReadData();

047

048	// use ExecuteNonQuery method for Delete

049	scd.DeleteData();

050

051	Console.WriteLine();

052	Console.WriteLine("Categories After Delete");

053	Console.WriteLine("------------------------------");

054

055	scd.ReadData();

056

057	// use ExecuteScalar method

058	int numberOfRecords = scd.GetNumberOfRecords();

059

060	Console.WriteLine();

061	Console.WriteLine("Number of Records: {0}", numberOfRecords);

062

}

063

064

///

065	<summary> /// use ExecuteReader method

066	/// </summary>

067	public void ReadData()

068

{

069	SqlDataReader rdr = null;

070

071

try

072

{

073	// Open the connection

074	conn.Open();

075

076	// 1. Instantiate a new command with a query and connection

077	SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

078

079	// 2. Call Execute reader to get query results

080	rdr = cmd.ExecuteReader();

081

082	// print the CategoryName of each record

083	while (rdr.Read())

084

{

085	Console.WriteLine(rdr[0]);

086

}

087

}

088

finally

089

{

090	// close the reader

091	if (rdr != null)

092

{

093	rdr.Close();

094

}

095

096	// Close the connection

097	if (conn != null)

098

{

099	conn.Close();

100

}

101

}

102

}

103

104

///

105	<summary> /// use ExecuteNonQuery method for Insert

106	/// </summary>

107	public void Insertdata()

108

{

109

try

110

{

111	// Open the connection

112	conn.Open();

113

114	// prepare command string

115	string insertString = @"

116	insert into Categories

117	(CategoryName, Description)

118	values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

119

120	// 1. Instantiate a new command with a query and connection

121	SqlCommand cmd = new SqlCommand(insertString, conn);

122

123	// 2. Call ExecuteNonQuery to send command

124	cmd.ExecuteNonQuery();

125

}

126

finally

127

{

128	// Close the connection

129	if (conn != null)

130

{

131	conn.Close();

132

}

133

}

134

}

135

136

///

137	<summary> /// use ExecuteNonQuery method for Update

138	/// </summary>

139	public void UpdateData()

140

{

141

try

142

{

143	// Open the connection

144	conn.Open();

145

146	// prepare command string

147	string updateString = @"

148	update Categories

149	set CategoryName = 'Other'

150	where CategoryName = 'Miscellaneous'";

151

152	// 1. Instantiate a new command with command text only

153	SqlCommand cmd = new SqlCommand(updateString);

154

155	// 2. Set the Connection property

156	cmd.Connection = conn;

157

158	// 3. Call ExecuteNonQuery to send command

159	cmd.ExecuteNonQuery();

160

}

161

finally

162

{

163	// Close the connection

164	if (conn != null)

165

{

166	conn.Close();

167

}

168

}

169

}

170

171

///

172	<summary> /// use ExecuteNonQuery method for Delete

173	/// </summary>

174	public void DeleteData()

175

{

176

try

177

{

178	// Open the connection

179	conn.Open();

180

181	// prepare command string

182	string deleteString = @"

183	delete from Categories

184	where CategoryName = 'Other'";

185

186	// 1. Instantiate a new command

187	SqlCommand cmd = new SqlCommand();

188

189	// 2. Set the CommandText property

190	cmd.CommandText = deleteString;

191

192	// 3. Set the Connection property

193	cmd.Connection = conn;

194

195	// 4. Call ExecuteNonQuery to send command

196	cmd.ExecuteNonQuery();

197

}

198

finally

199

{

200	// Close the connection

201	if (conn != null)

202

{

203	conn.Close();

204

}

205

}

206

}

207

208

///

209	<summary> /// use ExecuteScalar method

210	/// </summary>

211	/// number of records

212	public int GetNumberOfRecords()

213

{

214	int count = -1;

215

216

try

217

{

218	// Open the connection

219	conn.Open();

220

221	// 1. Instantiate a new command

222	SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

223

224	// 2. Call ExecuteScalar to send command

225	count = (int)cmd.ExecuteScalar();

226

}

227

finally

228

{

229	// Close the connection

230	if (conn != null)

231

{

232	conn.Close();

233

}

234

}

235	return count;

236

}

237

}

Trong Lising 1, đối tượng SqlConnection được khởi tạo trong lớp SqlCommandDemo. Điều này hợp lệ vì đối tượng sẽ được dọn dẹp khi CLR garbage collector làm việc. Điều quan trọng là ta đóng connection sau khi sử dụng nó. Chương trình mở connection trong một khổi try và đóng nó trong một khối finally cho mỗi phương thức.

Phương thức ReadData() hiển thị nội dung của cột CategoryName của bảng Categories. Chúng ta dùng nó vài lần trong phương thức Main() để hiển thị trạng thái hiện tại của bảng Categories. Bảng này sẽ bị thay đổi khi mỗi lệnh insert, update, delete được thực hiện.

Tổng kết

Đối tượng SqlCommand cho phép bạn truy vấn và gửi lệnh đến một database. Nó có các phương thức sử dụng cho các lệnh khác nhau. Phương thức ExecuteReader() trả về một đối tượng SqlDataReader để hiển thị kết quả của câu truy vấn. Cho các lệnh insert, update và delete, bạn dùng phương thức ExecuteNonQuery(). Nếu bạn chỉ cần một giá trị đơn từ một câu truy vấn, phương thức ExecuteScalar() là lựa chọn tốt nhất.