Starting LINQ Basic CRUD

Posted: Friday, August 15, 2008 1:35:27 PM | 3336 Views


Been talking to a few coworkers about LINQ and how simple it is to do the basic stuff, so figured I'd do a quick step by step on how to use it.

Step 1 - Add your database connection to your server explorer. Nothing fancy here, we're going to work with the Northwind database for our examples.



Step 2 - Add a DBML file to your project. So Right Click on the root of your project > Add > New Item. From here select "Linq to SQL Classes," I'm going to name this one LinqDemo.dbml.



Step 3- Drag and Drop the Tables and Stored Procedures you wish to incorporate into your "DataContext" onto the DBML page. You'll notice if you add items related to each other in the database, the link will be shown in your diagram. In this case I'm going to add the Customer Table, Orders Table, and the GetOrderDetail stored procedure to the DBML file.



So with your tables and stored proc added to your project, Linq To Sql will now generate classes and methods to encapsulate the items you've added from your database. So, now lets get to the CRUD, and see how to use it.

If you highlight the DBML file in your solution explorer and check out the properties you'll notice the file has already created a default name for your DataContext, typically the file name of your DBML file with "DataContext" appended.

As you can see, this is the case with the file we just created:



Also note you can update a bunch of other things such as the Namespace. For this demo we're leaving everything as is.


Create


To start lets add a new Customer to our database.



You'll also note you have full IntelliSense with the strongly typed customer object.



One thing to note, is you could actually add many new customers to the DataContext, but the actual update to the database will not happen until ".SubmitChanges()" is called.

Read


You can read data back in many ways here's a few examples. Note you can use Linq or Lambda expressions to return data.

In this first example we're using Linq syntax to return only one record from the Customer table for a matching ID. The "SingleOrDefault" method ensures that we receive one and only one record back in our query, and if not will throw an exception.



Using Lambda expression we can shorten this query even more:



There are also times where you'll want to return a whole list of records, in this case we can use the "var" keyword and let link sort out the type for us:



Simply use <%#Eval("FieldName")%> within your template, and your data is all set to render.

Updating


Updating is a matter of getting an instance of the object and then saving your DataContext back to the database. In this case, let's update a user, let's use the the example above where we retrieved a customer from the database, and make an update:



Deleting


Finally let's get rid of a customer in our database. To do this, we again get a reference to a LINQ Entity for the customer, then we tell the DataContext we wish to delete this record.


Summary


So as mentioned, this is a basic overview of some of the CRUD operations and LINQ. There's a great deal more than can be done with both Lambda Expression and the LINQ syntax, but this should provide a starting point for those beginning to try out LINQ to SQL.


User Comments

On Thursday, March 11, 2010 at 04:07 krisnamourt said:
Very good, man.
Is cleary and directly.


On Thursday, May 13, 2010 at 08:55 Kavitha said:
Really very good.. It helps a lot.... Thanks much...



Leave a Comment

*Your Name:

*Email (will not be shown):

Website (optional):

*Comments:

NOTE: HTML is stripped from comments.

  zDlzL  
*Enter code above:






(c) (2010 Mark Hansen aka "stonedonkey" ~ Powered By: donkeyblogger