The query in Callout B in Listing 1 uses a Join statement,
which names a second table and specifies which
columns will be used to join the two tables. The query
also includes a WHERE clause to limit the number
of results returned. The result is the DS object, which
is created as a query object based on type inference.
This query object maintains the underlying query and
provides an enumerator that lets you retrieve each row
of results. The query object is then assigned as the
data source for the datagrid. The resulting grid (shown
in Web Figure 1—www.sqlmag.com, InstantDoc
ID 98205) doesn’t support editing or adding entries
at runtime. Instead you have created the functional
equivalent of a T-SQL statement in which the results
have been assigned to a data set.
Adding Rows to the Grid
Now you can compile and run the application, and it
will execute the query defined in the Load event and
that data is loaded into the datagrid. However, that
query references a table with multiple relationships. To
illustrate the Insert, Update, and Delete operations, I’m
going to use a query that references a table with no relationships. Listing 2 shows the implementation of three
methods key to this process: ButtonLoad_Click, Bind-
Grid, and ButtonAdd_Click. The ButtonLoad_Click
method reloads the grid with a different table (i.e., the
HumanResources_Department table). The BindGrid
method creates the actual LINQ query against the
DataContext object for the entries in the table and
then updates the DataGridView data source with this
new query. The query demonstrates the ORDER BY
clause, which should be familiar to T-SQL developers.
Finally, the ButtonAdd_Click method activates the
Add button, which Web Figure 2 shows.
The Add button calls the ButtonAdd_Click method
in Listing 2 to add a new entry to the current table. This
method leverages the Department entity object created
as part of your form definition in Listing 1. The entity
object is then associated with a newly created instance
of a department as shown on the first line of the ButtonAdd_
Click method in Listing 2. This line of code
leverages one of the new VB syntax elements in that it
assigns values to the properties of an object when the
object is created. The With {.PropertyName = value}
syntax lets you assign values to an object’s properties
when the object is created using the New statement.
This line shows the creation of a new Department
entity that will reflect a row when it’s updated in the
database.
Next, you need to insert the new department entity
into the table. Updating the table is a two-step process:
First, you need to associate the new department object
with your database object’s list of queued insertion
statements by using the InsertOnUpdate method. This
method tells LINQ that the object is to be inserted into
the table, and lets you potentially define multiple new
objects before updating the database. With LINQ, these
and other updates are kept local until you execute the
SubmitChanges method. Second, the SubmitChanges
method tells LINQ to take those cached data updates
and apply the generated T-SQL code associated with
each to your SQL data source. If you want to add a
collection of entities, you can postpone updating the
database until all of the entities are created. Once you
click the Add button, the display is updated, with the
newly created department appearing at the top of the
list, as shown in Web Figure 3.
Updating and Deleting Rows
Now that you’ve inserted a row, let’s look at how you
can update it. Because your entity object was created
from your current DataContext object, all you need to
do is update one of the properties of your entity object
to contain the value you want to place in the database.
In my example, the name of the department will
change from Bike Computers to Fitness Computers.
To update the database, call the SubmitChanges
method on your DataContext object. Web Figure 4 shows how the department name changes from Bike
Computers to Fitness Computers after the Submit-
Changes method has been executed. The new department
name replaces the original department name.
Now only the Delete button is available for use.
In theory, it’s just as easy to delete a row as it is to
update one; however, behind the scenes there’s a challenge.
By default, LINQ uses an optimistic locking
scheme. If LINQ thinks that the data underlying your
object was changed, it will refuse to update that object
and will return an error message saying that the row
can’t be found or changed. This error message will also
be displayed if you edit the same entity more than once
or attempt to delete the entity after editing it.
One solution to this problem is to replace the instance
of your DataContext object with a new DataContext
instance. The first two lines of the ButtonDelete_Click
event handler (shown in Listing 3) recreate your Data-
Context object and associate your in-memory entity
object with the newly created DataContext object.
However, this solution can affect performance because it
requires you to create a new database connection.
Once you have the call to refresh the DataContext
object in place, the code calls the DeleteOnSubmit method to remove the current entity from the table and
then submit changes to actually remove the entity’s row
from the table. After you click the Delete button, you’ll
find that the display looks like Web Figure 2 once again,
and you can repeat the process. Note that the DeleteOn-
Submit method replaces the Remove method, which
was found in earlier versions of LINQ.
How Developers View LINQ
From the standpoint of a developer who is focused on
back-end work and familiar with T-SQL, LINQ is yet
another query interface that has to be mastered but
that doesn’t seem to provide a lot of value. From the
standpoint of a UI developer who is more accustomed
to data binding and data manipulation, LINQ is a
wonderful tool that simplifies data-access work.
I think these two views represent the best evaluation
of LINQ. As a RAD tool, LINQ is a powerful
addition to the .NET developer toolkit. It provides
a discoverable interface that automates the building
of potentially complex queries. However, if you’re a
data professional looking to optimize your data-access
strategy, you’re likely to find that LINQ doesn’t really
affect your daily tasks.
End of Article
Prev. page
1
[2]
next page -->
