data acess class

hi
i was reading book about asp.net and i found example for a dataacess class but i didn't understand this part

Private m_FieldData As New NameObjectCollection
Private _m_ConnectionString As String
Private m_dbConnection As SqlConnection
Private Sub AddParameters( _
ByVal objCommand As SqlCommand, _
ByVal objValues() As Object)

Dim objValue As Object
Dim I As Integer
Dim objParameter As SqlParameter

objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)

I = 0
For Each objParameter In objCommand.Parameters
If objParameter.Direction = ParameterDirection.Input _
Or objParameter.Direction = _
ParameterDirection.InputOutput Then

objValue = objValues(I)
objParameter.Value = objValue
I = I + 1
End If
Next
End Sub

Private Sub AddFieldParameters _
(ByVal objCommand As SqlCommand)

Dim objParameter As SqlParameter

objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)

For Each objParameter In objCommand.Parameters
objParameter.Value = _
_FieldData.Item(objParameter.ParameterName. _
Substring(1))
Next
End Sub
Public Function ExecDataReader _
(ByVal strStoredProc As String, _
ByVal ParamArray objValues() As Object) _
As SqlDataReader

Dim objCommand As SqlCommand
Dim objReader As SqlDataReader

objCommand = New SqlCommand

objCommand.CommandText = strStoredProc
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = dbConnection

Try
objCommand.Connection.Open()
If (objValues.Length = 0) Then
AddFieldParameters(objCommand)
Else
AddParameters(objCommand, objValues)
End If
objReader = objCommand. _
ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
If objCommand.Connection.State.Open Then
objCommand.Connection.Close()
End If
End Try

Return objReader
End Function
can anybody help me what the author want to doTamer, can you be more specific?

Thanks|||hi i don't understand sub AddParameters and addfieldparameter
and why to add values as an array
and the parameter have only one single value why to pass an array of object i can pass a single value hope u understand me|||I'm going to have to kind of guess here as I don't know the source or the context of the script. That being said

Looking at just the AddFieldParameters sub:
---------------------------------
Private Sub AddFieldParameters (ByVal objCommand As SqlCommand)

Dim objParameter As SqlParameter

objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)

For Each objParameter In objCommand.Parameters
objParameter.Value = FieldData.Item(objParameter.ParameterName.Substring(1))
Next
End Sub
---------------------------------

I read this as the SQL command is being passed into the sub as a value and that value can have multiple lines to it. The FieldData object is being created with each line of the SQL command as a different entry in the array.

The AddParameters looks pretty much the same.

Does this answer your question?

I can look into this further if you provide me with the origional source and location of the script.

Thanks

Dangers of Client batch cancellation

After reading Erland Sommarskog's most enlightening articles on SQL Server
2000's error handling capabilities
(http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html) I have a question regarding
batch cancellation caused by a client.

Because a batch cancel request from a client does not cause a rollback, is
there any danger of the commands that were executed in the batch before the
client cancelled to be persistent in the database since no commit is ever
called? Or is the real problem indefinite locking that only disappears when
the client connection is terminated?

Any insights are appreciated!

Regards,

Tyler
Tyler Hudson wrote:

> After reading Erland Sommarskog's most enlightening articles on SQL Server
> 2000's error handling capabilities
> (http://www.sommarskog.se/error-handling-I.html and
> http://www.sommarskog.se/error-handling-II.html) I have a question regarding
> batch cancellation caused by a client.
> Because a batch cancel request from a client does not cause a rollback, is
> there any danger of the commands that were executed in the batch before the
> client cancelled to be persistent in the database since no commit is ever
> called? Or is the real problem indefinite locking that only disappears when
> the client connection is terminated?
> Any insights are appreciated!

If a batch is being executed and you do a cancel, everything done before the
cancel is acknowledged and executed is still done. If a tx was underway, it still
is, and the updates and locks are still in effect until you commit or rollback,
at which time everything that was done during the tx and all locks obtained,
are released.
If a batch is executed in a non-transactional mode, I believe that everything
done by the batch is permanently done. No locks remain after the cancel.
Joe Weinstein at BEA
>
> Regards,
>
> Tyler|||So let's say the client disconnects after issuing the cancel command. Would
SQL server roll back the commands executed before the cancel command?

"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:40F6AC1A.3040303@.bea.com...
>
> Tyler Hudson wrote:
> > After reading Erland Sommarskog's most enlightening articles on SQL
Server
> > 2000's error handling capabilities
> > (http://www.sommarskog.se/error-handling-I.html and
> > http://www.sommarskog.se/error-handling-II.html) I have a question
regarding
> > batch cancellation caused by a client.
> > Because a batch cancel request from a client does not cause a rollback,
is
> > there any danger of the commands that were executed in the batch before
the
> > client cancelled to be persistent in the database since no commit is
ever
> > called? Or is the real problem indefinite locking that only disappears
when
> > the client connection is terminated?
> > Any insights are appreciated!
> If a batch is being executed and you do a cancel, everything done before
the
> cancel is acknowledged and executed is still done. If a tx was underway,
it still
> is, and the updates and locks are still in effect until you commit or
rollback,
> at which time everything that was done during the tx and all locks
obtained,
> are released.
> If a batch is executed in a non-transactional mode, I believe that
everything
> done by the batch is permanently done. No locks remain after the cancel.
> Joe Weinstein at BEA
> > Regards,
> > Tyler|||
Tyler Hudson wrote:

> So let's say the client disconnects after issuing the cancel command. Would
> SQL server roll back the commands executed before the cancel command?

If the connection was running in a transactional state, then the DBMS would find the
client had gone away, and would roll back the tx and anything it had done,
whether the cancel had been sent or not, though depending on how big the batch
was, the cancel might save some time.
If the connection was not in a transactional state, whatever was done before the
cancel or disconnect stays done.
Joe

>
> "Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
> news:40F6AC1A.3040303@.bea.com...
>>
>>Tyler Hudson wrote:
>>
>>
>>>After reading Erland Sommarskog's most enlightening articles on SQL
> Server
>>>2000's error handling capabilities
>>>(http://www.sommarskog.se/error-handling-I.html and
>>>http://www.sommarskog.se/error-handling-II.html) I have a question
> regarding
>>>batch cancellation caused by a client.
>>>
>>>Because a batch cancel request from a client does not cause a rollback,
> is
>>>there any danger of the commands that were executed in the batch before
> the
>>>client cancelled to be persistent in the database since no commit is
> ever
>>>called? Or is the real problem indefinite locking that only disappears
> when
>>>the client connection is terminated?
>>>
>>>Any insights are appreciated!
>>
>>If a batch is being executed and you do a cancel, everything done before
> the
>>cancel is acknowledged and executed is still done. If a tx was underway,
> it still
>>is, and the updates and locks are still in effect until you commit or
> rollback,
>>at which time everything that was done during the tx and all locks
> obtained,
>>are released.
>> If a batch is executed in a non-transactional mode, I believe that
> everything
>>done by the batch is permanently done. No locks remain after the cancel.
>>Joe Weinstein at BEA
>>
>>>
>>>Regards,
>>>
>>>
>>>Tyler
>>>
>>>
>>|||Tyler Hudson (TylerH@.Spam.MeNOTallpax.com) writes:
> Because a batch cancel request from a client does not cause a rollback,
> is there any danger of the commands that were executed in the batch
> before the client cancelled to be persistent in the database since no
> commit is ever called? Or is the real problem indefinite locking that
> only disappears when the client connection is terminated?

The most likely problem is the indefinite locking. But of course you
could run into data being persisted that you did not intend to persist.
Assume that further afield you have another error where you incorrectly
commit one time too much.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tyler Hudson (TylerH@.Spam.MeNOTallpax.com) writes:
> So let's say the client disconnects after issuing the cancel command.
> Would SQL server roll back the commands executed before the cancel
> command?

Uncommitted transactions would be rolled back. But anything that already
has been committed will of course not be rolled back. Say that the user
submits a call to a stored procedures that performs this:

BEGIN TRANSACTION A
-- do some stuff
COMMIT TRANSACTION A

BEGIN TRANSACTION B
-- do some stuff
COMMIT TRANSACTION B

UPDATE tbl SET ...

BEGIN TRANSACTION C
-- do some stuff
BEGIN TRANSACTION D
-- do some other stuff
COMMIT TRANSACTION D
-- do even more stuff
COMMIT TRANSACTION C

Say now that the timeout sets in while transaction C is in progress, and
the client then disconnects. Transactions A, B and the UPDATE statement
will remain, but there will be nothing of transaction C. Note that this
applies even if COMMIT TRANSACTION D has been committed, because COMMIT
for a nested transaction only decrements the transaction level. Nothing
is committed.

If the timeout sets in while the UPDATE command is running, transaction
A and B will remain, but there will be no trace of the UPDATE statement.
(And of course not of transactions C and D.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp