1
Answer

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

ahmed elbarbary

ahmed elbarbary
5y
852
1
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 
  1. class ExecuteSQL  
  2.     {  
  3.         private List<string> oExecuteErrors;  
  4.         private object oExecuteLocker;  
  5.         private string sExecuteServer;  
  6.         private string sExecuteDB;  
  7.         private string sExecuteTSQL;  
  8.         private int iExecuteRetries;  
  9.   
  10.         public ExecuteSQL(string sServer, string sDB, string sTSQL,  
  11.         int iRetries, ref List<string> oErrors, ref object oLocker)  
  12.         {  
  13.             this.sExecuteServer = sServer;  
  14.             this.sExecuteDB = sDB;  
  15.             this.sExecuteTSQL = sTSQL;  
  16.             this.iExecuteRetries = iRetries;  
  17.             this.oExecuteErrors = oErrors;  
  18.             this.oExecuteLocker = oLocker;  
  19.         }  
  20.   
  21.         public void Process()  
  22.         {  
  23.             int iTries = 1;  
  24.             SqlConnection oConn = new SqlConnection();  
  25.   
  26.         Retry:  
  27.             oConn = new SqlConnection("Data Source=" + sExecuteServer +  
  28.             ";Initial Catalog=" + sExecuteDB + ";Integrated Security=SSPI;");  
  29.             try  
  30.             {  
  31.                 oConn.Open();  
  32.   
  33.                 if (oConn.State == ConnectionState.Open)  
  34.                 {  
  35.                     SqlCommand oCmd = oConn.CreateCommand();  
  36.                     oCmd.CommandText = sExecuteTSQL;  
  37.                     oCmd.CommandTimeout = 0;  
  38.                     oCmd.ExecuteNonQuery();  
  39.   
  40.                     oCmd.Dispose();  
  41.                     oConn.Close();  
  42.                     oConn.Dispose();  
  43.                 }  
  44.                 else  
  45.                 {  
  46.                     throw new Exception("SQL Server not Found or Unable to Connect to SQL Server");  
  47.                 }  
  48.             }  
  49.             catch (Exception ex)  
  50.             {  
  51.                 if (oConn.State != ConnectionState.Closed) oConn.Close();  
  52.                 oConn.Dispose();  
  53.   
  54.                 if (iTries <= iExecuteRetries)  
  55.                 {  
  56.                     Thread.Sleep(5000);  
  57.                     iTries += 1;  
  58.                     goto Retry;  
  59.                 }  
  60.                 else  
  61.                 {  
  62.                     lock (oExecuteLocker)  
  63.                     {  
  64.                         char cSpace = char.Parse(" ");  
  65.                         oExecuteErrors.Add(this.sExecuteDB.PadRight(16, cSpace) + " : " + ex.Message);  
  66.                     }  
  67.                 }  
  68.             }  
  69.         }  
  70.     }  
  71. }  
  72.   
  73. namespace SqlServerProjectSp  
  74. {  
  75.     public partial class StoredProcedures  
  76.     {  
  77.         [Microsoft.SqlServer.Server.SqlProcedure]  
  78.         public static SqlInt32 spExecuteParallel(string DB, int MaxDOP, string TSQL, int msDelay, int Retries)  
  79.         {  
  80.             SqlConnection oConn = new SqlConnection();  
  81.             SqlCommand oCmd = new SqlCommand();  
  82.             List<string> oErrorString = new List<string>();  
  83.             object oLocker = new object();  
  84.             string sServer = null;  
  85.   
  86.             List<Thread> oThread = new List<Thread>();  
  87.             StringCollection sStopped = new StringCollection();  
  88.             if(string.IsNullOrEmpty(TSQL))  
  89.             {  
  90.                 return 0;  
  91.             }  
  92.             // Get Server Instance Name  
  93.             oConn = new SqlConnection("context connection = true;");  
  94.             oConn.Open();  
  95.   
  96.             oCmd = oConn.CreateCommand();  
  97.             oCmd.CommandText = "SELECT @@SERVERNAME";  
  98.             sServer = oCmd.ExecuteScalar().ToString();  
  99.   
  100.             oCmd.Dispose();  
  101.             oConn.Close();  
  102.             oConn.Dispose();  
  103.   
  104.             // Execute Threads  
  105.             int iCurrentThread = 0;  
  106.             while (iCurrentThread < MaxDOP)  
  107.             {  
  108.                 ExecuteSQL Executer = new ExecuteSQL  
  109.                 (sServer, DB, TSQL.Replace("?", DB.ToString().Trim()), Retries, ref oErrorString, ref oLocker);  
  110.   
  111.                 Thread oItem = new Thread(Executer.Process);  
  112.                 oItem.Name = "ExecuteSQL " + DB.ToString().Trim();  
  113.                 oItem.Start();  
  114.                 oThread.Add(oItem);  
  115.   
  116.                 SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() +  
  117.                 " : Start : " + oItem.Name.Replace("ExecuteSQL ", ""));  
  118.                 Thread.Sleep(msDelay);  
  119.   
  120.                 while (RunningThreads(ref oThread, ref sStopped) >= MaxDOP)  
  121.                 {  
  122.                     Thread.Sleep(1000);  
  123.                 }  
  124.                 iCurrentThread++;  
  125.             }  
  126.   
  127.             // Wait for all Threads to Stop  
  128.             while (RunningThreads(ref oThread, ref sStopped) > 0)  
  129.             {  
  130.                 Thread.Sleep(1000);  
  131.             }  
  132.             SqlContext.Pipe.Send("All Thread have Stopped with " +  
  133.             oErrorString.Count.ToString() + " Error/s ");  
  134.   
  135.             if (oErrorString.Count > 0)  
  136.             {  
  137.                 foreach (string sIndividualErrors in oErrorString)  
  138.                 {  
  139.                     SqlContext.Pipe.Send(sIndividualErrors.ToString());  
  140.                 }  
  141.   
  142.                 throw new Exception("Error Occurred.");  
  143.             }  
  144.   
  145.             return 0 - oErrorString.Count;  
  146.         }  
  147.   
  148.         public static int RunningThreads(ref List<Thread> oThread, ref StringCollection oStops)  
  149.         {  
  150.             int iRunningCount = 0;  
  151.   
  152.             foreach (Thread oIndividualThread in oThread)  
  153.             {  
  154.                 if (oIndividualThread.IsAlive)  
  155.                 {  
  156.                     iRunningCount += 1;  
  157.                 }  
  158.                 else if (!oStops.Contains(oIndividualThread.Name))  
  159.                 {  
  160.                     oStops.Add(oIndividualThread.Name);  
  161.                     SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() + " : Stop  : " + oIndividualThread.Name.Replace("ExecuteSQL ", ""));  
  162.   
  163.   
  164.   
  165.                 }  
  166.             }  
  167.             return iRunningCount;  
  168.         }  
  169.     }  
  170. }  
 
 
Answers (1)
Next Recommended Forum