如何在sqltransaction中读取未提交的事务？

Question

在我的.net Framework2.0 c#代码中使用SQLTransaction时遇到了一个问题

这是我的代码：

public bool register()
    {
        SqlConnection conn = DB.getInstance().getConnection();
        conn.Open();
        SqlTransaction sqlTransaction = conn.BeginTransaction();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.Transaction = sqlTransaction;          

        try
        {
            cmd = insertMembers(cmd);
            cmd.ExecuteNonQuery();

            SqlDataReader read = null;
            cmd.CommandText = "SELECT * FROM members WHERE username='" + username + "'";
            read = cmd.ExecuteReader();
            while (read.HasRows)
            {
                id0 = (int)read["id0"];
            }

            cmd = insertMembersBalance(cmd);
            cmd.ExecuteNonQuery();

            cmd = insertMembersEPoint(cmd);
            cmd.ExecuteNonQuery();

            cmd = insertMembersVerify(cmd);
            cmd.ExecuteNonQuery();

            reset();

            sqlTransaction.Commit();
        }
        catch(Exception e)
        {                
            sqlTransaction.Rollback();
            Console.WriteLine(e.ToString());
            return false;
        }
        finally
        {
            conn.Close();
        }

        return true;
    }

我无法从members表中获取用于将另一个记录插入到另一个表中的id。

有没有其他的解决方案？

Answer 1

必须先调用dr.Read()，然后再调用SqlDataReader dr = cmd.........

Answer 2

        if (read.HasRows)  // needs to be if not while or it will just loop
        {
             read.Read();
             id0 = (int)read["id0"];
        }
        read.Close();      // need to close the reader before you can use the cmd

        if you want to loop through all rows then 

        while (read.Read())
        {
             id0 = (int)read["id0"];
        }