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