How to replace string sTSQL parameter with stored procedure spqueRun on csharp clr function ?

[engahmedbarbary]

problem

How to replace string sTSQL parameter with stored procedure spqueRun on csharp clr function ?

clr function wih csharp work on sql good but must pass tsql text from sql

i need to call spquerun from csharp and no need to use use sTsqlParamter

How to do that ?

create Proc spqueRun
As
WITH CTE AS
(
Select Row_Number() Over (Order By GetDate())as rownumber, StoredProcedureName , ImporterQueue.CreateBy , ImporterQueueID,applicationid, dbo.ImporterTemplate.ImporterTemplateID, InputFilePath, OutputFilePath, StoredProcedureName [ImporterTemplate.StoredProcedureName],
RN = ROW_NUMBER() OVER (PARTITION BY applicationid ORDER BY ImporterQueueID asc)
From dbo.ImporterQueue
Inner Join dbo.ImporterTemplate On dbo.ImporterQueue.ImporterTemplateID = dbo.ImporterTemplate.ImporterTemplateID
Inner Join Privilages.Module On dbo.ImporterTemplate.ModuleID = Privilages.Module.ModuleID
Where dbo.ImporterQueue.IsDeleted = 0 And dbo.ImporterQueue.OverAllStatusID = 1
)
SELECT rownumber , RN , ImporterQueueID,CreateBy,StoredProcedureName,InputFilePath,OutputFilePath
into #results FROM CTE
WHERE RN = 1;
If (Select OverAllStatusID From dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID) <> 1 -- Pending
Return;
--loop through temp table
DECLARE @totalRecords INT
DECLARE @I INT




--Declare @UserID Int = (Select CreateBy From dbo.ImporterQueue Where ImporterQueueID = @ImporterQueueID)
Declare @ImportingStartDate DateTime = GetDate(), @DurationInSeconds Int

Update dbo.ImporterQueue Set
ImportingStartDate = @ImportingStartDate,
OverAllStatusID = 2, -- In Progress
StatusReason = Null,
UpdateBy = #results.CreateBy,
UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID


function csharp i need to modify it

[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;
}

namespace SqlServerProjectSp
{
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);
}
}
}
}

when execute on sql server i add assembly

then call spexecuteparllel stored procedure

i need to replace tsql with stored procedure spqueurun from csharp ?


USE [Test]
GO

DECLARE @return_value int

EXEC @return_value = spExecuteParallel
@DB = N'Test',
@MaxDOP = 8,

-- no need use @TSQL I NEED TO EXECUTE @TSQL FROM CSHARP
@TSQL = N'Insert into TestTable (messagesData, LogDateValues) values (''Test'', GetDate())',
@msDelay = 0,
@Retries = 1

SELECT 'Return Value' = @return_value
GO

Continue reading...