Best way to update/insert/delete in sqlserver database

[?scar Martins]

Hello

I've passed the last days going around with xml, sqlxml, uppdategrams,
diffgrams, transactions, sp's, everything, I suppose, but still have
not a clear line of thoughts...

I惴 new in the info systems world.. I'm currently developing an
windows app in vb.net running over sqlserver 2000. Till now I was
using simple inserts, updates and deletes in db tables (not much so
far) but now that performance and data consistency issues are being
discussed, I was trying to understand the best way to pass data
to/from database.

Clearly I want to have the business logic in server side, being
executed by storedprocedures, but in large updates, inserts and
deletes I must pass many data and that it's a problem.

For an update I was doing something like this:

Try

loTransaction = lcnPlada.BeginTransaction()
lcmActualiza.Transaction = loTransaction

For Each loLinha In tdtActualizaHistorico.Rows
lcmActualiza.CommandText = "insert into
stetiquetahistorico (encomenda,produto,tamanho,cor,etiqueta,etiquetaemissor,dataemissaoetiqueta,etiquetaqtd,ficheiroemissao)
" & _
"values (" & CType(loLinha(0), Integer) &
"," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
Integer) & "," & tnNumeroEmissao + 1 & ")"

lcmActualiza.ExecuteNonQuery()
Next

loTransaction.Commit()

Return True

end try

But have logic (a for each) inside a transaction it愀 not a good idea,
right?

I've seen many different examples of adodb objects constructing a
stream with the xml statements that executes a sp and maps the table
columns to perform updates or inserts and then

adodb.command.commandstream = stream
adodb.command.execute

and in server side creates a sp that

sp_xml_preparedocument
...
insert....

sp_xml_removedocument

Sqlserver books online has an example of it that works correct in
northwind database but what about using sqlcommand??? or
sqlxmlcommand??

Can I use a sqlxmlcommand to execute a stream that was written in
code, instead of using templates??

Is there other ways than sqlxml to perform this kind of tasks?

Anybody can help me, please?

Thanks in advance

[Michael Rys [MSFT]]

sp_xml_preparedocument/OpenXML is a T-SQL feature. So it is independent of
what client you are using to connect to the database. Steps are:

1. Write your stored proc to do the logic
2. Use the normal way that your client requires to pass arguments and
execute the stored proc.

You only need the command stream object if you are using FOR XML to get XML
back from the server.

HTH
Michael

"?scar Martins" <subd...@hotmail.com> wrote in message
news:229f0a23.04101...@posting.google.com...