// add mjm

        private void ProcessChildren(object o, int iRowId)
        {
            try
            {
                if (o is sNode)
                {
                    sNode s = (sNode)o;



                    foreach (object o1 in s.aColl)
                    {
                        if (o1 is sKeyValue)
                        {
                            sKeyValue sk = (sKeyValue)o1;
                            cmd.CommandText = "usp_AddXmlDtl";
                            SqlCommandBuilder.DeriveParameters(cmd);
                            cmd.Parameters["@unid"].Value = sCurrentUnid;
                            if (sk.bProduct)
                            {
                                cmd.Parameters["@sheet"].Value = "Y";
                            }


                            cmd.Parameters["@rowid"].Value = 0;
                            cmd.Parameters["@att"].Value   = sk.sAttributes;
                            cmd.Parameters.Add("@res", SqlDbType.Int);
                            cmd.Parameters["@res"].Value     = 0;
                            cmd.Parameters["@res"].Direction = ParameterDirection.ReturnValue;

                            cmd.Parameters["@val"].Value      = sk.sValue;
                            cmd.Parameters["@key"].Value      = sk.sKey;
                            cmd.Parameters["@id"].Value       = iRowId;
                            cmd.Parameters["@parentid"].Value = iParentid;
                            cmd.ExecuteNonQuery();

                            if ((int)(cmd.Parameters["@res"].Value) != 0)
                            {
                                break;
                            }
                        }
                        if (o1 is sNode)
                        {
                            ProcessChildren(o1, iRowId);
                        }
                    }
                }
                if (o is sKeyValue)
                {
                    sKeyValue sk = (sKeyValue)o; cmd.CommandText = "usp_AddXmlDtl";
                    SqlCommandBuilder.DeriveParameters(cmd);
                    cmd.Parameters["@unid"].Value    = sCurrentUnid;
                    cmd.Parameters["@att"].Value     = sk.sAttributes;
                    cmd.Parameters["@rowid"].Value   = 0;
                    cmd.Parameters["@issheet"].Value = "N";
                    cmd.Parameters.Add("@res", SqlDbType.Int);
                    cmd.Parameters["@res"].Value     = 0;
                    cmd.Parameters["@res"].Direction = ParameterDirection.ReturnValue;

                    cmd.Parameters["@val"].Value = sk.sValue;
                    cmd.Parameters["@key"].Value = sk.sKey;
                    cmd.Parameters["@id"].Value  = iRowId;

                    cmd.ExecuteNonQuery();
                }
            }


            catch (Exception ex)
            {
            }
        }
        public void DBProcess()
        {
            string sConnSrv = LoadSingleUnid.Properties.Settings.Default.Host;
            string sConnStr = LoadSingleUnid.Properties.Settings.Default.Connectionstr;
            int    irowid   = 0;

            cn  = new SqlConnection(sConnStr);
            cmd = new SqlCommand();


            try
            {
                cn.Open();

                cmd.CommandText = "delete from dbo.XMLParmsDtl ; delete from dbo.XMLParmsHdr ;";
                cmd.CommandType = CommandType.Text;
                cmd.Connection  = cn;
                cmd.ExecuteNonQuery();

                foreach (sNode s in sView.aList)
                {
                    sCurrentUnid = s.sUnid;


                    try
                    {
                        cmd.CommandText = "usp_AddXmlHdr";
                        cmd.CommandType = CommandType.StoredProcedure;

                        SqlCommandBuilder.DeriveParameters(cmd);

                        cmd.Parameters["@unid"].Value = s.sUnid;
                        cmd.Parameters["@xml"].Value  = s.val;
                        cmd.Parameters["@res"].Value  = 0;
                        cmd.ExecuteNonQuery();
                        irowid = (int)cmd.Parameters["@res"].Value;
                        // Load the current Parent this will change
                        iParentid = irowid;


                        //     foreach (sKeyValue sk in s.aColl)
                        foreach (object o in s.aColl)
                        {
                            if (o is sKeyValue)
                            {
                                sKeyValue sk = (sKeyValue)o;
                                Debug.Print("SK Name {0}", sk.sKey);


                                cmd.CommandText = "usp_AddXmlDtl";
                                SqlCommandBuilder.DeriveParameters(cmd);
                                cmd.Parameters["@unid"].Value = sCurrentUnid;

                                cmd.Parameters["@rowid"].Value = 0;
                                cmd.Parameters.Add("@res", SqlDbType.Int);
                                cmd.Parameters["@res"].Value      = 0;
                                cmd.Parameters["@res"].Direction  = ParameterDirection.ReturnValue;
                                cmd.Parameters["@sheet"].Value    = "N";
                                cmd.Parameters["@val"].Value      = sk.sValue;
                                cmd.Parameters["@key"].Value      = sk.sKey;
                                cmd.Parameters["@id"].Value       = irowid;
                                cmd.Parameters["@parentid"].Value = 0;
                                cmd.Parameters["@att"].Value      = sk.sAttributes;
                                cmd.ExecuteNonQuery();


                                if (sk.bHasChildren)
                                {
                                    iParentid = (int)cmd.Parameters["@rowid"].Value;
                                }

                                if ((int)(cmd.Parameters["@res"].Value) != 0)
                                {
                                    break;
                                }
                            }
                            if (o is sNode)
                            {
                                ProcessChildren(o, irowid);
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                cn.Close();
                cn.Dispose();
            }
        }
        private void LoopThruChildren(XmlNode rootnode, ref sNode s)
        {
            Debug.Print(rootnode.Name);
            int iEvalChildren = 0;

            if (rootnode.Name == "unid")
            {
                // Got the unid
                s.sUnid = rootnode.InnerText;
            }
            if (rootnode.Attributes != null)
            {
                foreach (XmlAttribute a in rootnode.Attributes)
                {
                    if (a.Name == "product" && a.Value == "Y")
                    {
                        s.isSHeets = true;
                    }
                }
                if (sCurrentParent == "" && rootnode.Name == "sheets")
                {
                    sKeyValue sMystruc = new sKeyValue();
                    sMystruc.sKey         = rootnode.Name;
                    sMystruc.bHasChildren = true;
                    sMystruc.sValue       = rootnode.InnerXml;
                    sMystruc.sAttributes  = "";
                    //     if (rootnode.Attributes["Product"].Value == "Y")
                    //          s.isSHeets = true;
                    if (rootnode.Attributes != null)
                    {
                        foreach (XmlAttribute att in rootnode.Attributes)
                        {
                            sMystruc.sAttributes += att.Value + "$";
                        }
                    }
                    s.aColl.Add(sMystruc);
                    sCurrentParent = rootnode.Name;
                }
                foreach (XmlNode x in rootnode.ChildNodes)
                {
                    Debug.Print("The name is {0}, value is {1}", x.ParentNode.Name, x.InnerText);
                    // mjm 21 Dec 2010
                    // if there is no value just blow through to the next one.

                    if (x.InnerText == null)
                    {
                        continue;
                    }
                    iEvalChildren = x.ChildNodes.Count;

                    if (iEvalChildren > 0)
                    {
                        iEvalChildren = 1;
                    }
                    foreach (XmlAttribute a in rootnode.Attributes)
                    {
                        if (a.Name == "Product" && a.Value == "Y")
                        {
                            s.isSHeets = true;
                        }
                    }

                    switch (iEvalChildren)
                    {
                    case 1:
                        // store the Parent in the collection as an anchor

                        Debug.Print("Has Children");



                        sNode s1 = new sNode("");
                        s1.sUnid    = s.sUnid;
                        s1.val      = x.InnerXml.ToString();
                        s1.isSHeets = s.isSHeets;
                        s1.sParent  = sCurrentParent;
                        s.bChildren = true;
                        LoopThruChildren(x, ref s1);
                        s.aColl.Add(s1);
                        break;



                    default:
                        if (x.ChildNodes.Count == 0)
                        {
                            sCurrentParent = "";
                            sKeyValue sMystruc1 = new sKeyValue();
                            if (s.isSHeets == true)
                            {
                                sMystruc1.bProduct = true;
                            }
                            sMystruc1.sKey        = x.ParentNode.Name;
                            sMystruc1.sValue      = x.InnerText;
                            sMystruc1.sAttributes = "";
                            if (x.Attributes != null)
                            {
                                foreach (XmlAttribute att in x.Attributes)
                                {
                                    sMystruc1.sAttributes += att.Value + "$";
                                }
                            }
                            s.aColl.Add(sMystruc1);
                        }
                        break;
                    }
                }



                foreach (XmlNode x in rootnode.ChildNodes)
                {
                    LoopThruChildren(x, ref s);
                }
            }
        }