The ADO.NET data providers used to connect to a database, execute commands, and retrieve results. The Command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.
The main advantage of Stored Procedure is that the Sql Server compiles each stored procedure once and then we can reuse the execution plan again and again. In many cases stored procedures accept input parameters and return multiple values . Parameter values can be supplied if a stored procedure is written to accept them. A sample stored procedure with accepting input parameter is given below :
CREATE PROCEDURE SPCITY @CITY VARCHAR(20) AS SELECT AU_LNAME FROM AUTHORS WHERE CITY = @CITY GO
The above stored procedure is accepting a city name (@CITY VARCHAR(20)) as parameter and return all the authors from the input city. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.vb.net
command.CommandType = CommandType.StoredProcedure param = New SqlParameter("@CITY", "Berkeley") param.Direction = ParameterDirection.Input param.DbType = DbType.String
command.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@CITY", "Berkeley"); param.Direction = ParameterDirection.Input; param.DbType = DbType.String;
The following ASP.NET program call a procedure and display all authors from Berkeley city.
Click the following links to see full source code