Dynamically Getting Stored Procedures Collection From SQL Server Using C#

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,

Step 3. Connection String.

<add name="<KEY>" 
     connectionString="Data Source=<SERVERNAME>;Initial Catalog=<DATABASE>;Integrated Security=True" />

Step 4. Used to get the 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 stored procedures of the database as a List.

StoredProcedures sp = SQLProcedureDatabaseClass.SProcedure(connectionString, "databaseName");

Here you can get a single StoredProcedure 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. Creating 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 StoredProcedureCollection from the database in the SQL Server.

public static StoredProcedureCollection SPCollections(string connectionString, string databaseName)
{
    StoredProcedureCollection col = SinglDatabase(connectionString, databaseName).StoredProcedures;
    return col;
}

Step 10. Getting a Single stored Procedure 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.