在代码中使用SqlCommand对象
在代碼中使用SqlCommand對象
(2009-09-21 11:16:10) 轉載| ? | 分類：.Net編程類 |
1.1? 創建SqlCommand對象
可以通過三種方式創建SqlCommand對象。第一種方式就是使用new關鍵字直接創建對象的一個新實例，然后設置適當屬性；還可以使用一個可用的構造函數來指定查詢字符串以及 SqlConnection對象；第三種方式是調用SqlConnection類(第3章對這個類進行了介紹)的CreaterCommand方法。這三種方法如下所示：
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
????????? ???"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT CustomerID, CompanyName FROM Customers";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlCommand cmd;
//使用無參數構造函數
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = strSQL;
//使用參數化構造函數
cmd = new SqlCommand(strSQL, cn);
//使用Connection對象的CreateCommand方法
cmd = cn.CreateCommand();
cmd.CommandText = strSQL;
1.2? 使用SqlCommand執行查詢
了解了如何創建SqlCommand對象，下面介紹使用SqlCommand執行查詢的基本情況。
1.3? 執行返回行的查詢
SqlCommand的最常見用法是執行返回結果的查詢。例如要獲取一特定國家(可能是加拿大)發貨訂單的信息，可以使用如下所示的查詢：
SELECT OrderID, CustomerID, OrderDate, ShippedDate, ShipCity
??? FROM Orders WHERE ShipCountry = 'Canada'
要執行此查詢，首先需要將SqlCommand對象的CommandText屬性設置為包含該查詢文本的一個字符串。然后調用SqlCommand對象的ExecuteReader方法，如以下代碼所示：
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
????????? ???"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, CustomerID, OrderDate, ShippedDate, ShipCity" +
????????? " FROM Orders WHERE ShipCountry = 'Canada'";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = strSQL;
SqlDataReader rdr = cmd.ExecuteReader();
1.4? 獲取單一值
在某些時候，可能希望執行一個返回單一值的查詢。例如執行一個簡單查詢，以確定在一個表中存在多少行，或者更復雜一些，如確定一個特定客戶所下訂單的總數是多少，可以使用類似于以下代碼的查詢：
SELECT SUM([Order Details].UnitPrice * [Order Details].Quantity)
? FROM Orders INNER JOIN [Order Details]
??? ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.CustomerID = 'ALFKI'
諸如此類的查詢在許多應用程序中都很常見。以下代碼段執行此查詢，并將訂單總值輸出到控制臺窗口。其中，創建一個SqlDataReader、調用Read、檢查第一行第一列的內容，然后再關閉
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
??? ?????????"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT SUM(UnitPrice * Quantity) " +
??? ???????" FROM Orders INNER JOIN [Order Details]" +
??? ???????" ON Orders.OrderID = [Order Details].OrderID" +
??? ???????" WHERE CustomerID = 'ALFKI'";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlCommand cmd = new SqlCommand(strSQL, cn);
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
decimal decOrderTotal = (decimal)rdr[0];
rdr.Close();
Console.WriteLine("Order Total: {0:c}", decOrderTotal);
為了幫助簡化此類情況，SqlCommand類公開了一個ExecuteScalar方法。ExecuteScalar方法不是返回一個SqlDataReader，而是返回在一般Object數據類型中第一行第一列的值。此類方法通常稱為“syntactic sugar”(語法甜頭)，至少在我工作的地方是這樣稱呼它的。換言之，調用ExecuteScalar有效地創建了一個SqlDataReader，并獲取期望信息，但不需要編寫全部代碼。然后，ExecuteScalar關閉并處置SqlDataReader。
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
??? ??????????"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT SUM(UnitPrice * Quantity) " +
??? ???????" FROM Orders INNER JOIN [Order Details]" +
??? ???????" ON Orders.OrderID = [Order Details].OrderID" +
??? ???????" WHERE CustomerID = 'ALFKI'";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlCommand cmd = new SqlCommand(strSQL, cn);
decimal decOrderTotal = (decimal)cmd.ExecuteScalar();
Console.WriteLine("Order Total: {0:c}", decOrderTotal);
1.5? 執行不返回結果集的查詢
不返回結果集的查詢通常被稱為“操作查詢(action query)”，在本書中有時會使用這一術語。操作查詢共有兩大類。
l?? 數據操作語言(DML)查詢? 也稱為“基于查詢的更新(QBU)”，這些查詢修改數據庫的內容。下面是一些例子：
INSERT INTO Customers (CustomerID, CompanyName)
??? VALUES ('NewID', 'NewCustomer')
UPDATE Customers SET CompanyName = 'NewCompanyName'
??? WHERE CustomerID = 'ALFKI'
DELETE FROM Customers WHERE CustomerID = 'ALFKI'
l?? 數據定義語言(DDL)查詢? 這些查詢修改數據庫的結構，如下例所示：
CREATE TABLE Table1 (Field1 int NOT NULL
??????????????????????? CONSTRAINT PK_Table1 PRIMARY KEY,
??????????????????????? Field2 varchar(32))
ALTER VIEW View1 AS SELECT Field1, Field2 FROM Table1
DROP PROCEDURE StoredProcedure1
可以通過調用SqlCommand的 ExecuteReader方法來執行這些查詢。但是，因為這些查詢不返回任何行，所以似乎是一些不必要的開銷。幸運的是，還有更簡單的方法。 SqlCommand類公開了ExecuteNonQuery方法，該方法執行查詢而不返回SqlDataReader對象。下面的例子說明如何使用這一特性。
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
????????? ???"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "UPDATE Customers SET CompanyName = 'NewValue' " +
????????? "WHERE CustomerID = 'ALFKI'";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlCommand cmd = new SqlCommand(strSQL, cn);
//cmd.ExecuteNonQuery();
int intRecordsAffected = cmd.ExecuteNonQuery();
if (intRecordsAffected == 1)
???? Console.WriteLine("Update succeeded");
else
??? //Assume intRecordsAffected = 0
??? Console.WriteLine("Update failed");
1.6? 執行批量操作查詢
假定您希望成批執行一系列操作查詢。例如，假定有一個Products表，并希望根據產品目錄改變一些產品的單價，一些標為漲價，一些標為降價。可以將以下查詢放在一起，然后同時執行它們。
UPDATE Products SET UnitPrice = UnitPrice * 0.85 WHERE CategoryID = 3;
UPDATE Products SET UnitPrice = UnitPrice * 1.15 WHERE CategoryID = 4;
UPDATE Products SET UnitPrice = UnitPrice * 0.75 WHERE CategoryID = 5;
如果希望知道每個查詢影響了多少行，應當怎么辦呢？理想情況下，可能會調用一個簡單的方法，例如ExecuteNonQuery，使此方法返回一個值的數組，其中，數組的項對應于由批處理中各個查詢所修改的行數。
ExecuteReader和ExecuteNonQuery方法都不能獨自完成此功能。這兩種方法都允許執行批量查詢，但都不能告知每個查詢所修改的行數。ExecuteNonQuery方法的返回值將告知整個批處理修改了多少行，但這一信息也許不能滿足您的需要。
ADO.NET 2.0中的(新的和)經過改進的SqlCommand公開了一個StatementCompleted事件，可以用來收集這一信息。 StatementCompleted事件的主要參數擁有唯一一個很有意義的屬性RecordCount，但是幸運的是，這就是我們要尋找的信息。以下代碼段說明可以如何使用這一事件來確定批量操作中各個查詢所修改的行數。此代碼包括一個處理StatementCompleted事件的過程。如果正在復制和粘貼此代碼，則需要單獨粘貼該過程。
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
????????? ???"Initial Catalog=Northwind;Trusted_Connection=Yes;";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
strSQL = "UPDATE Products SET UnitPrice = UnitPrice * 0.85 " +
??? ???????" WHERE CategoryID = 3;" +
??? ???????"UPDATE Products SET UnitPrice = UnitPrice * 1.15 " +
??? ???????" WHERE CategoryID = 4;" +
??? ???????"UPDATE Products SET UnitPrice = UnitPrice * 0.75 " +
??? ???????" WHERE CategoryID = 5;";
SqlCommand cmd = new SqlCommand(strSQL, cn);
cmd.StatementCompleted +=
??? ??????????new StatementCompletedEventHandler(HandleStatementCompleted);
int intTotalRowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine("TotalRowsAffected: {0} row(s)", intTotalRowsAffected);
cn.Close();
...
static void HandleStatementCompleted(object sender,
??? ????????????????????????????????????????????????StatementCompletedEventArgs e) {
??? Console.WriteLine("Statement Affected {0} row(s)", e.RecordCount);
}
1.7? 執行獲取XML數據的查詢
SQL Server支持以XML形式返回數據的查詢。如果正在使用這種類型的查詢，可以調用SqlCommand的ExecuteXmlReader方法，以通過XmlReader來獲得結果，而不是通過SqlDataReader。在第12章中將更詳細地研究這一情景。
1.8? 在事務中執行查詢
SqlCommand類擁有一個 Transaction屬性，必須對其進行設置以在SqlTransaction中執行SqlCommand。在上一章中，已經了解了如何使用 SqlConnection類的BeginTransaction方法創建SqlTransaction對象。
如果開始了SqlConnection的一個 SqlTransaction，必須將所有查詢與該事務相關聯。否則，將會接收到一個InvalidOperationException，同時帶有一條消息指出：“當分配給該命令的連接處于掛起的本地事務中時，ExecuteNonQuery要求該命令擁有一個事務。該命令的Transaction屬性未被初始化。”
有兩種方式可以將SqlCommand與 SqlTransaction關聯在一起。可以將SqlCommand的Transaction屬性設置為SqlTransaction，也可以將 SqlTransaction傳遞給SqlCommand的構造函數。以下代碼使用第二種方法。在執行查詢并確定該查詢影響多少行之后，該代碼調用 SqlTransaction的Rollback方法，以防止數據庫提交這些更改。
string strConn, strSQL;
strConn = @"Data Source=./SQLExpress;" +
????????? ???"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "UPDATE Products SET UnitPrice = UnitPrice * .7 " +
????????? " WHERE CategoryID = 1";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
using (SqlTransaction txn = cn.BeginTransaction()) {
??? SqlCommand cmd = new SqlCommand(strSQL, cn, txn);
??? int intRecordsAffected = cmd.ExecuteNonQuery();
??? Console.WriteLine("Query affected {0} row(s)", intRecordsAffected);
??? txn.Rollback();
}
cn.Close();
?提示??? 為了確保沒有使事務開放太長時間，請考慮在Using代碼塊中使用SqlTransaction對象，如以上代碼段所示。事務保持開放的時間越長，數據庫需要為該事務維護鎖的時間也就越長，多位用戶嘗試鎖定相同行的機會也就越大。如果在Using代碼塊的末尾沒有提交或回滾SqlTransaction，則會隱式調用Rollback方法。
與50位技術專家面對面20年技術見證，附贈技術全景圖總結
以上是生活随笔為你收集整理的在代码中使用SqlCommand对象的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 李名洋（1983-），男，中国电信股份有
- 下一篇： 《大数据》2015年第4期“金融、安全与