Sql事务问题 [英] Problem with Sql transactions

问题描述

你好！

我正在开发一个应用程序，其中我必须将组成员的名称保存到除主表之外的另一个表中(因为已完成标准化).

现在我正在使用sql事务将名称保存到表中.
两个表的表结构分别是


对于VisitorPass表

Hello!!

I am working on an application in which i have to save the name of members of a group to another table other then the main table(as normalization is done ) .

Now i am using sql transactions to save the names into table.
The table structure of two tables are


For VisitorPass Table

unqid || PassNo || PassType || VisitorName || ToMeet || Department 

和其他一些领域


对于GroupMember表

and some other fields


For GroupMember Table

unqid || PassNo || GroupMemberName

现在，我有两个存储过程，可以将数据输入这些表中.在5月的Class文件中，我制作了一些函数，这些函数从cmd.ExecuteNonQuery.ToString()(0或1)返回字符串值.

现在，如果passType为Group，则我必须将成员的名称保存在GroupMembers表中.
我已经做到了这里

Now i have two stored procedures to enter data into these tables. In may Class file i have made functions which return String values from cmd.ExecuteNonQuery.ToString() (0 or 1)

Now if the passType is Group the i will have to save the names of members in GroupMembers Table.
I have done it upto here

objMem.unqid = Convert.ToInt64(0);
           objMem.Passno = Convert.ToInt64(txtPassNo.Text);
           for (int i = 1; i<=Name.Count;i++ )
           {
               objMem.Visitorname = Name[1];
               con.Open();
               trns = con.BeginTransaction();
               y = objMem.fnSaveGroupMembers(trns);
           }

我需要知道的是，我可以将事务提交到foreach循环之外吗?如果可以，它将保存所有名称还是仅保存最后一个?如果通过类型为组，我将在两个表中保存数据，因此仅当两个表都获得数据时，才应提交事务.
还是可以建议我尝试其他方法?

What i need to know is can i commit the transaction out of the foreach Loop and if so will it save all the name or just the last one? I will be saving data in both the tables if pass types is group so the transactions should commit only if both the tables get data.
Or can you suggest some other approach that i should try ???

推荐答案

您有两种选择，具体取决于您是否要将每个对象保存为原子操作，或者是否要将整个更新都视为一个.

在第一种情况下，您应该将单个更新包装到事务中的两个表中.作为存储过程，这实际上可能会更好，但是用代码术语来说，我认为这是循环内的事务(在向两个表中写入一行后提交).

在第二个步骤中，您将在进行任何保存之前创建一个事务，然后在所有保存之后进行提交.

无论如何，您都不应该在循环内调用Connection.Open.​​

You have two options, depending on whether you want to treat saving each object as an atomic operation, or whether you want to treat the entire update as one.

In the first case you should wrap a single update into both tables in a transaction. This would probably actually be better as a stored procedure, but in code terms that would be, I think, a transaction inside the loop (committed after writing one row into both tables).

In the second, you would create a transaction before doing any saves and commit it after all of them.

In any case you shouldn''t be calling Connection.Open inside the loop.

这篇关于Sql事务问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！