/// <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>
/// 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);
}
}
/// <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);
}
}
public void TestDataEntityExtensionGetTableName()
{
// Prepare
var dbSetting = new CustomDbSetting();
// Act
var schema = DataEntityExtension.GetTableName("TableName", dbSetting);
// Assert
Assert.AreEqual("TableName", schema);
}
public void TestDataEntityExtensionGetTableNameWithSchemaFromQuotedAndIsWhitespaced()
{
// Prepare
var dbSetting = new CustomDbSetting();
// Act
var schema = DataEntityExtension.GetTableName("[SchemaName].[Table Name]", dbSetting);
// Assert
Assert.AreEqual("[Table Name]", schema);
}
/// <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)});");
}
/// <summary>
///
/// </summary>
/// <typeparam name="TDbConnection"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private async Task <string> GetIdentityFieldNameAsync <TDbConnection>(TDbConnection connection,
string tableName,
IDbTransaction transaction = null,
CancellationToken cancellationToken = default)
where TDbConnection : IDbConnection
{
// Sql text
var commandText = "SELECT sql FROM [sqlite_master] WHERE name = @TableName AND type = 'table';";
var sql = await connection.ExecuteScalarAsync <string>(commandText: commandText,
param: new { TableName = DataEntityExtension.GetTableName(tableName).AsUnquoted(DbSetting) },
transaction : transaction,
cancellationToken : cancellationToken);
// Return
return(GetIdentityFieldNameInternal(sql)?
.AsUnquoted(connection.GetDbSetting())?
.Replace(doubleQuote, string.Empty));
}
public TableIdentifier(IDataEntity entity, string alias = null)
{
this.Entity = entity ?? throw new ArgumentNullException(nameof(entity));
this.Alias = alias;
this.Name = DataEntityExtension.GetTableName(entity);
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private string GetCommandText(string tableName) =>
$"pragma table_info({DataEntityExtension.GetTableName(tableName, DbSetting).AsUnquoted(DbSetting)});";
/// <summary>
///
/// </summary>
/// <typeparam name="TDbConnection"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="transaction"></param>
/// <returns></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: commandText,
param: new { TableName = DataEntityExtension.GetTableName(tableName, DbSetting).AsUnquoted(DbSetting) },
transaction: transaction);
// Return
return(GetIdentityFieldNameInternal(sql)?
.AsUnquoted(connection.GetDbSetting())?
.Replace(doubleQuote, string.Empty));
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string GetTableName(string tableName,
IDbSetting dbSetting) =>
DataEntityExtension.GetTableName(tableName, dbSetting);