C#连接到数据库并列出数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12862604/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# connect to database and list the databases
提问by user1158745
Possible Duplicate:
SQL Server query to find all current database names
I am trying to figure out how to list the databases after connecting to the servers without specifying a database first.
我试图弄清楚如何在连接到服务器后列出数据库而不先指定数据库。
sqlConnection1 = new SqlConnection("Server=" + sqlServer + ";Database=" + database +
";User ID=" + userName + ";Password=" + password + ";Trusted_Connection=False;");
So basically what i want is the end user to connect to the sql server, then have a drop down list populated with the list of db's they can connect and query.
所以基本上我想要的是最终用户连接到 sql server，然后有一个下拉列表填充他们可以连接和查询的数据库列表。
Ideas?
想法？
采纳答案by Tim Schmelter
You can use SqlConnection.GetSchema:
您可以使用SqlConnection.GetSchema：
using(var con = new SqlConnection("Data Source=Yourserver; Integrated Security=True;"))
{
con.Open();
DataTable databases = con.GetSchema("Databases");
foreach (DataRow database in databases.Rows)
{
String databaseName = database.Field<String>("database_name");
short dbID = database.Field<short>("dbid");
DateTime creationDate = database.Field<DateTime>("create_date");
}
}
SQL Server Schema Collections (ADO.NET)
To determine the list of supported schema collections, call the GetSchema method with no arguments, or with the schema collection name "MetaDataCollections". This will return a DataTable with a list of the supported schema collections, the number of restrictions that they each support, and the number of identifier parts that they use.
要确定支持的架构集合列表，请不带参数或使用架构集合名称“MetaDataCollections”调用 GetSchema 方法。这将返回一个 DataTable，其中包含受支持的架构集合的列表、它们每个支持的限制数量以及它们使用的标识符部分的数量。
回答by tranceporter
You can write a stored proc which can return you a list of databases on that server.
您可以编写一个存储过程，它可以返回该服务器上的数据库列表。
SELECT name
FROM master.sys.databases
or
或者
EXEC sp_databases
回答by Code Magician
The most up to date list of databases will be in the database itself. Why not connect to tempdbas a default database (since you have to connect to something) to start with and then query from master.sys.databases.
最新的数据库列表将在数据库本身中。为什么不tempdb作为默认数据库连接（因为你必须连接到某些东西）来开始，然后从 master.sys.databases 查询。
select [name] from master.sys.databases
select [name] from master.sys.databases
Then you can update your connection string with whatever database is necessary or simply change the db using the ChangeDatabase()method.
然后您可以使用任何必要的数据库更新您的连接字符串，或者只需使用该ChangeDatabase()方法更改数据库。
e.g. connection.ChangeDatabase(selectedDB);
例如 connection.ChangeDatabase(selectedDB);
You could also connect to master, but I like to keep default connections in tempdb as occasionally people forget to change databases before creating objects. I would rather the junk go into tempdb than master since tempdb is recreated when SQL restarts.
您也可以连接到 master，但我喜欢在 tempdb 中保留默认连接，因为有时人们会在创建对象之前忘记更改数据库。我宁愿垃圾进入 tempdb 而不是 master，因为在 SQL 重新启动时会重新创建 tempdb。
回答by Aghilas Yakoub
You can try with
你可以试试
select * from master.sys.databases
回答by hamid reza mansouri
You can use SMO - SQL Server Management Objects.
您可以使用 SMO- SQL Server 管理对象。
This is two sample code in code project:
这是代码项目中的两个示例代码：
回答by Gromer
This should get you database names:
这应该为您提供数据库名称：
var connectionString = string.Format("Data Source=localhost;User ID={0};Password={1};", userName, password);
DataTable databases = null;
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
databases = sqlConnection.GetSchema("Databases");
sqlConnection.Close();
}
if (databases != null)
{
foreach (DataRow row in databases.Rows)
{
foreach (var item in row.ItemArray)
{
Console.Write("{0} ", item);
}
Console.WriteLine();
}
}
Feel free to exclude all the printing at the end. Toss all that in a console app to see it in action. The table names are in index 0 of row.ItemArray.
最后随意排除所有打印。将所有这些都放到控制台应用程序中以查看它的运行情况。表名位于 的索引 0 中row.ItemArray。
