Get list of database depends on chosen server
16,576
Solution 1
This is 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);
}
To Get a List of databases from 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);
}
Solution 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];
}
Author by
user609511
Updated on June 12, 2022Comments
-
user609511 almost 2 years
I'm using C# with framework 4.0 and SQL server 2008 R2. I have listed the SQL server 2008 with 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 on ComboBox.
How can I list the database, depends on which server that I choose in ComboBox?
I found this tutorial, but it needs
sqlconnection
, how can I connect when I have not chosen the server ?