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.

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; }
}

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

public interface IColumn<T> : IColumn
{ }

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; }
}

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).