Fastest Start for LINQ to SQL
[Blog Map]
Every now and then, I need to have a source of data for an example or proof of concept that I’m building. In my focus on LINQ to XML, Open XML, and now SharePoint, it’s pretty rare that I have to directly use a SQL database. I only do so maybe once or twice a year, and each time it comes up, I can’t remember where to get the database, and the easiest way to get started. This post is a cheat-sheet for the future - posting this just so that the next time it comes up, I can get going faster.
-
Download the Northwind and Pubs sample databases here.
-
Run the MSI. After it installs, you can find Northwind.mdb at “C:\SQL Server 2000 Sample Databases”. The installation program creates a shortcut to this directory on the start menu.
-
When running Windows Server 2008, default security settings prevent opening a database at “C:\SQL Server 2000 Sample Databases”. Create a directory in my Documents directory, such as NorthwindDatabase (C:\Users\ericwhit\Documents\NorthwindDatabase).
-
Copy NORTHWND.MDF to that directory. No need to copy NORTHWND.LDF – it’s just a log file, and will be re-created when the data connection is opened.
-
In Visual Studio, open the Server Explorer.
-
Right click on Data Connections, select Add Connection
-
Select Microsoft SQL Server Database File
-
Click Continue
-
Browse to C:\Users\ericwhit\Documents\NorthwindDatabase \NORTHWND.MDF
-
Click Test Connection, to make sure that the connection works.
-
Click Open
-
Use Windows Authentication
-
Create a Windows Console project.
-
Add a new item to the project, Select Data in Categories, Select LINQ to SQL in Templates.
-
Name the item Northwind.dbml
-
Drag tables as appropriate on to the O/R designer surface.
-
The O/R designer will ask if I want to add the database file to my project, and copy it to the output directory. Not necessary.
Add the following two Using statements:
using System.Data.Linq;
using System.Data.Linq.Mapping;
Add the following code to Main:
DataContext db = new DataContext(@"C:\Users\ericwhit\Documents\NorthwindDatabase\Northwnd.mdf");
Table<Customer> Customers = db.GetTable<Customer>();
var query =
from cust in Customers
where cust.City == "London"
select cust;
foreach (var cust in query)
Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);
To install adventure works database,
Download it here.
Install it in a sub folder of my Documents directory.
Add a data connection in Server Explorer.
