Get a list of the database depends on the selected server

Question

I am using C # with framework 4.0 and SQL Server 2008 R2. I have listed SQL Server 2008 using this code:

public static string[] GetSQLServerList() { SqlDataSourceEnumerator dse = SqlDataSourceEnumerator.Instance; DataTable dt = dse.GetDataSources(); if (dt.Rows.Count == 0) { return null; } string[] SQLServers = new string[dt.Rows.Count]; int f = -1; foreach (DataRow r in dt.Rows) { string SQLServer = r["ServerName"].ToString(); string Instance = r["InstanceName"].ToString(); if (Instance != null && !string.IsNullOrEmpty(Instance)) { SQLServer += "\\" + Instance; } SQLServers[System.Math.Max(System.Threading.Interlocked.Increment(ref f), f - 1)] = SQLServer; } Array.Sort(SQLServers); return SQLServers; } 

i listed my server in ComboBox.

How can I list the database depends on which server I chose in ComboBox?

I found this tutorial , but it needs sqlconnection , how can I connect when I did not select a server?

Answer 1

Here's how you get a list of server names on the network:

 List<String> ServerNames = new List<String>(); SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance; DataTable serversTable = servers.GetDataSources(); foreach (DataRow row in serversTable.Rows) { string serverName = row[0].ToString(); try { if (row[1].ToString() != "") { serverName += "\\" + row[1].ToString(); } } catch { } ServerNames.Add(serverName); } 

Get the list of databases from the selected server:

 List<String> databases = new List<String>(); SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder(); connection.DataSource = SelectedServer; // enter credentials if you want //connection.UserID = //get username; // connection.Password = //get password; connection.IntegratedSecurity = true; String strConn = connection.ToString(); //create connection SqlConnection sqlConn = new SqlConnection(strConn); //open connection sqlConn.Open(); //get databases DataTable tblDatabases = sqlConn.GetSchema("Databases"); //close connection sqlConn.Close(); //add to list foreach (DataRow row in tblDatabases.Rows) { String strDatabaseName = row["database_name"].ToString(); databases.Add(strDatabaseName); } 

Answer 2

  using (var connection = new System.Data.SqlClient.SqlConnection("ConnectionString")) { connection.Open(); var command = new System.Data.SqlClient.SqlCommand(); command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "SELECT name FROM master.sys.databases"; var adapter = new System.Data.SqlClient.SqlDataAdapter(command); var dataset = new DataSet(); adapter.Fill(dataset); DataTable dtDatabases = dataset.Tables[0]; }