• Northwind Database Sql Queries Example
• Northwind Database Sql Queries Tutorial

Northwind Database is a sample database that is shipped along with Microsoft Access application. Basically, the database is about a company named 'Northwind Traders'.The database captures all the sales transactions that occurs between the company i.e. Northwind traders and its customers as well as the purchase transactions between Northwind and its suppliers. However, in this case, I wanted to see if I could use SQL queries to not only download the data that I needed, but to calculate some basic summary stats that would be useful. Sql please tell me any site which have collection of queries on any microsoft database like Northwind,AdventureWorks,pub etc for practice.Please help me. Thanks in advance. Northwind Database Querying with Linq 1. Give the name, address, city, and region of employees. Give the name, address, city, and region of employees living in USA 3. Give the name, address, city, and region of employees older than 50 years old 4. Give the name, address, city, and region of employees that have placed orders to be delivered. Designating properties on the class to represent database columns. Specifying the connection to the Northwind database. Creating a simple query to run against the database. Executing the query and observing the results. Creating a LINQ to SQL Solution.

This walkthrough provides a fundamental end-to-end LINQ to SQL scenario with minimal complexities. You will create an entity class that models the Customers table in the sample Northwind database. You will then create a simple query to list customers who are located in London.

This walkthrough is code-oriented by design to help show LINQ to SQL concepts. Normally speaking, you would use the Object Relational Designer to create your object model.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

This walkthrough was written by using Visual C# Development Settings.

Prerequisites

• This walkthrough uses a dedicated folder ('c:linqtest5') to hold files. Create this folder before you begin the walkthrough.

• This walkthrough requires the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft download site. For instructions, see Downloading Sample Databases. After you have downloaded the database, copy the file to the c:linqtest5 folder.

Overview

This walkthrough consists of six main tasks:

• Creating a LINQ to SQL solution in Visual Studio.

• Mapping a class to a database table.

• Designating properties on the class to represent database columns.

• Specifying the connection to the Northwind database.

• Creating a simple query to run against the database.

• Executing the query and observing the results.

Creating a LINQ to SQL Solution

In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to SQL project.

To create a LINQ to SQL solution

1. On the Visual Studio File menu, point to New, and then click Project.

2. In the Project types pane of the New Project dialog box, click Visual C#.

3. In the Templates pane, click Console Application.

4. In the Name box, type LinqConsoleApp.

5. In the Location box, verify where you want to store your project files.

6. Click OK.

Adding LINQ References and Directives

This walkthrough uses assemblies that might not be installed by default in your project. If System.Data.Linq is not listed as a reference in your project (expand the References node in Solution Explorer), add it, as explained in the following steps.

To add System.Data.Linq

1. In Solution Explorer, right-click References, and then click Add Reference.

2. In the Add Reference dialog box, click .NET, click the System.Data.Linq assembly, and then click OK.

   The assembly is added to the project.

3. Add the following directives at the top of Program.cs:

Mapping a Class to a Database Table

In this step, you create a class and map it to a database table. Such a class is termed an entity class. Note that the mapping is accomplished by just adding the TableAttribute attribute. The Name property specifies the name of the table in the database.

To create an entity class and map it to a database table

• Type or paste the following code into Program.cs immediately above the Program class declaration:

Designating Properties on the Class to Represent Database Columns

In this step, you accomplish several tasks.

• You use the ColumnAttribute attribute to designate CustomerID and City properties on the entity class as representing columns in the database table.

• You designate the CustomerID property as representing a primary key column in the database.

• You designate _CustomerID and _City fields for private storage. LINQ to SQL can then store and retrieve values directly, instead of using public accessors that might include business logic.

To represent characteristics of two database columns

• Type or paste the following code into Program.cs inside the curly braces for the Customer class.

Specifying the Connection to the Northwind Database

In this step you use a DataContext object to establish a connection between your code-based data structures and the database itself. The DataContext is the main channel through which you retrieve objects from the database and submit changes.

You also declare a Table<Customer> to act as the logical, typed table for your queries against the Customers table in the database. You will create and execute these queries in later steps.

To specify the database connection

• Type or paste the following code into the Main method.

  Note that the northwnd.mdf file is assumed to be in the linqtest5 folder. For more information, see the Prerequisites section earlier in this walkthrough.

Creating a Simple Query

In this step, you create a query to find which customers in the database Customers table are located in London. The query code in this step just describes the query. It does not execute it. This approach is known as deferred execution. For more information, see Introduction to LINQ Queries (C#).

You will also produce a log output to show the SQL commands that LINQ to SQL generates. This logging feature (which uses Log) is helpful in debugging, and in determining that the commands being sent to the database accurately represent your query.

To create a simple query

• Type or paste the following code into the Main method after the Table<Customer> declaration.

Executing the Query

In this step, you actually execute the query. The query expressions you created in the previous steps are not evaluated until the results are needed. When you begin the foreach iteration, a SQL command is executed against the database and objects are materialized.

To execute the query

1. Type or paste the following code at the end of the Main method (after the query description).

2. Press F5 to debug the application.

   Note

   If your application generates a run-time error, see the Troubleshooting section of Learning by Walkthroughs.

   The query results in the console window should appear as follows:

   ID=AROUT, City=London

   ID=BSBEV, City=London

   ID=CONSH, City=London

   ID=EASTC, City=London

   ID=NORTS, City=London

   ID=SEVES, City=London

3. Press Enter in the console window to close the application.

Next Steps

The Walkthrough: Querying Across Relationships (C#) topic continues where this walkthrough ends. The Query Across Relationships walkthrough demonstrates how LINQ to SQL can query across tables, similar to joins in a relational database.

If you want to do the Query Across Relationships walkthrough, make sure to save the solution for the walkthrough you have just completed, which is a prerequisite.

See also

This topic shows you how you can use SQL Prompt. The following examples are provided:

You can follow the examples on your own system. Most examples in this topic use SQL Server 2005 and you will need access to a SQL Server on which the AdventureWorks example database has been installed. In addition, the cross-database queries and queries using linked SQL Servers examples use SQL Server 2000 on which the Northwind and pubs example databases have been installed.

Before you follow the examples, to ensure that you see the same results as those illustrated, set your options to the default values. To do this on the SQL Prompt menu, click Options, and then click Restore All Defaults.

Typing a simple query

This example shows you how you can use the SQL Prompt candidate list to assist you when you are typing a query.

1. On the Options dialog box, click the Inserted Candidates tab, click the Formatting page, and then select the Qualify object names check box and click OK.
2. In your query editor window, type:

   and press SPACEBAR.

   The candidate list displays the Suggested candidates category; tables are displayed at the top of the list.

3. Type c to filter the list by candidates that begin with c.

   The Contact table is highlighted.

4. Press ENTER to insert the Contact table.

   SQL Prompt inserts the table name. Because you selected the Qualify object names option, SQL Prompt qualifies the table name with the schema (owner) name Person. The identifiers are surrounded with brackets [ ]; this is another option in Inserted Candidates, Formatting, which you can switch off if required.

5. Type WHERE and press SPACEBAR.

   The candidate list displays column names for the Contact table and associated data types.

6. Type L and press ENTER to insert the LastName column.
7. Type LIKE 'Fr%' so that your query returns rows where LastName begins with Fr.

   By default, when you type an opening quotation mark, SQL Prompt automatically inserts the closing quotation mark. If you type the closing quotation mark (for example, because you have not noticed the automatic insertion), SQL Prompt overtypes the auto-inserted quotation mark. You can change this setting and other auto-completion options, on the Auto Insert, Closing Characters options page.

8. Delete the * (after SELECT) and press CTRL+SPACEBAR to display the candidate list.

   The candidate list displays columns from the Contact table.

   Note that you must always type * after the SELECT keyword, even when you want use SQL Prompt to insert the column list later. If you do not type * the candidate list may display incorrect candidates. For more information, see Troubleshooting

9. Type f and press ENTER to insert the FirstName column.
10. Press COMMA, type L, and then press ENTER to insert the LastName column.

Expanding column lists

These examples show you how SQL Prompt can expand a column list when you are using a SELECT statement.

In the first example, you type SELECT * and you specify the tables or views in the FROM clause; you can then complete the column list by placing the insertion point after the * and pressing TAB:

1. Ensure that Qualify object names is selected on the Inserted Candidates, Formatting options page.
2. In your query editor window, type:
3. Place the insertion point after the *
4. Press TAB.

   SQL Prompt expands the column list.

This next example shows how SQL Prompt can complete the column list for you when you type a SELECT table.* fragment and press TAB.

1. Type:

   SELECT [Person].[Contact].*

2. Press TAB.

   SQL Prompt expands the column list. This time, SQL Prompt qualifies the columns with the table name and schema (owner) name irrespective of your Qualify column names option setting, because you specified the table name.

Selecting columns

This example shows how you can use the SQL Prompt column picker to select columns for the column list in a SELECT statement:

1. Type:
2. Delete the * after SELECT and press CTRL+SPACEBAR to display the candidate list.

   The candidate list displays columns from the Address table and the AddressType table.

3. Press CTRL+LEFT ARROW or click to display the Column picker category.
4. Select the some columns.

   To select a column, click the check box, or highlight the column and press SPACEBAR. You can use SHIFT and CTRL to select multiple columns.

5. Press ENTER to insert the selected columns into your query editor.

For more information about how to use the column picker, see Using the Candidate List.

Generating full INSERT statements

These examples show how SQL Prompt generates full INSERT statements.

1. On the Options dialog, click the Inserted Candidates tab, click the SQL page, and ensure that the following check boxes are selected:
   • Insert full INSERT statement
   • Insert default values for data types
   • Show hints for data types
2. In your query editor window, type INSERT INTO cus
3. Select Customer.

   SQL Prompt inserts the INSERT statement. The insertion point is positioned for you to enter the values.

In the following example, hints are not shown.

1. On the Options dialog, click the Inserted Candidates tab, click the SQL page, and clear the Show hints for data types check box.
2. In your query editor window, type INSERT INTO cus, and select Customer from the candidate list.

   SQL Prompt inserts the INSERT statement. The insertion point is positioned for you to enter the values.

In the following example, default values are not shown for data types.

Northwind Database Sql Queries Example

1. On the Options dialog, click the Inserted Candidates tab, click the SQL page, and clear the Insert default values for data types check box.
2. In your query editor window, type INSERT INTO cus and select Customer from the candidate list.

   SQL Prompt inserts the INSERT statement. The insertion point is positioned for you to enter the values.

Northwind Database Sql Queries Tutorial

Executing stored procedures

This example shows how SQL Prompt inserts the parameters for stored procedures when you type EXEC and you select a stored procedure.

1. On the Options dialog box, click the Inserted Candidates tab, click the SQL page, and then ensure the Insert parameters for functions and stored procedures and Show hints for data types check boxes are selected.
2. In your query editor, type EXEC and then press SPACEBAR.

   SQL Prompt displays a list of stored procedures at the top of the candidate list. The schema panel shows the creation SQL for the highlighted stored procedure. For example:

3. Select a stored procedure by pressing UP ARROW or DOWN ARROW to highlight it, and then press ENTER to insert it.

   SQL Prompt inserts the parameters for the stored procedure and inserts the associated data types as comments. For example:

Using snippets

SQL Prompt is pre-configured with default snippets. To see the snippets, press CTRL+SPACEBAR and then CTRL+LEFT ARROW to display the Snippets category.

This example shows you how to insert the snippet code for disabling all triggers on a table.

1. Type:

   atdta

   The candidate list displays a description of the snippet and the schema panel displays the snippet code.

2. Press ENTER to insert the snippet code.

   SQL Prompt places the insertion point after TABLE for you to type the table name.

Cross-database queries and queries using linked SQL Servers

These examples use SQL Server 2000 on which the Northwind and pubs example databases have been installed.

The first example begins using the pubs database, and then accesses a table in the Northwind database.

1. On the Options dialog box, click the Listed Candidates tab, and on the Cross-Database Support page ensure the Enable cross-database and linked server support check box is selected.
2. In your query editor window, type:

   The candidate list displays the candidates. For example:

3. Select Northwind.
4. Type . (dot).

   SQL Prompt caches the Northwind database, and displays a list of the owners in the database. (Note that you may be asked to enter authentication credentials if SQL Prompt has been unable to log on to the database.)

5. Select dbo, and type . (dot) again.

   SQL Prompt displays the candidate list containing tables and views.

6. Select the Products table, then place the insertion point after the *.
7. Press TAB.

   SQL Prompt inserts the columns from the dbo.Products table in the Northwind database.

This next example shows a distributed query using a linked SQL Server 2000 instance from a SQL Server 2005 instance. The linked SQL Server 2000 instance is configured with the name READONLY.TESTNET on the SQL Server 2005 instance. For more information about linking servers, refer to SQL Server Books Online.

1. On the SQL Server 2005 instance, in your query editor window, type:

   The candidate list displays the candidates. For example:

2. Select the linked SQL Server, and then type . (dot).

   If the SQL Server uses SQL Server authentication and you have not saved the connection details, SQL Prompt displays a dialog box for you to enter the authentication credentials.

   If you click Do Not Connect, in future SQL Prompt will not display the candidate list for that SQL Server automatically; if you want to enter the credentials at a later date so that you can see candidates, press CTRL+SPACEBAR.

   For this example, enter the credentials and click Authenticate, and then press CTRL+SPACEBAR to display the candidate list. The candidate list shows a list of databases on the linked server.

3. Select Northwind, and then type . (dot).

   You may need to press CTRL+SPACEBAR to display the candidate list. If the authentication credentials you entered allow you unrestricted access to the SQL Server, you are not prompted to enter them again.

4. Select dbo from the list of candidates, and then type . (dot).
5. Select the Customers table from the list of candidates, then place the insertion point after the *.
6. Press TAB.

   SQL Prompt inserts the columns from the dbo.Customers table in the Northwind database.

Common table expressions (CTEs)

This example shows you how you can use SQL Prompt when writing a query that contains a CTE (common table expression). A common table expression is a named results set that exists only for the duration of the query.

This example uses the Northwind database.

1. In your query editor window, type:

   This statement defines a CTE called DirReps that creates a simple set of results, the number of employees that report to each manager.

2. In your query editor window, type:

   and press SPACEBAR.

   The candidate list displays the Suggested candidates category. The common table expression you have defined is displayed at the top of the list.

3. Press ENTER to insert the DirReps common table expression.
4. Type

   The candidate list displays the ManagerID column, the only column defined in the common table expression that matches the first character typed.

5. Press ENTER to insert the column name.

Note that SQL Prompt also supports recursive CTEs, and multiple CTEs within a single WITH statement.