Introduction
I will show you the steps so that you can 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:
![1.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 the Web.config file:
public static string GetConnectionStringFromWebConfigByName(string name)
{
return WebConfigurationManager.ConnectionStrings[name].ConnectionString;
}
Step 5: Usage:
SQLProcedureDatabaseClass tables = SQLProcedureDatabaseClass. SPCollections (connectionString,"databaseName");
Here you can get the StoredProcedures of database as List.
StoredProcedures sp = SQLProcedureDatabaseClass. SProcedure (connectionString,"databaseName");
Here you can get a single StoredProcedure 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 StoredProcedureCollection from database in the SQL Server:
public static StoredProcedureCollection SPCollections(string connectionString, string databaseName)
{
StoredProcedureCollection col = SinglDatabase(connectionString, databaseName).StoredProcedures;
return col;
}
Step 10: Getting Single StoredProcedure from the database:
public static StoredProcedure SProcedure(string connectionString, string databaseName, string procedureName)
{
StoredProcedureCollection col = SPCollections(connectionString, databaseName);
return col[procedureName];
}
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 SQLProcedureDatabaseClass
{
#region Database
#region Stored Procedure
public static StoredProcedureCollection SPCollections(string connectionString, string databaseName)
{
StoredProcedureCollection col = SinglDatabase(connectionString, databaseName).StoredProcedures;
return col;
}
public static StoredProcedure SProcedure(string connectionString, string databaseName, string procedureName)
{
StoredProcedureCollection col = SPCollections(connectionString, databaseName);
return col[procedureName];
}
#endregion
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.