SQL Tip: Concat entire column into a single CSV string

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/sql_tip_concat_entire_column_into_a_single_csv_string.htm]

Sometimes in SQL, you'll want to concat an entire column's values into a single string.

For example, say you have a parent-child relationship (like state-city, or entity-code), and in a parent's list page, you want to display a column with the CSV string of all the children.

State	City
IL	Chicago
IL	Springfield
IL	Rockford
WI	Madison

You can concatenate a list of values into a CSV string like "Chicago, Springfield, Rockford" by continually selecting into the same variable:

        declare @sCsv varchar(1000)
        set @sCsv = ''

        select @sCsv = @sCsv + City + ', '
        from MyTable
        where State = 'IL'; --any filter clause here

        --remove final ","
        if (Len(@sCsv) > 1)
                select @sCsv = substring(@sCsv,1,len(@sCsv)-1)
        return @sCsv

Regular Expressions: "Does not start with...."

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/regular_expressions_does_not_start_with.htm]

 

A coworker recently asked me how to have a regular expression check if something does not start with a certain value. For example, how to tell if an input value does not start with 'abc'.

Note that it's very easy to see if something does start with a certain value, just use the anchor-to-first-start char '^'. For example, this regex will check for all strings that start with 'abc' (followed by any \w character(s)):

    ^abc\w+

To see that something does not start with a given value, use the Grouping Construct 'Zero-width negative lookahead assertion":

    ^(?!abc)\w+

This would handle the following cases:

Pass - none of these start with 'abc':	Fail - all of these start with 'abc':
defg ab xyz 11999	abc abcdef

Note that there are four similar grouping constructs based on the combos of Positive/Negative - Lookahead/Lookbehind

• Positive Lookahead
• Negative Lookahead
• Positive Lookbehind
• Negative Lookbehind

You can download a free regex editor from MVP Roy Osherove

A Dozen Extracurricular Activities to be a Better Developer

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/a_dozen_extracurricular_activities_to_be_a_better_developer.htm]

 

I talk to a lot of developers, see a lot of resumes, and give a fair deal of interviews. One of the biggest gaps I see is a lack of any extracurricular activities - i.e. work-related tasks not required by your current job. Empirically, I notice a direct relationship between a dev's technical confidence and the quality of their extra curriculars. Here are some ideas of good extra curriculars that won't just make your resume stick out, they'll make you a fundamentally better developer.

1. Write a technical article (such as for www.codeProject.com, or any website in the Codezone community).
2. Start your own blog
3. Give an internal presentation to your company
4. Mentor newer developers
5. Help out with your companies technical interviews
6. Get a certification
7. Get a patent (obviously not feasible with most companies, but the larger ones may support it)
8. Start an open-source project
9. Attend a user group (for example, if you're in Chicago, consider attending the Chicago .Net User's Group)
10. Participate in online forums, answering others questions (which goes great with having your own blog and writing articles)
11. Make your own personal website
12. Help your non-technical friends set up their own web sites.

No matter where you are, there's likely at least some of these you could do in your current job.

Why use Stored Procedures over direct SQL calls?

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/why_use_stored_procedures_over_direct_sql_calls.htm]

Two main ways to interact with the database: using a stored procedure vs. using a direct SQL statement. Here's a brainstorm of the pros and cons to each:

Technique (called from managed code):	Pro	Con
Stored Procedure	Performance, as SP is already on the database Reusability - multiple sources (including other SP) can call a SP. Security, especially against SQL injection attacks. Maintainability - you can create the SP (and run and test it) in a database editor. Powerful - A SP consists of multiple SQL statements. Deployment - Modifying a database object is often easier than redeploying managed code. Code Generation - You can code generate a data access layer off of stored procs (although most of the time it's code-generated off of table schemas).	Have to install each SP on the database
Sql Statement	Quicker to get started, especially for simple code and a RAD, non-production app. Sql string is stored in managed code, so no need to install on the database. Flexible - you're generating any SQL statement you want.	Lacks most of the benefits of a SP.

In general (of what I've seen), many enterprise apps will use Stored Procs, while sometimes a RAD will use raw SQL statements.

What has your experience been?

Getting data snapshots with the MassDataHandler

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/getting_data_snapshots_with_the_massdatahandler.htm]

The other day I had a to investigate some data corruption. A certain table started out with one set of data, I ran an upgrade process, and QA told me it wasn't upgrading correctly. The problem was that I needed to compare two snapshots of SQL data, from the same table. I know there are many ways to solve this problem. The way I solved it was with the open-source MassDataHandler.

One of the main purposes of this free tool is to convert data back and forth between an Xml format and stored SQL data in a database. So, with the "Use Existing Data" feature, I took a snapshot of the table (using a select clause to specify the columns and order) and saved it as an Xml file. I then ran some black-box upgrade process, and took a second snapshot of the table. Because I now had two xml files, I could easily just diff them (I prefer using Beyond Compare for this).

The idea was to reduce the problem from an unknown state (how to compare Sql data) to a known one (how to compare two Xml files).

The MassDataHandler 1.2 has been released!

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/the_massdatahandler_12_has_been_released.htm]

This is a free, open-source tool to assist with database unit testing. The new version offers several new features:

• The GUI now offers the ability to ignore primary keys when generating from existing data.
• The Build process has a reusable script to drop all foreign keys from a database. This makes it easier for you to adapt the demo script to your own project.
• The Readme has been updated with better documentation.
• A few miscellaneous bugs have been fixed.

You can download it here.

Comments as version control

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/comments_as_version_control.htm]

At every company I've been, I see source code with old code commented out instead of deleted. In a sense, this is almost using commenting like a primitive form of version control. I see pros & cons to this approach:

Pro:

• In environments where requirements constantly change, some developers are more comfortable seeing how the chuck was previously written so that they can easily revert.
• You may be intending to switch back to the previous code, and it's easier to see it if the code is commented out, as opposed to digging through version control.

Con:

• As Steve McConnell suggests in Code Complete, the purpose of comments is to show the intent of your code. Using comments as version control can be misguided.
• It can imply a lack of confidence. If the code is really no longer used, why not just delete it and make your code leaner.
• It can bloat the source code very quickly, requiring future developers to read through more.

I think ideally old and obsolete code should be removed instead of commented out (makes for leaner, easier-to-read code), but I can see the other approach in certain development cultures.