VTNET.Vitado 7.1.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:

New Version

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.
• ExecuteOne<T>: Execute and return the first cell of the first row as a mapped object.
• ExecuteOneAsync<T>: Asynchronously execute and return the first cell of the first row as a mapped object.

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

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

Old Version

Query

• Query: Execute a query and return the number of affected rows.
• Query<T>: Execute a query and return a list of mapped data.
• QueryAsync: Asynchronously execute a query and return the number of affected rows.
• QueryAsync<T>: Asynchronously execute a query and return a list of mapped data.
• QueryTable: Execute a query and return the result as a DataTable.
• QueryTableAsync: Asynchronously execute a query and return the result as a DataTable.
• QueryFirst<T>: Execute a query and return the first row as a mapped object.
• QueryFirstAsync<T>: Asynchronously execute a query and return the first row as a mapped object.
• QueryOne<T>: Execute a query and return the first cell of the first row as a mapped object.
• QueryOneAsync<T>: Asynchronously execute a query and return the first cell of the first row as a mapped object.

Stored Procedure

• Stored: Execute a stored procedure and return the number of affected rows.
• Stored<T>: Execute a stored procedure and return a list of mapped data.
• StoredAsync: Asynchronously execute a stored procedure and return the number of affected rows.
• StoredAsync<T>: Asynchronously execute a stored procedure and return a list of mapped data.
• StoredTable: Execute a stored procedure and return the result as a DataTable.
• StoredTableAsync: Asynchronously execute a stored procedure and return the result as a DataTable.
• StoredFirst<T>: Execute a stored procedure and return the first row as a mapped object.
• StoredFirstAsync<T>: Asynchronously execute a stored procedure and return the first row as a mapped object.
• StoredOne<T>: Execute a stored procedure and return the first cell of the first row as a mapped object.
• StoredOneAsync<T>: Asynchronously execute a stored procedure and return the first cell of the first row as a mapped object.

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.

How to Use

Query

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

// Query and map data
List<DBOrderTable> fbBanLeTam = await db.QueryAsync<DBOrderTable>("SELECT * FROM [dbo].[FB_BANLE_TAM]");
DBOrderTable fbBanLeTam = await db.QueryFirstAsync<DBOrderTable>("SELECT TOP 1 * FROM [dbo].[FB_BANLE_TAM]");
string name = await db.QueryOneAsync<string>("SELECT TOP 1 * FROM [dbo].[FB_BANLE_TAM]");

// Parameters
var inforUser = await db.QueryFirstAsync<DB_SYS_USER>(
    query: "SELECT [UserName], [FullName], [Email], [PassWD], [UID], [Is_Employee], [Manv], [Must_Chg_Pwd] FROM [ERP_SOFTZ].[dbo].[SYS_USER] WHERE UID = @UID",
    paramsIn: new(("@UID", 241))
);

Stored Procedure

For using stored procedures, follow these examples:

// Query and map data
List<DBOrderTable> fbBanLeTam = await db.StoredAsync<DBOrderTable>("[dbo].[SYS_ReportParams]");
DBOrderTable fbBanLeTam = await db.StoredFirstAsync<DBOrderTable>("[dbo].[SYS_ReportParams]");

// Parameters
var inforUser = await db.QueryFirstAsync<DB_SYS_USER>(
    stored: "[dbo].[SYS_ReportParams]",
    paramsIn: new(("@UID", 241))
);

// Parameters with Output
var dataStored1 = db.StoredTable("[dbo].[SYS_ReportParams]", new(("@UID", 241), ("CODE", "NV1001")), new(("@Name", ""), ("@TestOutParams", 0)));

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.StoredTable("[dbo].[Login]", new("Username","Password", true));

// Execute stored procedures without regard to parameters but with paramsout
var vitParamsAuto = db.StoredParams("[dbo].[SYS_ReportParams]").ToVitParamsAuto();
var dataStored1 = db.StoredTable("[dbo].[SYS_ReportParams]", vitParamsAuto.Item1, vitParamsAuto.Item2);

// Insert and update
var dataTable = await db.QueryTableAsync("select * from [dbo].[Customers]");
var vitG = new VitTrack("Customers", dataTable.Rows[0]);
vitG.Row["Name"] = "insert name";
var insert = vitG.Insert();
db.Execute(insert);
vitG.Row["Email"] = "update@email.com";
var update = vitG.Update(conditionColumn:"Name");
db.Execute(update);