SqlCommand的ExecuteScaler的使用時機
前一陣子有同事問我，SqlCommand的ExecuteScaler方法是做什麼用的？為什麼要用ExecuteScaler呢？怎麼不用ExecuteNonQuery就好了。是的，當然也沒有錯。不過ExecuteScaler還是有它的使用時機與一點點的價值存在的，因為ExecuteScaler早在9年前的.NET Framework 1.0的ADO.NET中就已經有這個方法了，到現在也依然存在。而當同事提出個疑問時，我當時的回答是這樣的，通常會使用ExecuteScaler方法的目的不外乎下面三種狀況：
- 只要傳回單一值，如SQL中常用的COUNT(*)、SUM(*) 、AVG(*)等聚合函數 或傳回IDENTITY值等。
- 精簡程式碼，因為我只要單一值傳回，所以我不要寫一大串的程式碼。
- 由於只需要傳回一個單一值需要有較好的效能，我不要再Fill 一個DataSet或是DataTable，這時ExecuteScaler就派上用場。
比如在第一種中，間我想要取回在Northwind資料庫中某位Customer的Orders數量，程式可能會這樣寫，同常可能寫一個共用的Scaler方法。
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Collections.Generic;
5: using System.Linq;
6: using System.Web;
7: using System.Configuration;
8:
9: /// <summary>
10: /// DAL 的摘要描述
11: /// </summary>
12: public class DAL
13: {
14: private void PrepareCommand(
15: ref SqlCommand cmd,
16: SqlConnection conn,
17: string cmdText,
18: SqlParameter[] cmdParms)
19: {
20: if (conn.State != ConnectionState.Open)
21: conn.Open();
22: cmd.Connection = conn;
23: cmd.CommandText = cmdText;
24: cmd.CommandType = CommandType.Text;
25: if (cmdParms != null)
26: {
27: foreach (SqlParameter parm in cmdParms)
28: cmd.Parameters.Add(parm);
29: }
30: }
31:
32: public object GetScaler(string SqlStatement, SqlParameter [] parames)
33: {
34: using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
35: {
36: SqlCommand cmd = new SqlCommand(SqlStatement, cnn);
37: try
38: {
39: PrepareCommand(ref cmd, cnn, SqlStatement, parames);
40: return cmd.ExecuteScalar();
41: }
42: catch (Exception ex)
43: {
44: throw ex;
45: }
46: finally
47: {
48: if (cnn.State != ConnectionState.Closed)
49: cnn.Close();
50: cnn.Dispose();
51: }
52: }
53: }
54:
55: public DAL()
56: {
57: //
58: // TODO: 在此加入建構函式的程式碼
59: //
60: }
61: }
如上程式非常的單存，其實就是一行cmd.ExecuteScalar 即取回我們要的結果。另外商業邏輯Orders.cs 中的SQL 敘述如下：
1: public class Orders
2: {
3: /// <summary>
4: ///
5: /// </summary>
6: /// <param name="CustomerID"></param>
7: /// <returns></returns>
8: public string GetOrdersCountByCustomerID(string CustomerID)
9: {
10: string SqlStatement = @"select COUNT(*) AS COUNT_COL
11: from Orders s
12: where s.CustomerID=@CustomerID";
13: SqlParameter[] parameter = new SqlParameter[] {
14: new SqlParameter("@CustomerID", SqlDbType.VarChar)
15: };
16: parameter[0].Value = CustomerID;
17: return new DAL().GetScaler(SqlStatement, parameter).ToString();
18: }
19:
20: public Orders()
21: {
22: //
23: // TODO: 在此加入建構函式的程式碼
24: //
25: }
26: }
如上商業邏輯的程式，SQL敘述很單存的計算COUNT(*)，所以一定會有一個傳回值，即使目前沒有任何資料，也會傳回0，但並不表示所有敘述都會有值傳回來。
因此需要注意的地方，如果你的SQL傳回的資料有可能是空值的時候，也就是說您查詢的SQL並不是COUNT 等聚合函數時，你是使用SUM、AVG 等，接回來的就不見得會有資料。此時接回的 object 不要就直接ToString() 以免直接出現 null reference的錯誤。
比如SQL 是為了抓身分證字號，可是這樣的敘述是有可能是沒有結果的空值 ：
SELECT TOP 1 E.CHT_NAME FROM EMPLOYESS E WHERE E.AID=@AID
計算AVG 也是有可能得到NULL，因為筆數為0，這時計算不出結果時也是NULL，如下：
select Avg(s.Freight) AS AVG_COL from Orders s where s.CustomerID='任意錯誤的客戶編號'
而此時在.NET 的 Runtime下 executeScaler 回來會是null值。可是卻直接ToString() 回傳結果(如下紅色部分)。就會發生null reference，這是使用時須要注意的地方。
1: public string GetScaler(string SqlStatement, SqlParameter [] parames)
2: {
3: using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
4: {
5: SqlCommand cmd = new SqlCommand(SqlStatement, cnn);
6: try
7: {
8: PrepareCommand(ref cmd, cnn, SqlStatement, parames);
9: object o = cmd.ExecuteScalar();
10: return o.ToString();
11: }
12: catch (Exception ex)
13: {
14: throw ex;
15: }
16: finally
17: {
18: if (cnn.State != ConnectionState.Closed)
19: cnn.Close();
20: cnn.Dispose();
21: }
22: }
23: }
這也是筆者接下之前系統時，在DAL中時經常看見的狀況。提供給各位參考。
簽名：
學習是一趟奇妙的旅程
這當中，有辛苦、有心酸、也有成果。有時也會有瓶頸。要能夠繼續勇往直前就必須保有一顆最熱誠的心。
軟體開發之路（FB 社團）：https://www.facebook.com/groups/361804473860062/
Gelis 程式設計訓練營（粉絲團）：https://www.facebook.com/gelis.dev.learning/
如果文章對您有用，幫我點一下讚，或是點一下『我要推薦』，這會讓我更有動力的為各位讀者撰寫下一篇文章。
非常謝謝各位的支持與愛護，小弟在此位各位說聲謝謝!!! ^_^
