Partager via

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

  • Article

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

Comments