(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

Issue: April 2002
Section: ASP.NET
Author: Bill Wagner

VB.NET, SQL Server 2000	Find Products by Name 
Listing 1	You see here how to use the DataSet to insulate your application from the database. This accessor retrieves the results from a previous search, or performs a new search, caches the result, and returns the cached result.

Public ReadOnly Property FindProductName _
	(ByVal search As String) As DataSet
	Get
		Dim result As DataSet
		result = soughtProducts.Item(search)
		If result Is Nothing Then
			Dim northWind As _
				SqlClient.SqlConnection
			Dim sqlDataView As _
				SqlClient.SqlDataAdapter

			northWind = New _
				SqlClient.SqlConnection()
			northWind.ConnectionString = _
				connectString
			sqlDataView = New _
				SqlClient.SqlDataAdapter()
			result = New DataSet()

			sqlDataView.SelectCommand = New _
				System.Data.SqlClient.SqlCommand()
			sqlDataView.SelectCommand. _
				CommandText = "SELECT " & _
				"ProductID, ProductName, " & _
				"SupplierID, CategoryID, " & _
				"QuantityPerUnit, UnitPrice, " & _
				"UnitsInStock, UnitsOnOrder, " & _
				"ReorderLevel, Discontinued " & _
				"FROM Products WHERE " & _
				"(ProductName LIKE " & _
				"N'%" & search & "%')"
			sqlDataView.SelectCommand. _
				Connection = northWind
			sqlDataView.TableMappings.AddRange( _
				New System.Data.Common. _
				DataTableMapping() _
				{New System.Data.Common. _
				DataTableMapping("Table", _
				"Products", New System. _
				Data.Common.DataColumnMapping() _
				{New System.Data.Common. _
				DataColumnMapping _
				("ProductID", "ProductID"), _
				New System.Data.Common. _
				DataColumnMapping _
				("ProductName", "ProductName"), _
				New System.Data.Common. _
				DataColumnMapping _
				("SupplierID", "SupplierID"), _
				New System.Data.Common. _
				DataColumnMapping _
				("CategoryID", "CategoryID"), _
				New System.Data.Common. _
				DataColumnMapping _
				("QuantityPerUnit", _
				"QuantityPerUnit"), _
				New System.Data.Common. _
				DataColumnMapping _
				("UnitPrice", "UnitPrice"), _
				New System.Data.Common. _
				DataColumnMapping _
				("UnitsInStock", "UnitsInStock"), _
				New System.Data.Common. _
				DataColumnMapping _
				("UnitsOnOrder", "UnitsOnOrder"), _
				New System.Data.Common. _
				DataColumnMapping _
				("ReorderLevel", "ReorderLevel"), _
				New System.Data.Common. _
				DataColumnMapping _
				("Discontinued", "Discontinued")})})
			sqlDataView.Fill(result)
			soughtProducts.Item(search) = result
			northWind.Close()
		End If
		Return soughtProducts.Item(search)
	End Get
End Property
