Get List Of Database Name From SQL Server In C#

In real time scenarios, we validate the database connection string in our application. In the meantime, it will throw exceptions. If the connection is not valid at the time, we cannot judge easily whether its connection string is Invalid or our server doesn't contain that respective database. So, in this blog, I will explain how to get the list of database name from SQL Server using C# to help find the root cause of the problem and to notify the exact error to the user or developer. 

 

sys.databases 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace SQLTableListInCSharp {
    class Program {
        static void Main(string[] args) {
            string connectionString = "Data Source=.; Integrated Security=True;";
            using(SqlConnection con = new SqlConnection(connectionString)) {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con)) {
                    using(SqlDataReader dr = cmd.ExecuteReader()) {
                        while (dr.Read()) {
                            Console.WriteLine(dr[0].ToString());
                        }
                    }
                }
            }
        }
    }
}

In the above code, you can see that we can get the list of database names using sys.databases and the result will be as below.

 

 

 

Now, with the help of this list, we can validate the database connection string. Another way we can get the list of database name is by using GetSchema.

 

GetSchema 

using(var con = new SqlConnection(connectionString)) {
    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");
        Console.WriteLine(databaseName + "," + dbID + "," + creationDate);
    }
}

And, the result will be -

 

 

 

Now, you can use any one option to validate the connection string.

 

Complete Code 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace SQLTableListInCSharp {
    class Program {
        static void Main(string[] args) {
            string connectionString = "Data Source=.; Integrated Security=True;";
            using(SqlConnection con = new SqlConnection(connectionString)) {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con)) {
                    using(SqlDataReader dr = cmd.ExecuteReader()) {
                        while (dr.Read()) {
                            Console.WriteLine(dr[0].ToString());
                        }
                    }
                }
            }
            using(var con = new SqlConnection(connectionString)) {
                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");
                    Console.WriteLine("DatabaseName : " + databaseName + ", DatabaseID : " + dbID + ", CreationDate : " + creationDate);
                }
            }
        }
    }
}

I hope it's helpful.