[SQL，C#]为什么我的过程无法通过代码正常运行? [英] [SQL,C#] Why doesn't my procedure work properly from code?

问题描述

我正在用我的C#代码执行过程，由于某种原因，我看不到它们对服务器表的影响.
除了执行它们外，我还有什么需要做的吗?造成这个烦人问题的原因可能是什么?



这是过程的示例:

I''m executing my procedures from my C# code, and for some reason I can''t see their effects on the server''s tables.
Is there anything else I need to do except for executing them? What could be the cause for this annoying problem?



This is an example for a procedure:

public void DetachEventFromClient(long eventId, int clientId)
{
    ProcedureEnum procedure = ProcedureEnum.EventToClient_RemoveMatch;
    object[] input = new object[] { eventId, clientId };
    
// The get procedure method creates a command, and adds the input parameters.
    using (SqlCommand command = GetProcedure(procedure, input))
    {
        connection.ExecuteNonQuery(command);
    }
}

我的Get Procedure函数(我有一个存储所有过程的集合以及它们的参数.在这个执行"中，我只是创建过程的SqlCommand，然后添加值.字典是参数名)参数定义(必要时还有其类型和大小...)

My Get Procedure function (I have a collection storing all my procedures... along with their parameters. And in this "execution" I''m just creating the procedure''s SqlCommand, and adding the values. The dictionary is parameter name to parameter definition (along with its type and size if necessary...)

public SqlCommand GetProcedure(object[] input)
        {
            if (input.Length != Input.Count)
                throw new ArgumentException("Number of parameters is incorrect.");

            // Create STORED PROCEDURE Sql command of this type.
            SqlCommand c = new SqlCommand(Name) { CommandType = CommandType.StoredProcedure };
            // Add Input Values
            foreach (KeyValuePair<string, SQLParameter> kvp in Input)
                c.Parameters.AddWithValue(kvp.Key, input[0]); // DAMN IT, I'M AN IDIOT!!! :S 10 HOURS!!!

            // Add the output parameters.
            foreach (KeyValuePair<string, SQLParameter> kvp in Output)
            {
                SqlParameter p;
                if (kvp.Value.HasSize)
                    p = new SqlParameter(kvp.Value.Name, kvp.Value.Type, kvp.Value.Size) { Direction = ParameterDirection.Output };
                else
                    p = new SqlParameter(kvp.Value.Name, kvp.Value.Type) { Direction = ParameterDirection.Output };
                c.Parameters.Add(p);
            }

            return c;
        }

public int ExecuteNonQuery(SqlCommand command, bool showException = true)
{
    try
    {
        // Open connection
        if(connection.State == ConnectionState.Closed)
            connection.Open();

        // Set command's connection.
        command.Connection = connection;

        // Execute the query
        int affectedRows = command.ExecuteNonQuery();

        // Close connection
        connection.Close();

        // Return
        return affectedRows;
    }
    catch (SqlException e)
    {
        if(showException)
            printException(e);
        return 0;
    }

在这里执行的实际过程是:(没有理由不能在C#中运行，您不同意吗?)

The actual procedure that is being executed here is: (No reason that it wouldn''t work from C#, don''t you agree?)

ALTER PROCEDURE EventToClient_RemoveMatch
 (@EventID bigint,@ClientID int) AS
 BEGIN
 DELETE FROM _EventToClient WHERE EventID = @EventID AND ClientID = @ClientID
 END

为什么它不能在C#中工作?与交易有关系吗?我什么也没用，我从没想过有必要.他们有必要吗?有时，仅一行会受到过程运行的影响(除去)，其余的行将被忽略.

Why wouldn''t it work from C#? does it have anything to do with transactions? I''m not using any, I never thought it was necessary. Are they necessary? Sometimes only a single row is affected (removed) by the procedure run, and the rest are ignored.

推荐答案

使用SQL tracer运行程序，并查看哪些参数正在传递给您的存储过程以及服务器上发生的情况.



阅读此内容:如何:使用SQL事件探查器 [

Run your program using the SQL tracer and see what parameters are being passed to your stored procedure and what is happening on the server.

EDIT :

Read this : How To: Use SQL Profiler[^]

这篇关于[SQL，C#]为什么我的过程无法通过代码正常运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！