A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).
Read more →
Let's say you have a very large DropDownList with lots of values and text. We need to maintain ViewState in this DropDownList so that we can retrieve the selected value on a post back.
Read more →
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 →
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 →
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 →
In my SQLBulkCopy article, I mentioned that you can quickly copy data from anything that implements IDataReader to a SQL Server table using SQLBulkCopy (new in .NET 2.0). In this SQLTeam forum post, Jesse Hersch (jezemine) tells us that SQLBulkCopy only actually uses 3 methods of the interface.
Read more →
My latest article has just been posted over at SQLTeam.com: Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server .NET Framework 2.0 introduces a very handy new class in the System.
Read more →
I had to do some data clean up the other day, and really needed some regular expression replacements to do the job. Since .NET has a great RegularExpressions namespace, and since SQL 2005 allows you to integrate .
Read more →
Time for another exciting edition of the mailbag! Maxime writes: Hi,First of all, your class is really nice and it handles my problem really well.I would like to ask you something about the class.
Read more →
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 →
(3/25/2007 update: Fixed the incorrect parameter name the last two examples)
Parameters without Stored Procedures? Let's assume that for some reason you are not using Stored Procedures. While I can respect your choice in that regard, that doesn't mean that you cannot still use parameters when constructing your SQL statements at the client.
Read more →
I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive. Basically, you use Excel or Word to create templates utilizing data markers and merge fields which allow you to databind sections of the document to a data source.
Read more →
By default, the ViewState for ASP.NET DataGrids can be quite large, as it normally stores enough information to recreate the grid completely after a postback. Often, I have found that I need the grid to display a list of items with only paging, sorting, and some buttons that let you delete or edit (via another page) individual items.
Read more →
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
Read more →
In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database. Consider the result of a crosstab operation – the columns returned will vary depending on the data.
Read more →