Stored Procedure with Parameter

The ADO.NET classes are divided into two components, Data Providers and DataSet. A .NET data provider is 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.

A Stored Procedure contain programming statements that perform operations in the database, including calling other procedures. 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 SPCOUNTRY
@COUNTRY VARCHAR(20)
AS
SELECT PUB_NAME FROM publishers WHERE COUNTRY = @COUNTRY

GO

The above stored procedure is accepting a country name (@COUNTRY VARCHAR(20)) as parameter and return all the publishers from the input country. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.

command.CommandType = CommandType.StoredProcedure
param = New SqlParameter("@COUNTRY", "Germany")
param.Direction = ParameterDirection.Input
param.DbType = DbType.String

command.Parameters.Add(param)

The above code passing country parameter to the stored procedure from vb.net.

This is code:
////////
Imports System.Data.SqlClient
Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim command As New SqlCommand
Dim param As SqlParameter
Dim ds As New DataSet

Dim i As Integer

connetionString = "Data Source=servername;_
Initial Catalog=PUBS;User ID=sa;Password=yourpassword"
connection = New SqlConnection(connetionString)

connection.Open()
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.CommandText = "SPCOUNTRY"

param = New SqlParameter("@COUNTRY", "Germany")
param.Direction = ParameterDirection.Input
param.DbType = DbType.String
command.Parameters.Add(param)

adapter = New SqlDataAdapter(command)
adapter.Fill(ds)

For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(0))
Next
connection.Close()

End Sub

End Class
///////