Querying a Database with LINQ to SQL Using Visual Basic 2010

Introduction

Vanity is as ill at ease under indifference as tenderness is under a love which it cannot return.

–George Eliot

“Mirror, mirror on the wall, who is the smartest of us all?” “While you Paul are smart tis true, there are millions of guys smarter than you.”

Crap, now what?

The mirror of vanity perhaps is a compelling story because the mirror lies. Vanity is a lie. Vanity lets us focus on a couple of traits and focus on perfecting them and comparing them to others. If the mirror is kind then what? There is no real prize simply because Western society values beauty, power, wealth, and intelligence (in that order) if one happens to win the mirror lottery. If the mirror says “you are the smartest” does this mean there is nothing to be learned from others? It is such a silly notion that when stated so matter of factly it sounds ludicrous. If one stares into the mirror long enough one may get the desired answer to the question being asked; it doesn’t mean it is the right question or even a meaningful one.

The opposite of vanity is humility. Where vanity metaphorically causes us to look at our own reflection and admire it, humility has us looking outward at the reflection of others. Emerson wrote “every man I meet in some way is my superior”. In a broader sense I think what Emerson meant is that each of us is given a set of gifts in varying degrees-looks, intelligence, health, wealth, luck, humor, joy–and focusing intently on the one or two in which we are more fortunate than some others separates us from humanity. That is the lie.

Humility is an acknowledgement that each individual is the sum of all of the gifts in their varying degrees and each person is superior to the next in some way. I try to incorporate some humility in these articles. I am sure some of you may be able to write better than I do. I am sure some of you may be able to improve upon the code samples, and for some this article will contain information you have known about for some time. I accept that. If you respond with refinements to the prose or code then each reader is a benefactor. If something new to any particular reader is conveyed then that person benefits. With humility a community of shared information is created and nurtured; with vanity nothing much really happens.

I get a little philosophical as I get older. Chemically, it may be an increase in estrogen, but that’s not important. What is important is that this article covers three pretty specific aspects of LINQ to SQL: retrieving data, updating changes, and managing concurrency conflicts. If you have these subjects mastered then I invite you to read and critique so that others may benefit. If these subjects are new to you then enjoy.

Defining a Typed DataContext and an Entity

LINQ to SQL is dead. Long live the Entity Framework. (April Fools!). Usually when something is dead it is physically taken away, sealed in a container, and sent to the Smithsonian. (If you don’t know, the short story is that the Entity Framework is another implementation of LINQ for managing data.) LINQ to SQL is one of several implementations on top of LINQ, and I have no problem using LINQ to SQL whenever the mood strikes. While it may be true that in the resource game, more resources may be given to the Entity Framework right now, one could easily make the argument that LINQ to SQL is a baked cookie for now and it needs fewer resources.

The central object in LINQ to SQL is the DataContext. The DataContext is sort of like an C

To create the demo based on the Northwind database follow these steps:


  1. Starting with a Console Application add a LINQ to SQL item from the Add New Items dialog (see Figure 1)
  2. Expand the Server Explorer and drag a table onto the DBML designer-see Figure 2. (The Northwind Customers table was used for the demo, but any table will do as long as you change the referenced items in the code.)



Figure 1: Add a LINQ to SQL item from the Add New Items dialog.



Figure 2: Drag at least one table onto the DBML design surface in Visual Studio

That’s it. You are ready to start coding.

Based on the steps above your project will now have a file named Customers.designer.vb. This is generated code, so leave it alone. It does contain partial classes so if you want to add custom code then create a new file and define the other part of the partial class, which will prevent the IDE from cuisinarting your custom code if you update the DBML.

The designer.vb code will contain a subclass that inherits from DataContext. Think of this as your central management point. It will also contain an entity class for each of the tables dropped onto the DBML designer. From here things get real easy.

More by Author

Must Read