c# - 发生类型为 'MySql.Data.MySqlClient.MySqlException' 的异常

Question

我正在尝试创建一个跨越两个数据库表的新 UserFingerprintModel。一个来自 PaymentFingerprint，一个来自 PaymentFingerprintGrant。对于我的服务测试，我创建了这两个 sql 语句:

private const string _ADD_FINGERPRINT_QUERY = @"
            INSERT INTO FinOps.PaymentFingerprint(
              CreateDate,
              Fingerprint,
              PaymentTypeId,
              FingerprintTypeId
            )
            VALUES (
              NOW(),
              @pFingerprint,
              @pPaymentTypeId,
              @pFingerprintTypeId
            ); SELECT LAST_INSERT_ID();";

然后为了创建 FingerprintGrant，我需要 fingerprintId，这解释了上面的“SELECT LAST_INSERT_ID();”。

这是我的 FingerprintGrant sql 字符串:

private const string _ADD_FINGERPRINT_GRANT_QUERY = @"

            INSERT INTO FinOps.PaymentFingerprintGrant(
              PaymentFingerprintId,
              CreateDate,
              DepositLimit,
              DepositLimitIntervalDays,
              IsDeleted,
              PaymentFingerprintStatusId,
              UserId
            )
            VALUES (
              @pPaymentFingerprintId,
              NOW(),
              @pDepositLimit,
              @pDepositLimitIntervalDays,
              @pIsDeleted,
              @pPaymentFingerprintStatusId,
              @pUserId
        );";

然后我尝试使用 MySqlConnection。下面是我的代码:

// Returns the last insert id
        public int AddUserFingerprint (string fingerprint, int paymentTypeId, int fingerprintTypeId)
        {
            using (var conn = new MySqlConnection(_finopsConnection))
                return conn.Execute(_ADD_FINGERPRINT_QUERY,
                    new
                    {
                        pFingerprint = fingerprint,
                        pPaymentTypeId = paymentTypeId,
                        pFingerprintTypeId = fingerprintTypeId   
                    }, commandType: CommandType.Text);
        }

public void AddUserFingerprintGrant(int paymentFingerprintId, DateTime createdDate, int depositLimit, int depositLimitInteveralDays, int isDeleted, int userId, int paymentFingerprintStatusId, string fingerprint, int paymentTypeId, int fingerprintTypeId)
        {
            using (var conn = new MySqlConnection(_finopsConnection))
                conn.ExecuteScalar(_ADD_FINGERPRINT_GRANT_QUERY,
                    new
                    {
                        pPaymentFingerprintId = AddUserFingerprint(fingerprint, paymentTypeId, fingerprintTypeId),
                        pDepositLimit = depositLimit,
                        pDepositLimitIntervalDays = depositLimitInteveralDays,
                        pIsDeleted = isDeleted,
                        pPaymentFingerprintStatusId = paymentFingerprintStatusId,
                        pUserId = userId
                    }, commandType: CommandType.Text);
        }

这就是我在 Service Test

中调用这两个方法的地方

[测试]

public void TestGetFingerprintById()
    {
        HttpStatusCode status;

        var userFingerprint = CreateRandomFingerprintGrant();
        var paymentFingerprintId = AddUserFingerprint(userFingerprint.Fingerprint, userFingerprint.PaymentTypeId, userFingerprint.FingerprintTypeId);
        AddUserFingerprintGrant(paymentFingerprintId, userFingerprint.CreateDate, userFingerprint.DepositLimit,
            userFingerprint.DepositLimitIntervalDays, userFingerprint.IsDeleted, userFingerprint.UserId,
            userFingerprint.PaymentFingerprintStatusId, userFingerprint.Fingerprint, userFingerprint.PaymentTypeId,
            userFingerprint.FingerprintTypeId);


        var query = new GetFingerprintByIdQuery()
        {
            UserId = userFingerprint.UserId,
            FingerprintId = paymentFingerprintId
        };
        var resp = _hermesDriver.GetFingerprintInfoById(query, out status);

    }

但是，当我运行服务测试时，出现此错误:TestGetFingerprintById [0:01.608] Failed: MySql.Data.MySqlClient.MySqlException : Duplicate entry '1-3b590375-06d2-4923-a266-d98d44ab2b0f ' 对于键 'idx_payment_type_fingerprint_uniq'

指纹是在我的辅助方法中生成的:

public AddUserFingerprintModel CreateRandomFingerprintGrant()
        {
            var fingerprint = Guid.NewGuid().ToString();
            var userFingerprintDto = new AddUserFingerprintModel()
            {
                Fingerprint = fingerprint,
                UserId = 100001,
                IsDeleted = 0,
                UpdatedDate = DateTime.Today,
                PaymentTypeId = 1,
                DepositLimit = 10000,
                PaymentFingerprintGrantId = 100010,
                Version = 1,
                PaymentFingerprintStatusId = 1,
                CreateDate = DateTime.Today,
                FingerprintTypeId = 1,
                DepositLimitIntervalDays = 5
            };
            return userFingerprintDto;   
        }

我真的不明白为什么我会收到这个异常。即使我将 fingerprint 字符串硬编码为随机乱码，我仍然会遇到此异常/错误。

如有任何帮助，我们将不胜感激!

Answer 1

确保您拥有每个列的所有变量。