LiteXDbHelper

LiteXDbHelper is simple and tiny yet powerful and very high-performance library to working with ADO.NET for different database providers in C#.

This library contains useful database utility classes, functions and extension methods. ADO.NET wrapper specifically develop to help make life easy working with relational databases like SQLServer, MySql, PostgreSql, MariaDB, Oracle and stored procedures for .NET and .NET Core applications. This is a tiny library helps write less code, to execute queries and stored procedures against SQL Server as like any normal CLR method. It is just written for the purpose to bring a new level of ease to the developers who deal with ADO.NET for data access.

Database Providers :books:

• SqlServer
• MySql
• PostgreSql
• MariaDB
• Oracle

Features :pager:

Basic features

• ExecuteNonQuery
• ExecuteScalar
• GetDataTable
• GetDataSet
• ExecuteReader
• CreateParameters (for each providers)

Advanced features

• ExecuteScalar<>
• GetSingle<T>
• GetList<T>
• GetArray<T>
• GetDictionary<TKey, TValue>

Instantiate the DBHelper

using DBHelpers;
...
// just use the connection string name
IDBHelper dbHelper = new SqlHelper("MyCN");
// use different provider class based on your database.

Basic ADO.NET methods

// Create parameters
var paramResultOut = DbHelper.CreateOutParameter("@Result", SqlDbType.NVarChar, 100);
var paramIsActive = DbHelper.CreateParameter("@IsActive", isActive);
var paramId = DbHelper.CreateParameter("@Id", id);

  
// ExecuteNonQuery
// Stored procedure
var count = _dbHelper.ExecuteNonQuery("DeleteCountry", paramIsActive, paramResultOut);
// OR
// Raw sql commnad
var count = _dbHelper.ExecuteNonQuery("DELETE FROM [dbo].[Country] WHERE Id = @Id", CommandType.Text, paramIsActive);


// ExecuteScalar - returning a object type scalar value 
var name = _dbHelper.ExecuteScalar("SELECT Name FROM COUNTRY");


// DataReader
// Stored procedure
var dataReader = _dbHelper.ExecuteReder("GetCountries", paramIsActive, paramResultOut);
// OR
// Raw sql commnad
var dataReader = _dbHelper.ExecuteReder("SELECT * FROM Country WHERE IsActive = @IsActive", CommandType.Text, paramIsActive);


// DataTable
// Stored procedure
var dataTable = _dbHelper.GetDataTable("GetCountries", paramIsActive, paramResultOut);
// OR
// Raw sql commnad
var dataTable = _dbHelper.GetDataTable("SELECT * FROM Country WHERE IsActive = @IsActive", CommandType.Text, paramIsActive);


// DataSet
// Stored procedure
var dataSet = _dbHelper.GetDataSet("GetCountriesAndStates", paramIsActive, paramResultOut);

Scalar methods: (Coming soon)

// returning a object type scalar value 
var name = _dbHelper.ExecuteScalar("SELECT Name FROM COUNTRY");

// returning a int scalar value
var count = _dbHelper.ExecuteScalar<int>("SELECT COUNT(*) FROM COUNTRY", cmdType: CommandType.Text);

// returning a datetime scalar value
//var lastDate = _dbHelper.ExecuteScalar<DateTime>("SELECT MAX(change_date) FROM TABLENAME");
var valueDatetime = _dbHelper.ExecuteScalar<DateTime>("SELECT GETDATE()", cmdType: CommandType.Text);


// This provides a quite safe experience for most cases and allow you to not care about DBNulls or conversions.

// int is a struct, so value = default(int) = 0
var valueInt = _dbHelper.ExecuteScalar<int>("SELECT CAST(NULL AS int)", cmdType: CommandType.Text);

// string is a reference type, so value = default(string) = null
var valueString = _dbHelper.ExecuteScalar<string>("SELECT CAST(NULL AS varchar)", cmdType: CommandType.Text);

// int? is a nullable, so value = default(int?) = null
var valueNullableInt = _dbHelper.ExecuteScalar<int?>("SELECT CAST(NULL AS int)", cmdType: CommandType.Text);

// value comes as int or long depending on the provider, but is converted to byte using System.Convert
//var valueByte = _dbHelper.ExecuteScalar<byte>("SELECT COUNT(*) FROM TABLENAME", cmdType: CommandType.Text);

Advanced feature (Coming soon)

ADO.NET is not hard to use, but as any low level component it requires a lot of plumbing. It requires you to explicitly open connections and remember to close them. It requires you to convert values and handle DBNulls. As you work with it, it becomes clear that many things could be automated. This library is basically a lot of overloads that do most of this plumbing and let you concentrate on what you need to do. It returns storng type values.

Once instantiated, DBHelper supports executing queries directly to the database and returning useful types in a single command. For example:

// returning a int scalar value
var count = _dbHelper.ExecuteScalar<int>("SELECT COUNT(*) FROM COUNTRY", cmdType: CommandType.Text);


// get int array 
int[] arrayInt = _dbHelper.GetArray<int>("SELECT Id FROM COUNTRY", cmdType: CommandType.Text);

// get string array 
string[] arrayString = _dbHelper.GetArray<string>("SELECT Name FROM COUNTRY", cmdType: CommandType.Text);


// get key-pair value result in Dictionary
Dictionary<int, string>  dictNameAndId = _dbHelper.GetDictionary<int, string>("SELECT Id, Name FROM COUNTRY", cmdType: CommandType.Text);


// get strong type object
// Stored procedure
var country = _dbHelper.GetSingle<Country>("GetCountryById", paramId, paramResultOut);
// OR
// Raw sql commnad
var country = _dbHelper.GetSingle<Country>("SELECT * FROM Country WHERE Id = @Id", CommandType.Text, paramId);


// get strong type list
// Stored procedure
var countries = _dbHelper.GetList<Country>("GetCountries", paramId, paramResultOut);
// OR
// Raw sql commnad
var countries = _dbHelper.GetList<Country>("SELECT * FROM Country WHERE IsActive = @IsActive", CommandType.Text, paramIsActive);

Automatic Type Conversion (Coming soon)

---

When loading data from the database, values can be null/DBNull or can be of a slightly different type. DBHelpers adds some extension methods to DbDataReader, so you can safely expect certain types.

This is how you can read data from a table to a list of anonymous objects for quick use:

var list = _dbHelper.GetList("SELECT Id, Name, NumericIsoCode FROM Country", cmdType: CommandType.Text, r => new {
    ID = r.Get<int>("Id"),
    Name = r.Get<string>("Name"),
    IsoCode = r.Get<int?>("NumericIsoCode")
});

Basic Usage :page_facing_up:

Step 1 : Install the package :package:

Choose one kinds of sms provider type that you needs and install it via Nuget. To install LiteXDbHelper, run the following command in the Package Manager Console

PM> Install-Package LiteX.DbHelper.SqlServer
PM> Install-Package LiteX.DbHelper.MySql
PM> Install-Package LiteX.DbHelper.PostgreSql
PM> Install-Package LiteX.DbHelper.MariaDB
PM> Install-Package LiteX.DbHelper.Oracle

Step 2 : Configuration 🔨

Different types of database provider have their own way to config. Here are samples that show you how to config.

2.1 : AppSettings

{
  "ConnectionStrings": {
    "LiteXConnection": "Server=AASHISH-PC;Database=LiteXDB;user id=sa;password=PASSWORD;Trusted_Connection=True MultipleActiveResultSets=true;"
}
}

2.2 : Configure Startup Class

// No configuration required

Step 3 : Use in Controller or Business layer :memo:

 /// <summary>
/// Country controller
/// </summary>
[Route("api/[controller]")]
public class CountryController : Controller
{
    #region Fields

    public readonly string _connectionString;
    private readonly IDbHelper _dbHelper;
    private readonly DbProvider _dbProvider;

    #endregion

    #region Ctor

    /// <summary>
    /// Ctor
    /// </summary>
    public CountryController(IConfiguration configuration)
    {
        _dbProvider = configuration.GetValue<DbProvider>("DbProvider");

        switch (_dbProvider)
        {
            case DbProvider.SqlServer:
                _connectionString = configuration.GetConnectionString("SqlServerConnection");
                _dbHelper = new SqlHelper(_connectionString);
                break;
            //case DbProvider.MySql:
            //    _connectionString = configuration.GetConnectionString("MySqlConnection");
            //    _dbHelper = new LiteX.DbHelper.MySql.MySqlHelper(_connectionString);
            //    break;
            //case DbProvider.NpgSql:
            //    _connectionString = configuration.GetConnectionString("NpgSqlConnection");
            //    _dbHelper = new NpgsqlHelper(_connectionString);
            //    break;
            //case DbProvider.MariaDB:
            //    _conne