Dynamically Getting Database Collection From SQL Server Using C#

In this article we are going to see how to get a database List dynamically using C#.

Step 1: Used Namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Step 2: Referenced DLL

Step 3: Connection String:

<add name="<KEY>" connectionString="Data Source=<SERVERNAME>;Initial Catalog=<DATABASE>;Integrated Security=True" />

Step 4: Used to get Connection String from Web.config file:

public static string GetConnectionStringFromWebConfigByName(string name)
{
    return WebConfigurationManager.ConnectionStrings[name].ConnectionString;
}

Step 5: Usage

DatabaseCollection databases =  SQLDatabaseClass .DbCollection(connectionString);

Here you can get the collection of a database as a List.

Database database =  SQLDatabaseClass.SinglDatabase(connectionString);

Here you can get a single database by its name.

Step 6: Creating Connection String for server connection:

public static SqlConnection Connecection(string connectionString)
{
    SqlConnection con = new SqlConnection(connectionString);
    return con;
}

Step 7: Creating Server connection using Connection string:

public static ServerConnection GetServerConnection(string connectionString)
{
    ServerConnection serverCon = new ServerConnection(Connecection(connectionString));
    serverCon.Connect();
    return serverCon;
}

Step 8: Creating Server Object:

public static Server GetServer(string connectionString)
{
    Server server = new Server(GetServerConnection(connectionString));
    return server;
}

Step 9: Getting Database collection from the SQL Server:

public static DatabaseCollection DbCollection(string connectionString)
{
    Server server = GetServer(connectionString);
    return server.Databases;
}

Step 10: Getting a specific database from the collection of databases in the SQL Server:

public static Database SinglDatabase(string connectionString, string databaseName)
{
    return GetServer(connectionString).Databases[databaseName];
}

Code Snippet:

class SQLDatabaseClass
{
    #region Database
    public static DatabaseCollection DbCollection(string connectionString)
    {
        Server server = GetServer(connectionString);
        return server.Databases;
    }
 
public static Database SinglDatabase(string connectionString, string Name)
{
    return GetServer(connectionString).Databases[databaseName];
}
 
#endregion
 
public static Server GetServer(string connectionString)
{
    Server server = new Server(GetServerConnection(connectionString));
    return server;
}

public static ServerConnection GetServerConnection(string connectionString)
{
    ServerConnection serverCon = new ServerConnection(Connecection(connectionString));
    serverCon.Connect();
    return serverCon;
}

public static ServerConnection GetServerConnectionByLogin(bool isWindows, string serverName)
    {
        ServerConnection serverCon = new ServerConnection();
        serverCon.LoginSecure = isWindows;
        serverCon.ServerInstance = serverName;
        serverCon.Connect();
        return serverCon;
    }

    public static SqlConnection Connecection(string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);
        return con;
    }
}

Thanks for reading this article. Have a nice day.