Try your search with a different keyword or use * as a wildcard.
using System.Data.Common;
using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider;
using LinqToDB.DataProvider.SqlServer;
using Microsoft.Data.SqlClient;
using Nop.Core;
using Nop.Data.Mapping;
namespace Nop.Data.DataProviders;
///
/// Represents the MS SQL Server data provider
///
public partial class MsSqlNopDataProvider : BaseDataProvider, INopDataProvider
{
#region Utilities
///
/// Gets the connection string builder
///
/// The connection string builder
protected static SqlConnectionStringBuilder GetConnectionStringBuilder()
{
var connectionString = DataSettingsManager.LoadSettings().ConnectionString;
return new SqlConnectionStringBuilder(connectionString);
}
///
/// Gets a connection to the database for a current data provider
///
/// Connection string
/// Connection to a database
protected override DbConnection GetInternalDbConnection(string connectionString)
{
ArgumentException.ThrowIfNullOrEmpty(connectionString);
return new SqlConnection(connectionString);
}
#endregion
#region Methods
///
/// Create the database
///
/// Collation
/// Count of tries to connect to the database after creating; set 0 if no need to connect after creating
public void CreateDatabase(string collation, int triesToConnect = 10)
{
if (DatabaseExists())
return;
var builder = GetConnectionStringBuilder();
//gets database name
var databaseName = builder.InitialCatalog;
//now create connection string to 'master' dabatase. It always exists.
builder.InitialCatalog = "master";
using (var connection = GetInternalDbConnection(builder.ConnectionString))
{
var query = $"CREATE DATABASE [{databaseName}]";
if (!string.IsNullOrWhiteSpace(collation))
query = $"{query} COLLATE {collation}";
var command = connection.CreateCommand();
command.CommandText = query;
command.Connection.Open();
command.ExecuteNonQuery();
}
//try connect
if (triesToConnect <= 0)
return;
//sometimes on slow servers (hosting) there could be situations when database requires some time to be created.
//but we have already started creation of tables and sample data.
//as a result there is an exception thrown and the installation process cannot continue.
//that's why we are in a cycle of "triesToConnect" times trying to connect to a database with a delay of one second.
for (var i = 0; i <= triesToConnect; i++)
{
if (i == triesToConnect)
throw new Exception("Unable to connect to the new database. Please try one more time");
if (!DatabaseExists())
Thread.Sleep(1000);
else
break;
}
}
///
/// Checks if the specified database exists, returns true if database exists
///
///
/// A task that represents the asynchronous operation
/// The task result contains the returns true if the database exists.
///
public async Task DatabaseExistsAsync()
{
try
{
await using var connection = GetInternalDbConnection(GetCurrentConnectionString());
//just try to connect
await connection.OpenAsync();
return true;
}
catch
{
return false;
}
}
///
/// Checks if the specified database exists, returns true if database exists
///
/// Returns true if the database exists.
public bool DatabaseExists()
{
try
{
using var connection = GetInternalDbConnection(GetCurrentConnectionString());
//just try to connect
connection.Open();
return true;
}
catch
{
return false;
}
}
///
/// Returns queryable source for specified mapping class for current connection,
/// mapped to database table or view.
///
/// Entity type
/// Queryable source
public override IQueryable GetTable()
{
var table = (ITable)base.GetTable();
return DataSettingsManager.UseNoLock() ? table.With("NOLOCK") : table;
}
///
/// Get the current identity value
///
/// Entity type
///
/// A task that represents the asynchronous operation
/// The task result contains the integer identity; null if cannot get the result
///
public virtual Task GetTableIdentAsync() where TEntity : BaseEntity
{
using var currentConnection = CreateDataConnection();
var tableName = NopMappingSchema.GetEntityDescriptor(typeof(TEntity)).EntityName;
var result = currentConnection.Query($"SELECT IDENT_CURRENT('[{tableName}]') as Value")
.FirstOrDefault();
return Task.FromResult(result.HasValue ? Convert.ToInt32(result) : 1);
}
///
/// Set table identity (is supported)
///
/// Entity type
/// Identity value
/// A task that represents the asynchronous operation
public virtual async Task SetTableIdentAsync(int ident) where TEntity : BaseEntity
{
using var currentConnection = CreateDataConnection();
var currentIdent = await GetTableIdentAsync();
if (!currentIdent.HasValue || ident <= currentIdent.Value)
return;
var tableName = NopMappingSchema.GetEntityDescriptor(typeof(TEntity)).EntityName;
await currentConnection.ExecuteAsync($"DBCC CHECKIDENT([{tableName}], RESEED, {ident})");
}
///
/// Creates a backup of the database
///
/// A task that represents the asynchronous operation
public virtual async Task BackupDatabaseAsync(string fileName)
{
using var currentConnection = CreateDataConnection();
var commandText = $"BACKUP DATABASE [{currentConnection.Connection.Database}] TO DISK = '{fileName}' WITH FORMAT";
await currentConnection.ExecuteAsync(commandText);
}
///
/// Restores the database from a backup
///
/// The name of the backup file
/// A task that represents the asynchronous operation
public virtual async Task RestoreDatabaseAsync(string backupFileName)
{
using var currentConnection = CreateDataConnection();
var commandText = string.Format(
"DECLARE @ErrorMessage NVARCHAR(4000)\n" +
"ALTER DATABASE [{0}] SET OFFLINE 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);
await currentConnection.ExecuteAsync(commandText);
}
///
/// Re-index database tables
///
/// A task that represents the asynchronous operation
public virtual async Task ReIndexTablesAsync()
{
using var currentConnection = CreateDataConnection();
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";
await currentConnection.ExecuteAsync(commandText);
}
///
/// Build the connection string
///
/// Connection string info
/// Connection string
public virtual string BuildConnectionString(INopConnectionStringInfo nopConnectionString)
{
ArgumentNullException.ThrowIfNull(nopConnectionString);
var builder = new SqlConnectionStringBuilder
{
DataSource = nopConnectionString.ServerName,
InitialCatalog = nopConnectionString.DatabaseName,
PersistSecurityInfo = false,
IntegratedSecurity = nopConnectionString.IntegratedSecurity,
TrustServerCertificate = true
};
if (!nopConnectionString.IntegratedSecurity)
{
builder.UserID = nopConnectionString.Username;
builder.Password = nopConnectionString.Password;
}
return builder.ConnectionString;
}
///
/// Gets the name of a foreign key
///
/// Foreign key table
/// Foreign key column name
/// Primary table
/// Primary key column name
/// Name of a foreign key
public virtual string CreateForeignKeyName(string foreignTable, string foreignColumn, string primaryTable, string primaryColumn)
{
return $"FK_{foreignTable}_{foreignColumn}_{primaryTable}_{primaryColumn}";
}
///
/// Gets the name of an index
///
/// Target table name
/// Target column name
/// Name of an index
public virtual string GetIndexName(string targetTable, string targetColumn)
{
return $"IX_{targetTable}_{targetColumn}";
}
///
/// Updates records in table, using values from entity parameter.
/// Records to update are identified by match on primary key value from obj value.
///
/// Entities with data to update
/// Entity type
/// A task that represents the asynchronous operation
public override async Task UpdateEntitiesAsync(IEnumerable entities)
{
using var dataContext = CreateDataConnection();
await dataContext.GetTable()
.Merge()
.Using(entities)
.OnTargetKey()
.UpdateWhenMatched()
.MergeAsync();
}
///
/// Updates records in table, using values from entity parameter.
/// Records to update are identified by match on primary key value from obj value.
///
/// Entities with data to update
/// Entity type
public override void UpdateEntities(IEnumerable entities)
{
using var dataContext = CreateDataConnection();
dataContext.GetTable()
.Merge()
.Using(entities)
.OnTargetKey()
.UpdateWhenMatched()
.Merge();
}
#endregion
#region Properties
///
/// Sql server data provider
///
protected override IDataProvider LinqToDbDataProvider => SqlServerTools.GetDataProvider(SqlServerVersion.v2012, SqlServerProvider.MicrosoftDataSqlClient);
///
/// Gets allowed a limit input value of the data for hashing functions, returns 0 if not limited
///
public int SupportedLengthOfBinaryHash { get; } = 8000;
///
/// Gets a value indicating whether this data provider supports backup
///
public virtual bool BackupSupported => true;
#endregion
}