Low Level Database Access

It is sometimes useful to send SQL commands directly to a database, or for other reasons to drop down to ADO.NET.  LightSpeed supports this through the IDataProviderObjectFactory interface and IUnitOfWork.PrepareCommand and FindBySql methods.

Creating ADO.NET Objects with LightSpeed

The LightSpeedContext.DataProviderObjectFactory property returns an IDataProviderObjectFactory for the context’s data provider.  You can use this to create ADO.NET connection, command and command parameter objects suitable to the database in use.

Note that connection objects are not automatically initialised with the context connection string.

Using ADO.NET Objects with LightSpeed

To associate an ADO.NET command with the connection and transaction of a unit of work, call IUnitOfWork.PrepareCommand.  This does not execute the command – you must still call ExecuteReader, ExecuteScalar or ExecuteNonQuery – but it prepares the command to run over the unit of work’s connection, and enrols it in the unit of work’s transaction if one is in progress.

If you want to materialise the results of a SQL command as entities, call IUnitOfWork.FindBySql.  In this case, the results of the command must include an Id column.