Modern .NET Development and The Joy of Simple LINQ to SQL

Introduction – Modern .NET Development

It took me a while to realize it, but Microsoft .NET application development has significantly evolved in the past year and a half. This is not your dad’s .NET. The following releases have added many considerable advances in .NET application development technologies:

                                               

Microsoft Product

Release Date

Major Features

.NET Framework version 3.0

November 11th, 2006

WPF, WCF

Silverlight version 1.0

September 5th, 2007

XAML rendering for web applications, JavaScript API

.NET Framework version 3.5

November 19th, 2007

Language integrated query (LINQ)

Visual Studio 2008

November 19th, 2007

IDE for all things .NET

Silverlight version 2.0

Beta 1 on March 5, 2008, RTM late summer 2008

Managed CLR, C#, .NET Framework, greater parity with WPF XAML

Expression Blend

v1 May 2007, v2 beta March 2008

XAML user experience (UX) IDE

 

If you fancy yourself an adept .NET developer, you had better be ramped up on these new technologies.

Today, if you are considering user interface/experience development, you should consider leveraging the rich UX elements and platform independence offered by Silverlight, and the power of authoring with the Expression Studio. If you are designing some middleware, you should consider developing software services using Windows Communication Foundation (WCF) technology. For coding backend data access it is now wise to evaluate LINQ to Entities or LINQ to SQL techniques. And before you write any C# foreach loop on an array or other collection, you might be able to write it better using LINQ to Objects techniques instead.

The bottom line here is that .NET developers have many new tools available to leverage for developing just about any application. Using these new techniques should eventually accelerate the delivery of applications that look better and are better-built. I say “eventually” because these new tricks will take some ramp up time to learn and master. And first, the developer needs to be aware of them and know how to judiciously leverage them.

However, we are talking about a pretty big load of new stuff to digest here. So let us discuss just one example of one these new tricks: LINQ to SQL. The primary purpose of rest of this article is to illustrate one simple example application of LINQ to SQL.

 

Simple LINQ to SQL

A couple of months ago, I was tasked with developing an internal application that would periodically transfer some data from a SQL database into a monitoring system. The SQL database was a Hewlett Packard Systems Insight Manager installation. The monitoring system is a Microsoft Systems Center Operations Manager (SCOM) 2007 installation, accessed via the System Center Operations Manager 2007 SDK, and the Microsoft.EnterpriseManagement Namespace.

LINQ to SQL is well-suited for this situation as it helps avoid changing either the source or destination installations, and with a minimal “footprint” of moving parts. With a single stand-alone console application, I was able to get this job done, and with a not-too-kludgey implementation. I developed a single, modest-length class file to do the whole thing, with no hard-coded transact SQL code!

I won’t drag you through my entire solution. I pared down the code to illustrate just the LINQ to SQL mechanics, as run against that Hewlett Packard Systems Insight Manager installation:

using System;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Data.Linq;

namespace LinqLite

{

    class Program

    {

        static void Main(string[] args)

        {

            DataContext db = new DataContext("YOUR HPSIM SQL SERVER CONNECTION STRING GOES HERE");

            Table<CIM_Chassis> TableCIM_Chassis = db.GetTable<CIM_Chassis>();

            Table<Devices> TableDevices = db.GetTable<Devices>();

            Table<Notices> TableNotices = db.GetTable<Notices>();

            var LinqQuery = from c in TableCIM_Chassis

                            join d in TableDevices on c.NodeID equals d.DeviceKey

                            join n in TableNotices on d.DeviceKey equals n.DeviceKey

                            where n.NoticeId > 35000 //TODO: make this a LastIdProcessed variable

                            orderby n.NoticeId

                            select new { n.NoticeId, n.NoticeSeverity, n.Generated, n.Comments, d.DeviceKey, d.ProductName, d.Name };

            foreach (var qdata in LinqQuery)

                Console.WriteLine(string.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}"

                    , qdata.NoticeId

                    , qdata.NoticeSeverity

                    , qdata.Generated

                    , qdata.Comments

                    , qdata.DeviceKey

                    , qdata.ProductName

                    , qdata.Name

                ));

            Console.ReadLine();

        }

    }

    [Table]

    class CIM_Chassis

    {

        [Column] public long NodeID = 0;

    }

    [Table]

    class Devices

    {

        [Column] public int DeviceKey = 0;

        [Column] public string ProductName = string.Empty;

        [Column] public string Name = string.Empty;

    }

    [Table]

    class Notices

    {

        [Column] public int NoticeId = 0;

        [Column] public int NoticeSeverity = 0;

        [Column] public long Generated = 0;

        [Column] public string Comments = string.Empty;

        [Column] public int DeviceKey = 0;

    }

}

 

I call this solution simple LINQ to SQL primarily because it is all done in one code file, and does not involve using the fancy Visual Studio 2008 Object Relational Designer (O/R Designer). When you use the O/R Designer, you get a handy GUI for assembling some classes that map to the SQL data. You can easily end up with several auto-generated class files with many code stubs, just in case you might need them. You use these classes in your LINQ queries to access the data. But if you are doing simple read-only selections, you can hand-code the classes, make them lean, and place them in-line in the code, like I did:

    [Table]

    class CIM_Chassis

    {

        [Column] public long NodeID = 0;

    }

    [Table]

    class Devices

    {

        [Column] public int DeviceKey = 0;

        [Column] public string ProductName = string.Empty;

        [Column] public string Name = string.Empty;

    }

    [Table]

    class Notices

    {

        [Column] public int NoticeId = 0;

        [Column] public int NoticeSeverity = 0;

        [Column] public long Generated = 0;

        [Column] public string Comments = string.Empty;

        [Column] public int DeviceKey = 0;

    }

 

These class definitions only list the fields that I am interested in, and not the entire tables. Another simple part of my example is the brief “using” list:

using System;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Data.Linq;

 

Hopefully my core LINQ selection code is not too hard to decipher – seven fields selected from two joined tables, with a third table joined in to filter the data to only notices for “chassis” devices. First, a DataContext is created to set up a connection the SQL Server database. Next, references are made to the table classes using DataContext.GetTable(TEntity) Generic Method:

            Table<CIM_Chassis> TableCIM_Chassis = db.GetTable<CIM_Chassis>();

            Table<Devices> TableDevices = db.GetTable<Devices>();

            Table<Notices> TableNotices = db.GetTable<Notices>();

 

Then the LINQ query is defined, complete with joins, a where clause, and orderby:

            var LinqQuery = from c in TableCIM_Chassis

                            join d in TableDevices on c.NodeID equals d.DeviceKey

                            join n in TableNotices on d.DeviceKey equals n.DeviceKey

                            where n.NoticeId > 35000 //TODO: make this a LastIdProcessed variable

                            orderby n.NoticeId

                            select new { n.NoticeId, n.NoticeSeverity, n.Generated, n.Comments, d.DeviceKey, d.ProductName, d.Name };

 

I put the “TODO” comment on the where clause to indicate that a LastIdProcessed variable could be persisted and recalled to use for filtering the notices data. Thus, you might fetch only new notices that have occurred after the last time this process was executed.

Finally, a foreach construct is employed to “harvest” and display the selected data:

            foreach (var qdata in LinqQuery)

                Console.WriteLine(string.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}"

                    , qdata.NoticeId

                    , qdata.NoticeSeverity

                    , qdata.Generated

                    , qdata.Comments

                    , qdata.DeviceKey

                    , qdata.ProductName

         , qdata.Name

                ));

 

The Console.ReadLine() at the very end merely serves as a way to pause program execution, so the displayed data can be viewed before the console window disappears.

MSDN’s “LINQ to SQL: .NET Language-Integrated Query for Relational Data” article does well to explain more about how this code works.

 

Conclusion

I hope this article has helped demystify LINQ for you, and has maybe inspired you to consider using this powerful .NET feature. I have a lot yet to learn about LINQ and the rest of those modern .NET technologies previously listed in that first table. But, I feel it is ripe time to dig into these things to advance the craft.