DataBooster - Extension to ADO.NET Data Provider

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

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

• Column-->Property Mapping
  • You can explicitly specify which database column map to which property or field of your strongly typed class. E.g.

    ....
    colMap.Add("BUY_AMOUNT",  t => t.Cost);
    colMap.Add("ISSUER_REGION",  t =>
               t.NestedProperty.DeeperField.Issuer.Region.IsoCode);
    ....

    To allow map a database column to a deep Properties or Fields in .Net, please make sure that,
    1. All nested properties or fields (above e.g. NestedProperty, DeeperField, Issuer and Region) run through in middle levels must be:
       • Class only. Struct is not supported;
       • The class has public parameterless constructor (or by default);
       • The nested propertie/field has both read and write accessibility.
    2. The end property or field (above e.g. Cost and IsoCode) must be:
       • A value type or string type; (Implement IConvertible interface)
       • With both read and write accessibility.
    3. There is no limit to the number of how many levels through the path from the root class object to the end property or field.
  • If you don't explicitly specify mapping (omit the whole parameter at all), the full-auto-mapping mechanism will be activated to match database columns with .Net one level properties/fields as more as possible. Following is the matching precedence:
    1. Match the names of database columns with the names of .Net properties/fields in a case-sensitive manner;
    2. Match the rest in a case-insensitive manner;
    3. De-underscore(compact) the rest of database column names (as we know, most Oracle users adopt underscores naming convention), then match the rest names with rest .Net properties/fields in a case-insensitive manner. - v1.7.6.2+
       E.g. A database column named "USER_FIRST_NAME" will auto-map to a .Net property named UserFirstName (or userFirstName).
   
• Data Type Mapping
  Supported mapping of Database Engine Data Types to .NET Common Language Runtime Types relies on two levels conversion:
  
  
  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.

public class DbWebApiController : ApiController
{
    [DbWebApiAuthorize]
    [AcceptVerbs("GET", "POST", "PUT", "DELETE")]
    public StoredProcedureResponse Execute(string sp,
                                           IDictionary<string, object> parameters)
    {
        StoredProcedureRequest spRequest = new StoredProcedureRequest(sp,parameters);

        using (DbAccess dbAccess = DbPackage.CreateConnection())
        {
            return dbAccess.ExecuteStoredProcedure(spRequest);
        }
    }
}

StoredProcedureResponse ExecuteStoredProcedure(StoredProcedureRequest request);

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

OracleLauncher(string connectionString, string storedProcedure, Action<DbParameterBuilder> parametersBuilder, int multipleRockets = _DefaultMultipleRockets, int bulkSize = _DefaultBulkSize, int commandTimeout = _CommandTimeout);

Methods
- Post:

void Post(params object[] values);

public void AddQuote(int id, DateTime time, int level, decimal price)
{
    _launcher.Post(id, time, level, price);
}

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