' File: http://www.garlen.net/SampleCode/Console_SQL_DataSet_Fill_Using_Stored_Proc.vb.txt
' (re)written By: Dan Garlen 3/21/04
'
'   CREATE PROCEDURE dbo.GetCustomers
'      AS
'	Select * From Customers
'   RETURN 


imports System
imports System.data
imports sql = System.Data.SqlClient

public module MyModule
	sub Main
		Console_SQL_DataSet_Fill_Using_Stored_Proc()
        AnyKey
	end sub

Private Sub Console_SQL_DataSet_Fill_Using_Stored_Proc()
		Dim strSQL as String = "user id=sa;integrated security=SSPI;data source='(local)';Database=Northwind;"

		Dim cn AS new sql.SqlConnection
		cn.ConnectionString = strSQL

		Try
			' Setup Command
			Dim cmd as new sql.SqlCommand
			cmd.CommandText = "GetCustomers"
			cmd.CommandType = CommandType.StoredProcedure
			cmd.Connection = cn
	
			Dim da as new sql.SqlDataAdapter
			da.SelectCommand = cmd

			Dim ds as New DataSet
			da.Fill(ds, "MyTableName")
			
			Dim dr as DataRow = ds.Tables("MyTableName").Rows(0)
			Dim strField As String = dr.Item("CompanyName").ToString

			Console.WriteLine("Element(0,1): " + strField)
			
			cmd = Nothing
			da = Nothing
			cn.Close()
			cn = Nothing

		Catch ex As Exception 
			Console.WriteLine("Error", ex.Message.tostring)
		End Try
	End Sub

	sub AnyKey
		Console.WriteLine("")
		Console.Write("Press enter key to continue...  ")
		Console.ReadLine()
	end Sub
			
end module