Futureload406

• The Northwind database is a sample database that was originally created by Microsoft and used as the basis for their tutorials in a variety of database products for decades.
• The most common format is the Structured Query Language (SQL) standard query format that many database management systems use. SQL is a powerful language capable of advanced queries. SQL uses a SELECT statement to select specific data. Consider an example based on the Northwind database that frequently ships with database products as a tutorial.

• Northwind Database Script Sql
• Northwind Database Sql Queries List
• Northwind Database Sql Queries Login
• Northwind Database Sql Queries
• Northwind Database Sql Questions
• Northwind Database Sql Queries Example

By: Edwin Sarmiento | Updated: 2008-07-07 | Comments (5) | Related: 1 | 2 | 3 | 4 | 5 | More >Language Integrated Query LINQ

Problem

SQL Querying multiple tables using JOIN - Northwind. Inner Join Query with 2 tables giving different result compared to Sub Query for same 2 tables Northwind database.

In a recent tip onLanguage INtegrated Query (LINQ),you have described how it can be used as a query language extension to both VB.NETand C#. How can we use LINQ to SQL in .NET applications to query SQL Server databases?

Solution

LINQ to SQL is a component of .NET Framework version3.5 that provides a run-time infrastructure for managing relational data as objects. As most developers are used to the object-oriented paradigm, accessingdata from a relational database requires understanding of relational paradigm. LINQto SQL makes querying a SQL Server database as if it was an object and eliminatethe need to write very complex SQL statements whenever necessary. It is also anOR/M (object-relational mapping) implementation that allows you to model a relationaldatabase using .NET classes. You can then run queries in the database as well asperform insert, update and delete actions to it.

Model your SQL Server databases using LINQ

VisualStudio 2008 and the corresponding Express Editions come with a visual designer whichmakes it easy to model and visualize a database as a LINQ to SQL object model. Let'screate a console project by following the steps outlined in theIntroduction to LINQ tip. Wewill name our Visual Studio project ConsoleLINQ2SQL and use theC# language. Next, let's add a new item in our project. Click on the Projectmenu and select Add New Item... from Visual Studio.

On the Add New Item window, select LINQto SQL Classes under the Templates section and name itNorthwind.dbml as we will be using the Northwind database for thesample queries

This will do a couple of things. First, it will add theSystem.Data.Linqnamespace in the project. This assembly contains the framework implementation forLINQ to SQL and makes it easy to refer to SQL Server database objects as how developersdo in object-oriented programming. Next, it gives you a object-relational designerwhere you can drag-and-drop database objects and it will create the corresponding.The designer will open up, displaying two separate panes, after adding the LINQto SQL class in your project.

To start adding database objects in the designer surface, clickon the Server Explorer link. Create a new connection to a SQL Serverdatabase by clicking on the Connect to Database icon in theServer Explorer window. The Add Connection windowappears which will ask you to specify the SQL Server instance name, database nameand your login credentials. I've selected the Northwind databasefor the samples

Once the database connection has been created, you can start dragging and droppingdatabase objects in the designer surface. Expand the Server Explorer window andselect the Instancename.Northwind.dbo data connection. Expand theTables folder to see all of the tables inside the Northwinddatabase. Start by selecting the Customers, Order Details,Orders, Products, Shippers andSuppliers tables and dragging them on the designer surface.

The designer will create the corresponding classes necessary to translate yourdatabase into objects. Each table dragged into the designer surface will have acorresponding entity class. Each instance of the entity class represents a row withinthe table. The arrows represent the relationships between the entity classes asdefined by the primary key/foreign key constraints in the database. The directionof the arrows indicate whether the associations are one-to-one or one-to-many.

Introducing the DataContext class

The designer will create a corresponding DataContext class together with allof the entity classes that correspond to the tables in the database. This is doneimmediately after clicking the Save button within the designersurface. TheDataContext class represents the main entry point for the LINQ to SQL frameworkand is the most important class. I refer to it as a proxy class responsible forrepresenting how the database should look like from an object-oriented perspective.We will be using this proxy class to query entities from the database as well asperform changes. The properties and methods of this proxy class correspond to thetables and stored procedures we have added, respectively. We will look at usingstored procedures with LINQ to SQL in a future tip.

Querying tables using LINQ

Let's start writing some code inside the static void Main(string[] args).

As you would have figured out, there isn't much difference in the query fromthe Introduction to LINQ tip except for the part where the output needs to be writtento the console window. Since c is an instance of the dbNorthwind.Customersclass. Since we are returning an instance of the class, it would make sense to simplydisplay the properties rather than the entire object which, in this case, theCompanyName property of the dbNorthwind.Customersclass. As previously mentioned, the properties of the class represent the columnsin the associated table. LINQ to SQL also makes sure that the properties are strongly-typedto avoid data type conflicts between the class and the columns in the associatedtable. You can also apply filtering and sorting in the queries as you would do usingT-SQL.

Your output will look like this when you run your project in Visual Studio. Youcan press F5 or click on Debug - Start Debuggingin Visual Studio

We can see what is happening under the covers by logging theactivity to the console. Insert the following code after thevar dbNorthwind=newNorthwindDataContext();line and run the application.

The first few lines of the output display the T-SQL query that LINQ generatedand the provider used for the backend SQL Server database. Mine displays SqlProvider( Sql2000) as I am using a SQL Server 2000 instance. You canalso use SQL Profiler to capture the generated T-SQL queries. Noticethe use of parameters in the query because of the existence of a whereclause. LINQ to SQL generates parameterized queries in order to prevent SQL injectionattacks thru the application.

Query Across Relationships

Since the O/R model has generated the corresponding relationships for your classes,you can write queries that refer to those relationship properties. Let's say youwanted to retrieve all the OrderIDs and ContactNameof all Customers based in Italy. You can writeyour query as shown below

You will notice that the IntelliSense feature in VisualStudio will pick up the corresponding relationships between the class instancesusing the dot (.) notation.

Next Steps

You have seen how you can use LINQ to SQL to querySQL Server databases. LINQ to SQL is only for SQL Server and not for any other relationaldatabases out there so we still need to wait for LINQ to SQL implementations forOracle or IBM DB2. In future tips, we will look at data manipulation and using storedprocedures in LINQ to SQL.

• Give this example a try and change the query parameters so you can havea feel of how LINQ to SQL works.
• Learn more aboutLINQ to SQL

About the author

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

Northwind Database Script Sql

• 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

Northwind Database Sql Queries List

• 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

Northwind Database Sql Queries Login

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

Northwind Database Sql Queries

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.

Northwind Database Sql Questions

Northwind Database Sql Queries Example

See also