private void GetServices()
{
Cursor csr = null;
ManagedComputer mc;
ListViewItem ServiceListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Clear control
ServicesListView.Items.Clear();
mc = new ManagedComputer(SqlServerSelection.Name);
foreach (Service svc in mc.Services)
{
ServiceListViewItem = ServicesListView.Items.Add(svc.Name);
ServiceListViewItem.SubItems.Add(svc.DisplayName);
ServiceListViewItem.SubItems.Add(svc.ServiceState.ToString());
ServiceListViewItem.SubItems.Add(svc.StartMode.ToString());
ServiceListViewItem.SubItems.Add(svc.ServiceAccount);
ServiceListViewItem.SubItems.Add(svc.State.ToString());
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
/// <summary>
/// Checks the server available.
/// </summary>
/// <returns>bool</returns>
public static bool IsServerAvailable()
{
try
{
ManagedComputer managedComputer = new ManagedComputer();
//ManagedComputer managedComputers = new ManagedComputer("latha");
managedComputer.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
//ServerInstanceCollection serverInstances = managedComputers.ServerInstances;
//int count = serverInstances.Count;
foreach (ServerInstance serverInstance in managedComputer.ServerInstances)
{
if (serverInstance.Name.Equals("TERRASCANFIELD"))
{
return(true);
}
////else
////{
//// return false;
////}
}
return(false);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// Finds all availible SQL servers in local network via UDP broadcast, and
/// searching registered servers. This takes ~10s to complete so take care.
/// </summary>
/// <returns></returns>
public static List <string> GetHostnameList()
{
List <string> hostnames = new List <string>();
// Try grab registered SQL instances
try {
ManagedComputer mc = new ManagedComputer();
foreach (ServerInstance instance in mc.ServerInstances)
{
hostnames.Add(instance.Name);
}
} catch {}
// Try grab SQL instances with UDP broadcast
try {
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow dr in dt.Rows)
{
if (string.IsNullOrEmpty(dr["Instance"].ToString()))
{
hostnames.Add(dr["Name"].ToString());
}
else
{
hostnames.Add($"{dr["Name"]}\\{dr["Instance"]}");
}
}
} catch { }
// Remove dupes
return(hostnames.Distinct().ToList());
}
private static List <string> LocalSqlServerInstancesByCallingSqlWmi(ProviderArchitecture providerArchitecture)
{
try
{
ManagedComputer managedComputer32 = new ManagedComputer();
managedComputer32.ConnectionSettings.ProviderArchitecture = providerArchitecture;
const string defaultSqlInstanceName = "MSSQLSERVER";
return(managedComputer32.ServerInstances.Cast <ServerInstance>()
.Select(v =>
(string.IsNullOrEmpty(v.Name) || string.Equals(v.Name, defaultSqlInstanceName, StringComparison.OrdinalIgnoreCase)) ?
v.Parent.Name : string.Format("{0}\\{1}", v.Parent.Name, v.Name))
.OrderBy(v => v, StringComparer.OrdinalIgnoreCase)
.ToList());
}
catch (SmoException ex)
{
Console.WriteLine(ex.Message);
return(new List <string>());
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(new List <string>());
}
}
public bool getSeverConnection()
{
////Start service when it was not running
ManagedComputer managerCmp = new ManagedComputer();
/*
// Service service = default(Service);
foreach (Service service in managerCmp.Services)
{
if (service.ServiceState != ServiceState.Running)
{
if (service.ServiceState == ServiceState.Paused)
{
service.Resume();
}
else
{
service.Start();
}
service.Refresh();
}
}*/
//Get Instance of Sqlsever
RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;
using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
{
RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
if (instanceKey != null)
{
foreach (var instanceName in instanceKey.GetValueNames())
{
//if (instanceName.ToLower() == "SQLEXPRESS".ToLower())
//{
// object temp = Environment.MachineName + @"\" + instanceName;
// cmbServerName.Properties.Items.Add(temp);
//}
//else
//{
object temp = Environment.MachineName;
cmbServerName.Properties.Items.Add(temp);
//}
}
return true;
}
else
{
XtraMessageBox.Show("Microsoft SQL server chưa được cài đặt trên máy của bạn!\nXin vui cài đặt và thử lại!");
return false;
}
//else
//{
// foreach (ServerInstance sever in managerCmp.ServerInstances)
// {
// object temp = managerCmp.Name + @"\" + sever.Name;
// cmbServerName.Properties.Items.Add(temp);
// }
// return true;
//}
}
}
public IEnumerable <string> GetLocalSqlServers()
{
var result = new List <string>();
// Using ManagedComputer because EnumAvailableSqlServers does not work with local firewall (broadcast)
ManagedComputer mc = new ManagedComputer();
_logger.Debug("Determine OS architecture");
mc.ConnectionSettings.ProviderArchitecture = OperatingSystem.IsOs64Bit() ? ProviderArchitecture.Use64bit : ProviderArchitecture.Use32bit;
_logger.Debug("Loop through all instances");
foreach (ServerInstance si in mc.ServerInstances)
{
string name = null;
if (si.Name == "MSSQLSERVER")
{
name = si.Parent.Name;
}
else
{
name = Path.Combine(si.Parent.Name, si.Name);
}
Console.WriteLine("Instance name = {0}", name);
_logger.Debug("Instance name = {0}", name);
result.Add(name);
}
_logger.Debug("Returning result");
return(result);
}
private Service GetAgentService()
{
ManagedComputer computer = new ManagedComputer();
if (computer.Services.Contains(serviceName))
{
return(computer.Services[serviceName]);
}
return(null);
}
public SqlServerInstance(string name, string server, string instance, string version, bool isLocal)
{
Name = name;
Server = server;
Instance = instance;
Version = version;
IsLocal = isLocal;
_managedComputer = new ManagedComputer(server);
_managedComputer.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
}
static int Main(string[] args)
{
//args = Console.ReadLine().Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
ParseParameters(args);
// check parameters and show help
if (args.Length == 0 || (args.Length > 2 && (!lastArgumentWasDelete)))
{
Console.WriteLine(@"Usage: AliasDatabaseServer [ServerInstanceName] AliasName [/delete]");
Console.WriteLine("\nOptions\n /drop will delete the alias corresponding to AliasName");
Console.WriteLine("\n" + @"Example: AliasDatabasServer \\localhost\MYSQLSERVER MyAlias" + "\n");
Console.WriteLine(string.Format(@"If not specified [ServerInstanceName] = '{0}'", DefaultHostname));
Console.ReadKey();
return (int)ErrorValues.Help;
}
else
{
ManagedComputer computer = new ManagedComputer();
if (!string.IsNullOrEmpty(serverInstanceName) && serverInstanceName == "default")
{
if (computer.ServerInstances.Contains("SQLEXPRESS"))
{
serverInstanceName = @"MSSQL\SQLEXPRESS";
}
else if (computer.ServerInstances.Contains("SQLEXPRESS"))
{
serverInstanceName = @"MSSQLSERVER";
}
else
{
serverInstanceName = computer.ServerInstances[0].Name;
}
}
if (lastArgumentWasDelete)
{
DeleteAlias(computer, serverInstanceName, aliasName);
}
else
{
CreateAlias(computer, serverInstanceName, aliasName);
}
return returnValue;
}
}
private void GetServerName()
{
try
{
ManagedComputer mc = new ManagedComputer();
mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
this._serverName = mc.Name + "\\" + mc.ServerInstances[0].Name;
}
catch (Exception)
{
MessageBox.Show("Could not retrieve Serve Name. The Program will Close!!");
this.Close();
}
}
private Service GetSelectedService()
{
// Return the service selected in the list
ManagedComputer mc;
if (ServicesListView.SelectedItems.Count > 0)
{
mc = new ManagedComputer(SqlServerSelection.Name);
return(mc.Services[ServicesListView.SelectedItems[0].Text]);
}
else
{
return(null);
}
}
// Get Server Name Method
internal bool GetServerName()
{
try
{
ManagedComputer ms = new ManagedComputer();
ms.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
this._serverName = ms.Name + "\\" + ms.ServerInstances[0].Name;
return(true);
}
catch (Exception)
{
return(false);
}
}
public static void EnableTCP(this Service service, ManagedComputer mc)
{
var serverProtocol = mc.ServerInstances[0].ServerProtocols["Tcp"];
if (!serverProtocol.IsEnabled)
{
Console.WriteLine("Enabling TCP connections");
serverProtocol.IsEnabled = true;
serverProtocol.Alter();
}
else
{
ConsoleHelper.WriteLine(ConsoleColor.Yellow, "TCP connections already enabled");
}
}
private string getMssqlInstanceName()
{
string mssqlInstanceName = "";
ManagedComputer mc = new ManagedComputer();
if (mc.ServerInstances.Count == 1)
{
mssqlInstanceName = mc.ServerInstances[0].Name;
}
if (mssqlInstanceName == "")
{
mssqlInstanceName = "SQLEXPRESS";
}
return(mssqlInstanceName);
}
private void RestartService()
{
//Declare and create an instance of the ManagedComputer
//object that represents the WMI Provider services.
ManagedComputer mc;
mc = new ManagedComputer();
//Iterate through each service registered with the WMI Provider.
foreach (Service svc in mc.Services)
{
Console.WriteLine(svc.Name);
}
//Reference the Microsoft SQL Server service.
Service Mysvc = mc.Services["MSSQLSERVER"];
//Stop the service if it is running and report on the status
// continuously until it has stopped.
if (Mysvc.ServiceState == ServiceState.Running)
{
Mysvc.Stop();
Console.WriteLine(string.Format("{0} service state is {1}", Mysvc.Name, Mysvc.ServiceState));
while (!(string.Format("{0}", Mysvc.ServiceState) == "Stopped"))
{
Console.WriteLine(string.Format("{0}", Mysvc.ServiceState));
Mysvc.Refresh();
}
Console.WriteLine(string.Format("{0} service state is {1}", Mysvc.Name, Mysvc.ServiceState));
//Start the service and report on the status continuously
//until it has started.
Mysvc.Start();
while (!(string.Format("{0}", Mysvc.ServiceState) == "Running"))
{
Console.WriteLine(string.Format("{0}", Mysvc.ServiceState));
Mysvc.Refresh();
}
Console.WriteLine(string.Format("{0} service state is {1}", Mysvc.Name, Mysvc.ServiceState));
Console.ReadLine();
}
else
{
Console.WriteLine("SQL Server service is not running.");
Console.ReadLine();
}
}
private List <string> LocalServersList()
{
List <string> localServers = new List <string>();
bool bServerBrowserRun = false;
bool bServerRun = false;
try
{
ManagedComputer comp = new ManagedComputer(System.Environment.MachineName.ToString());
foreach (Service service in comp.Services)
{
bServerRun = bServerRun || ((!bServerRun) && service.Name.StartsWith("MSSQL") && (service.ServiceState == ServiceState.Running));
bServerBrowserRun = bServerBrowserRun || ((!bServerBrowserRun) && service.Name.StartsWith("SQLBROWSER") && (service.ServiceState == ServiceState.Running));
}
if (!bServerRun)
{
localServers.Add("Running SQL Server is not found.");
return(localServers);
}
if (bServerBrowserRun)
{
DataTable table = SmoApplication.EnumAvailableSqlServers(true);
foreach (DataRow row in table.Rows)
{
localServers.Add(row["Name"].ToString());
}
}
else
{
foreach (ServerInstance instance in comp.ServerInstances)
{
localServers.Add(comp.Name + ((instance.Name != "MSSQLSERVER") ? (@"\" + instance.Name) : ""));
}
}
}
catch (Exception ex)
{
}
return(localServers);
}
private void ConnectDialog_Load(object sender, EventArgs e)
{
// Get available local instance names.
localServer = new ManagedComputer(Environment.MachineName);
foreach (ServerInstance instance in localServer.ServerInstances)
{
// Display the default server instance as the machine name.
string instanceName = (instance.Name == "MSSQLSERVER" ?
instanceName = Environment.MachineName :
instanceName = Environment.MachineName + "\\" + instance.Name);
// Add the instance to the list.
subscriberComboBox.Items.Add(instanceName);
}
// Set to the first item to load available subscriptions.
subscriberComboBox.SelectedIndex = 0;
UpdateButtons();
}
public static void StartSQLService()
{
//Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
ManagedComputer mc = default(ManagedComputer);
mc = new ManagedComputer();
//Iterate through each service registered with the WMI Provider.
Service svc = default(Service);
foreach (svc in mc.Services)
{
Console.WriteLine(svc.Name);
}
//Reference the Microsoft SQL Server service.
svc = mc.Services("MSSQLSERVER");
//Stop the service if it is running and report on the status continuously until it has stopped.
if (svc.ServiceState == ServiceState.Running)
{
svc.Stop();
Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState));
while (!(string.Format("{0}", svc.ServiceState) == "Stopped"))
{
Console.WriteLine(string.Format("{0}", svc.ServiceState));
svc.Refresh();
}
Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState));
//Start the service and report on the status continuously until it has started.
svc.Start();
while (!(string.Format("{0}", svc.ServiceState) == "Running"))
{
Console.WriteLine(string.Format("{0}", svc.ServiceState));
svc.Refresh();
}
Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState));
}
else
{
Console.WriteLine("SQL Server service is not running.");
}
}
private void DBConnectForm_Load(object sender, EventArgs e)
{
cbxLoginMode.SelectedIndex = 0;
// Get available local instance names.
localServer = new ManagedComputer(Environment.MachineName);
foreach (ServerInstance instance in localServer.ServerInstances)
{
// Display the default server instance as the machine name.
string instanceName = (instance.Name == "MSSQLSERVER" ?
instanceName = Environment.MachineName :
instanceName = Environment.MachineName + "\\" + instance.Name);
// Add the instance to the list.
cbxDatabase.Items.Add(instanceName);
}
// Set to the first item to load available subscriptions.
cbxDatabase.SelectedIndex = 0;
}
private void DBConnectForm_Load(object sender, EventArgs e)
{
cbxLoginMode.SelectedIndex = 0;
// Get available local instance names.
localServer = new ManagedComputer(Environment.MachineName);
foreach (ServerInstance instance in localServer.ServerInstances)
{
// Display the default server instance as the machine name.
string instanceName = (instance.Name == "MSSQLSERVER" ?
instanceName = Environment.MachineName :
instanceName = Environment.MachineName + "\\" + instance.Name);
// Add the instance to the list.
cbxDatabase.Items.Add(instanceName);
}
// Set to the first item to load available subscriptions.
cbxDatabase.SelectedIndex = 0;
}
private static void EnableMixedAuthenticationMode(Server server)
{
Console.WriteLine("Enabling Mixed Authentication Mode");
var mc = new ManagedComputer();
var is64 = Environment.Is64BitOperatingSystem;
if (is64)
{
mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
}
var service = mc.Services[Configuration.SqlServiceName];
service.StartAndWait();
server.Settings.LoginMode = ServerLoginMode.Mixed;
server.Settings.Alter();
service.StopAndWait();
service.EnableTCP(mc);
service.StartAndWait();
}
private Service GetSelectedService()
{
// Return the service selected in the list
ManagedComputer mc;
if (ServicesListView.SelectedItems.Count > 0)
{
mc = new ManagedComputer(SqlServerSelection.Name);
return mc.Services[ServicesListView.SelectedItems[0].Text];
}
else
{
return null;
}
}
private void GetServices()
{
Cursor csr = null;
ManagedComputer mc;
ListViewItem ServiceListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Clear control
ServicesListView.Items.Clear();
mc = new ManagedComputer(SqlServerSelection.Name);
foreach (Service svc in mc.Services)
{
ServiceListViewItem = ServicesListView.Items.Add(svc.Name);
ServiceListViewItem.SubItems.Add(svc.DisplayName);
ServiceListViewItem.SubItems.Add(svc.ServiceState.ToString());
ServiceListViewItem.SubItems.Add(svc.StartMode.ToString());
ServiceListViewItem.SubItems.Add(svc.ServiceAccount);
ServiceListViewItem.SubItems.Add(svc.State.ToString());
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
public static void CreateAlias(ManagedComputer computer, string serverInstance, string aliasName)
{
if (computer.ServerAliases.Contains(aliasName))
{
string aliasExistMessage = string.Format("{0} alias already exists. Not creating the alias.", aliasName);
Console.WriteLine(aliasExistMessage);
returnValue = (int)ErrorValues.Success;
}
else
{
ServerAlias alias = RetrievePreparedServerAlias(computer, serverInstance, aliasName);
try
{
alias.Create();
Console.WriteLine("An alias has been created");
}
catch (FailedOperationException)
{
Console.WriteLine(@"The alias creation failed, please check that you specified
the correct server instance name or that you have enough permissions");
returnValue = (int)ErrorValues.NotCreatedWrongServerOrUser;
}
catch
{
Console.WriteLine("The alias creation failed");
returnValue = (int)ErrorValues.NotCreatedUnknown;
}
}
}
public bool getSeverConnection()
{
////Start service when it was not running
ManagedComputer managerCmp = new ManagedComputer();
/*
* // Service service = default(Service);
* foreach (Service service in managerCmp.Services)
* {
* if (service.ServiceState != ServiceState.Running)
* {
* if (service.ServiceState == ServiceState.Paused)
* {
* service.Resume();
* }
* else
* {
* service.Start();
* }
* service.Refresh();
* }
*
* }*/
//Get Instance of Sqlsever
RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;
using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
{
RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
if (instanceKey != null)
{
foreach (var instanceName in instanceKey.GetValueNames())
{
//if (instanceName.ToLower() == "SQLEXPRESS".ToLower())
//{
// object temp = Environment.MachineName + @"\" + instanceName;
// cmbServerName.Properties.Items.Add(temp);
//}
//else
//{
object temp = Environment.MachineName;
cmbServerName.Properties.Items.Add(temp);
//}
}
return(true);
}
else
{
XtraMessageBox.Show("Microsoft SQL server chưa được cài đặt trên máy của bạn!\nXin vui cài đặt và thử lại!");
return(false);
}
//else
//{
// foreach (ServerInstance sever in managerCmp.ServerInstances)
// {
// object temp = managerCmp.Name + @"\" + sever.Name;
// cmbServerName.Properties.Items.Add(temp);
// }
// return true;
//}
}
}
public static void DeleteAlias(ManagedComputer computer, string serverInstance, string aliasName)
{
ServerAlias alias = RetrievePreparedServerAlias(computer, serverInstance, aliasName);
if (computer.ServerAliases.Contains(aliasName))
{
try
{
alias.Drop();
Console.WriteLine(string.Format("The alias {0} has been deleted", aliasName));
}
catch (FailedOperationException)
{
Console.WriteLine("The alias deletion failed, please check that you specified the correct server instance name");
returnValue = (int)ErrorValues.NotDeletedWrongServerOrUser;
}
catch
{
Console.WriteLine("The alias deletion failed");
returnValue = (int)ErrorValues.NotDeletedUnknown;
}
}
else
{
Console.WriteLine("Alias not found in the database");
}
}
private static ServerAlias RetrievePreparedServerAlias(ManagedComputer computer, string serverInstance, string aliasName)
{
ServerAlias alias = new ServerAlias(computer, aliasName);
alias.ProtocolName = "np";
alias.ServerName = ".";
string instanceName = ParseInstanceNameFromFullName(serverInstance);
if (serverInstance.IndexOf('\\') < 0)
{
alias.ConnectionString = @"sql\query";
}
else
{
alias.ConnectionString = String.Format("MSSQL${0}\\sql\\query", instanceName);
}
return alias;
}
public static (bool, List <string>) IsSqlServiceOk(string connectionstring)
{
if (string.IsNullOrEmpty(connectionstring))
{
throw new ArgumentNullException("connectionstring");
}
try
{
var conn = new SqlConnectionStringBuilder(connectionstring);
if (string.IsNullOrEmpty(conn.DataSource))
{
return(false, new List <string>()
{
"رشته اتصال نامعتبر است"
});
}
var isIp = IPAddress.TryParse(conn.DataSource, out var remoteIp);
if (isIp && conn.DataSource != "127.0.0.1")
{
return(!RemoteServerIsAlive(remoteIp)
? (false, new List <string>()
{
"سیستم میزبان sql server در دسترس نیست"
})
: CheckConnection(connectionstring));
}
var mc = new ManagedComputer();
var servicecoll = mc.Services;
var messages = new List <string>();
var errorInStartService = false;
var haveValisSqlService = false;
foreach (Service serv in servicecoll)
{
if (!serv.Type.Equals(ManagedServiceType.SqlServer))
{
continue;
}
haveValisSqlService = true;
var status = WindowsServiceHelper.GetServiceStatus(serv.Name);
if (status == ServiceState.Unknown)
{
messages.Add($"سرویس {serv.DisplayName} پیدا نشد.");
}
else if (status == ServiceState.Stopped)
{
try
{
WindowsServiceHelper.StartService(serv.Name);
messages.Add($"سرویس {serv.DisplayName} اجرا شد.");
errorInStartService = true;
}
catch (Exception ex)
{
messages.Add($" خطا در اجرای سرویس {serv.DisplayName}.");
messages.Add(ex.Message);
errorInStartService = true;
}
}
else
{
errorInStartService = true;
}
Console.WriteLine(serv.DisplayName);
}
if (!haveValisSqlService)
{
messages.Add("سرویس sql پیدا نشد");
}
return(errorInStartService, messages);
}
catch (Exception ex)
{
WebErrorLog.ErrorInstence.StartErrorLog(ex);
return(false, new List <string>()
{
ex.Message
});
}
}
private void ConnectDialog_Load(object sender, EventArgs e)
{
// Get available local instance names.
localServer = new ManagedComputer(Environment.MachineName);
foreach (ServerInstance instance in localServer.ServerInstances)
{
// Display the default server instance as the machine name.
string instanceName = (instance.Name == "MSSQLSERVER" ?
instanceName = Environment.MachineName :
instanceName = Environment.MachineName + "\\" + instance.Name);
// Add the instance to the list.
subscriberComboBox.Items.Add(instanceName);
}
// Set to the first item to load available subscriptions.
subscriberComboBox.SelectedIndex = 0;
UpdateButtons();
}
