0

I have a method that executes a SqlCommand and returns the result to a winforms application. My method is this:

public bool ApplyRoles(string roleApp, string roleAppPassword)
{
     Command = new SqlCommand("EXEC sp_setapprole @roleApp, @rolePassword", Connection);
     AssignParam("roleApp", roleApp, SqlDbType.VarChar);
     AssignParam("rolePassword", roleAppPassword, SqlDbType.VarChar);
     bool ret = Command.ExecuteNonQuery() == -1;
     return ret;
}

and the AssignParam method is this:

public void AssignParam(string name, object value, SqlDbType type)
{
    var parameter = new SqlParameter(name, type)
    {
         Value = value ?? DBNull.Value
    };
    Command.Parameters.Add(parameter);
}

Now, this ApplyRoles method throws the exception: Application roles can only be activated at the ad hoc level. but if i change the ApplyRoles to this:

public bool ApplyRoles(string roleApp, string roleAppPassword)
{
   Command = new SqlCommand(string.Format("EXEC sp_setapprole '{0}', '{1}'", roleApp, roleAppPassword), Connection);
   bool ret = Command.ExecuteNonQuery() == -1;
   return ret;
}

The method works fine.. so i'm guessing that the problem is in the AssignParam method. What is the problem? I don't want to use the "working" method because i could have sql injection on it.

Phoenix_uy
  • 3,173
  • 9
  • 53
  • 100
  • I don't see the error but why aren't you using the normal way of invoking a proc? Leave out the `EXEC` stuff. – usr May 20 '14 at 18:23
  • You mean `new SqlCommand("sp_setapprole", Connection)`? but how .NET identify the `@roleApp` and `@rolePassword` when i set the `AssignParam` method? – Phoenix_uy May 20 '14 at 18:30
  • you have to set the `CommandType.StoredProcedure`. See my answer below to what your root problem was. – Cam Bruce May 20 '14 at 18:35

1 Answers1

0

sp_setapprole uses the parameter names @rolename and @password. You have to use those parameter names if you are passing the parameter names into the a SqlCommand` instance. It was working for you in your second example because you were passing only the parameter values in the correct order that the stored proc was expecting them.

Since you're executing a stored procedure, use CommandType.StoredProcedure

Also put the '@' character in front of your parameter names. 

Command = new SqlCommand("sp_setapprole", Connection);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddWithValue( @rolename, roleApp);
Command.Parameters.AddWithValue( @password, rolePassword);
bool ret = Command.ExecuteNonQuery() == -1;
     return ret;
Cam Bruce
  • 5,632
  • 19
  • 34