Author Topic: SQL Server ExecuteNonQuery() state?  (Read 4121 times)

stanl

  • Pundit
  • *****
  • Posts: 932
SQL Server ExecuteNonQuery() state?
« on: January 10, 2014, 07:56:09 am »
I have written a script which creates a linked server to SQL Server (2008 Express R2) with an Access Table using the CLR. It works and is quite fast, and would like to post it.

When I wrote a similar script in COM, after Executing the linked server SQL I put in a while loop based on the command state.

I looked at MSDN OLEDB connection and command classes, there is no state property for the command class, and the connection state is either open or close. I assumed there would be states like executing, or fetching like in COM.

Therefore the script runs (cm = the command object)

cm.ExecuteNonQuery

cm.dispose()

Then closes the connection and exits the script. Like I said it works, but isn't there a danger if the script exits before the link is completed, or does it run on its own thread independent of WB?



td

  • Tech Support
  • *****
  • Posts: 2996
    • WinBatch
Re: SQL Server ExecuteNonQuery() state?
« Reply #1 on: January 10, 2014, 09:46:29 am »
The documentation for the ExecuteNonQuery does not state that it is asynchronous.  The MSFT FCL documentation convention is to state this fact when a method processes asynchronously.  Also, FCL asynchronous method names usually start with the word 'Begin'.  These two facts make it fairly safe to assume that the function is synchronous and not asynchronous.   The class does provide a 'CommandTimeout' property that can be used to cause an exception which will be returned as an error by WinBatch, if the command is not completed within the allotted time.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

  • Pundit
  • *****
  • Posts: 932
Re: SQL Server ExecuteNonQuery() state?
« Reply #2 on: January 11, 2014, 04:18:15 am »
The documentation for the ExecuteNonQuery does not state that it is asynchronous.  The MSFT FCL documentation convention is to state this fact when a method processes asynchronously.  Also, FCL asynchronous method names usually start with the word 'Begin'.  These two facts make it fairly safe to assume that the function is synchronous and not asynchronous.   The class does provide a 'CommandTimeout' property that can be used to cause an exception which will be returned as an error by WinBatch, if the command is not completed within the allotted time.

Understood. But the question is more about what does WB do between
 
cm.ExecuteNonQuery()

cm.dispose()

The CommandTimeOut defaults to 30 seconds, so does WB put up a 30 sec TimeDelay, or wait for an error?

td

  • Tech Support
  • *****
  • Posts: 2996
    • WinBatch
Re: SQL Server ExecuteNonQuery() state?
« Reply #3 on: January 11, 2014, 09:11:29 am »
As I said, the the method's MSFT documentation indicates that it is synchronous.  That should be sufficient information to answer the question "what does WB do...?" but apparently not.  I recommend gaining an understanding of the difference between synchronous and asynchronous method calls. It is fundamental.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

  • Tech Support
  • *****
  • Posts: 2996
    • WinBatch
Re: SQL Server ExecuteNonQuery() state?
« Reply #4 on: January 11, 2014, 09:21:56 am »
Here is a link to MSFT's asynchronous programming model discussion:

http://msdn.microsoft.com/en-us/library/jj152938(v=vs.110).aspx

Of course, this does not apply to your method since it is synchronous like the majority of CLR and COM methods, and almost all WinBatch functions and operators.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

DAG_P6

  • Full Member
  • ***
  • Posts: 184
  • WinBatch rocks, and so does Wilson WindowWare!
    • WizardWrx
Re: SQL Server ExecuteNonQuery() state?
« Reply #5 on: January 25, 2014, 12:17:43 pm »
I've watched ExecuteNonQuery run in the CLR debugger, and it ran synchronously; the statement pointer didn't advance until the query completed.
David A. Gray
You are more important than any technology.