/// <summary>
/// Insert entities
/// </summary>
/// <param name="entities">Entities</param>
public virtual void Insert(IEnumerable <TEntity> entities)
{
if (entities == null)
{
throw new ArgumentNullException(nameof(entities));
}
using (var _dataConnection = new NopDataConnection())
{
_dataConnection.BeginTransaction();
try
{
foreach (var entity in entities)
{
Insert(entity);
}
_dataConnection.CommitTransaction();
}
catch
{
_dataConnection.RollbackTransaction();
throw;
}
}
}
/// <summary>
/// Returns mapped entity descriptor
/// </summary>
/// <typeparam name="TEntity">Type of entity</typeparam>
/// <returns>Mapped entity descriptor</returns>
public virtual EntityDescriptor GetEntityDescriptor <TEntity>() where TEntity : BaseEntity
{
using (var currentConnection = new NopDataConnection())
{
return(currentConnection.MappingSchema.GetEntityDescriptor(typeof(TEntity)));
}
}
/// <summary>
/// Restores the database from a backup
/// </summary>
/// <param name="backupFileName">The name of the backup file</param>
public virtual void RestoreDatabase(string backupFileName)
{
CheckBackupSupported();
using (var currentConnection = new NopDataConnection())
{
var commandText = string.Format(
"DECLARE @ErrorMessage NVARCHAR(4000)\n" +
"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\n" +
"BEGIN TRY\n" +
"RESTORE DATABASE [{0}] FROM DISK = '{1}' WITH REPLACE\n" +
"END TRY\n" +
"BEGIN CATCH\n" +
"SET @ErrorMessage = ERROR_MESSAGE()\n" +
"END CATCH\n" +
"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE\n" +
"IF (@ErrorMessage is not NULL)\n" +
"BEGIN\n" +
"RAISERROR (@ErrorMessage, 16, 1)\n" +
"END",
currentConnection.Connection.Database,
backupFileName);
currentConnection.Execute(commandText);
}
}
/// <summary>
/// Truncates database table
/// </summary>
/// <param name="resetIdentity">Performs reset identity column</param>
public virtual void Truncate(bool resetIdentity = false)
{
using (var _dataConnection = new NopDataConnection())
{
_dataConnection.GetTable <TEntity>().Truncate(resetIdentity);
}
}
/// <summary>
/// Executes command using System.Data.CommandType.StoredProcedure command type
/// and returns results as collection of values of specified type
/// </summary>
/// <param name="storeProcedureName">Store procedure name</param>
/// <param name="dataParameters">Command parameters</param>
/// <returns>Collection of query result records</returns>
public virtual IList <TEntity> EntityFromSql(string storeProcedureName, params DataParameter[] dataParameters)
{
using (var _dataConnection = new NopDataConnection())
{
return(_dataConnection.ExecuteStoredProcedure <TEntity>(storeProcedureName, dataParameters?.ToArray()));
}
}
/// <summary>
/// Executes SQL command and returns results as collection of values of specified type
/// </summary>
/// <typeparam name="T">Type of result items</typeparam>
/// <param name="sql">SQL command text</param>
/// <param name="parameters">Parameters to execute the SQL command</param>
/// <returns>Collection of values of specified type</returns>
public virtual IList <T> Query <T>(string sql, params DataParameter[] parameters)
{
using (var currentConnection = new NopDataConnection())
{
return(currentConnection.Query <T>(sql, parameters)?.ToList() ?? new List <T>());
}
}
/// <summary>
/// Executes command using System.Data.CommandType.StoredProcedure command type and
/// returns results as collection of values of specified type
/// </summary>
/// <typeparam name="T">Result record type</typeparam>
/// <param name="procedureName">Procedure name</param>
/// <param name="parameters">Command parameters</param>
/// <returns>Returns collection of query result records</returns>
public virtual IList <T> QueryProc <T>(string procedureName, params DataParameter[] parameters)
{
using (var currentConnection = new NopDataConnection())
{
return(currentConnection.ExecuteStoredProcedure <T>(procedureName, parameters) ?? new List <T>());
}
}
/// <summary>
/// Loads the original copy of the entity
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="entity">Entity</param>
/// <returns>Copy of the passed entity</returns>
public virtual TEntity LoadOriginalCopy <TEntity>(TEntity entity) where TEntity : BaseEntity
{
using (var currentConnection = new NopDataConnection())
{
var entities = currentConnection.GetTable <TEntity>();
return(entities.FirstOrDefault(e => e.Id == Convert.ToInt32(entity.Id)));
}
}
/// <summary>
/// Insert a new entity
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="entity">Entity</param>
/// <returns>Entity</returns>
public virtual TEntity InsertEntity <TEntity>(TEntity entity) where TEntity : BaseEntity
{
using (var currentConnection = new NopDataConnection())
{
entity.Id = currentConnection.InsertWithInt32Identity(entity);
return(entity);
}
}
/// <summary>
/// Creates a backup of the database
/// </summary>
public virtual void BackupDatabase(string fileName)
{
CheckBackupSupported();
//var fileName = _fileProvider.Combine(GetBackupDirectoryPath(), $"database_{DateTime.Now:yyyy-MM-dd-HH-mm-ss}_{CommonHelper.GenerateRandomDigitCode(10)}.{NopCommonDefaults.DbBackupFileExtension}");
using (var currentConnection = new NopDataConnection())
{
var commandText = $"BACKUP DATABASE [{currentConnection.Connection.Database}] TO DISK = '{fileName}' WITH FORMAT";
currentConnection.Execute(commandText);
}
}
/// <summary>
/// Get the current identity value
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <returns>Integer identity; null if cannot get the result</returns>
public virtual int?GetTableIdent <T>() where T : BaseEntity
{
using (var currentConnection = new NopDataConnection())
{
var tableName = currentConnection.GetTable <T>().TableName;
var result = currentConnection.Query <decimal?>($"SELECT IDENT_CURRENT('[{tableName}]') as Value")
.FirstOrDefault();
return(result.HasValue ? Convert.ToInt32(result) : 1);
}
}
/// <summary>
/// Execute commands from the SQL script
/// </summary>
/// <param name="sql">SQL script</param>
public void ExecuteSqlScript(string sql)
{
var sqlCommands = GetCommandsFromScript(sql);
using (var currentConnection = new NopDataConnection())
{
foreach (var command in sqlCommands)
{
currentConnection.Execute(command);
}
}
}
/// <summary>
/// Update entity
/// </summary>
/// <param name="entity">Entity</param>
public virtual void Update(TEntity entity)
{
if (entity == null)
{
throw new ArgumentNullException(nameof(entity));
}
using (var _dataConnection = new NopDataConnection())
{
_dataConnection.Update(entity);
}
}
/// <summary>
/// Insert entity
/// </summary>
/// <param name="entity">Entity</param>
public virtual void Insert(TEntity entity)
{
if (entity == null)
{
throw new ArgumentNullException(nameof(entity));
}
using (var _dataConnection = new NopDataConnection())
{
entity.Id = _dataConnection.InsertWithInt32Identity(entity);
}
}
/// <summary>
/// Set table identity (is supported)
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <param name="ident">Identity value</param>
public virtual void SetTableIdent <T>(int ident) where T : BaseEntity
{
using (var currentConnection = new NopDataConnection())
{
var currentIdent = GetTableIdent <T>();
if (!currentIdent.HasValue || ident <= currentIdent.Value)
{
return;
}
var tableName = currentConnection.GetTable <T>().TableName;
currentConnection.Execute($"DBCC CHECKIDENT([{tableName}], RESEED, {ident})");
}
}
/// <summary>
/// Drops table identified by mapping class
/// </summary>
public virtual void DeleteTableIfExists()
{
var dataConnection = new NopDataConnection();
var sp = dataConnection.DataProvider.GetSchemaProvider();
var dbSchema = sp.GetSchema(dataConnection);
if (dbSchema.Tables.All(t => t.TypeName != typeof(TEntity).Name))
{
return;
}
//table exists delete it
try
{
dataConnection.DropTable <TEntity>();
}
catch (System.Data.SqlClient.SqlException)
{
}
}
/// <summary>
/// Re-index database tables
/// </summary>
public virtual void ReIndexTables()
{
using (var currentConnection = new NopDataConnection())
{
var commandText = $@"
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM [{currentConnection.Connection.Database}].information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec('ALTER INDEX ALL ON [' + @TableName + '] REBUILD')
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex";
currentConnection.Execute(commandText);
}
}
/// <summary>
/// Create database schema if it not exists
/// </summary>
/// <param name="assembly">Assembly to find the mapping configurations classes;
/// leave null to search mapping configurations classes on the whole application pull</param>
public virtual void CreateDatabaseSchemaIfNotExists(Assembly assembly = null)
{
DataConnection.DefaultSettings = Singleton <DataSettings> .Instance;
using (var currentConnection = new NopDataConnection())
{
ConfigureDataContext(currentConnection);
//find database mapping configuration by other assemblies
var typeFinder = new AppDomainTypeFinder();
var typeConfigurations = assembly != null
? typeFinder.FindClassesOfType <IMappingConfiguration>(new List <Assembly> {
assembly
}).ToList()
: typeFinder.FindClassesOfType <IMappingConfiguration>().ToList();
foreach (var typeConfiguration in typeConfigurations)
{
var mappingConfiguration = (IMappingConfiguration)Activator.CreateInstance(typeConfiguration);
mappingConfiguration.CreateTableIfNotExists(currentConnection);
}
}
}