Monday, November 4, 2019



ADO.DataAccessHelper is .net standard 2.0 class library, lite, simple, and more than one database connections from different technologies made possible with ADO.Net DbProviderFactory.
Database                DbProviderFactory
MS SQL                   SqlClientFactory
MySql                     MySqlClientFactory
Oracle                     OracleClientFactory
PostgreSql               NpgsqlFactory
---                           ---
Provided features
  • Helper methods eliminates ADO.net repetitive code for the given DbProviderFactory.
  • Works for .net Framework 4.6.1 and later.
  • Works for .net core 2.0 and later.
Example
ADO.DataAccessHelper factory repository to be set at start of the application, it’s an optional and connection can be provided as input parameter to helper method, see Connection Example.
DatabaseProvider<SqlClientFactory>.Set("Sql.Connection1", $"{SqlConnection1String}");
DatabaseProvider<SqlClientFactory>.Set("Sql.Connection2", $"{SqlConnection2String}");
DatabaseProvider<NpgsqlFactory>.Set("PostgreSql.Connection", $"{PostgreSqlConnectionString}");
DatabaseProvider<MySqlClientFactory>.Set("MySql.Connection", $"{MySqlConnectionString}");


Different ways to use set factories.
DatabaseProvider<SqlClientFactory>.... // Use desired factory to connect database server, e.g. here SqlClientFactory is used.
How to use the preset factory.
DataAccess<SqlClientFactory>.... // Use desired factory, e.g. SqlClientFactory is used to connect SQL Server Instance.
Option#1
//Default connection is, first connection set for SqlClientFactory
DataSet dataSet = DataAccess<SqlClientFactory>.ExecuteDataSetProcedure("SelectEmployee",
    new SqlParameter[] {
    new SqlParameter("@EmployeeId",7)
});

Option#2
//Explicit connection name to connect specific database instance with give DbProvider
DataSet dataSet = DataAccess<SqlClientFactory>.Get("Sql.Connection2")
    .ExecuteDataSetProcedure("SelectCustomer",
        new SqlParameter[] {
        new SqlParameter("@CustomerId",7)
     });

Option#3 In case there is one more database from PostgreSQL then NpgsqlFactory is used to perform data access.
//Default connection is  'PostgreSql.Connection', NpgsqlFactory has one connection
DataAccess<NpgsqlFactory>.ExecuteProcedure("DeleteAllLogs",
      new NpgsqlParameter[] {
      new NpgsqlParameter("@UserId",7 )});
Option#4 Connection Example Provide connection string while making a call to helper method.
const string ConnectionString = @"Data Source=XYZ\SQLDEV;Initial Catalog=PlayGround;User Id=SA;Password=XXXXX";

//Make a call
string name = DataAccess<SqlClientFactory>.ExecuteScalarQuery<string>
      ("Select Name from Employees Where EmployeeId = 7", ConnectionString);