c# - Running SQL transaction script from code without waiting for the result - Stack Overflow

admin管理员组

文章数量:1419666

We have a three script process to transfer certain data from one database to another. The first script is a transaction that copies that data to a in-between database. The second script is just 4 simple select queries to compare two tables from the production database with the in-between database. THe third script is a transaction script that copies the data from the in-between database to the production database.

Running the scripts in SQL Management studio works fine. We have been using it for years now.

Our client doesn't want to be dependend on us to run the scripts so I'm trying to make them work from our webapplication with just the click on a button.

First I load the first script from the text file and send it to the database. Since it returns into a dataset and only takes a few seconds, it is send with EXECUTESCALAR.

The second script also returns tables, the check is done in code.

The third script is a large script, it can take up to 30 minutes to finish.

I'm sending the script with this:

 cnnUse = OpenSQLClient()
 With queryCommandSQLClient
   .CommandTimeout = commandTimeOut
   .CommandType = CommandType.Text
   .CommandText = source
   .Connection = CType(cnnUse, SqlConnection)
   .ExecuteNonQueryAsync()
 End With

I also tried without the Async, but in both cases it is not working.

I check the database with:

SELECT * FROM sys.sysprocesses WHERE dbid = 30 
select * from sys.dm_tran_active_transactions where name like 't_TAPBeheer_Copy_To_AP'

If I use ExecuteNonQuery(), then the script is running until a timeout (30sec) comes. With the async version, it never runs.

I have also tried BeginExecuteNonQuery() but then I seen the process in sysprocesses but it is waiting for something.

How can I make the script run even if the closes the browser although the user should do that?

To clarify how the process goes:

• user clicks on the start button
• a jquery event catches that click and does an ajax request to a webmethod in the codebehind of the page. When it returns it updates a DIV with the current time.
• then the second script is run the same way and after that the time is updated again.
• finally the last script is run by a async ajax call to the codebehind. When that call is done, a javascript intervaltimer will run asking the sql server if the transaction is done or not on every second. It also updates the time on the screen so the user knows it still running.

We have a three script process to transfer certain data from one database to another. The first script is a transaction that copies that data to a in-between database. The second script is just 4 simple select queries to compare two tables from the production database with the in-between database. THe third script is a transaction script that copies the data from the in-between database to the production database.

Running the scripts in SQL Management studio works fine. We have been using it for years now.

Our client doesn't want to be dependend on us to run the scripts so I'm trying to make them work from our webapplication with just the click on a button.

First I load the first script from the text file and send it to the database. Since it returns into a dataset and only takes a few seconds, it is send with EXECUTESCALAR.

The second script also returns tables, the check is done in code.

The third script is a large script, it can take up to 30 minutes to finish.

I'm sending the script with this:

 cnnUse = OpenSQLClient()
 With queryCommandSQLClient
   .CommandTimeout = commandTimeOut
   .CommandType = CommandType.Text
   .CommandText = source
   .Connection = CType(cnnUse, SqlConnection)
   .ExecuteNonQueryAsync()
 End With

I also tried without the Async, but in both cases it is not working.

I check the database with:

SELECT * FROM sys.sysprocesses WHERE dbid = 30 
select * from sys.dm_tran_active_transactions where name like 't_TAPBeheer_Copy_To_AP'

If I use ExecuteNonQuery(), then the script is running until a timeout (30sec) comes. With the async version, it never runs.

I have also tried BeginExecuteNonQuery() but then I seen the process in sysprocesses but it is waiting for something.

How can I make the script run even if the closes the browser although the user should do that?

To clarify how the process goes:

• user clicks on the start button
• a jquery event catches that click and does an ajax request to a webmethod in the codebehind of the page. When it returns it updates a DIV with the current time.
• then the second script is run the same way and after that the time is updated again.
• finally the last script is run by a async ajax call to the codebehind. When that call is done, a javascript intervaltimer will run asking the sql server if the transaction is done or not on every second. It also updates the time on the screen so the user knows it still running.

• c#
• sql-server
• vb

Share Improve this question Follow edited Jan 29 at 8:38 Eric asked Jan 29 at 7:06 EricEric 14711 silver badge1313 bronze badges 7

• Why don't you increase the timeout from the default (30 seconds)? – Jonathan Willcock Commented Jan 29 at 7:15
• 6 Pretty sure if you close the browser it closes the connection to the server which then closes the connection to the database.. You might need to kick off a background worker process, and ensure that IIS (or your web host) doesn't shutdown due to inactivity. Realistically, doing long running processes from a website isn't a great way to do it. – Dale K Commented Jan 29 at 7:19
• 2 You can run this in a sql agent job perhaps. Although, you need a way of knowing how to post specific code, but that's solvable. Async calls probably don't survive the asp life cycle – siggemannen Commented Jan 29 at 8:23
• @JonathanWillcock I build it so that the calls to run a script are done via a call by jquery ajax to a webmethod. This so I can show a timer and animation on screen for the user to know the process is still running. The user should not actually close the browser. From the webmethod the call goes to a webservice and then to the business layer. So increasing the time out would not only block the application, it would also require to increase the timeout on the webservice. – Eric Commented Jan 29 at 8:33
• 2 @Eric AJAX/JS won't help. It is the lifetime of the specific HTTP request: when then ends then all resources (including open DB connections) linked to that request are cleaned up. Background worker avoids being tied to a web request. SQL Agent is even better. – Richard Commented Jan 29 at 9:16

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 2

You can of course kick off a new separate process.

However, I think there is some risk by having a connection timeout of 30 minutes, and I can't say this being web based is going to work all that reliable.

While using session() for this typically is not thread safe, if you not in that process "constantly" updating the session() object, then you probably can get away with saving the thread object into session.

Much better would be to kick off the SQL process, and then poll a database. The long running stored procedure would then when done, update a status in a database row (you could even use the thread "id" in that database table).

Unfortunately, you can't get a running managed thread by "id", so you have to persist the thread object in some kind of persisting storage, such as session().

I have to think / guess, you are using a timeout = 0 (which means no timeout).

So while the separate running process could be passed the HttpContext.Current.Session() object?

I don't think it's a good idea to pass session() to a seperate running thread (Session can still go out of scope, and as noted, session() being updated by a seperate thread not recommended. However, as noted, you could try the following.

In this example, we do use session(), but the separate thread is not constantly updating some session() value for you to test. We are however storing the thread object in session (still not great, but better then trying to update session() inside of the separate thread).

So, here is a proof of concept:

Markup:

        <asp:Button ID="cmdStart" runat="server" Text="run SQL"
            CssClass="btn ui-widget-shadow"
            OnClientClick="mystart();return false;"
            />
        <br />
        <asp:Label ID="lblStatus" runat="server" Text="" ClientIDMode="Static"></asp:Label>


        <script>

            function mystart() {

                // start the long process
                $.ajax({
                    url: "LongSqlScript.aspx/StartLong",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    data: {},
                    success: function (data) {
                        startpolling()
                    },
                    failure: function (rData) {
                        alert("error " + rData.d);
                    }
                });


            }


            function startpolling() {

                var mydate = new Date();

                var seconds = mydate.getSeconds();
                var minutes = mydate.getMinutes();
                var hour = mydate.getHours();


                $.ajax({
                    url: "LongSqlScript.aspx/PollProcess2",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    data: {},
                    success: function (data) {
                        console.log(data.d)
                        if (data.d == "true") {
                            // still running - so we check again in 1 second
                            $('#lblStatus').text(hour + ":" + minutes + ":" + seconds)
                            setTimeout(startpolling,1000)
                        }
                        else {
                            // process is done
                            $('#lblStatus').text('** done ***')
                        }
                    },
                    failure: function (rData) {
                        alert("error " + rData.d);
                    }
                });
            }

        </script>

And code behind:

<WebMethod(EnableSession:=True)>
Public Shared Sub StartLong()

    ' process the images as seperate process
    'HttpContext.Current.Session("Working") = "true"

    Dim mypthread As New Thread(New ParameterizedThreadStart(AddressOf MyProcess))

    HttpContext.Current.Session("mythread") = mypthread

    mypthread.Start()


End Sub


Public Shared Sub MyProcess()

    ' my long fake process

    Dim strSQL As String =
        "WAITFOR DELAY '00:00:10'"
    ' process the images as seperate process

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            cmdSQL.CommandTimeout = 0
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using


End Sub


<WebMethod(EnableSession:=True)>
Public Shared Function PollProcess2() As String

    Dim MyProcess As Thread = HttpContext.Current.Session("mythread")

    If MyProcess.IsAlive Then
        Return "true"
    Else
        Return "false"
    End If


End Function

Now, above needs some love and care, but when I run it (10 second example), then we see this:

However, I would suggest that you consider the database idea, and have the separate process update a row in a database when done, which is a good deal safer and more reliable then using session().

In above, I use a timeout = 0, which means no timeout.

And since the page (every one second) is polling the server? Then that will well keep alive the page, and prevent a session() timeout.

Above assumes jQuery.

All in all? A general approach to web sites and pages does not at all well support long running server processes in a browser environment.

本文标签： cRunning SQL transaction script from code without waiting for the resultStack Overflow