Project Description
Less Code, Less Configuration, Less Time.
The DataBooster library is a high-performance extension to ADO.NET Data Provider, includes two aspects:
  • A slimmed down API encapsulation which simplified the most common data access operations (DbConnection -> DbCommand -> DbParameter -> DbDataReader) into a single class DbAccess, to help application with a clean DAL, avoid over-packing and redundant-copy of data transfer.
    Please consider using this for your application development if Entity Framework can not meet your performance requirement.
  • A booster for writing mass data onto database. Base on a rational utilization of database concurrency and a effective utilization of network bandwidth, the booster implements a transmitting pipeline of dataflow like a Multiple Launch Rocket System, The speed of writing data into database can be improved about 5 times faster than an ordinary bulk insert/copy, or up to 700 times faster than a row-by-row insert.
    The detail implement closely depends on the specific database. For the moment, the class OracleLauncher and SqlLauncher are ready for Oracle and SQL Server. They handle the throughput control and other low-level synchronization details. They are thread-safe which avoid locks. By using the Launcher, you can maximize the performance of your code while filling data row-by-row simply.
Data Launcher

Quick Starts

  • DbAccess:
    The following examples code shows its 4 different fine-grained user control of usages.
using System;
using System.Collections.Generic;
using DbParallel.DataAccess;

namespace DbAccessExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            decimal sumAmount = 0m;
            long sumHoldings = 0;

            // Example of constructor overload1
            using (DbAccess db = new DbAccess(MyConfigMgr.DbProviderFactory,
                MyConfigMgr.ConnectionString))
            {
                //=======================================================================
                // Example1: single result set
                db.ExecuteReader(GetProcedure("READ_TEST_DATA1"),
                    parameters =>
                    {
                        parameters.Add("inDate", DateTime.Today);
                        parameters.Add("inCount", 300000);
                    },
                    row =>
                    {
                        sumAmount += row.Field<decimal>("TEST_AMOUNT");
                    });

                sumAmount = 0m;

                //=======================================================================
                // Example2: multiple result sets
                db.ExecuteReader(GetProcedure("READ_TEST_DATA2"),
                    parameters =>
                    {
                        parameters.Add("inMarket", "NYSE");
                        parameters.Add("inDate", DateTime.Today);
                    },
                    (row, resultSet) =>
                    {
                        switch (resultSet)
                        {
                            case 0:     // First result set
                                sumAmount += row.Field<decimal>("MARKET_VALUE");
                                break;
                            case 1:     // Second result set
                                sumHoldings += row.Field<long>("HOLDINGS");
                                break;
                        }
                    });
            }

            // Example of constructor overload4
            using (DbAccess db = new DbAccess("MyDbConnKey"))
            {
                List<MyBusiness1> myBusiness1List = new List<MyBusiness1>();
                List<MyBusiness2> myBusiness2List = new List<MyBusiness2>();

                //=======================================================================
                // Example3: map every result row to a business class by specified column-property mappings
                db.ExecuteReader<MyBusiness1>(GetProcedure("READ_TEST_DATA3"),
                    parameters =>
                    {
                        parameters.Add("inAs_Of_Date", DateTime.Today);
                    },
                    map =>
                    {
                        map.Add("SEC_ID", t => t.Id);
                        map.Add("MARKET_VALUE", t => t.MarketValue);
                        map.Add("CLOSE_DATE", t => t.CloseDate);
                    },
                    myBusiness1 =>
                    {
                        myBusiness1List.Add(myBusiness1);
                    });

                //=======================================================================
                // Example4: auto map every result row to a business class by matching database column names with the same name of class properties, ignore mismatched columns.
                db.ExecuteReader<MyBusiness2>(GetProcedure("READ_TEST_DATA4"),
                    parameters =>
                    {
                        parameters.Add("inAs_Of_Date", DateTime.Today);
                        parameters.Add("inDept_Code", "LOCAL_SALES");
                    },
                    myBusiness2 =>
                    {
                        myBusiness2List.Add(myBusiness2);
                    });
            }
        }

        // Get stored procedure full name [schema.package.sp]
        static string GetProcedure(string sp)
        {
            return MyConfigMgr.DatabasePackage + sp;
        }
    }

    public class MyBusiness1
    {
        public int Id { get; set; }
        public decimal MarketValue { get; set; }
        public DateTime? CloseDate { get; set; }
    }

    public class MyBusiness2
    {
        public string Prod_Code { get; set; }
        public int Stock { get; set; }
        public float? Discount_Rate { get; set; }
    }
}
  • ExecuteMultiReader:
    Multi-ResultSet is a common scenario in package based (Oracle) or stored procedure based (SQL Server) database development, using the ExecuteMultiReader Method can minimize your DAL code to process multiple results.
internal static Tuple<List<MyBusiness1>, List<MyBusiness2>, List<MyBusiness3>> ViewReport(this DbAccess dbAccess, DateTime date, int sessionId)
{
    const string sp = "VIEW_REPORT";

    var resultTuple = Tuple.Create(new List<MyBusiness1>(), new List<MyBusiness2>(), new List<MyBusiness3>());

    dbAccess.ExecuteMultiReader(GetProcedure(sp), parameters =>
        {
            parameters.Add("inDate",    date);
            parameters.Add("inSession", sessionId);
        }, resultSets =>
            {
                // Specified fields mapping example
                resultSets.Add(resultTuple.Item1, colMap =>  // 1st ResultSet
                    {
                        colMap.Add("SEC_ID",        t => t.Id);
                        colMap.Add("MARKET_VALUE",  t => t.MarketValue);
                        colMap.Add("CLOSE_DATE",    t => t.CloseDate);
                    });

                // Full-automatic (case-insensitive) fields mapping examples
                resultSets.Add(resultTuple.Item2);   // 2nd ResultSet
                resultSets.Add(resultTuple.Item3);   // 3rd ResultSet
            }
    );

    return resultTuple;
}

Normally, all these method calls and their parameters should be wrapped in your DAL.
Further, the user class (e.g. above MyBusiness2 class) can be auto generated by the utility under UtilityScripts folder.
  • Data Type Mapping
    Supported mapping of Database Engine Data Types to .NET Common Language Runtime Types relies on two levels conversion:
    Data Type Mapping
    1. ADO.NET Provider converts database data type into inferred .NET Framework data type;
    2. If level 1 conversion has not yet reached business target type, an appropriate IConvertible method of the inferred .NET Framework type will be called automatically to convert further into the target type.
  • OracleLauncher:
    A quick example:
using System;
using System.Threading.Tasks;
using DbParallel.DataAccess.Booster.Oracle;
using DDTek.Oracle;

namespace OracleLauncherExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleLauncher launcher = new OracleLauncher(ConfigurationManager.ConnectionStrings["MyDbConnKey"].ConnectionString,
                "SCHEMA.PACKAGE.WRITE_BULK_DATA"/* stored procedure */,
                parameters =>
                {
                    parameters.Add("inGroup_ID", 1001);     // Ordinary parameter
                    parameters.AddAssociativeArray("inItem_IDs",
                        OracleDbType.Int32);
                    parameters.AddAssociativeArray("inItem_Values",
                        OracleDbType.Double);
                }))
            {
                Parallel.For(0, 100, i =>   // Just simulating multiple(100) producers
                {
                    for (int j = 0; j < 200000; j++)
                    {
                        AddRow(launcher, i * 200000 + j, (double)j * 0.618);
                    }
                });

                // launcher.Complete(); // This is not necessary because here creates the instance with a using statement, the Complete method will be called by Dispose method automatically.
            }
        }

        static void AddRow(OracleLauncher launcher, int itemId, double itemValue)
        {
            launcher.Post(itemId, itemValue);
        }
    }
}
The database side stored procedure is like following:
TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE DOUBLE_ARRAY IS TABLE OF BINARY_DOUBLE INDEX BY PLS_INTEGER;

PROCEDURE WRITE_BULK_DATA
(
    inGroup_ID      NUMBER,
    inItem_IDs      NUMBER_ARRAY,
    inItem_Values   DOUBLE_ARRAY
)   AS
BEGIN
    FORALL i IN inItem_IDs.FIRST .. inItem_IDs.LAST
    INSERT /*+ APPEND_VALUES */ INTO XYZ.TEST_WRITE_DATA
    (
        GROUP_ID,
        ITEM_ID,
        ITEM_VALUE
    )
    VALUES
    (
        inGroup_ID,
        inItem_IDs(i),
        inItem_Values(i)
    );
    COMMIT;
END WRITE_BULK_DATA;
 

API Reference

  • DbAccess
    (Namespace: DbParallel.DataAccess)
Constructors (4 overloads):
public DbAccess(DbProviderFactory dbProviderFactory, string connectionString);
public DbAccess(string providerName, string connectionString);
public DbAccess(ConnectionStringSettings connSetting);
public DbAccess(string connectionStringKey);

Static Global
public static CommandType DefaultCommandType { get; set; }
    The default is StoredProcedure.
 
Methods
- ExecuteReader (9 overloads):
void ExecuteReader(string commandText, Action<DbParameterBuilder> parametersBuilder, Action<DbDataReader> dataReader);

void ExecuteReader(string commandText, int commandTimeout, CommandType commandType, Action<DbParameterBuilder> parametersBuilder, Action<DbDataReader> dataReader);

IEnumerable<T> ExecuteReader<T>(string commandText, Action<DbParameterBuilder> parametersBuilder, Action<DbFieldMap<T>> resultMap = null) where T : new();

IEnumerable<T> ExecuteReader<T>(string commandText, int commandTimeout, CommandType commandType, Action<DbParameterBuilder> parametersBuilder, Action<DbFieldMap<T>> resultMap = null) where T : new();

void ExecuteReader<T>(string commandText, Action<DbParameterBuilder> parametersBuilder, Action<DbFieldMap<T>> resultMap, Action<T> readEntity) where T : new();

void ExecuteReader<T>(string commandText, Action<DbParameterBuilder> parametersBuilder, Action<T> readEntity) where T : new();

void ExecuteReader<T>(string commandText, int commandTimeout, CommandType commandType, Action<DbParameterBuilder> parametersBuilder, Action<DbFieldMap<T>> resultMap, Action<T> readEntity) where T : new();

void ExecuteReader(string commandText, Action<DbParameterBuilder> parametersBuilder, Action<DbDataReader, int/*resultSet*/> dataReaders); // For multiple result set

void ExecuteReader(string commandText, int commandTimeout, CommandType commandType, Action<DbParameterBuilder> parametersBuilder, Action<DbDataReader, int/*resultSet*/> dataReaders); // For multiple result set
- DbParameterBuilder
public DbParameter DbParameterBuilder.Add(string parameterName, object oValue, int nSize = 0); // Direction defaults to ParameterDirection.Input

public DbParameter DbParameterBuilder.AddReturn(string parameterName = "RETURN_VALUE", DbType dbType = DbType.Int32, int nSize = 0);

public DbParameter DbParameterBuilder.AddOutput(string parameterName, DbType dbType, int nSize = 0);
- DbFieldMap
public DbFieldMap<T> DbFieldMap<T>.Add(string columnName, Expression<Func<T, object>> fieldExpr);
- ExecuteMultiReader (2 overloads):
public void ExecuteMultiReader(string commandText, Action<DbParameterBuilder> parametersBuilder, Action<DbMultiResultSet> multiResultSetMap);

public void ExecuteMultiReader(string commandText, int commandTimeout, CommandType commandType, Action<DbParameterBuilder> parametersBuilder, Action<DbMultiResultSet> multiResultSetMap);
- DbMultiResultSet
public void DbMultiResultSet.Add<T>(ICollection<T> resultSet, Action<DbFieldMap<T>> resultMap = null) where T : new();
- ExecuteNonQuery (2 overloads):
int ExecuteNonQuery(string commandText, Action<DbParameterBuilder> parametersBuilder = null);

int ExecuteNonQuery(string commandText, int commandTimeout, CommandType commandType, Action<DbParameterBuilder> parametersBuilder);
  • OracleLauncher
    (Namespace: DbParallel.DataAccess.Booster.Oracle)
Constructors:
OracleLauncher(string connectionString, string storedProcedure, Action<DbParameterBuilder> parametersBuilder, int multipleRockets = _DefaultMultipleRockets, int bulkSize = _DefaultBulkSize, int commandTimeout = _CommandTimeout);
The _DefaultMultipleRockets is 6:
Multiple Rockets


Methods
- Post:

void Post(params object[] values);
Your application DAL should wrap this method as strong type parameters, like following example:
public void AddQuote(int id, DateTime time, int level, decimal price)
{
    _launcher.Post(id, time, level, price);
}
  • SqlLauncher
    (Namespace: DbParallel.DataAccess.Booster.SqlServer)
Constructors:
SqlLauncher(string connectionString, string destinationTableName, Action<SqlBulkCopyColumnMappingCollection> columnMappings = null, int multipleRockets = _DefaultMultipleRockets, int bulkSize = _DefaultBulkSize, int commandTimeout = _CommandTimeout);

Detail for destinationTableName and SqlBulkCopyColumnMappingCollection, please refer to SqlBulkCopy.DestinationTableName, SqlBulkCopy.ColumnMappings and SqlBulkCopyColumnMappingCollection on MSDN.

Methods
- Post: The same as OracleLauncher

NuGet Packages
It is strongly recommended to use this library through NuGet Library Package Manager right in Visual Studio. NuGet Packages can also generate some scaffolding, sample code and configuration into your project for a very quick start from the ground.
Package Package ID
DataBooster for SQL Server DataBooster.SqlServer
DataBooster for SQL Server + Oracle (use ODP.NET Provider) DataBooster.Oracle.ODP
DataBooster for SQL Server + Oracle (use ODP.NET Managed Driver)   DataBooster.Oracle.Managed
DataBooster for SQL Server + Oracle (use DataDirect Provider) DataBooster.Oracle.DataDirect  


All sample code is only available in DEBUG mode, you can even turn off sample code in DEBUG mode easily, just add NO_SAMPLE into your project Conditional Compilation Symbols (Visual Studio Project Properties Dialog -> Build -> General -> Conditional Compilation Symbols).
 

Requirements

  • .NET Framework 4.0 or later.
  • Oracle ADO.NET Provider (Optional):
    - ODP.NET 4 (Reference: Oracle.DataAccess.dll; Conditional Compilation Symbols: "ORACLE;ODP_NET")
    or
    - ODP.NET Managed Driver (Reference: Oracle.ManagedDataAccess.dll; Conditional Compilation Symbols: "ORACLE;ODP_NET_MANAGED")
    or
    - DataDirect Connect for ADO.NET (Reference: DDTek.Oracle.dll; Conditional Compilation Symbols: "ORACLE;DATADIRECT")
    or
    (the provider can be easily replaced by other provider for Oracle)

Contributions

  • Welcome all feedback through the CodePlex project (through comments, patches, or items in the Issue Tracker);
  • Needs to add support for other databases;
  • Looking for Clean-Style Developers, Testers and Editors.
Support
Feel free to use the source or binaries in your apps, and products.
This project is developed in personal time, the source code support can be available only at night - Easten Time (US & Canada).

Last edited Sep 21 at 4:45 AM by AbelCheng, version 65