public static ActionResult EnumerateSqlServers(Session session)
{
if (null == session)
{
throw new ArgumentNullException("session");
}
session.Log("EnumerateSQLServers: Begin");
// Check if running with admin rights and if not, log a message to
// let them know why it's failing.
if (false == HasAdminRights())
{
session.Log("EnumerateSQLServers: " + "ATTEMPTING TO RUN WITHOUT ADMIN RIGHTS");
return(ActionResult.Failure);
}
ActionResult result;
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
DataRow[] rows = dt.Select(string.Empty, "IsLocal desc, Name asc");
result = EnumSqlServersIntoComboBox(session, rows);
session.Log("EnumerateSQLServers: End");
return(result);
}
/// <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());
}
/// <summary>
/// Function to list all the SQL Servers available to the user
/// </summary>
/// <remarks></remarks>
private void LoadSQLServers()
{
if (cboServer.Items.Count == 0)
{
cboServer.Items.Add("");
string sqlServerName = "";
DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow row in dtServers.Rows)
{
if (row["Instance"] != null && row["Instance"].ToString().Length > 0)
{
sqlServerName += @"\" + row["Instance"].ToString();
}
else
{
sqlServerName = row["Server"].ToString();
}
cboServer.Items.Add(sqlServerName.ToUpper());
}
if (cboServer.Items.Count == 0)
{
cboServer.Text = "<No available SQL Servers>";
}
}
}
private void txtLServer_DropDown(object sender, EventArgs e)
{
DataTable sqls = SmoApplication.EnumAvailableSqlServers(true);
txtLServer.ValueMember = "Name";
txtLServer.DataSource = sqls;
}
private void _ServerName_ComboBox_DropDown(object sender, EventArgs e)
{
if (_ServerName_ComboBox.Items.Count == 0)
{
DataTable dt;
dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
_ServerName_ComboBox.Items.Add(dr["Name"]);
}
// Set local server as default
_ServerName_ComboBox.SelectedIndex = _ServerName_ComboBox.FindStringExact(System.Environment.MachineName);
if (_ServerName_ComboBox.SelectedIndex < 0)
{
_ServerName_ComboBox.SelectedIndex = 0;
}
}
else
{
MessageBox.Show("sql server was not found!");
}
}
}
private void GetServerList()
{
DataTable dt;
// Set local server as default
dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
// Load server names into combo box
foreach (DataRow dr in dt.Rows)
{
ServerNamesComboBox.Items.Add(dr["Name"]);
}
// Default to this machine
ServerNamesComboBox.SelectedIndex
= ServerNamesComboBox.FindStringExact(
System.Environment.MachineName);
// If this machine is not a SQL server
// then select the first server in the list
if (ServerNamesComboBox.SelectedIndex < 0)
{
ServerNamesComboBox.SelectedIndex = 0;
}
}
else
{
Logger.LogError("", new Exception("No servers found"));
}
}
public async Task <bool> GetDefaultInstanceAsync()
{
var serverInstanceTable = new DataTable();
try
{
await Task.Run(() => { serverInstanceTable = SmoApplication.EnumAvailableSqlServers(true); });
if (serverInstanceTable != null)
{
databaseServer = serverInstanceTable.Rows[0].Field <string>("name");
mFoundServerInstanceName = true;
}
else
{
mFoundServerInstanceName = false;
}
return(true);
}
catch (Exception ex)
{
mExceptionMessage = ex.Message;
HasException = true;
return(false);
}
}
private void btnRefresh_Click(object sender, EventArgs e)
{
DialogResult result = MsgboxUtil.YesNo("Tìm kiếm các máy chủ trong mạng LAN có thể mất nhiều thời gian."
+ "\nBạn có thể gõ Tên máy chủ hoặc IP của máy chủ trong mạng LAN."
+ "\nBạn có chắc chắn muốn tìm các máy chủ hoạt động trong mạng LAN?");
if (result == DialogResult.Yes)
{
Cursor = Cursors.WaitCursor;
//tìm tất cả các server sql có trong mạng
DataTable mdtServers = SmoApplication.EnumAvailableSqlServers(false);
DataRow dr;
foreach (DataRow item in mdtServers.Rows)
{
if (!CheckExistsServer(_dtServers, item))
{
dr = _dtServers.NewRow();
dr["Name"] = item["Name"];
_dtServers.Rows.Add(dr);
}
}
cmbServerName.DataSource = _dtServers;
cmbServerName.DisplayMember = "Name";
cmbServerName.ValueMember = "Name";
Cursor = Cursors.Default;
}
}
public List <Server> getList(bool localOnly)
{
List <Server> result = new List <Server>();
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
DataTable t = SmoApplication.EnumAvailableSqlServers(localOnly);
#region Alternativa
//DataTable table = instance.GetDataSources();
//foreach (DataRow dr in table.Rows)
//{
// string Nombre = dr.Field<string>("ServerName");
// string Instancia = dr.Field<string>("InstanceName");
// if (!string.IsNullOrEmpty(Instancia))
// result.Add(string.Format("{0}\\{1}", Nombre, Instancia));
// else
// result.Add(Nombre);
//}
#endregion
foreach (DataRow dr in t.Rows)
{
Server serv = new Server();
serv.PCName = dr.Field <string>("Name");
serv.ServerName = dr.Field <string>("Server");
serv.Instance = dr.Field <string>("Instance");
serv.isLocal = dr.Field <bool>("isLocal");
result.Add(serv);
}
return(result);
}
private void KETNOISQL_Load(object sender, EventArgs e)
{
txttencsdl.DropDownStyle = ComboBoxStyle.DropDownList;
txttensv.DropDownStyle = ComboBoxStyle.DropDownList;
dt = SmoApplication.EnumAvailableSqlServers(true);// lấy tên server và tên máy
txttencsdl.Enabled = false;
}
private void GetServerList()
{
DataTable dt;
// Set local server as default
dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
// Load server names into combo box
foreach (DataRow dr in dt.Rows)
{
ServerNamesComboBox.Items.Add(dr["Name"]);
}
// Default to this machine
ServerNamesComboBox.SelectedIndex = ServerNamesComboBox.FindStringExact(System.Environment.MachineName);
// If this machine is not a SQL server
// then select the first server in the list
if (ServerNamesComboBox.SelectedIndex < 0)
{
ServerNamesComboBox.SelectedIndex = 0;
}
}
else
{
this.Cursor = Cursors.Default;
MessageBox.Show(Properties.Resources.NoSqlServers, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public Configuracoes carregaServer(Configuracoes config)
{
#region Declaração
DataTable dtServers = null;
#endregion
#region Implementação
dtServers = new DataTable();
dtServers = SmoApplication.EnumAvailableSqlServers(true);
//Instancia uma nova Lista
config.Servidores = new List <string>();
if (dtServers.Rows.Count > 0)
{
foreach (DataRow drServer in dtServers.Rows)
{
config.Servidores.Add(drServer["Name"].ToString());
}
}
return(config);
#endregion
}
public IEnumerable <SqlServerDbo> EnumerateSqlServers(bool localOnly, string namePattern)
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
DataTable dtSmo = SmoApplication.EnumAvailableSqlServers(localOnly);
var rzlt = new List <SqlServerDbo>();
SqlServerDbo dbo;
foreach (DataRow dr in dtSmo.Rows)
{
dbo = new SqlServerDbo()
{
Name = dr.Field <string>("Name"),
Server = dr.Field <string>("Server"),
Instance = dr.Field <string>("Instance"),
IsClustered = dr.Field <bool>("IsClustered"),
Version = dr.Field <string>("Version"),
IsLocal = dr.Field <bool>("IsLocal")
};
if (String.IsNullOrEmpty(namePattern) || dbo.Name.ToUpper().Contains(namePattern.ToUpper()))
{
rzlt.Add(dbo);
}
}
trScope.Complete();
return(rzlt);
}
}
private void FillListOfSQLServer()
{
if (SQLServerText.Items.Count != 0)
{
return;
}
try
{
DataTable servers = SmoApplication.EnumAvailableSqlServers(false);
SQLServerText.Items.Clear();
List <string> serverList = new List <string>();
foreach (DataRow serverInfo in servers.Rows)
{
serverList.Add(serverInfo["Name"].ToString());
}
serverList.Sort();
foreach (string serverName in serverList)
{
SQLServerText.Items.Add(serverName);
}
}
catch (Exception e)
{
//Sorry guys we can't enumerate SQL servers
//TODO: Write something else here
}
}
private void _buttonRefresh_Click(object sender, EventArgs e)
{
_buttonRefresh.Enabled = false;
this.Cursor = Cursors.WaitCursor;
try
{
cmbServer.Items.Clear();
// Create a DataTable where we enumerate the available servers
DataTable dtServers = SmoApplication.EnumAvailableSqlServers(true);
// If there are any servers at all
if (dtServers.Rows.Count > 0)
{
// Loop through each server in the DataTable
foreach (DataRow drServer in dtServers.Rows)
{
// Add the name to the combobox
cmbServer.Items.Add(drServer["Name"]);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "发现数据库服务器失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
this.Cursor = Cursors.Default;
_buttonRefresh.Enabled = true;
}
}
public async Task FindServersAsync()
{
var defaultItem = new ComboBoxItem {
Content = "localhost"
};
try
{
var results = await Task.Factory.StartNew(() =>
{
var dataTable = SmoApplication.EnumAvailableSqlServers(false);
return(dataTable.Rows.Cast <DataRow>().Select(row => (string)row["Name"]).ToList());
});
var serverList = results.Select(name =>
new ComboBoxItem
{
Content = name,
Foreground = name == Environment.MachineName ? Brushes.SteelBlue : Brushes.Black
})
.ToList();
serverList.Insert(0, defaultItem);
ServersList = serverList;
}
catch
{
ServersList = new List <ComboBoxItem> {
defaultItem
};
}
}
void btnRefreshMSSQLServerList_Click(object sender, RoutedEventArgs e)
{
btnSaveDatabaseSettings.IsEnabled = false;
cboDatabaseType.IsEnabled = false;
btnRefreshMSSQLServerList.IsEnabled = false;
try
{
Cursor = Cursors.Wait;
cboMSSQLServerList.Items.Clear();
DataTable dt = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dr in dt.Rows)
{
cboMSSQLServerList.Items.Add(dr[0]);
}
}
catch (Exception ex)
{
logger.Error(ex, ex.ToString());
MessageBox.Show(ex.Message, Commons.Properties.Resources.Error, MessageBoxButton.OK,
MessageBoxImage.Error);
}
Cursor = Cursors.Arrow;
btnSaveDatabaseSettings.IsEnabled = true;
cboDatabaseType.IsEnabled = true;
btnRefreshMSSQLServerList.IsEnabled = true;
}
private void GetServerList()
{
DataTable dt;
// Set local server as default
dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
// Load server names into combo box
foreach (DataRow dr in dt.Rows)
{
ServerNamesComboBox.Items.Add(dr["Name"]);
}
// Default to this machine
ServerNamesComboBox.SelectedIndex
= ServerNamesComboBox.FindStringExact(
System.Environment.MachineName);
// If this machine is not a SQL server
// then select the first server in the list
if (ServerNamesComboBox.SelectedIndex < 0)
{
ServerNamesComboBox.SelectedIndex = 0;
}
}
else
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.NoSqlServers;
emb.Show(this);
}
}
private void btnRefresh_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Tìm kiếm các máy chủ trong mạng LAN có thể mất nhiều thời gian."
+ "\nBạn có thể gõ Tên máy chủ hoặc IP của máy chủ trong mạng LAN."
+ "\nBạn có chắc chắn muốn tìm các máy chủ hoạt động trong mạng LAN?",
"Network finding...",
MessageBoxButtons.YesNo, MessageBoxIcon.Question)
== DialogResult.Yes)
{
Cursor = Cursors.WaitCursor;
DataTable mdtServers = SmoApplication.EnumAvailableSqlServers(false);
DataRow dr;
foreach (DataRow item in mdtServers.Rows)
{
if (!CheckExistsServer(_dtServers, item))
{
dr = _dtServers.NewRow();
dr["Name"] = item["Name"];
_dtServers.Rows.Add(dr);
}
}
cmbServerName.DataSource = _dtServers;
cmbServerName.DisplayMember = "Name";
cmbServerName.ValueMember = "Name";
Cursor = Cursors.Default;
}
}
private string[] ListLocalSqlInstances()
{
DataTable table = null;
table = SmoApplication.EnumAvailableSqlServers();
try
{
table = SmoApplication.EnumAvailableSqlServers();
}
catch (Exception e)
{
MessageBox.Show("ListLocalSqlInstances: " + e.Message);
return(null);
}
string [] instances = new string[table.Rows.Count];
int cont = 0;
foreach (DataRow row in table.Rows)
{
instances[cont] = row["Name"].ToString();
cont++;
}
return(instances);
}
private void PickServerForm_Shown(object sender, EventArgs e)
{
try
{
listBox.Items.Add("Loading server list...");
listBox.Enabled = false;
listBox.Refresh();
this.Cursor = Cursors.WaitCursor;
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
listBox.Items.Clear();
foreach (DataRow r in dt.Rows)
{
string name = r["Name"].ToString();
listBox.Items.Add(name);
}
}
finally
{
this.Cursor = Cursors.Default;
listBox.Enabled = true;
}
}
private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
if (IsRunningInAzure) //SmoApplication.EnumAvailableSqlServers does not work on Azure
{
e.Result = new string[0]; // Just go with the empty list
}
else
{
try
{
DataTable tbl = SmoApplication.EnumAvailableSqlServers(false);
string[] servers = new string[tbl.Rows.Count];
for (int i = 0; i < tbl.Rows.Count; i++)
{
servers[i] = tbl.Rows[i][0].ToString();
}
e.Result = servers;
}
catch
{
// MessageBox.Show("Unable to get server list."); //\r\n" + exe.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
e.Result = null;
}
}
}
private void CreateDB_Load(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Going to look for all SQL Servers in the Network this might take some time.\n Do you want to proceed...", "SB Payroll", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
DataTable dt = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dr in dt.Rows)
{
this.cboServers.Items.Add(dr[0]);
}
}
else
{
this.Close();
}
StreamReader sr = new StreamReader("Data/SBPayrollDB.sql");
dbstring = sr.ReadToEnd();
sr.Close();
richTextScript.Text = dbstring;
}
catch (Exception ex)
{
Utils.LogEventViewer(ex);
Debug.WriteLine(ex.ToString());
this.txtProgressStatus.Text = ex.Message;
this.txtProgressStatus.ScrollToCaret();
}
}
private void btnRefreshServerList_Click(object sender, System.EventArgs e)
{
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
try
{
this.dtList = SmoApplication.EnumAvailableSqlServers(false);
this.cbServerList.Items.Clear();
foreach (System.Data.DataRow dataRow in this.dtList.Rows)
{
string text = dataRow["Server"].ToString();
if (dataRow["Instance"] != null && dataRow["Instance"].ToString().Length > 0)
{
text = text + "\\" + dataRow["Instance"].ToString();
}
if (this.cbServerList.Items.IndexOf(text) < 0)
{
this.cbServerList.Items.Add(text);
}
}
}
finally
{
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
public List <LocalServer> LocalServers() =>
SmoApplication.EnumAvailableSqlServers(true).AsEnumerable()
.Select(row => new LocalServer()
{
Name = row.Field <string>("Name"),
Instance = row.Field <string>("Instance"),
ServerName = row.Field <string>("Server")
}).ToList();
private void InitServerCombo()
{
DataTable dataTable = SmoApplication.EnumAvailableSqlServers(false);
this.cboServers.ValueMember = "Name";
this.cboServers.DataSource = dataTable;
this.areServersLoaded = true;
}
private static List <string> GetLocalSqlServerInstancesByCallingSqlBrowser()
{
DataTable dt = SmoApplication.EnumAvailableSqlServers(true);
return(dt.Rows.Cast <DataRow>()
.Select(v => v.Field <string>("Name"))
.ToList());
}
static void Main()
{
string a = "00";
string b = a.TrimStart('0');
int ca = b.Length;
try
{
var ApplicationOrDeviceType = "TasmusNetasMoltu";
var ApplicationRootName = @"http://localhost/" + "Moltu" + "/Moltu/";
var ApplicationInitPage = "Home/Index";
SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();
connection.DataSource = @"ICTSIMOS\ICT";
connection.UserID = "sa";
connection.Password = "******";
connection.InitialCatalog = "Siskon";
connection.IntegratedSecurity = false;
String strConn = connection.ToString();
SqlConnection sqlConn = new SqlConnection(strConn);
String sql = @"INSERT INTO ApplicationRef (ApplicationOrDeviceType,ApplicationRootName,ApplicationInitPage) VALUES('" + ApplicationOrDeviceType + "','" + ApplicationRootName + "','" + ApplicationInitPage + "')";
sqlConn.Open();
SqlCommand cmd = new SqlCommand(sql, sqlConn);
cmd.ExecuteNonQuery();
sqlConn.Close();
}
catch (Exception ex)
{
}
//ExecuteCommand("dism /online /enable-feature /featurename:WCF-HTTP-Activation45");
//var serviceList = ServiceController.GetServices().ToList().OrderBy(o => o.ServiceName);
//for (int i = 0; i < serviceList.Count(); i++)
//{
// Console.WriteLine(i.ToString() + "- ServiceName : " + serviceList.ElementAt(i).ServiceName + " ~~ " + "DisplayName : " + serviceList.ElementAt(i).DisplayName + " ~~ " + "MachineName : " + serviceList.ElementAt(i).MachineName);
//}
//Console.ReadLine();
//ServiceController service = ServiceController.GetServices().FirstOrDefault(f => f.ServiceName == "NetMsmqActivator");
// string a = GetBatOutput();
DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
DBSelector dBSelector = new DBSelector();
Application.EnableVisualStyles();
dBSelector.Visible = true;
Application.Run(dBSelector);
}
private List <SqlServerInstance> GetServerInstances(IList <string> serverNames)
{
var table = InstanceDataTable.Copy();
var list = GetFromDataTable(table, "ServerName", "InstanceName", "Version");
var smoList = GetFromDataTable(SmoApplication.EnumAvailableSqlServers(), "Server", "Instance", "Version");
var res = list.Concat(smoList).Where(r => serverNames == null || serverNames.Contains(r.ServerName)).Concat(GetServerInstancesMock()).ToList();
return(res.Distinct(SqlServerInstanceComparer.Instance).ToList());
}
/// <summary>
/// Get all available Sql servers, may take several seconds
/// to return which is why it' awaited.
/// </summary>
/// <returns></returns>
public async Task <DataTable> AvailableServersAsync()
{
var serverTable = await Task.Run(() =>
{
return(SmoApplication.EnumAvailableSqlServers(false));
});
return(serverTable);
}
