diff options
Diffstat (limited to 'VNLib.Plugins.Extensions.Data/SQL')
| -rw-r--r-- | VNLib.Plugins.Extensions.Data/SQL/DbExtensions.cs | 526 | ||||
| -rw-r--r-- | VNLib.Plugins.Extensions.Data/SQL/EnumerableTable.cs | 118 | ||||
| -rw-r--r-- | VNLib.Plugins.Extensions.Data/SQL/SqlColumnName.cs | 65 | ||||
| -rw-r--r-- | VNLib.Plugins.Extensions.Data/SQL/SqlVariable.cs | 58 | ||||
| -rw-r--r-- | VNLib.Plugins.Extensions.Data/SQL/TableManager.cs | 65 |
5 files changed, 832 insertions, 0 deletions
diff --git a/VNLib.Plugins.Extensions.Data/SQL/DbExtensions.cs b/VNLib.Plugins.Extensions.Data/SQL/DbExtensions.cs new file mode 100644 index 0000000..e6ee6b1 --- /dev/null +++ b/VNLib.Plugins.Extensions.Data/SQL/DbExtensions.cs @@ -0,0 +1,526 @@ +/* +* 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 General Public License as published +* by the Free Software Foundation, either version 2 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 +* General Public License for more details. +* +* You should have received a copy of the GNU General Public License +* along with VNLib.Plugins.Extensions.Data. If not, see http://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 +{ + /// <summary> + /// Provides basic extension methods for ADO.NET abstract classes + /// for rapid development + /// </summary> + public static class DbExtensions + { + /* + * Object rental for propery dictionaries used for custom result objects + */ + private static readonly ObjectRental<Dictionary<string, PropertyInfo>> DictStore; + + static DbExtensions() + { + //Setup dict store + DictStore = ObjectRental.Create<Dictionary<string, PropertyInfo>>(null, static dict => dict.Clear(), 20); + } + + /// <summary> + /// Creates a new <see cref="DbParameter"/> configured for <see cref="ParameterDirection.Input"/> with the specified value + /// and adds it to the command. + /// </summary> + /// <param name="cmd"></param> + /// <param name="name">The parameter name</param> + /// <param name="value">The value of the parameter</param> + /// <param name="type">The <see cref="DbType"/> of the column</param> + /// <param name="nullable">Are null types allowed in the value parameter</param> + /// <returns>The created parameter</returns> + public static DbParameter AddParameter<T>(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; + } + /// <summary> + /// Creates a new <see cref="DbParameter"/> configured for <see cref="ParameterDirection.Input"/> with the specified value + /// and adds it to the command. + /// </summary> + /// <param name="cmd"></param> + /// <param name="name">The parameter name</param> + /// <param name="value">The value of the parameter</param> + /// <param name="type">The <see cref="DbType"/> of the column</param> + /// <param name="size">Size of the data value</param> + /// <param name="nullable">Are null types allowed in the value parameter</param> + /// <returns>The created parameter</returns> + public static DbParameter AddParameter<T>(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; + } + /// <summary> + /// Creates a new <see cref="DbParameter"/> configured for <see cref="ParameterDirection.Output"/> with the specified value + /// and adds it to the command. + /// </summary> + /// <param name="cmd"></param> + /// <param name="name">The parameter name</param> + /// <param name="value">The value of the parameter</param> + /// <param name="type">The <see cref="DbType"/> of the column</param> + /// <param name="nullable">Are null types allowed in the value parameter</param> + /// <returns>The created parameter</returns> + public static DbParameter AddOutParameter<T>(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; + } + /// <summary> + /// Creates a new <see cref="DbParameter"/> configured for <see cref="ParameterDirection.Output"/> with the specified value + /// and adds it to the command. + /// </summary> + /// <param name="cmd"></param> + /// <param name="name">The parameter name</param> + /// <param name="value">The value of the parameter</param> + /// <param name="type">The <see cref="DbType"/> of the column</param> + /// <param name="size">Size of the data value</param> + /// <param name="nullable">Are null types allowed in the value parameter</param> + /// <returns>The created parameter</returns> + public static DbParameter AddOutParameter<T>(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; + } + + /// <summary> + /// Creates a new <see cref="DbCommand"/> for <see cref="CommandType.Text"/> with the specified command + /// </summary> + /// <param name="db"></param> + /// <param name="cmdText">The command to run against the connection</param> + /// <returns>The initalized <see cref="DbCommand"/></returns> + 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; + } + /// <summary> + /// Creates a new <see cref="DbCommand"/> for <see cref="CommandType.StoredProcedure"/> with the specified procedure name + /// </summary> + /// <param name="db"></param> + /// <param name="procedureName">The name of the stored proecedure to execute</param> + /// <returns>The initalized <see cref="DbCommand"/></returns> + 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; + } + + /// <summary> + /// Creates a new <see cref="DbCommand"/> for <see cref="CommandType.Text"/> with the specified command + /// on a given transaction + /// </summary> + /// <param name="db"></param> + /// <param name="cmdText">The command to run against the connection</param> + /// <param name="transaction">The transaction to execute on</param> + /// <returns>The initalized <see cref="DbCommand"/></returns> + public static DbCommand CreateTextCommand(this DbConnection db, string cmdText, DbTransaction transaction) + { + return CreateCommand(db, transaction, CommandType.Text, cmdText); + } + /// <summary> + /// Shortcut to create a command on a transaction with the specifed command type and command + /// </summary> + /// <param name="db"></param> + /// <param name="transaction">The transaction to complete the operation on</param> + /// <param name="type">The command type</param> + /// <param name="command">The command to execute</param> + /// <returns>The intialized db command</returns> + 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; + } + /// <summary> + /// Creates a new <see cref="DbCommand"/> for <see cref="CommandType.StoredProcedure"/> with the specified procedure name + /// </summary> + /// <param name="db"></param> + /// <param name="procedureName">The name of the stored proecedure to execute</param> + /// <param name="transaction">The transaction to execute on</param> + /// <returns>The initalized <see cref="DbCommand"/></returns> + public static DbCommand CreateProcedureCommand(this DbConnection db, string procedureName, DbTransaction transaction) + { + return CreateCommand(db, transaction, CommandType.StoredProcedure, procedureName); + } + + /// <summary> + /// Reads all available rows from the reader, adapts columns to public properties with <see cref="SqlColumnName"/> + /// attributes, and adds them to the collection + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="reader"></param> + /// <param name="container">The container to write created objects to</param> + /// <returns>The number of objects created and written to the collection</returns> + public static int GetAllObjects<T>(this DbDataReader reader, ICollection<T> 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<string, PropertyInfo> avialbleProps = DictStore.Rent(); + //Itterate through public properties + foreach (PropertyInfo prop in objectType.GetProperties()) + { + //try to get the column name attribute of the propery + SqlColumnName colAtt = prop.GetCustomAttribute<SqlColumnName>(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<DbColumn> 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; + } + /// <summary> + /// Reads all available rows from the reader, adapts columns to public properties with <see cref="SqlColumnName"/> + /// attributes, and adds them to the collection + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="reader"></param> + /// <param name="container">The container to write created objects to</param> + /// <returns>The number of objects created and written to the collection</returns> + public static async ValueTask<int> GetAllObjectsAsync<T>(this DbDataReader reader, ICollection<T> 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<string, PropertyInfo> avialbleProps = DictStore.Rent(); + //Itterate through public properties + foreach (PropertyInfo prop in objectType.GetProperties()) + { + //try to get the column name attribute of the propery + SqlColumnName colAtt = prop.GetCustomAttribute<SqlColumnName>(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<DbColumn> 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; + } + /// <summary> + /// Reads the first available row from the reader, adapts columns to public properties with <see cref="SqlColumnName"/> + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="reader"></param> + /// <returns>The created object, or default if no rows are available</returns> + public static T GetFirstObject<T>(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<DbColumn> 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<string, PropertyInfo> availbleProps = DictStore.Rent(); + //Itterate through public properties + foreach (PropertyInfo prop in objectType.GetProperties()) + { + //try to get the column name attribute of the propery + SqlColumnName colAtt = prop.GetCustomAttribute<SqlColumnName>(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; + } + /// <summary> + /// Reads the first available row from the reader, adapts columns to public properties with <see cref="SqlColumnName"/> + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="reader"></param> + /// <returns>The created object, or default if no rows are available</returns> + public static async Task<T> GetFirstObjectAsync<T>(this DbDataReader reader) where T : new() + { + //Read + if (await reader.ReadAsync()) + { + //Get the object type + Type objectType = typeof(T); + //Get the column schema + ReadOnlyCollection<DbColumn> columns = await reader.GetColumnSchemaAsync(); + //Rent a dict of properties that have the column attribute set so we can load the proper results + Dictionary<string, PropertyInfo> availbleProps = DictStore.Rent(); + //Itterate through public properties + foreach (PropertyInfo prop in objectType.GetProperties()) + { + //try to get the column name attribute of the propery + SqlColumnName colAtt = prop.GetCustomAttribute<SqlColumnName>(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; + } + /// <summary> + /// Executes a nonquery operation with the specified command using the object properties set with the + /// <see cref="SqlVariable"/> attributes + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="cmd"></param> + /// <param name="obj">The object containing the <see cref="SqlVariable"/> properties to write to command variables</param> + /// <returns>The number of rows affected</returns> + /// <exception cref="TypeLoadException"></exception> + /// <exception cref="ArgumentException"></exception> + /// <exception cref="NotSupportedException"></exception> + /// <exception cref="ArgumentNullException"></exception> + /// <exception cref="AmbiguousMatchException"></exception> + /// <exception cref="TargetInvocationException"></exception> + public static ERRNO ExecuteNonQuery<T>(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 + SqlVariable varprops = prop.GetCustomAttribute<SqlVariable>(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.Nullable).Direction = varprops.Direction; + } + } + //Prepare the sql statement + cmd.Prepare(); + //Exect the query and return the results + return cmd.ExecuteNonQuery(); + } + /// <summary> + /// Executes a nonquery operation with the specified command using the object properties set with the + /// <see cref="SqlVariable"/> attributes + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="cmd"></param> + /// <param name="obj">The object containing the <see cref="SqlVariable"/> properties to write to command variables</param> + /// <returns>The number of rows affected</returns> + /// <exception cref="TypeLoadException"></exception> + /// <exception cref="ArgumentException"></exception> + /// <exception cref="NotSupportedException"></exception> + /// <exception cref="ArgumentNullException"></exception> + /// <exception cref="AmbiguousMatchException"></exception> + /// <exception cref="TargetInvocationException"></exception> + public static async Task<ERRNO> ExecuteNonQueryAsync<T>(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 + SqlVariable varprops = prop.GetCustomAttribute<SqlVariable>(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.Nullable).Direction = varprops.Direction; + } + } + //Prepare the sql statement + await cmd.PrepareAsync(); + //Exect the query and return the results + return await cmd.ExecuteNonQueryAsync(); + } + } +}
\ No newline at end of file diff --git a/VNLib.Plugins.Extensions.Data/SQL/EnumerableTable.cs b/VNLib.Plugins.Extensions.Data/SQL/EnumerableTable.cs new file mode 100644 index 0000000..23cd889 --- /dev/null +++ b/VNLib.Plugins.Extensions.Data/SQL/EnumerableTable.cs @@ -0,0 +1,118 @@ +/* +* Copyright (c) 2022 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Data +* File: EnumerableTable.cs +* +* EnumerableTable.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 General Public License as published +* by the Free Software Foundation, either version 2 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 +* General Public License for more details. +* +* You should have received a copy of the GNU General Public License +* along with VNLib.Plugins.Extensions.Data. If not, see http://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; +using System.Threading; +using System.Data.Common; +using System.Threading.Tasks; +using System.Collections.Generic; + +namespace VNLib.Plugins.Extensions.Data.SQL +{ + /// <summary> + /// A base class for client side async enumerable SQL queries + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + public abstract class EnumerableTable<T> : TableManager, IAsyncEnumerable<T> + { + const string DEFAULT_ENUM_STATMENT = "SELECT *\r\nFROM @table\r\n;"; + + public EnumerableTable(Func<DbConnection> factory, string tableName) : base(factory, tableName) + { + //Build the default select all statment + Enumerate = DEFAULT_ENUM_STATMENT.Replace("@table", tableName); + } + public EnumerableTable(Func<DbConnection> factory) : base(factory) + { } + + /// <summary> + /// The command that will be run against the database to return rows for enumeration + /// </summary> + protected string Enumerate { get; set; } + + /// <summary> + /// The isolation level to use when creating the transaction during enumerations + /// </summary> + protected IsolationLevel TransactionIsolationLevel { get; set; } = IsolationLevel.ReadUncommitted; + + IAsyncEnumerator<T> IAsyncEnumerable<T>.GetAsyncEnumerator(CancellationToken cancellationToken) + { + return GetAsyncEnumerator(cancellationToken: cancellationToken); + } + + /// <summary> + /// Transforms a row from the <paramref name="reader"/> into the item type + /// to be returned when yielded. + /// </summary> + /// <param name="reader">The reader to get the item data from</param> + /// <param name="cancellationToken">A token to cancel the operation</param> + /// <returns>A task that returns the transformed item</returns> + /// <remarks>The <paramref name="reader"/> position is set before this method is invoked</remarks> + protected abstract Task<T> GetItemAsync(DbDataReader reader, CancellationToken cancellationToken); + /// <summary> + /// Invoked when an item is no longer in the enumerator scope, in the enumeration process. + /// </summary> + /// <param name="item">The item to cleanup</param> + /// <param name="cancellationToken">A token to cancel the operation</param> + /// <returns>A ValueTask that represents the cleanup process</returns> + protected abstract ValueTask CleanupItemAsync(T item, CancellationToken cancellationToken); + + /// <summary> + /// Gets an <see cref="IAsyncEnumerator{T}"/> to enumerate items within the backing store. + /// </summary> + /// <param name="closeItems">Cleanup items after each item is enumerated and the enumeration scope has + /// returned to the enumerator</param> + /// <param name="cancellationToken">A token to cancel the enumeration</param> + /// <returns>A <see cref="IAsyncEnumerator{T}"/> to enumerate records within the store</returns> + public virtual async IAsyncEnumerator<T> GetAsyncEnumerator(bool closeItems = true, CancellationToken cancellationToken = default) + { + await using DbConnection db = GetConnection(); + await db.OpenAsync(cancellationToken); + await using DbTransaction transaction = await db.BeginTransactionAsync(cancellationToken); + //Start the enumeration command + await using DbCommand cmd = db.CreateTextCommand(Enumerate, transaction); + await cmd.PrepareAsync(cancellationToken); + await using DbDataReader reader = await cmd.ExecuteReaderAsync(cancellationToken); + //loop through results and transform each element + while (reader.Read()) + { + //get the item + T item = await GetItemAsync(reader, cancellationToken); + try + { + yield return item; + } + finally + { + if (closeItems) + { + //Cleanup the item + await CleanupItemAsync(item, cancellationToken); + } + } + } + } + } +}
\ No newline at end of file diff --git a/VNLib.Plugins.Extensions.Data/SQL/SqlColumnName.cs b/VNLib.Plugins.Extensions.Data/SQL/SqlColumnName.cs new file mode 100644 index 0000000..0039fb5 --- /dev/null +++ b/VNLib.Plugins.Extensions.Data/SQL/SqlColumnName.cs @@ -0,0 +1,65 @@ +/* +* Copyright (c) 2022 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Data +* File: SqlColumnName.cs +* +* SqlColumnName.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 General Public License as published +* by the Free Software Foundation, either version 2 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 +* General Public License for more details. +* +* You should have received a copy of the GNU General Public License +* along with VNLib.Plugins.Extensions.Data. If not, see http://www.gnu.org/licenses/. +*/ + +using System; + +namespace VNLib.Plugins.Extensions.Data.SQL +{ + /// <summary> + /// Property attribute that specifies the property represents an SQL column in the database + /// </summary> + [AttributeUsage(AttributeTargets.Property)] + public class SqlColumnName : Attribute + { + public bool Nullable { get; } + public bool Unique { get; } + public bool PrimaryKey { get; } + public string ColumnName { get; init; } + /// <summary> + /// Specifies the property is an SQL column name + /// </summary> + /// <param name="columnName">Name of the SQL column</param> + /// <param name="primaryKey"></param> + /// <param name="nullable"></param> + /// <param name="unique"></param> + public SqlColumnName(string columnName, bool primaryKey = false, bool nullable = true, bool unique = false) + { + this.ColumnName = columnName; + this.PrimaryKey = primaryKey; + this.Nullable = nullable; + this.Unique = unique; + } + } + + /// <summary> + /// Allows a type to declare itself as a <see cref="System.Data.DataTable"/> with the specified name + /// </summary> + [AttributeUsage(AttributeTargets.Class, AllowMultiple =false, Inherited = true)] + public class SqlTableName : Attribute + { + public string TableName { get; } + + public SqlTableName(string tableName) => TableName = tableName; + } +}
\ No newline at end of file diff --git a/VNLib.Plugins.Extensions.Data/SQL/SqlVariable.cs b/VNLib.Plugins.Extensions.Data/SQL/SqlVariable.cs new file mode 100644 index 0000000..d33854a --- /dev/null +++ b/VNLib.Plugins.Extensions.Data/SQL/SqlVariable.cs @@ -0,0 +1,58 @@ +/* +* Copyright (c) 2022 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Data +* File: SqlVariable.cs +* +* SqlVariable.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 General Public License as published +* by the Free Software Foundation, either version 2 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 +* General Public License for more details. +* +* You should have received a copy of the GNU General Public License +* along with VNLib.Plugins.Extensions.Data. If not, see http://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; + +namespace VNLib.Plugins.Extensions.Data.SQL +{ + /// <summary> + /// Property attribute that specifies the property is to be used for a given command variable + /// </summary> + [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)] + public class SqlVariable : Attribute + { + public string VariableName { get; init; } + public DbType DataType { get; init; } + public ParameterDirection Direction { get; init; } + public int Size { get; init; } + public bool Nullable { get; init; } + /// <summary> + /// Specifies the property to be used as an SQL variable + /// </summary> + /// <param name="variableName">Sql statement variable this property will substitute</param> + /// <param name="dataType">The sql data the property will represent</param> + /// <param name="direction">Data direction during execution</param> + /// <param name="size">Column size</param> + /// <param name="isNullable">Is this property allowed to be null</param> + public SqlVariable(string variableName, DbType dataType, ParameterDirection direction, int size, bool isNullable) + { + this.VariableName = variableName; + this.DataType = dataType; + this.Direction = direction; + this.Size = size; + this.Nullable = isNullable; + } + } +} diff --git a/VNLib.Plugins.Extensions.Data/SQL/TableManager.cs b/VNLib.Plugins.Extensions.Data/SQL/TableManager.cs new file mode 100644 index 0000000..14c4e64 --- /dev/null +++ b/VNLib.Plugins.Extensions.Data/SQL/TableManager.cs @@ -0,0 +1,65 @@ +/* +* Copyright (c) 2022 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Data +* File: TableManager.cs +* +* TableManager.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 General Public License as published +* by the Free Software Foundation, either version 2 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 +* General Public License for more details. +* +* You should have received a copy of the GNU General Public License +* along with VNLib.Plugins.Extensions.Data. If not, see http://www.gnu.org/licenses/. +*/ + +using System; +using System.Data.Common; + +namespace VNLib.Plugins.Extensions.Data.SQL +{ + /// <summary> + /// A class that contains basic structures for interacting with an SQL driven database + /// </summary> + public abstract class TableManager + { + private readonly Func<DbConnection> Factory; + protected string Insert { get; set; } + protected string Select { get; set; } + protected string Update { get; set; } + protected string Delete { get; set; } + + /// <summary> + /// The name of the table specified during initialized + /// </summary> + protected string TableName { get; } + + public TableManager(Func<DbConnection> factory, string tableName) + { + this.Factory = factory ?? throw new ArgumentNullException(nameof(factory)); + this.TableName = !string.IsNullOrWhiteSpace(tableName) ? tableName : throw new ArgumentNullException(nameof(tableName)); + } + public TableManager(Func<DbConnection> factory) + { + this.Factory = factory ?? throw new ArgumentNullException(nameof(factory)); + this.TableName = ""; + } + /// <summary> + /// Opens a new <see cref="DbConnection"/> by invoking the factory callback method + /// </summary> + /// <returns>The open connection</returns> + protected DbConnection GetConnection() + { + return Factory(); + } + } +} |