c# - 将对象列表传递给 SQL Server 存储过程并检索输出值

问题描述

我正在尝试使用 ado.net 将对象列表以及其他一些数字和字符串参数传递给 SQL Server 存储过程并执行它，并从输出参数中检索一个值。

为了从 SQL Server 存储过程中捕获对象列表，我使用了用户定义的表类型，如下所示：

这是存储过程：

ALTER PROCEDURE [dbo].[UpdateMailjetDetails] 
    @listCode VARCHAR(1000),
    @listName VARCHAR(1000),
    @mailjetListId BIGINT,
    @mailjetListStatus INT,
    @autoAsync BIT,
    @contacts Contact READONLY,
    @companyId INT,
    @tblContactCompanyStatus INT,   
    @espListContactStatus INT,
    @outputMessage VARCHAR(1000) OUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION [Tran1]
    BEGIN TRY
         -- logic
         --
         --

         SET @outputMessage = 'success';    
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION [Tran1];  

        SELECT  
            'Error - Rollbacked -' AS CustomMessage,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;  

        SET @outputMessage = 'error - ' + ERROR_MESSAGE();
    END CATCH   
END

这是调用存储过程的 C# 代码。

public string SaveAndPassToMailjetWindowsService(string listCode, string listName, long mailjetListId, MailjetListStatus mailjetListStatus, bool autoSync, List<Contact> contacts, int companyId, TblContactCompanyStatus tblContactCompanyStatus, EspListContactStatus espListContactStatus)
{
    try
    {
        string result;
        var conString = GetMailCoreConnectionString();

        using (var conn = new SqlConnection(conString))
        {
            var command = new SqlCommand("UpdateMailjetDetails", conn) 
                          { CommandType = CommandType.StoredProcedure };

            command.Parameters.Add(new SqlParameter("@listCode", listCode));
            command.Parameters.Add(new SqlParameter("@listName", listName));
            command.Parameters.Add(new SqlParameter("@mailjetListId", mailjetListId));
            command.Parameters.Add(new SqlParameter("@mailjetListStatus", (int) mailjetListStatus));
            command.Parameters.Add(new SqlParameter("@autoAsync", autoSync));

            var contactsParam =
                        new SqlParameter("@contacts", SqlDbType.Structured)
                        {
                            TypeName = "dbo.Contact",
                            Value = GetSqlDataRecordsContactsList(contacts)
                        };
            command.Parameters.Add(new SqlParameter("@contacts", contactsParam));

            command.Parameters.Add(new SqlParameter("@companyId", companyId));
            command.Parameters.Add(new SqlParameter("@tblContactCompanyStatus", (int) tblContactCompanyStatus));
            command.Parameters.Add(new SqlParameter("@espListContactStatus", (int) espListContactStatus));

            var outputParameter = new SqlParameter
                   {
                        ParameterName = "@outputMessage",
                        SqlDbType = SqlDbType.VarChar,
                        Direction = ParameterDirection.Output
                   };
            command.Parameters.Add(outputParameter);

            conn.Open();
            command.ExecuteNonQuery();      // throws exception
            result = outPutParameter.Value.ToString();
            conn.Close();
        }

        return result;
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        throw;
    }
}

下面是将联系人列表转换为数据表的 C# 方法。

private List<SqlDataRecord> GetSqlDataRecordsContactsList(List<Contact> contacts)
{
    try
    {
        List<SqlDataRecord> datatable = new List<SqlDataRecord>();
        SqlMetaData[] sqlMetaData = new SqlMetaData[5];
        sqlMetaData[0] = new SqlMetaData("Email", SqlDbType.NVarChar, 512);
        sqlMetaData[1] = new SqlMetaData("Name", SqlDbType.NVarChar, 512);
        sqlMetaData[2] = new SqlMetaData("TblContactId", SqlDbType.BigInt);
        sqlMetaData[3] = new SqlMetaData("CompanyId", SqlDbType.Int);
        sqlMetaData[4] = new SqlMetaData("TblContactCompanyId", SqlDbType.BigInt);

        foreach (var contact in contacts)
        {
            SqlDataRecord row = new SqlDataRecord(sqlMetaData);
            row.SetValues(contact.Email, contact.Name, contact.TblContactId ?? (object) DBNull.Value,
                        contact.CompanyId ?? (object) DBNull.Value,
                        contact.TblContactCompanyId ?? (object) DBNull.Value);
            datatable.Add(row);
        }

        return datatable;
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        throw;
    }
}

现在，当我运行代码时，它会正确执行存储过程中的所有逻辑并完成我想要做的所有更改。我可以看到已经通过存储过程正确添加了数据，但是SaveAndPassToMailjetWindowsService方法

command.ExecuteNonQuery();

在 C# 端引发异常（即使存储过程正确执行其逻辑）。

不存在从对象类型 System.Data.SqlClient.SqlParameter 到已知托管提供程序本机类型的映射。

非常感谢任何解决此问题的指导。谢谢。

标签： c#stored-proceduresado.net

c# - 将对象列表传递给 SQL Server 存储过程并检索输出值

问题描述

解决方案

推荐阅读