Concurrency issues in trees

Hi NG,
My indented Bill of Materials (BOM) report has been working for some time
now. It is time to deal with the issue of multiuser operations and
concurrency. My approach has been to use the tree spanning algorithm
suggested by Steve Kass. This stored procedure builds a table called
'ibom'. Next, my application reads ibom and places the result in the client
machine's XML file. Finally, a Crystal Report reads the XML file and prints
the report. This approach works fine as long as there is only one user
running the application. All hell would break loose if two or more users
were constructing the ibom table. The code at the end of this post shows my
C# application first building ibom and then reading ibom. No harm would
come if the ibom table was locked from the point of its creation until the
last row was read by my method WriteXML(). How do you set a lock in the
stored procedure TREESPAN (not shown in this post) and remove it after the
execution of WriteXML()? It is perfectly OK to DROP TABLE ibom after the
WriteXML(). Also note that this code will be infrequently run so locking
ibom will have little effect on performance. However, a garbaged ibom table
would be a disaster!
Thank you,
Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
bob@.s-i-inc.com
781/329-4828
private void LoadIbom(string treeSpan)
{
//--Call the stored procedure--
mcs3Connection.Open();
SqlCommand cmd = new SqlCommand(treeSpan, mcs3Connection);
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
reader.Close();
mcs3Connection.Close();
}
//--Method to getting ibom table and writing XML--
private void WriteXML()
{
//--SQL code to get ibom table--
selectStmt = "SELECT "
+ "ps_PartIndex, "
+ "ps_ParentIndex, "
+ "ps_PartNo, "
+ "ps_Qty_Per, "
+ "Level, "
+ "ps_Effectivity, "
+ "ps_Add_Del, "
+ "ps_Ref, "
+ "path "
+ "FROM ibom "
+ "ORDER BY path ";
SqlCommand selectCmd = new SqlCommand(selectStmt, mcs3Connection);
mcs3Connection.Open();
try
{
bomReader = selectCmd.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n\n"
+ ex.GetType().ToString() + "\n\n"
+ ex.StackTrace, "Exception");
mcs3Connection.Close();
}
while (bomReader.Read()) //read entire ibom table
{
strComponent = (string) bomReader["ps_PartNo"];
itemMaster = ItemMasterTbl.GetItemMaster(strComponent);
//format issues corrected here
intLevel = (int) bomReader["Level"];
if (intLevel == 0) BlankRow();
strComponent = " ".PadLeft(intLevel*3) + strComponent;
strLevel = intLevel.ToString();
decQtyPer = (decimal) bomReader["ps_Qty_Per"];
if (decQtyPer == 0) strQtyPer = "0.000000";
else strQtyPer = decQtyPer.ToString();
counter++;
xmlOut.WriteStartElement("PS1307");
xmlOut.WriteAttributeString("ps_component", strComponent);
xmlOut.WriteElementString("ps_parent", strDummy);
xmlOut.WriteElementString("ps_qty_per", strQtyPer);
xmlOut.WriteElementString("ps_desc", (string) itemMaster.IM_Desc);
xmlOut.WriteElementString("ps_phantom_flag", (string)
itemMaster.IM_phantom_flag);
xmlOut.WriteElementString("ps_UOM", (string) itemMaster.IM_UOM);
xmlOut.WriteElementString("ps_pur_mfg_code", (string)
itemMaster.IM_pur_mfg_code);
xmlOut.WriteElementString("ps_effectivity", (string)
bomReader["ps_Effectivity"]);
xmlOut.WriteElementString("ps_add_delete", (string)
bomReader["ps_Add_Del"]);
xmlOut.WriteElementString("ps_ref", (string) bomReader["ps_Ref"]);
xmlOut.WriteElementString("ps_level", strLevel);
xmlOut.WriteEndElement(); //PS1307
}
mcs3Connection.Close();
}
//----robert,
you can virtually partition your ibom table by adding a column (say, 'user')
with default of suser_sid() or suser_sname(), and reference this column in
where clause:
where user=suser_sname()
also, don't drop the ibom table, but rather delete ibom where
user=suser_sname()
hth
dean
"Robert Schuldenfrei" <schuldenfrei@.comcast.net> wrote in message
news:Y4SdnaXXUMpV14_fRVn-jg@.comcast.com...
> Hi NG,
> My indented Bill of Materials (BOM) report has been working for some time
> now. It is time to deal with the issue of multiuser operations and
> concurrency. My approach has been to use the tree spanning algorithm
> suggested by Steve Kass. This stored procedure builds a table called
> 'ibom'. Next, my application reads ibom and places the result in the
client
> machine's XML file. Finally, a Crystal Report reads the XML file and
prints
> the report. This approach works fine as long as there is only one user
> running the application. All hell would break loose if two or more users
> were constructing the ibom table. The code at the end of this post shows
my
> C# application first building ibom and then reading ibom. No harm would
> come if the ibom table was locked from the point of its creation until the
> last row was read by my method WriteXML(). How do you set a lock in the
> stored procedure TREESPAN (not shown in this post) and remove it after the
> execution of WriteXML()? It is perfectly OK to DROP TABLE ibom after the
> WriteXML(). Also note that this code will be infrequently run so locking
> ibom will have little effect on performance. However, a garbaged ibom
table
> would be a disaster!
> Thank you,
> Bob
> --
> Robert Schuldenfrei
> S. I. Inc.
> 32 Ridley Road
> Dedham, MA 02026
> bob@.s-i-inc.com
> 781/329-4828
> private void LoadIbom(string treeSpan)
> {
> //--Call the stored
procedure--
> mcs3Connection.Open();
> SqlCommand cmd = new SqlCommand(treeSpan, mcs3Connection);
> cmd.CommandType = CommandType.Text;
> SqlDataReader reader = cmd.ExecuteReader();
> reader.Close();
> mcs3Connection.Close();
> }
> //--Method to getting ibom table and writing
XML--
> private void WriteXML()
> {
> //--SQL code to get ibom
table--
> selectStmt = "SELECT "
> + "ps_PartIndex, "
> + "ps_ParentIndex, "
> + "ps_PartNo, "
> + "ps_Qty_Per, "
> + "Level, "
> + "ps_Effectivity, "
> + "ps_Add_Del, "
> + "ps_Ref, "
> + "path "
> + "FROM ibom "
> + "ORDER BY path ";
> SqlCommand selectCmd = new SqlCommand(selectStmt, mcs3Connection);
> mcs3Connection.Open();
> try
> {
> bomReader = selectCmd.ExecuteReader();
> }
> catch (Exception ex)
> {
> MessageBox.Show(ex.Message + "\n\n"
> + ex.GetType().ToString() + "\n\n"
> + ex.StackTrace, "Exception");
> mcs3Connection.Close();
> }
> while (bomReader.Read()) //read entire ibom table
> {
> strComponent = (string) bomReader["ps_PartNo"];
> itemMaster = ItemMasterTbl.GetItemMaster(strComponent);
> //format issues corrected here
> intLevel = (int) bomReader["Level"];
> if (intLevel == 0) BlankRow();
> strComponent = " ".PadLeft(intLevel*3) + strComponent;
> strLevel = intLevel.ToString();
> decQtyPer = (decimal) bomReader["ps_Qty_Per"];
> if (decQtyPer == 0) strQtyPer = "0.000000";
> else strQtyPer = decQtyPer.ToString();
> counter++;
> xmlOut.WriteStartElement("PS1307");
> xmlOut.WriteAttributeString("ps_component", strComponent);
> xmlOut.WriteElementString("ps_parent", strDummy);
> xmlOut.WriteElementString("ps_qty_per", strQtyPer);
> xmlOut.WriteElementString("ps_desc", (string) itemMaster.IM_Desc);
> xmlOut.WriteElementString("ps_phantom_flag", (string)
> itemMaster.IM_phantom_flag);
> xmlOut.WriteElementString("ps_UOM", (string) itemMaster.IM_UOM);
> xmlOut.WriteElementString("ps_pur_mfg_code", (string)
> itemMaster.IM_pur_mfg_code);
> xmlOut.WriteElementString("ps_effectivity", (string)
> bomReader["ps_Effectivity"]);
> xmlOut.WriteElementString("ps_add_delete", (string)
> bomReader["ps_Add_Del"]);
> xmlOut.WriteElementString("ps_ref", (string) bomReader["ps_Ref"]);
> xmlOut.WriteElementString("ps_level", strLevel);
> xmlOut.WriteEndElement(); //PS1307
> }
> mcs3Connection.Close();
> }
> //----
>|||Hi Dean and NG,
Thank you for the "very excellent" suggestion. I am an "old dog" and I have
trouble thinking in sets of rows. I am sure that will produce the desired
result with a trivial amount of additional code. This technique will come
in handy in a number of situations beyond my immediate issue.
Sincerely,
Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
bob@.s-i-inc.com
781/329-4828
"Dean" <deanv@.nospam.spin.hr> wrote in message
news:uSMRNc5EFHA.560@.TK2MSFTNGP15.phx.gbl...
> robert,
> you can virtually partition your ibom table by adding a column (say,
> 'user')
> with default of suser_sid() or suser_sname(), and reference this column in
> where clause:
> where user=suser_sname()
> also, don't drop the ibom table, but rather delete ibom where
> user=suser_sname()
> hth
> dean
> "Robert Schuldenfrei" <schuldenfrei@.comcast.net> wrote in message
> news:Y4SdnaXXUMpV14_fRVn-jg@.comcast.com...
> client
> prints
> my
> table