LINQ to SQL in under 15 minutes...

This is the first in what I hope becomes a series of video/blog posts from me.  My goal with this video/post is to provide a 10-15 minute video (or text you can read quickly) that covers LINQ to SQL.  You'll learn how to set up the environment, how to do a select from the database, and finally how to save your changes back to the database.  You will NOT learn everything, but this should be a great starting point (if someone wants to copy this format for a video/post on some other topic, please feel free).  So start your stop watch... 13.5 minutes from now you will be knowledgable in LINQ to SQL.

Video

The video runs in 13.5 minutes (sorry I wanted to do it in 10 minutes or less but I couldn't without removing content or sacrificing the demo... the first title for the post was "LINQ to SQL in 5 minutes" that ended up being too ambitious).  Also this is my first demo so please forgive the slight clicking noise (I will get better at this)...

Direct link to the video

Background

Everyone has their pet project that they try to build with a new technology.  My pet project happens to be a lightweight content manager.  For this demo/post we will be building simple lightweight content control that is given a pagename and a contentkey and will retrieve the appropriate html from the database and will place this on the screen.  It will also provide a logged in user with the admin role access to an editor (in this case a text area) where this content can be edited and updated in the database.  It will do an insert if no content exists yet (that way the programmer can add content controls as needed).  Caching of content will be seen but not described in the demo.  Also, the database is a variation of one that I pretty much always use, so not much will be explained about it's creation... the focus afterall is LINQ to SQL.

Step 1 - Create LINQ to SQL Classes (map)

First step is to create LINQ to SQL Classes DBML file.  If you are creating this directly in the project as I am you'll want to add this to App_Code (or if you are using Web Projects you can create it anywhere -- I think...). 

LINQ to SQL Classes (add item) dialog

The dialog for adding a LINQ to SQL Classes item

DBML designer

First of all sorry that this is image is so large (but I wanted you to be able to read it).  Essentially all you need to do drag tables from the server explorer over to the tables area of the dbml (LINQ to SQL Classes) designer.  There's also an area for procedres, but we will not look at that right now. Our surface has 4 tables: cms_Content, cms_ContentType, cms_ContentPage, and cms_Site.  Site will contain the various sites (so 1 database can house the data for multiple sites), ContentPage contains the pages names we will be using, ContentType contains the types of content we will support (right now the only content type is HTML), and finally Content contains the actual content.

We will spend much of our time in the next section where we will retrieve content and will store that content in the database.

Step 2 - Creating a ContentSupport class

Next step is to go into App_Code (or anywhere if you are using a Web Project), and create a new class called ContentSupport.  We'll be creating a class that can read/write the content. Initial code will look like this:

   1: using System;
   2: using System.Data;
   3: using System.Configuration;
   4: using System.Linq;
   5: using System.Web.UI;
   6: using System.Data;
   7: using System.Data.Linq;
   8:  
   9: public class ContentSupport
  10: {
  11:     private string SiteName { get; set; }
  12:     public string PageName { get; set; }
  13:     public string ContentKey { get; set; }
  14:     private Page page;
  15:  
  16:     public ContentSupport(String SiteName, String PageName, String ContentKey, Page page)
  17:     {
  18:         this.SiteName = SiteName;
  19:         this.PageName = PageName;
  20:         this.ContentKey = ContentKey;
  21:         this.page = page;
  22:     }
  23:     
  24:     public string GetHTMLContent()
  25:     {
  26:     }
  27:  
  28:     public void SaveHtmlText(string ContentValue)
  29:     {
  30:     }
  31: }

I went ahead and included all the Namespaces we will need.  Beyond that we have a couple properties (the key information that we will need to query the database for our content).  The consturctor let's us pass this information in and also a reference to the page.  This is so we can get to the page's Cache object (which we aren't explaining in detail).

We also have the methods for retrieving the HTML and saving it back although we haven't written these methods yet.

Step back

Before we continue I need to step back for a second and explain something that we gained when we created that DBML file (the LINQ to SQL Classes).   We created a whole bunch of new types.  The most important of which is a derivative DataContext class.  In my project this class is called "CMSDataContext."  This class encapsulates all our communications with SQL Server.  It's similar to the Database object in Enterprise Library except that it does more.  One of the things it gives is Get methods that will retrieve data from a table (well at least one of the tables we dragged over).  If we have a CMSDataContext instance we can simply call "instanceOfCMSDataContext.GetTable<cms_ContentPage>()" this gives an instance of a Table<cms_ContentPage>.  This generic provides a way that we can interact with the table via LINQ and when the query is executed then the database will actually be contacted (LINQ queries don't actually execute until the results are used).  So this DataContext class is extremely important for our use.  The next thing we want to do then is to create a way to get the CMSDataContext. So Let's add a property to manage this for us.

   1: private CMSDataContext _ctx = null;
   2: private CMSDataContext ctx
   3: {
   4:     get
   5:     {
   6:         if (_ctx == null)
   7:             _ctx = new CMSDataContext(ConfigurationManager.ConnectionStrings["IntraD"].ConnectionString);
   8:         return _ctx;
   9:     }
  10: }

Our derived DataContext has a way to provide a connectionstring (which means we can easily swap in a different ConnectionString if need be).

Reading data (simple)

In the save routine I need to retrieve the ID for the HTML content (so I can use it for inserting/updating data).  Here's the important code:

   1: Table<cms_ContentType> ContentTypes = ctx.GetTable<cms_ContentType>();
   2:  
   3: int? contentTypeID = (from ContentType in ContentTypes
   4:                       where ContentType.ContentTypeDescription == "HTML"
   5:                       select ContentType.ContentTypeID).FirstOrDefault() as int?;

As you can see I am using the DataContext to get the cms_ContentType table int a variable called ContentTypes.  I could have skipped this step and simply used the ctx GetTable right in the LINQ query.  The LINQ just seems easier to read to me this way.  If you have done much LINQ the query should seem fairly simple to you.  Essentially gets the ContentTypeID from the record in the cms_ContentType table that has a description of "HTML."  We call FirstOrDefault() to return the first value (believe it or not this will return an array.. I should have probably used one of the other Select methods, but I wanted to show off some of the tools you have at your disposal).  The value is then cast to a nullable int and giving us the first int that gets returned (we should really only receive one, but that's because of our table structures).

Writing data (insert)

Writing data back to the database is a little trickier, but not that tricky.  We do need to handle things differently for inserts versus updates.  Let's look at the insert code first.

   1: ctx.cms_Contents.InsertOnSubmit(new cms_Content()
   2:                {
   3:                    PageID = pageID.Value,
   4:                    ContentKey = this.ContentKey,
   5:                    ContentTypeID = contentTypeID.Value,
   6:                    ContentValue = ContentValue
   7:                });
   8: ctx.SubmitChanges();

The insert takes advantage of the DataContext (again).  Actually an instance of the derived/specialized CMSDataContext contains a cms_Content object that contains some methods for adding a new object, and will insert when the DataContext is submitted.  This creates a cache of changes that will all be submitted at once (this lets you make a couple changes and then submit the results to the database).

Writing data (update)

Writing data back is equally easy.  Here's the code:

   1: cms_Content updatedRow = ctx.cms_Contents.Single(c => c.ContentID == contentID);
   2: updatedRow.ContentValue = ContentValue;
   3: ctx.SubmitChanges();

We simply retrieve a row using the same cms_Content object that is a property of our data context, set the valuefields we need to update and then call ctx.SubmitChanges to save the data back to the database.  BTW, that call to cms_Content.Single call retrieves a single row from the database using the Lambda expression.

Read Data (complex)

If you paid attention you probably have realized that a we need to query more than just the cms_Content table.  We have the SiteName which is really the SiteName field in the cms_Site table, the PageName which is really the PageName field in the cms_Page, and the ContentKey which is in the cms_Content table.  cms_Content has a PageID and a SiteID.  It would also be nice to factor in that we only want to handle HTML content.  Well, the good news is that joins are possible with LINQ (and LINQ to SQL).  Here's what it looks like:

   1: Table<cms_Site> Sites = ctx.GetTable<cms_Site>();
   2: Table<cms_ContentPage> ContentPages = ctx.GetTable<cms_ContentPage>();
   3: Table<cms_Content> PageContents = ctx.GetTable<cms_Content>();
   4: Table<cms_ContentType> ContentTypes = ctx.GetTable<cms_ContentType>();
   5:  
   6: var ContentObject = (from Site in Sites
   7:                      join ContentPage in ContentPages
   8:                        on Site.SiteID equals ContentPage.SiteID
   9:                      join HtmlContent in PageContents
  10:                        on ContentPage.PageID equals HtmlContent.PageID
  11:                      join ContentType in ContentTypes
  12:                        on HtmlContent.ContentTypeID equals ContentType.ContentTypeID
  13:                      where
  14:                        Site.SiteName == SiteName &&
  15:                        ContentPage.PageName == PageName &&
  16:                        HtmlContent.ContentKey == ContentKey &&
  17:                        ContentType.ContentTypeDescription == "HTML"
  18:                      select new { HtmlContent.ContentValue, HtmlContent.ContentID });

As you can see this query is a little more complex, but not so much so if you are familiar with SQL.  We're joining 4 tables together.  Primarily we are doing this via the table's ID column.  We then use each of the parameters (PageName, SiteName, and ContentKey) in the where clause.  Finally we are returning an anonymous type that contains the HTML content and the ContentID (we use the latter for caching, so we don't have to run this query often).

I have attached the code to this post.  The only change you may want to make is that the code should auto add page's if the page name is not found (making life a little more convenient for the developer).

[Since LINQ to SQL was the focus here I'll leave the User Control code for you to take a look at on your own.  One thing to note.  The attached project has example controls that have as an editor: a TextArea, FCK Editor v2.5 control, FreeTextBox v3 control, and an OpenWyswyg control... Eventually I'll publish a FreeTextBox4 version (and other free controls).]

Attached File: DTCMS.zip

Print | posted on Wednesday, January 23, 2008 10:43 PM

Feedback

# re: LINQ to SQL in under 15 minutes...

left by Kevin Dorsett at 1/24/2008 9:51 AM Gravatar

The video was very interesting and needed for those begining to use LINQ for the first time.  Thank you for putting it together.  Only one comment though, I had a real difficult seeing what you were doing, because the video screen was so small.  Would it be possible to get a bigger view on the next video.  Thanks for putting this together.

# re: LINQ to SQL in under 15 minutes...

left by jkimble at 1/24/2008 10:25 AM Gravatar

Kevin,

I'm making a couple changes to this to make it a little bigger, and will try to render the next one a little better...

Jay

# re: LINQ to SQL in under 15 minutes...

left by jkimble at 1/24/2008 10:33 AM Gravatar

Sorry this is so small... It's my first demo...

I do highlight the code that is in this document...

I will see if I can find a link on Soapbox direct to the video

# re: LINQ to SQL in under 15 minutes...

left by Brian Finnerty at 1/24/2008 1:24 PM Gravatar

Nice job on the LINQ to SQL video - no doubt you'll iron out the quality kinks in time. Here's a link to some free VS 2008 training from InnerWorkings and Microsoft - it covers VB 9, C# 3.0, and LINQ to SQL.

www.innerworkings.com/.../visual-studio-2008-promotion

# re: LINQ to SQL in under 15 minutes...

left by jkimble at 1/24/2008 10:03 PM Gravatar

If you actually tried to use this code on a real site there is a small problem that I thought I should mention.  The CMS control will treat a URL like "www.theruntime.com" and "theruntime.com" as 2 separate locations.  You probably don't want this type of functionality.

The way to fix this is to open the BaseContentControl in App_Code and change the code on line 24... it should look like this -

_SiteName = Request.ServerVariables["Server_Name"];

it should look like this -

_SiteName = Request.ServerVariables["Server_Name"].Replace("www.", "");

Actually you may want to convert that to lower case first (but you get the idea)

Jay

# re: LINQ to SQL in under 15 minutes...

left by Srikanth at 7/31/2008 6:09 AM Gravatar
HI,

Actually i was very eager to watch your video but i was unable to wacth it, can u send the video t o my mail id as mentioned.

it would be greatful.

thanks.

# re: LINQ to SQL in under 15 minutes...

left by Chris DuFour at 6/30/2009 4:18 PM Gravatar
Nice video, good work. A couple of your images in the middle of your article are not available.
Thanks

# re: LINQ to SQL in under 15 minutes...

left by Jeremy at 7/22/2009 2:40 PM Gravatar
The links for your image and the zip seem to be broken, FYI.

# re: LINQ to SQL in under 15 minutes...

left by vinay at 10/13/2009 11:47 PM Gravatar
video no more available,images not available
Comments have been closed on this topic.