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

Call store Procedure and pass parameters

<source lang="vbnet"> 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

 </source>

Call Store Procedure in SQL Server

<source lang="vbnet"> 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

 </source>

Fill dataset with the results from Store Procedure

<source lang="vbnet"> 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

 </source>

Stored Procedure Example

<source lang="vbnet"> 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
  
   
 </source>