How to Loop Through Results in LINQ to SQL

Fill DataSet with data from Access.

Looping through results in LINQ to SQL is not as complicated as you may think. You can use the LINQ technology to access SQL databases just as you would access an in-memory collection. In Visual Basic, you can connect to an Access database using the "OleDbConnection" class and query the database using a SQL statement through the "OleDbDataAdapter" class. Once you query the results, then you can loop through the query results by using LINQ to SQL.

Things You'll Need

• Microsoft Visual Studio

Instructions

1. Launch Microsoft Visual Studio, click "New Project" from the left pane of your computer screen, and expand "Visual Basic" below "Installed Templates." Click "Windows" and double-click "Console Application" from the center of the dialog window to create a new console project.

2. Copy and paste the following lines of code to import the namespaces:

   Imports System.Data.Linq.DataContext

   Imports System.Data.OleDb

3. Copy and paste the following line of code to make the connection to your Access database:

   Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Northwind 2007.accdb"

   Dim myConnection As OleDbConnection = New OleDbConnection

   Dim ds As DataSet

   Dim da As OleDbDataAdapter

4. Define the SQL statement to query all the data from a table:

   myConnection.ConnectionString = connString

   da = New OleDbDataAdapter("Select * From Employees", myConnection)

   ds = New DataSet

   da.Fill(ds, "Emp")

   myConnection.Close()

5. Copy and paste the following code to use LINQ to SQL and loop through the results:

   Dim employeeCityQuery = _

   From cust In ds.Tables("Emp").AsEnumerable _

   Where cust!Company = "Northwind Traders" _

   Select cust!City

   For Each customer In employeeCityQuery

   Console.WriteLine(customer)

   Next

   Console.ReadLine()

6. Press "F5" to run the program and view the results.

References

• MSDN: LINQ to SQL
• MSDN: Getting Started (LINQ to SQL)