/*
* Copyright (c) 2022 Vaughn Nugent
*
* Library: VNLib
* Package: VNLib.Plugins.Extensions.Data
* File: DbExtensions.cs
*
* DbExtensions.cs is part of VNLib.Plugins.Extensions.Data which is part of the larger
* VNLib collection of libraries and utilities.
*
* VNLib.Plugins.Extensions.Data is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* VNLib.Plugins.Extensions.Data is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see https://www.gnu.org/licenses/.
*/
using System;
using System.Data;
using System.Reflection;
using System.Data.Common;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using VNLib.Utils;
using VNLib.Utils.Memory.Caching;
namespace VNLib.Plugins.Extensions.Data.SQL
{
///
/// Provides basic extension methods for ADO.NET abstract classes
/// for rapid development
///
public static class DbExtensions
{
/*
* Object rental for propery dictionaries used for custom result objects
*/
private static ObjectRental> DictStore { get; } = ObjectRental.Create>(null, static dict => dict.Clear(), 20);
///
/// Creates a new configured for with the specified value
/// and adds it to the command.
///
///
/// The parameter name
/// The value of the parameter
/// The of the column
/// Are null types allowed in the value parameter
/// The created parameter
public static DbParameter AddParameter(this DbCommand cmd, string @name, T @value, DbType @type, bool @nullable = false)
{
//Create the new parameter from command
DbParameter param = cmd.CreateParameter();
//Set parameter variables
param.ParameterName = name;
param.Value = value;
param.DbType = type;
//Force non null mapping
param.SourceColumnNullMapping = nullable;
//Specify input parameter
param.Direction = ParameterDirection.Input;
//Add param to list
cmd.Parameters.Add(param);
return param;
}
///
/// Creates a new configured for with the specified value
/// and adds it to the command.
///
///
/// The parameter name
/// The value of the parameter
/// The of the column
/// Size of the data value
/// Are null types allowed in the value parameter
/// The created parameter
public static DbParameter AddParameter(this DbCommand cmd, string @name, T @value, DbType @type, int @size, bool @nullable = false)
{
DbParameter param = AddParameter(cmd, name, value, type, nullable);
//Set size parameter
param.Size = size;
return param;
}
///
/// Creates a new configured for with the specified value
/// and adds it to the command.
///
///
/// The parameter name
/// The value of the parameter
/// The of the column
/// Are null types allowed in the value parameter
/// The created parameter
public static DbParameter AddOutParameter(this DbCommand cmd, string @name, T @value, DbType @type, bool @nullable = false)
{
//Create the new parameter from command
DbParameter param = AddParameter(cmd, name, value, type, nullable);
//Specify output parameter
param.Direction = ParameterDirection.Output;
return param;
}
///
/// Creates a new configured for with the specified value
/// and adds it to the command.
///
///
/// The parameter name
/// The value of the parameter
/// The of the column
/// Size of the data value
/// Are null types allowed in the value parameter
/// The created parameter
public static DbParameter AddOutParameter(this DbCommand cmd, string @name, T @value, DbType @type, int @size, bool @nullable = false)
{
DbParameter param = AddOutParameter(cmd, name, value, type, nullable);
//Set size parameter
param.Size = size;
return param;
}
///
/// Creates a new for with the specified command
///
///
/// The command to run against the connection
/// The initalized
public static DbCommand CreateTextCommand(this DbConnection db, string cmdText)
{
//Create the new command
DbCommand cmd = db.CreateCommand();
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text; //Specify text command
return cmd;
}
///
/// Creates a new for with the specified procedure name
///
///
/// The name of the stored proecedure to execute
/// The initalized
public static DbCommand CreateProcedureCommand(this DbConnection db, string procedureName)
{
//Create the new command
DbCommand cmd = db.CreateCommand();
cmd.CommandText = procedureName;
cmd.CommandType = CommandType.StoredProcedure; //Specify stored procedure
return cmd;
}
///
/// Creates a new for with the specified command
/// on a given transaction
///
///
/// The command to run against the connection
/// The transaction to execute on
/// The initalized
public static DbCommand CreateTextCommand(this DbConnection db, string cmdText, DbTransaction transaction)
{
return CreateCommand(db, transaction, CommandType.Text, cmdText);
}
///
/// Shortcut to create a command on a transaction with the specifed command type and command
///
///
/// The transaction to complete the operation on
/// The command type
/// The command to execute
/// The intialized db command
public static DbCommand CreateCommand(this DbConnection db, DbTransaction transaction, CommandType type, string command)
{
//Create the new command
DbCommand cmd = db.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = command;
cmd.CommandType = type;
return cmd;
}
///
/// Creates a new for with the specified procedure name
///
///
/// The name of the stored proecedure to execute
/// The transaction to execute on
/// The initalized
public static DbCommand CreateProcedureCommand(this DbConnection db, string procedureName, DbTransaction transaction)
{
return CreateCommand(db, transaction, CommandType.StoredProcedure, procedureName);
}
///
/// Reads all available rows from the reader, adapts columns to public properties with
/// attributes, and adds them to the collection
///
///
///
/// The container to write created objects to
/// The number of objects created and written to the collection
public static int GetAllObjects(this DbDataReader reader, ICollection container) where T : new()
{
//make sure its worth collecting object meta
if (!reader.HasRows)
{
return 0;
}
Type objectType = typeof(T);
//Rent a dict of properties that have the column attribute set so we can load the proper results
Dictionary avialbleProps = DictStore.Rent();
//Itterate through public properties
foreach (PropertyInfo prop in objectType.GetProperties())
{
//try to get the column name attribute of the propery
SqlColumnNameAttribute? colAtt = prop.GetCustomAttribute(true);
//Attribute is valid and coumn name is not empty
if (!string.IsNullOrWhiteSpace(colAtt?.ColumnName))
{
//Store the property for later
avialbleProps[colAtt.ColumnName] = prop;
}
}
//Get the column schema
ReadOnlyCollection columns = reader.GetColumnSchema();
int count = 0;
//Read
while (reader.Read())
{
//Create the new object
T ret = new();
//Iterate through columns
foreach (DbColumn col in columns)
{
//Get the propery if its specified by its column-name attribute
if (avialbleProps.TryGetValue(col.ColumnName, out PropertyInfo? prop))
{
//make sure the column has a value
if (col.ColumnOrdinal.HasValue)
{
//Get the object
object val = reader.GetValue(col.ColumnOrdinal.Value);
//Set check if the row is DB null, if so set it, otherwise set the value
prop.SetValue(ret, Convert.IsDBNull(val) ? null : val);
}
}
}
//Add the object to the collection
container.Add(ret);
//Increment count
count++;
}
//return dict (if an error occurs, just let the dict go and create a new one next time, no stress setting up a try/finally block)
DictStore.Return(avialbleProps);
return count;
}
///
/// Reads all available rows from the reader, adapts columns to public properties with
/// attributes, and adds them to the collection
///
///
///
/// The container to write created objects to
/// The number of objects created and written to the collection
public static async ValueTask GetAllObjectsAsync(this DbDataReader reader, ICollection container) where T : new()
{
//make sure its worth collecting object meta
if (!reader.HasRows)
{
return 0;
}
Type objectType = typeof(T);
//Rent a dict of properties that have the column attribute set so we can load the proper results
Dictionary avialbleProps = DictStore.Rent();
//Itterate through public properties
foreach (PropertyInfo prop in objectType.GetProperties())
{
//try to get the column name attribute of the propery
SqlColumnNameAttribute? colAtt = prop.GetCustomAttribute(true);
//Attribute is valid and coumn name is not empty
if (!string.IsNullOrWhiteSpace(colAtt?.ColumnName))
{
//Store the property for later
avialbleProps[colAtt.ColumnName] = prop;
}
}
//Get the column schema
ReadOnlyCollection columns = await reader.GetColumnSchemaAsync();
int count = 0;
//Read
while (await reader.ReadAsync())
{
//Create the new object
T ret = new();
//Iterate through columns
foreach (DbColumn col in columns)
{
//Get the propery if its specified by its column-name attribute
if (avialbleProps.TryGetValue(col.ColumnName, out PropertyInfo? prop))
{
//make sure the column has a value
if (col.ColumnOrdinal.HasValue)
{
//Get the object
object val = reader.GetValue(col.ColumnOrdinal.Value);
//Set check if the row is DB null, if so set it, otherwise set the value
prop.SetValue(ret, Convert.IsDBNull(val) ? null : val);
}
}
}
//Add the object to the collection
container.Add(ret);
//Increment count
count++;
}
//return dict (if an error occurs, just let the dict go and create a new one next time, no stress setting up a try/finally block)
DictStore.Return(avialbleProps);
return count;
}
///
/// Reads the first available row from the reader, adapts columns to public properties with
///
///
///
/// The created object, or default if no rows are available
public static T? GetFirstObject(this DbDataReader reader) where T : new()
{
//make sure its worth collecting object meta
if (!reader.HasRows)
{
return default;
}
//Get the object type
Type objectType = typeof(T);
//Get the column schema
ReadOnlyCollection columns = reader.GetColumnSchema();
//Read
if (reader.Read())
{
//Rent a dict of properties that have the column attribute set so we can load the proper results
Dictionary availbleProps = DictStore.Rent();
//Itterate through public properties
foreach (PropertyInfo prop in objectType.GetProperties())
{
//try to get the column name attribute of the propery
SqlColumnNameAttribute? colAtt = prop.GetCustomAttribute(true);
//Attribute is valid and coumn name is not empty
if (colAtt != null && !string.IsNullOrWhiteSpace(colAtt.ColumnName))
{
//Store the property for later
availbleProps[colAtt.ColumnName] = prop;
}
}
//Create the new object
T ret = new();
//Iterate through columns
foreach (DbColumn col in columns)
{
//Get the propery if its specified by its column-name attribute
if (availbleProps.TryGetValue(col.ColumnName, out PropertyInfo? prop) && col.ColumnOrdinal.HasValue)
{
//Get the object
object val = reader.GetValue(col.ColumnOrdinal.Value);
//Set check if the row is DB null, if so set it, otherwise set the value
prop.SetValue(ret, Convert.IsDBNull(val) ? null : val);
}
}
//Return dict, no stress if error occurs, the goal is lower overhead
DictStore.Return(availbleProps);
//Return the new object
return ret;
}
return default;
}
///
/// Reads the first available row from the reader, adapts columns to public properties with
///
///
///
/// The created object, or default if no rows are available
public static async Task GetFirstObjectAsync(this DbDataReader reader) where T : new()
{
//Read
if (await reader.ReadAsync())
{
//Get the object type
Type objectType = typeof(T);
//Get the column schema
ReadOnlyCollection columns = await reader.GetColumnSchemaAsync();
//Rent a dict of properties that have the column attribute set so we can load the proper results
Dictionary availbleProps = DictStore.Rent();
//Itterate through public properties
foreach (PropertyInfo prop in objectType.GetProperties())
{
//try to get the column name attribute of the propery
SqlColumnNameAttribute? colAtt = prop.GetCustomAttribute(true);
//Attribute is valid and coumn name is not empty
if (colAtt != null && !string.IsNullOrWhiteSpace(colAtt.ColumnName))
{
//Store the property for later
availbleProps[colAtt.ColumnName] = prop;
}
}
//Create the new object
T ret = new();
//Iterate through columns
foreach (DbColumn col in columns)
{
//Get the propery if its specified by its column-name attribute
if (availbleProps.TryGetValue(col.ColumnName, out PropertyInfo? prop) && col.ColumnOrdinal.HasValue)
{
//Get the object
object val = reader.GetValue(col.ColumnOrdinal.Value);
//Set check if the row is DB null, if so set it, otherwise set the value
prop.SetValue(ret, Convert.IsDBNull(val) ? null : val);
}
}
//Return dict, no stress if error occurs, the goal is lower overhead
DictStore.Return(availbleProps);
//Return the new object
return ret;
}
return default;
}
///
/// Executes a nonquery operation with the specified command using the object properties set with the
/// attributes
///
///
///
/// The object containing the properties to write to command variables
/// The number of rows affected
///
///
///
///
///
///
public static ERRNO ExecuteNonQuery(this DbCommand cmd, T obj) where T : notnull
{
if (obj == null)
{
throw new ArgumentNullException(nameof(obj));
}
//Get the objec type
Type objtype = typeof(T);
//Itterate through public properties
foreach (PropertyInfo prop in objtype.GetProperties())
{
//try to get the variable attribute of the propery
SqlVariableAttribute varprops = prop.GetCustomAttribute(true);
//This property is an sql variable, so lets add it
if (varprops == null)
{
continue;
}
//If the command type is text, then make sure the variable is actually in the command, if not, ignore it
if (cmd.CommandType != CommandType.Text || cmd.CommandText.Contains(varprops.VariableName))
{
//Add the parameter to the command list
cmd.AddParameter(varprops.VariableName, prop.GetValue(obj), varprops.DataType, varprops.Size, varprops.IsNullable).Direction = varprops.Direction;
}
}
//Prepare the sql statement
cmd.Prepare();
//Exect the query and return the results
return cmd.ExecuteNonQuery();
}
///
/// Executes a nonquery operation with the specified command using the object properties set with the
/// attributes
///
///
///
/// The object containing the properties to write to command variables
/// The number of rows affected
///
///
///
///
///
///
public static async Task ExecuteNonQueryAsync(this DbCommand cmd, T obj) where T : notnull
{
if (obj == null)
{
throw new ArgumentNullException(nameof(obj));
}
//Get the objec type
Type objtype = typeof(T);
//Itterate through public properties
foreach (PropertyInfo prop in objtype.GetProperties())
{
//try to get the variable attribute of the propery
SqlVariableAttribute? varprops = prop.GetCustomAttribute(true);
//This property is an sql variable, so lets add it
if (varprops == null)
{
continue;
}
//If the command type is text, then make sure the variable is actually in the command, if not, ignore it
if (cmd.CommandType != CommandType.Text || cmd.CommandText.Contains(varprops.VariableName))
{
//Add the parameter to the command list
cmd.AddParameter(varprops.VariableName, prop.GetValue(obj), varprops.DataType, varprops.Size, varprops.IsNullable).Direction = varprops.Direction;
}
}
//Prepare the sql statement
await cmd.PrepareAsync();
//Exect the query and return the results
return await cmd.ExecuteNonQueryAsync();
}
}
}