SQL Server 和 .NET：在程式碼中插入失敗（靜默！），但在手動執行時不會

我的插入儲存過程：

ALTER procedure proj_ins_all 
(
@proj_number INT, 
@usr_id INT, 
@download DATETIME, 
@status INT 
)
as
INSERT INTO project
(proj_number, usr_id, date_download, status_id)
VALUES
(@proj_number, @usr_id, @download, @status)
select SCOPE_IDENTITY()

…手動呼叫時執行良好，如下所示：

exec proj_ins_all 9001210, 2, '2009-09-03', 2 

…但是當從程式碼中呼叫時：

_id = data.ExecuteIntScalar("proj_ins_all", arrParams);

…插入不會發生。現在，標識列確實增加了，_id確實設置為它的值。但該行本身從未出現在表中。

我能想到的最好的猜測是一個插入觸發器，它刪除了新插入的行，但表上沒有觸發器（為什麼手動完成它會起作用？）。我的其他嘗試是圍繞儲存過程以某種方式回滾插入的猜測，因此將begin和end以及go和分號放入儲存過程中以正確分隔“插入”和“身份選擇”位。那什麼都沒解決。

有任何想法嗎？

更新：

感謝到目前為止所有幫助過的人。根據 Preet 的建議（第一個答案），我學會瞭如何使用 SQL Server Profiler（我不敢相信我以前從來不知道它 - 我認為它只對性能調整有用，沒有意識到我可以確切地看到查詢正在發生什麼與它一起到數據庫）。

它顯示 SqlCommand.ExecuteScalar() 方法發送的 SQL 與我手動執行的略有不同。它正在發送：

exec proj_ins_all @proj_number=9001810,@usr_id=2,@download='2009-09-03 16:20:11.7130000',@status=2

我手動執行它，瞧！一個實際的 SQL 伺服器錯誤（！）：

將數據類型 varchar 轉換為日期時間時出錯。

由於我是手動測試的，我只是將日期時間從**‘2009-09-03 16:20:11.7130000’縮短到‘2009-09-03 16:20:11’**並修復了錯誤；現在插入的行很好。

但這引出了一個問題：為什麼 Microsoft 的 SQL Server 不能在該日期時間參數中處理超過 23 個字元？是微軟的 SqlCommand.ExecuteScalar() 方法構造了這樣的查詢，而不是我。這是一個問題，因為我的程式碼仍然無法正常工作。

一旦我手動操作它，我就研究瞭如何在程式碼中為日期設置 SqlParameter，以便它發送一個與有效值類似的值。我嘗試將數據類型從SqlDbType.DateTime更改為SqlDbType.SmallDateTime。分析器顯示這確實產生了更短的日期時間值“2009-09-03 17:15:00”，但插入仍然默默失敗（原始問題）。但是，當我從探查器複製粘貼 sql 並手動嘗試時 - 它起作用了。沒有錯誤。發送它作為 varchar 的相同交易 - SSMS 查詢視窗喜歡它，通過 .net 的相同查詢靜默失敗。

:(

還有其他想法嗎？SQL 伺服器中的一些“環境”類型設置，如“設置 ansi_nulls off”或手動和直通程式碼連接之間可能不同的任何東西？

（另一個問題是為什麼每次出現這個錯誤時sql server 不給我一個錯誤資訊並產生一個異常？）

.Net 程式碼

根據 van 的要求，這裡是相關的 .NET 程式碼（C# ASP.NET）：

data.MakeInParam("@proj_number", SqlDbType.Int, _projNo),
data.MakeInParam("@usr_id", SqlDbType.Int, _usr.Id),
data.MakeInParam("@download", SqlDbType.SmallDateTime, _downloadDate),
data.MakeInParam("@status", SqlDbType.Int, (int)_status),

MakeInParam 有這一行：

param = new SqlParameter(paramName, DbType);

它會像這樣執行：

SqlCommand cmd = CreateCommand(procName, prams);
object result = cmd.ExecuteScalar();

注意：在上面的參數中，問題出在**@download**。_downloadDate 是一個可為空的日期時間。請注意，此行中的 sql 類型曾經是 SqlDbType.DateTime，但我將其更改為 SqlDbType.SmallDateTime 以生成手動呼叫（從程式碼執行時仍然失敗）。

更新：解決 感謝馬特，我終於找到並解決了這個問題：自定義助手類在它的一個函式中缺少一個 cmd.Transaction.Commit() ！它裡面有一堆不同的亂七八糟的東西，我在最初瀏覽程式碼時錯過了這一點。

感謝 Matt 和 Preet 以及其他所有做出貢獻的人。

經驗教訓

• SQL Profiler 非常有用。花時間學習如何使用它。

• 如果數據庫正在吞噬錯誤，或者似乎正在根據您的程式碼執行某些操作，但實際上並沒有出現在數據庫中，請檢查事務是否正在送出。

• 不要太相信別人寫的數據庫助手類。即使是一開始看起來很簡單的一個。確保你完全理解它的作用，因為它可能有問題，或者只是不按照你認為他們總是做的方式做事。

每當嘗試插入時，標識列都會增加，即使它失敗了，因此該部分不一定是異常的。

我會在 arrParams 中查找您的參數值和/或類型的問題。什麼樣的對像是“數據”？（我幾乎不敢問，但我沒有在 msdn 上獲得 ExecuteIntScalar 的任何點擊）

編輯：

我認為 van 在未送出的交易方面走在了正確的軌道上。看起來您正在使用某種自定義幫助程序類來管理對數據庫中儲存過程的呼叫（可能還有其他數據庫訪問），並且可能是該程式碼正在吞噬 SQL 伺服器引發的錯誤。我創建了一個小測試應用程序，並且能夠重現您描述的行為。由於我們無法判斷您的類如何擷取異常等。這可能不是您的實際問題，但它是儲存過程呼叫可能以您描述的方式失敗的一種方式。

// call the proj_ins_all SP every time a button is clicked.
protected void Button1_Click(object sender, EventArgs e)
{
   using (SqlConnection conn = new SqlConnection(myConnectionString))
   using (SqlCommand cmd = new SqlCommand("proj_ins_all", conn))
   {
       try
       {
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@proj_number", SqlDbType.Int));
           cmd.Parameters["@proj_number"].Value = 9001810;
           cmd.Parameters.Add(new SqlParameter("@usr_id", SqlDbType.Int));
           cmd.Parameters["@usr_id"].Value = 2;
           cmd.Parameters.Add(new SqlParameter("@download", SqlDbType.SmallDateTime));
           cmd.Parameters["@download"].Value = "2009-09-03 16:20:11";
           cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.Int));
           cmd.Parameters["@status"].Value = 2;

           conn.Open();
           cmd.Transaction = conn.BeginTransaction();

           object _id = cmd.ExecuteScalar();

           // _id now contains the value of the Identity column for
           // the row just inserted by proj_ins_all

           // Assume (or simulate) an error is raised after the SP is called but
           // before the transaction is committed.
           // (Comment this line out and the DB update succeeds, as expected.)
           throw new Exception();

           // If the transaction is not committed, it'll be rolled back when
           // the connection is closed and the inserted row won't be in the
           // table even though the incremented Identity value was returned above.
           cmd.Transaction.Commit();

       }
       catch (Exception)
       {
           // "swallow" (i.e. just ignore) any errors raised by the DB code.
           //throw;
       }
   }

}

事務不必顯式聲明發生。例如，如果您從上面的程式碼中刪除了對 BeginTransaction() 和 Transaction.Commit() 的呼叫，仍然會有一個隱式事務，理論上可能會被中斷並導致回滾。所以你的問題的根源可能比這個例子更微妙，它需要一個顯式的事務來展示這個概念。

更實際的是，您可以（通過 Profiler）查看應用程序發送到 SQL 伺服器的實際 SQL，並驗證它在從 SSMS 執行時是否有效，因此我認為問題可能出在呼叫儲存過程的應用程式碼上。

引用自：https://stackoverflow.com/questions/1371597