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:
public void DetachEventFromClient(long eventId, int clientId)
{
ProcedureEnum procedure = ProcedureEnum.EventToClient_RemoveMatch;
object[] input = new object[] { eventId, clientId };
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...)
public SqlCommand GetProcedure(object[] input)
{
if (input.Length != Input.Count)
throw new ArgumentException("Number of parameters is incorrect.");
SqlCommand c = new SqlCommand(Name) { CommandType = CommandType.StoredProcedure };
foreach (KeyValuePair<string, SQLParameter> kvp in Input)
c.Parameters.AddWithValue(kvp.Key, input[0]);
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
{
if(connection.State == ConnectionState.Closed)
connection.Open();
command.Connection = connection;
int affectedRows = command.ExecuteNonQuery();
connection.Close();
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?)
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.