Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




.NET (C# / VB)

.NET is great, and I cover it a lot in my blog posts. SQL Server and .NET make such a great team that it can be tough to discuss one without the other.
Convert input explicitly at your client; don't rely on the database to "figure it out"

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).  I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled. In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written...

posted @ Thursday, July 24, 2008 9:12 AM | Feedback (11) | Filed Under [ T-SQL .NET (C# / VB) Techniques DateTime Data ]

Minimize a DropDownList's ViewState

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.   Of course, this means that now the ViewState contains the data for every single value in the list, both values and text included.   Even though the page itself may be fairly simple and lightweight, the result of having this simple DropDownList on the page is that the page size is quite large and the amount of data passed back and forth on a postback is very...

posted @ Thursday, April 17, 2008 12:21 PM | Feedback (8) | Filed Under [ .NET (C# / VB) ASP.NET ]

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.  You can create this object by using the CreateDataReader() method of a DataTable.  This is great because now you can create a generic ReadFromDatabase() method for your classes that accepts an IDataReader parameter and you can create those objects from pretty much any data source, including DataTables, which wasn't as easy to do previously. There is one catch:...

posted @ Friday, February 22, 2008 12:30 PM | Feedback (1) | Filed Under [ .NET (C# / VB) ]

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 Foundation          Product 1   $200          Product 2   $437          Product 3   $523         The XLZ Company          Product 1   $240          Product 2   $892          Product 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).  To use nested repeaters, you would return two separate result sets from SQL:  A "Customers" result set,...

posted @ Friday, November 02, 2007 12:16 PM | Feedback (0) | Filed Under [ .NET (C# / VB) ASP.NET Code Library - C# / VB.NET ]

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

A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...

posted @ Tuesday, October 09, 2007 3:02 PM | Feedback (19) | Filed Under [ T-SQL .NET (C# / VB) Efficiency Report Writing ASP.NET Joins/Relations ]

Using SQLBulkCopy to copy any .NET Object to a SQL Server Table

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. This means that it is very easy to quickly create your own custom class which implements this interface to bulk copy pretty much any type of object collection or array to a SQL Server table.

posted @ Tuesday, May 22, 2007 2:40 PM | Feedback (3) | Filed Under [ .NET (C# / VB) Links Imports/Exports ]

Using SQLBulkCopy to quickly transfer data from .NET to SQL Server

My latest article has just been posted over at 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.Data.SqlClient namespace called SqlBulkCopy that makes it very easy and efficient to copy large amounts of data from your .NET applications to a SQL Server database. You can even use this class to write a short .NET application that can serve as a "middleman" to move data between database servers. If you ever need to move large amounts of data to SQL Server from a .NET application, SQLBulkCopy...

posted @ Monday, May 07, 2007 9:29 AM | Feedback (3) | Filed Under [ .NET (C# / VB) Links Imports/Exports ]

Regular Expression Replace in SQL 2005 (via the CLR)

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 .NET CLR functions in your T-SQL code, I thought I'd go ahead and experiment with creating a RegExReplace() function.


posted @ Friday, April 27, 2007 4:20 PM | Feedback (13) | Filed Under [ .NET (C# / VB) SQL Server 2005 Code Library - C# / VB.NET ]

Sorting Columns with the C# Pivot Function

Time for another exciting edition of the mailbag! This time: making an improvement to the C# Pivot function.

posted @ Friday, April 20, 2007 12:05 PM | Feedback (5) | Filed Under [ CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

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 ...

read more

posted @ Wednesday, March 28, 2007 12:09 PM | Feedback (12) | Filed Under [ .NET (C# / VB) ASP.NET Code Library - ASP.NET ]

Always Use Parameters. Even if you don't use 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.


posted @ Friday, July 21, 2006 10:43 AM | Feedback (22) | Filed Under [ T-SQL .NET (C# / VB) Techniques ]

Rendering Excel and Word files with SQL Server

I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive.


posted @ Tuesday, July 18, 2006 3:05 PM | Feedback (2) | Filed Under [ Miscellaneous .NET (C# / VB) Report Writing ]

Minimizing the ViewState for an ASP.NET 1.1 DataGrid

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.


posted @ Tuesday, July 11, 2006 10:22 PM | Feedback (5) | Filed Under [ .NET (C# / VB) ASP.NET Code Library - ASP.NET ]

.NET CrossTabs versus SQL Server CrossTabs

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.


posted @ Thursday, May 12, 2005 10:01 AM | Feedback (7) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Efficiency Report Writing Code Library - C# / VB.NET ]

.NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

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.  There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes.  Also, in T-SQL there is no easy way to dynamically pivot data, and even doing it with...

posted @ Wednesday, May 11, 2005 3:49 PM | Feedback (23) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

Powered by:
Powered By Subtext Powered By ASP.NET