///GENMHASH:E27FF56BE6087C2995B3750D57DF922E:A6DB664201785B3A134ED16B070CE73F
public SqlSyncGroupImpl WithExistingSqlDatabase(ISqlDatabase sqlDatabase)
{
this.resourceGroupName = sqlDatabase.ResourceGroupName;
this.sqlServerName = sqlDatabase.SqlServerName;
this.sqlDatabaseName = sqlDatabase.Name;
return(this);
}
private static void CreateDatabaseFromRestorePoint(IAzure azure)
{
ISqlServer sqlServer = azure.SqlServers.GetById(sqlSubscriptionId);
ISqlDatabase database = sqlServer.Databases.Get(sourceDatabase);
// Let the user know something is happening
Console.WriteLine("Starting creation of new database from restore point of existing item");
Console.WriteLine("This can take a long time...");
// There only ever seems to be one come back...?
IRestorePoint restorePoint = database.ListRestorePoints()[0];
// Select the Elastic Pool to deploy too
ISqlElasticPool elasticPool = sqlServer.ElasticPools.Get(sqlElasticPoolName);
// Restore the database from 5 minutes ago to a random name prefixed with Example_
string dbName = SdkContext.RandomResourceName("Example_", 20);
ISqlDatabase newDatabase = sqlServer.Databases
.Define(dbName)
.WithExistingElasticPool(elasticPool)
.FromRestorePoint(restorePoint, DateTime.UtcNow.AddMinutes(-5))
.Create();
// The process is finished...
Console.WriteLine($"Database {newDatabase.Name} deployed to pool {elasticPool.Name}");
}
public static Task AddData <TDbContext>(this ISqlDatabase <TDbContext> database, IEnumerable <object> entities)
where TDbContext : DbContext
{
Guard.AgainstNull(nameof(entities), entities);
database.Context.AddRange(entities);
return(database.Context.SaveChangesAsync());
}
public QueueTransaction(string dbPath, ISqlDatabase mySqlDb) : base(dbPath)
{
//TODO: Check if path exists
DbPath = dbPath;
FilePath = dbPath;
MySqlData = mySqlDb;
}
///GENMHASH:1EBE935317F60FA68F721CC479412692:6FB795033CA9840988DF51037F5F9950
public ISqlDatabase AddExistingDatabase(ISqlDatabase database)
{
return(database
.Update()
.WithExistingElasticPool(this)
.Apply());
}
public static async Task AddDataUntracked <TDbContext>(this ISqlDatabase <TDbContext> database, IEnumerable <object> entities)
where TDbContext : DbContext
{
Guard.AgainstNull(nameof(entities), entities);
await using var context = database.NewDbContext();
context.AddRange(entities);
await context.SaveChangesAsync();
}
///GENMHASH:642F972C91F9E70B14E53881C1FCA8F9:F069850B2BE457D02CB713E8708DE59C
public SqlDatabaseImpl WithSourceDatabase(ISqlDatabase sourceDatabase)
{
if (sourceDatabase == null)
{
throw new ArgumentNullException("sourcedatabase");
}
return(this.WithSourceDatabase(sourceDatabase.Id));
}
private static void ValidateSqlDatabase(ISqlDatabase sqlDatabase, string databaseName)
{
Assert.NotNull(sqlDatabase);
Assert.Equal(sqlDatabase.Name, databaseName);
Assert.Equal(SqlServerName, sqlDatabase.SqlServerName);
Assert.Equal(sqlDatabase.Collation, Collation);
Assert.Equal(sqlDatabase.Edition, DatabaseEditions.Standard);
}
public ShiragameProvider(ISqlDatabase backingDatabase)
{
this.backingDatabase = backingDatabase;
string shiragameVer = this.backingDatabase.Query <string>("SELECT version FROM shiragame").First();
string stoneVer = this.backingDatabase.Query <string>("SELECT version FROM shiragame").First();
this.StoneVersion = Version.Parse(stoneVer);
this.DatabaseVersion = Version.Parse(shiragameVer);
}
public static Task AddData <TDbContext>(this ISqlDatabase <TDbContext> database, IEnumerable <object> entities)
where TDbContext : DbContext
{
Guard.AgainstNull(nameof(entities), entities);
foreach (var entity in entities)
{
database.Context.Set(entity.GetType()).Add(entity);
}
return(database.Context.SaveChangesAsync());
}
protected override void OnFixtureInitialize()
{
DropStructureSet<Class_2579AF20_51A0_475A_A24D_8056828DB1DC>();
_structureSchema = Database.StructureSchemas.GetSchema<Class_2579AF20_51A0_475A_A24D_8056828DB1DC>();
_structureSetPrefix = typeof(Class_2579AF20_51A0_475A_A24D_8056828DB1DC).Name;
_indexesTableName = _structureSetPrefix + "Indexes";
_sqlDb = new SqlDatabase(Database.ConnectionInfo);
}
private string GenerateConnectionString(ISqlDatabase sqlDatabase)
{
SqlDatabaseAccess sqlDatabaseAccess =
this.cloudBroker.GetAdminAccess();
return($"Server=tcp:{sqlDatabase.SqlServerName}.database.windows.net,1433;" +
$"Initial Catalog={sqlDatabase.Name};" +
$"User ID={sqlDatabaseAccess.AdminName};" +
$"Password={sqlDatabaseAccess.AdminAccess};");
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (SqlDatabase != null)
{
SqlDatabase.Dispose();
SqlDatabase = null;
}
}
}
public static async Task AddDataUntracked <TDbContext>(this ISqlDatabase <TDbContext> database, IEnumerable <object> entities)
where TDbContext : DbContext
{
Guard.AgainstNull(nameof(entities), entities);
using var context = database.NewDbContext();
foreach (var entity in entities)
{
database.Context.Set(entity.GetType()).Add(entity);
}
await context.SaveChangesAsync();
}
public static SqlDataReader CreateDataReader(this ISqlDatabase db, SqlConnection cnn, SqlTransaction transaction, string query, params object[] parameters)
{
using (var command = db.CreateCommand(cnn, transaction, query, parameters))
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
return(command.ExecuteReader());
}
}
public static SqlCommand CreateCommand(this ISqlDatabase db, SqlConnection cnn, SqlTransaction transaction, string query, params object[] parameters)
{
var command = new SqlCommand(query);
var ex = new Regex(@"[\\\!\@\#|$\^\&\*\(\)\{\}\[\]\:\;\""\'\<\>\,\.\?\/\~\-\+\=\`]", RegexOptions.Compiled & RegexOptions.Singleline);
command.Connection = (cnn == null)
? new SqlConnection(db.ConnectionString)
: command.Connection = cnn;
command.Transaction = transaction;
if (parameters != null)
{
var parameterNames = new object[parameters.Length - 1 + 1];
for (var counter = 0; counter <= parameters.Length - 1; counter++)
{
if (parameters[counter] is Dictionary <string, object> index)
{
foreach (var entry in index)
{
command.Parameters.AddWithValue(entry.Key.StartsWith("@") ? entry.Key : $"@{entry.Key}", GetParameter(entry.Value));
}
}
else if (parameters[counter] is KeyValuePair <string, object> entry)
{
command.Parameters.AddWithValue(entry.Key.StartsWith("@") ? entry.Key : $"@{entry.Key}", GetParameter(entry.Value));
}
else if (parameters[counter] is IEnumerable items && !(parameters[counter] is string))
{
var sb = new StringBuilder();
foreach (object item in items)
{
if (IsNumber(item))
{
sb.Append($"{item},");
}
else if (item is string s && !ex.IsMatch(s))
{
sb.Append($"'{s}',");
}
else
{
// dim parameterName = CreateParameterName()
// sb.Append($"@P{counter}_{counter2},")
// command.Parameters.AddWithValue(String.Concat("@P", counter, "_", counter2), GetParameter(item))
var parameterName = CreateParameterName();
sb.Append($"{parameterName},");
command.Parameters.AddWithValue(parameterName, GetParameter(item));
}
}
public async Task BackupDatabase(Backup backup)
{
_logger.LogInformation("Authenticating Azure Management object");
AzureCredentials credentials = SdkContext.AzureCredentialsFactory.FromFile(Directory.GetCurrentDirectory() + "\\Azure_Credentials.txt");
IAzure azure = Azure
.Configure()
.WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic)
.Authenticate(credentials)
.WithDefaultSubscription();
_logger.LogInformation("Get SQL database reference");
ISqlServer sqlServer = await azure.SqlServers.GetByResourceGroupAsync(backup.Source.SqlServerResourceGroup, backup.Source.SqlServerName);
ISqlDatabase sqlDatabase = await sqlServer.Databases.GetAsync(backup.Source.SqlDatabaseName);
_logger.LogInformation("Get storage account reference");
IStorageAccount storageAccount = azure.StorageAccounts.GetByResourceGroup(backup.Destination.StorageAccountResourceGroup, backup.Destination.StorageAccountName);
_logger.LogInformation("Export database to storage account");
string blobPath = backup.Source.SqlDatabaseName + "_" + DateTime.Now.ToString("yyyyMMdd") + ".bacpac";
ISqlDatabaseImportExportResponse exportedSqlDatabase = sqlDatabase.ExportTo(storageAccount, backup.Destination.StorageContainerName, blobPath)
.WithSqlAdministratorLoginAndPassword(backup.Source.SqlAdminUsername, backup.Source.SqlAdminPassword)
.Execute();
_logger.LogInformation("Get reference to storage account");
CloudBlobContainer container = new CloudBlobContainer(new Uri(backup.Destination.StorageContainerConnectionString));
_logger.LogInformation("Get reference to blob");
CloudBlockBlob blockBlob = container.GetBlockBlobReference(blobPath);
_logger.LogInformation("Download blob");
if (await blockBlob.ExistsAsync())
{
string filePath = backup.Destination.LocalDirectory + blobPath;
await blockBlob.DownloadToFileAsync(filePath, FileMode.Create);
}
else
{
throw new FileNotFoundException("Target blob not found in storage account");
}
if (backup.Destination.DeleteFromStorageAfterDownload)
{
_logger.LogInformation("Removing file from storage");
await blockBlob.DeleteAsync();
}
}
private static void CreateNewDatabase(IAzure azure, string serverId)
{
try
{
ISqlDatabase tenantDatabase = azure.SqlServers.GetById(serverId).Databases
.Define(TenantParameters.GetFormattedShortName)
.Create();
MiscellaneousUtils.PrintDatabase(tenantDatabase);
}
catch (Exception ex)
{
throw ex;
}
}
private static void CreateNewDatabaseWithActiveDirectory(IAzure azure, string serverId)
{
try
{
IStorageAccount account = azure.StorageAccounts.GetById(TenantParameters.StorageAccountId);
ISqlDatabase tenantDatabase = azure.SqlServers.GetById(serverId).Databases
.Define(TenantParameters.GetFormattedShortName)
.ImportFrom(account, TenantParameters.ContainerName, TenantParameters.TenantTemplateName)
.WithActiveDirectoryLoginAndPassword(UserCredentials.UserName, UserCredentials.Password)
.Create();
MiscellaneousUtils.PrintDatabase(tenantDatabase);
}
catch (Exception ex)
{
throw ex;
}
}
public async ValueTask <SqlDatabase> ProvisionSqlDatabaseAsync(
string projectname,
string environment,
ISqlServer sqlServer)
{
string sqlDatabaseName = $"{projectname}-db-{environment}".ToLower();
this.loggingBroker.LogActivity(message: $"Provisioning {sqlDatabaseName}...");
ISqlDatabase sqlDatabase =
await this.cloudBroker.CreateSqlDatabaseAsync(
sqlDatabaseName,
sqlServer);
this.loggingBroker.LogActivity(message: $"{sqlDatabaseName} Provisioned");
return(new SqlDatabase
{
Database = sqlDatabase,
ConnectionString = GenerateConnectionString(sqlDatabase)
});
}
///GENMHASH:D55C0BC6C1896D10050A2A45B9F1E6FC:2D29DF7CB31893427EBF0651FBEE3AD4
public SqlSyncMemberImpl WithMemberSqlDatabase(ISqlDatabase sqlDatabase)
{
this.Inner.ServerName = sqlDatabase.SqlServerName;
this.Inner.DatabaseName = sqlDatabase.Name;
return(this);
}
/**
* Azure App Service basic sample for managing web apps.
* - Create a SQL database in a new SQL server
* - Create a web app deployed with Project Nami (WordPress's SQL Server variant)
* that contains the app settings to connect to the SQL database
* - Update the SQL server's firewall rules to allow the web app to access
* - Clean up
*/
public static void RunSample(IAzure azure)
{
string appName = SdkContext.RandomResourceName("webapp1-", 20);
string appUrl = appName + Suffix;
string sqlServerName = SdkContext.RandomResourceName("jsdkserver", 20);
string sqlDbName = SdkContext.RandomResourceName("jsdkdb", 20);
string rgName = SdkContext.RandomResourceName("rg1NEMV_", 24);
try
{
//============================================================
// Create a sql server
Utilities.Log("Creating SQL server " + sqlServerName + "...");
ISqlServer server = azure.SqlServers.Define(sqlServerName)
.WithRegion(Region.USWest)
.WithNewResourceGroup(rgName)
.WithAdministratorLogin(Admin)
.WithAdministratorPassword(Password)
.Create();
Utilities.Log("Created SQL server " + server.Name);
//============================================================
// Create a sql database for the web app to use
Utilities.Log("Creating SQL database " + sqlDbName + "...");
ISqlDatabase db = server.Databases.Define(sqlDbName)
.Create();
Utilities.Log("Created SQL database " + db.Name);
//============================================================
// Create a web app with a new app service plan
Utilities.Log("Creating web app " + appName + "...");
IWebApp app = azure.WebApps
.Define(appName)
.WithRegion(Region.USWest)
.WithExistingResourceGroup(rgName)
.WithNewWindowsPlan(PricingTier.StandardS1)
.WithPhpVersion(PhpVersion.V5_6)
.DefineSourceControl()
.WithPublicGitRepository("https://github.com/ProjectNami/projectnami")
.WithBranch("master")
.Attach()
.WithAppSetting("ProjectNami.DBHost", server.FullyQualifiedDomainName)
.WithAppSetting("ProjectNami.DBName", db.Name)
.WithAppSetting("ProjectNami.DBUser", Admin)
.WithAppSetting("ProjectNami.DBPass", Password)
.Create();
Utilities.Log("Created web app " + app.Name);
Utilities.Print(app);
//============================================================
// Allow web app to access the SQL server
Utilities.Log("Allowing web app " + appName + " to access SQL server...");
Microsoft.Azure.Management.Sql.Fluent.SqlServer.Update.IUpdate update = server.Update();
foreach (var ip in app.OutboundIPAddresses)
{
update = update.WithNewFirewallRule(ip);
}
server = update.Apply();
Utilities.Log("Firewall rules added for web app " + appName);
Utilities.PrintSqlServer(server);
Utilities.Log("Your WordPress app is ready.");
Utilities.Log("Please navigate to http://" + appUrl + " to finish the GUI setup. Press enter to exit.");
Utilities.ReadLine();
}
finally
{
try
{
Utilities.Log("Deleting Resource Group: " + rgName);
azure.ResourceGroups.DeleteByName(rgName);
Utilities.Log("Deleted Resource Group: " + rgName);
}
catch (NullReferenceException)
{
Utilities.Log("Did not create any resources in Azure. No clean up is necessary");
}
catch (Exception g)
{
Utilities.Log(g);
}
}
}
private static void ValidateSqlDatabaseWithElasticPool(ISqlDatabase sqlDatabase, string databaseName)
{
ValidateSqlDatabase(sqlDatabase, databaseName);
Assert.Equal(SqlElasticPoolName, sqlDatabase.ElasticPoolName);
}
/// <summary>
/// Sets the parent SQL server for the new Sync Group.
/// </summary>
/// <param name="sqlDatabase">The parent SQL database.</param>
/// <return>The next stage of the definition.</return>
SqlSyncGroupOperations.Definition.IWithSyncDatabaseId SqlSyncGroupOperations.Definition.IWithSqlServer.WithExistingSqlDatabase(ISqlDatabase sqlDatabase)
{
return(this.WithExistingSqlDatabase(sqlDatabase));
}
/// <summary>
/// Adds an existing SQL Database to the Elastic Pool.
/// </summary>
/// <param name="database">The database to be added.</param>
/// <return>The database.</return>
Microsoft.Azure.Management.Sql.Fluent.ISqlDatabase Microsoft.Azure.Management.Sql.Fluent.ISqlElasticPool.AddExistingDatabase(ISqlDatabase database)
{
return(this.AddExistingDatabase(database));
}
public TransactionRepository(ISqlDatabase db)
{
_db = db;
}
/// <summary>
/// Adds the database in the SQL elastic pool.
/// </summary>
/// <param name="database">Database instance to be added in SQL elastic pool.</param>
/// <return>The next stage of the definition.</return>
SqlElasticPoolOperations.Definition.IWithCreate SqlElasticPoolOperations.Definition.IWithDatabase.WithExistingDatabase(ISqlDatabase database)
{
return(this.WithExistingDatabase(database));
}
/// <summary>
/// Adds the database in the SQL elastic pool.
/// </summary>
/// <param name="database">Database instance to be added in SQL elastic pool.</param>
/// <return>The next stage of the definition.</return>
SqlElasticPool.Update.IUpdate SqlElasticPool.Update.IWithDatabase.WithExistingDatabase(ISqlDatabase database)
{
return(this.WithExistingDatabase(database));
}
// This function will get triggered/executed when a new message is written
// on an Azure Queue called queue.
public static async Task ProcessQueueMessage([QueueTrigger("tenant-queue")] Tenant tenant)
{
TenantRepository tenantRepo = new TenantRepository(tenant.TenantID);
data.ITenant t = await tenantRepo.GetAsync();
if (t == null || string.IsNullOrEmpty(t.Server) == false || string.IsNullOrEmpty(t.Database) == false)
{
Console.WriteLine("[{0}] Tenant already configured or something when wrong.", tenant.TenantID);
return;
}
var credentials = SdkContext.AzureCredentialsFactory
.FromServicePrincipal(CloudConfigurationManager.GetSetting("az:clientId"), //clientId,
CloudConfigurationManager.GetSetting("az:clientSecret"), //clientSecret,
CloudConfigurationManager.GetSetting("az:tenantId"), //tenantId,
AzureEnvironment.AzureGlobalCloud);
var azure = Microsoft.Azure.Management.Fluent.Azure
.Configure()
.Authenticate(credentials)
.WithDefaultSubscription();
string startAddress = "0.0.0.0";
string endAddress = "255.255.255.255";
var servers = await azure.SqlServers.ListAsync();
var avaibleServers = servers.Where(x => x.Databases.List().Count < 150 && x.ResourceGroupName == "ssas-demo");
ISqlServer sqlServer;
if (avaibleServers.Any())
{
sqlServer = avaibleServers.FirstOrDefault();
}
else
{
string sqlServerName = SdkContext.RandomResourceName("saas-", 8);
// Create the SQL server instance
sqlServer = azure.SqlServers.Define(sqlServerName)
.WithRegion(Region.USEast)
.WithExistingResourceGroup("ssas-demo")
.WithAdministratorLogin(CloudConfigurationManager.GetSetting("sqlserver:username"))
.WithAdministratorPassword(CloudConfigurationManager.GetSetting("sqlserver:password"))
.WithNewFirewallRule(startAddress, endAddress)
.Create();
}
string dbName = SdkContext.RandomResourceName("saas-", 8);
// Create the database
ISqlDatabase sqlDb = sqlServer.Databases.Define(dbName)
.WithEdition(DatabaseEditions.Standard)
.WithServiceObjective(ServiceObjectiveName.S0)
.Create();
Console.WriteLine(sqlServer.FullyQualifiedDomainName);
Console.WriteLine(sqlDb.Name);
await tenantRepo.UpdateAsync(sqlDb.SqlServerName, sqlDb.Name);
if (string.IsNullOrEmpty(tenant.Email) == false)
{
await SendEmailNotification(tenant.Email, tenant.Organization);
}
}
private DatabaseFixture([NotNull] ISqlDatabase database, [NotNull] IServiceProvider serviceProvider)
{
_database = database;
ServiceProvider = serviceProvider;
}
protected override void OnFixtureInitialize()
{
DropStructureSet<Class_12E6E3A7_482C_4E1A_88BE_393D29253203>();
_structureSchema = Database.StructureSchemas.GetSchema<Class_12E6E3A7_482C_4E1A_88BE_393D29253203>();
_structureSetPrefix = typeof(Class_12E6E3A7_482C_4E1A_88BE_393D29253203).Name;
_structureTableName = _structureSetPrefix + "Structure";
_indexesTableName = _structureSetPrefix + "Indexes";
_sqlDb = new SqlDatabase(Database.ConnectionInfo);
}
protected override void OnFixtureInitialize()
{
DropStructureSet<Class_53966417_B25D_49E1_966B_58754110781C>();
_structureSchema = Database.StructureSchemas.GetSchema<Class_53966417_B25D_49E1_966B_58754110781C>();
_structureSetPrefix = typeof (Class_53966417_B25D_49E1_966B_58754110781C).Name;
_structureTableName = _structureSetPrefix + "Structure";
_indexesTableName = _structureSetPrefix + "Indexes";
_sqlDb = new SqlDatabase(Database.ConnectionInfo);
}
/// <summary>
/// Sets the resource if of source database for the SQL Database.
/// Collation, Edition, and MaxSizeBytes must remain the same while the link is
/// active. Values specified for these parameters will be ignored.
/// </summary>
/// <param name="sourceDatabase">Instance of the source database.</param>
/// <return>The next stage of the definition.</return>
SqlDatabase.Definition.IWithCreateMode SqlDatabase.Definition.IWithSourceDatabaseId.WithSourceDatabase(ISqlDatabase sourceDatabase)
{
return(this.WithSourceDatabase(sourceDatabase) as SqlDatabase.Definition.IWithCreateMode);
}