/// <summary>
/// Gets the list of <see cref="DbField"/> of the table in an asychronous way.
/// </summary>
/// <param name="connection">The instance of the connection object.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
/// <returns>A list of <see cref="DbField"/> of the target table.</returns>
public async Task <IEnumerable <DbField> > GetFieldsAsync(IDbConnection connection,
string tableName,
IDbTransaction transaction = null)
{
// Variables
var commandText = GetCommandText();
var param = new
{
TableSchema = connection.Database,
TableName = DataEntityExtension.GetTableName(tableName, m_dbSetting)
};
// Iterate and extract
using (var reader = await connection.ExecuteReaderAsync(commandText, param, transaction: transaction))
{
var dbFields = new List <DbField>();
// Iterate the list of the fields
while (reader.Read())
{
dbFields.Add(ReaderToDbField(reader));
}
// Return the list of fields
return(dbFields);
}
}
/// <summary>
/// Gets the list of <see cref="DbField"/> of the table.
/// </summary>
/// <param name="connection">The instance of the connection object.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
/// <returns>A list of <see cref="DbField"/> of the target table.</returns>
public IEnumerable <DbField> GetFields(IDbConnection connection,
string tableName,
IDbTransaction transaction = null)
{
// Variables
var commandText = GetCommandText();
var setting = connection.GetDbSetting();
var param = new
{
Schema = DataEntityExtension.GetSchema(tableName, setting),
TableName = DataEntityExtension.GetTableName(tableName, setting)
};
// Iterate and extract
using (var reader = (DbDataReader)connection.ExecuteReader(commandText, param, transaction: transaction))
{
var dbFields = new List <DbField>();
// Iterate the list of the fields
while (reader.Read())
{
dbFields.Add(ReaderToDbField(reader));
}
// Return the list of fields
return(dbFields);
}
}
/// <summary>
/// Creates a SQL Statement for repository <i>Insert</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for <i>Insert</i> operation.</returns>
internal string CreateInsert <TEntity>(QueryBuilder <TEntity> queryBuilder, bool isPrimaryIdentity)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var fields = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Insert)
.Where(property => !(isPrimaryIdentity && property == primary))
.Select(p => new Field(p.Name));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Insert()
.Into()
.TableFrom(Command.Insert)
.OpenParen()
.FieldsFrom(fields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields)
.CloseParen()
.End();
var result = isPrimaryIdentity ? "SCOPE_IDENTITY()" : (primary != null) ? $"@{primary.GetMappedName()}" : "NULL";
queryBuilder
.Select()
.WriteText(result)
.As("[Result]")
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Update</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <returns>A string containing the composed SQL Statement for <i>Update</i> operation.</returns>
public string CreateUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where)
where TEntity : DataEntity
{
var properties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Update);
if (properties?.Any() == false)
{
throw new InvalidOperationException($"No updateable fields found from type '{typeof(TEntity).FullName}'.");
}
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var identity = DataEntityExtension.GetIdentityProperty <TEntity>();
if (identity != null && identity != primary)
{
throw new InvalidOperationException($"Identity property must be the primary property for type '{typeof(TEntity).FullName}'.");
}
var fields = properties
.Where(property => property != primary && property != identity)
.Select(p => new Field(p.GetMappedName()));
where?.AppendParametersPrefix();
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Update()
.TableFrom(Command.Update)
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Gets the list of <see cref="DbField"/> of the table in an asynchronous way.
/// </summary>
/// <param name="connection">The instance of the connection object.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>A list of <see cref="DbField"/> of the target table.</returns>
public async Task <IEnumerable <DbField> > GetFieldsAsync(IDbConnection connection,
string tableName,
IDbTransaction transaction = null,
CancellationToken cancellationToken = default)
{
// Variables
var commandText = GetCommandText();
var setting = connection.GetDbSetting();
var param = new
{
Schema = DataEntityExtension.GetSchema(tableName, setting),
TableName = DataEntityExtension.GetTableName(tableName, setting)
};
// Iterate and extract
using (var reader = (DbDataReader)await connection.ExecuteReaderAsync(commandText, param,
transaction: transaction, cancellationToken: cancellationToken))
{
var dbFields = new List <DbField>();
// Iterate the list of the fields
while (await reader.ReadAsync(cancellationToken))
{
dbFields.Add(await ReaderToDbFieldAsync(reader, cancellationToken));
}
// Return the list of fields
return(dbFields);
}
}
/// <summary>
/// Gets the list of <see cref="DbField"/> of the table in an asychronous way.
/// </summary>
/// <param name="connection">The instance of the connection object.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>A list of <see cref="DbField"/> of the target table.</returns>
public async Task <IEnumerable <DbField> > GetFieldsAsync(IDbConnection connection,
string tableName,
IDbTransaction transaction = null,
CancellationToken cancellationToken = default)
{
cancellationToken.ThrowIfCancellationRequested();
// Variables
var commandText = GetCommandText();
var param = new
{
TableSchema = connection.Database,
TableName = DataEntityExtension.GetTableName(tableName, m_dbSetting)
};
// Iterate and extract
using (var reader = (DbDataReader)await connection.ExecuteReaderAsync(commandText, param, transaction: transaction,
cancellationToken: cancellationToken))
{
var dbFields = new List <DbField>();
// Iterate the list of the fields
while (await reader.ReadAsync(cancellationToken))
{
// The 'ReaderToDbFieldAsync' is having a bad behavior on different versions
// of MySQL for this driver (from Oracle). Also, the 'CAST' and 'CONVERT' is
// not working on our DEVENV.
// dbFields.Add(await ReaderToDbFieldAsync(reader, cancellationToken));
dbFields.Add(ReaderToDbField(reader));
}
// Return the list of fields
return(dbFields);
}
}
private static Func <IEnumerable <ClassProperty> > GetFunc()
{
var body = Expression.Constant(DataEntityExtension.GetProperties <TEntity>());
return(Expression
.Lambda <Func <IEnumerable <ClassProperty> > >(body)
.Compile());
}
public void TestMapAttributeName()
{
// Act
var actual = DataEntityExtension.GetMappedName <TestMapAttributeNameClass>();
var expected = "Name";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestDataEntityExtensionGetSchemaFromQuotedAndIsWhitespaced()
{
// Prepare
var dbSetting = new CustomDbSetting();
// Act
var schema = DataEntityExtension.GetSchema("[Schema Name].[TableName]", dbSetting);
// Assert
Assert.AreEqual("[Schema Name]", schema);
}
public void TestDataEntityExtensionGetTableName()
{
// Prepare
var dbSetting = new CustomDbSetting();
// Act
var schema = DataEntityExtension.GetTableName("TableName", dbSetting);
// Assert
Assert.AreEqual("TableName", schema);
}
/// <summary>
/// Gets the cached mapped-name for the entity.
/// </summary>
/// <param name="type">The type of the target entity.</param>
/// <returns>The cached mapped name of the entity.</returns>
internal static string Get(Type type)
{
var key = type.FullName;
var result = (string)null;
if (m_cache.TryGetValue(key, out result) == false)
{
result = DataEntityExtension.GetMappedName(type);
m_cache.TryAdd(key, result);
}
return(result);
}
/// <summary>
/// Gets the cached mapped-name for the entity.
/// </summary>
/// <param name="type">The type of the target entity.</param>
/// <param name="command">The target command.</param>
/// <returns>The cached command type of the entity.</returns>
internal static string Get(Type type, Command command = Command.None)
{
var key = $"{type.FullName}.{command.ToString()}";
var result = (string)null;
if (m_cache.TryGetValue(key, out result) == false)
{
result = DataEntityExtension.GetMappedName(type, command);
m_cache.TryAdd(key, result);
}
return(result);
}
/// <summary>
/// Gets a function used to extract the properties of a class.
/// </summary>
/// <typeparam name="T">The target type.</typeparam>
/// <returns>The properties of the class.</returns>
private static Func <IEnumerable <ClassProperty> > GetCompiledFunctionForClassPropertiesExtractor <T>()
where T : class
{
// Variables type (Command.None is preloaded everywhere)
var properties = DataEntityExtension.GetProperties <T>();
// Set the body
var body = Expression.Constant(properties);
// Set the function value
return(Expression
.Lambda <Func <IEnumerable <ClassProperty> > >(body)
.Compile());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Merge</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="qualifiers">The list of qualifier fields to be used for the <i>Merge</i> operation in SQL Statement composition.</param>
/// <returns>A string containing the composed SQL Statement for <i>Merge</i> operation.</returns>
public string CreateMerge <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> qualifiers)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var identity = DataEntityExtension.GetIdentityProperty <TEntity>();
if (identity != null && identity != primary)
{
throw new InvalidOperationException($"Identity property must be the primary property for type '{typeof(TEntity).FullName}'.");
}
var isPrimaryIdentity = (identity != null) && identity == primary;
return(CreateMerge(queryBuilder, qualifiers, isPrimaryIdentity));
}
/// <summary>
/// Creates a SQL Statement for repository <i>InlineInsert</i> operation.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="fields">The list of the fields to be a part of the inline insert operation in SQL Statement composition.</param>
/// <param name="overrideIgnore">
/// Set to <i>true</i> if the defined <i>RepoDb.Attributes.IgnoreAttribute</i> would likely
/// be ignored on the inline insert operation in SQL Statement composition.
/// </param>
/// <returns>A string containing the composed SQL Statement for <i>InlineInsert</i> operation.</returns>
public string CreateInlineInsert <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields, bool?overrideIgnore = false)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var identity = DataEntityExtension.GetIdentityProperty <TEntity>();
if (identity != null && identity != primary)
{
throw new InvalidOperationException($"Identity property must be the primary property for type '{typeof(TEntity).FullName}'.");
}
var isPrimaryIdentity = (identity != null) && identity == primary;
return(CreateInlineInsert <TEntity>(queryBuilder, fields, overrideIgnore, isPrimaryIdentity));
}
/// <summary>
/// Gets the cached command type for the entity.
/// </summary>
/// <typeparam name="TEntity">The type of the target entity.</typeparam>
/// <param name="command">The target command.</param>
/// <returns>The cached command type of the entity.</returns>
public static CommandType Get <TEntity>(Command command = Command.None)
where TEntity : class
{
var type = typeof(TEntity);
var key = $"{type.FullName}.{command.ToString()}";
var result = CommandType.Text;
if (m_cache.TryGetValue(key, out result) == false)
{
result = DataEntityExtension.GetCommandType <TEntity>(command);
m_cache.TryAdd(key, result);
}
return(result);
}
/// <summary>
/// Gets a delegate that is used to convert the <i>System.Data.Common.DbDataReader</i> object into <i>RepoDb.DataEntity</i> object.
/// </summary>
/// <typeparam name="TEntity">The <i>RepoDb.DataEntity</i> object to convert to.</typeparam>
/// <param name="reader">The <i>System.Data.Common.DbDataReader</i> to be converted.</param>
/// <returns>An instance of <i>RepoDb.DataEntity</i> object.</returns>
public static DataReaderToDataEntityDelegate <TEntity> GetDataReaderToDataEntityDelegate <TEntity>(DbDataReader reader) where TEntity : DataEntity
{
var entityType = typeof(TEntity);
var dynamicMethod = new DynamicMethod(StringConstant.DynamicMethod,
entityType,
new[] { typeof(DbDataReader) },
typeof(Assembly).Module,
true);
var ilGenerator = dynamicMethod.GetILGenerator();
// Declare IL Variables
ilGenerator.DeclareLocal(entityType);
ilGenerator.DeclareLocal(typeof(object));
// Create instance of the object type
ilGenerator.Emit(OpCodes.Newobj, entityType.GetConstructor(Type.EmptyTypes));
ilGenerator.Emit(OpCodes.Stloc, 0);
// Matching the fields
var fields = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
var matchedCount = 0;
// Iterate the properties
DataEntityExtension.GetPropertiesFor <TEntity>(Command.Query)
.Where(property => property.CanWrite)
.ToList()
.ForEach(property =>
{
var ordinal = fields.IndexOf(property.GetMappedName());
if (ordinal >= 0)
{
EmitDataReaderToDataEntityMapping <TEntity>(ilGenerator, ordinal, property);
matchedCount++;
}
});
// Throw an error if there are no matching atleast one
if (matchedCount == 0)
{
throw new NoMatchedFieldsException($"There is no matching fields between the result set of the data reader and the type '{typeof(TEntity).FullName}'.");
}
// Return the TEntity instance value
ilGenerator.Emit(OpCodes.Ldloc, 0);
ilGenerator.Emit(OpCodes.Ret);
// Create a delegate
return((DataReaderToDataEntityDelegate <TEntity>)dynamicMethod.CreateDelegate(typeof(DataReaderToDataEntityDelegate <TEntity>)));
}
/// <summary>
/// Gets the cached mapped-name for the entity.
/// </summary>
/// <param name="type">The type of the target entity.</param>
/// <returns>The cached mapped name of the entity.</returns>
internal static string Get(Type type)
{
var key = type.FullName.GetHashCode();
var result = (string)null;
// Try get the value
if (m_cache.TryGetValue(key, out result) == false)
{
result = DataEntityExtension.GetMappedName(type);
m_cache.TryAdd(key, result);
}
// Return the value
return(result);
}
/// <summary>
/// Checks whether the column is identity.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">The connection string object to be used.</param>
/// <param name="command">The target command.</param>
/// <param name="columnName">The name of the column.</param>
/// <returns>A boolean value indicating the identification of the column.</returns>
public static bool IsIdentity <T>(string connectionString, Command command, string columnName)
where T : DataEntity
{
var isIdentity = false;
// Open a connection
using (var connection = new SqlConnection(connectionString).EnsureOpen())
{
var commandType = DataEntityExtension.GetCommandType <T>(command);
// Check for the command type
if (commandType != CommandType.StoredProcedure)
{
var mappedName = DataEntityExtension.GetMappedName <T>(command);
var commandText = @"
SELECT CONVERT(INT, c.is_identity) AS IsIdentity
FROM [sys].[columns] c
INNER JOIN [sys].[objects] o ON o.object_id = c.object_id
WHERE (c.[name] = @ColumnName)
AND (o.[name] = @TableName)
AND (o.[type] = 'U');" ;
// Open a command
using (var dbCommand = connection.EnsureOpen().CreateCommand(commandText))
{
// Create parameters
dbCommand.CreateParameters(new
{
ColumnName = columnName.AsUnquoted(),
TableName = mappedName.AsUnquoted()
});
// Execute and set the result
var result = dbCommand.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
isIdentity = Convert.ToBoolean(result);
}
}
}
}
// Return the value
return(isIdentity);
}
/// <summary>
/// Creates a SQL Statement for repository <i>InlineUpdate</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="fields">The list of the fields to be a part of inline update operation in SQL Statement composition.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="overrideIgnore">
/// Set to <i>true</i> if the defined <i>RepoDb.Attributes.IgnoreAttribute</i> would likely
/// be ignored on the inline update operation in SQL Statement composition.
/// </param>
/// <returns>A string containing the composed SQL Statement for <i>InlineUpdate</i> operation.</returns>
public string CreateInlineUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields,
QueryGroup where, bool?overrideIgnore = false)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var hasFields = fields?.Any(field => field.Name.ToLower() != primary?.GetMappedName().ToLower());
// Check if there are fields
if (hasFields == false)
{
throw new InvalidOperationException($"No inline updatable fields found at type '{typeof(TEntity).FullName}'.");
}
// Get the target properties
var updateableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Update);
var inlineUpdateableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.InlineUpdate)
.Where(property => property != primary && updateableProperties.Contains(property))
.Select(property => property.GetMappedName());
// Check for the unmatches
if (overrideIgnore == false)
{
var unmatchesProperties = fields?.Where(field =>
inlineUpdateableProperties?.FirstOrDefault(property => field.Name.ToLower() == property.ToLower()) == null);
if (unmatchesProperties.Count() > 0)
{
throw new InvalidOperationException($"The fields '{unmatchesProperties.Select(field => field.AsField()).Join(", ")}' are not " +
$"inline updateable for object '{DataEntityExtension.GetMappedName<TEntity>(Command.InlineUpdate)}'.");
}
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Update()
.TableFrom(Command.InlineUpdate)
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>BatchQuery</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="page">The page of the batch.</param>
/// <param name="rowsPerBatch">The number of rows per batch.</param>
/// <param name="orderBy">The list of fields used for ordering.</param>
/// <returns>A string containing the composed SQL Statement for <i>BatchQuery</i> operation.</returns>
public string CreateBatchQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where, int page, int rowsPerBatch, IEnumerable <OrderField> orderBy)
where TEntity : DataEntity
{
var queryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Query);
var batchQueryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.BatchQuery)
.Where(property => queryProperties.Contains(property));
var fields = batchQueryProperties.Select(property => new Field(property.GetMappedName()));
// Validate the fields
if (fields?.Any() == false)
{
throw new InvalidOperationException($"No batch queryable fields found from type '{typeof(TEntity).FullName}'.");
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.With()
.WriteText("CTE")
.As()
.OpenParen()
.Select()
.RowNumber()
.Over()
.OpenParen()
.OrderByFrom(orderBy)
.CloseParen()
.As("[RowNumber],")
.FieldsFrom(fields)
.From()
.TableFrom(Command.BatchQuery)
.WhereFrom(where)
.CloseParen()
.Select()
.FieldsFrom(fields)
.From()
.WriteText("CTE")
.WriteText($"WHERE ([RowNumber] BETWEEN {(page * rowsPerBatch) + 1} AND {(page + 1) * rowsPerBatch})")
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Update</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <returns>A string containing the composed SQL Statement for <i>Update</i> operation.</returns>
public string CreateUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where)
where TEntity : DataEntity
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
var fields = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Update)
.Where(property => property != DataEntityExtension.GetPrimaryProperty <TEntity>())
.Select(p => new Field(p.Name));
queryBuilder
.Clear()
.Update()
.TableFrom(Command.Update)
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Gets the cached mapped-name for the entity.
/// </summary>
/// <param name="entityType">The type of the data entity.</param>
/// <returns>The cached mapped name of the entity.</returns>
public static string Get(Type entityType)
{
// Validate
ThrowNullReferenceException(entityType, "EntityType");
// Variables
var key = GenerateHashCode(entityType);
var result = (string)null;
// Try get the value
if (m_cache.TryGetValue(key, out result) == false)
{
result = DataEntityExtension.GetMappedName(entityType);
m_cache.TryAdd(key, result);
}
// Return the value
return(result);
}
/// <summary>
/// Creates a new instance of <i>RepoDb.DataEntityListDataReader</i> object.
/// </summary>
/// <param name="entities">The list of the <i>DataEntity</i> object to be used for manipulation.</param>
/// <param name="command">The type of command to be used by this data reader.</param>
public DataEntityListDataReader(IEnumerable <TEntity> entities, Command command)
{
if (entities == null)
{
throw new NullReferenceException("The entities could not be null.");
}
// Fields
_isClosed = false;
_isDisposed = false;
_position = -1;
_recordsAffected = -1;
// Properties
Properties = DataEntityExtension.GetPropertiesFor <TEntity>(command).ToList();
Enumerator = entities.GetEnumerator();
Entities = entities;
FieldCount = Properties.Count;
}
/// <summary>
/// Creates a SQL Statement for repository <i>BatchQuery</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="page">The page of the batch.</param>
/// <param name="rowsPerBatch">The number of rows per batch.</param>
/// <param name="orderBy">The list of fields used for ordering.</param>
/// <returns>A string containing the composed SQL Statement for <i>BatchQuery</i> operation.</returns>
public string CreateBatchQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where, int page, int rowsPerBatch, IEnumerable <OrderField> orderBy)
where TEntity : DataEntity
{
var queryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Query);
var batchQueryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.BatchQuery)
.Where(property => queryProperties.Contains(property));
var fields = batchQueryProperties.Select(property => new Field(property.Name));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.With()
.WriteText("CTE")
.As()
.OpenParen()
.Select()
.RowNumber()
.Over()
.OpenParen()
.OrderByFrom(orderBy)
.CloseParen()
.As("[RowNumber],")
.FieldsFrom(Command.BatchQuery)
.From()
.TableFrom(Command.BatchQuery)
.WhereFrom(where)
.CloseParen()
.Select()
.FieldsFrom(fields)
.From()
.WriteText("CTE")
.WriteText($"WHERE ([RowNumber] BETWEEN {(page * rowsPerBatch) + 1} AND {(page + 1) * rowsPerBatch})")
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Gets the cached mapped-name for the entity.
/// </summary>
/// <param name="type">The type of the target entity.</param>
/// <param name="quoted">True whether the string is quoted.</param>
/// <param name="dbSetting">The database setting that is currently in used.</param>
/// <returns>The cached mapped name of the entity.</returns>
internal static string Get(Type type,
bool quoted,
IDbSetting dbSetting)
{
var key = type.FullName.GetHashCode() + quoted.GetHashCode();
var result = (string)null;
// Add the DbSetting hashcode
if (dbSetting != null)
{
key += dbSetting.GetHashCode();
}
// Try get the value
if (m_cache.TryGetValue(key, out result) == false)
{
result = DataEntityExtension.GetMappedName(type, quoted, dbSetting);
m_cache.TryAdd(key, result);
}
// Return the value
return(result);
}
/// <summary>
/// Gets the <i>RepoDb.Attributes.MapAttribute.Name</i> value implemented on the data entity on a target command.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connectionString">The connection string object to be used.</param>
/// <param name="command">The target command.</param>
/// <returns>A boolean value indicating the identification of the column.</returns>
public static bool Get <TEntity>(string connectionString, Command command)
where TEntity : DataEntity
{
var key = $"{typeof(TEntity).FullName}.{command.ToString()}".ToLower();
var value = false;
lock (_syncLock)
{
if (_cache.ContainsKey(key))
{
value = _cache[key];
}
else
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
if (primary != null)
{
value = SqlDbHelper.IsIdentity <TEntity>(connectionString, command, primary.Name);
}
_cache.Add(key, value);
}
}
return(value);
}
/// <summary>
/// Creates a SQL Statement for repository <i>Query</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="orderBy">The list of fields to be used for ordering in SQL Statement composition.</param>
/// <param name="top">The number of rows to be returned by the <i>Query</i> operation in SQL Statement composition.</param>
/// <returns>A string containing the composed SQL Statement for <i>Query</i> operation.</returns>
public string CreateQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where, IEnumerable <OrderField> orderBy = null, int?top = 0)
where TEntity : DataEntity
{
var properties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Query);
if (properties?.Any() == false)
{
throw new InvalidOperationException($"No queryable fields found from type '{typeof(TEntity).FullName}'.");
}
var fields = properties?.Select(property => new Field(property.GetMappedName().AsQuoted(true)));
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Select()
.TopFrom(top)
.FieldsFrom(fields)
.From()
.TableFrom(Command.Query)
.WhereFrom(where)
.OrderByFrom(orderBy)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Gets the list of <see cref="DbField"/> of the table.
/// </summary>
/// <typeparam name="TDbConnection">The type of <see cref="DbConnection"/> object.</typeparam>
/// <param name="connection">The instance of the connection object.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
/// <returns>A list of <see cref="DbField"/> of the target table.</returns>
private string GetIdentityFieldName <TDbConnection>(TDbConnection connection,
string tableName,
IDbTransaction transaction = null)
where TDbConnection : IDbConnection
{
// Sql text
var commandText = "SELECT sql FROM [sqlite_master] WHERE name = @TableName AND type = 'table';";
var sql = connection.ExecuteScalar <string>(commandText, new { TableName = DataEntityExtension.GetTableName(tableName) });
var fields = ParseTableFieldsFromSql(sql);
// Iterate the fields
if (fields != null && fields.Length > 0)
{
foreach (var field in fields)
{
if (field.ToUpper().Contains("AUTOINCREMENT"))
{
return(field.Substring(0, field.IndexOf(" ")));
}
}
}
// Return null
return(null);
}
/// <summary>
/// Returns the command text that is being used to extract schema definitions.
/// </summary>
/// <param name="tableName">The name of the target table.</param>
/// <returns>The command text.</returns>
private string GetCommandText(string tableName)
{
return($"pragma table_info({DataEntityExtension.GetTableName(tableName)});");
}
