Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

DataTable, DataView and CreateDataReader

Here's something I was not aware of in .NET 2.0+ that I recently discovered. There is a new DataTableReader class that implements IDataReader so that you can loop through an in-memory DataTable using the same interface that you would to loop through a SqlDataReader. Read more →

Splitting a single DataTable into Parent/Child DataTables for Hierarchical Processing (e.g., nested ASP.NET Repeater controls)

In ASP.NET, we often would like to output "grouped" data on our web pages, like this: Customer Product Sales——– ———- —–ABC FoundationProduct 1 $200Product 2 $437Product 3 $523The XLZ CompanyProduct 1 $240Product 2 $892Product 3 $395 The easiest way to do this is with nested Repeater controls; one for the outer group (Customers, this case), and within that Repeater's ItemTemplate we'd have another Repeater control for the details (Products). Read more →

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

Introduction A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating multiple values into single CSV string columns. For example, taking data like this: FarmerName FruitName  ——————– ———- Farmer Ted Apple Farmer Ted Orange Farmer Fred Orange Farmer Fred Grapes Farmer Fred Grapefruit Farmer Jed Orange  … and returning the results from the database in this format: Farmer FruitList——— ——-Farmer Ted Apple,OrangesFarmer Fred Grapefruit,Grapes,OrangeFarmer Jed Orange Notice that we are only returning one row per Farmer, and the "FruitList" column returned is a concatenated comma-separated list of values in the Fruit column. Read more →

ASP.NET 1.1 – Appsettings in Web.config

It's great to be able to put settings in the Web.Config file for my ASP.NET projects. The problem for me, though, is that when I use System.Configuration.ConfigurationSettings.AppSettings(name) to return a setting that doesn't exist in the file, an empty string ("") is returned, when ideally I would like an exception to let me know that something is missing or mispelled in my config file (or application code). Read more →