Seungweon's Blog

Install Northwind for SQL Express and use Visual Studio and DLINQ to query it.



SQLExpress is free and comes with Visual Studio, but the sample Northwind database isn’t included. You can download sample databases Northwind and Pubs from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en. (or you can use the Fox Upsizing wizard to send the fox native Northwind to SQL Express or SQL Server)

To install Northwind or migrate any other MSDE data, execute a stored procedure exec sp_attach_db using the command line program SQLCMD see http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx.

Notice how Northwnd is spelled without a “I”:

d:\>sqlcmd -S .\sqlexpress
1> select name from master.dbo.sysdatabases
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb

(4 rows affected)

1> exec sp_attach_db 'NorthWind','C:\SQL Server 2000 Sample Databases\northwnd.mdf','C:\SQL Server 2000 Sample Databases\northwnd.ldf'
2> go

Converting database 'NorthWind' from version 539 to the current version 611.
Database 'NorthWind' running the upgrade step from version 539 to version 551.
Database 'NorthWind' running the upgrade step from version 551 to version 552.
Database 'NorthWind' running the upgrade step from version 552 to version 553.
Database 'NorthWind' running the upgrade step from version 553 to version 554.
Database 'NorthWind' running the upgrade step from version 554 to version 589.
Database 'NorthWind' running the upgrade step from version 589 to version 590.
Database 'NorthWind' running the upgrade step from version 590 to version 593.
Database 'NorthWind' running the upgrade step from version 593 to version 597.
Database 'NorthWind' running the upgrade step from version 597 to version 604.
Database 'NorthWind' running the upgrade step from version 604 to version 605.
Database 'NorthWind' running the upgrade step from version 605 to version 606.
Database 'NorthWind' running the upgrade step from version 606 to version 607.
Database 'NorthWind' running the upgrade step from version 607 to version 608.
Database 'NorthWind' running the upgrade step from version 608 to version 609.
Database 'NorthWind' running the upgrade step from version 609 to version 610.
Database 'NorthWind' running the upgrade step from version 610 to version 611.
1> exit

(SQL Express supports multiple users, but SQL Server Compact doesn’t: see Choosing Between SQL Server Compact and SQL Server Express

To enable other users on other machines to connect to SQL Express, enable remote connectivity: see http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277)

Now let’s create some queries of Northwind:

Start Visual Studio 2008

Choose File->New->Project->VB Windows Forms Application

1. To add Dlinq, Choose Project->Add New Item->LINQ to SQL Classes
2. Go to Server Explorer, right click on Data Connections, choose Add Connection. For Data Source, choose Microsoft SQL Server
3. Data Provider: .Net Framework Data Provider for SQL Server
4. For Server name, type “.\SQLExpress”, then Select database name Northwind.(It’s reassuring that “Test Connection” succeeds.)
5. Expand Server Explorer->SQLExpress.Northwind\Tables and Drag/Drop Customers, Orders and Order Details onto the DataClasses1.dbml design surface.
6. Expand Stored Procedures and Drag/Drop “Ten Most Expensive Products” onto the right part of the designer.
7. In Solution Explorer, click on “Show All Files” and take a look at all the code that was generated for you in DataClassses1.Designer.VB.

Choose Form1, View->Code, then paste in:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.WindowState = FormWindowState.Maximized
Dim NWind As New DataClasses1DataContext
NWind.Log = Console.Out ' add this line to see the generated SQL stmt
Browse(NWind.Ten_Most_Expensive_Products)
End Sub

Sub Browse(Of t)(ByVal seq As IEnumerable(Of t))
Dim GridView As New DataGridView
GridView.Width = Me.Width
GridView.Height = Me.Height
Me.Controls.Add(GridView)
Dim pl = New List(Of t)(seq)
GridView.DataSource = pl
Me.Text = pl.Count.ToString
GridView.Dock = DockStyle.Fill
GridView.AutoResizeColumns()
End Sub

End Class

Now hit F5 and see the 10 most expensive products!

In order to get the total amount ordered per customer, we need to calculate the sum of orders per customer. This is a 3 table join: To calculate the amount for a particular order, the detail table line items need to be examined: The amount ordered is the sum of the detail quantity multiplied by the unit price (less any discount)

Source: http://blogs.msdn.com/calvin_hsia/archive/2007/08/17/install-northwind-for-sql-express-and-use-visual-studio-and-dlinq-to-query-it.aspx