John's Brain Dump

SQL Trick: Finding Integers in Character Columns

Q: What WHERE condition do you use to find all values in a column that contain only numbers? (No decimal points, no minus signs, just 0-9.

A: [column name ] NOT LIKE '%[^0-9]%'

This is the kind of thing that I'll forget a year from now when I need it again.

UPDATE: 7/11/06

I ran into the need for this again, and I thought I should note that this technique can be used to check that a column contains only characters from any subset, not just numbers. I just used it to verify U.S. Bankruptcy case numbers, which contain only numbers and dashes. My search condition was [CaseNo] NOT LIKE '%[^-0-9]%'.

DataSet Toolkit: Can't Live Without It!

I haven't been this excited about the release of a product, but I really think that this one will make the lives of developers a whole lot easier. The DataSet Toolkit from Hydrus Software is an intelligent framework that makes data access in .NET applications a breeze. All you need to do is to define a DataSet that resembles the structure of your SQL database (SQL Server, Oracle, or whatever), and you can use the toolkit to fill your DataSet, send updates back to the database, run ad-hoc queries, and perform other data operations. It infers the structure of your database from the DataSet itself, so you don't need to worry about managing your data access code; you just keep your DataSet up to date, and you're finished.

I've actually worked with a pre-release version of this component for quite a while now, and I've used it as the data-access backbone of more than one large ASP.NET application. I have a lot to say about it, so over the next few days (or weeks, or maybe months), I'll be posting usage examples, tips and tricks that I've discovered.

This may all sound like shameless product marketing, and I'd be lying if I didn't say that I had a hand in developing it. But this toolkit has made my programming life a whole lot easier for a while now, and I think it's worthwhile for any developer to look into. There's a 30 day trial available for download, so give it a whirl!

Download the DataSet Toolkit

Edit:

Free Intellisense for SQL Server!

I read this morning that Red Gate Software has released a *free* program that adds Intellisense to your standard SQL editors (Query Analyzer, Enterprise Manager, Management Studio and VS). I tried it out, and had it up and running in less than five minutes. Since I write a lot of queries everyday, and often find myself retyping the same things over and over, this is sure to be a huge timesaver. You can download the installer from here:

http://www.red-gate.com/products/SQL_Prompt/index.htm

Thanks to Dan Wahlin for writing this up and posting it to DotNetKicks.com, where I found it.

Optional, Multi-Column DataSet Relationships

Perhaps I'm a moron and everybody else in the .NET development community knew this before I did. But I will confess that until today, I had no idea how to create optional, multi-column relationships in strongly typed data sets. I knew that you can use the <xs:keyref> element to create multi-column relationships, but these relationships come with foreign keys, making them required. I also knew that you could use the <msdata:Relationship> annotation to create relationships without requiring foreign keys, but up until today, I thought you could only specify a single column in each of the msdata:parentkey and msdata:childkey attributes. Well, it turns out I was wrong. After reflecting deep into the data set code generator, I found that you can specify a space-delimited or plus-delimited list of columns in each of these attributes to create a multi-column relationship. So, the following is a perfectly valid element in an XSD schema used to generate a strongly-typed DataSet:

<xs:annotation>
    <xs:appinfo>
        <msdata:Relationship 
            name="SurplusLetter_SurplusWarrant"
            msdata:parent="SurplusWarrant"
            msdata:child="SurplusLetter"
            msdata:parentkey="SurplusAccountID WarrantID"
            msdata:childkey="ResultingWarrantAccountID ResultingWarrantID" />
    </xs:appinfo>
</xs:annotation>

I have scoured the web searching for any documentation of the msdata:Relationship annotation, and I have never found any mention of this feature. So hopefully I can save you the headaches that I had to put up with until I stumbled on this.