Is there alternative to use dbSchemaName.StoreProcedureName in ADO.NET?

Question

DAL -

SqlConnection con = new SqlConnection(conPath);
SqlCommand cmd = new SqlCommand("School.usp_GetSchoolName", con);

SQL 2005
-StoreProcedure : I have schema named School and usp_GetSchoolName is saved like this.

School.usp_GetSchoolName.

Here is my problem -
I have put about 44 StoreProcedures inside this new container called School Schema. In my ADO.NET ((data access tier) I am passing storeprocedureName and Connection information without SchemaName. When I do this it says storeprocedure is not found. If I go and supply the School.usp_GetSchoolName then it works. I do not feel it is a right practice to supply schemaName in every storeprocedure in ADO.NET. Does anyone have any suggestions? My storeprocedures are divided into many schemas such as School, Student, Administrative, Sports.

Akash · Answer

Hi Daniel Thanks for your reply but it is same. You assigned your SP name to sqlIns variable where as I supplied the SP name as a paramenter. I am very surprised no one has encountered this in ADO.NET where we are forced to supply schema name alone with SP name.

Chongo · Answer

I am using this to execute a stored procedure on a sql2005 server do not know if ado is the same: SqlCommand SqlCom = new SqlCommand(sqlIns SqlCon); SqlCom.CommandType = CommandType.Text; try { /* Execute Insert */ SqlCom.ExecuteNonQuery(); } Where sqlIns = EXEC sp_procedurename and SqlCon is my SQL connection hope this helps.