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:

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=master;Integrated Security=True;" >
	<sql>CREATE DATABASE XTest</sql>
	<print>Database created</print>
</database>

Adding a table:

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=XTest;Integrated Security=True;" >
	<sql>
		create table Customers (
			customerId int identity(1,1) not null primary key,
			firstName nvarchar(50),
			lastName	nvarchar(50),
			address	nvarchar(50)
		)
	 </sql>
	<print>Table Customers created</print>
</database>

In Oracle case, this would look like

<database cs="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User Id=system;Password=123;" factory="Oracle.DataAccess.Client">
<sql>create table info (
	mother varchar2(100),
	child varchar2(100),
	bdob timestamp
	)</sql>
</database>

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:

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=XTest;Integrated Security=True;" >
	<sqlinsert table="customers" csvColumns="firstName,lastName,address">
		John, Smith, "32 Thornbush Court, Toronto, Ontario"
		Michael, White, "12/22 Yonge street, Toronto, Ontario"
		Melissa, Brown, "52 Parlament ave., Toronto, Ontario"
	</sqlinsert>
</database>

Getting the data back

This is easy:

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=XTest;Integrated Security=True;" >
	<sql outTo="^out" type="query">Select * from Customers</sql>
</database>		

produces

+------------+-----------+----------+--------------------------------------+
| customerId | firstName | lastName | address                              |
+------------+-----------+----------+--------------------------------------+
|          1 | John      | Smith    | 32 Thornbush Court, Toronto, Ontario |
|          2 | Michael   | White    | 12/22 Yonge street, Toronto, Ontario |
|          3 | Melissa   | Brown    | 52 Parlament ave., Toronto, Ontario  |
+------------+-----------+----------+--------------------------------------+

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:

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=XTest;Integrated Security=True;" >
	<rowset id="data" csvColumns="firstName|lastName|address" csvSeparator='|'>
		John| Smith|50 Blackberry Rd, Ottawa, Ontario
	</rowset>
	
	<sqlinsert 	table="customers"  
					rowsetId="data" updateMode="mssql" 
					keys="FirstName,lastName"  />

	<print outTo="^bold">Result:</print>
	<sql outTo="^out" mode="query">Select * from Customers</sql>
</database>

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

UPDATE [customers]
SET [address]='50 Blackberry Rd, Ottawa, Ontario'
WHERE ([FirstName] = 'John') AND ([lastName] = 'Smith')
IF @@ROWCOUNT=0
INSERT INTO [customers] ( [firstName], [lastName], [address] )
SELECT 'John','Smith','50 Blackberry Rd, Ottawa, Ontario'

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:

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=XTest;Integrated Security=True;" >
	<rowset id="data" csvColumns="firstName,lastName,address">
		John, Smith, "90 Main Street, Lake Placid, NY"
	</rowset>

	<foreach rowsetId="data">
		<sql outTo="rc" mode="scalar">
			<param name="@first">${firstName}</param>
			<param name="@last">${lastName}</param>
			<param name="@addr">${address}</param>

			UPDATE Customers
			SET Address=@addr
			WHERE firstName=@first AND lastName=@last
			
			select @@rowcount
		</sql>
		<print>Rows updated=${rc|'none'}</print>
	</foreach>

	<sql outTo="^out" mode="query">Select * from Customers</sql>
</database>

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

<database cs="Data Source=.\SQLEXPRESS; Initial Catalog=XTest;Integrated Security=True;" >
	<print>Before</print>
	<sql outTo="^out" mode="query">Select * from Customers</sql>
	<try>
		<transaction isolationLevel="serializable" timeout="00:05:00">
			<sql>
				UPDATE Customers
				SET Address='Test'
				WHERE lastName='Smith'
			</sql>
			<sleep timeout="00:00:05" />
			<throw>Oops!</throw>
		</transaction>
	</try>
	<finally>
		<print>After</print>
		<sql outTo="^out" mode="query">Select * from Customers</sql>
	</finally>
</database>