/// <summary>
/// Get databases for server
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Shown(object sender, EventArgs e)
{
var ops = new SqlDatabases();
DatabaseNameListBox.DataSource = ops.DatabaseNames();
var dOperations = new SqlTables("master");
if (!dOperations.NorthWindDemoExists())
{
return;
}
DatabaseNameListBox.SelectedIndex = DatabaseNameListBox.FindString("NorthWindDemo");
DatabaseTableNamesListBox.SelectedIndex = DatabaseTableNamesListBox.FindString("Customer");
var databaseTables = new SqlTables("NorthWindDemo");
_countryColumnList = databaseTables.CountrySelected;
foreach (var item in _countryColumnList)
{
SelectedTableColumnCheckedListBox.FindItemAndSetChecked(item);
}
}
public int Run()
{
var backupFolder = settings.Folder;
logger.Start();
var systemConnectionString = settings.MasterConnection;
var databaseBackupedCount =
new SqlDatabases()
.Backup(logger, systemConnectionString, backupFolder, databaseNames, settings.AppendDatabase);
return databaseBackupedCount;
}
public int Run()
{
var backupFolder = settings.Folder;
logger.Start();
var systemConnectionString = settings.MasterConnection;
var databaseBackupedCount =
new SqlDatabases()
.Backup(logger, systemConnectionString, backupFolder, databaseNames, settings.AppendDatabase);
return(databaseBackupedCount);
}
public void TestServiceBuilder()
{
var bldr = new ConfigurationBuilder()
.AddJsonFile("configurationsettings.json");
var config = bldr.Build();
var services = new ServiceCollection();
services.AddOptions();
services.AddLogging();
services.AddSqlServices(config);
var serviceProvider = services.BuildServiceProvider();
var globalOptions = serviceProvider.GetService <IOptions <SqlGlobalPropertiesOptions> >();
var sqlDbOptions = serviceProvider.GetService <IOptions <SqlDbConnectionOptions> >();
var sqlShardOptions = serviceProvider.GetService <IOptions <SqlShardConnectionOptions> >();
var dbLogger = NSubstitute.Substitute.For <Microsoft.Extensions.Logging.ILogger <SqlDatabases> >();
var dbService = new SqlDatabases(sqlDbOptions, globalOptions, dbLogger);
dbService.Count.Should().Be(2, "two connections are defined in the configuration file");
dbService["MainDb"].Read.ConnectionString.Should().Be("Data Source=10.10.25.1;Initial Catalog=MainDb;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyApp;ConnectRetryCount=0", "this is the value inherited from global configuratoin settings");
dbService["MainDb"].Write.ConnectionString.Should().Be("Data Source=10.10.25.5;Initial Catalog=MainDb;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyApp;ConnectRetryCount=0", "this is the value inherited from global configuratoin settings");
dbService["OtherDb"].Read.ConnectionString.Should().Be("Data Source=MyOtherServer;Initial Catalog=OtherDb;Connect Timeout=20;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;Current Language=English;ConnectRetryCount=0", "this is the value inherited from global configuratoin settings");
var shardLogger = NSubstitute.Substitute.For <Microsoft.Extensions.Logging.ILogger <ArgentSea.Sql.SqlShardSets> >();
var shardService = new ArgentSea.Sql.SqlShardSets(sqlShardOptions, globalOptions, shardLogger);
shardService.Count.Should().Be(2, "two shard sets are defined in the configuration file");
shardService["Inherit"].Count.Should().Be(2, "the configuration file has two shard connections defined on shard set Set1");
shardService["Explicit"].Count.Should().Be(2, "the configuration file has two shard connections defined on shard set Set2");
shardService["Inherit"][0].Read.ConnectionString.Should().Be("Data Source=10.10.23.20;Failover Partner=MyMirror;Initial Catalog=dbName2;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;ApplicationIntent=ReadOnly;ConnectRetryCount=0", "the configuration file builds this connection string");
shardService["Inherit"][0].Write.ConnectionString.Should().Be("Data Source=10.10.23.20;Failover Partner=MyMirror;Initial Catalog=dbName2;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;ApplicationIntent=ReadWrite;ConnectRetryCount=0", "the configuration file builds this connection string");
shardService["Inherit"][1].Read.ConnectionString.Should().Be("Data Source=MyOtherServer;Initial Catalog=dbName4;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;ApplicationIntent=ReadOnly;ConnectRetryCount=0", "the configuration file builds this connection string");
shardService["Inherit"][1].Write.ConnectionString.Should().Be("Data Source=MyOtherOtherServer;Initial Catalog=dbName3;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;ApplicationIntent=ReadWrite;ConnectRetryCount=0", "the configuration file builds this connection string");
shardService["Explicit"][0].Read.ConnectionString.Should().Be("Data Source=localhost;Failover Partner=;Initial Catalog=MyDb1;Integrated Security=False;Persist Security Info=False;User ID=;Password=;Pooling=True;Min Pool Size=0;Max Pool Size=100;MultipleActiveResultSets=False;Replication=False;Connect Timeout=2;Encrypt=False;TrustServerCertificate=True;Load Balance Timeout=0;Packet Size=8000;Type System Version=Latest;Application Name=MyWebApp2;Current Language=english;Workstation ID=;ApplicationIntent=ReadWrite;MultiSubnetFailover=True;ConnectRetryCount=0", "the configuration file builds this connection string");
shardService["Explicit"][0].Write.ConnectionString.Should().Be(shardService["Explicit"][0].Read.ConnectionString, "the read and write connections should be identical");
shardService["Explicit"][1].Read.ConnectionString.Should().Be("Data Source=localhost;Failover Partner=rorriMyM;Initial Catalog=MyDb2;Integrated Security=False;Persist Security Info=False;User ID=;Password=;Pooling=True;Min Pool Size=0;Max Pool Size=100;MultipleActiveResultSets=False;Replication=False;Connect Timeout=2;Encrypt=False;TrustServerCertificate=True;Load Balance Timeout=0;Packet Size=8000;Type System Version=Latest;Application Name=MyWebApp3;Current Language=english;Workstation ID=;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;ConnectRetryCount=0", "the configuration file builds this connection string");
shardService["Explicit"][1].Write.ConnectionString.Should().Be("Data Source=localhost;Failover Partner=;Initial Catalog=MyDb3;Integrated Security=False;Persist Security Info=False;User ID=;Password=;Pooling=True;Min Pool Size=0;Max Pool Size=100;MultipleActiveResultSets=False;Replication=False;Connect Timeout=2;Encrypt=False;TrustServerCertificate=True;Load Balance Timeout=0;Packet Size=8000;Type System Version=Latest;Application Name=MyWebApp4;Current Language=english;Workstation ID=;ApplicationIntent=ReadWrite;MultiSubnetFailover=True;ConnectRetryCount=0", "the configuration file builds this connection string");
}
public void TestConfigChange()
{
var bldr = new ConfigurationBuilder()
.AddJsonFile("configurationsettings.json");
var config = bldr.Build();
var services = new ServiceCollection();
services.AddOptions();
services.AddLogging();
services.AddSqlServices(config);
var serviceProvider = services.BuildServiceProvider();
var globalOptions = serviceProvider.GetService <IOptions <SqlGlobalPropertiesOptions> >();
var sqlDbOptions = serviceProvider.GetService <IOptions <SqlDbConnectionOptions> >();
var sqlShardOptions = serviceProvider.GetService <IOptions <SqlShardConnectionOptions> >();
var dbLogger = NSubstitute.Substitute.For <Microsoft.Extensions.Logging.ILogger <SqlDatabases> >();
var dbService = new SqlDatabases(sqlDbOptions, globalOptions, dbLogger);
var shardLogger = NSubstitute.Substitute.For <Microsoft.Extensions.Logging.ILogger <ArgentSea.Sql.SqlShardSets> >();
var shardService = new SqlShardSets(sqlShardOptions, globalOptions, shardLogger);
dbService["MainDb"].Read.ConnectionString.Should().Be("Data Source=10.10.25.1;Initial Catalog=MainDb;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyApp;ConnectRetryCount=0", "this is the value inherited from global configuratoin settings");
shardService["Inherit"][0].Read.ConnectionString.Should().Be("Data Source=10.10.23.20;Failover Partner=MyMirror;Initial Catalog=dbName2;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;ApplicationIntent=ReadOnly;ConnectRetryCount=0", "the configuration file builds this connection string");
globalOptions.Value.CurrentLanguage = "Pigeon";
dbService["MainDb"].Read.ConnectionString.Should().Be("Data Source=10.10.25.1;Initial Catalog=MainDb;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyApp;Current Language=Pigeon;ConnectRetryCount=0", "this is the value inherited from global configuratoin settings");
shardService["Inherit"][0].Read.ConnectionString.Should().Be("Data Source=10.10.23.20;Failover Partner=MyMirror;Initial Catalog=dbName2;Connect Timeout=5;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;Current Language=Pigeon;ApplicationIntent=ReadOnly;ConnectRetryCount=0", "the configuration file builds this connection string");
sqlDbOptions.Value.SqlDbConnections[0].PacketSize = 16384;
sqlShardOptions.Value.SqlShardSets[0].PacketSize = 16384;
dbService["MainDb"].Read.ConnectionString.Should().Be("Data Source=10.10.25.1;Initial Catalog=MainDb;Connect Timeout=5;Packet Size=16384;Type System Version=\"SQL Server 2012\";Application Name=MyApp;Current Language=Pigeon;ConnectRetryCount=0", "this is the value inherited from global configuratoin settings");
shardService["Inherit"][0].Read.ConnectionString.Should().Be("Data Source=10.10.23.20;Failover Partner=MyMirror;Initial Catalog=dbName2;Connect Timeout=5;Packet Size=16384;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;Current Language=Pigeon;ApplicationIntent=ReadOnly;ConnectRetryCount=0", "the configuration file builds this connection string");
sqlShardOptions.Value.SqlShardSets[0].PacketSize = 4096;
shardService["Inherit"][0].Read.ConnectionString.Should().Be("Data Source=10.10.23.20;Failover Partner=MyMirror;Initial Catalog=dbName2;Connect Timeout=5;Packet Size=4096;Type System Version=\"SQL Server 2012\";Application Name=MyOtherApp;Current Language=Pigeon;ApplicationIntent=ReadOnly;ConnectRetryCount=0", "the configuration file builds this connection string");
}
public SubscriberStore(SqlDatabases dbs, ILogger <SubscriberStore> logger)
{
_dbs = dbs;
_logger = logger;
}
//private void initSqlExpressionDependencies(int serverObjectId, int databaseId)
//{
// try
// {
// var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName;
// var databaseName = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName;
// MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading expression dependencies from server object '{0}', database '{1}'... ", serverObjectName, databaseName));
// int counter = 0;
// DateTime dt = DateTime.Now;
// string query = QueryReader.Read("expression_dependencies.sql");
// ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName);
// SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
// cmd.CommandText = query;
// cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
// cmd.CommandType = CommandType.Text;
// SqlDataReader rdr = cmd.ExecuteReader();
// while (rdr.Read())
// {
// counter++;
// int referencedId = -1;
// int.TryParse(rdr["referenced_id"].ToString(), out referencedId);
// _sqlExpressionDependencies.Add(new MySqlExpressionDependency
// {
// ServerObjectId = serverObjectId,
// ServerObjectName = serverObjectName,
// DatabaseId = databaseId,
// DatabaseName = databaseName,
// ReferencingId = (int)rdr["referencing_id"],
// ReferencedServerName = (string)rdr["referenced_server_name"],
// ReferencedDatabaseName = (string)rdr["referenced_database_name"],
// ReferencedSchemaName = (string)rdr["referenced_schema_name"],
// ReferencedEntityName = (string)rdr["referenced_entity_name"],
// ReferencedId = (referencedId != -1) ? referencedId : (int?)null
// });
// }
// rdr.Close();
// MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} expression dependencies.", counter), (DateTime.Now - dt).TotalSeconds);
// }
// catch (Exception ex)
// {
// MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading expression dependencies: {0}", ex.Message));
// var exForm = new ExceptionForm(ex);
// exForm.ShowDialog();
// }
//}
private void initSqlExpressionDependencies(int serverObjectId, int databaseId)
{
var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName;
var databaseName = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName;
MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading expression dependencies from server '{0}', database '{1}'... ", serverObjectName, databaseName));
int initialNumberOfRows = SqlExpressionDependencies.Length;
DateTime dt = DateTime.Now;
//DateTime timeCounterInner;
DataTable ExpressionDependenciesDataTable = new DataTable();
string query = QueryReader.Read("expression_dependencies.sql");
var svr = SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId);
var db = SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId && o.DatabaseId == databaseId);
if (svr == null || db == null)
{
MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies.", SqlExpressionDependencies.Length - initialNumberOfRows), (DateTime.Now - dt).TotalSeconds);
return;
}
ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName);
SqlCommand cmd = ConnectionPool.GetConnection(serverObjectId).CreateCommand();
cmd.CommandText = query;
cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
cmd.CommandType = CommandType.Text;
ExpressionDependenciesDataTable.Load(cmd.ExecuteReader());
//var results = from myRow in dataTable.AsEnumerable()
// select new
// {
// ReferencingId = Convert.ToInt32(myRow["referencing_id"]),
// ReferencedId = Convert.ToInt32(((myRow["referenced_id"] != DBNull.Value) ? Convert.ToInt32(myRow["referenced_id"]) : -1)),
// ReferencedSchemaId = _objectList.First(o => o.ServerName.Equals(serverName, StringComparison.InvariantCultureIgnoreCase)
// && o.DatabaseName.Equals(databaseName, StringComparison.InvariantCultureIgnoreCase)
// && o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)).SchemaId,
// ReferencedEntityName = (string)myRow["referenced_entity_name"]
// };
var databaseDependencies = from myRow in ExpressionDependenciesDataTable.AsEnumerable()
select new
{
ReferencingId = Convert.ToInt32(myRow["referencing_id"]),
ReferencedId = Convert.ToInt32(((myRow["referenced_id"] != DBNull.Value) ? Convert.ToInt32(myRow["referenced_id"]) : -1)),
ReferencedSchemaId = (SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId &&
o.DatabaseId == databaseId &&
o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)) != null) ?
(SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId &&
o.DatabaseId == databaseId &&
o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)).SchemaId)
: -1,
ReferencedServerName = (string)myRow["referenced_server_name"],
ReferencedDatabaseName = (string)myRow["referenced_database_name"],
ReferencedSchemaName = (string)myRow["referenced_schema_name"],
ReferencedEntityName = (string)myRow["referenced_entity_name"]
};
var res = from dbDep in databaseDependencies
join parentObj in SqlObjects.Where(p => p.ServerObjectId == serverObjectId && p.DatabaseId == databaseId)
on dbDep.ReferencingId equals parentObj.ObjectId
join childObj in SqlObjects.Where(c => c.ServerObjectId == serverObjectId && c.DatabaseId == databaseId)
on new { SchemaId = dbDep.ReferencedSchemaId, ObjectId = dbDep.ReferencedId, ObjectName = dbDep.ReferencedEntityName } equals
new { SchemaId = childObj.SchemaId, ObjectId = childObj.ObjectId, ObjectName = childObj.ObjectName }
select new MySqlExpressionDependency
{
ServerObjectId = serverObjectId,
DatabaseId = databaseId,
ParentServerObjectId = parentObj.ServerObjectId,
ParentDatabaseId = parentObj.DatabaseId,
ParentObjectId = parentObj.ObjectId,
ChildServerObjectId = childObj.ServerObjectId,
ChildDatabaseId = childObj.DatabaseId,
ChildObjectId = childObj.ObjectId,
ChildObjectExists = true
};
var uwq = from dbDep in databaseDependencies.Where(dd => dd.ReferencedId == -1 || dd.ReferencedSchemaId == -1)
join parentObj in SqlObjects.Where(p => p.ServerObjectId == serverObjectId && p.DatabaseId == databaseId)
on dbDep.ReferencingId equals parentObj.ObjectId
select new MySqlExpressionDependency
{
ServerObjectId = serverObjectId,
DatabaseId = databaseId,
ParentServerObjectId = parentObj.ServerObjectId,
ParentDatabaseId = parentObj.DatabaseId,
ParentObjectId = parentObj.ObjectId,
ChildServerObjectName = dbDep.ReferencedServerName,
ChildDatabaseName = dbDep.ReferencedDatabaseName,
ChildSchemaName = dbDep.ReferencedSchemaName,
ChildObjectName = dbDep.ReferencedEntityName,
ChildObjectExists = false
};
_sqlExpressionDependencies.AddRange(res.Union(uwq));
MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies.", res.Count()), (DateTime.Now - dt).TotalSeconds);
}
private void initSqlObjects(int serverObjectId, int databaseId)
{
try
{
var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName;
var databaseName = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName;
MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading objects from server object '{0}', database '{1}'... ", serverObjectName, databaseName));
int counter = 0;
DateTime dt = DateTime.Now;
string query = QueryReader.Read("objects.sql");
ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName);
SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
cmd.CommandText = query;
cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
counter++;
_sqlObjects.Add(new MySqlObject
{
ServerObjectId = serverObjectId,
ServerObjectName = serverObjectName,
DatabaseId = Convert.ToInt32(rdr["db_id"]),
DatabaseName = (string)rdr["db_name"],
SchemaId = Convert.ToInt32(rdr["schema_id"]),
SchemaName = (string)rdr["schema_name"],
ObjectId = (int)(rdr["object_id"]),
ObjectName = (string)rdr["object_name"],
ObjectTypeId = (string)rdr["type"],
ObjectType = (string)rdr["type_desc"],
ObjectNameFull = (string)rdr["object_name_full"],
BaseObjectNameFull = (rdr["base_object_name_full"] != DBNull.Value) ? rdr["base_object_name_full"].ToString() : null
});
}
rdr.Close();
// Row counts
query = QueryReader.Read("table_sizes.sql");
cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
cmd.CommandText = query;
cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
cmd.CommandType = CommandType.Text;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var obj = _sqlObjects.FirstOrDefault(o => o.ObjectType.Equals("USER_TABLE") &&
o.SchemaName.Equals((string)rdr["schema_name"], StringComparison.InvariantCultureIgnoreCase) &&
o.ObjectName.Equals((string)rdr["table_name"], StringComparison.InvariantCultureIgnoreCase));
if (obj != null)
{
obj.RowCount = (long)rdr["row_counts"];
obj.TotalSpaceUsedKB = (long)rdr["total_space_kb"];
}
}
rdr.Close();
// Object definitions
query = QueryReader.Read("object_definitions.sql");
cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
cmd.CommandText = query;
cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
cmd.CommandType = CommandType.Text;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var obj = _sqlObjects.FirstOrDefault(o => o.ObjectId == (int)rdr["object_id"]);
if (obj != null)
{
obj.ObjectDefinition = (string)rdr["definition"];
}
}
rdr.Close();
MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} objects.", counter), (DateTime.Now - dt).TotalSeconds);
}
catch (Exception ex)
{
MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading database objects: {0}", ex.Message));
var exForm = new ExceptionForm(ex);
exForm.ShowDialog();
}
}
