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:

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.