VTNET.Vitado 7.2.0-beta.1

ADO.NET Wrapper Library

Introduction

This library is designed to simplify ADO.NET interactions by wrapping around ADO.NET and using the SqlDataReader class to read data. It provides a set of methods for executing queries and stored procedures and mapping the results to objects, making database interactions in your .NET applications more straightforward and efficient.

Commands

The library offers a variety of commands to execute and interact with your database:

The Motivation

The genesis of this library stems from the challenges encountered during a project that required database-first development. The project demanded dealing with complex database requirements, such as seamlessly adapting to changes like renaming parameters in stored procedures without necessitating corresponding modifications in C# code, handling table alterations like adding or removing columns without affecting the C# Insert and Update logic, and more. In essence, the project's data structure was highly dynamic and prone to alterations.

While searching for existing libraries to address these unique demands, it became evident that finding a suitable solution was a daunting task (or my search skills were not up to the challenge! 🤣) Consequently. I embarked on the journey to develop this library to cater to the project's specific needs and ensure a smoother development process. 👌

Integration with ASP/Blazor

You can easily integrate this library into your ASP.NET or Blazor applications. To get started, add the following code to your Startup.cs or Program.cs:

builder.Services.AddVitado("Data Source=.;Initial Catalog=...;MultipleActiveResultSets=True;User Id=...;Password=...");

Make sure to replace the connection string with your specific database details.

I no longer differentiate between 'Query' and 'Stored', now there’s only one keyword: 'Execute.'

Execute

• Execute: Execute and return the number of affected rows.
• Execute<T>: Execute and return a list of mapped data.
• ExecuteAsync: Asynchronously execute and return the number of affected rows.
• ExecuteAsync<T>: Asynchronously execute and return a list of mapped data.
• ExecuteTable: Execute and return the result as a DataTable.
• ExecuteTableAsync: Asynchronously execute and return the result as a DataTable.
• ExecuteFirst<T>: Execute and return the first row as a mapped object.
• ExecuteFirstAsync<T>: Asynchronously execute and return the first row as a mapped object.
• ExecuteCell<T>: Execute and return the first cell of the first row as a mapped object.
• ExecuteCellAsync<T>: Asynchronously execute and return the first cell of the first row as a mapped object.

How to Use

Here are examples of how to use the library for querying:

Execute query string

• Simple using

    var rowsAffected = db.Execute("select * from [Customers]");
    var list = db.Execute<ReportParams>("select * from [Customers]");
    var data = db.ExecuteFirst<ReportParams>("select * from [Customers]");
    var id = db.ExecuteCell<int>("select * from [Customers]");

• parameter in

    var rowsAffected = db.Execute("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var list = db.Execute<ReportParams>("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var data = db.ExecuteFirst<ReportParams>("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var id = db.ExecuteCell<int>("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    
    //you can using parameter in like: new(("@id", 69),("@name","abc")) or new(){ {"@id", 69}, {"@name", "abc"} }

Execute stored procedure

• Simple using

    var rowsAffected = db.Execute("SYS_ReportParams");
    var list = db.Execute<ReportParams>("SYS_ReportParams");
    var data = db.ExecuteFirst<ReportParams>("SYS_ReportParams");
    var id = db.ExecuteCell<int>("SYS_ReportParams");

Class Structure

• VitProviderBase: Defining the common architecture for 'Execution'
  • ExecuteCore: this is the core of all methods, you must inherit this method and handle data execute

    //example for inherit ExecuteCore
    protected override T ExecuteCore<T>(SqlCommand command, Func<SqlCommand, T> func)
    {
        //VitProviderBase does not contain a Connection. Therefore, you must specify a connection for the command.
        using SqlConnection connection = new(_connectionString);
        command.Connection = connection;
        //VitProviderBase does not handle life circle connection. Therefore, you must open connection.
        connection.Open(); 
        try
        {
            command.Connection = connection;
            return func(command);
        }
        catch (SqlException)
        {
            connection.Close();
            throw;
        }
        //VitProviderBase does not handle life circle connection. Therefore, you must Close connection.
        finally { connection.Close(); }
    }
    

• VitProvider inherit VitProviderBase: handle life circle connection and add define all Execute methods.
• VitProviderScope inherit VitProvider: handle life circle scope. Connection of VitProviderScope don't close when end method, you have to close the connection yourself or use 'using'

  db.Dispose(); //Closed by yourself.
  

  using db = new VitProviderScope(connectString); //The connection will close when the scope ends.
  

• VitProviderBeta inherit VitProvider: this class will contain experimental code snippets.
• VitProviderScope inherit VitProviderBeta: like VitProviderScope. But, this is beta.
• DBService:

All the classes above allow you to inherit and modify them as you wish.

Example for custom VitProvider

public class VitProviderScope : VitProvider
{
    public VitProvider(string connectionString) : base(connectionString){}
    protected override T ExecuteCore<T>(SqlCommand command, Func<SqlCommand, T> func)
    {
        //Your code...
        return base.ExecuteCore(command, func);
    }
}

Custom Name Compare

    VitMapper.NameCompare = (string name, string dbName) =>
    {
        return name.Equals(dbName.Replace("_",""), StringComparison.OrdinalIgnoreCase);
    };

Support MySql

• using MySqlConnector dotnet add package MySqlConnector --version 2.3.7
• new file MySqlProvider and inheritance VitProvider

internal class MySqlProvider : VitProvider
{
    public MySqlProvider(string connectionString) : base(connectionString)
    {
    }
    protected override DbConnection Connection() => new MySqlConnection(ConnectionString);
}

Support Linq

• Context file

internal class MyContext : VitContext
{
    public MyContext(IVitProvider provider) : base(provider)
    {
        MapTable<Customer>("Customers");
    }

    public VitSet<Customer> Customers { get; set; } = default!;
}

• Customer file

class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Address { get; set; } = "";
}

• Program file

var context = new MyContext(new VitProviderBeta("Data Source=.;Initial Catalog=TestDB;MultipleActiveResultSets=True;Integrated Security=True;"));

var query = from data in context.Customers select data;

foreach (var name in query)
{
    Console.WriteLine(name.Id);
}

• Now, you can execute mysql.

Get Parameters Of Stored Procedure And Mapping Type

var vitParams = db.StoredParams("[dbo].[SYS_ReportParams]");
foreach (var item in vitParams)
{
    var type = item.Type.GetTypeMap(); // mapping SqlDbType to Type
    var defaultValue = type.GetDefaultValue(); // get default value of type
    Console.WriteLine($"Type:{type}, ValueDefault:{defaultValue}");
}

Solutions For DynamicData

// Execute stored procedures without regard to parameters
var dataStored = db.ExecuteTable("[Login]", new("Username","Password", true));

// Execute stored procedures without regard to parameters but with paramsout
var paramsAuto = db.GetProcedureParams("[SYS_ReportParams]").ToVitParamsAuto();
var dataStored1 = db.StoredTable("[SYS_ReportParams]", paramsAuto.In, paramsAuto.Out);

You can now seamlessly work with your database using this ADO.NET wrapper library, saving time and effort in your .NET applications.