Dynamically Getting Database Collection From SQL Server Using C#

In this article, we will see how to dynamically get a database List 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 a Connection String for server connection.

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

Step 7. Create a Server connection using a 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. I hope you have a nice day.