C# connect to database and list the databases [duplicate]

Question

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.

Ideas?

Colleen

Answer 1

You can use 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.

Stuffed

Of course sqlConnection.Open(); should be wrapped in a try{} catch(Exception e){} in case of a bad login/timeout etc. – Kingcraft

@Dannybecket: you should not use an empty catch to handle connection problems. Then its better to let the exception bubble to a global handler. Since it was not part of this question, i have not shown it. – Stuffed

An empty catch wasn't the idea, I was just pointing out that you should check for Exceptions. – Kingcraft

Answer 2

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

Leeanneleeboard

Or connect directly to the master database and query it via code - assuming the SQL Login in the connection string has sufficient privileges to access master. – Profanity

Answer 3

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.

Hedwig

Of course sqlConnection.Open(); should be wrapped in a try{} catch(Exception e){} in case of a bad login/timeout etc. – Kingcraft

You don't need to close the connection manually, since you wrapped it in a using statement. – Petiole

Answer 4

You can use SMO - SQL Server Management Objects.

This is two sample code in code project:

SQL Server Authentication using SMO

Databases using SMO

Teratology

Answer 5

The most up to date list of databases will be in the database itself. Why not connect to tempdb as a default database (since you have to connect to something) to start with and then query 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.

e.g. 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.

Ilona

Answer 6

You can try with

select * from master.sys.databases

Ermina