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 https://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 https://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 https://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
To add Dlinq, Choose Project->Add New Item->LINQ to SQL Classes
Go to Server Explorer, right click on Data Connections, choose Add Connection. For Data Source, choose Microsoft SQL Server
Data Provider: .Net Framework Data Provider for SQL Server
For Server name, type “.\SQLExpress”, then Select database name Northwind.
It’s reassuring that “Test Connection” succeeds.
Expand Server Explorer->SQLExpress.Northwind\Tables and Drag/Drop Customers, Orders and Order Details onto the DataClasses1.dbml design surface.
Expand Stored Procedures and Drag/Drop “Ten Most Expensive Products” onto the right part of the designer.
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)
The Foxpro SQL code:
IF .t.
*use SQL Express or SQL Server data
CLOSE DATABASES all
CREATE DATABASE test
CREATE CONNECTION nwind CONNSTRING "DRIVER=SQL Server;SERVER=.\sqlexpress;DATABASE=northwind;Trusted_Connection=Yes"
CREATE VIEW customers remote connection nwind as select * from customers
CREATE VIEW orders remote connection nwind as select * from orders
CREATE VIEW orderDetails remote connection nwind as select * from "Order Details"
ELSE
*Use native fox data
OPEN DATABASE HOME()+"\samples\northwind\northwind"
ENDIF
SELECT ;
C.customerID, ;
C.companyname, ;
SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ;
FROM Customers C ;
INNER JOIN Orders O ;
ON C.customerID = O.customerID ;
INNER JOIN OrderDetails D ;
ON O.orderid = D.orderid ;
GROUP BY C.customerID, C.companyname ;
ORDER BY C.customerID
The Linq code:
q = From cust In NWind.Customers _
Join ord In NWind.Orders On cust.CustomerID Equals ord.CustomerID _
Join det In NWind.Order_Details On ord.OrderID Equals det.OrderID _
Group By cust.CustomerID Into _
Sum(det.Quantity * det.UnitPrice * (1 - det.Discount)) _
Order By CustomerID _
Select CustomerID, Sum
The Linq generated SQL statement sent to SQL Express can be seen from the log:
SELECT [t4].[CustomerID], [t4].[value] AS [Sum]
FROM (
SELECT SUM([t3].[value]) AS [value], [t3].[CustomerID]
FROM (
SELECT (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t2].[Quantity]))) * [t2].[UnitPrice])) * (@p0 - [t2].[Discount]) AS [value], [t0].[CustomerID]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
INNER JOIN [dbo].[Order Details] AS [t2] ON [t1].[OrderID] = [t2].[OrderID]
) AS [t3]
GROUP BY [t3].[CustomerID]
) AS [t4]
ORDER BY [t4].[CustomerID]
-- @p0: Input Single (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20808.0
Now, let’s refine the query to be per customer per order
Fox code:
SELECT ;
C.customerID, ;
C.companyname, ;
o.orderid,;
SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ;
FROM Customers C ;
INNER JOIN Orders O ;
ON C.customerID = O.customerID ;
INNER JOIN OrderDetails D ;
ON O.orderid = D.orderid ;
GROUP BY C.customerID, C.companyname ,o.orderid INTO CURSOR foo
Linq code:
q = From cust In NWind.Customers.AsEnumerable _
Join ord In NWind.Orders.AsEnumerable On cust.CustomerID Equals ord.CustomerID _
Join det In NWind.Order_Details.AsEnumerable On ord.OrderID Equals det.OrderID _
Group By cust.CustomerID, cust.CompanyName, ord.OrderID _
Into CustTotal = Sum(det.Quantity * det.UnitPrice * (1 - det.Discount))
Note: the AsEnumerable is required only for Beta 2: it’s due to a bug with composite keys that will be fixed.
Now a more complicated query: What are NorthWind’s best customers by calculating Sales total by customer with % of all customer orders.
Fox code:
SELECT ;
C.customerID, ;
C.companyname, ;
SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ,;
(SUM(D.quantity*D.unitprice*(1-discount)) / ;
(SELECT SUM((quantity*unitprice)*(1-discount)) ;
FROM OrderDetails D2) ;
)*100 AS PctTotal ;
FROM Customers C ;
INNER JOIN Orders O ;
ON C.customerID = O.customerID ;
INNER JOIN OrderDetails D ;
ON O.orderid = D.orderid ;
GROUP BY C.customerID, C.companyname
ORDER BY pctTotal DESC
Linq code:
q = From cust In NWind.Customers.AsEnumerable _
Join ord In NWind.Orders.AsEnumerable On cust.CustomerID Equals ord.CustomerID _
Join det In NWind.Order_Details.AsEnumerable On ord.OrderID Equals det.OrderID _
Group By cust.CustomerID, cust.CompanyName Into _
CustTotal = Sum(det.Quantity * det.UnitPrice * (1 - det.Discount)), _
PctTotal = Sum(100 * (det.Quantity * det.UnitPrice * (1 - det.Discount)) / _
Aggregate d2 In NWind.Order_Details _
Into Sum(d2.Quantity * d2.UnitPrice * (1 - d2.Discount))) _
Order By CustomerID _
Select CustomerID, CompanyName, CustTotal, PctTotal _
Order By PctTotal Descending
The Fox code executes nearly instantaneously, but the Linq code took a long time. The log showed that the Aggregate was being calculated repeatedly. A simple optimization was to calculate the constant SumDetails before the query, and it executed much faster.
Dim SumDetails = Aggregate d2 In NWind.Order_Details _
Into Sum(d2.Quantity * d2.UnitPrice * (1 - d2.Discount))
q = From cust In NWind.Customers.AsEnumerable _
Join ord In NWind.Orders.AsEnumerable On cust.CustomerID Equals ord.CustomerID _
Join det In NWind.Order_Details.AsEnumerable On ord.OrderID Equals det.OrderID _
Group By cust.CustomerID, cust.CompanyName Into _
CustTotal = Sum(det.Quantity * det.UnitPrice * (1 - det.Discount)), _
PctTotal = Sum(100 * (det.Quantity * det.UnitPrice * (1 - det.Discount)) / _
SumDetails) _
Order By CustomerID _
Select CustomerID, CompanyName, CustTotal, PctTotal _
Order By PctTotal Descending
BTW, try typing some of the code to experience the intellisense within VB Linq!
Which one executes faster for you?
See also:
Simple demo of Customer/Orders one to many form with conflict detection and resolution
C# and Fox versions of Cool Linq Query
Webcrawl a blog to retrieve all entries locally: RSS on steroids