locked
how to run query based on multithread using clr function spExecuteParallel ? RRS feed

  • Question

  • User696604810 posted

    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

    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 <= iExecuteRetries)
                    {
                        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<Thread> oThread = new List<Thread>();
                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 @@SERVERNAME";
                sServer = oCmd.ExecuteScalar().ToString();
    
                oCmd.Dispose();
                oConn.Close();
                oConn.Dispose();
    
                // Execute Threads
                int iCurrentThread = 0;
                while (iCurrentThread < MaxDOP)
                {
                    ExecuteSQL Executer = new ExecuteSQL
                    (sServer, DB, TSQL.Replace("?", DB.ToString().Trim()), Retries, ref oErrorString, ref oLocker);
    
                    Thread oItem = new Thread(Executer.Process);
                    oItem.Name = "ExecuteSQL " + DB.ToString().Trim();
                    oItem.Start();
                    oThread.Add(oItem);
    
                    SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() +
                    " : Start : " + oItem.Name.Replace("ExecuteSQL ", ""));
                    Thread.Sleep(msDelay);
    
                    while (RunningThreads(ref oThread, ref sStopped) >= MaxDOP)
                    {
                        Thread.Sleep(1000);
                    }
                    iCurrentThread++;
                }
    
                // Wait for all Threads to Stop
                while (RunningThreads(ref oThread, ref sStopped) > 0)
                {
                    Thread.Sleep(1000);
                }
                SqlContext.Pipe.Send("All Thread have Stopped with " +
                oErrorString.Count.ToString() + " Error/s ");
    
                if (oErrorString.Count > 0)
                {
                    foreach (string sIndividualErrors in oErrorString)
                    {
                        SqlContext.Pipe.Send(sIndividualErrors.ToString());
                    }
    
                    throw new Exception("Error Occurred.");
                }
    
                return 0 - oErrorString.Count;
            }
    
            public static int RunningThreads(ref List<Thread> oThread, ref StringCollection oStops)
            {
                int iRunningCount = 0;
    
                foreach (Thread oIndividualThread in oThread)
                {
                    if (oIndividualThread.IsAlive)
                    {
                        iRunningCount += 1;
                    }
                    else if (!oStops.Contains(oIndividualThread.Name))
                    {
                        oStops.Add(oIndividualThread.Name);
                        SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() + " : Stop  : " + oIndividualThread.Name.Replace("ExecuteSQL ", ""));
    
    
    
                    }
                }
                return iRunningCount;
            }
        }
    }
    

    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 

    thanks

    Thursday, December 5, 2019 2:53 AM

Answers

  • User696604810 posted

    thank you for reply

    the code above work on csharp i need to use that on asp.net core 2.2

    this code execute queries on sql server 2012 based on multi thread based on number of maxdop 

    if maxdop =4 this meaning i will use 4 thread on server 

    not wait every process to finish then run the following process

    so that my question here are there are any technologies are equal or alternative to code above

    the code above represent c sharp code done by clr function because i cannot use multi thread on SQL server 2012

    are there are any way alternative doing multi thread without using code above and work on asp.net core 2.2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 7, 2019 7:37 PM

All replies

  • User696604810 posted

    can any one help me please

    Saturday, December 7, 2019 1:50 AM
  • User-474980206 posted

    whats is the actual question? what with the current code does not work? 

    As ado has supported async queries for years now, not sure why the multiple threads.

    Saturday, December 7, 2019 4:42 AM
  • User696604810 posted

    thank you for reply

    the code above work on csharp i need to use that on asp.net core 2.2

    this code execute queries on sql server 2012 based on multi thread based on number of maxdop 

    if maxdop =4 this meaning i will use 4 thread on server 

    not wait every process to finish then run the following process

    so that my question here are there are any technologies are equal or alternative to code above

    the code above represent c sharp code done by clr function because i cannot use multi thread on SQL server 2012

    are there are any way alternative doing multi thread without using code above and work on asp.net core 2.2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 7, 2019 7:37 PM