public void DBProcess()
{
int irowid = 0;
cn = new SqlConnection(Properties.Settings.Default.Connectionstr);
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;
// string scmd = "";
// scmd = "INSERT INTO [dbo].[XMLParmsHdr]"
//+ "([unid]"
//+ " ,[xmlFld]"
//+ ",[crtdate])"
//+ " VALUES "
//+ s.sUnid.ToString() + ","
//+ s.val.ToString() + ","
//+ "getdate())";
// cmd.CommandText = scmd;
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)
{
Console.WriteLine("Error - {0}", ex.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error - {0}", ex.Message);
}
}
}
catch (SqlException ex)
{
}
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;
//case "text":
// // s.aColl.Add(x.InnerXML);
// break;
//case "CustomerName":
// s.aColl.Add(x.InnerText);
// break;
//case "entrydata":
// s.aColl.Add(x.InnerText);
// break;
//case "UserName":
// s.aColl.Add(x.InnerText);
// break;
//case "ConnectionString":
// s.aColl.Add(x.InnerText);
// break;
//case "UserType":
// s.aColl.Add(x.InnerText);
// break;
//case "BudSummary":
// s.aColl.Add(x.InnerText);
// break;
//case "BudDet":
// s.aColl.Add(x.InnerText);
// break;
//case "BudCn":
// s.aColl.Add(x.InnerText);
// break;
//case "PO":
// s.aColl.Add(x.InnerText);
// break;
//case "AbsenceSettings":
//s.aColl.Add(x.InnerText);
//break;
//case "Substitues":
// s.aColl.Add(x.InnerText);
// break;
//case "MasterEmployee":
// s.aColl.Add(x.InnerText);
// 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);
}
}
}
private void ProcessChildren(object o, int iRowId)
{
try
{
if (o is sNode)
{
sNode s = (sNode)o;
// MJM
// Added to reflect Heirarchies
// 30 Dec 2010
//cmd.CommandText = "usp_AddXmlDtl";
//SqlCommandBuilder.DeriveParameters(cmd);
//cmd.Parameters["@unid"].Value = s.sUnid;
//cmd.Parameters.Add("@res", SqlDbType.Int);
//cmd.Parameters["@res"].Value = 0;
//cmd.Parameters["@res"].Direction = ParameterDirection.ReturnValue;
//cmd.Parameters["@val"].Value = s.val;
//cmd.Parameters["@key"].Value = s.sUnid;
//cmd.Parameters["@id"].Value = iRowId;
//cmd.Parameters["@parentid"].Value = 0;
//cmd.ExecuteNonQuery();
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)
{
}
}