Underground And Other Stuff

SQL stored procedures are designed to be more efficient than outside queries. However, any performance boost you gain from using a stored procedure may be lost when you load the whole stored procedure result set into a DataSet. Instead, you should use the SQLDataReader to retrieve the data one row at a time. Luckily, pairing the two together is really quite simple. Consider the following code:

Dim cn As SqlConnection = New SqlConnection( “server=svr1;uid=uid1;pwd=pw1;database=yoursqldb")
Dim myCMD As SqlCommand = New SqlCommand( storedprocedurename, cn)

myCMD.CommandType = CommandType.StoredProcedure
cn.Open
Dim myReader As SqlDataReader = myCMD.ExecuteReader(CommandBehavior.CloseConnection)

If the stored procedure has parameters, you'll need to create a parameter object, supplying the name of the parameter and its SQL data type. For example, you'd use code similar to the following to create an object that accepts an Integer value for input:

Dim param As SqlParameter = myCMD.Parameters.Add( _
parametername, sqlDbType.Int)
param.Direction = ParameterDirection.Input

If your stored procedure returns more than one result set, the DataReader allows you to retrieve the result sets one at a time using the NextResult() method, like so:

myReader.NextResult()