DataWings and SQL Server Identity Columns

SQL Server has the practical concept of identity – you specify a column in your table as being the identity column, and this column automatically gets populated with unique values. This mechanism is most commonly used for the primary key column in the table.

When you use such identity columns you are not allowed to provide a value yourself (at least not by default). This has been a major problem when using the DataBoy functionality in everybody’s favorite tool for data driven DataWings: oftentimes you need to set up known data consisting of several rows in different tables where the data is connected by primary key/foreign key associations. This simply hasn’t been supported in DataWings

Until now, that is: enter DataWings v 1.1. This version contains a few bug fixes, but apart from this version 1.1 introduces return value functionality. This is functionality for getting data back from the database as you insert it, and using this data in subsequent inserts or updates.

This new functionality hinges on the two new commands ReturnValue and BindColumn. I think that a couple of examples should make it clear what this is about. In these samples we assume the existence of two tables: Person with primary key column IdPerson and Address with foreign key column also named IdPerson which refers to the primary key of Person.

So, how to insert a person row and an address row connected to person row. Like this:

Guid personId = Guid.NewGuid();
Guid addressId = Guid.NewGuid();
DataBoy
    .ForTable("Person")
        .Row("Id", personId)
        .ReturnValue("IdPerson").ForImmediateUse()
    .ForTable("Address")
        .Row("Id", addressId)
        .BindColumn("IdPerson").ToLast()
.Commit();

The ForImmediateUser() and ToLast() methods are useful for quick usage where the associated columns are inserted directly after one another. The functionality also has the ability of naming return values for more complex usage scenarios:

Guid pId1 = Guid.NewGuid();
Guid pId2 = Guid.NewGuid();
Guid aId1 = Guid.NewGuid();
Guid aId2 = Guid.NewGuid();
DataBoy
    .ForTable("Person")
        .Row("Id", pId1).ReturnValue("IdPerson").AtKey("Person1")
        .Row("Id", pId2).ReturnValue("IdPerson").AtKey("Person2")
    .ForTable("Address")
        .Row("Id", aId1).BindColumn("IdPerson").To("Person1")
        .Row("Id", aId2).BindColumn("IdPerson").To("Person2")
    .Commit();

Get the bits here.