ExecuteScalar拿不到Update後的影響筆數？ExecuteNonQuery與ExecuteScalar的使用探討

是否有發生過ExecuteScalar()拿不到Insert、Delete、Update之影響筆數的困擾？那為何可以拿到Select的回傳值？ExecuteNonQuery為何明明是NonQuery卻拿的到Insert、Delete、Update執行後的影響筆數？ 這篇文章逐一的來實驗一下，ExecuteNonQuery與ExecuteScalar那些指令下可以拿到回傳值，而哪些又不行。

先建立基本心法，以免被下面的實驗搞混亂了

ExecuteNonQuery();
專門用來執行非查詢式的SQL語法，所以NonQuery的意思即是指用於「非查詢」的語法，也就是INSERT、UPDATE與DELETE。

ExecuteScalar();
最方便用來查詢回傳單一值的語法，例如：SELECT count(*)。

實驗環境程式碼

  1. 資料庫名稱：TEST
  2. 欄位一：iAutoIndex int 自動增量索引
  3. 欄位二：cName NVarChar(50)

以下這邊把ADO.NET的經典SQL語法框架列出，讓想要自己實驗的人可以方便的複製貼上。

using (System.Data.SqlClient.SqlConnection oConn = new System.Data.SqlClient.SqlConnection("YourDB"))
{
  using (System.Data.SqlClient.SqlCommand oCmd = new System.Data.SqlClient.SqlCommand())
  {
    oCmd.Connection = oConn;
    oConn.Open();
    oCmd.CommandText = "";
    oCmd.Parameters.Add("@iAutoIndex", System.Data.SqlDbType.Int).Value = 1;
    oCmd.Parameters.Add("@cName", System.Data.SqlDbType.NVarChar, 50).Value = "";
    
    object oTemp = oCmd.ExecuteScalar();
    int iTemp = System.Convert.ToInt32(oTemp);

    //可以縮寫成下方程式碼，比較精簡
    //int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());
    //int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());
  }
}

實驗：SQL Insert

INSERT INTO TEST (cName) VALUES (@cName)

//很OK，回傳插入筆數
int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());

//不OK。回傳型別恆等於null
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

這邊要特別提醒一下，oCmd.ExecuteScalar()沒有回傳值的時候，是回傳「null」而不是「System.DBNull」，千萬不要拿DBNull去判斷，不然就中坑了。而null經過System.Convert.ToInt32轉換後恆為「0」。

實驗：SQL Insert（插入後馬上取回Index、IDENTITY）

INSERT INTO TEST (cName) VALUES (@cName); SELECT SCOPE_IDENTITY();

//不OK。依然取到插入筆數
int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());

//很OK。取得到最後的索引值編號
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

實驗：SQL Delete

DELETE FROM TEST WHERE iAutoIndex in (3, 4, 5)

//很OK，回傳刪除筆數
int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());

//不OK。回傳型別恆等於null
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

實驗：SQL Update

Update TEST SET cName=@cName WHERE iAutoIndex in (9, 10)

//很OK，回傳更新筆數
int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());

//不OK。回傳型別恆等於null
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

實驗：SQL Select

Select * From TEST WHERE iAutoIndex in (9, 10)

//不OK。回傳值永遠恆等於「-1」
int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());

//很OK，回傳第一列（First Row）第一欄（First Column）的資料
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

之前實驗中無往不利的ExecuteNonQuery()，終於在SELECT指令中敗陣下來，簡單的記憶方法就是，不要把NonQuery的指令拿去Query（SELECT），這樣在有時頭腦寫CODE寫到混亂時，這個口訣還蠻好用的。

註：ExecuteScalar中的Scalar代表的意思就是「數量、純量」的意思。

實驗：SQL Select（使用聚合函數count();）

Select count(*) From TEST WHERE iAutoIndex in (9, 10)

//不OK。回傳值永遠恆等於「-1」
int iTemp = System.Convert.ToInt32(oCmd.ExecuteNonQuery());

//很OK，回傳count找到的加總筆數
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

使用聚合函數方式的SELECT自然是一樣的結果了。

使用SQL OUTPUT子句，讓ExecuteScalar()也有能力拿到資料

如果在某些應用場合，限定只能使用用ExecuteScalar()來執行，那麼如果資料庫是Microsoft SQL SERVER 2005（含以上）的版本的話，是可以使用OUTPUT 子句 (Transact-SQL)（OUTPUT Clause），來讓ExecuteScalar();也能讓拿到INSERT、DELETE、UPDATE的返回資料，具體的使用方式如下：

實驗：SQL OUTPUT for Insert

INSERT INTO TEST (cName) OUTPUT INSERTED.cName VALUES (@cName)

//很OK，回傳插入的cName字串值
string cTemp = System.Convert.ToString(oCmd.ExecuteScalar());

不知道大家看到這邊是否有發現，若不想使用SELECT SCOPE_IDENTITY();來取回當下資料庫賦予的自動索引值的話，其實可以使用「INSERTED.iAutoIndex」這個方式去拿也可以喔！

實驗：SQL OUTPUT for Update

UPDATE TEST SET cName=@cName OUTPUT INSERTED.iAutoIndex WHERE iAutoIndex in (13, 14)

//很OK，回傳插入的第一列（First Row）的iAutoIndex欄的資料，也就是「13」
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

實驗：SQL OUTPUT for Delete

DELETE FROM TEST OUTPUT DELETED.iAutoIndex WHERE iAutoIndex in (13, 14)

//很OK，回傳刪除的第一列（First Row）的iAutoIndex欄的資料，也就是「13」
int iTemp = System.Convert.ToInt32(oCmd.ExecuteScalar());

如何把SQL OUTPUT子句的資料列舉取出

要知道，OUTPUT子句所回傳的資料可不是單一行單一欄位而已，如果真的有需要把所有的資料跑一次取出來，那麼ExecuteReader();會是你的好朋友。以下是簡單的程式範例：

var oReader = oCmd.ExecuteReader();
while(oReader.Read()) 
{
  cTemp += oReader.GetValue(0).ToString();
}

以上是ExecuteNonQuery與ExecuteScalar的使用實驗過程，另外也介紹了SQL OUTPUT子句的使用方式，希望對大家有所理解與幫助。

ExecuteNonQuery() ExecuteScalar() ExecuteReader() SCOPE_IDENTITY() OutputClause ExecuteScalar INSERTED DELETED