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

Akash

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akash19y ago
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.
Chongo19y ago
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.