Ann Lewkowicz

A Request

2011-11-17T17:19:00.000-05:00

I know "the combined Ohio, Indiana, and Northern Kentucky metro area" is an awkward phrase. It cries out for abbreviation.

But please, don't call it "OINK".

Towards a Print Statement that Appears in SQL Profiler –Part 1

2011-03-28T11:11:00.003-04:00

The SQL Server PRINT statement can be very useful. Some uses:

Impromptu performance testing: save the start time of a chunk of SQL in a variable, and at the end PRINT the difference between the current time and end time.
Verifying during debugging that a variable used in a stored proc has the expected value at a certain point.
Obtaining the value of a variable retrieved from the database during a proc so that the developer can do impromptu querying of other tables and verify their contents.

But the PRINT statement doesn’t echo to SQL Profiler. This means that you can only do the above if you’re working with SSMS or otherwise have access to SQL Info Messages. Oh, you can get a copy of the command you executed – “PRINT @ID” or whatever – but you won’t get visibility through Profiler to what @ID was.

This is a problem if you’re trying to diagnose a stored procedure as an application executes it. I started research a few days ago on how I might provide a PRINT to Profiler capability.

Let me say up front that I did not find a perfect solution. But I can offer three partial ones.

1. Use sp_trace_generateevent. This is an extended stored procedure that lets you write a user-defined event to the trace log. You can provide up to 256 chars of text and 8000 bytes of event data. The characters appear in TextData and the 8000 bytes in BinaryData. There are 10 reserved event Ids (82 to 91) that you can use for your own purposes.

Here’s a stored proc that combines PRINT and using sp_trace_generateevent:

CREATE PROCEDURE PrintTrace1
@Text nvarchar(max) 
AS
BEGIN
DECLARE @UserData BINARY(8000) = 0
DECLARE @UserInfo NVARCHAR(256) = SUBSTRING(@Text,1,256)
PRINT   @Text
EXEC sp_trace_generateevent 82, @UserInfo, @UserData
END
GO

In order to see the trace output in Sql Server Profiler, you need to turn on the appropriate user defined event. My code above uses user-configurable event 0 (id=82), so you need to turn it on:

Here’s what the test script looked like:

DECLARE @PrintText varchar(256) = 'This is a test 
This is a test
This is a test
This is a test
This is a test'
EXEC PrintTrace1 @PrintText

And here’s what the SQL Profiler output looks like:

This works just fine, as long as you only need to PRINT 256 chars. But suppose you need more? Suppose your app works by retrieving templated SQL from a database table, and you need to be able to see every line that’s about to be executed? In that case, sp_trace_generateevent won’t help you. We’ll discuss other options next time.

Quick and Dirty Unpivoting with SQL Server’s XML Features

2011-03-06T17:41:00.001-05:00

Let’s suppose you want to return some data in the following extremely unpivoted format:

SELECT  ID
   ,    ColumnName
   ,    Value
FROM    .. something ..

Your source table or query has (let’s say) about 30 different columns. How can you quickly produce something in the format above?

Well, one way is a bunch of UNION statements, like so (using the AdventureWorks database):

select  ID = ProductId
    ,   ColumnName = 'Name'
    ,   Value = Name
from    Production.Product
union
select  ID = ProductId
    ,   ColumnName = 'ProductNumber'
    ,   Value = ProductNumber
from    Production.Product    
union
select  ID = ProductId
    ,   ColumnName = 'MakeFlag' 
    ,   Value = CONVERT(char(1), MakeFlag)
from    Production.Product

This works fine, if you have only a few columns you want to return in this format. If you have 20-30, as with the Production.Product table, that’s a lot of UNION statements. And copying the code to use for another table is basically a waste of time: you’ll have to change so many things you might as well just start typing from scratch.

Another way is using the UNPIVOT statement. It produces the same sort of result, and looks like this:

select  *
from    (
    Select  ProductId, 
            ProductLine, 
            Class, 
            Style 
    from Production.Product) as pv
UNPIVOT (Value for FieldName IN (ProductLine, Class, Style)) p

But all the fields you want to unpivot need to be of the exact same type. For strings, that means the same type AND the same length. (The ProductLine, Class, and Style fields above are are nchar(2)). Plus, you need to list all the fields to which you want the UNPIVOT to apply. If you have 20 or 30 fields, that’s a lot of fields to list.

So, what we need is a way to produce a list of this form, that can adapt to differing column types, can be applied to all fields or some fields in a table with a minimum of column-name-typing, and which can be copied-and-repurposed without requiring a complete retype.

Here’s one way to do it, using XML. In terms of performance, it is the slowest of the three options, but in terms of programmer time, it’s definitely the fastest and most versatile.

with A
as (
    --  We're going to return the product ID, plus an XML version of the 
    --  entire record.
	select  ProductId
	    ,   (
	        Select  * 
	        from    Production.Product 
	        where   ProductID = pp.ProductId
	        for xml auto, type) as X
	from    Production.Product pp
	)
,	B
as	(
    --  We're going to run an Xml query against the XML field, and transform it
    --  into a series of name-value pairs.  But X2 will still be a single XML
    --  field, associated with this product ID.
    select  ProductId
        ,   X.query(
		       'for $f in Production.Product/@*
		        return 
    				<product name="{ local-name($f) }" value="{ data($f) }" />
		    ') as X2
	from	A
	)
,	C
as	(
    --  We're going to run the Nodes function against the X2 field, splitting 
    --  our list of "product" elements into individual nodes.  We will then use
    -- the Value function to extract the name and value.  
	select	B.ProductId as ID
		,	norm.product.value('@name', 'varchar(max)') as Name
		,	norm.product.value('@value', 'varchar(max)') as Value
from	B
	cross apply B.X2.nodes('/product') as norm(product)
)
-- Select our results.
select	*
from	C

Note a couple of things:

1. We’re using (and returning) all the fields from Production.Product, but we didn’t have to list the field names.

2. We’re using the term “Product” in C and referring to the XML node Production.Product, but those names are arbitrary: we could have formed the XML in step A in such a way that it had a generic name.

3. If you wanted to return fewer columns, you have a couple of choices: you can specify the particular columns you want in the subquery in step A, or you could filter them out at the final step. (Since the column names are in the field Name, you could even join to sys.all_columns or some other list of column names, so as to filter by type or some other criteria.)

4. We are returning the Value column as varchar(max); this is because anything can be expressed as a varchar. But if we were returning, say, all ints, we could specify in the value ()function that the data should be returned as an type int.

5. Nulls are automatically filtered out.

Here’s what the results look like, by the way:

Not bad, I think.

Doing Things with Strings: SQL, Xml, and String Manipulation, Part II

2011-02-20T18:41:00.001-05:00

This series of posts is about using SQL Server’s Xml features to do string manipulations. Part I talked about creating comma-separated (CSV) lists from SQL database data. Part II, this post, will talk about parsing a comma-separated list.

Until Xml-typed parameters came along in SQL Server 2005, the only easy way to pass a set of information into a stored procedure was by passing a string parameter that represented serialized data. The two most common forms were an Xml fragment, and a CSV string. Parsing these CSV strings usually required a custom table-valued function that used T-SQL string manipulation operations to parse out the information and build the return table.

This is a perfectly good solution, but there’s a faster one. I’ll give you the code for it, right up front:

CREATE FUNCTION ParseCsv
(
    @Csv varchar(max)
)
RETURNS TABLE
AS
    RETURN 
    (
        -- We're being passed in a CSV string; we'll replace the commas with
        -- endtag/start tag pairs.
        WITH A
        AS  (
            SELECT  REPLACE(@Csv, ',', '</dummy><dummy>') as XmlFrag1
        )
        ,   B
        AS  (
            -- We're building an XML fragment out of the CSV, in fact.
            SELECT  '<dummy>' + XmlFrag1 + '</dummy>' as XmlFrag2
            FROM    A
        )
        ,   C
        AS  (
            -- Convert it actually to type XML
            SELECT  CONVERT(xml, XmlFrag2) as XmlFrag3
            FROM    B
        )
            -- For more on XmlFragment, see below.  Right now, we want the
            -- content of the fragment, which we're getting with the value()
            -- function, and converting it to "int". 
        SELECT  D.XmlFragment.value('.', 'int') as Value
        FROM    C
            -- When you want to access the Xml field in every record, and 
            -- apply the "nodes" function to it, you need to remember that
            -- nodes() is a *function*, and thus can be used with CROSS APPLY
            -- to run the function for every record in the record source (C).
            -- Hence, we run nodes() on C.XmlFrag3 for every record, and call
            -- the resulting set of data D, with the results of "nodes" being
            -- the field XmlFragment.  
            CROSS APPLY C.XmlFrag3.nodes('/dummy') as D(XmlFragment)
     )

You’ll want to test it, so here’s code (using the AdventureWorksLT database) that generates a CSV string.

DECLARE @csv varchar(max)
WITH A
as (
    SELECT (
        SELECT  ProductID 
        FROM    SalesLT.Product
        FOR XML AUTO, ELEMENTS, TYPE
    ) as ProductXml
   )
   
,   B
AS  (
    SELECT A.ProductXml.query('data(*)') AS ProductSeries 
    FROM A
)
SELECT  @csv = REPLACE(CONVERT(varchar(max), ProductSeries),' ',',')
        FROM B
SELECT @csv
select * from ParseCsv(@csv)

And here’s the result you get from the test. The first recordset is just the select on @Csv, the second one is the result of the function.

In tests, this consistently ran in half the time of a T-SQL string manipulation solution, regardless of the number of values in the CSV.

Useful Things to Know, #428

2011-01-30T22:26:00.001-05:00

Contrary to a lot of what you’ll find said online, Linq to SQL seems to work fairly happily with tables from different SQL Server databases (but the same server) in the same model. You just have to put the full name (including the database) into the Name property of the entities representing tables in the “foreign” database. You can even build associations between tables from different databases: Linq to SQL will take your word for it that those relationships exist.

A caveat: I have only tested this on VS 2008 (what I happen to be working with right now) and I haven’t attempted anything fancy with updates or inserts (since what I’m working on doesn’t require them). But, since I found people saying emphatically that cross-database modeling couldn’t be done in Linq to SQL, I wanted to report that it can.

Doing Things With Strings: SQL, Xml, and String Manipulation, Part I

2011-01-22T17:03:00.008-05:00

SQL Server 2005 introduced new features for handling and manipulating Xml. Generating Xml via T-SQL is much simpler than it used to be. Parsing Xml is much, much easier, and we have a an Xml data type to use, too. All of that is pretty well documented in the Books Online. So if you need to produce or consume Xml, that's where to look.

But what these posts will be about is using Xml and the Xml features to get some non-Xml things done. Specifically, I'm going to talk about some types of string manipulation that the Xml features make faster and easier.

Generating Comma-Separated Lists (CSV Strings)

Sometimes, when you're summarizing data, you want the values in a column to be aggregated into a comma-separated list. An example of this might be a list of your biggest customers, with the trade associations or buying groups they belong to listed off to one side.

As you know, there is no built-in aggregate to do this in SQL Server. Microsoft (and, I believe, others) have published a SQL CLR aggregate function that will do this for you.

But, thanks to the Xml features, this can be also be done with T-SQL code, without publishing assemblies or writing custom functions. I’m going to demonstrate it with a series of CTEs, so that each step in the transformation is separate, but you can combine all of them into one expression if you like.

Let’s suppose that we are querying the AdventureWorks2008 database, and we want a list of stores with a list of contacts for each store in the same row, in a comma-separated list.

We start by querying the Sales.Store table, and include a subquery of the Person.Contact table, using the Sales.StoreContact table to link between stores and contacts. Our subquery is going to return the names of the contacts as an Xml fragment.

select  CustomerId
    ,   Name
    ,   (
        Select  ContactName  = FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
        from    Person.Contact pc
                    inner join
                Sales.StoreContact ssc
                    on    pc.ContactID = ssc.ContactID
        where   ssc.CustomerID = ss.CustomerID
        for     xml auto, elements, type
        ) as ContactList1
from    Sales.Store ss

This looks like this:

Note that we’re only returning one element in our fragments: the Contact Name element. This is the only text that the fragment contains.

Our next step transforms the ContactList1 Xml field into a space-delimited list of names. Because our data has spaces, we’re going to alter our subquery a little to replace the spaces in the data with a character unlikely to appear in a name. You’ll see why in a minute.

With A
as  (        
    Select    ContactName  = FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
         ,    ssc.CustomerID   
    from      Person.Contact pc            
                  inner join                  
              Sales.StoreContact ssc              
                  on   pc.ContactID = ssc.ContactID    
 ),  B
 as  (    
     select  CustomerId        
         ,   Name
         ,   (                
                 select  ContactName = REPLACE(A.ContactName, ' ','|')
                 from    A
                 where   CustomerID = ss.CustomerID 
                 for     xml auto, elements, type 
             ) as ContactList1    
     from   Sales.Store ss
)
select  B.*
   ,    ContactList2 = B.ContactList1.query('data(*)')
from    B

This produces results like this:

To convert this to a comma-separated list, we just need to do a conversion and some REPLACE statements:

With A
as  (
        Select    ContactName  = FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
             ,    ssc.CustomerID
        from      Person.Contact pc
                        inner join
                  Sales.StoreContact ssc
                        on   pc.ContactID = ssc.ContactID
    )
,   B
as  (
    select  CustomerId
        ,   Name
        ,   (
                select  ContactName = REPLACE(A.ContactName, ' ','|') 
                from    A
                where   CustomerID = ss.CustomerID
                for     xml auto, elements, type
            ) as ContactList1
    from   Sales.Store ss
)
,   C
as  (
select  B.*
   ,    ContactList2 = B.ContactList1.query('data(*)')
from    B
)
select  C.CustomerID
    ,   C.Name
    ,   ContactList = REPLACE(
                        REPLACE(
                            CONVERT(varchar(max), C.ContactList2)
                        ,   ' '  -- convert spaces ...
                        ,   ', ' -- to commas (followed by spaces, if youlike)
                        )
                      , '|'  -- replace the pipe char ...
                      , ' '  -- with the original spaces!
                      )
from    C

… and the result looks like this:

Voilà: inline comma-separated lists!

Useful Things to Know, #427

2010-10-30T19:46:00.003-04:00

When it comes to XML, SQL Server won't always eat its own cooking.

What I mean by that is that it's quite possible to generate syntactically valid XML from valid SQL Server data, via SQL Server queries, that is semantically invalid. SQL Server will deliver this data, but it won’t consume it or allow it to be assigned to an XML-type variable.

Here's how. Suppose you have the bad luck to have some non-printable characters in some of your text fields. XML only tolerates a few non-printable characters -- although I'd have to look it up, I think the ASCII characters for 9, 10, and 13 are the only ones it does tolerate.

So consider this SQL:

create table BadChars
(
TextData varchar(255)
)
insert into BadChars
values
  (char(4)) -- bad char
, (char(6)) -- bad char
, (char(10)) -- good non-printable char
, (char(13)) -- good non-printable char
, (char(33)) -- regular character

Let's retrieve this data as XML:

select *
from BadChars
for xml auto, root('BadCharTable')

This will run fine, and return you a chunk of XML in SSMS, like so:

<BadCharTable>
  <BadChars TextData="&#4;">
  <BadChars TextData="&#6;">
  <BadChars TextData="&#10;">
  <BadChars TextData="&#13;">
  <BadChars TextData="!">
<BadCharTable>

Just try doing anything with it, though:

declare @xml Xml
select @xml =
(select *
 from BadChars
 for Xml auto, root('BadCharTable')
)

The error you'll get is this:

Msg 9420, Level 16, State 1, Line 3
XML parsing: line 1, character 40, illegal xml character

That's because the XML it so happily generated a few moments ago is not valid, due to those non-printable characters, that even in escaped form are not allowed.

So SQL Server will return invalid XML in a result set, if the source data contains invalid chars. It just won't consume it, or allow it to be assigned to an XML variable.

The Joy of HashSets

2010-10-25T12:07:00.001-04:00

A while back, I mentioned to a colleague that I was going to write a post about hash sets and their general coolness. He said that hash sets were old hat: every C++ developer had been using them for years.

“Yes,” I said, “but not all of us were C++ developers!”

Here, then, is the MSDN description: HashSet<T> Class. In brief, a hash set is a collection, one that you can very quickly check for membership. It’s a way to store stuff you find important, when you’re interested in membership in the collection, but not (particularly) in the data or attributes of what you’re storing.

The key difference between the hash set and its sibling, the hash table, is that with the hash set you’re not looking anything up by key. You’re just looking it up by itself, or by key without any interest in what the key refers to.

So what’s so cool about yet another collection class? This: those suckers are fast. Consider the following scenario:

You are processing some data;
You don’t want to process duplicates;
Ergo, you need to keep a list of what you’ve already processed.

This – keeping a collection of stuff you find important, that you can check for membership – is what hash sets are for. The items in them are in not in any order (although, if you need that, consider the SortedSet class). And lookups are fast. I shaved a large percentage of time off one application, just by switching from using a List to store my processed ids to a HashSet.

Reverse foreign key relationships

2010-10-22T14:21:00.001-04:00

Sometimes you have a master table and some child tables, which have foreign key relationships to the master table. The child table can’t have a value for the foreign key that is not in the master table, and the master table can’t delete a record that is referred to by records in the child table. All this is well and good.

But sometimes, a correct representation of the entities the data is embodying requires that every master table record have one (or more) records in every child table. An example might be a table of products for sale and a table of prices for those products. A price must refer to a specific product, but every product must have at least one price.

How does one enforce this in the database?

Now you can say, “That’s business logic! That doesn’t belong in the database!” But I tend to feel that there’s a fuzzier line between business logic and database integrity rules than is sometimes acknowledged. Why is “a price must refer to a product” a question of database integrity, but “a product must have at least one price” a business logic issue?

Things I’d Like to See, #766

2010-10-17T12:13:00.001-04:00

I’d like a class derived from IEnumerable<T> to which it was possible to add an index.

Hear me out. I have a project that involves generating and capturing a lot of data. It’s not being retrieved from a database, and it’s not being saved to a database: it’s being generated, analyzed, and ultimately discarded.

While it lives, it lives in some collection classes, Lists and Lookups and the like. While I’m analyzing it, I often have to summarize in multiple different ways, and do cross-verification and lookups by properties of the classes in the collections. It’s (relatively) slow. Any given lookup is, of course, lightning fast, but if it occurs over a big enough data set enough times, it gets slow.

Well, why not use a literal DataSet, then, you ask? Those have indexes and primary keys and so on. And that’s true. But I want to preserve my strongly typed properties, and create derived (anonymous) summary types. The syntax of doing that from a DataSet can get complex. And as for typed DataSets, they have their value, but they’re tough to customize, even with the wonders that are partial classes.

Moreover, if I went the DataSet route, I’d need to define a DataTable for the result of every Linq summary class that I create, rather than using an anonymous type. That does away with all the benefits of anonymous types, including the rather significant one that the compiler will adapt to any new fields I create in the source types (the ones being summarized), and either change the definition of the anonymous type or let me know I have a problem. If I’m relying on DataTable that I create programmatically (or even with an XSD), I don’t find out I have a conflict in definitions until run time. (Hopefully, test time, but once in a while something slips by you.)

So: I’d like a IEnumerable<T>-based type that supports adding indexes for performance purposes.

Useful Things to Know, #426

2010-10-15T10:13:00.001-04:00

You can add an extension method to an enumerated type.

You can. The code looks like that of any other extension method:

  public enum SalesCallObjective
    {
        NewCustomer = 1,
        RetainCustomer = 2,
        KeepInTouch = 3
    }
    public static class SalesCallObjectiveExtensions
    {
        public static ProductCategory ToProductCategory(this SalesCallObjective value)
        {
            switch (value)
            {
                case SalesCallObjective.NewCustomer:
                    return ProductCategory.CompetitiveProduct;
                case SalesCallObjective.RetainCustomer:
                    return ProductCategory.UpgradeProduct;
                case SalesCallObjective.KeepInTouch:
                    return ProductCategory.PromotionalProduct;
                default:
                    return ProductCategory.Unknown;
            }
        }
    }

The above is just an example of what you can do, not a real-life attempt to solve a business problem. But it does show one potential use: mapping an enum from one domain (sales call objective) to another.

Malfunctioning scrollbars in Panel/ScrollableControl with AutoScroll turned on

2010-10-08T13:01:00.002-04:00

I have recently been working on a WinForms app that involved a Panel control with dynamically-added child controls. AutoScroll was turned on. When you scrolled vertically, the “thumb” changed size and position seemingly at random: large amounts of white space were added as you scrolled to the bottom, which would then disappear as you scrolled in the other direction. Sometimes the scrollbar would disappear entirely. The “thumb” seemed to jump around, as if the panel were somehow changing size every time we scrolled.

Googling turned up a lot of complaints and a few workarounds, but none of them worked for us. (Our dynamic controls were capable of changing size, and most of the workarounds didn’t work with that. )

Well, after some investigation with Reflector, I think I’ve figured it out. There appears to be a bug with ScrollableControl, the base of the Panel control. Scrolling vertically causes the DisplayRectangle property’s Height to increase (incorrectly), which in turn causes the Maximum property of the VerticalScroll object to increase.

What can you do about it? Well, you can’t change DisplayRectangle, which is read-only, and if you have AutoScroll turned on, attempts to set the VerticalScroll.Maximum property are ignored. But you can do the following:

If you’re hosting a Panel in something else, put the following code in your Scroll event handler. If you’re deriving a control from Panel, you’ll have to adapt it slightly for the OnScroll method (probably by replacing references to “pnl” with “this”):

System.Windows.Forms.Panel pnl = sender as System.Windows.Forms.Panel;
int maxHeight = pnl.Controls.Cast<Control>().Sum(c => c.Height);
if (pnl.VerticalScroll.Maximum > maxHeight)
{
    MethodInfo mi = typeof(ScrollableControl).GetMethod("SetDisplayRectangleSize", BindingFlags.Instance | BindingFlags.NonPublic, null, new Type[] { typeof(Int32), typeof(Int32) }, null);   
    if (mi != null)
        mi.Invoke(pnl, new object[] { pnl.DisplayRectangle.Width, maxHeight });
    mi = typeof(ScrollableControl).GetMethod("SyncScrollbars", BindingFlags.Instance | BindingFlags.NonPublic, null, new Type[] { typeof(bool) }, null);
    if (mi != null)
        mi.Invoke(pnl, new object[] { pnl.AutoScroll });
}

ETA: Actually, depending on how your child controls are arranged, you might want to calculate maxHeight as Max(c => c.Bottom) rather than Sum(c => c.Height). Ours were arranged in a single column with no intervening space between them, but your situation might be different.

Access and OleDb for the SQL Server programmer

2010-09-18T12:24:00.003-04:00

I have been working on a project that uses MS Access, accessed from C# via OleDb. The following are some of the things I’ve discovered. (This list will be updated as time goes on.)

1. Joins need to be encased in nested parentheses, like so:

Select *
From  ((Tbl1 t1
  inner join Tbl2 t2
    on t1.Id = t2.Id)
  left outer join Tbl3 t3
    on t1.Id = t3.Id)

Incidentally, if you have fallen into the habit of dropping the “inner” and “outer” from your join statements, break it. Access requires them.

2. It doesn’t matter what you call your parameters: OleDb will apply the supplied variables in the order the parameters are used. So if you have this SQL statement:

Select *
from   Tbl1
where State = @State
   and (ZipCode = @ZipCode or @ZipCode is Null)

… you need to supply the value for @State first, and then @ZipCode, because that’s the order in which they’re used. (The official way to do parameters in OleDb is with ? rather than a parameter name, but if you use your parameter more than once (like @ZipCode, above), you’d have to provide a value for it, each time.)

3. BUT. There is at least one exception to the previous. If you use the parameters in subqueries in your WHERE clause, those parameters will be supplied with values first, even if another variable is used earlier in the WHERE clause. So if your query looked like this:

Select * from Tbl1 t1
where State = @State
and (select *
  from Tbl2 t2
  where t1.Id = t2.Id
  and t2.ZipCode = @ZipCode)

… you would need to provide the value for @ZipCode before that for @State.

Mercifully, there’s a way to check this and get the necessary order. Open Access, begin a query, switch to the SQL view, and paste in your SQL. Then execute it. The order in which Access prompts you for parameters is the order in which you need to provide the parameters in OleDb.

4. If you’re calculating columns in your query, the syntax “Result = a + b” won’t work. It’ll treat Result as a parameter. You need to use the older syntax “a + b as Result”.

A Conversation

2010-08-17T17:07:00.001-04:00

Geek 1: Here's an LOL for ya... My co-worker is our VMS guy here, and programs in Perl in VI on the VMS server. I introduced him to Notepad++ today and syntax highlighting, and he almost had a heart attack, and can't stop thanking me... he was so amazed.

Geek 2: His civilization has just been introduced to Fire.

Welcome back, GOSUB

2010-08-05T12:09:00.003-04:00

Okay, who here remembers GOSUB?

Wow, that few? Okay, GOSUB was a feature of BASIC implementations pre VB.Net. It executed a subroutine that was defined within another routine, sort of like this:

Dim x as Integer

x = 1

Gosub Sub1

x = 0

Sub1:

x = x + 1

Return

If the syntax is a bit off, it's because I haven't even seen a GOSUB in many years. (I'm capitalizing it because I originally learned it in Apple II Basic, where all things were capitalized.) VB.Net did away with it on the grounds that it isn't structured, entirely correctly in my opinion. You see, a subroutine in a GOSUB had the same scope as the parent routine, and so had access to all its variables.

Which brings us back to my previous post, about anonymous methods having access to the variables in the routines in which they are defined, even if they are invoked from outside the method. It occurred to me that this basically replicates the GOSUB. Consider the following:

int i = 0;

Action a = () =>

{

System.Diagnostics.Debug.WriteLine("I am routine 'A'");

i++;

};

Action b = () =>

{

System.Diagnostics.Debug.WriteLine("I am routine 'B'");

i++;

};

// Regular inline code

i = 1;

System.Diagnostics.Debug.WriteLine("I am inline code");

System.Diagnostics.Debug.WriteLine("The value of i is currently {0}",i);

a();

b();

System.Diagnostics.Debug.WriteLine("I am inline code again");

System.Diagnostics.Debug.WriteLine("And the value of i is now {0}",i);

The only way in which this differs in function from the Basic above is that the Action variables have to have anonymous methods assigned to them before they are invoked, rather than after as was the norm (and perhaps a requirement) in Basic.

Welcome back, GOSUB.

Cognitive Dissonance, or why I don't like this picture

2010-07-22T14:02:00.003-04:00

This is legal code. The Assert completes successfully:

class Program
{
    static EventHandler eh;
    static void Main(string[] args)
    {
        int i = 0;
        eh = (source, e) => { i = 1; };
        eh.Invoke(null, EventArgs.Empty);
        System.Diagnostics.Debug.Assert(i == 1);
    }
}

As you can see, it is possible for an event handler defined elsewhere to have an anonymous method assigned to it that modifies an internal variable in the method in which it is defined. (Ignore the fact that the above example has the event handler invoked synchronously and from within the same method that defines the anonymous method. I’ve experimented and this still works even if these things aren’t true.)

I don’t like this. The whole idea of an internal variable is that it is internal: nothing outside the method in which it is defined can access it.

Yes, the anonymous method is defined within the method, but it can be invoked from outside the method. It isn’t internal.

I’m sure there is a very sound reason why this was done. I just can’t think of any problem that opening a tunnel to a method’s internal variables solves that couldn’t be solved some other way.

A Thought about Organizational Systems

2010-07-03T11:56:00.000-04:00

For me, it's more important that a system be easy to put things into than that it be easy to get things out of.

I'm willing to rummage through a fat folder of "2009 Medical Bills" to find one particular thing, as long as I'm confident that all medical bills from 2009 really are in that folder and aren't in some pile across the room. But I also know that if filing 2009 medical bills required making little piles by practitioner and labeling a new folder every time an Explanation of Benefits came in, probably the bills never got filed at all and are still in some pile across the room.

Hence: putting things into a system easily > getting things out of it in a hurry. Your mileage may vary. But if you're having trouble getting or staying organized, you might think about which side of the transaction is more important for you.

SSMS 2008 add-ins: one problem solved

2010-06-30T10:07:00.000-04:00

If you Google SQL Server Management Studio Add-Ins, you'll find quite a few articles on writing them for SSMS 2005. You'll also find people complaining that they can't get them to work in 2008.

I can't add any useful comment on the changes made in assemblies between versions, because I didn't attempt writing an SSMS add-in until I'd already upgraded to 2008. However, I did encounter one problem right away when I attempted to write an SSMS version of a VS 2010 add-in, as have others. The boilerplate code in the OnConnect method, which was generated by VS, would fail on this line:

public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)

{

_applicationObject = (DTE2)application;

_addInInstance = (AddIn)addInInst;

The error message was “No such interface supported”.

A little poking about, and it became clear that this is because, well, the host application isn't a DTE2 anymore. It's a SSMS object with a very long name and no obvious connection to the DTE2. Which is a problem if you need any of the DTE2's properties, as you probably do.

But the fix is easy. The addInInst object has a DTE property that is, in fact, of type DTE2! So all you have to do to get over this speed bump is to change those two assignment statements:

_addInInstance = (AddIn) addInInst;

_applicationObject = (DTE2) _addInInstance.DTE;

And carry on from there.

Things I'd Like To See, #765

2010-06-23T15:13:00.001-04:00

I'd like to be able to put attributes (à la .Net) on Sql Server columns. I'd like to be able to flag certain columns so as to indicate that these columns with the name UserId that relate to the master User table are for change tracking, as opposed to other columns called UserId that relate to the User table but might be part of security or personnel records.

Why do I want this? So that I can run queries to ensure that standard columns all have standard definitions. Standards are only standards if they're enforced, after all.

This came up because I stumbled upon a standard column with some non-standard characteristics today, and had to write a change script. If we had an equivalent to FxCop that we could apply to table definitions and stored procedures, we could keep an eye on these things and catch them before they got to production. Being able to run a metadata query to ensure that all columns with the attribute "TrackingUserId" had a certain definition would be a good start.

(Before you suggest it, let me say that I know we could probably simulate this with extended properties. If I were staying longer on this project, I might try to implement that.)

Useful Things to Know, #425

2010-06-22T13:59:00.002-04:00

You can call an extension method on a null class instance.

For example, if you have an extension method IsEmptyOrWhitespace on the class System.String, the following code will work:

string s = null;
Debug.Writeline(s.IsEmptyOrWhitespace());

It makes sense because the string s is actually passed as a parameter to the method IsEmptyOrWhitespace. Therefore, if you code the method correctly, it'll accommodate a null value and respond in whatever way makes sense.

It's interesting because it's a place where the facade of the extension method actually being an instance method of the class breaks down. (But I wouldn't code on the assumption that this will always be true!)

Why does this fail?

2010-06-17T17:40:00.002-04:00

This code throws an InvalidCastException, on the grounds that you can't convert a DateTime to anything else but a string. But I didn't think I was converting a DateTime: I thought I was converting a nullable DateTime.

DateTime? nDate = new DateTime? (DateTime.Today);
Convert.ChangeType(nDate, typeof(DateTime?));

Tomorrow I will research this.

ETA: The answer.

Advice to New Programmers, Part I

2010-06-02T12:49:00.006-04:00

I was talking to someone who was a professor of Management Information Systems at a local university not too long ago. We were talking about his students and how they would do at their first jobs.

Now, I have quite a bit I can say on the subject of new programmers and mistakes they tend to make, most of which I know from having made them myself and taken the consequences. But I'll spare you that (for now) and just list some books that young programmers ought to read:

The Programmer's Survival Guide, by Janet Ruhl. This is over 20 years old, and much of what it says about the IT environment is extremely dated. Network management and web page design had barely been conceived when this was written. Nonetheless, the advice on legacy code, legacy people, getting along with your peers, and relating to management is still quite sound. Ms. Ruhl has since written The Computer Job Survival Guide, which I have not read, but which supposedly has updated advice.

Games Mother Never Taught You, by Betty Lehan Harragan. This was written, I was surprised to discover, back in 1977. The world has changed (in spots) since then. If you find the idea of office politics uncomfortable, this book can scare you to death: its tone is harsh, and the picture it paints an intimidating one. But you still ought to read it. Not all organizations work like the ones described in this book, and not all those that do are like that on the lower levels, where the programmers are. But I've seen things in my career that only made sense in light of what this book teaches.

Keep the Joint Running, a regular column by Bob Lewis of IT Catalysts. Possibly the most sensible man currently writing about information systems (as opposed to pure programming), Lewis consistently gives good advice to both IT workers and business managers. He's entertaining as well as insightful, and leaves you with the sense that you've both gained something useful and learned to see a little more clearly.

LINQ challenge - an answer

2010-05-09T18:18:00.007-04:00

Someone asked for an answer to the LINQ challenge I posted a while back. The following is an answer to the "find all descendants of a given Parent" part of the challenge. It cheats a little, in that it makes use of a variable external to the method (stack), but it prints out the descendants of whatever parent you feed it, so that's good.

This is not the only way to get an answer to this particular problem, and I certainly don't guarantee it's the best.

Incidentally, if anybody knows a utility for HTML-formatting code examples in a Blogger-friendly way, I'd like to hear about it.

Assume a class called ForeignKey that has two fields, both strings, called ParentTable and ChildTable. Assume also a variable list
defined earlier in the routine, that is declared as List<ForeignKey>.

Action<string, int> f = null;

Stack<string> stack = new Stack<string>();

f = (table, level) =>
{

stack.Push(table);

list.Where(fk => fk.ParentTable.Equals(table))

.Where(fk => stack.Contains(fk.ChildTable) == false)

.ToList()

.ForEach(fk=> {

Console.WriteLine("{0}: {1}", level, fk.ChildTable);

f(fk.ChildTable, level + 1);

});

stack.Pop();

};

You might be curious about what the level and stack variables are doing here. The level parameter is so we have some way to indicate how many levels deep we are: otherwise, we'd just be printing table names and not know where in the structure we were. But if you don't care, leave it out.

The stack variable is there to guard against "loops", where a "descendant" of one parent is ultimately itself an ancestor of that parent. If your hierarchy is one that cannot have any loops (say, a family tree) then you don't need to guard against them. But it's a decent idea to do so anyway, in case your data has been corrupted.

When you can do without foreign keys

2010-05-06T14:47:00.000-04:00

I had a boss once who said, after I'd reported that a database design was done except for the foreign keys, "Don't do that -- just a bunch of overhead, and if you code it right you don't need them."

I didn't say anything, but I remember thinking, "I had a ten year career fixing bad data! A database that can defend itself against corruption is a wonderful thing!"

Someone else recently said to me that they didn't use to believe in foreign keys for the same reason: if you write your business layer right, you don't need them to protect your database. So the following is my summary of when you can get away with not having foreign keys in your database:

If you are the only programmer on the app;
If you are the only programmer who ever has or ever will work on this app;
If no apps not under your control can make any changes to your database;
If no ad-hoc changes or fixes are ever made to the database;
If you and your tester (who may also be you) are blessed with the following conditions:

Lots of experience;
Plenty of time;
Great thoroughness;
A good memory for detail;
Perfect understanding of both the database and app design and of the business problem

... then you can do without foreign keys.

But -- seriously, I'm asking -- why would you want to?

Why musicians make good programmers

2010-04-21T11:38:00.000-04:00

I was chatting to someone the other day and we got talking about the other things we did besides programming. It turns out that both of us were musicians. I got to thinking and realized that I knew at least six technical people who were also musicians -- and pretty good ones, at that.

It's an old maxim that musicians are likely to make good programmers, but most of the time the reasons why it might be true are left unsaid. I've received the impression that the association is something like music --> rhythm --> counting --> math --> programming.

Personally, I think the reasons are quite other. As someone who's spent a long time doing both, I think that what programming and instrumental music performance study have in common is a certain set of mental skills. In both cases, you have to take something that's quite abstract (and perhaps only vaguely conceived), flesh out its shape in your mind, take it apart, put it back together, and then translate it into something concrete: property definitions, muscle movements, rises and falls in volume. There's a degree of analytical skill, pattern-matching, and pattern-mapping that's required in both.

Relational Integrity and Candidate Keys

2010-04-02T14:13:00.000-04:00

On my current project, we're not using natural, semantic keys: we're using Guids for primary keys and enforcing unique constraints on the combination of columns that make up the candidate keys. And this is fine for most purposes, but there's an aspect of relational integrity that gets left out.

Consider a system that schedules events within multiple buildings for multiple organizations. Some tables might include the following:

OrganizationalSchedule

  ScheduleId   PRIMARY KEY

  OrganizationId

  FiscalYearId

Speakers

  SpeakerId PRIMARY KEY

  OrganizationId 

  Name

Event

  EventId PRIMARY KEY

  OrganizationalScheduleId

  SpeakerId 

  Date

Assume the obvious foreign key relationships and implied Organization parent table.

Now, you will want your SpeakerId in the Event table to be of a speaker whose Speaker record has the same Organization Id as the Organizational Schedule record does. But how are you going to relate the records in order to assure this?

In SQL Server 2008, I don't think you can, not directly, unless you're using natural keys as the actual primary keys. So I'm looking at check constraints that use UDFs and the possibility of triggers.

Something to think about

2010-03-24T13:54:00.000-04:00

Let's not have any misunderstandings: I think this is quite ingenious. It taught me about something that I didn't know could be done. I intend to study it and learn how it works.

However, I also think it's the least maintainable solution to a simple SQL problem that I have ever seen.

First Monday of Daylight Savings Time, 2010

2010-03-15T09:55:00.001-04:00

My colleague Lydia calls today, "National Be-An-Hour-Late-To-Work Day".

A challenge for you LINQ experts

2010-03-10T17:30:00.000-05:00

Imagine a simple, recursive parent-child relationship, modeled in data. The class definition would be:

class Relationship
{
public int ChildId { get; set; }
public int ParentId { get; set; }
}

You have a collection of these things. Let's define it as an IEnumerable and call it c.

The challenge:
1. Can you, with one LINQ query, find all the elements that are descendents of a given parent?
2. (Bonus points) Can you, with one LINQ query, find all "loops" in the collection, where an element is ultimately descended from itself?

How could this be done -- efficiently -- in .Net?

2010-03-03T12:16:00.002-05:00

As you can tell if you check out the links on the right, I had a previous career as an IBM AS/400 specialist. Now, I have a whole rant on the subject of What I Miss About The Bad Old Days, that goes into things the AS/400 did right, but this post is about one thing in particular: level checks.

A program written on the AS/400, when compiled, contained information about the database tables it had been compiled against. Specifically, for each table or view (we called them different things, but that's what they were), it contained a guid-like string that represented the structure and some of the attributes of the table / view as it had been at compile time. If you attempted to run the program against a different table, or the same table after structure changes, the level ids wouldn't match, and the program wouldn't run. This was called a level check.

I'd like to be able to do something similar with a .Net application and the database it was written for. Specifically, I'd like for an application to be able to tell that the stored procedure it's about to call isn't the same procedure that it was compiled for. Oh, the specifics of the change may or may not matter: what I want is to have some flag raised that says: something has changed since we finalized this release.

This would be handy in sorting out problems with installation packages and incorrect deployments. I'll readily admit that if you had to contend with this during development, it would drive you crazy. But once you think you have everything finalized, if you could turn on this integrity check, it would be very handy.

What's the dividing line ...

2010-02-23T12:59:00.000-05:00

... between a referential integrity issue and a business logic issue?

Assume tables A and B with fields A.RefId and B.RecordId. There's a foreign key relationship from A.RefId --> B.RecordId.

Let's say the values of A.RefId need to be further restricted: only values of B.RecordId where B.TypeId = 3 can be used.

Is this a business logic issue, or a referential integrity issue?

If it's business logic, does it have any place in the database?

I'd be curious to hear what people think.

Why can't I ...

2010-02-22T14:12:00.001-05:00

... set up a foreign key relationship in SQL Server 2008 where the parent is a view with a unique index set up over it?

Unless I'm much mistaken, you can't do this.

You can achieve the same result with triggers, but:

A lot of people hate triggers;
You have to code the things, which introduces possible errors, depending on how sharp the coder is and how well he or she knows trigger behavior. Foreign key relationships are hard to get wrong.
ETA: You also have to set up triggers on both sides of the relationship, if you want to constrain both tables. With a foreign key, the mutual constraining is automatic.

Why do I want to do this? Well, I have a table where a certain field can be any value from the Id column of Table 2, provided [Table 2].TypeId = 3. I'd love it if the database would enforce this. But unless I can persuade folks that a trigger is an acceptable choice in this situation, it'll have to be done in code.

Useful Things to Know, #424

2010-02-18T14:35:00.000-05:00

This is an oldie I've been meaning to post for years.

Assume a variable n of an enumerated type t. Let's say t has the traditional values Red, Blue, and Green.

n.ToString("g") produces a string equal to the name of the enumeration value: Green.
n.ToString("d") produces a string equal to the underlying value of the enumeration: 2.

Plain n.ToString() acts like n.ToString("g")

Why is this useful to remember? Because if you ever have to build a filter string in the form "ColorId = value", and your value is an instance of an enumeration, you can build the string like so:

string filter = String.Format("ColorId = {0:d}", n);

I've seen people handle this a number of ways, including casting back to Int32 and so forth, but this is definitely the shortest, tidiest way.

Graceful Failure

2010-02-12T19:13:00.000-05:00

I've been thinking lately about the semantics of graceful failure.

If your software is customer-facing, you don't want an internal error to cause it to spew internal details at the consternated user. That is, among other things, a security flaw. But, at the extreme other end, you don't want it to be pretend that everything is hunky-dory when it isn't. It shouldn't say that a transaction completed when it didn't, and it shouldn't pretend that no results were found when it couldn't successfully complete the call to retrieve them.

If you are designing a web service, and the service does not convey error messages to the user, what should it convey when it is unable (due to internal error) to complete its business? I would have voted for null, but for Get-type actions, null is sometimes used to mean, "Nothing found" which is not the same thing.

Perhaps having the web service throw an error for the user to handle *is* the best solution.

Serializing a Null

2010-02-07T19:23:00.000-05:00

What do you suppose happens when you pass a null reference to XmlSerializer.Serialize as the object to be serialized?

You'd probably think an error, or perhaps that nothing would be written to the stream to which the object is to be serialized. Instead, you get something like this, depending on what the type is that you initialized the serializer with (I used a List<string>):

<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:nil="true" />

In other words, you get an empty fragment, an element with xsi:nil="true", an empty (in this case) list.

Passing a non-null but empty List<string> gave me this fragment:

<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />

So that xsi:nil is very important: it's the difference between an object with no elements and an object that is itself null.

I would not have guessed you could serialize a null, but you can.

Useful Things to Know, #423

2009-09-13T12:34:00.002-04:00

You can't use extension methods to provide a better .Equals method. Even if your Equals method takes an instance of the actual class as a parameter rather than System.Object, the innate method of the class will be used before yours.

Create DDL from an XSD file, Part II

2008-01-28T13:18:00.000-05:00

As requested, here is the stylesheet for creating a series of SQL DDL statements from a DataSet schema file. The results may require some tweaking, depending on the structure of your schema. If you have a "collection"-type element that has no other purpose but to enclose multiple instances of a sub-element, you may wind up with a superfluous table. That was something I couldn't work around without procedural code.

Good luck with it, and let me know how it works out.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
               version="1.0"
               xmlns:xs="http://www.w3.org/2001/XMLSchema"
               xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xsl:output method="text" omit-xml-declaration="yes" />
  <xsl:template match="/">
    <xsl:text>
-- ---------------------------------------------------------------
-- DDL generated from a .Net DataSet XSD via XSLT 
-- Stylesheet by Ann Lewkowicz, 2007
-- ---------------------------------------------------------------
</xsl:text>
    <xsl:apply-templates />
  </xsl:template>
  <!-- Get schema nodes from this schema and any included schemas -->
  <xsl:variable name="contents" select="/|document(//xs:include/@schemaLocation)" />
  <xsl:variable name="complexTypes" select="$contents//xs:complexType[@name]" />
  <!-- Match data table elements -->
  <xsl:template match="*" >
    <xsl:for-each select="$contents" >
      <xsl:apply-templates select=".//xs:element" />
    </xsl:for-each>
  </xsl:template>
  <!--xsl:template match="xs:element[xs:complexType]"-->
  <xsl:template match="xs:element">
    <!-- Get the complex type that defines it, if any -->
    <xsl:variable name="complexType" select="xs:complexType[not(@name)]|$complexTypes[@name=current()/@type]" />
    <xsl:if test="$complexType">
      <!-- Only create a table if the table has at least one simple type'd
    element or attribute.
  -->
      <xsl:variable name="fields"
       select="$complexType/xs:sequence/xs:element[not(@type=$complexTypes/@name)]|$complexType/xs:attribute|$complexType/xs:simpleContent|$complexType/xs:simpleContent/xs:extension/xs:attribute" />
      <xsl:if test="$fields">
        <!-- table name -->
        <xsl:variable name="tableName" select="@name" />
        <!-- Generated primary key name.  We may not use this, depending on
     whether a primary key is declared in the xsd.  -->
        <xsl:variable name="generatedKey" select="concat($tableName, '_id')" />
        <!-- Create statement -->
        <xsl:text>CREATE TABLE dbo.[</xsl:text>
        <xsl:value-of select="$tableName" />
        <xsl:text>] &#10;( &#10;</xsl:text>
        <!-- Get primary key declaration, if any, and use that to determine
     whether to create an Id field -->
        <xsl:variable name="primaryKey"
         select="//xs:key[@msdata:PrimaryKey='true'][xs:selector[@xpath=concat('.//mstns:',$tableName)]]" />
        <!-- Insert ID field -->
        <xsl:if test="not($primaryKey)" >
          <xsl:text>&#9;[</xsl:text>
          <xsl:value-of select="$generatedKey"/>
          <xsl:text>] int Identity(1,1),&#10;</xsl:text>
        </xsl:if>
        <!-- If table is a nested table, insert parent table Id field -->
        <!--   '<xsl:value-of select="$contents//xs:element[@type=current()/ancestor::*/@name]/@name" />'-->
        <xsl:variable name="ancestorTables"
         select="ancestor::xs:element[@name][not(@msdata:IsDataSet)][xs:complexType[xs:sequence[xs:element[@type]] or xs:attribute[@type] or xs:simpleContent]]|$contents//xs:element[@type=current()/ancestor::*/@name]" />
        <xsl:if test="$ancestorTables">
          <xsl:variable name="foreignKey" select="concat( $ancestorTables[1]/@name, '_id' )" />
          <!-- Build foreign key field name -->
          <xsl:text>&#9;[</xsl:text>
          <xsl:value-of select="$foreignKey" />
          <xsl:text>] int</xsl:text>
          <!-- Keywords and key name -->
          <xsl:text> CONSTRAINT FK_</xsl:text>
          <xsl:value-of select="$tableName"/>
          <xsl:text>_</xsl:text>
          <xsl:value-of select="$ancestorTables[1]/@name" />
          <!-- Build reference to parent table -->
          <xsl:text> FOREIGN KEY REFERENCES dbo.[</xsl:text>
          <xsl:value-of select="$ancestorTables[1]/@name" />
          <xsl:text>]</xsl:text>
          <!-- Name of key field of parent -->
          <xsl:text> ( [</xsl:text>
          <xsl:value-of select="$foreignKey" />
          <xsl:text>] ),&#10;</xsl:text>
        </xsl:if>
        <!-- Iterate through fields -->
        <xsl:for-each select="$fields[not(@type=$complexTypes/@name)]">
          <!-- white space -->
          <xsl:text>&#9;</xsl:text>
          <!-- Field name -->
          <xsl:text>[</xsl:text>
          <xsl:choose>
            <xsl:when test="@msdata:ColumnName">
              <xsl:value-of select="@msdata:ColumnName" />
            </xsl:when>
            <xsl:when test="@name">
              <xsl:value-of select="@name" />
            </xsl:when>
            <xsl:when test="local-name()='simpleContent'">
              <xsl:value-of select="concat(parent::*/@name,'_Text')" />
            </xsl:when>
          </xsl:choose>
          <xsl:text>] </xsl:text>
          <!-- Type -->
          <xsl:choose>
            <xsl:when test="@type">
              <xsl:call-template name="Types">
                <xsl:with-param name="type" select="@type" />
              </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
              <xsl:call-template name="Types">
                <xsl:with-param name="type" select="xs:extension/@base" />
              </xsl:call-template>
            </xsl:otherwise>
          </xsl:choose>
          <!-- Handle identity columns -->
          <xsl:if test="@msdata:AutoIncrement='true'">
            <xsl:text> IDENTITY(</xsl:text>
            <xsl:choose>
              <!-- If a seed was specified -->
              <xsl:when test="@msdata:AutoIncrementSeed">
                <xsl:value-of select="@msdata:AutoIncrementSeed"/>
              </xsl:when>
              <xsl:otherwise>
                <xsl:text>1</xsl:text>
              </xsl:otherwise>
            </xsl:choose>
            <xsl:text>,</xsl:text>
            <xsl:choose>
              <!-- If a step is specified -->
              <xsl:when test="@msdata:AutoIncrementStep">
                <xsl:value-of select="@msdata:AutoIncrementStep" />
              </xsl:when>
              <xsl:otherwise>
                <xsl:text>1</xsl:text>
              </xsl:otherwise>
            </xsl:choose>
            <xsl:text>)</xsl:text>
          </xsl:if>
          <!-- Optional elements become nullable columns -->
          <xsl:if test="@minOccurs=0"> NULL</xsl:if>
          <!-- separator -->
          <xsl:if test="position()&lt;last()">,</xsl:if>
          <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
        <!-- Primary key.  We determine the primary key by selecting an xs:key
     element that has the msdata:PrimaryKey attribute flag, that has a 
      child xs:selector element that references our table name.  -->
        <xsl:choose>
          <xsl:when test="$primaryKey">
            <!-- White space and keywords -->
            <xsl:text>&#10;&#9;PRIMARY KEY ( </xsl:text>
            <!-- Go through all the fields -->
            <xsl:for-each select="$primaryKey/xs:field">
              <xsl:text>[</xsl:text>
              <xsl:value-of select="substring-after(@xpath, 'mstns:')" />
              <xsl:text>]</xsl:text>
              <xsl:if test="position()&lt;last()">, </xsl:if>
            </xsl:for-each>
            <!-- Closing parenthesis -->
            <xsl:text> )</xsl:text>
          </xsl:when>
          <xsl:otherwise>
            <!-- White space and keywords -->
            <xsl:text>&#10;&#9;PRIMARY KEY ( [</xsl:text>
            <xsl:value-of select="$generatedKey" />
            <xsl:text>] )</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
        <!-- closing parenthesis and white space -->
        <xsl:text>&#10;)&#10;&#10;</xsl:text>
      </xsl:if>
    </xsl:if>
    <xsl:apply-templates />
  </xsl:template>
  <!-- Process the value of the "type" attribute of an element.  -->
  <xsl:template name="Types">
    <xsl:param name="type" />
    <xsl:choose>
      <!-- Process ordinary simple types -->
      <xsl:when test="substring-after($type,':')='int'">int</xsl:when>
      <xsl:when test="substring-after($type, ':')='string'">
        varchar<xsl:if test="local-name()!='restriction'">(255)</xsl:if>
      </xsl:when>
      <xsl:when test="$type='money_type'">money</xsl:when>
      <xsl:when test="substring-after($type,':')='nonNegativeInteger'">int</xsl:when>
      <xsl:when test="substring-after($type,':')='date'">datetime</xsl:when>
      <xsl:when test="substring-after($type,':')='boolean'">bit</xsl:when>
      <xsl:when test="substring-after($type,':')='decimal'">decimal(19,9)</xsl:when>
      <xsl:when test="substring-after($type,':')='gYearMonth'">datetime</xsl:when>
      <!-- If a type is not one of the simple types above, look for a 
    defined simple type and reference its base.  This is recursive,
    so a defined simple type that restricts another defined simple
    type will eventually reach an xsl: data type -->
      <xsl:otherwise>
        <xsl:apply-templates select="$contents//xs:simpleType[@name=$type]" mode="derivedType" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  <!-- Handles restriction element of xs:simpleType declaration -->
  <xsl:template match="xs:restriction" mode="derivedType" >
    <xsl:call-template name="Types">
      <xsl:with-param name="type">
        <xsl:value-of select="@base"/>
      </xsl:with-param>
    </xsl:call-template>
    <xsl:apply-templates mode="derivedType" />
  </xsl:template>
  <!-- Handles max length constraint (for strings) of xs:simpleType declaration -->
  <xsl:template match="xs:maxLength" mode="derivedType" >
    (<xsl:value-of select="@value" />)
  </xsl:template>
  <!-- Create foreign keys.
  For this, we're processing xs:keyref elements.  We determine the 
  child table name from the selector child of the keyref, and the
  parent table name from the key specified in the xs:keyref as the
  key of the parent table.  
-->
  <xsl:template name="ForeignKey" match="xs:keyref[@refer]" >
    <!-- variables -->
    <xsl:variable name="parentKeyName" select="@refer" />
    <xsl:variable name="childTable" select="substring-after(xs:selector[1]/@xpath, './/mstns:')" />
    <xsl:variable name="parentTable" select="substring-after(//xs:key[@name=$parentKeyName][1]/xs:selector[1]/@xpath, './/mstns:')" />
    <!-- Opening keywords -->
    <xsl:text>ALTER TABLE dbo.</xsl:text>
    <!-- Child table being changed -->
    <xsl:value-of select="$childTable" />
    <!-- Whitespace and constraint keywords -->
    <xsl:text>&#10;&#9;ADD CONSTRAINT </xsl:text>
    <!-- Make constraint name -->
    <xsl:text>[FK_</xsl:text>
    <xsl:value-of select="$childTable" />
    <xsl:text>_</xsl:text>
    <xsl:value-of select="$parentTable" />
    <xsl:text>] </xsl:text>
    <!-- Foreign key keyword and parenthesis -->
    <xsl:text>FOREIGN KEY &#10;&#9;( &#10;</xsl:text>
    <!-- Fields in foreign key -->
    <xsl:for-each select="xs:field">
      <xsl:text>&#9;&#9;[</xsl:text>
      <xsl:value-of select="substring-after(@xpath, 'mstns:')" />
      <xsl:text>]</xsl:text>
      <xsl:if test="position()&lt;last()">
        <xsl:text>,</xsl:text>
      </xsl:if>
      <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
    <!-- Closing parenthesis and REFERENCES keyword -->
    <xsl:text>&#9;) &#10;&#9;REFERENCES </xsl:text>
    <!-- Referenced table name -->
    <xsl:text>dbo.</xsl:text>
    <xsl:value-of select="$parentTable" />
    <xsl:text>&#10;&#9;( &#10;</xsl:text>
    <!-- Referenced fields -->
    <xsl:for-each select="//xs:key[@name=$parentKeyName][1]/xs:field">
      <xsl:text>&#9;&#9;[</xsl:text>
      <xsl:value-of select="substring-after(@xpath, 'mstns:')" />
      <xsl:text>]</xsl:text>
      <xsl:if test="position()&lt;last()">
        <xsl:text>,</xsl:text>
      </xsl:if>
      <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
    <!-- Closing parenthesis and white space-->
    <xsl:text>&#9;) &#10;&#10;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

Create DDL from a DataSet XSD file

2007-03-17T15:02:00.001-04:00

Recently I started dealing with a web service that came with a series of XSDs. We intend to store the data coming back from the web service, more or less in the form in which it arrived. That meant creating a series of database tables that would look very similar to the XSD. The XSD was long and complicated. I really, really didn't want to have to hand-create SQL tables from it. Surely, I thought, surely somebody has written something to create DDL from an XSD.

Well, yes, they have, but they want money for it, so I did it myself. I wrote an XSLT stylesheet that transforms a DataSet XSD into a series of DDL statements. I based it on a DataSet because the additional msdata: attributes incorporated in the DataSet and the DataSet designer let you specify auto-numbering for columns, designate one key as the primary key, and so on. Plus, the DataSet designer makes it easy to specify foreign keys and relationships. Turning the XSD into a DataSet just required some cutting and pasting, wrapping complex types with xs:element tags to create table definitions, and adding key fields. The DataSet designer was very nice about converting from one alias for the XSD namespace to another during a paste between documents.

I'm proud of it. It creates tables, declares primary keys, handles identity columns, and creates foreign key relationships. It supports derived simple types, drilling down to their base types and handling maxLength restrictions. Additional things that could be done with it (that I didn't need) include turning non-primary key key definitions into unique indexes, creating check constraints from some of the other restrictions put on derived types, and turning enumerations into lookup tables and usages of enumerations into references to the lookup tables.

This is the kind of thing that you don't need until you need it and is very helpful then. If people are interested in seeing some of it, let me know.

ETA: The XSLT is here.

Merging Side-by-Side Tables

2007-01-04T15:34:00.000-05:00

One thing I have always been frustrated with in ASP.Net is that you can't use templated bound controls to produce side-by-side lists within a table. The problem comes from the fact that for each row you need to render the cells from list A, then the cells from list B, and so on. If you want, for example, email addresses to be listed vertically on the left and phone numbers listed on the right, within a single table, you have three options:

Display each list in a single cell on a single row, perhaps using one of the list tags. No alternate row styling is available, and you're out of luck if your list needs to be displayed in more than one column.
Use templated databound controls to embed subtables within single cells on a single row. If you're fastidious, tweak the margins of the subtable cells to keep the contents properly left-aligned. Live with the fact that unexpected content wrapping would destroy the illusion of synchronized lists and that any alternate row styling will be discontinuous with the parent table.
Manually build the cells and rows from the two data sources and add them to the outer table. Track alternate rows and set styles yourself.

Option 3 gives you the look you want plus better HTML, but it's a pain in the neck to do. So one thing I have always wanted to have is a way to merge tables horizontally. Bind your two lists to two separate templated bound controls, and then combine them, matching rows and combining cell collections. To do it, you'd probably have to render each databound control to a string and then merge the generated HTML, which wouldn't be as hard as it sounds if you remember that valid HTML is supposed to be a variant of XML.

Since I am currently somewhat idle I may spend time putting this together. Let me know if you want me to post the results.

AppSettings and Visual Studio Test System

2006-11-17T19:13:00.000-05:00

I started using Visual Studio Test System today, and quickly ran into a problem with my application's app.config not being available to my test project. But a few keystrokes brought me to Todd Girvin's AppSettings and Visual Studio Test System. Exactly what I needed to know.

Custom Properties on Typed Datasets

2006-11-01T17:17:00.000-05:00

In .Net 2.0 it's possible to define custom properties for a typed Dataset and retain them when the code is regenerated, thanks to the wonders of partial classes. I was writing a UI prototype recently for a Windows application, and used typed Datasets and TableAdapters to speed up development.

It's always been possible to define an expression for a calculated column in a DataTable, using a syntax that I think is supposed to remind us of SQL. But you're limited in what you can put in there, restricted to a few defined functions. To pick one example, there is, as far as I can tell, no way to specify that the current date be used as one of the expression's operands.

This led me to writing a custom property for my typed DataRow, one that used some business rules about effective dates and anniversary dates to calculate a duration. In Object Explorer, this property looked like all the others. Could I use it in Windows databinding?

The answer appears to be "No", although I haven't found anything written about it one way or the other. But it won't appear in the Property window's field picker, and specifying it leads to an runtime error. I don't know the reason for sure, but I suspect it's because the object you're connecting your controls to when you bind to a DataTable is really a DataView. A custom property on the typed DataRow wouldn't be accessible through a DataRowView.

And this is a pity. When you're writing a little three-day minimum-investment project like my prototype, you want an easy, clean solution, one where you do the hard stuff once if at all and don't lock yourself into any particular architecture. A typed DataRow that could expose bindable custom properties would be a good stand-in for the custom business objects that it's too early in the project to create.

Active Directory and User Roles

2006-10-24T16:57:00.000-04:00

The WinForms app I'm working on has two modes: edit and read-only. It's all or nothing: either you can edit everything or you can edit nothing. If you're not allowed even read-only access you can't run the app.

So I am faced with the challenge, familiar to many of you, of providing read-only vs. edit access in a nice, clean way. There's an article by Irena Kennedy called Granular Role Based Security Without a Line of Code: Attribute-Based Authorization that I really like. It uses attributes to indicate default values to be set for object properties based on their authorization to specific actions in Authorization Manager. I like this idea. I could put something in a post-InitializeComponent method in my form base class that ran through the controls declared in its descendant and set default properties based on the role membership of the user.

But I'm using AD and WinForms. So how do we shoehorn AD groups into the Identity, Principal, and Role model? The user's groups seem obvious choices for roles, but the WindowsIdentity object already has its own collection of groups. I thought about loading the AD groups of each user into the Roles collection of a GenericPrincipal, but it seems counter-intuitive to replace an authenticated WindowsPrincipal with an unauthenticated GenericPrincipal.

So I was thinking about turning around and heading in the opposite direction. Maybe I should create Attributes that didn't pretend to be other than AD-specific: IsInGroupAttribute(string groupName, string propertyName, object defaultValue) and maybe NotInGroupAttribute(string groupName, string propertyName, object defaultValue). When processed by the initialization routine we would just do an AD lookup (cached, of course) and check group membership.

Go on, tell me why this is a bad idea.

To Refactor or not to Refactor

2006-10-05T16:58:00.000-04:00

I created a WinForms combo box that restricts what options you can choose. This is for the situation where only a few of the options are valid choices at this point in your process, but the data you are binding to includes other values that must appear in the combo box to avoid binding errors. For example, it might be possible to manually change the status of any order only to Canceled or Hold, but the application allows the users to query and view orders of any status.

(ETA: we're assuming in-place editing, here. The problem goes away if you have separate read-only "Status" and write-only "New Status" controls. I'm not sure if you can do that with databinding alone.)

I created a stand-alone combo box version of this -- the RestrictedComboBox -- and also a DataGridView version. After a day or so of work it became obvious that rather than building column, cell, and editing control classes on top of the RestrictedComboBox, it made more sense to subclass the ComboBox versions of these classes.

This means that the RestrictedComboBox and DataGridViewRestrictedComboBoxEditingControl code files are made up of almost identical code. Both of them are descended from the ComboBox (directly in the case of the RestrictedComboBox, more distantly in the case of the other) and do their thing by interfering with combo box item selection and drawing. I'm toying with refactoring the common logic into a helper class. What do you think -- go to the trouble or not go to the trouble?

DataGridView, DateTimePicker, and Nullable Types

2006-09-11T16:13:00.000-04:00

I'm building a WinForms app with C# 2.0, and I need to display some data in a grid. I want to use the new DataGridView, since it supercedes the DataGrid.

One of the fields in the data being bound is a DateTime. I would like to display a DateTimePicker in that column when it's being edited.

That part isn't hard. One Yildirim Kocdag posted an article on CodeProject that includes a DataGridViewCalendarColumn. It's simple, yet effective. One hurdle vaulted.

But, the DateTime value can sometimes be null. I want the DateTimePicker to handle nulls appropriately. Once again to CodeProject: Claudio Grazioli wrote an article on implementing a nullable DateTimePicker. Two hurdles jumped, now. All I need to do is adapt the DataGridViewCalendarColumn to use a nullable DateTimePicker. This requires some changes to the nullable DateTimePicker and some additions to the DataGridViewCalendarColumn, but eventually it works.

The support for it in Visual Studio 2005 is beautiful, just beautiful: all I had to do was add a reference to the project in which I implemented the new controls and my DataGridViewCalendarColumn appears in a list with all the other DataGridViewColumns when you edit the columns of a DataGridView. I was so surprised to see it there already that I actually canceled out of that operation and double-checked that it really was my DataGridViewCalendarColumn that was appearing. It was. Three cheers for Visual Studio and three hurdles jumped.

So I try running the app to see how it works in context. For some reason it takes three clicks on a DataGridView cell to get an editable textbox, and it won't let me simply type in a date, but if you click on the Picker you can pick a date without difficulty. Clicking away from it updates the date in the data source. We're partway over the last hurdle: in mid-air, you might say.

But, I try to change an existing date to null and save that. I'm able to delete the contents of the DateTimePicker without problems, but when I click away the original date comes back. I do a little debugging and find that it's refusing to be set to the value I gave it for a null DateTime.

A bit of background: I hadn't decided just what flavor of nullable type I was going to use for my business objects, so in these initial forays I was simply using DateTime.MinValue as a proxy for Null (or DBNull). I forgot that the DateTimePicker doesn't like that: it only supports dates going back to 1753. That's why the cell won't accept the new, empty value: deep down within a Try... Catch, it's rejecting DateTime.MinValue with a supercilious message. It's serious about it, too: a quick check with Reflector reveals that 1/1/1753 is hardcoded in several places to make sure that no matter what anybody does with the MinValue property of the DateTimePicker, it doesn't go below 1753.

I momentarily consider expanding my definition of Null to include 1/1/1753 but immediately abandon that as A Very Bad Idea. So it looks like I have to make up my mind about nullable types right now.

So, to Nullable<T>. I love generics. But I already know that there's no easy conversion between DBNull and Nullable<T>. A little Googling reveals that WinForms databinding doesn't go out of its way to support Nullable<T>, either. In fact, it doesn't support it at all! But, again, there is a workaround! The very helpful Bruusi has posted a blog entry titled Data Binding and Nullable types in WinForms.NET.

The secret, it appears, is an extender provider. But his example is for binding to a TextBox. Can an extender provider be made to work with the DateTimePicker? In a DataGridView?

I don't know. But tune in tomorrow. I'll let you know.

"Just like CodeSmith, only more complicated"

2006-09-05T19:22:00.000-04:00

That's my initial response to Microsoft's Guidance Automation Toolkit. It combines Visual Studio add-ins with code templates that look remarkably like CodeSmith's. Programming of "recipes" is done with an XML-based declaration language. "Actions" encapsulate project and project item manipulation as well as code generation from the previously mentioned templates. Wizards organize it all for the user. Declarations in an xml configuration file add commands to the familiar project and item context menus. It's completely extensible, too.

Getting into it is more difficult than it should be, since the available labs aren't from the final version of the product and don't always match the sample files you're supposed to manipulate. Some of the best stuff -- the actions to generate and insert code from templates -- isn't documented at all. But it's worth working with. It brings the promise of integrating your architecture standards and your preferred formats for boilerplate code into the Visual Studio environment for others to use.

CodeSmith 2.6 (Freeware) Still Available

2006-06-25T19:30:00.000-04:00

CodeSmith, which many of us depend on, has become a commercial tool, and I wish them plenty of success with that. Every development group should have it in its arsenal of tools.

But for those of us not able to authorize a corporate purchase, the freeware version is still available. It's just a little hard to find.

VS 2003 Property Expansion in Debugger Windows Bug

2006-06-18T17:04:00.000-04:00

I found a solution on the web to an obscure bug in VS 2003, and since the solution was many Google pages deep I thought I'd post it here.

People working with VS 2003 solutions with multiple projects sometimes encounter a bug where the property expansion of objects viewed in the debugger windows no longer works. The object may or may not have a plus sign when viewed in the Watches, QuickWatch, or Locals window, but if you click the plus sign, it merely disappears. The properties of the object are not displayed and there is no easy way to inspect them. This seriously impairs the debugger's usefulness as a tool.

The root cause of this is having a project in your solution that doesn't have any classes. Build projects and catchall projects with non-code items often fit the bill. Adding a dummy class -- just a skeletal declaration -- makes the problem go away.

Computer Recycling Collection Event

2006-06-18T16:54:00.000-04:00

If you live in Hamilton County and you've been accumulating bits of old computers because you're too conscientious to throw their lead-ridden carcasses into the dumpster, this is for you:

HCDOES Solid Waste Management Division is once again hosting a computer recycling event. The 6th Annual Computer Recycling Collection Event will be held July 11– 16, 2006 at Technology Recycling Group located in St. Bernard, Ohio. All businesses, schools, and non-profits must pre-register for the event. Residents do not need to pre-register. The registration deadline is June 9, 2006. Hamilton County Residents only.
http://www.keepcincinnatibeautiful.org

Client-side C#

2006-06-01T17:49:00.000-04:00

I've always had a soft spot for "little" languages and the translators that read them. "Little" languages typically compile to source code in some other language rather than to an executable. The first one of these I encountered was RPG/free, Paul Conte's innovative free-form, arbitrarily-long-variable-name version of RPG (so old at this point that I couldn't even find a link.)

I was reminded of this when I encountered Script#, by Nikhil Kothari. Quite simply, it allows you to write C#, complete with classes, inheritance, and strong typing, and then compile it into javascript. Because the source code is C#, you have the use of all the intellisense and other support in Visual Studio, as VS reflects on the core sscorlib assembly.

Selenium IDE and ASP.NET validators

2006-05-16T12:56:00.000-04:00

After reading Jess Tedder's praise of Selenium IDE, I went to give it a try. I liked it, thought it was very easy to use and very effective.

There were two problems I had with it: one, that there doesn't appear to be a way to stop a test once it is in progress without closing the IDE (a problem if you accidentally get yourself into a wait state for something that will never happen), and two, that there doesn't seem to be a straightforward way to test for the appearance of ASP.NET validator control indicators and messages on the page.

Validator indicators and messages are rendered on the page, but initially have their style.visibility attribute set to hidden. Indicators appear at the site of the validator control in the page, while messages are commonly dynamically collected displayed in validation summary controls, elsewhere on the page. Selenium has methods to test for the presence of text on a page, but since the messages and indicators are indeed on the page but not necessarily visible, you get a lot of false positives. I tried using methods that looked for attributes of specific elements, but ran into problems because it isn't always possible to predict what the id will wind up being for a control on an ASP.NET page, depending on what it's nested in and how the markup was generated. Indeed, some controls (like the <li> error message elements that are added to validation summary controls during validation) don't even get their own ids. Finding the control by its location rather than its id can be problematic in ASP.NET because since the error messages are added dynamically, you don't necessarily know where in the sequence the list entry with your message will be.*

I'm sure eventually someone will write a Selenium extension that will take care of this -- a text-on-page test that tests for the visibility of the text. It would be nice if it also allowed expressions for the search text. I might even try it myself once I get past my home programming backlog.

* Yes, ideally you'll know exactly which messages to expect and can predict where in the sequence the message you're testing for will fall. But the test you're running might be just for one function generating one message, and not concerned with nor wishing to specify what other validation messages might be visible.

Linking Wizard Controls

2006-04-29T20:10:00.000-04:00

.NET 2.0 has a wizard control, in which you define steps in markup and then step through them. Starting in December I was working at a client who needed a seven or eight step wizard, with each step having complex validating and navigation requirements. The user control in which we built this wizard became enormous and disorganized, as successive steps used different data structures and sprouted subroutines and event handlers. We wished we could make several sub-wizards of a few related pages each and link them together.

Well, you can, sort of. What you can do is implement a user control that contains a wizard control, implement whatever page-specific logic you can factor out of the big wizard, and then expose the WizardSteps collection as a property. Then you can have your parent user control (with the big wizard) load the user control, but add each step in the WizardSteps property to the WizardSteps collection of the big wizard. The steps will behave to the user as if they were defined in the big wizard, with their titles appearing as steps in the big wizard's sidebar, but the event handlers for all the controls will still be handled by the original user control.

To reference the big wizard, which is now the parent of the steps, set up a private property on the subwizard user control that references the Wizard property of one of the steps. This will now be the big wizard. The subwizard will now be inert and won't have a useful ActiveStepIndex or generate NextStepClicked events. If you need handlers for that in the subwizard user control you'll have to explicitly hook them up to your new Wizard property.

Client-side objects for server controls

2006-04-20T18:39:00.000-04:00

I had a conversation with Chris Peterson on Tuesday regarding the difficulty of manipulating the HTML controls inside composite or templated .NET controls with Javascript. The controls have ids that you can't easily predict unless you are generating the javascript from within the templated control, and even if you could predict them you'd be violating one of the rules of encapsulation, namely that code manipulating an object (your server control) shouldn't have to know the internals of that control.

Given that Javascript can create objects with arbitrary properties, it occurred to me that it would be possible to expose the objects in a .NET templated control as properties of a client-side object, making them available for manipulating by javascript not generated by the control. Validation controls in .NET 2.0 already do this, using the AddExpandoProperty method of the ClientScriptManager object. I see an area here for experimentation.

Geek 1:	Here's an LOL for ya... My co-worker is our VMS guy here, and programs in Perl in VI on the VMS server. I introduced him to Notepad++ today and syntax highlighting, and he almost had a heart attack, and can't stop thanking me... he was so amazed.
Geek 2:	His civilization has just been introduced to Fire.