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.

I spent a little too long on this rainy Sunday afternoon tripping over myself. I'm using the SqlDataSource and the FormView controls in a quick prototype. I clicked the "Use optimistic concurrency" field in the SqlDataSource configuration page so it would compare the original values before updating the database.

I was going along fine when I realized the updates didn't work, but the insert was OK. After several repeated attempts with zero rows updates, I fired up the SQL Profiler and saw my update query right there, plain as day. Why wasn't the update working?

I even grabbed the SQL out of SQL Profiler and ran it through Query Analyzer - and then bam! I could see it on my screen. The milliseconds for the date time fields were all set to zero, and the actual values in the database were non-zero values. Who was loosing the milliseconds?

Finally, it hit me. I was losing the data.

I was trying to be real smart about setting the hidden "Created" and "Modified" DateTime fields during the updating event of the SqlDataSource control. I would do a similar assignment during the update event for just the "Modified" DateTime field.

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
   DateTime now = DateTime.Now;

   e.Command.Parameters["@Created"].Value = now;
   e.Command.Parameters["@Modified"].Value = now;
}

As I was peering at this method, it struck me that I was inadvertently setting the fields to a value that was too granular for my SqlDataSource object. It wasn't passing back the millisecond value, so I compensated by making sure the value is always zero milliseconds, like so:

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
   DateTime now = DateTime.Now;
   now = now.AddMilliseconds(1000 - now.Millisecond);

   e.Command.Parameters["@Created"].Value = now;
   e.Command.Parameters["@Modified"].Value = now;
}

With this adjustment, my inserts and updates are playing nicely. Yay!

So, now that i works, I'm still not real happy with it. I'd much rather have the code check a single timestamp column named "Version" than see all that bloat in there.

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.