VTNET.Vitado 7.2.0

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. 👌

Simple using

var db = new VitProvider("Data Source=.;Database=...;Integrated Security=sspi;Encrypt=true;TrustServerCertificate=true;Trusted_Connection=True;");

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=...");

• But I recommend you to inherit the IVitProvider Interface and VitProvider class even if you don't have any modifications.

//IMyProvider.cs
public interface IMyProvider : IVitProvider{}

//MyProvider.cs
public class MyProvider : VitProvider, IMyProvider{}

builder.Services.AddVitado<IMyProvider, MyProvider>("Data Source=.;Initial Catalog=...;MultipleActiveResultSets=True;User Id=...;Password=...");

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

Class Structure

• VitProviderBase: Defining the common architecture for 'Execution'
• VitProvider inherit VitProviderBase: handle life circle connection and add define all Execute methods.
  • This class only defines the most basic methods (can be used for sqlserver), I recommend you not to use it directly, you should inherit it with your own class for easy modification in the future.
• 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'
• VitProviderBeta inherit VitProvider: this class will contain experimental code snippets.
• VitProviderScope inherit VitProviderBeta: like VitProviderScope. But, this is beta.

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

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]");
    var dataTable = db.ExecuteTable("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)) );
    var dataTable = db.ExecuteTable("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"} }

• parameter out

    var table_test_stored = provider.Execute("[MyStoredProcedure]", 
        new(("@InputParam", "xxxx")),
        new ()
            {
                {"@OutputParam", DbType.String},
                {"@OutputParamSize", DbType.String, int.MaxValue },
                {"@InOutParam", 69 }
            }
        );

• await async

    var rowsAffected = await db.ExecuteAsync("select * from [Customers]");
    var list = await db.ExecuteAsync<ReportParams>("select * from [Customers]");
    var data = await db.ExecuteFirstAsync<ReportParams>("select * from [Customers]");
    var id = await db.ExecuteCellAsync<int>("select * from [Customers]");
    var dataTable = await db.ExecuteTableAsync("select * from [Customers]");

    var rowsAffected = await db.ExecuteAsync("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var list = await db.ExecuteAsync<ReportParams>("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var data = await db.ExecuteFirstAsync<ReportParams>("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var id = await db.ExecuteCellAsync<int>("select * from [Customers] where Id = @Id", new(("@id", 69)) );
    var dataTable = await db.ExecuteTableAsync("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: The usage is similar to the example above, but you only need to provide the above stored procedure and parameters if necessary.

    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");

Execute multiple

• This method uses ProviderScope to keep the transaction.

var affected = provider.ExecuteMulti(db =>
{
    var rowaffected = 0;
    rowaffected += db.Execute("UPDATE [Customers] SET [Name] = @name WHERE id = @id", new(("@name", "test1"),("@id", 60)));
    rowaffected += db.Execute("UPDATE [Customers] SET [Name] = @name WHERE id = @id", new(("@name", "test2"), ("@id", 61)));
    rowaffected += db.Execute("UPDATE [Customers] SET [Name] = @name WHERE id = @id", new(("@name", "test3"), ("@id", 62)));
    rowaffected += db.Execute("UPDATE [Customers] SET [Name] = @name WHERE id = @id", new(("@name", "test4"), ("@id", 63)));
    return rowaffected;
});
Console.WriteLine(affected);

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 (beta: This method may change in the future.)

    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 SQLite

• using MySqlConnector dotnet add package Microsoft.Data.Sqlite --version 8.0.6
• new file MySqlProvider and inheritance VitProvider

internal class SqliteProvider : VitProvider
{
    public SqliteProvider(string connectionString) : base(connectionString)
    {
    }
    protected override DbConnection Connection() => new SqliteConnection(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.cs

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

• Program.cs

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);

Solutions for multiple results

If you have a "stored procedure" that returns multiple results like this, you can implement the method as shown below:

CREATE PROCEDURE [dbo].[RP_DM_MAU]
		@pUID		int = 0,
        @pLang		nvarchar(5)		= 'vi',
		@pTest		NVARCHAR(max)	= 'hehehe' OUT,
		@pTest1		NVARCHAR(max)	= 'hahaha' OUT

AS

select @pUID uid, @pLang lang

select 'Test1' Test1, 'Test2' Test2, 'Test3' Test3, 'Test4' Test4, 'Test5' Test5

select @pTest outTest, @pTest1 outTest1

return 0

GO

Currently, with "stored procedures" that return multiple results, "Vitado" has not found a simple and effective solution, so it has not implemented any official solution yet. But "Vitado" still provides some basic methods for you to implement those functions yourself.

public class CustomProvider : VitProvider, ICustomProvider
{
    public CustomProvider(string connectionString) : base(connectionString)
    {
    }
    public bool IsStoredProcedure(string query) => !query.Contains(' ');
    
    //Get multiple rusults with "List<DataTable>"
    public List<DataTable> ExecuteTables(string query) => BaseExecute(query, FuncExecuteTables);
    public List<DataTable> ExecuteTables(string query, VitParams paramsIn) => BaseExecute(query, paramsIn, FuncExecuteTables);
    public VitParamOutDataResult<List<DataTable>> ExecuteTables(string query, VitParams paramsIn, VitParamsOut paramsOut) => BaseExecute(query, paramsIn, paramsOut, FuncExecuteTables);

    public Task<List<DataTable>> ExecuteTablesAsync(string query) => BaseExecute(query, FuncExecuteTablesAsync);
    public Task<List<DataTable>> ExecuteTablesAsync(string query, VitParams paramsIn) => BaseExecute(query, paramsIn, FuncExecuteTablesAsync);
    public Task<VitParamOutDataResult<List<DataTable>>> ExecuteTablesAsync(string query, VitParams paramsIn, VitParamsOut paramsOut) => BaseExecute(query, paramsIn, paramsOut, FuncExecuteTablesAsync);
    
    //Get multiple rusults with "DbMultiResultsModel"
    public T ExecuteMultiResults<T>(string query) where T : new()
    {
        if (typeof(T) == typeof(List<DataTable>))
        {
            return (T)(object)BaseExecute(query, FuncExecuteTables);
        }
        else if (typeof(T).IsSubclassOf(typeof(DbMultiResultsModel)))
        {
            var model = (DbMultiResultsModel)(object)new T();
            if (model.ResultFields.Count < 1) return new();
            return BaseExecute(query, command =>
            {
                var reader = command.ExecuteReader();
                var indexField = 0;
                do
                {
                    model.ResultFields[indexField++].SetValue(reader);
                } while (reader.NextResult());
                return (T)(object)model;
            });
        }
        return new();
    }
}

"DbMultiResultsModel" is a base model class that provides "DbResultList" and "DbResult" management methods.

An important note is that all properties must be assigned "new()" by default.

internal class TesMultiResultModel : DbMultiResultsModel
{
    public DbResultList<Mau> List { get; set; } = new(); //new() default
    public DbResult<TestResult2> Test { get; set; } = new(); //new() default
}
public class Mau
{
    public string Test1 { get; set; } = "";
    public string Test2 { get; set; } = "";
    public string Test3 { get; set; } = "";
    public string Test4 { get; set; } = "";
    public string Test5 { get; set; } = "";
}
public class TestResult2
{
    [MapName("outTest")]
    public string OutTest { get; set; } = ""; 
    [MapName("outTest1")]
    public string OutTest1 { get; set; } = "";
}

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