Behind the scenes, I see a lot of companies run on Excel and duct tape. So it's common for clients to hand me a slab of data organized into neat little rows and columns. Excel is just a handy way to throw some data over the wall and get things done. This might be a list of dealers, a collection of user profiles, product information, or anything that just needs to get somewhere else.

Depending on the scenario, I'll might massage this data and slide it into a SQL Server database or an XML file. I'm a web developer so I use the ADO.Net stack on a regular basis. If I were a Windows client developer, I might prefer to solve this problem with the Excel object model, but that really looks like more code to me, so here's how I like to roll:

The following code block accepts an Excel file path and returns an ordinary DataTable object, which can be manipulated easily by the code that calls this method. The first row of the Excel document becomes the columns in the DataTable object and each row thereafter are DataRow objects.

(Click to enlarge)

If you like, take a look at both techniques for working with Excel data and see what one speaks to you. With this block of code, I can happily accept large chunks of data from a client without spending precious time fiddling with administrivia.

LINQ is an acronym for Language-Integrated Query and a new feature in v3.5 of the .Net Framework from Microsoft. This new version of Microsoft .Net reached RTM status a couple of weeks ago — this framework is chock full of brilliant things I can use to improve my efficiency and effectiveness on daily tasks here at Pop Art.

As one of my favorite features, LINQ helps me write data-driven application even faster than what .Net 2.0 brought to the table. A common example starts with launching Visual Studio 2008. After dragging a collection of tables from a SQL Server database onto a surface in my solution, I can see a visualization of the columns in the tables as well as the relationships between them. Click the following thumbnail to see a larger image.

Next, I start writing data access code directly in my C# program as opposed to switching languages and writing in the T-SQL language. Visual Studio gives me Intellisense here too; as I type the name of a table and click the period key, all of the columns in the table appear where the cursor is located. Big time saver. Huge! I'm certain that I didn't misspell a column name and that my code will compile.

The syntax for LINQ in a C# program is very similar to the T-SQL language, which is a "set based" language. LINQ statements are compiled, just like the rest of my C# code. The first thing someone well versed in T-SQL will notice is that the columns normally specified in a SELECT query are at the end of the LINQ statement instead of the start as in T-SQL. The idea is that you're articulating your constraints at the start of the LINQ statement and then pulling out the fields you need at the very end.

Behind the scenes, LINQ is using the relationships expressed in the database to generate T-SQL scripts on the fly. This is a clear line of demarcation for LINQ. If you're using stored procedures exclusively for database access, then LINQ isn't going to buy you much. You'll still get Intellisense inside Visual Studio and you can specify an existing stored procedure instead of using the auto-generated SQL, but you're giving up a lot of acceleration tools. Perhaps more than you're getting in return.

LINQ really shines in multiple table joins and aggregation. The following two blocks of code show a query is performed in LINQ and its equivalent T-SQL script. The query retrieves product information from three tables where the list price is below a given amount and a sub-category name exists. If you go the T-SQL route, you will still need to write some C# code to call your database query.

Which of the following versions would you rather author and support?

LINQ Version

T-SQL Version

MSDN is a great online resource for developers and they really hit a home-run here. They have a page with 101 LINQ samples. This is my preferred way to learn when I already know the surrounding technologies and I want to fill in a specific gap. The page categorizes several ways of retrieving and iterating over information.

Like many of the three of you who subscribe my blog, I download a bunch of tools & utilities that I read about online and seldom have an opportunity to use on a real project.

Today was my first bona fide use of the Snippet Compiler and it just plain rocked. Its a small client application that can take the place of many throw-away command line programs written just to test out a concept. The application launches fast and I can start writing and executing my code immediately. I don't need to select a project template, name it, or any other of the standard housekeeping items. It even has statement completion!

I was working on an existing ASP.Net v1.1 code base for a quick maintenance project. I gleaned a test order number from the system and quickly realized my specific test required an obfuscated order number from the following "simple" and "natural" function:

I was doing integration testing and further more, I was nearly done. I didn't feel like firing up VS.Net to figure out how to get my test order number obfuscated, so I thought about it and decided to have a spontaneous moment.

I (1) fired up the Snippet Compiler, (2) added a reference to the assembly and (3) wrote a single line of code that called the static method DecodeOrderId() which wrote the result to the console output. Booya!

The PDX Code Camp is next weekend, May 19th and 20th. I've been preparing a talk on how to create and use X.509 certificates. Developers need this technology for local testing of plain old ASP.Net sites, Web Services Enhancements (WSE) or Windows Communication Foundation (WCF) code.

The Windows SDK and Visual Studio.Net have some good tools for helping developers use certificates. I'll show some certificate basics, common examples of certs in action and tools that help us along the way. My goal is to get the session attendees comfortable with creating & installing certificates on their local machine in a variety of code scenarios - that seems like a reasonable task for a 60 minute presentation and 15 minutes of Q & A.

Just for fun, I worked on a local checkout of DotNetOpenID and implemented SSL for the authentication steps. A lot of the other code in the presentation is based on the excellent examples from Michele Leroux Bustamante. She does a great job of providing info on these topics for the developer community.

I have to leave for New London, CT on Sunday so I can only attend one day of this developer event. Normally, that would suck big time, but I'm also gearing up for a week long IDesign WCF Master Class at Carl Franklins house. When it rains, it pours!

There are tons of blog entries like this one explaining how to get intellisense for WPF/e using the February 2007 CTP. The SDK for that release has a file named wpfe.xsd and you drop it into your VS.Net schema folder here:

C:\Program Files\Microsoft Visual Studio 8\Xml\Schemas

I'm using the Silverlight 1.1 alpha bits, and the 1.1 SDK doesn't have a file named wpfe.xsd, so I was baffled for a minute. The Silverlight 1.1 SDK is laid out quite differently and comes in a handy zip file instead of a chunky MSI file like its predecessor.

Perhaps its just too early on a Sunday morning, but it took me a few minutes to realize that (A) the 1.1 SDK does have a file named Silverlight.xsd and (2) dropping Silverlight.xsd into my VS.Net schema folder does the trick. I'm going back to bed.

I was chatting with my pal Kelly at work last week about code snippets. This weekend, I had a pile of dirty dishes to clean, so I fired up my laptop and downloaded the archived DotNetRocks interview with Michael Palermo on code snippets. Before I knew it, the dishes were clean and I had refresher on code snippets!

I took a look at Palermo's site, www.gotcodesnippets.com. I was looking for some snippets I'd could install. I downloaded one that creates a property whose value is stored in the ASP.Net viewstate. I do that technique quite a bit, so it'll be fun to hit ctrl+k+x to run that snippet.

Next, I was interested in writing one by myself, just to see what it was like. I had downloaded the ternary code snippet, but I wasn't to warm and fuzzy about it. The snippet ought to have given me the opportunity to type in the variables using the special code snippet mode before reverting back to standard mode in Visual Studio. So, I grabbed their code, made a few changes and now creates a line of code with a ternary operator in it - just how I like. Here' the snippet that I dropped into my snippet folder.

Folder: \My Documents\Visual Studio 2005\Code Snippets\Visual C#\My Code Snippets

ternary.snippet XML File:

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
   <CodeSnippet Format="1.0.0">
      <Header>
         <Title>Ternary</Title>
         <Shortcut>ter</Shortcut>
         <Description>Custom code snippet for ternary operator</Description>
         <Author>andrewdothay</Author>
         <SnippetTypes>
            <SnippetType>Expansion</SnippetType>
         </SnippetTypes>
      </Header>
      <Snippet>
         <Declarations>
             <Literal>
                 <ID>result</ID>
                 <ToolTip>Replace with the field or property that will recieve the value</ToolTip>
                 <Default>result</Default>
             </Literal>
             <Literal>
                 <ID>expression</ID>
                 <ToolTip>Replace with the expression to compare</ToolTip>
                 <Default>expression</Default>
             </Literal>
             <Literal>
                 <ID>trueValue</ID>
                 <ToolTip>Replace with the value if the expression is true</ToolTip>
                 <Default>trueValue</Default>
             </Literal>
             <Literal>
                 <ID>falseValue</ID>
                 <ToolTip>Replace with the value if the expression is false</ToolTip>
                 <Default>falseValue</Default>
             </Literal>
         </Declarations>
         <Code Language="csharp">
            <![CDATA[ $result$ = ( $expression$ ) ? $trueValue$ : $falseValue$;$end$]]>
         </Code>
      </Snippet>
   </CodeSnippet>
</CodeSnippets>

This snippet uses four variables. The <Code> element contains placeholders for the line that will be emitted by the code snippet. The "$" symbol surrounds the variable so its easily identifiable by the snippet engine. When Visual Studio is in the special mode, I can tab between the variable fields to enter the value, then hit tab+tab to switch back to normal view after I've entered in the customizations. Sweet!

>>>>>>>>>>>>>>>>

I just found this four minute screencast on Channel 9 too! She does an excellent job of showing one up close.