SQL Server Magazine

Language Integrated Query (LINQ), which is now available in Microsoft Visual Studio 2008, lets developers use native syntax with traditional programming languages, such as C# and Visual Basic (VB), to reference database objects as native language objects and create queries against these objects. Visual Studio 2008 and LINQ queries convert procedural code into T-SQL–based database calls, which are then executed against SQL Server. LINQ is an excellent tool for rapid application development (RAD) and generates reasonably efficient queries, although a qualified DBA can often optimize such queries further. The three versions of LINQ that are currently available are LINQ for SQL, LINQ for XML, and LINQ for Objects. Let’s take an in-depth look at LINQ for SQL, which I’ll refer to simply as LINQ.

As a new .NET technology, LINQ is part of the Microsoft .NET Framework 3.5 libraries. To leverage LINQ, your Visual Studio 2008 projects will need to target the .NET Framework 3.5. LINQ has some specific data type requirements that previous .NET versions didn’t support, causing Microsoft to make some major changes in the core .NET languages. For example, C# now includes the var type, and VB now includes inferred types. These .NET object types enable you to specify a type when you don’t know the data type of a query’s results before you execute the query.

LINQ Basics
Let’s quickly look at how LINQ works. Visual Studio 2008 provides developers with integrated tools for data-access technologies in SQL Server 2005. Note that Microsoft didn’t release a service pack for SQL Server 2005 to support the .NET Framework 3.5 and LINQ because, behind the scenes, LINQ leverages ADO.NET and the existing data-access methods that the .NET Framework 2.0 already supports.

In LINQ, an object model represents a data source. LINQ then references that data source as a DataContext object (e.g., System.Data.Linq.DataContext). The Data- Context object encapsulates the ADO.NET Connection string for your database. The DataContext object is then used with a set of object definitions (e.g., System.Data .Linq.Mapping.TableAttribute) for the tables, stored procedures, and functions in your database. Each database object that you define requires a DataContext object.

Creating Classes Using SQLMetal
You have a couple of options for creating the classes that you need to leverage LINQ within your application code. You can use Visual Studio’s (VS’s) object-relational mapper to manually type each of the necessary classes. Or you can use VS’s typed dataset tools to handle the data access and retrieval, and then use LINQ to query the result sets created by your typed datasets. Of course, using object-relational mapping limits you to one-toone (1:1) relationships between tables in your database and the objects you create. As you’ll see in the Load method, 1:1 relationships aren’t always going to meet your requirements. Therefore, I’m going to show you how to use SQLMetal to generate entity classes for your database objects.

SQLMetal.exe is a free database-mapping utility that’s included in Visual Studio 2008. You can find this command-line tool under Program Files\Microsoft SDKs\Windows V6.0a\bin. SQLMetal generates the necessary data entity and DataContext object for LINQ as either a .vb or .cs source file. To create VB source files for a database on your local SQL Server and include stored procedures in them, open the command window, navigate to the installed location (or reference the SQLMetal tool in that directory), and run the following command:

 SQLMetal.exe /server:.\SQLEXPRESS
  /database:AdventureWorks /
  sprocs /functions /language:vb /
  code:AdventureWorks.vb

This command creates the AdventureWorks.vb file in the current directory. Note that you’ll want to change the server reference because .\SQLEXPRESS references the SQL Express instance on my local server. You’ll want this parameter to contain either a shortcut to the local machine or the name of your database server. The database:AdventureWorks parameter specifies the database that’s being processed. The sprocs and function parameters indicate that you want SQLMetal to generate entity files to support the stored procedures and functions within your database. The language:vb and code:AdventureWorks.vb parameters specify your programming language and target source file, respectively. To create the AdventureWorks file in the current directory in C#, you must replace the language:vb parameter with the language:cs parameter and the .vb extension with the .cs extension. The resulting source file when I ran this command on my test machine consisted of about 20,000 lines of source code.

Using LINQ
Once you’ve generated the database entities, you can add the AdventureWorks.vb file to any project. To do so, create a new VB Windows Forms project. Next, right-click your project in the Solution Explorer and click Add Existing Item in the context menu to open the Add Existing Item dialog box, which you can use to navigate to the AdventureWorks.vb file. Then add this file to your project.

When you add the AdventureWorks.vb file to the project, VB’s background compiler will generate hundreds of errors because Visual Studio 2008 doesn’t add the references necessary for LINQ to SQL. To make these errors disappear, go to the project properties, click the References tab, and add System.Data.LINQ as a reference to your project. Then, click the Load Web Settings tab and use the interface shown in Figure 1 to add the AdvWorksDB Connection string setting for your database. This Connection string setting should use the same settings you used with SQLMetal to generate your source file. Note that you’ll need to reference security (e.g., Integrated Security in Figure 1) in your Connection string setting.

Next, create a simple form. For this example, I’ve placed a DataGridView control at the top of the form and four command buttons below it. Although the locations of the buttons aren’t important, you should name the buttons ButtonLoad, ButtonAdd, ButtonEdit, and ButtonDelete. Next, double-click each button in the design view so that Visual Studio 2008 will automatically generate the Click event handler for each button.

Now, double-click the design surface of the form to generate a Load event for the form. Then, modify the Load event with the code shown in Listing 1. Callout A in Listing 1 shows the definition of the DataContext object named AdvWorksDC that leverages the database Connection string you defined earlier. This DataContext object has been defined in the scope of the form, which enables the reuse of the DataContext object across the event handlers on the form. The second line of code in Callout A, Listing 1, defines an entity object (i.e., Department) for the HumanResources_Department table from the AdventureWorks database, which will also be used across multiple event handlers.

In Callout B in Listing 1, the code defines the Form Load event for the display. Within the Load event in Callout B in Listing 1, the code disables the Add, Edit, and Delete buttons. Because the Load event occurs only once, now is a good time to create an initial query of some data to populate the grid. Rather than use a simple query that’s easy to add, update, and delete, this complex query illustrates more of the format of LINQ queries. LINQ queries differ from SQL queries in that they start with the FROM clause. This clause lets you specify a target in-memory table to hold your query definition. The In portion of the FROM clause lets you identify where in the DataContext object you intend to make your query. Once you’ve defined the context of your query, the LINQ engine can provide IntelliSense for the table(s) and columns that are available in your query.

Continued on page 2

   Prev. page   [1] 2     next page