Then, the GetTable method creates a table variable that a DLinq query expression can use. In this example, the code creates the Department variable for the Department table. Next, the new var keyword creates a variable named qry. The var keyword is different from a standard declaration because var doesn't directly create a given type. Instead, it instructs the compiler to determine the variable type. You can see the actual query operators in the assignment to the qry variable.
Unlike in standard ADO.NET, you don't need to specify the SELECT statement in a text string that's passed to a command object. Instead, the query operators are integrated directly into the .NET language. The query must begin with the from clause, which is a generator for an iterator variable that the application uses over the specified table object. The query can have optional where, orderby, ascending, and descending clauses to filter and sort the results. Next, the foreach statement iterates over the content of the result set, adding the values of the DepartmentID and Name columns to a ListBox.
When the foreach statement executes, the DLinq code accesses SQL Server and begins returning results to the application. Figure 2 shows the result of the simple DLinq query.
DLinq can do the same type of joins, selections, and ordering of result sets that TSQL can. The example in Listing 4 illustrates how to use DLinq to join the Employee and Contacts tables. The DataContext object creates the connection to the SQL Server AdventureWorks database. Then, the Get-Table method creates variables for the Employee and Contacts tables. Next, the code sets up the qry variable with a join query. In this query, the from clause specifies the two tables, separated by a comma, that will be joined. Just as in T-SQL, you can assign a shorthand alias to each table. The code will reference the Employee table as e and the Contact table as c.The where clause specifies the join condition. The code will join the Employee and Contact tables by using the ContactID column from each table.
The select new clause shapes the results that will be returned. This example returns the EmployeeID column from the Employee table and the values from the FirstName and LastName columns from the Contact table.The foreach statement iterates over the results, adding the values to a ListBox. Figure 3 shows the results of the DLinq join query.
Insert/Update/Delete
In addition to performing queries, DLinq can also insert, update, and delete data from the target database. As you saw in the previous query examples, you don't have to change programming paradigms or drop back into T-SQL.All the update actions are fully object oriented.
As Listing 5 shows, adding rows to a table is a straightforward process. Creating the connection and the variable for the Department table is the same as the process you saw in the previous query listings. To add a new row a new instance of the Department table, you create an object named newDept by using the new keyword. Then, you assign values to the newDept object's properties. Remember that each property represents a column in the target table. In this example, the Name column is set to "New Department," the GroupName column is set to "Research and Development," and the ModifiedDate column is set to the current date and time. After you assign the values, you use the Add method to add the row to the Department table object. This action updates the object but not the database.The HumanResources.Department table isn't updated until the SubmitChanges method executes. The reminder of the code in the listing queries the table to retrieve the new values, as you saw in the previous examples. Figure 4 shows the new row that the code added to the HumanResource.Department table.
The DLinq code that you need to update a row is a bit different.The code in Listing 6 retrieves the newly added row and changes the value of the Name column from "New Department" to "Updated Department". The code to connect to the database and create the table variable is the same as in the previous examples. The biggest change in this example is the use of the First() method in the DLinq query. The First() method returns one object instead of a collection of objects.
The update query that Listing 6 shows retrieves the row in which the value of the Department table's Name column is "New Department." After the singleton object is returned, you can update the object's value by using a standard assignment operator, which will result in the execution of the column object's Set method. You can then update the target database table by using the Submit-Changes method. The reminder of the code in Listing 6 queries the table again to return the updated values.
The DLinq code for deleting a row is similar to the previous Listing's update code, as Listing 7 shows. After creating a connection and the database table variable, a DLinq query that use the First() method returns one object representing the row that will be deleted. This query goes on to retrieve the row from the Department table in which the value of the Name column is "Updated Department. "To actually delete the row, you use the Department object's Remove() method.
Up LINQ
LINQ is quite possibility the biggest paradigm change for database developers since the advent of ODBC. LINQ eliminates the language-database disconnect and enables object-oriented database access through SQL-like extensions to VB or C# languages. Although it's not supported in the early release code, one of the natural uses for DLinq (and one that I expect will be supported in the final release) is writing SQLCLR objects. Considering that LINQ is still in its early stages, it might be a while before you have to start worrying about converting your applications to use LINQ, but there's little doubt that LINQ and DLinq represent the future of database development.
Michael Otey (mikeo@teca.com), technical director for SQL Server Magazine, is president of TECA, a software-development and consulting company in Portland, Oregon, and coauthor of The SQL Server 2005 Developer's Guide (Osborne/McGraw-Hill).
End of Article
Prev. page
1
[2]
next page -->
