调用带有表参数的存储过程

本文关键字：存储过程 参数 调用 | 更新日期: 2023-09-27 18:15:18

我试图调用下面的存储过程，但我不确定通过其中一个参数(@UnsubscribeTypes)传递什么，我已经尝试传递列表，但得到了编译错误。我正在使用c#， Visual Studio 2010, web表单。在c#代码(ado.net)中调用存储过程时，我应该传递什么?

这是我的存储过程:

ALTER PROCEDURE [czone].[SetAccountEmailPreference] 
(
    @EmailAddress VARCHAR(255),
    @UnsubscribeTypes dbo.ListOfIDs READONLY,
    @SentEmailID INT = NULL
)
AS
   SET NOCOUNT ON;
   EXEC dbo.LogObjectExecution @@PROCID;
   DECLARE @UnsubscribeID INT = (SELECT TOP 1 UnsubscribeID 
                                 FROM Email.dbo.Unsubscribe 
                                 WHERE EmailAddress = @EmailAddress 
                                 ORDER BY UnsubscribeID DESC);
   -- Unsubscribe
   IF ((SELECT COUNT(*) FROM @UnsubscribeTypes) > 0)
   BEGIN
       IF(@UnsubscribeID IS NULL)
       BEGIN
          -- ADD UNSUBSCRIBE
          INSERT INTO Email.dbo.Unsubscribe (EmailAddress, CreatedDate) 
          VALUES (@EmailAddress, CURRENT_TIMESTAMP)     
          SET @UnsubscribeID = @@IDENTITY;
       END
       -- Remove current mappings
       DELETE FROM Email.dbo.UnsubscribeTypeMapping 
       WHERE UnsubscribeFK = @UnsubscribeID;
       -- Add new mappings
       INSERT INTO Email.dbo.UnsubscribeTypeMapping (UnsubscribeFK, UnsubscribeTypeFK, SentEmailFK)
           SELECT   
              @UnsubscribeID, ID, @SentEmailID
           FROM 
              @UnsubscribeTypes;
    END
    -- Subscribe
    ELSE IF (@UnsubscribeID IS NOT NULL)
    BEGIN
       DELETE FROM Email.dbo.Unsubscribe 
       WHERE UnsubscribeID = @UnsubscribeID;
    END

调用带有表参数的存储过程

ListOfIDs是一个表类型。首先，找出数据库中的类型，然后检查列。生成一个包含UnsubscribeTypeFK id的数据表。

ADO.net代码(未编译)

创建表

DataTable dt = new DataTable("Items");
dt.Columns.Add("ID", typeof(int));
dt.Rows.Add(4);
调用proc 

    con = new SqlConnection(conStr);    
    con.Open();    
    using (con)    {                
       // Configure the SqlCommand and SqlParameter.
       SqlCommand sqlCmd = new SqlCommand("czone.SetAccountEmailPreference", con);
       sqlCmd.CommandType = CommandType.StoredProcedure;
       SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@UnsubscribeTypes", _dt); // TVP
       tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
       //pass other parameters
       sqlCmd.ExecuteNonQuery();
    }    
    con.Close();

您可以在这里找到更多关于表值参数的信息

相关文章：