Vitado.MySql 7.0.0-beta.3

This is a library that inherits Vitado and implements it for MySQL.

IVitProvider Interface

Overview

The IVitProvider interface provides a structured approach for executing database queries, retrieving results, and managing stored procedure parameters efficiently. It supports both synchronous and asynchronous operations, allowing developers to interact with databases in a flexible and performant manner.

Features

• Query Execution: Execute SQL queries and retrieve results in different formats.
• Parameter Handling: Manage input and output parameters seamlessly.
• Async Support: Improve application responsiveness with asynchronous methods.
• Strong Typing: Generic methods ensure type safety and reduce casting errors.
• Flexible Return Types: Support for various data structures such as lists, single values, and DataTable.

Methods

Execute Methods

These methods execute a query and return the number of affected rows, useful for INSERT, UPDATE, and DELETE statements.

int Execute(string query);
int Execute(string query, VitParams paramsIn);
VitParamOutDataResult<int> Execute(string query, VitParams paramsIn, VitParamsOut paramsOut);

Async Versions

Task<int> ExecuteAsync(string query);
Task<int> ExecuteAsync(string query, VitParams paramsIn);
Task<VitParamOutDataResult<int>> ExecuteAsync(string query, VitParams paramsIn, VitParamsOut paramsOut);

Query Execution with Result List

These methods execute a query and return the results as a list of objects of type T, providing a way to retrieve multiple records efficiently.

List<T> Execute<T>(string query) where T : new();
List<T> Execute<T>(string query, VitParams paramsIn) where T : new();
VitParamOutDataResult<List<T>> Execute<T>(string query, VitParams paramsIn, VitParamsOut paramsOut) where T : new();

Async Versions

Task<List<T>> ExecuteAsync<T>(string query) where T : new();
Task<List<T>> ExecuteAsync<T>(string query, VitParams paramsIn) where T : new();
Task<VitParamOutDataResult<List<T>>> ExecuteAsync<T>(string query, VitParams paramsIn, VitParamsOut paramsOut) where T : new();

Query Execution with Single Result

These methods execute a query and return the first result as an object of type T. Useful for retrieving a single record efficiently.

T? ExecuteFirst<T>(string query) where T : new();
T? ExecuteFirst<T>(string query, VitParams paramsIn) where T : new();
VitParamOutDataResult<T?> ExecuteFirst<T>(string query, VitParams paramsIn, VitParamsOut paramsOut) where T : new();

Async Versions

Task<T?> ExecuteFirstAsync<T>(string query) where T : new();
Task<T?> ExecuteFirstAsync<T>(string query, VitParams paramsIn) where T : new();
Task<VitParamOutDataResult<T?>> ExecuteFirstAsync<T>(string query, VitParams paramsIn, VitParamsOut paramsOut) where T : new();

Query Execution with Single Value

These methods retrieve the data from the first cell of the first row in the result set, ideal for scalar values such as counts and sums.

T? ExecuteValue<T>(string query);
T? ExecuteValue<T>(string query, VitParams paramsIn);
VitParamOutDataResult<T?> ExecuteValue<T>(string query, VitParams paramsIn, VitParamsOut paramsOut);

Async Versions

Task<T?> ExecuteValueAsync<T>(string query);
Task<T?> ExecuteValueAsync<T>(string query, VitParams paramsIn);
Task<VitParamOutDataResult<T?>> ExecuteValueAsync<T>(string query, VitParams paramsIn, VitParamsOut paramsOut);

Query Execution with DataTable

These methods execute a query and return the results as a DataTable, suitable for dynamic data processing.

DataTable ExecuteTable(string query);
DataTable ExecuteTable(string query, VitParams paramsIn);
VitParamOutDataResult<DataTable> ExecuteTable(string query, VitParams paramsIn, VitParamsOut paramsOut);

Async Versions

Task<DataTable> ExecuteTableAsync(string query);
Task<DataTable> ExecuteTableAsync(string query, VitParams paramsIn);
Task<VitParamOutDataResult<DataTable>> ExecuteTableAsync(string query, VitParams paramsIn, VitParamsOut paramsOut);

Parameters

• query: The SQL query to execute.
• paramsIn: Input parameters for queries and stored procedures.
• paramsOut: Output parameters for stored procedures, including result sets and out values.

Return Values

• int: The number of rows affected.
• List<T>: A list of objects of type T.
• T?: The first result of type T, or null if no data is available.
• DataTable: A table containing the query results.
• VitParamOutDataResult<T>: An object that contains output parameters along with the result data.

Example Usage

Executing a Non-Query Command

IVitProvider provider = new VitProvider();
string query = "UPDATE Users SET Status = 'Active' WHERE Id = @Id";
VitParams parameters = new VitParams().Add("Id", 123);
int rowsAffected = provider.Execute(query, parameters);

Executing an Async Query

IVitProvider provider = new VitProvider();
string query = "SELECT * FROM Users WHERE Status = 'Active'";
List<User> users = await provider.ExecuteAsync<User>(query);

Retrieving a Single Value

IVitProvider provider = new VitProvider();
string query = "SELECT COUNT(*) FROM Users";
int userCount = provider.ExecuteValue<int>(query);

Calling a Stored Procedure with Output Parameters

IVitProvider provider = new VitProvider();
string query = "EXEC GetUserById @Id, @UserName OUTPUT";
VitParams inputParams = new VitParams().Add("Id", 123);
VitParamsOut outputParams = new VitParamsOut().Add("UserName", DbType.String);
VitParamOutDataResult<User> result = provider.ExecuteFirst<User>(query, inputParams, outputParams);
string userName = outputParams.Get<string>("UserName");

Using DataTable for Dynamic Queries

IVitProvider provider = new VitProvider();
string query = "SELECT * FROM Orders WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate";
VitParams parameters = new VitParams()
    .Add("StartDate", new DateTime(2024, 1, 1))
    .Add("EndDate", new DateTime(2024, 12, 31));
DataTable ordersTable = provider.ExecuteTable(query, parameters);

Summary

The IVitProvider interface offers a robust and extensible solution for executing database queries efficiently. By supporting both synchronous and asynchronous execution, handling stored procedure parameters seamlessly, and providing a structured approach to data retrieval, IVitProvider enhances database interaction in modern applications.