Dynamically Getting Tables Collection From SQL Server Using C#

In this article we are going to see how to get tables from a database 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:

SQLTableDatabaseClass tables =  SQLTableDatabaseClass.
GetTablesFromDatabase (connectionString,"databaseName");

Here you can get the tables of database as List.

Table table =  SQLTableDatabaseClass.
SingleTable(connectionString,"databaseName");

Here you can get a single table 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 Tables from Database in the SQL Server:

public static TableCollection GetTablesFromDatabase(string connectionString, string databaseName)
{
    Database db = SinglDatabase(connectionString, databaseName);
    return db.Tables;
}

Step 10: Getting a single table from the database:

public static Table SingleTable(string connectionString, string databaseName, string tableName)
{
    TableCollection tableCol = GetTablesFromDatabase(connectionString, databaseName);
    return tableCol[tableName];
}

Step 11: 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];
}

COPY & PASTE Code Snippet:

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

namespace ApplicationClassLibrary
{
    class SQLTableDatabaseClass
    {
        #region Database
        public static TableCollection GetTablesFromDatabase(string connectionString, string databaseName)
        {
            Database db = SinglDatabase(connectionString, databaseName);
            return db.Tables;
        }
        public static Table SingleTable(string connectionString, string databaseName, string tableName)
        {
            TableCollection tableCol = GetTablesFromDatabase(connectionString, databaseName);
            return tableCol[tableName];
        }
        public static Database SinglDatabase(string connectionString, string databaseName)
        {
            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.