C＃SQL存储过程未提交 [英] c# sql stored procedure isn't committing

问题描述

每当我运行以下代码时，我都会得到预期的输出

Whenever I run the following code I get the expected output

private int NewBorrower(string givenName, string surname)
{
    int returnValue = 0;
    using (conn)
    {
        conn.Open();
        string sql = "AddBorrower";

        cmd = new SqlCommand(sql, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@givenName", SqlDbType.NVarChar).Value = givenName;
        cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = surname;
        SqlParameter id = cmd.Parameters.Add("@id", SqlDbType.Int);
        id.Direction = ParameterDirection.ReturnValue;

        try
        {
            cmd.ExecuteNonQuery();
            returnValue = (int)cmd.Parameters["@id"].Value;
        }
        catch (Exception e)
        {
            Console.WriteLine("Commit Exception Type: {0}", e.GetType());
            Console.WriteLine("  Message: {0}", e.Message);
        }
    }

    return returnValue;
}

从前端运行时，我得到了想要的结果，但是当我运行检查数据库未显示在表中。

When run from the front end I get the results I want, but when I check the database it doesn't show up in the table.

出于良好的考虑，这也是正在使用的存储过程

For good measure this is also the stored procedure being used

CREATE PROCEDURE [dbo].[AddBorrower]
    @givenName nvarchar(50),
    @surname nvarchar(50),
    @id int = NULL OUTPUT
AS
    INSERT INTO llBorrowers (givenName, surname)
    VALUES (@givenName, @surname);
    SET @id = SCOPE_IDENTITY();
RETURN @id

我已经尝试在c＃和sql端都使用事务，那根本没有用。
我还应该提到这是一个本地数据库，但是我不确定这是否会影响它。

I've tried using transactions on both the c# and sql sides, and that didn't work at all. I should also mention that it is a local database, but I'm not sure that should affect it.

推荐答案

在WinForms应用程序中使用DataDirectory替换字符串时，其实际值会根据调试或发布配置而变化。

When you use the DataDirectory substitution string in a WinForms application, its real value changes depending on your debug or release configuration.

在调试中，DataDirectory指向PROJECTFOLDER\BIN BDEBUG（如果是这种情况，则为x86变体）。

因此，这很容易被愚弄。您可以在服务器资源管理器中创建一个连接，但是在其他数据库上运行的代码会忽略该连接。

In DEBUG your DataDirectory points to PROJECTFOLDER\BIN\DEBUG (or x86 variation if it is the case).
So it is extremely easy to get fooled by this. You create a connection in server explorer but this connection is ignored by your code that works on a different database.

您可以在服务器资源管理器中创建另一个连接，并将其命名为DebugConnection，在使用DebugConnection来检查代码是否按预期执行时，仍保留原始的架构更改

You could create another connection in Server Explorer and name it DebugConnection, still keeping the original one for schema changes while you use the DebugConnection to check if your code executes as expected

作为一个说明，请特别注意属性<$ c如果在项目之间列出了MDF文件的$ c>复制到输出目录属性。如果将其设置为始终复制，则每次启动调试会话时，db的新副本将从项目目录复制到输出目录，从而有效销毁由以下人员执行的更新：您的代码。我建议将其设置为从不复制并手动处理数据库架构更改

As a side note, keep particular attention to the property Copy To the Output Directory property on the MDF file if it is listed between the project items. If you set it to Copy Always every time you start a debug session a fresh copy of your db will be copied from the project directory to the output directory effectively destroying the updates executed by your code. I recommend to set it to Copy Never and handle manually the database schema changes

参考：在哪里数据目录

这篇关于C＃SQL存储过程未提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！