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.




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

 VB.NET Data Providers - Related Contents


 VB.NET Data Providers - Related Programs

.