private List <SqlInstance> GetSqlServersFromNetwork(bool test = false)
{
#if NoSearch
test = true;
#endif
var 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 (!test)
{
proc.Start();
}
output = test ? $"\r\nServers:\r\n FENIX\r\n FENIX\\EGRUL\r\n URAN\\RN\r\n" : proc.StandardOutput.ReadToEnd();
}
var serversStrings = output.Replace("Servers:", "")
.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s in serversStrings)
{
var server = s.Split(new[] { "\\" }, StringSplitOptions.None);
SqlInstance temp = server.Length > 1 ? new SqlInstance(server[0], server[1]) : new SqlInstance(server[0]);
if (temp.Host != null)
{
sqlInstances.Add(temp);
}
}
return(sqlInstances);
}
private void gridView1_RowCellClick(object sender, RowCellClickEventArgs e)
{
if (sender is GridView gv)
{
this.SelectedInstance = (SqlInstance)gv.GetRow(e.RowHandle);
}
}
private void GetSqlStatus(List <SqlInstance> instances)
{
Parallel.ForEach(instances.GroupBy(x => x.Host.FQDN),
server => {
SqlInstance srv = server.First();
this.splash.Status = $"Обработка сервера '{srv.Host.FQDN}'";
//if (srv.Host.Domain?.ToLower() == "formulabi.local") {
if (Helpers.PingHost(srv.Host.FQDN, 500, out IPStatus status))
{
ServiceController sc = new ServiceController {
MachineName = srv.Host.FQDN
};
foreach (SqlInstance instance in server)
{
instance.ServerStatus = status;
sc.ServiceName = instance.ServiceName;
try {
instance.ServiceStatus = sc.Status;
//GetinstanceDetails(service);
}
catch (InvalidOperationException invalidOperationException) {
instance.AbleToConnect = false;
instance.Info = $"Ошибка подключения: {invalidOperationException.Message}";
Debug.WriteLine(invalidOperationException.Message);
if (invalidOperationException.InnerException != null)
{
instance.Info += $": {invalidOperationException.InnerException.Message}";
Debug.WriteLine(invalidOperationException.InnerException.Message);
}
}
catch (Exception e) {
instance.AbleToConnect = false;
instance.Info = e.Message;
}
}
}
else
{
foreach (SqlInstance instance in server)
{
instance.AbleToConnect = false;
instance.ServerStatus = status;
}
}
});
this.splash.Status = "Достаём инфу по инстансам";
Parallel.ForEach(instances, instance => GetinstanceDetails(instance));
}
private void PopulateGridWithServers()
{
this.Enabled = false;
this.splash = Splash.ShowSplash();
this.splash.Status = "Поиск SQL серверов в локальной сети.";
var tmpList = GetSqlServersFromNetwork();
this.splash.Status = "Уточнение информации об инстансах.";
GetSqlStatus(tmpList);
foreach (SqlInstance srv in tmpList)
{
if (this.SqlServers.Where(x => x.Host.FQDN == srv.Host.FQDN).Any(y => y.ServiceName == srv.ServiceName))
{
SqlInstance Element =
this.SqlServers.Where(x => x.Host.FQDN == srv.Host.FQDN).First(y => y.ServiceName == srv.ServiceName);
string Description = Element.Description;
this.SqlServers.Remove(Element);
srv.Description = Description;
Element.Info = Description;
}
this.SqlServers.Add(srv);
}
var tmpList1 = LoadFromFile();
if (tmpList1 != null && tmpList1.Any())
{
foreach (SqlInstance instance in this.SqlServers)
{
SqlInstance tmp =
tmpList.Where(x => x.Host.FQDN == instance.Host.FQDN)
.FirstOrDefault(y => y.ServiceName == instance.ServiceName);
if (tmp != null)
{
instance.Description = tmp.Description;
}
}
}
this.splash.Status = "Инициализация формы.";
RefreshGrid();
this.splash.CloseSplash();
this.Enabled = true;
Show();
Focus();
Activate();
}
public InstanceDetail(SqlInstance Instance, Splash splash)
{
this.splash = splash;
InitializeComponent();
this.Instance = Instance;
this.Instance.Databases = new List <Database>();
this._selectedDatabases = new List <Database>();
this.gridControlDatabase.DataSource = this.Instance.Databases;
this.Instance = Instance;
this.Connection = new SqlConnection(this.Instance.ConnectionString);
this.splash.Status = "Открываем подключение.";
this.Connection.Open();
}
private void StopService(SqlInstance s)
{
if (s.Host.FQDN == null)
{
return;
}
Splash splash = Splash.ShowSplash($"Остановка сервиса {s.ServiceName} на компьютере {s.Host.FQDN}");
splash.Status = "Подключаемся к компьютеру";
ServiceController sc = new ServiceController(s.ServiceName, s.Host.FQDN);
splash.Status = "Посылаем команду на остановку";
sc.Stop();
sc.WaitForStatus(ServiceControllerStatus.Stopped);
s.ServiceStatus = sc.Status;
splash.CloseSplash();
RefreshGrid();
}
private void StartService(SqlInstance s)
{
if (s.Host.FQDN == null)
{
return;
}
Splash splash = Splash.ShowSplash($"Запуск сервиса {s.ServiceName} на компьютере {s.Host.FQDN}");
splash.Status = "Подключаемся к компьютеру";
ServiceController sc = new ServiceController(s.ServiceName, s.Host.FQDN);
splash.Status = "Посылаем команду на запуск";
sc.Start();
sc.WaitForStatus(ServiceControllerStatus.Running);
s.ServiceStatus = sc.Status;
GetinstanceDetails(s);
RefreshGrid();
splash.CloseSplash();
}
private void contextMenuStrip1_Opening(object sender, CancelEventArgs e)
{
this.SelectedInstance = (SqlInstance)this.gridView1.GetRow(this.gridView1.FocusedRowHandle);
if (this.SelectedInstance == null)
{
this.contextMenuStrip1.Hide();
}
if (this.SelectedInstance?.ServiceStatus == ServiceControllerStatus.Running)
{
this.toolStripMenuItemStart.Enabled = false;
this.toolStripMenuItemStop.Enabled = true;
}
else if (this.SelectedInstance?.ServiceStatus == ServiceControllerStatus.Stopped)
{
this.toolStripMenuItemStart.Enabled = true;
this.toolStripMenuItemStop.Enabled = false;
}
else
{
this.toolStripMenuItemStart.Enabled = false;
this.toolStripMenuItemStop.Enabled = false;
}
}
private SqlInstance GetinstanceDetails(SqlInstance instance)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder {
DataSource = instance.InstanceName == "DEFAULT"
? $"{instance.Host.FQDN}"
: $"{instance.Host.FQDN}\\{instance.InstanceName}",
IntegratedSecurity = true,
ConnectTimeout = 2,
ConnectRetryCount = 10,
ConnectRetryInterval = 2,
MultipleActiveResultSets = true
};
instance.ConnectionString = builder.ConnectionString;
instance.DatabasesSize = 0;
if (instance.ServiceStatus == ServiceControllerStatus.Running)
{
using (SqlConnection SqlConnection = new SqlConnection(instance.ConnectionString)) {
try {
SqlConnection.Open();
DataTable dtm = GetInstanceMemory(SqlConnection);
if (GetInstanceDiskUsage(SqlConnection, out DataTable dtd))
{
foreach (DataRow row in dtd.Rows)
{
instance.DatabasesSize += int.Parse(row.ItemArray[1].ToString());
}
}
instance.ServerMemoryMax = int.Parse(dtm.Rows[0].ItemArray[1].ToString());
instance.ServerMemoryRunning = int.Parse(dtm.Rows[0].ItemArray[2].ToString());
instance.AbleToConnect = true;
}
catch (SqlException sqlException) {
instance.AbleToConnect = false;
switch (sqlException.Number)
{
case 18456:
instance.Info = $"Ошибка авторизации: {sqlException.Message}";
break;
case -1:
instance.Info = $"Ошибка подключения: {sqlException.Message}";
break;
default:
Debug.WriteLine(sqlException.Message);
MessageBox.Show(sqlException.Message);
break;
}
}
catch (Exception) {
instance.AbleToConnect = false;
throw;
}
}
}
else
{
instance.ServerMemoryMax = 0;
instance.ServerMemoryRunning = 0;
}
return(instance);
}
