LINQ to SQL : A Comparison
One of my goals, as you see from a prior post, is to tackle LINQ to (SQL/Objects/Stuff) in a practical sense. I love learning new technologies, but I know that if I cannot apply them to my job, then I’ll not retain them.
Recently, a project that I’ve taken on has been a simple record keeping solution. It’s web-based, LDAP authentication driven, and has simple CRUD functionality against a SQL Server 2005 database. Easy enough. The “incidents” are created into an object called… *cough* Incidents.
When I look at it though, that was a LOT of work. So, how about using this as my first adventure into OR/M automation.
Each incident contains 16 fields: a primary key, a few date times and string fields, a few boolean fields, and some big text fields for the bulk of the records. The object is then created in a class called Incident. A snippet of the code is as follows. There’s about 140 lines of code for each public/private set.
Finally, the collection to group the Incidents into a generic List collection.
Right there, that’s a LOT of code. We haven’t addressed CRUD, we’ve simply created our object and it’s collection. So how would we do that in LINQ to SQL? A few mouse clicks.
On your project, Add New Item > Linq to SQL File. Name it approprately. We’ll name this one DCI (our project’s name).
Using Server Explorer, connect to your database and DRAG the tables over that you want to create objects for. In this case, our Incidents table. The designer automatically recognizes that our incidents table contains a row for each incident and names our object accordingly. At this point, we could change it’s name, but we’ll opt just to captialize it.
If we look at the code-behind (Your-dbml-file.designer.cs), you’ll see each of your columns, the approprate LINQ connections, and even a collection turning your Incident into “Incidents”.
Now, the quick “select * from table” example using LINQ. I’ve placed a single Placeholder object on my web form and will create a gridview, populate it, and drop it into the placeholder.
That’s it. Now, what about Inserting and Updating? It’s as easy as .Add. Assume, in reality, these are coming from a user-entered form or other data object… not hand-coding it.
Since our Incidents object is made up of Table<Incident>; it acts as a collection that you can Add, Remove, etc. from. Update works the same way:
Finally, deleting (Removing):
So far, from what I see, this allows me to focus on the application—not simply spending half-a-day creating objects. Very cool indeed. Though, now I ponder… how do technologies such as Enterprise Library’s DAAB fit into this? Why would I use the Factory? How would this work against Oracle (a GREAT deal of our information is located in Oracle Databases)? Hmm.