A Wenham

Websites and whatnot

VB.NET ADO examples and whatnot

Getting a table of data from a MS-SQL database using a Stored Procedure in VB.NET

Here we are getting a table of data, a fairly common thing to do in using VB.NET, note here that you will want to make a function called getSQLConnection to get and open a vaild connection to your database.

For your connection string, if you are in a web environment, you might want to get from the web.config of your site. Like so :

	ConfigurationManager.ConnectionStrings("NameOfConnectionString")

Here is a get data table function that you might want to use, note the using blocks which help with GC should the statment fail.


Public Function GetDataTable(ByVal StoredProcName As String, ByVal StoredProcPrams As SqlParameter()) As System.Data.DataTable
	Try
		
		Dim returnDT as New DataTable
		
		Using SQLConnection = getSQLConnection() ' call a function to get your connection string and open it.
			Using command = New SqlCommand

				command.Connection  = SQLConnection 
				command.CommandType = Data.CommandType.StoredProcedure
				command.CommandText = StoredProcName

				If Not (StoredProcPrams Is Nothing) Then
					For Each Parameter As SqlParameter In StoredProcPrams
						command.Parameters.Add(Parameter)
					Next
				End If
				
				Using DataAdapter As New SqlDataAdapter(command)
					
					DataAdapter.Fill(returnDT)
					Return returnDT

				End Using
			End Using
		End Using
	

	Catch ex As Exception
		Throw New ApplicationException("Error in GetDataTable (" & StoredProcName & ") EX:" & ex.ToString)
		' maybe some logging here 
	End Try

End Function