Timer class in C#

Hi

Does anone know if its possible to use a timer class inside a C# sql procedure method?

the reason i need this if i dont have my data inserted into my DB within 5-10 secs i need to throw an exception.

if i have my method...

[Microsoft.SqlServer.Server.SqlProcedure]
    public void GetOrders()
    {
        // i want to kick off a timer here
        // then pull in my data from SAP and put it into DB and if taking more than 10 secs throw an exception

     }

Or if i can throw the exception actually inside the actual SP in the DB?

Thanks
LVL 1
razza_bAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What the SP does?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
which part could take more than 10 seconds? is that within the stored procedure itself?

you could fetch "getdate()" before and after the step, and before committing, check and eventually rollback.
to throw an exception in a sql server stored procedure, use RAISERROR with a severity > 16:
https://msdn.microsoft.com/en-us/library/ms178592.aspx
razza_bAuthor Commented:
i have attached my code.

when i execute my stored procedure (calling it in the DB by a sql job thats scheduled) i can more than10 secs to pull everything in.

so i would need to know how to do some kind of timer in that code to detect if its taking longer than the 10 secs, if so throw an error.


[Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetOrderStatus()
    {
        string[] SAPString = GetSAPConnectionForCLR().ToString().Split(',');
        SqlContext.Pipe.Send("Connecting to " + SAPString[0]);
        R3Connection R3con = new R3Connection(SAPString[0], int.Parse(SAPString[1]), SAPString[2], SAPString[3], SAPString[4], SAPString[5]);
        R3con.Open(false);

        RFCFunction func = R3con.CreateFunction("Order_Status");


        try
        {
            string Plant = GetSAPPlantForCLR().ToString();
            func.Exports["PLANT"].ToStructure()["WERKS"] = Plant;

            func.Execute();

            SqlContext.Pipe.Send("Saving work order status for plant " + Plant.ToString());

            SqlConnection SQLCon = new SqlConnection();
            SQLCon.ConnectionString = "Context Connection=True";
            SqlCommand cmdInsertAux = new SqlCommand();
            cmdInsertAux.Connection = SQLCon;
            cmdInsertAux.Connection.Open();

            for (int i = 0; i < func.Tables["STATTABLE"].RowCount; i++)
            {

                cmdInsertAux.CommandText = "INSERT INTO SAP.SapDownloadStatus (Client,MoNumber) " +
                "VALUES (" +
                "'" + func.Tables["STATTABLE"].Rows[i, "MANDT"].ToString().Trim().Replace("'", "").Replace(",", " ") + "'," +
                "'" + func.Tables["STATTABLE"].Rows[i, "AUFNR"].ToString().Trim().Replace("'", "").Replace(",", " ") + "')";
                cmdInsertAux.ExecuteNonQuery();
            }
            cmdInsertAux.Dispose();
            SQLCon.Close();

        }
        catch (ERPException e)
        {
            SqlContext.Pipe.Send(e.Message);
            return;
        }
        catch (SqlException e)
        {
            SqlContext.Pipe.Send(e.Message);
            throw e;
        }
        catch (Exception e)
        {
            SqlContext.Pipe.Send(e.Message);
            throw e;
        }
        finally
        {
            R3con.Close();
        }
    }

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

razza_bAuthor Commented:
thats just a small method getting the status, the one i have been using has more data to pull through, so embedded code is just a small example....
[Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetOrderStatus()
    {
        string[] SAPString = GetSAPConnectionForCLR().ToString().Split(',');
        SqlContext.Pipe.Send("Connecting to " + SAPString[0]);
        R3Connection R3con = new R3Connection(SAPString[0], int.Parse(SAPString[1]), SAPString[2], SAPString[3], SAPString[4], SAPString[5]);
        R3con.Open(false);

        RFCFunction func = R3con.CreateFunction("Order_Status");


        try
        {
            string Plant = GetSAPPlantForCLR().ToString();
            func.Exports["PLANT"].ToStructure()["WERKS"] = Plant;

            func.Execute();

            SqlContext.Pipe.Send("Saving work order status for plant " + Plant.ToString());

            SqlConnection SQLCon = new SqlConnection();
            SQLCon.ConnectionString = "Context Connection=True";
            SqlCommand cmdInsertAux = new SqlCommand();
            cmdInsertAux.Connection = SQLCon;
            cmdInsertAux.Connection.Open();

            for (int i = 0; i < func.Tables["STATTABLE"].RowCount; i++)
            {

                cmdInsertAux.CommandText = "INSERT INTO SAP.SapDownloadStatus (Client,MoNumber) " +
                "VALUES (" +
                "'" + func.Tables["STATTABLE"].Rows[i, "MANDT"].ToString().Trim().Replace("'", "").Replace(",", " ") + "'," +
                "'" + func.Tables["STATTABLE"].Rows[i, "AUFNR"].ToString().Trim().Replace("'", "").Replace(",", " ") + "')";
                cmdInsertAux.ExecuteNonQuery();
            }
            cmdInsertAux.Dispose();
            SQLCon.Close();

        }
        catch (ERPException e)
        {
            SqlContext.Pipe.Send(e.Message);
            return;
        }
        catch (SqlException e)
        {
            SqlContext.Pipe.Send(e.Message);
            throw e;
        }
        catch (Exception e)
        {
            SqlContext.Pipe.Send(e.Message);
            throw e;
        }
        finally
        {
            R3con.Close();
        }
    }

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use CommandTimeout property and set it to 10 seconds, meaning that if after 10 seconds the command still running it will throw an exception: cmdInsertAux.CommandTimeout = 10;
razza_bAuthor Commented:
thats something that is good to have but ...

it actually hangs inbetween these lines of code...

func.Exports["PLANT"].ToStructure()["WERKS"] = Plant;
// here i need some timer exception //
func.Execute();
Vitor MontalvãoMSSQL Senior EngineerCommented:
What those lines of code does?
razza_bAuthor Commented:
its made the connection to SAP and executed the function, so it hangs in there...
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to dig that function out. Something is happening there and you need to realize what.
razza_bAuthor Commented:
because its a third party DLL i cant debug a further thats why i want to have a timer to throw an error if nothing is happening...
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. I can't see another solution but the timeout option.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.