SQL Server

Connect to SQL Server using the SqlClient provider in the .NET Framework. Notice that the SQL statement in Listing 17-4 joins the Customers table and the Orders table by the CustomerId. This is much faster than creating two dataset objects and having the report join them on the client machine.

Listing 17-4. Populating a dataset with a SQL Server table.
Private Sub FillDataset(ByVal myDataSet As DataSet)
Dim myConnectionString As String
Dim myDataAdapter As SqlClient.SqlDataAdapter
Dim SQL As String
SQL = "SELECT Customers.*, Orders.* " & _
"FROM Customers INNER JOIN Orders " & _
"ON Customers.CustomerId = Orders.CustomerId"
myConnectionString = "Data Source=(local);UID=sa;pwd=pw;Database=Northwind"
myDataAdapter = New SqlClient.SqlDataAdapter(mySQL, myConnectionString)
myDataAdapter.Fill(myDataSet, "Customers")
End Sub