SQLParameter名稱為啥不會對應

緣起

承接上篇，在撰寫呼叫 Database Mail 的Stored Procedure(預存程序)時，發現在使用Parameter上有些小地方需要注意；以前使用沒發現，因為呼叫Stored Procedure時使用的參數不多，所以都沒發現，這次記錄一下。

我的環境

$ systeminfo
作業系統名稱:         Microsoft Windows 10 專業版
作業系統版本:         10.0.10240 N/A 組建 10240
作業系統設定:         獨立工作站
作業系統組建類型:     Multiprocessor Free
系統類型:             x64-based PC
處理器:               已安裝 1 處理器。
BIOS 版本:            American Megatrends Inc. 219, 2015/5/4
實體記憶體總計:       16,264 MB

Visual Studio Version

Visual Studio 2015

C# Version

c:\Windows\Microsoft.NET\Framework\v4.0.30319
$ csc
Microsoft (R) Visual C# Compiler version 4.6.0079.0
for C# 5

使用方式

先介紹 SqlParameter 的三種使用方式，這三種方式都大同小異，至於為什麼要使用，可以看一下余小章大大的說明

第一種
指定參數名稱、格式與值

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(str, conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@profile_name", SqlDbType.VarChar).Value = "SendMail";
        cmd.Parameters.Add("@recipients", SqlDbType.VarChar).Value = "test@test.com";
        cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = "ADO method 1 test";
        cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = "Hello World";
        cmd.Parameters.Add("@importance", SqlDbType.VarChar).Value = "NORMAL";
        result = cmd.ExecuteNonQuery();
    }
}

第二種
指定參數名稱與值

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(str, conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@profile_name", "SendMail"));
        cmd.Parameters.Add(new SqlParameter("@recipients", "test@test.com;test@test.com"));
        cmd.Parameters.Add(new SqlParameter("@subject", "ADO method 2 test"));
        cmd.Parameters.Add(new SqlParameter("@body", @"Hello World"));
        cmd.Parameters.Add(new SqlParameter("@importance", "NORMAL"));
        result = cmd.ExecuteNonQuery();
    }
}

第三種
一樣指定參數名稱與值

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(str, conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@profile_name", "SendMail");
        cmd.Parameters.AddWithValue("@recipients", "test@test.com;test@test.com;test@test.com");
        cmd.Parameters.AddWithValue("@subject", "ADO method 3 test");
        cmd.Parameters.AddWithValue("@body", @"Hello World");
        cmd.Parameters.AddWithValue("@importance", "NORMAL");
        result = cmd.ExecuteNonQuery();
    }
}

上述的方法看起來都是正常的，但是如果沒有設定

1	cmd.CommandType = CommandType.StoredProcedure;

就會失去按照參數名稱對應的功能，只能按照順序對應(這順序是 Procedure 的參數宣告順序)；這情形出現在Stored Procedure上，也是因為Database Mail送信的參數很多，才發覺有這情形。

以下列出不使用CommandType的 Sql 字串

static void Main(string[] args)
{
	// 不使用 CommandType 的時候，呼叫 Procedure 必須指定參數名稱
	// 跟直接使用 Sql 字串查詢一樣
    string str = "msdb.dbo.sp_send_dbmail @profile_name,@recipients,@subject,@body,@importance";
	string connStr = ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connStr))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(str, conn))
        {
            cmd.Parameters.Add(new SqlParameter("@profile_name", "SendMail"));
            cmd.Parameters.Add(new SqlParameter("@recipients", "test@test.com;test@test.com"));
            cmd.Parameters.Add(new SqlParameter("@subject", "ADO method 2 test"));
            cmd.Parameters.Add(new SqlParameter("@body", @"Hello World"));
            cmd.Parameters.Add(new SqlParameter("@importance", "NORMAL"));
            cmd.ExecuteNonQuery();
        }
    }
    Console.ReadKey();
}