如何使用c#.net从组合框中的sql server获取所有数据库的列表

How to get list of all database from sql server in a combobox using c#.net

提问by Ajit Nair

I am entering the source name userid and password through the textbox and want the database list should be listed on the combo box so that all the four options sourcename, userid, password and databasename can be selected by the user to perform the connectivity

我正在通过文本框输入源名称用户 ID 和密码，并希望数据库列表应列在组合框中，以便用户可以选择所有四个选项源名称、用户 ID、密码和数据库名称来执行连接

The databases are to be retrieve from other system as per the user. User will enter the IP, userid and password and they should get the database list in the combo box so that they can select the required database and perform the connectivity

数据库将根据用户从其他系统中检索。用户将输入IP，用户ID和密码，他们应该在组合框中获得数据库列表，以便他们可以选择所需的数据库并执行连接

private void frmConfig_Load(object sender, EventArgs e)
{
        try
        {
            string Conn = "server=servername;User Id=userid;" + "pwd=******;";
            con = new SqlConnection(Conn);
            con.Open();

            da = new SqlDataAdapter("SELECT * FROM sys.database", con);
            cbSrc.Items.Add(da);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

I am trying to do this but it is not generating any data

我正在尝试这样做，但它没有生成任何数据

采纳答案by Chris Gessler

sys.databases

sys.databases

SELECT name
FROM sys.databases;

Edit:

编辑：

I recommend using IDataReader, returning a List and caching the results. You can simply bind your drop down to the results and retrieve the same list from cache when needed.

我建议使用 IDataReader，返回一个列表并缓存结果。您可以简单地将下拉列表绑定到结果，并在需要时从缓存中检索相同的列表。

public List<string> GetDatabaseList()
{
    List<string> list = new List<string>();

    // Open connection to the database
    string conString = "server=xeon;uid=sa;pwd=manager; database=northwind";

    using (SqlConnection con = new SqlConnection(conString))
    {
        con.Open();

        // Set up a command with the given query and associate
        // this with the current connection.
        using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
        {
            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    list.Add(dr[0].ToString());
                }
            }
        }
    }
    return list;

}

回答by Serge Bollaerts

you can use on of the following queries:

您可以使用以下查询之一：

• EXEC sp_databases
• SELECT * FROM sys.databases

• EXEC sp_databases
• SELECT * FROM sys.databases

Serge

哔叽

回答by Amir

First add following assemblies:

首先添加以下程序集：

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.Smo.dll

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• 微软.SqlServer.Smo.dll

from

从

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

and then use below code:

然后使用以下代码：

var server = new Microsoft.SqlServer.Management.Smo.Server("Server name");

foreach (Database db in server.Databases) {
    cboDBs.Items.Add(db.Name);
}

回答by nznoor

    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];
    }

回答by Minh Nguyen

Simply using GetSchemamethod:

简单的使用GetSchema方法：

using (SqlConnection connection = GetConnection())
{
    connection.Open();
    DataTable dtDatabases = connection.GetSchema("databases");

    //Get database name using dtDatabases["database_name"]
}

回答by Ambrish kumar Mishra

   How to get list of all database from sql server in a combobox using c# asp.net windows application  
     try
        {
            string Conn = "server=.;User Id=sa;" + "pwd=passs;";
            SqlConnection con = new SqlConnection(Conn);
            con.Open();

            SqlCommand cmd = new SqlCommand();
         //   da = new SqlDataAdapter("SELECT * FROM sys.database", con);
            cmd = new SqlCommand("SELECT name FROM sys.databases", con);
           // comboBox1.Items.Add(cmd);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    //comboBox2.Items.Add(dr[0]);
                    comboBox1.Items.Add(dr[0]);
                }
            }

           // .Items.Add(da);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }