Commit and Rollback?

Hi,

I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:

Product:

--

productID

(...)

Acessories:

-

acessID

(...)

ProductAcess:

--

productID

acessID

So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess

fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks

hi,

you can use transactions in Ado.Net as well, similarly to the trivial code snippet like

SqlConnection con = new SqlConnection(""); SqlCommand cmd = new SqlCommand(); SqlTransaction tx = con.BeginTransaction; cmd.Connection = con; cmd.Transaction = tx; int Res = cmd.ExecuteNonQuery(); cmd.Dispose; cmd = null; if (Res == 0) tx.Rollback; else tx.Commit; tx.Dispose; tx = null; con.Dispose; con = null;

regards