VTNET.Vitado 2.0.7

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:

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.

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:

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

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

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