Stored Procedures in VB.NET

The data provider is a set of components that include the Connection, Command, DataReader, and DataAdapter Objects. The command Object provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.

A stored procedure is a precompiled executable object that contains one or more SQL statements. A sample Stored Procedure is given below :

  CREATE PROCEDURE SPPUBLISHER
  AS
  SELECT PUB_NAME FROM publishers
  GO

The above code create a procedure named as 'SPPUBLISHER' and it execute SQL statement that select all publisher name from publishers table from the PUB database.

Using stored procedures, database operations can be encapsulated in a single command, optimized for best performance, and enhanced with additional security. To call a stored procedure from VB.NET application, set the CommandType of the Command object to StoredProcedure.

  command.CommandType = CommandType.StoredProcedure

From the following source code you can see how to call a stored procedure from VB.NET application.

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 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 = "SPPUBLISHER"
		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