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.