dotNet core (console app) with Enterprise Library Data (EnterpriseLibrary.Data.NetCore) and JSON

Question

I am trying to use:

EnterpriseLibrary.Data.NetCore

.nuget\packages\enterpriselibrary.data.netcore\6.0.1313

https://www.nuget.org/packages/EnterpriseLibrary.Data.NetCore/

.NET Core 2.1

I have the following JSON

{
  "ConnectionStrings": {
    "MyDefaultConnectionName": "Server=.\\MyInstance;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

And this code works fine (in my Program.cs dotnet console app) (simple showing my appsettings.json is being picked up, and it looks like my connection-string json structure looks right.

using Microsoft.Extensions.Configuration;

            IConfiguration config = new ConfigurationBuilder()
                    .SetBasePath(System.IO.Directory.GetCurrentDirectory())
                    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
                    .Build();


            string conString = Microsoft
               .Extensions
               .Configuration
               .ConfigurationExtensions
               .GetConnectionString(config, "MyDefaultConnectionName");

            Console.WriteLine("MyDefaultConnectionName.conString='{0}'", conString);

and I see:

MyDefaultConnectionName.conString='Server=.\MyInstance;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true'

However, my attempt to use the code is failing:

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

    public DataSet GetADataSet()
    {
        DataSet returnDs = null;
        string sql = "Select * from dbo.MyTable";

        try
        {

            DatabaseProviderFactory factory = new DatabaseProviderFactory();
            Database db = DatabaseFactory.CreateDatabase("MyDefaultConnectionName");
            DbCommand dbc = db.GetSqlStringCommand(sql);
            returnDs = db.ExecuteDataSet(dbc);
        }
        catch (Exception ex)
        {
            string temp = ex.Message;
            throw ex;
        }

        return returnDs;
    }

The exception is:

Exception while running 'Select * from dbo.MyTable' ('MyDefaultConnectionName') ('Database provider factory not set for the static DatabaseFactory. Set a provider factory invoking the DatabaseFactory.SetProviderFactory method or by specifying custom mappings by calling the DatabaseFactory.SetDatabases method.')

I found this link....

Microsoft.Practices.EnterpriseLibrary.Data.DLL but was not handled in user code

to "older" pre dotnet core xml based.

I've been too the project website

https://github.com/Chavoshi/EnterpriseLibrary.NetCore

but cannot find a working example

..... in regards to my line of code above:

DatabaseProviderFactory myFactory= new DatabaseProviderFactory();

and the error message:

DatabaseFactory.SetProviderFactory

myFactory does not have a method SetProviderFactory

........

I tried this...(but I know it really isn't the same as the xml attribute (future readers, don't waste your time with the below json)

{
  "ConnectionStrings": {
    "MyDefaultConnectionName": "Server=.\\MyInstance;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true",
    "providerName": "System.Data.SqlClient"
  }
}

What is the magic syntax sugar for using

https://github.com/Chavoshi/EnterpriseLibrary.NetCore

with a dotnet core console app? WITH JSON

APPEND:

Based on the comments, I also have chased this example:

https://github.com/Chavoshi/EnterpriseLibrary.NetCore/tree/master/Examples

The one example there is xml based, not json based.

Did you check the [example code](https://github.com/Chavoshi/EnterpriseLibrary.NetCore/blob/master/Examples/EnterpriseLibrary.Test.MySql/EnterpriseLibrary.Test.MySql/Program.cs#L31)? — DavidG, Nov 12 '18 at 13:37
@DavidG. That helped a tad. I see some of the methods are static methods, not instance methods. But now I get : The connection string for the database 'DefaultConnection' does not exist or does not have a valid provider. See my "future readers" where I try to find the way to set the providerName in the json as one once did in the xml. — granadaCoder, Nov 12 '18 at 13:48
@DavidG .. the example you gave....helped a tad. I'm pointing out that the example is xml based. see the sibling file : https://github.com/Chavoshi/EnterpriseLibrary.NetCore/blob/master/Examples/EnterpriseLibrary.Test.MySql/EnterpriseLibrary.Test.MySql/App.config — granadaCoder, Nov 12 '18 at 13:50
Yes, I did see that example a few days ago. (the single example given at : https://github.com/Chavoshi/EnterpriseLibrary.NetCore/tree/master/Examples ) It is xml based. That's the main issue, what is the ~json~ magic sauce that makes the world happy. — granadaCoder, Nov 12 '18 at 14:08
That's not Microsoft's Enterprise Library Data Access component's, that's someone's repo with cloned code and text. Why are you using it in the first place? When ADO.NET 2.0 came out it [contained almost all of the old EntLib's data access patterns](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/obtaining-a-dbproviderfactory). As a result, Entlib Data was abandoned. Why use `DatabaseFactory.CreateDatabase` when ADO.NET provides its own `DbProviderFactories.GetFactory(providerName);` etc ? — Panagiotis Kanavos, Nov 12 '18 at 14:19
@granadaCoder the reason you won't find any JSON configuration examples is that the original code was written between 2005 and 2008. The cloned repo would need a significant rewrite to combine 10 year old configuration patterns with .NET Core's own configuration mechanism. — Panagiotis Kanavos, Nov 12 '18 at 14:22
@granadaCoder why are you looking at Entlib Data in the first place? Why don't you use a micro-ORM like StackOverlfow's own Dapper to execute SQL queries, or a full ORM like Entity Framework Core? — Panagiotis Kanavos, Nov 12 '18 at 14:25
@PanagiotisKanavos Ok. That makes sense. (The age of the original). So this more looks like a copy/code and build against dotNetCore. EF.ORM is too "chatty" for my performance reasons..thus why I'm trying to use ole/faithful IDataReader. I will check Dapper, I know its a fairly performance microORM. I need to be able to handle muliple resultSets (Multiple SELECT statements in stored procedures). Security team demands stored procedures.......another reason I've leaned toward EnterpriseLibrary.Data. Thanks for food-for-thought. — granadaCoder, Nov 12 '18 at 14:38
SOF provides once again : https://stackoverflow.com/questions/6317937/dapper-net-and-stored-proc-with-multiple-result-sets — granadaCoder, Nov 12 '18 at 14:39
So future readers. My (bad) assumption was that the new library "enterpriselibrary.data.netcore" would have done a few things with json configuration, and it doesn't look like that has happened. It will still support Xml based stuff (see the example at the enterpriselibrary.data.netcore source code site).......So since this library isn't fully developed.....I have moved to Dapper. EF is too chatty for my performance needs. Dapper is very close to "IDataReader" type performance, so I have moved to it (Dapper that is). — granadaCoder, Nov 14 '18 at 20:24
See https://noamlewis.wordpress.com/2012/07/18/net-4-5-improves-orm-performance-across-the-chart/ — granadaCoder, Nov 14 '18 at 20:24
See https://weblogs.asp.net/fbouma/fetch-performance-of-various-net-orm-data-access-frameworks this is the one that has a very close Ado.NET vs Dapper comparison — granadaCoder, Nov 14 '18 at 20:29

Mo Chavoshi · Answer 1 · 2018-11-18T03:49:52.520

Thanks all for your explanations. As @panagiotis-kanavos also mentioned in the comments, adding JSON support to the library is a major change and needs a lot of efforts and for sure I cannot do it individually.

BTW I am going to somehow start the Enterprise Library Community and make it alive again -as I was (and am) a fan of it always, because I personally think it's really handy, specially in Exception Handling and Logging blocks- so absolutely adding JSON support will be one of the very first changes in the new major versions.

But for now this project is just to help the ones who want to convert their old projects to .Net Core with less challenges, without changing the infrastructure.

At the end, I appreciate if someone will help me on the community as I have started to discuss with Microsoft on this subject and waiting for their decision to let me update the official packages with the new versions in case if we are going to continue on updating the library.

Thanks for chiming in! Yeah, even after all these years with "cooler toys", I still like the compactness of EnterprieLibrary.Data. AddParameter, AddParameter and Execute syntax sugar. — granadaCoder, Nov 16 '18 at 13:41
MohammadC, you may be interested in my post here: https://stackoverflow.com/questions/40845542/how-to-read-a-connectionstring-with-provider-in-net-core/53326582#53326582 — granadaCoder, Nov 16 '18 at 13:41

score 2 · Answer 2 · answered Mar 26 '22 at 16:04

There is a way of doing it.

Here is my appsettings.json

{

  "APP": {
    "APP_NAME": "EOD.BACKUP.JOB",
    "APP_AUTHOR": "Lutaaya Fauzi"
  },

  "LOGGING": {
    "LOG_FILE_EXXT": "log",
    "LOG_FILE_PRFX": "DailyEODBackUp",
    "LOG_FILE_PATH": "D:\\109\\APP_LOGS\\EODDataBackUpJob"
  },

  "DATABASE_PROVIDERS": {
    "ODBC": "System.Data.Odbc",
    "MSSQL": "System.Data.SqlClient"
  },

  "DATABASE": {
    "XVERSEDB": {
      "DBMS": "ORACLE",
      "CONN_STRING": "Dsn=XEPDB1; Pwd=XVERSE",
      "DSN": "XEPDB1",
      "PWD": "XVERSE"
    },
    "E4MDB": {
      "DBMS": "ORACLE",
      "CONN_STRING": "Dsn=XEPDB1_E4M; Pwd=E4M",
      "DSN": "XEPDB1_E4M",
      "PWD": "E4M"
    },
    "LIVEEXIMSUPPORTDB": {
      "DBMS": "MSSQL",
      "CONN_STRING": "Data Source=DESKTOP-NL8PUQI\\SQLEXPRESS;Initial Catalog=LIVEEXIMSUPPORTDB;User ID=sa;Password=bundestag",
      "DATA_SOURCE": "DESKTOP-NL8PUQI\\SQLEXPRESS",
      "DATABASE": "LIVEEXIMSUPPORTDB",
      "USER": "XEPDB1",
      "PASSWORD": "XVERSE"
    }

  }

}

Here is my DBOperations.cs class

using Microsoft.Extensions.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EODDataBackUpJob
{
    internal class DBOperations
    {
        #region ... VARIABLES
        // ... configuration
        IConfiguration appconfig = new ConfigurationBuilder().AddJsonFile("appsettings.json").AddEnvironmentVariables().Build();
        ApplicationLogger applogger = new ApplicationLogger();
        private Database XVERSEDB, EXIMDB, E4MDB;
        private DbCommand mycommand;
        #endregion


        #region ... Database Connection
        public DBOperations()
        {
            try
            {
                #region ... Register Database Factories
                string ODBC_PROVIDER = appconfig["DATABASE_PROVIDERS:ODBC"];
                string MSSQL_PROVIDER = appconfig["DATABASE_PROVIDERS:MSSQL"];

                DbProviderFactories.RegisterFactory(ODBC_PROVIDER, OdbcFactory.Instance);
                DbProviderFactories.RegisterFactory(MSSQL_PROVIDER, SqlClientFactory.Instance);

                DbProviderFactory ODBC_FACTORY_PROVIDER = DbProviderFactories.GetFactory(ODBC_PROVIDER);
                DbProviderFactory MSSQL_FACTORY_PROVIDER = DbProviderFactories.GetFactory(MSSQL_PROVIDER);
                #endregion



                // ... XVERSEDB
                string XVERSEDB_CONN = appconfig["DATABASE:XVERSEDB:CONN_STRING"];
                XVERSEDB = new GenericDatabase(XVERSEDB_CONN, ODBC_FACTORY_PROVIDER);

                // ... EXIMDB
                string EXIMDB_CONN = appconfig["DATABASE:LIVEEXIMSUPPORTDB:CONN_STRING"];
                EXIMDB = new GenericDatabase(EXIMDB_CONN, MSSQL_FACTORY_PROVIDER);

                // ... E4MDB
                string E4MDB_CONN = appconfig["DATABASE:E4MDB:CONN_STRING"];
                E4MDB = new GenericDatabase(E4MDB_CONN, ODBC_FACTORY_PROVIDER);
           

            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                string stack_trace = ex.StackTrace;
                applogger.LogToFile("DBOperations.DBOperations", msg);
                applogger.LogToFile("DBOperations.DBOperations", stack_trace);
                applogger.LogFileSeparator();
            }
        }
        #endregion


        #region ... oracle DB 
        public void ExecuteDBReport()
        {
            try
            {
                DataTable result = new DataTable();

                ////mycommand = XVERSEDB.GetStoredProcCommand("UATJUN.SP_NPA_MANUAL_PROCESS", "28/02/2022", "");
                //XVERSEDB.ExecuteDataSet(mycommand);

                mycommand = E4MDB.GetSqlStringCommand("SELECT * FROM E4M.CFG_BILLER");
                mycommand.CommandTimeout = 5000;
                result = E4MDB.ExecuteDataSet(mycommand).Tables[0];

            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                string stack_trace = ex.StackTrace;
                applogger.LogToFile("DBOperations.ExecuteDBReport", msg);
                applogger.LogToFile("DBOperations.ExecuteDBReport", stack_trace);
                applogger.LogFileSeparator();
            }
        }
        #endregion



    }
}

This example covers Oracle database connection via ODBC datasource plus MSSQL connect via ODBC Datasource

You can now apply for other Databases like MySQL

It should work fine for .NET core

Thank you

dotNet core (console app) with Enterprise Library Data (EnterpriseLibrary.Data.NetCore) and JSON

2 Answers2

Linked