Databases

XML data islands and rowsets are handy for small amount of data. Handling larger datasets requires proper database support. In particular, I needed XSharper to install & initialize MS SQL databases (any other database with a .NET client may be used with client factor class name specified through factory attribute of database action).

The samples below assume that there is a default SQLExpress installation on the machine.

Creating a database:
 

Adding a table:

 

In Oracle case, this would look like

 

Please note that each statement is executed separately as a command, "GO" command from MS SQL batches is not currently allowed. Separate SQL statements need to be executed as separate <sql> actions.

Inserting records.

The usual INSERT SQL is always an option, but I find its syntax rather difficult to read or change (only in MS SQL 2008 there are some notable improvements). Instead there is sqlinsert action, which is similar to rowset and may contain CSV inside:

 

Getting the data back

This is easy:
 

produces
 

There are many attributes of sql action that can be used, for example, to save the data to a rowset for future processing using toRowsetId attribute, and so on.

Upsert

sqlinsert is getting particularly useful for "upsert", add a record only if no record already exists, and update otherwise. T-SQL verbosity in this case just hits the roof. In XSharper only a new field keys is added, to specify which columns are used to find the existing record.

For example, change address of John Smith to "50 Blackberry Rd, Ottawa, ON", if he does exist, and insert the record otherwise:

 

Generated syntax depends on the updateMode, with simple and msSql choices doing the usual "if (exists) update() else insert()", which is potentially unsafe if used outside of transaction with serializable isolation level (but probably fine more most scripts). Of the two, msSQL is a bit faster, and generates something like

 

There are also merge option invoking the new SQL MERGE statement, requiring MS-SQL 2008. To get XSharper to autodetect the database updateMode can be set to auto.

Of course, if just update is needed, it can be done with a parameterized query:

 

Transactions

And the last but not least, transactions can be done too (using the usual TransactionScope from .NET2). Transaction commits automatically if the block exits normally, and rollbacks if leaving the block via exception