Passing collections to a stored procedure

[aimlion11yahoo]

I have a number of collection each holding objects of different types. I need to populate multiple tables in the database with the contents of these collections, and I want to use a stored procedure so I can rollback the transaction if any part of it fails. The database is SQL Server 2000. My question is, how do you pass collections and objects to a stored procedured?

 

[BrianB]

Why not use a for each loop and just call the sproc multiple times?


Begin trans

for each myObj in myCollection
mysproc.addParam, yadda...yadda...
mySproc.Execute()
Next myObj

Commit Trans

Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com

 

[aimlion11yahoo]

I'll answer my own question here. It is best to write the information to datatables, then create relationships between them using a dataset, then write the dataset to an XMLDataDocument, and pass the XMLDataDocument to the stored procedure.

 

[BrianB]

You could, but would you rather code your app in VB.NET or T-SQL?

You get better error handling, and better debugging with VB.NET.

T-SQL is barbaric by comparison.


Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com

 

[chrissie1]

Yes of course it is barbaric ;-). In the sense that T-SQl 2000 is a procedural langauge. But that was the whole point of SQL, so all SQL is in a sense barbaric compared to languages like Java, C and VB.NET. But SQL was made for one purpose only and not for what some people want to make of it. SQl-server is suppossed to store the raw data and T-SQL is meant to retrieve and save the data it was never meant to write routines in it (perhaps that has improved in 2005). And I don't see a point, I think that aimlion11yahoo should do what he has to do in his DAL.

Just my 2cents.

Christiaan Baes
Belgium

"My new site" - Me

 

[aimlion11yahoo]

Thanks for the feedback and suggestions. Yes, I sure would like to do it in code, but here is the catch: I am placing an order and there are 9 tables in the database that need to be updated so I need to rollback the transaction if there is an error at any point in the transaction. Does that make sense?

 

[BrianB]

Easy as pie:

try

Begin trans

Save Customer
Save Order
Save Shipping Info

Commit Trans

Catch

Rollback Trans
Log Error

Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com

 

[aimlion11yahoo]

Thanks, BrianB. That looks very attractive to me. Think I'll give it a try.

 

[aimlion11yahoo]

I had a little problem getting the .NET syntax right. Though I would post this example from Microsoft.

Public Sub RunSqlTransaction(myConnString As String)
    Dim myConnection As New SqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As SqlCommand = myConnection.CreateCommand()
    Dim myTrans As SqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As SqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub 'RunSqlTransaction

 

[Qik3Coder]

did you have a problem with YOUR code? Or with Microsoft's?
There's no point in posting the Microsoft code, if you don't understand your code.

If you would like to highlight a line or two, and ask "What does this do, well, that's different"

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[Tarwn]

(perhaps that has improved in 2005)

Actually with MS SQL 2005 you can embed .net in SQL Server to extend your stored procedures. Personally i find that a lot more barbaric than using a procedural language, and I'm a porgrammer at a .Net house, not a dba

 

[aimlion11yahoo]

Qik3Coder,

For others who might come across this post, I thought I would post a more detailed example than BrianB's pseudo code. I hope that is alright to do?