Thực thi lệnh với Database và C#

Chia sẻ: Han Quyet | Ngày: | Loại File: DOC | Số trang:12

Thêm vào BST

Báo xấu

159
lượt xem 31
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

cách tạo một mệnh lệnh (command) và thực thi trên cơ sở dữ liệu (database). Tạo một mệnh lệnh (CREATING A COMMAND) Có rất nhiều cách ngoài cách sử dụng SqlCommand nhưng mình sẽ dùng cách này để làm ví dụ vì nó được cung cấp tốt nhất trong việc liên kết với cơ sở dữ liệu SQL. Mở Visual C# chọn Console Application và dùng thử code sau để phân tích :

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Thực thi lệnh với Database và C#

Thực thi lệnh với Database và C# cách tạo một mệnh lệnh (command) và thực thi trên cơ sở dữ liệu (database). Tạo một mệnh lệnh (CREATING A COMMAND) Có rất nhiều cách ngoài cách sử dụng SqlCommand nhưng mình sẽ dùng cách này để làm ví dụ vì nó được cung cấp tốt nhất trong việc liên kết với cơ sở dữ liệu SQL. Mở Visual C# chọn Console Application và dùng thử code sau để phân tích : Code: using System; using System.Data; using System.Data.SqlClient; class MenhLenh { public static void Main() { // tạo một kết nối SqlConnection conn = new SqlConnection(@"Server = .\SQLEXPRESS; Integrated Security = True; Database = Northwind"); // tạo một đối tượng giữ lệnh cần thực thi SqlCommand cmd = new SqlCommand(); Console.WriteLine("Command created."); try { // mở kết nối conn.Open(); } catch (SqlException e) { Console.WriteLine(e.ToString()); } finally { // đóng kết nối conn.Close(); Console.WriteLine("Connection Closed."); } } } Ở đây thông qua lớp ‘SqlCommand’ mình tạo một đối tượng để nắm giữ thông tin về lệnh. Sau khi tạo xong thì thông báo ra Console “Command created.” Chương trình về cơ bản là vậy rất đơn giản dễ hiểu. Đó là cách tạo đối tượng nắm giữ mệnh lệnh. Mệnh lệnh tạo ra chỉ có tác dụng KHI VÀ CHỈ KHI nó được chỉ định thực thi trong kết nối nào. Vì vậy phải cho nó đi kèm với một kết nối tồn tại để tạo và thi hành mệnh lệnh được viết ra. Ví dụ sau mình sẽ minh họa các gán mệnh lệnh vào một kết nối.
Code: using System; using System.Data; using System.Data.SqlClient; class MenhLenh { public static void Main() { // tạo một kết nối SqlConnection conn = new SqlConnection(@"Server = .\SQLEXPRESS; Integrated Security = True; Database = Northwind"); // tạo một đối tượng giữ lệnh cần thực thi SqlCommand cmd = new SqlCommand(); Console.WriteLine("Command created."); try { // mở kết nối conn.Open(); // gán mệnh lệnh vào kết nối cmd.Connection = conn; Console.WriteLine("Connected command to connection !"); } catch (SqlException e) { Console.WriteLine(e.ToString()); } finally { // đóng kết nối conn.Close(); Console.WriteLine("Connection Closed."); } } } Bạn dễ thấy ví dụ này cũng chính là ví dụ ban đầu nhưng mình chỉ có thay đổi thêm một chút trong khối lệnh ‘try’ Code: // gán mệnh lệnh vào kết nối cmd.Connection = conn; Console.WriteLine("Connected command to connection !"); Sau khi ở trên mình đã tạo ra một kết nối tới cơ sở dữ liệu và một đối tượng nắm giữ mệnh lệnh thi hành thì bên dướ mình thử gán mệnh lệnh với kết nối. Trong đối tượng của mệnh lệnh có một thuộc tính (properties) để gán vào một kết nối
nào đó : CommandObject.Connection = ConnectionObject; Như ở trên thì sau khi gán kết nối xong thì Console sẽ thông báo ra kết quả là gán mệnh lệnh vào kết nối thành công. Câu hỏi đặt ra ở đây là : Tại sao không gán kết nối ngay khi tạo ra đối tượng kết nối mà phải cho vào khối ‘try..catch..finally’ làm cái gì ? Câu trả lời rất dễ hiểu: Bạn thử nghĩ xem sau khi bạn tạo kết nối thì muốn kích hoạt và kiểm tra kết nối có tốt hay không thì phải ném vào trong khối ‘try..catch’. Giả sử mà bạn khai báo gán mệnh lệnh ở ngay trước khối ‘try..catch’ và kết nối thành công thì không có lỗi gì cả. Nhưng trong trường hợp nếu có lỗi biến cố bất ngờ xảy ra thì khi gán mệnh lệnh vào kết nối sẽ có lỗi và chương trình sẽ ngưng hoạt động và ở tình trạng treo mà vẫn tiêu thụ tài nguyên máy, rất lãng phí. Vì vậy mà tốt nhất gán trong khối ‘try..catch’ sau khi mở kết nối để gặp lỗi thì ta có thể biết được lỗi và nguyên nhân gây lỗi và sửa (debug).Giải thích thế này không biết có hiểu không ^_^! Tuy nhiên nếu bạn chắc chắn là kết nối đảm bảo tốt thì bạn có thể gán mệnh lệnh vào kết nối ngay từ đầu và rút ngắn lại code Code: SqlCommand cmd = conn.CreateCommand(); Tương đương với 2 dòng code code: SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; Bạn đã tạo được đối tượng nắm giữ mệnh lệnh và gán được vào kết nối, bây giờ phải viết mệnh lệnh cho đối tượng. Ta dùng thuộc tình ‘CommandObject.CommandText’ để tạo một lệnh cần thực thi với cơ sở dữ liệu. Bạn vẫn dùng code ở trên nhưng chỉ thay đổi đi phần code trong khối ‘try..catch’ bằng phần code dưới đây nhé : Code: try { // mở kết nối conn.Open(); // gán mệnh lệnh vào kết nối cmd.Connection = conn; Console.WriteLine("Connected command to connection !"); // tạo một lệnh cho đối tượng cmd.CommandText = @" SELECT count(*) FROM Employees "; Console.WriteLine(" Ready to execute SQL command : {0} ", cmd.CommandText); }
Bạn để ý ở đây tớ có thêm 2 dòng code khác bên dưới. ‘CommandText’ ở đây là một lệnh SQL, nếu bạn viết cái gì đó khác hơn là một câu lệnh SQL thì sẽ có lỗi thông báo khi thực thi lệnh này, đây là đoạn trick nếu mà bạn không thông thạo về cấu trúc câu lệnh SQL . Xem bài 1 ‘Cơ bản về SQL cho người mới bắt đầu’ để biết chút cơ bản ban đầu về SQL nhé. Thực thi mệnh lệnh (EXECUTING COMMAND) Bây giờ thử thực thi câu lệnh nhé xem kết quả thế nào. Nhưng khi thực thi một lệnh thì kết quả sẽ cho ra khác nhau nếu như bạn sử dụng phương thức thực thi (execution method) khác nhau. Có 4 cách thực thi lệnh theo bảng sau : ExecuteNonQuery() -------------------> không trả về gì vì không phải Query 1. -------------------> Một giá trị duy nhất 2. ExecuteScalar() ExecuteReader() -------------------> Không hoặc nhiều hàng 3. 4. ExecuteXmlReader -------------------> XML À bạn có thể hiểu query là một biểu thức có giá trị trả về. Bây giờ vẫn bài ở trên tớ dùng ‘ExecuteScalar’ để thu giá trị lấy về using System; using System.Data; using System.Data.SqlClient; class MenhLenh { public static void Main() { // tạo một kết nối SqlConnection conn = new SqlConnection(@"Server = .\SQLEXPRESS; Integrated Security = True; Database = Northwind"); // tạo một đối tượng giữ lệnh cần thực thi SqlCommand cmd = new SqlCommand(); Console.WriteLine("Command created."); try { // mở kết nối conn.Open(); // gán mệnh lệnh vào kết nối cmd.Connection = conn; Console.WriteLine("Connected command to connection !"); // tạo một lệnh cho đối tượng cmd.CommandText = @"SELECT count(*) FROM Employees "; Console.WriteLine("Ready to execute SQL command : {0} ", cmd.CommandText); // Thực thi câu lệnh
Console.WriteLine("Number of Employees is {0} ", cmd.ExecuteScalar()); } catch (SqlException e) { Console.WriteLine(e.ToString()); } finally { // đóng kết nối conn.Close(); Console.WriteLine("Connection Closed."); } } } Ở đây bạn sẽ thu kết quả là : ‘Number of Employees is 9’. Bạn học thêm một câu lệnh SQL mới luôn nhé. Để đếm số dòng (rows) trong một bảng ta dùng tham số ‘count(*)’ Cách gọi : SELECT count(*) FROM Giá trị trả về là kiểu ‘Object’ và có một kết quả nên sử dụng ‘ExecuteScalar’. Bạn nên chú ý ở đây : ‘ExecuteScalar’ trả về kiểu ‘Object’ và trong môi trường .NET Framework thì cơ sở dữ liệu có thể chứa bất cứ kiểu dữ liệu nào. Khi đưa ra Console dùng ‘WriteLine’ thì tất cả các kiểu đều bị convert sang kiểu kí tự in ra màn hình hết. Bạn có thể chuyển giá trị thu được về một kiểu nào đó nhưng nếu convert không hợp lệ thì sẽ có ‘Runtime Error : Invaliad type cast’ hay gì đó đại loại như thế. Mình giả sử bài ở trên mình muốn giá trị thu về kiểu ‘Int’ thì mình code như sau : int count = (int)cmd.ExecuteScalar(); Console.WriteLine(“ Number of Employees is {0} “, count); Nếu như mình vẫn dùng thêm cái code convert ở ngay trên đây và thay đổi đi câu lệnh thực thi (commandText) thành : cmd.CommandText = “SELECT firstname FROM employees WHERE lastname = ‘Davolio’ Thì với ExecuteScalar() sẽ trả về kiểu Object có giá trị là “Nancy” thực chất là kiểu String (C#) nhưng mà ở trên bạn lại cast nó sang kiểu ‘Int’ và màn hình sẽ thông báo lỗi như sau Unhandled Exception : System.InvalidCastException: Specified cast is not valid. Kiểu ‘String’ không thể bị convert sang kiểu ‘Int’. Bây giờ chúng ta cùng thực hiện câu lệnh thu về nhiều kết quả xem. Tất nhiên là sử dụng : ExecuteReader() using System;
using System.Data; using System.Data.SqlClient; class MenhLenh { public static void Main() { // tạo một kết nối SqlConnection conn = new SqlConnection(@"Server = .\SQLEXPRESS; Integrated Security = True; Database = Northwind"); // tạo một đối tượng giữ lệnh cần thực thi SqlCommand cmd = new SqlCommand(); Console.WriteLine("Command created."); try { // mở kết nối conn.Open(); // gán mệnh lệnh vào kết nối cmd.Connection = conn; Console.WriteLine("Connected command to connection !"); // tạo một lệnh cho đối tượng cmd.CommandText = @"SELECT firstname, lastname FROM Employees "; Console.WriteLine("Ready to execute SQL command : {0} ", cmd.CommandText); // Thực thi câu lệnh SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(" Employee name : {0} {1} ", reader.GetValue(0), reader.GetValue(1)); } reader.Close(); } catch (SqlException e) { Console.WriteLine(e.ToString()); } finally { // đóng kết nối conn.Close(); Console.WriteLine("Connection Closed."); } } } Ở đây ta dùng query đẻ lấy về họ tên trong bảng Employees. Và phương thức : ExecuteReader() trả về giá trị là một đối tượng SqlDataReader. Vì thế ở đây tớ tạo ra một đối tượng SqlDataReader là reader để thu nhận tất cả kết quả lấy dược từ database. Kết quả sẽ theo từng dòng (row) và mỗi lần đọc ‘reader.Read()’
thì nó chứa giá trị có được từ bảng theo dòng và chi nắm giữ thông tin lấy chứ không phải tất cả các cột có trong bảng. Ví dụ ở đây tớ chỉ lấy 2 cột họ và tên thì mỗi hàng trong reader khi Read() thì chỉ có 2 value tương ứng với Value(1) là họ và Value(0) là tên. Nếu như ở đây mà bạn GetValue(i) với i khác 0 và 1 thì chương trình sẽ treo . Phương thức Read() sau mỗi lần thực hiện thì nó sẽ trả về hàng tiếp theo. Do dó để đọc hết kết quả thu được ở đây tớ dùng vòng lặp ‘while’ đọc cho đến khi không còn hàng nào nữa thì thôi. Để thực thi một biểu thức thì ta sử dụng : ExecuteNonQuery(). Đơn giản vì biểu thức chẳng trả về giá trị gì cả. Ví dụ bên dưới minh họa cách dùng : Code: using System; using System.Data; using System.Data.SqlClient; public class ThiHanhMenhLenh { public static void Main() { SqlConnection conn = new SqlConnection(@"Server = .\SQLEXPRESS; Integrated Security = True; Database = Northwind"); string querySelect = @"SELECT count(*) FROM Employees"; string queryInsert = @"INSERT INTO Employees (firstname,lastname) VALUES ('Pete','Houston')"; string queryDelete = @"DELETE FROM Employees WHERE firstname = 'Pete' AND lastname = 'Houston'"; SqlCommand cmdSelect = new SqlCommand(querySelect, conn); SqlCommand cmdInsert = new SqlCommand(queryInsert, conn); SqlCommand cmdDelete = new SqlCommand(queryDelete, conn); try { conn.Open(); Console.WriteLine("Open connection !"); Console.WriteLine("Before INSERT : "); Console.WriteLine(" Number of employees : {0} ", cmdSelect.ExecuteScalar()); cmdInsert.ExecuteNonQuery(); Console.WriteLine("Insert complete !"); Console.WriteLine("After INSERT : "); Console.WriteLine(" Number of employees : {0} ", cmdSelect.ExecuteScalar()); cmdDelete.ExecuteNonQuery(); Console.WriteLine("Delete complete !"); Console.WriteLine("After DELETE : "); Console.WriteLine(" Number of employees : {0} ", cmdSelect.ExecuteScalar());
} catch (SqlException sqle) { Console.WriteLine(sqle.ToString()); } finally { conn.Close(); Console.WriteLine("Close connection !"); } } } Như bạn thấy ở đây mình tạo ra hai đối tượng cmdInsert và cmdDelete dùng để thực thi hai biểu thức thi hành xóa và chèn thêm giá trị vào bảng, vì thế mà nó không có giá trị trả về và dùng ExecuteNonQuery. Việc thực thi mệnh lệnh không có gì khó khăn cả và phụ thuộc vào mục đích sử dụng của bạn để thu về kết quả cần tìm. Tham số truyền vào lệnh (COMMAND PARAMETERS) Nhiều lúc mà code như trên ấy, mình muốn thay đổi mệnh lệnh chẳng lẽ cứ phải thay đổi cái CommandText….Nếu như thế là code không linh đông, mình muốn thay đổi và thực thi câu lệnh bất cứ khi nào muốn thì lám sao. May thay .NET Framework có hỗ trợ tham số truyên vào lệnh trong biểu thức SQL và ta có thể thay đổi thông tin cần thiết trong CommandText. Nếu thủ công lao động chân tay thì có thể theo kiểu này : code: String fname = “Pete”; String lname = “Houston”; String value = “(‘” + fname + “’,” + “’” + lname + “’)”; String queryInsert = @” INSERT INTO Employees (firstname, lastname) VALUES ” + value; Nhưng mà kiểu này thì khác gì làm như trên, nhìn code lại hoa cả mắt. Cách tốt nhất là làm việc với tham số truyền trong SQL. Trong câu lệnh SQL thì tham số được kí hiệu với dấu @ ở trước.. Ví dụ với câu lệnh INSERT : code: INSERT INTO MyTable VALUES (@MyName, @MyNumber) OK ! Thử làm xem nó ra sao. Dưới đây là code minh họa Code: using System; using System.Data; using System.Data.SqlClient; public class ThiHanhMenhLenh
{ public static void Main() { SqlConnection conn = new SqlConnection(@"Server = .\SQLEXPRESS; Integrated Security = True; Database = Northwind"); string fname = "Pete"; string lname = "Houston"; string querySelect = @"SELECT firstname, lastname FROM Employees WHERE EmployeeID > 9"; string queryInsert = @"INSERT INTO Employees (firstname,lastname) VALUES (@fname,@lname)"; SqlCommand cmdSelect = new SqlCommand(querySelect, conn); SqlCommand cmdInsert = new SqlCommand(queryInsert, conn); cmdInsert.Parameters.Add("@fname", SqlDbType.NVarChar, 10); cmdInsert.Parameters.Add("@lname", SqlDbType.NVarChar, 20); try { conn.Open(); Console.WriteLine("Open connection !"); cmdInsert.Parameters["@fname"].Value = fname; cmdInsert.Parameters["@lname"].Value = lname; cmdInsert.ExecuteNonQuery(); SqlDataReader reader = cmdSelect.ExecuteReader(); while (reader.Read()) { Console.WriteLine(" New Employee : {0} {1} ", reader.GetValue(0), reader.GetValue(1)); } reader.Close(); } catch (SqlException sqle) { Console.WriteLine(sqle.ToString()); } finally { conn.Close(); Console.WriteLine("Close connection !"); } } } Đầu tiên ta khai báo tham số truyền vào trong chuôi lệnh thực thi SQL : Code: cmdInsert.Parameters.Add("@fname", SqlDbType.NVarChar, 10); cmdInsert.Parameters.Add("@lname", SqlDbType.NVarChar, 20); Sau đó thì truyền vào giá trị cần thực hiện của tham số :
Code: cmdInsert.Parameters["@fname"].Value = fname; cmdInsert.Parameters["@lname"].Value = lname; Chú ý là mỗi lần thực hiện xong một lệnh thì bạn phải truyền lại CommandText nếu không lệnh sẽ không có hiệu lực. Nhưng như thế thì rất là bất tiện vì thế một phương thức được tạo ra để chúng ta thi hành một lệnh nhiều lần : CommandObject.Prepare(). Nhưng nếu mà thay đổi CommandText thì phương thức prepare() sẽ lập tức mất ngay hiệu lực [?????] using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { //tao ket noi toi sql server SqlConnection conn=new SqlConnection(); conn.ConnectionString = "server=.//SQLExpress;"+ "integrated security=true;"+ "database=northwnd";//database northwnd bat ky // tao query (la 1 cau lenh trong sql) //TRINH BAY NHU TREN CHO DE HIEU VE CAU TRUC SQL string sql = "SELECT "+ "*" + "FROM"+ "Employees"; //cung cap 1 datastream duoi ket qua SqlDataReader datareader = null; try { Console.WriteLine("connect start"); //mo ket noi conn.Open(); //thuc thi sql query SqlCommand cmd = new SqlCommand(sql, conn); //tra ve gia tri co doi tiuong datareader = cmd.ExecuteReader();
Console.WriteLine("SQl Privider instances");//giai thich thoi Console.WriteLine("truy van co so du lieu {0} voi cau lenh truy van la {1}", conn.Database, cmd.CommandText); Console.WriteLine("FirstName/t" + "LastName/n"); while (datareader.Read()) //kiem tra xem con data stream khong //dua ket qua ra man hinh Console.WriteLine("{0}/t{1}", datareader["FirstName"].ToString().PadLeft(8), datareader["LastName"].ToString().PadLeft(8)); } catch (SqlException e) { //trong runtime neu ma xay ra ngaoi le dong nay se duoc thuc // hien(thuong la 1 list cac dong) Console.WriteLine(e.ToString()); } finally { //cho du 1 trong 2 cau lenh tren co xay ra di chang nua thi //dong ket noi toi sql server datareader.Close(); conn.Close(); Console.WriteLine("connection closed"); } } } } Nếu bạn sử dụng sql2000 thì bạn có thể thay đổi //tao ket noi toi sql server SqlConnection conn=new SqlConnection(); conn.ConnectionString = "server=.//SQLExpress;"+ "integrated security=true;"+ "database=northwnd";//database northwnd bat ky Thành //tao ket noi toi sql server SqlConnection conn=new SqlConnection(System.configuration.configurationmanager.connectionstrin g["strconn"].Connectionstring().Tostring()); Trong đó ở file Configuration bạn phải khai báo chuối kết nối cho strconn đến csdl northwind.
Không phải. Nếu bạn viết ứng dụng của bạn là Webform thì cấu hình kết nối đến csdl thì bạn nên khai báo vào File webconfig còn nếu ứng dụng của bạn là Winform hay console App thì bạn lưu cấu hình kết nối tới csdl thông qua file appconfig. Nội dung của nó là trong cặp thẻ bạn chèn chuỗi kết nối là : Nội dung đầy đủ sẽ là Mình nghĩ vấn đề ở đây có thể bạn tạo connection chưa chính xác. Bạn có thể vào đây để thử các kiểu connection phù hợp với Sql Server tại máy bạn đã cài. http://www.connectionstrings.com/?carrier=sqlserver