使用預存程序修改資料

  • 發行項

預存程序 (Stored Procedure) 可以接受資料做為輸入參數，也可以將資料以輸出參數、結果集 (Result Set) 或傳回值的形式傳回。 下列範例說明 ADO.NET 如何傳送及接收輸入參數、輸出參數和傳回值。 此範例會將新記錄插入資料表 (該資料表的主索引鍵資料行是 SQL Server 資料庫中的識別欄位)。

注意

如果您要使用 SQL Server 預存程序搭配 SqlDataAdapter 來編輯或刪除資料，請務必不要在預存程序定義中使用 SET NOCOUNT ON。 因為這樣會讓傳回的受影響資料列計數成為零，而 DataAdapter 會將它解譯為並行衝突。 在此事件中，系統會擲回 DBConcurrencyException。

範例

此範例使用下列預存程序來將新類別插入 Northwind 的 Categories 資料表。 此預存程序使用 CategoryName 資料行中的值做為輸入參數，然後使用 SCOPE_IDENTITY() 函式來擷取識別欄位 CategoryID 的新值，並以輸出參數加以傳回。 RETURN 陳述式使用 @@ROWCOUNT 函式來傳回插入的資料列數。

CREATE PROCEDURE dbo.InsertCategory  
  @CategoryName nvarchar(15),  
  @Identity int OUT  
AS  
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)  
SET @Identity = SCOPE_IDENTITY()  
RETURN @@ROWCOUNT

下列程式碼範例使用以上所示的 InsertCategory 預存程序做為 InsertCommand 的 SqlDataAdapter 的來源。 在呼叫 @Identity 的 DataSet 方法而將記錄插入至資料庫之後，Update 輸出參數將反映在 SqlDataAdapter 中。 程式碼也會擷取傳回值。

注意

使用 OleDbDataAdapter 時，您必須在其他參數前面指定 ParameterDirection 為 ReturnValue 的參數。

using System;
using System.Data;
using System.Data.SqlClient;

static class Program
{
    static void Main()
    {
        var connectionString = GetConnectionString();
        ReturnIdentity(connectionString);
        // Console.ReadLine();
    }

    static void ReturnIdentity(string connectionString)
    {
        using (SqlConnection connection = new(connectionString))
        {
            // Create a SqlDataAdapter based on a SELECT query.
            SqlDataAdapter adapter = new("SELECT CategoryID, CategoryName FROM dbo.Categories", connection)
            {
                // Create a SqlCommand to execute the stored procedure.
                InsertCommand = new SqlCommand("InsertCategory", connection)
                {
                    CommandType = CommandType.StoredProcedure
                }
            };

            // Create a parameter for the ReturnValue.
            SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
            parameter.Direction = ParameterDirection.ReturnValue;

            // Create an input parameter for the CategoryName.
            // You do not need to specify direction for input parameters.
            adapter.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");

            // Create an output parameter for the new identity value.
            parameter = adapter.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
            parameter.Direction = ParameterDirection.Output;

            // Create a DataTable and fill it.
            DataTable categories = new();
            adapter.Fill(categories);

            // Add a new row.
            DataRow categoryRow = categories.NewRow();
            categoryRow["CategoryName"] = "New Beverages";
            categories.Rows.Add(categoryRow);

            // Update the database.
            adapter.Update(categories);

            // Retrieve the ReturnValue.
            var rowCount = (int)adapter.InsertCommand.Parameters["@RowCount"].Value;

            Console.WriteLine("ReturnValue: {0}", rowCount.ToString());
            Console.WriteLine("All Rows:");
            foreach (DataRow row in categories.Rows)
            {
                Console.WriteLine("  {0}: {1}", row[0], row[1]);
            }
        }
    }

    static string GetConnectionString() =>
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";
}

Option Explicit On
Option Strict On

Imports System.Data
Imports System.Data.SqlClient

Module Class1

    Sub Main()
        Dim connectionString As String = _
            GetConnectionString()
        ReturnIdentity(connectionString)
        ' Console.ReadLine()
    End Sub


    Private Sub ReturnIdentity(ByVal connectionString As String)
        Using connection As SqlConnection = New SqlConnection( _
           connectionString)

            ' Create a SqlDataAdapter based on a SELECT query.
            Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
               "SELECT CategoryID, CategoryName FROM dbo.Categories", _
               connection)

            ' Create a SqlCommand to execute the stored procedure. 
            adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
               connection)
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure

            ' Create a parameter for the ReturnValue.
            Dim parameter As SqlParameter = _
               adapter.InsertCommand.Parameters.Add( _
              "@RowCount", SqlDbType.Int)
            parameter.Direction = ParameterDirection.ReturnValue

            ' Create an input parameter for the CategoryName.
            ' You do not need to specify direction for input parameters.
            adapter.InsertCommand.Parameters.Add( _
              "@CategoryName", SqlDbType.NChar, 15, "CategoryName")

            ' Create an output parameter for the new identity value.
            parameter = adapter.InsertCommand.Parameters.Add( _
              "@Identity", SqlDbType.Int, 0, "CategoryID")
            parameter.Direction = ParameterDirection.Output

            ' Create a DataTable and fill it.
            Dim categories As DataTable = New DataTable
            adapter.Fill(categories)

            ' Add a new row.
            Dim newRow As DataRow = categories.NewRow()
            newRow("CategoryName") = "New Category"
            categories.Rows.Add(newRow)

            ' Update the database.
            adapter.Update(categories)

            ' Retrieve the ReturnValue.
            Dim rowCount As Int32 = _
               CInt(adapter.InsertCommand.Parameters("@RowCount").Value)

            Console.WriteLine("ReturnValue: {0}", rowCount.ToString())
            Console.WriteLine("All Rows:")
            Dim row As DataRow
            For Each row In categories.Rows
                Console.WriteLine("  {0}: {1}", row(0), row(1))
            Next
        End Using
    End Sub

    Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        Return "Data Source=(local);Initial Catalog=Northwind;" _
           & "Integrated Security=true;"
    End Function

End Module

另請參閱