[RESOLVED]Commit Assitance

[nige]

Hey all,

I need to start using Commit, and rollback functionality to my code and need some advice in how best to implement it.

Currently I have two tables, a header table and a detail table. I first insert a header record, recording the primary key in a field and then I loop through all the details rows inserting them in to the detail table.

If there is any problems with the insertions along the way I need a way to rollback in order to cancel all insertions and display an error message to the user.

The following code is pretty much how I currently do it minus the commit, rollback code.

I use VB.Net 2008, MS SQL Server.

Many Thanks

Dim connBranch As SqlClient.SqlConnection
Dim cmdBranch As New SqlClient.SqlCommand

connBranch = New SqlClient.SqlConnection(<Connection String>)
connBranch.Open()

sBldQuery = New System.Text.StringBuilder

sBldQuery.AppendLine("INSERT INTO")
sBldQuery.AppendLine("       mytable1")
sBldQuery.AppendLine("           (")
sBldQuery.AppendLine("            <fields>")
sBldQuery.AppendLine("           )")
sBldQuery.AppendLine("      VALUES")
sBldQuery.AppendLine("           (")
sBldQuery.AppendLine("            <data>")
sBldQuery.AppendLine("           );")
sBldQuery.AppendLine("")
sBldQuery.AppendLine("SELECT Scope_Identity();")


cmdBranch.CommandText = sBldQuery.ToString
cmdBranch.CommandType = CommandType.Text
cmdBranch.Connection = connBranch

pkReturned = Integer.Parse(cmdBranch.ExecuteScalar().ToString)

For Each lvItem As ListViewItem In listview1.Items
	sBldQuery = New System.Text.StringBuilder

	sBldQuery.AppendLine("INSERT INTO")
	sBldQuery.AppendLine("       mytabledetail")
	sBldQuery.AppendLine("           (")
	sBldQuery.AppendLine("            <fields>")
	sBldQuery.AppendLine("           )")
	sBldQuery.AppendLine("      VALUES")
	sBldQuery.AppendLine("           (")
	sBldQuery.AppendLine("            <data>,")
	sBldQuery.AppendLine("            pkReturned") 
	sBldQuery.AppendLine("           );")

	cmdBranch.CommandText = sBldQuery.ToString

	iNumberOfRowsAffected = cmdBranch.ExecuteNonQuery()
Next

[nige]

I guess I could record every new PK inserted and then go back and delete them if there's a problem. But that would be a bit of a pain....?

Anyone?

[techgnome]

Look at your connection object...
http://msdn.microsoft.com/en-us/libr...onnection.aspx

In it, you will find a BeginTransaction, which returns a SQLTransaction object... which then has Commit and Rollback methods...

-tg

[nige]

Thanks Techgome for the pointer.

For anyone else, I used the following example:

http://msdn.microsoft.com/en-us/library/86773566.aspx