/// <summary>
/// Creates and runs a command to retrieve a DataTable using a stored procedure
/// </summary>
/// <param name="commandText">Stored procedure name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the query results</returns>
public static DataTable RetrieveDataTableSP(string commandText, DBSqlServerConnection connection = null, string connectionStringName = null, params DBSqlServerParameter[] parameters)
{
using (var dbSqlServerMedatadataDAL = DBSqlServerMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbSqlServerMedatadataDAL.RetrieveDataTableSP(commandText: commandText, parameters: parameters));
}
}
/// <summary>
/// Get the length of a database column
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="tableName">Table name</param>
/// <param name="columnName">Column name</param>
/// <param name="useCache">Use column length cache (default is true, use false only to detect changes in the database structure)</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The length of the database column; null if the owner, the table or the column does not exist</returns>
public static int?GetColumnLength(string owner, string tableName, string columnName, bool useCache = true, DBSqlServerConnection connection = null, string connectionStringName = null)
{
if (!useCache)
{
using (var dbSqlServerMedatadataDAL = DBSqlServerMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbSqlServerMedatadataDAL.GetColumnLength(owner: owner, tableName: tableName, columnName: columnName));
}
}
owner = owner.ToUpper();
if (!GetColumnLength_Cache.TryGetValue(owner, out var ownerCache))
{
using (var dbSqlServerMedatadataDAL = DBSqlServerMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
ownerCache = dbSqlServerMedatadataDAL.GetColumnLengthTable(owner);
}
lock (GetColumnLength_Lock)
{
if (!GetColumnLength_Cache.TryGetValue(owner, out var ownerCacheAux))
{
var newCache = new Dictionary <string, Dictionary <string, Dictionary <string, int> > >(GetColumnLength_Cache);
newCache[owner] = ownerCache;
GetColumnLength_Cache = newCache;
}
}
}
return(ownerCache.GetOrDefault(tableName.ToUpper())
?.GetOrDefault(columnName.ToUpper()));
}
/// <summary>
/// Get the current database date/time
/// </summary>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The current database date/time</returns>
public static async Task <DateTime> GetDateTimeAsync(DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = await DBSqlServerMetadataDAL.CreateAsync(connection: connection, connectionStringName: connectionStringName).ConfigureAwait(false))
{
return(await dbSqlServerMedatadataDAL.GetDateTimeAsync().ConfigureAwait(false));
}
}
/// <summary>
/// Creates and runs a command to execute a stored procedure
/// </summary>
/// <param name="commandText">Stored procedure name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Number of affected rows</returns>
public static async Task <int> ExecuteSPAsync(string commandText, DBSqlServerConnection connection = null, string connectionStringName = null, params DBSqlServerParameter[] parameters)
{
using (var dbSqlServerMedatadataDAL = await DBSqlServerMetadataDAL.CreateAsync(connection: connection, connectionStringName: connectionStringName).ConfigureAwait(false))
{
return(await dbSqlServerMedatadataDAL.ExecuteSPAsync(commandText : commandText, parameters : parameters).ConfigureAwait(false));
}
}
/// <summary>
/// Get next value from a database sequence
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="sequenceName">Sequence name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The next value from a database sequence</returns>
public static async Task <decimal> GetSequenceNextValueAsync(string owner, string sequenceName, DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = await DBSqlServerMetadataDAL.CreateAsync(connection: connection, connectionStringName: connectionStringName).ConfigureAwait(false))
{
return(await dbSqlServerMedatadataDAL.GetSequenceNextValueAsync(owner : owner, sequenceName : sequenceName).ConfigureAwait(false));
}
}
/// <summary>
/// Get next value from a database sequence
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="sequenceName">Sequence name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The next value from a database sequence</returns>
public static decimal GetSequenceNextValue(string owner, string sequenceName, DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = DBSqlServerMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbSqlServerMedatadataDAL.GetSequenceNextValue(owner: owner, sequenceName: sequenceName));
}
}
/// <summary>
/// Get the names of the indexes for a specified owner
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>List of index names for the specified owner</returns>
public static async Task <Dictionary <string, string> > GetIndexesAsync(string owner, DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = await DBSqlServerMetadataDAL.CreateAsync(connection: connection, connectionStringName: connectionStringName).ConfigureAwait(false))
{
return(await dbSqlServerMedatadataDAL.GetIndexesAsync(owner : owner).ConfigureAwait(false));
}
}
/// <summary>
/// Get the names of the indexes for a specified owner
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>List of index names for the specified owner</returns>
public static Dictionary <string, string> GetIndexes(string owner, DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = DBSqlServerMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbSqlServerMedatadataDAL.GetIndexes(owner: owner));
}
}
/// <summary>
/// Get the current database date/time
/// </summary>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The current database date/time</returns>
public static DateTime GetDateTime(DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = DBSqlServerMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbSqlServerMedatadataDAL.GetDateTime());
}
}