private static void GetSqlStatus(ref List <SqlInstance> servers)
{
ServiceController sc;
foreach (IGrouping <string, SqlInstance> server in servers.GroupBy(x => x.Host.FQDN))
{
SqlInstance srv = server.First();
if (srv.Host.Domain?.ToLower() == "formulabi.local")
{
if (Helpers.PingHost(srv.Host.FQDN, 500, out IPStatus status))
{
sc = new ServiceController();
sc.MachineName = srv.Host.FQDN;
foreach (SqlInstance service in server)
{
service.ServerStatus = status;
sc.ServiceName = service.ServiceName;
try {
service.ServiceStatus = sc.Status;
}
catch (Exception e) {
service.Info = e.Message;
}
}
}
else
{
foreach (SqlInstance service in server)
{
service.ServerStatus = status;
}
}
}
}
}
public static List <SqlInstance> GetSqlServersFromNetwork(bool debug = false)
{
List <SqlInstance> sqlInstances = new List <SqlInstance>();
string output;
using (Process proc = new Process()) {
proc.StartInfo.CreateNoWindow = true;
proc.StartInfo.FileName = "sqlcmd.exe";
proc.StartInfo.Arguments = "-L";
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
if (!debug)
{
proc.Start();
}
output = debug ? $"\r\nServers:\r\n URAN\\LG\r\n MARS\\RFM\r\n" : proc.StandardOutput.ReadToEnd();
}
string[] serversStrings = output.Replace("Servers:", "").Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s in serversStrings)
{
string[] server = s.Split(new string[] { "\\" }, StringSplitOptions.None);
SqlInstance temp = (server.Length > 1 ? new SqlInstance(server[0], server[1]) : new SqlInstance(server[0]));
if (temp.Host != null)
{
sqlInstances.Add(temp);
}
}
GetSqlStatus(ref sqlInstances);
List <SqlInstance> result = sqlInstances.Where(x => x.ServiceStatus == ServiceControllerStatus.Running).ToList();
return(result);
}
private void comboBoxInstances_SelectedIndexChanged(object sender, EventArgs e)
{
if (!this.FormLoaded)
{
return;
}
if (sender is ComboBox cmbx)
{
SqlInstance instance = cmbx.SelectedItem as SqlInstance;
string ConnectionString = (instance.InstanceName == "DEFAULT") ? $@"Server={instance.Host.FQDN};Database=KB;Trusted_Connection=True;Connection Timeout=2;" : $@"Server={instance.InstanceFullName};Database=KB;Trusted_Connection=True;Connection Timeout=2;";
if (this.Sql == null)
{
this.Sql = new SqlConnection(ConnectionString);
}
switch (this.Sql.State)
{
case ConnectionState.Open:
case ConnectionState.Connecting:
case ConnectionState.Executing:
case ConnectionState.Fetching:
case ConnectionState.Broken:
this.Sql.Close();
this.Sql = new SqlConnection(ConnectionString);
break;
}
this.Sql.Open();
List <Roles> roles = GetRoles(instance);
if (roles.Any())
{
this.listBoxRoles.Enabled = true;
this.listBoxRoles.DataSource = roles;
this.listBoxRoles.DisplayMember = "RoleName";
this.listBoxRoles.ClearSelected();
}
else
{
this.listBoxRoles.Items.Clear();
this.listBoxRoles.Enabled = false;
}
List <KBMenu> menus = GetMenus(instance);
if (roles.Any())
{
this.listBoxMenu.Enabled = true;
this.listBoxMenu.DataSource = menus;
this.listBoxMenu.DisplayMember = "MenuName";
this.listBoxMenu.ClearSelected();
}
else
{
this.listBoxMenu.Items.Clear();
this.listBoxMenu.Enabled = false;
}
}
}
private List <Roles> GetRoles(SqlInstance instance)
{
List <Roles> result = new List <Roles>();
string query = @"SELECT * FROM [KB].[dbo].[Roles]";
DataTable dt = SqlInstance.GetSqlDataTable(query, this.Sql);
try {
result = dt.ToList <Roles>();
}
catch (Exception e) {
Console.WriteLine(e);
throw;
}
return(result);
}
private List <KBMenu> GetMenus(SqlInstance instance)
{
List <KBMenu> result = new List <KBMenu>();
string query = @"SELECT * FROM [KB].[dbo].[Menu] where IdMenuType = 4";
DataTable dt = SqlInstance.GetSqlDataTable(query, this.Sql);
try {
result = dt.ToList <KBMenu>();
}
catch (Exception e) {
Console.WriteLine(e);
throw;
}
return(result);
}
private void SetupVars()
{
//checkTrustedHosts();
this.StartupSplash.Status = "Получаем список доменов";
this.comboBoxAdDomain.DataSource = Enum.GetValues(typeof(Domain.AdDomain));
this.StartupSplash.Status = "Получаем список почтовых доменов";
this.comboBoxMailDomain.DataSource = Enum.GetValues(typeof(Domain.MailDomain));
this.StartupSplash.Status = "Получаем список инстансов в сети";
#if DEBUG
this.SqlInstances = SqlInstance.GetSqlServersFromNetwork(debug: true);
#else
this.SqlInstances = SqlInstance.GetSqlServersFromNetwork(debug: false);
#endif
this.comboBoxInstances.DataSource = this.SqlInstances;
this.comboBoxInstances.DisplayMember = "InstanceFullName";
this.comboBoxInstances.SelectedIndex = -1;
this.checkBoxAddMail.Checked = true;
this.checkBoxPassword.Checked = true;
checkBox1_CheckedChanged(null, null);
checkBoxAddMail_CheckedChanged(null, null);
this.TransType = Helpers.TransliterationType.Gost;
}
