/// <summary>
/// Get the names of the tables 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 table names for the specified owner</returns>
public static async Task <List <string> > GetTablesAsync(string owner, DBSqlServerConnection connection = null, string connectionStringName = null)
{
using (var dbSqlServerMedatadataDAL = await DBSqlServerMetadataDAL.CreateAsync(connection: connection, connectionStringName: connectionStringName).ConfigureAwait(false))
{
return(await dbSqlServerMedatadataDAL.GetTablesAsync(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 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>
/// 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 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());
}
}
/// <summary>
/// Creates and runs a command to execute a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Number of affected rows</returns>
internal static async Task <int> ExecuteNonQueryAsync(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
return(await command.Command.ExecuteNonQueryAsync().ConfigureAwait(false));
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Create a new prepared statement (used by DBSqlServerBaseDAL)
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
internal DBSqlServerPreparedStatement(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
Command = DBSqlServerCommand.CreatePreparedStatement(
connection: connection,
commandText: commandText,
isStoredProcedure: isStoredProcedure,
parameters: parameters);
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates a disposable container with a database connection, creating a new connection if necessary
/// </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>
protected static T Create <T>(DBSqlServerConnection connection, string connectionStringName) where T : DBSqlServerConnection
{
var newConnection = (T)Activator.CreateInstance(type: typeof(T), nonPublic: true);
if (connection is null)
{
newConnection.DisposeConnection = true;
newConnection.Connection = DBSqlServerConnectionInstance.Open(connectionStringName);
}
else
{
newConnection.DisposeConnection = false;
newConnection.Connection = connection.Connection;
}
return(newConnection);
}
/// <summary>
/// Creates a disposable container with a database connection, creating a new connection if necessary
/// </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>
public static async Task <DBSqlServerConnection> CreateAsync(DBSqlServerConnection connection, string connectionStringName)
{
var newConnection = new DBSqlServerConnection();
if (connection is null)
{
newConnection.DisposeConnection = true;
newConnection.Connection = await DBSqlServerConnectionInstance.OpenAsync(connectionStringName).ConfigureAwait(false);
}
else
{
newConnection.DisposeConnection = false;
newConnection.Connection = connection.Connection;
}
return(newConnection);
}
/// <summary>
/// Creates a disposable container with a database connection, creating a new connection if necessary
/// </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>
protected static async Task <T> CreateAsync <T>(DBSqlServerConnection connection, string connectionStringName) where T : DBSqlServerConnection
{
var newConnection = (T)Activator.CreateInstance(type: typeof(T), nonPublic: true);
if (connection is null)
{
newConnection.DisposeConnection = true;
newConnection.Connection = await DBSqlServerConnectionInstance.OpenAsync(connectionStringName).ConfigureAwait(false);
}
else
{
newConnection.DisposeConnection = false;
newConnection.Connection = connection.Connection;
}
return(newConnection);
}
/// <summary>
/// Creates a disposable container with a database connection, creating a new connection if necessary
/// </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>
public static DBSqlServerConnection Create(DBSqlServerConnection connection, string connectionStringName)
{
var newConnection = new DBSqlServerConnection();
if (connection is null)
{
newConnection.DisposeConnection = true;
newConnection.Connection = DBSqlServerConnectionInstance.Open(connectionStringName);
}
else
{
newConnection.DisposeConnection = false;
newConnection.Connection = connection.Connection;
}
return(newConnection);
}
/// <summary>
/// Creates a new prepared statement (used by DBSqlServerPreparedStatement)
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
internal static DBSqlServerCommand CreatePreparedStatement(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
DBSqlServerCommand command = null;
try
{
command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters);
command.Command.Prepare();
return(command);
}
catch (Exception ex)
{
command?.Close();
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates a new DBSqlServerMetadataDAL, opening a new connection if necessary
/// </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>
public new static DBSqlServerMetadataDAL Create(DBSqlServerConnection connection, string connectionStringName)
{
return(Create <DBSqlServerMetadataDAL>(connection: connection, connectionStringName: connectionStringName));
}
/// <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()));
}
private DBSqlServerCommand(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
if (connection is null)
{
throw new ArgumentNullException(DBSqlServerLocalizedText.DBSqlServerCommand_NullConnection);
}
try
{
Command = connection.Connection.Connection.CreateCommand();
Command.Transaction = connection.Connection.Transaction;
Command.CommandType = (isStoredProcedure) ? CommandType.StoredProcedure : CommandType.Text;
Command.CommandText = commandText;
}
catch (Exception ex)
{
try
{
Log.Debug(DBSqlServerLocalizedText.DBSqlServerCommand_Error, ex);
}
catch (Exception)
{
// Nothing to do
}
Close();
throw;
}
string lastParameterName = null;
try
{
foreach (DBSqlServerParameter parameter in parameters)
{
if (parameter is null)
{
try
{
Log.Error(string.Concat(DBSqlServerLocalizedText.DBSqlServerCommand_NullParameter, Environment.NewLine, Environment.StackTrace));
}
catch (Exception)
{
// Nothing to do
}
continue;
}
lastParameterName = parameter.Parameter.ParameterName;
Command.Parameters.Add(parameter.Parameter);
}
}
catch (Exception ex)
{
try
{
Log.Debug(string.Format(DBSqlServerLocalizedText.DBSqlServerCommand_BindError, lastParameterName), ex);
}
catch (Exception)
{
// Nothing to do
}
Close();
throw;
}
}
/// <summary>
/// Creates and runs a command to execute a command text that returns multiple rows
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="connection">Database connection</param>
/// <param name="dataFiller">Function to convert the returned rows into the return type object</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>List of objects of the return type</returns>
internal static async Task <List <T> > RetrieveDataListAsync <T>(DBSqlServerConnection connection, Func <DBSqlServerDataReader, T> dataFiller, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
var sqlDataReader = await command.Command.ExecuteReaderAsync().ConfigureAwait(false);
DBSqlServerDataReader dataReader = null;
List <T> rowList = null;
try
{
dataReader = new DBSqlServerDataReader(sqlDataReader);
rowList = new List <T>();
while (await sqlDataReader.ReadAsync().ConfigureAwait(false))
{
rowList.Add(dataFiller(dataReader));
}
return(rowList);
}
catch (Exception)
{
rowList?.Clear(); // This method fills the internal array with zeros to help the gc
throw;
}
finally
{
dataReader?.Cleanup();
try
{
sqlDataReader?.Close();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_CloseDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
try
{
sqlDataReader?.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_DisposeDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <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>
/// Creates and runs a command to execute a query command text that returns a single row
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="connection">Database connection</param>
/// <param name="dataFiller">Function to convert the returned row into the return type object</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="nullValue">Value to be returned if the query command text does not return any row</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Command text result, or nullValue if none</returns>
internal static async Task <T> RetrieveDataItemAsync <T>(DBSqlServerConnection connection, Func <DBSqlServerDataReader, T> dataFiller, string commandText, bool isStoredProcedure, T nullValue = default(T), params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
var sqlDataReader = await command.Command.ExecuteReaderAsync(behavior : CommandBehavior.SingleRow).ConfigureAwait(false);
DBSqlServerDataReader dataReader = null;
try
{
dataReader = new DBSqlServerDataReader(sqlDataReader);
return((await sqlDataReader.ReadAsync().ConfigureAwait(false))
? dataFiller(dataReader)
: nullValue);
}
finally
{
dataReader?.Cleanup();
try
{
sqlDataReader?.Close();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_CloseDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
try
{
sqlDataReader?.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_DisposeDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to retrieve a DataTable using a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the command text results</returns>
internal static DataTable GetDataTable(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
SqlDataAdapter dataAdapter;
try
{
dataAdapter = new SqlDataAdapter(command.Command);
}
catch (Exception ex)
{
try
{
Log.Debug(DBSqlServerLocalizedText.DBSqlServerCommand_GetDataSet_DataAdapterError, ex);
}
catch (Exception)
{
// Nothing to do
}
throw;
}
bool suppressDisposeException = false;
try
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return(dataTable);
}
catch (Exception ex)
{
try
{
Log.Debug(DBSqlServerLocalizedText.DBSqlServerCommand_GetDataSet_DataRetrieveError, ex);
}
catch (Exception)
{
// Nothing to do
}
suppressDisposeException = true;
throw;
}
finally
{
try
{
dataAdapter.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_DataAdapter_Disposal_Error, ex);
}
catch (Exception)
{
// Nothing to do
}
if (!suppressDisposeException)
{
throw;
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates a new DBSqlServerMetadataDAL, opening a new connection if necessary
/// </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>
public new static async Task <DBSqlServerMetadataDAL> CreateAsync(DBSqlServerConnection connection, string connectionStringName)
{
return(await CreateAsync <DBSqlServerMetadataDAL>(connection : connection, connectionStringName : connectionStringName).ConfigureAwait(false));
}