Net-informations.com
SiteMap  | About    

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 :

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.

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





net-informations.com (C) 2019    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.