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

  • Question

  • 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


    • Moved by CoolDadTx Tuesday, November 12, 2019 2:55 PM SQL related
    Saturday, November 9, 2019 9:51 AM

Answers

  • Hi engahmedbarbary,

    Thank you for posting here.

    For your question, you want to use a stored procedure instead of a SQL statement in C# to manipulate the database.

    There is a simple code example, you can refer to it and modify your code.

            static void InsertPersonInAdapter(String connectionString, String firstName, String lastName)
            {
                String commandText = "dbo.InsertPerson";
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);
    
                    mySchool.InsertCommand = new SqlCommand(commandText, conn);
                    mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;
    
                    mySchool.InsertCommand.Parameters.Add(
                        new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));
                    mySchool.InsertCommand.Parameters.Add(
                        new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));
    
                    SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));
                    personId.Direction = ParameterDirection.Output;
    
                    DataTable persons = new DataTable();
                    mySchool.Fill(persons);
    
                    DataRow newPerson = persons.NewRow();
                    newPerson["FirstName"] = firstName;
                    newPerson["LastName"] = lastName;
                    persons.Rows.Add(newPerson);
    
                    mySchool.Update(persons);
    
                }
            }

    Stored procedure:

    CREATE PROCEDURE[dbo].[InsertPerson]   
    -- Add the parameters for the stored procedure here
    @FirstName nvarchar(50),@LastName nvarchar(50),  
    @PersonID int output
    AS
    BEGIN
        insert[dbo].[Person]
        (LastName, FirstName) Values(@LastName, @FirstName)
    
        set @PersonID = SCOPE_IDENTITY()
    END
    Go

    Hope this could be helpful.

    Best Regards,

    Timon


    Monday, November 11, 2019 8:39 AM

All replies

  • Lots of code but what I don't see if CommandType = CommandType.StoredProcedure

    A typical code sample (and I know it does not match your code)

    public int AddCustomer(string companyName, string contactName, int contactTypeIdentifier)
    {
    	mHasException = false;
    	try
    	{
    		using (var cn = new SqlConnection {ConnectionString = ConnectionString})
    		{
    
    			using (var cmd = new SqlCommand
    			{
    				Connection = cn,
    				CommandType = CommandType.StoredProcedure
    			})
    			{
    
    				cmd.CommandText = "dbo.InsertCustomer";
    
    				cmd.Parameters.Add(new SqlParameter
    				{
    					ParameterName = "@CompanyName",
    					SqlDbType = SqlDbType.NVarChar
    				});
    				cmd.Parameters.Add(new SqlParameter
    				{
    					ParameterName = "@ContactName",
    					SqlDbType = SqlDbType.NVarChar
    				});
    				cmd.Parameters.Add(new SqlParameter
    				{
    					ParameterName = "@ContactTypeIdentifier",
    					SqlDbType = SqlDbType.Int
    				});
    				cmd.Parameters.Add(new SqlParameter
    				{
    					ParameterName = "@Identity",
    					SqlDbType = SqlDbType.Int,
    					Direction = ParameterDirection.Output
    				});
    
    				cmd.Parameters["@CompanyName"].Value = companyName;
    				cmd.Parameters["@ContactName"].Value = contactName;
    				cmd.Parameters["@ContactTypeIdentifier"].Value = contactTypeIdentifier;
    
    				cn.Open();
    
    				cmd.ExecuteScalar();
    
    				return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
    
    			}
    		}
    	}
    	catch (Exception ex)
    	{
    		mHasException = true;
    		mLastException = ex;
    
    		return -1;
    
    	}
    }

    SP

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[InsertCustomer]  
        @CompanyName NVARCHAR(200), 
        @ContactName NVARCHAR(200), 
        @ContactTypeIdentifier INT, 
        @Identity INT OUT 
    AS 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON; 
     
    INSERT INTO Customer(CompanyName,ContactName,ContactTypeIdentifier)  
        VALUES(@CompanyName,@ContactName,@ContactTypeIdentifier) 
     
    SET @Identity = SCOPE_IDENTITY() 
     
    END 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, November 9, 2019 12:22 PM
  • Hi engahmedbarbary,

    Thank you for posting here.

    For your question, you want to use a stored procedure instead of a SQL statement in C# to manipulate the database.

    There is a simple code example, you can refer to it and modify your code.

            static void InsertPersonInAdapter(String connectionString, String firstName, String lastName)
            {
                String commandText = "dbo.InsertPerson";
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);
    
                    mySchool.InsertCommand = new SqlCommand(commandText, conn);
                    mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;
    
                    mySchool.InsertCommand.Parameters.Add(
                        new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));
                    mySchool.InsertCommand.Parameters.Add(
                        new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));
    
                    SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));
                    personId.Direction = ParameterDirection.Output;
    
                    DataTable persons = new DataTable();
                    mySchool.Fill(persons);
    
                    DataRow newPerson = persons.NewRow();
                    newPerson["FirstName"] = firstName;
                    newPerson["LastName"] = lastName;
                    persons.Rows.Add(newPerson);
    
                    mySchool.Update(persons);
    
                }
            }

    Stored procedure:

    CREATE PROCEDURE[dbo].[InsertPerson]   
    -- Add the parameters for the stored procedure here
    @FirstName nvarchar(50),@LastName nvarchar(50),  
    @PersonID int output
    AS
    BEGIN
        insert[dbo].[Person]
        (LastName, FirstName) Values(@LastName, @FirstName)
    
        set @PersonID = SCOPE_IDENTITY()
    END
    Go

    Hope this could be helpful.

    Best Regards,

    Timon


    Monday, November 11, 2019 8:39 AM