Data.Sql

Neptuo.Data.Sql is concept for simple and thin SQL data layer.

Here are some thoughts on design. Implementation is currently far away.

Library supports all database operations, from creating empty database, through selecting, inserting and updating existing data to migrations of schema changes.

Tables are described by TableColumns classes. These classes contains table, joined and computed columns.

Columns

Columns are key to everything. The base contract for column is:

public interface IColumn
{
    Type ValueType { get; }
    IColumnPath Path { get; }
}

and the generic version for specifying generic return type (for reading values, described later):

public interface IColumn<T> : IColumn
{ }

The IColumnPath is for defining unique doted 'path' to request column which provides abstractions to the generated SQL queries and other commands.

For these column contracts there are implementations for defining table owned column, left and inner joins, computed columns and etc.

Primary keys will be also marked own implementation of the IColumn. Inserts will defuse columns of this type, but updates will require those.

Joins

Having columns organized in TableColumns allows for great reuse in terms of joins. Define class with columns and create instance of it in the another TableColumns class to define join on the first table.

public class ProductTable
{
    public TableColumn<int> Id { get; private set; }
    public TableColumn<string> Name { get; private set; }
}

public class OrderTable
{
    public TableColumn<int> Id { get; private set; }
    public ProductTable Product { get; private set; }
}

Quite problematic are LEFT joins. Left joins should make all properties nullable, because left joins are optional by nature. This fact complicates writing table classes a little bit. Every type that is used as generic column argument and is not nullable by nature should be wrapped as table class generic argument. This provides ability to substitute int as Int32 and also as `Nullable. Example:

public class ProductTable<TInt32>
{
    public TableColumn<TInt32> Id { get; private set; }

    ...
    public ProductTable<Nullable<Int32>> OtherLeft { get; private set; }
    public ProductTable<Int32> OtherInner { get; private set; } 
}

For simplier use, we will create wraps for these generic classes:

public class ProductTable : ProductTable<Int32>
{ }

public class ProductLeftJoin : ProductTable<Nullable<Int32>>
{ }

Reading data

When using IDataReader you can define columns select, where clause and sorting, and than execute composed SQL query.

OrderTable orderTable = new OrderTable();

IDataReader<OrderTable> reader = dataReaderFactory.Create();
reader.Columns.Add(orderTable.Id);
reader.Columns.Add(orderTable.Product.Id);
reader.Columns.Add(orderTable.Product.Name);

reader.Sorting.Add(orderTable.Product.Name, SortDirection.Descending);
reader.Sorting.Add(orderTable.Id);

reader.Filters.Add(orderTable.Product.Name, "Holiday Hotel");

IEnumerable<IDataItem<OrderTable>> orders = readers.GetList();
foreach (IDataItem<OrderTable> order in orders)
{
    int orderId = order.GetValue(orderTable.Id);
    string productName = order.GetValue(orderTable.Product.Name);
}

Inserting and updating

IDataSaver is used for inserting new records. After obtaining the instance, you fill columns and its values to insert and call Insert.

ProductTable productTable = new ProductTable();

IDataSaver<ProductTable> saver = saverFactory.Create();
IDataItem<ProductTable> newProduct = new DataItem<ProductTable>();
newProduct.SetValue(productTable.Name, "Holiday Hotel");

saver.Insert(newProduct);

The same component is used for updating, but the Update method also takes where clause in the same form as in IDataReader.

The insert and update operations only works with TableColumns, so SetValue only takes this type of columns. This can be quite limiting, so we are considering separation of the IDataItem contract for reading and writing (with the option of extension method for fast creation of edit item for read item).

Extensible joins

Defining joins that "static" way creates dependencies between modules, which can be unintended. This can be solved with "table factories". These can take other table instance and create instance, that joins to main table. Example:

Product module contains product definition. In CMS module we want to extend product with image banners. Displaying products in CMS is made by creating SQL query on product table and we want to create join on image banner, but won't this reference/join in product module.

ProductTable productTable = new ProductTable();
ImageBannerTable bannerTable = ImageBannerTable.CreateJoinOnProduct(productTable);

IDataReader<ProductTable> reader = dataReaderFactory.Create();
reader.Columns.Add(productTable.Id);
reader.Columns.Add(bannerTable.Url);

...

int productId = dataItem.GetValue(productTable.Id);
string bannerUrl = dataItem.GetVale(bannerTable.Url);

Supporting SQL engines

Engine specific SQL command formatting is separated into own packages, so we are planning for supporting different SQL engines. These will be implement for example in Neptuo.Data.Sql.SqlServer or Neptuo.Data.Sql.MySql.

Common SQL constructs will be placed in shared assembly with concrete formatting placed in engine specific assemblies. So selects, inserts, updates, basic joins and others can be written with SQL engine independent references. For using those engine specific features you will have reference concrete engine assembly.