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

Question

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

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

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

What are you really stuck on? Getting the list of databases? or, figuring out how to use a combo box? (If it is both, then you might be better splitting this into two questions) — Colin Mackay
– Colin Mackay, Commented Dec 4, 2012 at 12:46
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 — Ajit Nair
– Ajit Nair, Commented Dec 4, 2012 at 12:56

Chris Gessler · Accepted Answer · 2012-12-04 13:35:06Z

44

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.

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;

}

edited Dec 4, 2012 at 13:35

answered Dec 4, 2012 at 12:50

Chris Gessler

23.2k8 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ajit Nair Over a year ago

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

Ajit Nair Over a year ago

It was not the exact answer but i got the answer with the help of ur example thanks chris

Naveen Kumar Over a year ago

how can we get the same list using EF?

Chris Gessler Over a year ago

@NaveenKumar Try this out

Amir · Accepted Answer · 2015-07-10 22:13:58Z

12

First add following assemblies:

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

from

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

edited Jul 10, 2015 at 22:13

answered Dec 4, 2012 at 13:01

Amir

7241 gold badge13 silver badges38 bronze badges

8 Comments

Amir Over a year ago

for more information you can see this question

Mastro Over a year ago

tried this but the min I reference the connectioninfo.dll I get "Cannot find type System.SystemException in module CommonLanguageRuntimeLibrary." build error.

Motaz Over a year ago

why this solution doesn't work in ASP.net MVC project ?

Motaz Over a year ago

@Amir it is the same code as about which is working fine with desktop application but doesn't work in ASP.net MVC project

Amir Over a year ago

@Motaz do you have access to instance Or throw exception?

|

marc_s · Accepted Answer · 2012-12-04 13:24:42Z

3

you can use on of the following queries:

EXEC sp_databases
SELECT * FROM sys.databases

Serge

edited Dec 4, 2012 at 13:24

marc_s

760k185 gold badges1.4k silver badges1.5k bronze badges

answered Dec 4, 2012 at 12:52

Serge Bollaerts

3242 silver badges6 bronze badges

Comments

Minh Nguyen · Accepted Answer · 2018-04-16 08:40:42Z

2

Simply using GetSchema method:

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

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

answered Apr 16, 2018 at 8:40

Minh Nguyen

2,2112 gold badges31 silver badges34 bronze badges

Comments

Md Nazmoon Noor · Accepted Answer · 2014-05-07 13:59:57Z

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

Ambrish kumar Mishra · Accepted Answer · 2020-01-27 08:49:22Z

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

Please consider adding some explanation and details to your answer.

ZygD · Accepted Answer · 2021-09-19 20:09:34Z

0

Try this:

SqlConnection con = new SqlConnection(YourConnectionString);
SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    cbSrc.Items.Add(dr[0].ToString());
}
con.Close();

or this:

DataSet ds = new DataSet();
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT name from sys.databases", YourConnectionString);
sqlda.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    cbSrc.Items.Add(ds.Tables[0].Rows[i][0].ToString());
}

edited Sep 19, 2021 at 20:09

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

answered Sep 19, 2021 at 11:07

mohammad

115 bronze badges

Comments

Ravindra Salunke · Accepted Answer · 2023-02-05 04:12:27Z

   public static List<string> GetAllDatabaseNamesByServerName(string ServerName, [Optional] string UserID, [Optional] string Password)
{

        List<string> lstDatabaseNames = null;

        try
        {
            lstDatabaseNames = new List<string>();
            //string servername = System.Environment.MachineName;
            string newConnString = string.Format("Data Source={0};", ServerName);

            if (UserID == null)
            {
                newConnString += "Integrated Security = True;";
            }
            else
            {
                newConnString += string.Format("User Id ={0}; Password={1};", UserID, Password);
            }
            SqlConnection con = new SqlConnection(newConnString);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT name FROM master.sys.databases", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                lstDatabaseNames.Add(row[0].ToString());
            }
            con.Close();
            return lstDatabaseNames;
        }
        finally
        {
   
        }
    }

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

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

8 Answers 8

4 Comments

8 Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

8 Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Linked

Related