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:

Image1.jpg

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. 


Similar Articles