Lpslsn.Net.Core.Data.Manager 1.0.7

/* // *********************************************Create your own provider example Oracle ---------Nuget packages required: Lpslsn.Net.Core.Data.Manager.IDataProvider Microsoft.Windows.Compatibility Oracle.ManagedDataAccess.Core --> this is managed provider for oracle and you can use any other core providers

using System.Data; using System.ComponentModel.Composition; using Lpslsn.Net.Core.Data.Manager.Providers; using Lpslsn.Net.Core.Data.Manager.Providers.DbUtils; using System.Data.Common; using Oracle.ManagedDataAccess.Client; using System.Linq; using System;

namespace My.Oracle.Provider {

[Export(typeof(IDataProvider))]
public class OracleProvider : IDataProvider
{
    public string PROVIDER_NAME
    {
        get
        {
            return "ORACLE_PROVIDER";-->This is important in order to properly load the provider at runtime
        }
    }

    public DbCommand GetNewCommand()
    {
        return new OracleCommand();

    }

    public DbConnection GetNewConnection()
    {
        return  new OracleConnection();
     
    }

    public DbParameter GetNewParameter()
    {
        return new OracleParameter();
    }

    public string CommandParameterSymbol
    {
        get { return ":"; }
    }

    public string ParamPrefix
    {
        get { return ":"; }
    }

     public void ImportCustomBulkData(string destinationTbName, ref  DataTable sourceTable, IDbConnection destConnection, ref string[] columnsToSkip, int BatchSize = 1000)
    {
        try
        {
            if (string.IsNullOrWhiteSpace(sourceTable.TableName))
            {
                sourceTable.TableName = " /*+ APPEND NOLOGGING */ " +  destinationTbName;
            }
            if (destConnection.State == ConnectionState.Closed)
            {
                destConnection.Open();
            }
            int recCount = sourceTable.Rows.Count;
            GenerateBulkInsertFromDataTable _gen = new GenerateBulkInsertFromDataTable(sourceTable, destConnection, columnsToSkip.ToList(), ":", false,PROVIDER_TYPE.ORACLE);
            _gen.ExecuteInsertTable(BatchSize, (cmd,tran)=> {
                ((OracleCommand)cmd).BindByName = true;
                ((OracleCommand)cmd).ArrayBindCount = recCount;
            });
        }
        catch (Exception Ex)
        {
            throw Ex;
        }
    }
}

}

// ********************************************* Configure the folder for the providers extensions and put the assemblies inside to load the provider dynamically at run time DataProviderExtensions (folder) - runtimes - Lpslsn.Net.Core.Data.Manager.IDataProvider.dll - Oracle.ManagedDataAccess.dll - MyOracleProvider.dll

Note: In your application, you can configure another position by appsettings.json:
 "LpslsnDataManager": 
 {
    "DataProvidersPath": "D:\\User\\MyProviderExtensions"
 }

*/

// ********************************************* Use DBProvider in your Application ---------Nuget packages required: Lpslsn.Net.Core.Data.Manager

            DBProvider db = null;
            try
            {
                db = new DBProvider(DataProvider.Custom, @"DATA SOURCE=IP/ORCL:1252;PASSWORD=PWD;USER ID=USER", "ORACLE_PROVIDER");
                db.Open();

                DataSet ds = db.ExecuteDataSet(System.Data.CommandType.Text, "Select * from MyTable");
              
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    foreach (DataColumn c in ds.Tables[0].Columns)
                    {
                        Console.WriteLine(c.ColumnName + " - " + r[c] + "");
                    }
                }
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                if(db != null)
                db.Close();
            }

            //OR

            DBProvider db = null;
            try
            {
                db = new DBProvider(DataProvider.Custom, @"DATA SOURCE=IP-HOST/ORCL:1252;PASSWORD=PWD;USER ID=USER", "ORACLE_PROVIDER");
                db.Open();

                IDataReader r = db.ExecuteReader(System.Data.CommandType.Text, "Select * from MyTable");
                 while(r.Read())
                {
                   
                    Console.WriteLine("Data :" + r["MyColum"]);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (db != null)
                    db.Close();
            }

            // UPDATE WITH TRANSACTION
            DBProvider db = null;
            try
            {
                db = new DBProvider(DataProvider.Custom, @"DATA SOURCE=HOST-IP/ORCL:1252;PASSWORD=password;USER ID=user", "ORACLE_PROVIDER");
                db.Open();
                db.BeginTransaction();
                db.AddParameters("NAME", "HELLO");
                db.AddParameters("UNIQUE_ID", "1234567");
                db.ExecuteNonQuery(System.Data.CommandType.Text, "UPDATE MYTABLE SET NAME = :NAME WHERE UNIQUE_ID = :UNIQUE_ID");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                if (db != null)
                    db.RollBackTransaction();
                throw e;
            }
            finally
            {
                if (db != null)
                    db.Close();
            }


            //DEFAULT SQLSERVER WITHOUT CUSTOM PROVIDER
            DBProvider db = null;
            try
            {
                db = new DBProvider(DataProvider.SqlServer, @"Data Source=HOST;Initial Catalog=MYDATABASE;Persist Security Info=True;User ID=user;Password=password");
                db.Open();
                db.BeginTransaction();
                db.AddParameters("UserID", "a.lippolis");
                db.AddParameters("Id", 371);
                db.ExecuteNonQuery(System.Data.CommandType.Text, "UPDATE dbo.MyTable SET UserID = @UserID WHERE Id = @Id");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                if (db != null)
                    db.RollBackTransaction();
                throw e;
            }
            finally
            {
                if (db != null)
                    db.Close();
            }