操作数类型冲突：varchar与试图插入加密数据库的varchar（50）不兼容

问题描述

我收到一个 SqlException ：

我的C＃代码：

using (var connection = new SqlConnection(GetConnectionString()))
{
    using (var cmd = new SqlCommand("Clients_Insert", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = client.Email;
        cmd.Parameters.Add("@ContactPerson", SqlDbType.VarChar, 400).Value = client.ContactPerson;

        connection.Open();
        cmd.ExecuteNonQuery();
    }
}

我的存储过程：

ALTER PROCEDURE [dbo].[Clients_Insert]
    @Email VARCHAR(50),
    @ContactPerson VARCHAR(400)
AS
BEGIN
    INSERT into dbo.Clients(Email, ContactPerson)
    VALUES (@Email, @ContactPerson);

    SELECT SCOPE_IDENTITY();
END;

我没有将数据插入未加密字段的问题。

I have no problems with inserting data into not encrypted fields.

我发现这篇文章

我的问题很类似，但我没有找到解决方案。

My issue is similiar but i haven't found the solution.

推荐答案

有两件事你可以尝试，

确保连接字符串中已启用列加密设置。这可以使用 SqlConnectionStringBuilder 对象并将 SqlConnectionStringBuilder.ColumnEncryptionSetting 设置为启用如下

Ensure that Column encryption setting is enabled in your connection string. This can be done using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled as follows

strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

如果在加密列之前创建了存储过程，则需要刷新存储的元数据程序如下

If your stored procedure was created before you encrypted your column, you will need to refresh metadata for your stored procedure as follows

Use [Database]
GO
--Do this for all stored procedures
EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[Clients_Insert]'

这篇关于操作数类型冲突：varchar与试图插入加密数据库的varchar（50）不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！