This walkthrough explains how to connect to your SQL Server database and read from it using LINQ.
LINQ is Microsoft's latest solution to the problem of manipulating data from within .NET 3.5. If you are using an application to generate a Data-Access Layer for you, or you are implementing your own database calls using ADO.NET, you can now use LINQ to do those things for you, very quickly and efficiently and without any third-party components. It's fast, and it's pre-installed in Visual Studio 2008. Here's what you do to use LINQ in Visual Studio 2008:
Problem
You want to read something from your SQL Server database using LINQ. Make sure you have Visual Studio 2008 and a populated SQL Server database ready.
Solution
> Create a connection to the database:
1. Start Visual Studio 2008.
2. Start a new Web application (or a project type of your choice).
3. From the menu select View > Server Explorer (Ctrl+Alt+S)
4. In the Server Explorer pane, Right-Click Data Connections and select Add Connection.
5. In the 'Choose Data Source' dialog, choose 'SQL Server' and press Continue.
6. In the 'Add connection' dialog, enter your SQL Server name and login credentials, and then choose a database and press OK.
> Tell LINQ about the database:
7. From the menu select View > Add New Item (Ctrl+Shift+A)
8. From the list of templates, choose "LINQ To SQL Classes" and add a new instance (it's in the Data cateogry if you can't see it!)
9. In Solution Explorer, you'll now have a strange new file called DataClasses1.dbml and you'll see a big empty window as it opens.
11. Go back to the Server Explorer and pop open the new data connection you made in step 6. Open the "tables" node and then select every table (shift + mouse click).
12. Drag the whole lot onto the big empty tab called DataClasses1.dbml (which should still be open).
13. You'll see the Database schema appear in the window. Save it!
> Read something from the database using LINQ:
Your database is now visible to intellisense, via the DataClasses1DataContext object. and you are now ready to write your LINQ code which will read from the database!
14. Now to code some LINQ. In this example, I have used a table called tbCustomers, so you need to substitute your own table name. The references to AccountBalance and FirstName are fields in the tbCustomers table.
> Open default.aspx.vb and paste this into your Page_Load() event:
Dim db As New DataClasses1DataContext
Dim custs = From c In db.tbCustomers _ Where c.AccountBalance > 0 _ Select c For Each cust In custs
Response.Write(cust.FirstName) Next
Run the project and you should see your data being written to the page.