Cannot find stored procedure (using schemas)

Question

In my database I have three schemas:

1. Schema1
2. Schema2
3. Schema3

Objects are separated out into each of these schemas depending on that objects function, which I thought was a good way of doing things. This in turn leaves no objects in dbo except the default objects from the model database which I have not modified.

When I try to reference a stored procedure from code, whilst specifying its a stored procedure, I receive a Stored Procedure Not Found error. When I change the CommandType back to text, the code then works.

I don't have the code to had, but it looks something like this (written off the top of my head, so dont expect it to compile):

using(DbConnection conn as new DbConnection("Conn String")
{
     using(DbCommand cmd = conn.CreateCommand())
     {
         cmd.CommandText = "Schema1.usp_Category_MySproc @param1,@param2";
         cmd.CommandType = CommandType.StoredProcedure;
         conn.Open();
         DbDataReader rdr = cmd.ExecuteQuery();
         while(rdr.Read())
         {
              // DO STUFF
         }
     }
}

I also noticed that the parameters have to passed in order else they are all swapped around (since that I have only been able to use CommandType.Normal I have not tested the params with the other command type). I am guessing it shouldn't matter what order the parameters are passed in as, as long as every parameter is passed in?

Any ideas?

As always, answers in either c# or vb are acceptable.

Notes:

1. .Net 3.5 on Windows 7 Enterprise
2. Yes, I am using DbCommand not SqlCommand
3. Targetting Sql-Server 2005->2012

Answer 1

According to the documentation, it is looking for a stored procedure named "Schema1.usp_Category_MySproc @param1,@param2", which of course, doesn't exist.

When you set the CommandType to StoredProcedure, you should set the CommandText property to the name of the stored procedure.

I was able to use the following code to add parameters to the command:

cmd.CommandText = "Schema1.usp_Test";
cmd.CommandType = CommandType.StoredProcedure;

var parameter = cmd.CreateParameter();

parameter.ParameterName = "@value";
parameter.DbType = DbType.Int32;
parameter.Value = 100;

cmd.Parameters.Add(parameter);

Answer 2

I think you are trying to execute a command that already exists in your database? If so your better off getting the StoredProc back and using it to add the parameters in using the following format...

string sqlCommand = "schema1.StoredProcName";    
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "@Param1", DbType.Int32, 1);
db.AddInParameter(dbCommand, "@Param2", DbType.Int32, 2);
db.ExecuteNonQuery(dbCommand); 

Check out Handling Parameters: http://msdn.microsoft.com/en-us/library/ff649451.aspx