Database CUD operations

In RDO.Data, you can create, update and delete data in DbTable<T>:

API Description
InsertAsync Insert from scalar data, DataSet or DbSet.
UpdateAsync Update from scalar data, DataSet or DbSet.
DeleteAsync Delete by condition, or match with DataSet or DbSet.

Inserting Data

You can insert into DbTable<T> via InsertAsync API, from various of data sources:

  • Scalar data: Scalar data is represented by Expression Column. You can use ColumnMapper class to map between scalar data column and target DbTable column. If all columns of target DbTable has default values, you can insert a row without any data specified. Optionally you can provide a <Action int?> delegate to receive newly generated identity column value for current insert.
  • DataSet: A DataSet<T> object can be used as source data for inserting. The generated native SQL for inserting from DataSet is database specific. For SQL Server and MySQL, multiple row DataSet will be serialized into JSON and passed to database server as single param for inserting, all done in one server round trip for best performance. To support this feature, SQL Server requires version 2016 (13) or later, MySQL requires version 8.0.4 or later. For newly generated identity column values, you can specify a optional boolean value parameter to indicate whether these values should be updated back to the source DataSet. This feature is especially useful if you're inserting parent-child hierarchical DataSet.
  • DbSet: A DbTable<T> or DbQuery<T> can be used as source data for inserting. Note DbTable<T> object can be temporary table.

A sample code of inserting data:

partial class Db
{
    ...
    public async Task<int?> CreateSalesOrderAsync(DataSet<SalesOrderInfo> salesOrders, CancellationToken ct)
    {
        await EnsureConnectionOpenAsync(ct);
        using (var transaction = BeginTransaction())
        {
            ...
            await SalesOrderHeader.InsertAsync(salesOrders, true, ct);
            var salesOrderDetails = salesOrders.GetChild(_ => _.SalesOrderDetails);
            ...
            await SalesOrderDetail.InsertAsync(salesOrderDetails, ct);

            await transaction.CommitAsync(ct);
            return salesOrders.Count > 0 ? salesOrders._.SalesOrderID[0] : null;
        }
    }
    ...
}

Updating Data

You can update DbTable<T> via UpdateAsync API, from various of data sources:

  • Scalar data: Scalar data is represented by Expression Column. You can use ColumnMapper class to map between scalar data column and target DbTable column. A condition can be provided to filter rows should be updated in the table. If no condition specified, all rows in the table will be updated with the same scalar value.
  • DataSet: A DataSet<T> object can be used as source data for updating. The source DataSet and target table must all have primary key, and if there are different you must provide a KeyMapping between them. The generated native SQL for updating from DataSet is database specific. For SQL Server and MySQL, multiple row DataSet will be serialized into JSON and passed to database server as single param for updating, all done in one server round trip for best performance. To support this feature, SQL Server requires version 2016 (13) or later, MySQL requires version 8.0.4 or later.
  • DbSet: A DbTable<T> or DbQuery<T> can be used as source data for inserting. Note DbTable<T> object can be temporary table. The source DbSet and target table must all have primary key, and if there are different you must provide a KeyMapping between them.

A sample code of updating data:

partial class Db
{
    ...
    public async Task UpdateSalesOrderAsync(DataSet<SalesOrderInfo> salesOrders, CancellationToken ct)
    {
        await EnsureConnectionOpenAsync(ct);
        using (var transaction = BeginTransaction())
        {
            await SalesOrderHeader.UpdateAsync(salesOrders, ct);
            await SalesOrderDetail.DeleteAsync(salesOrders, (s, _) => s.Match(_.FK_SalesOrderHeader), ct);
            var salesOrderDetails = salesOrders.GetChild(_ => _.SalesOrderDetails);
            await SalesOrderDetail.InsertAsync(salesOrderDetails, ct);

            await transaction.CommitAsync(ct);
        }
    }
    ...
}

Deleting Data

You can delete data in DbTable<T> via DeleteAsync API:

  • By condition: A condition can be provided to filter rows should be deleted in the table. If no condition specified, all rows in the table will be deleted.
  • DataSet: A DataSet<T> object can be used as source data for deleting. The source DataSet and target table must all have primary key, and if there are different you must provide a KeyMapping between them. The generated native SQL for deleting from DataSet is database specific. For SQL Server and MySQL, multiple row DataSet will be serialized into JSON and passed to database server as single param for deleting, all done in one server round trip for best performance. To support this feature, SQL Server requires version 2016 (13) or later, MySQL requires version 8.0.4 or later.
  • DbSet: A DbTable<T> or DbQuery<T> can be used as source data for inserting. Note DbTable<T> object can be temporary table. The source DbSet and target table must all have primary key, and if there are different you must provide a KeyMapping between them.

A sample code of deleting data:

partial class Db
{
    ...
    public Task<int> DeleteSalesOrderAsync(DataSet<SalesOrderHeader.Key> dataSet, CancellationToken ct)
    {
        return SalesOrderHeader.DeleteAsync(dataSet, (s, _) => s.Match(_), ct);
    }
    ...
}