VB.Net/Database ADO.net/Sql Server Store Procedure

Call store Procedure and pass parameters

 
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
    Shared Dim WithEvents con As SqlConnection
    Shared Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand("GetCountryAndOutputParam", con)
        cmd.rumandType = CommandType.StoredProcedure
        cmd.Parameters.Add( _
            New SqlParameter("@country", SqlDbType.VarChar, 50)).Value = "USA"
        cmd.Parameters.Add(New SqlParameter("@count", SqlDbType.Int))
        cmd.Parameters("@count").Direction = ParameterDirection.Output
        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))
            End While
            reader.Close()
        Finally
            con.Close()
        End Try
        Console.WriteLine("{0} - {1}", _
                          "Count", cmd.Parameters("@count").Value.ToString())
    End Sub
End Class

Call Store Procedure in SQL Server

 
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
    Shared Dim WithEvents con As SqlConnection
    Shared Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand("Ten Most Expensive Products", con)
        cmd.rumandType = CommandType.StoredProcedure
        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Console.WriteLine("{0} - {1:C}", _
                                  reader.GetString(0), reader.GetDecimal(1))
            End While
            reader.Close()
        Finally
            con.Close()
        End Try
    End Sub
End Class

Fill dataset with the results from Store Procedure

 
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Module Module1
    Sub Main()
        " Create a connection object
        Dim dbConn As New SqlConnection( _
                       "server=(local); database=pubs; integrated security=true")
        Dim cmd As New SqlCommand()
        cmd.rumandText = "spRetrieveAuthor"
        cmd.rumandType = System.Data.rumandType.StoredProcedure
        cmd.Connection = dbConn
        cmd.Parameters.Add(New SqlParameter("@au_id", SqlDbType.Char, 11, "au_id"))
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet("Author")
        da.SelectCommand.Parameters(0).Value = "172-32-1176"
        da.Fill(ds)
        If Not ds Is Nothing Then
            MessageBox.Show("Hi, author: " & _
                                   ds.Tables(0).Rows(0)("au_lname").ToString())
        End If

    End Sub
End Module

Stored Procedure Example

  
Imports System
Imports System.Data
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub StoredProcedureExample(ByVal con As SqlConnection, ByVal managerID As Integer)
            Using com As SqlCommand = con.CreateCommand
                com.rumandType = CommandType.StoredProcedure
                com.rumandText = "uspGetManagerEmployees"
                com.Parameters.Add("@ManagerID", SqlDbType.Int).Value = managerID
                Dim result As Integer = com.ExecuteNonQuery
                Using reader As SqlDataReader = com.ExecuteReader
                    Console.WriteLine("Employees managed by manager #{0}.", managerID.ToString)
                    While reader.Read
                        Console.WriteLine("  {0}, {1} ({2})", reader("LastName"), reader("FirstName"), reader("employeeID"))
                    End While
                End Using
            End Using
        End Sub
        Public Shared Sub Main()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                StoredProcedureExample(con, 185)
                con.Close()
            End Using
        End Sub
    End Class