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




Code Library - C# / VB.NET

Windows forms controls, classes, methods and more that you might find useful in general .NET programming.
Simple DataViewReader class that implements IDataReader for a DataView

I previously wrote that the .NET 2.0 DataTableReader class is really handy, but unfortunately there is no DataViewReader class.   Thus, the only way to use the IDataReader interface with a sorted/filtered DataView was to first use the ToRows() method of the view to create a brand new DataTable, and then call CreateDataReader() on that new table.   This is not the most efficient process when all you want to do is enumerate a DataView. So, here's a solution: a simple, efficient, mostly-complete DataViewReader class.   Just create a new instance of it passing in the source DataView and off you go.  This will...

posted @ Thursday, February 28, 2008 10:34 AM | Feedback (4) | Filed Under [ Code Library - C# / VB.NET ]

Exporting data to a remote server with SQL Express

I recently helped a friend out who only had access to SQL Server Management Studio Express, and he needed to copy a database locally from his PC to his remote web hosting company.  Normally, the process is a simple backup/restore, but his hosting company does not allow restoring databases.  Luckily, however, the company does allow direct access to his hosted database via client tools such as SSMS.  Unluckily, SSMS Express does not include any tools that allow you to export data to a remote server. So, here's what we did:  First, we scripted out the entire database, including all tables, indexes,...

posted @ Wednesday, February 27, 2008 3:04 PM | Feedback (10) | Filed Under [ Code Library - C# / VB.NET Imports/Exports ]

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 ]

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 ]

.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