Peter's Gekko

public Blog MyNotepad : Imho { }

How to execute a SQL storedproc from an SqlCommand

A database has a storedproc with some parameters. I don't know exactly what it does, I don't want to know, I don't have to know, it's not under my management. But my app does have a connection to it and has to call to stored proc. This is how it does that :

SqlCommand berekenUrenverdeling = new SqlCommand("EXEC dbo.SP_STUDOND_URVRD_BEREKEN @AIDGEBRUIKER, @AIDURENVRD", sqlConnection1);

berekenUrenverdeling.Parameters.Add("@AIDGEBRUIKER", idGebruiker);
berekenUrenverdeling.Parameters.Add("@AIDURENVRD", idUrenvrd);

berekenUrenverdeling.ExecuteNonQuery();

I create a new sql command. The command text contains the name of the stored proc, dbo.SP_STUDOND_URVRD_BEREKEN, and the name of the two parameters, @AIDGEBRUIKER and @AIDURENVRD. Both parameters are added to the SqlCommands parameter collection as name value pairs. (In ADO.NET 2.0 the Add method has 3 parameters). ExecuteNonQuery fires off the proc. In case I'm interested it will return the number of rows affected.

Nothing special at all, works like a charm and thanks to the parameters it's pretty safe against a sqlinjection attack.

Published Apr 15 2005, 08:19 AM by pvanooijen

Filed under: Data

Comments

Frans Bouma said:

Use EXEC sp_executesql because otherwise the execution plan isn't cached.

# April 15, 2005 10:29 AM

pvanooijen said:

thanks Frans !

# April 15, 2005 11:52 AM

Josh said:

I don't get it. Why not just pass the name of the stored procedure to the SqlCommand constructor, and set the CommandType to StoredProcedure?

# April 15, 2005 1:45 PM

pvanooijen said:

Josh, thanks for pointing that out.
You can do that as well. This will change the code to

SqlCommand berekenUrenverdeling = new SqlCommand("dbo.SP_STUDOND_URVRD_BEREKEN", sqlConnection1);
berekenUrenverdeling.CommandType = System.Data.CommandType.StoredProcedure;

berekenUrenverdeling.Parameters.Add("@AIDGEBRUIKER", idGebruiker);
berekenUrenverdeling.Parameters.Add("@AIDURENVRD", idUrenvrd);

berekenUrenverdeling.ExecuteNonQuery();

The commandtext for the sqlcommand is simpler, but you have to include an extra line of code to set the commandtype.

Imho the first version demonstrates more of the possibilities of a sqlCommand. No more, no less.

# April 16, 2005 2:27 AM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?