Click here to Skip to main content
15,901,035 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
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?

EDIT:

This is an example for a procedure:

C#
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);
    }
}


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...)

C#
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;
        }


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;
    }



The actual procedure that is being executed here is: (No reason that it wouldn't work from C#, don't you agree?)
SQL
ALTER PROCEDURE EventToClient_RemoveMatch
 (@EventID bigint,@ClientID int) AS
 BEGIN
 DELETE FROM _EventToClient WHERE EventID = @EventID AND ClientID = @ClientID
 END


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.
Posted
Updated 29-Oct-11 23:34pm
v6
Comments
Sander Rossel 29-Oct-11 20:35pm    
It would help if you posted some code...
ShacharK 29-Oct-11 20:36pm    
Well, my code works when I'm executing it directly using the server, but I just don't get why it wouldn't commit.
Amir Mahfoozi 30-Oct-11 3:03am    
The best way is observing the situation with Query Profiler, run it and watch for the procedure to be called and see if it has the proper parameters with suitable values...
OriginalGriff 30-Oct-11 4:09am    
Without seeing the GetProcedure method and what it produces, it is not really possible to answer accurately.
ShacharK 30-Oct-11 5:17am    
I added my GetProcedure method. Does it help you to help me?

1 solution

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[^]
 
Share this answer
 
v2
Comments
ShacharK 30-Oct-11 4:56am    
Could you please be more specific? Maybe me send me to a relevant reference? This problem is very crucial... It happens quite a lot (unfortunately...)
Mehdi Gholam 30-Oct-11 5:06am    
I have updated the solution.
Amir Mahfoozi 31-Oct-11 6:45am    
+5
Mehdi Gholam 31-Oct-11 6:50am    
Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900