How to run query based on multi thread using clr function sp

Question

I work on visual studio 2019 asp.net core 2.2 and sql server 2012 I need to run query every 2 seconds work on background based on 4 threads only below is clr function but i need to apply below code on asp.net core so that How to call function spExecuteParallel under action controller like that public Actionresult executequery() { write here calling clr function } so that my question How to apply clr function on asp.net core 2 or there are any thing can do that on asp.net core what i try  class  ExecuteSQL       {            private  List string > oExecuteErrors;            private   object  oExecuteLocker;            private   string  sExecuteServer;            private   string  sExecuteDB;            private   string  sExecuteTSQL;            private   int  iExecuteRetries;               public  ExecuteSQL( string  sServer,  string  sDB,  string  sTSQL,            int  iRetries,  ref  List string > oErrors,  ref   object  oLocker)           {                this .sExecuteServer = sServer;                this .sExecuteDB = sDB;                this .sExecuteTSQL = sTSQL;                this .iExecuteRetries = iRetries;                this .oExecuteErrors = oErrors;                this .oExecuteLocker = oLocker;           }               public   void  Process()           {                int  iTries = 1;               SqlConnection oConn =  new  SqlConnection();              Retry:               oConn =  new  SqlConnection( "Data Source="  + sExecuteServer +                ";Initial Catalog="  + sExecuteDB +  ";Integrated Security=SSPI;" );                try                {                   oConn.Open();                       if  (oConn.State == ConnectionState.Open)                   {                       SqlCommand oCmd = oConn.CreateCommand();                       oCmd.CommandText = sExecuteTSQL;                       oCmd.CommandTimeout = 0;                       oCmd.ExecuteNonQuery();                          oCmd.Dispose();                       oConn.Close();                       oConn.Dispose();                   }                    else                    {                        throw   new  Exception( "SQL Server not Found or Unable to Connect to SQL Server" );                   }               }                catch  (Exception ex)               {                    if  (oConn.State != ConnectionState.Closed) oConn.Close();                   oConn.Dispose();                       if  (iTries                  {                       Thread.Sleep(5000);                       iTries += 1;                        goto  Retry;                   }                    else                    {                        lock  (oExecuteLocker)                       {                            char  cSpace =  char .Parse( " " );                           oExecuteErrors.Add( this .sExecuteDB.PadRight(16, cSpace) +  " : "  + ex.Message);                       }                   }               }           }       }   }      namespace  SqlServerProjectSp   {        public  partial  class  StoredProcedures       {           [Microsoft.SqlServer.Server.SqlProcedure]            public   static  SqlInt32 spExecuteParallel( string  DB,  int  MaxDOP,  string  TSQL,  int  msDelay,  int  Retries)           {               SqlConnection oConn =  new  SqlConnection();               SqlCommand oCmd =  new  SqlCommand();               List string > oErrorString =  new  List string >();                object  oLocker =  new   object ();                string  sServer =  null ;                  List  oThread =  new  List ();               StringCollection sStopped =  new  StringCollection();                if ( string .IsNullOrEmpty(TSQL))               {                    return  0;               }                // Get Server Instance Name                oConn =  new  SqlConnection( "context connection = true;" );               oConn.Open();                  oCmd = oConn.CreateCommand();               oCmd.CommandText =  "SELECT…

Ashutosh Gupta · Answer

you may have to do multiple steps here to achive it. you can create services to communicate with b/w controller and sql like  SqlDBService : ISqlDBService {       public async  SqlInt32 spExecuteParallel(.......) }    make your method to async type so that easy to call from controller. you await till all threads are completed.   next step is to add this service interface on your .net core startup class -> ConfigureServices(...)  services.AddScoped ();    and next use it on your controller constructor you can use this service ControllerXyz( ISqlDBService service) { }    now you have this service instance on your controller and just start using all services exposed by this service.