• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

XML Populating with data from session

Hello experts,

I am creating a Risk Matrix that depending on what selections get made calculate a result based on the result and with the OnClick event I want to trigger a SqlBulkCopy that will add milestones to the customer if the project gets approved.. but i need some dynamic predefined values in the xml before it gets transfered. these variable come from session and is referenced as such 'Geek.ProjectID', 'Geek.UserName' etc and some may come from Querystring

I need help to get this to work in the page load event.

please find xml schema below
C#
 
    public int z = 9;
    public int y = 8;
    public int x = 7;
    public int w = 6;
    public int v = 5;
    public int u = 4;
    public int t = 3;
    public int s = 2;
    public int r = 1;  
 
  protected void Commit_OnClick(object sender, EventArgs e)
    {
 
 
        int a = Convert.ToInt32(RadioButtonList1.SelectedValue);
        int b = Convert.ToInt32(RadioButtonList2.SelectedValue);
        int c = Convert.ToInt32(RadioButtonList3.SelectedValue);
 
        if (a + b + c != z)
        {
            var dataTable = new DataTable("Customer_MERI");
 
            dataTable.Columns.Add("Project_Details_ID");
            dataTable.Columns.Add("Milestone_UserID");
            dataTable.Columns.Add("ProjectMilestones_EditedBy");
            dataTable.Columns.Add("ProjectMilestones_EditedDate");
 
            foreach (Session.Variables)
            {
                var hashtable = new Hashtable();
                gridDataItem.ExtractValues(hashtable);
 
                var dataRow = dataTable.NewRow();
                foreach (DictionaryEntry entry in hashtable)
                {
                    dataRow[entry.Key.ToString()] = entry.Value;
                }
                dataTable.Rows.Add(dataRow);
            }
 
            if (dataTable.Rows.Count > 0)
            {
                dataTable.WriteXml(Server.MapPath("~/Xml/MERI/MERI_Customer.xml"));
            }
 
            var MilestoneData = new DataSet();
            MilestoneData.ReadXml(Server.MapPath("~/XML/RiskMatrix/5milestone.xml"));
 
            var connection = new SqlConnection("CMA_project");
            var sbc = new SqlBulkCopy(connection)
                          {
                              DestinationTableName = "project_data_project_3_milestones"
                          };
 
            //if your DB col names dont match your XML element names 100%
            //then relate the source XML elements (1st param) with the destination DB cols
            //sbc.ColumnMappings.Add("campaign", "campaign_id");
            //sbc.ColumnMappings.Add("cost", "cost_USD");
 
            connection.Open();
 
            //table 4 is the main table in this dataset
            sbc.WriteToServer(MilestoneData.Tables[0]);
            connection.Close();
 
            //remove the xml file
 
        }
        else if ((a + b + c >= x) || (a + b + c <= y))
        {
 
        }
        else if ((a + b + c >= v) || (a + b + c <= w))
        {
 
        }
        else if ((a + b + c >= t) || (a + b + c <= u))
        {
 
        }
        else if (a + b + c != r)
        {
 
        }
 
    }
 
 
XML
 
 
<?xml version="1.0" encoding="utf-8" ?>
<DocumentElement>
  <Milestone>
    
    <PaymentSplitID></PaymentSplitID>
    <Project_Details_ID></Project_Details_ID>
    <Milestone_UserID></Milestone_UserID>
    <Milestone_Status_ID>1</Milestone_Status_ID>
    <MilestonePaymentInfoID></MilestonePaymentInfoID>
    <Milestone_Sequence>1</Milestone_Sequence>
    <Milestone_Begin_Date></Milestone_Begin_Date>
    <Milestone_Due_Date></Milestone_Due_Date>
    <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
    <Milestone_Notes></Milestone_Notes>
    <Percentage>100</Percentage>
    <PaymentAmount></PaymentAmount>
    <InvoiceSentDate></InvoiceSentDate>
    <InvoiceReceivedDate></InvoiceReceivedDate>
    <Checklist_Payments_Complete></Checklist_Payments_Complete>
    <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
    <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
    
  </Milestone>
</DocumentElement>

Open in new window

0
Small_Balls
Asked:
Small_Balls
  • 6
  • 4
3 Solutions
 
GuitarRichCommented:
If I read the code right - your trying to populate the data from the session variables here:
            foreach (Session.Variables)
            {
                var hashtable = new Hashtable();
                gridDataItem.ExtractValues(hashtable);

                var dataRow = dataTable.NewRow();
                foreach (DictionaryEntry entry in hashtable)
                {
                    dataRow[entry.Key.ToString()] = entry.Value;
                }
                dataTable.Rows.Add(dataRow);
            }

if so - I would have thought the easiest way to do it is also the simplest:

var dataRow = dataTable.NewRow();
 
dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
dataRow["Milestone_UserID"] = Session["Geek.UserID"];
etc....
 
dataTable.Rows.Add(dataRow);

Open in new window

0
 
Small_BallsAuthor Commented:
Sweet will the same apply for this schema as there are five milestones (if the Project is high risk)



<DocumentElement>
  <Milestone>
 
    <PaymentSplitID></PaymentSplitID>
    <Project_Details_ID></Project_Details_ID>
    <Milestone_UserID></Milestone_UserID>
    <Milestone_Status_ID></Milestone_Status_ID>
    <MilestonePaymentInfoID></MilestonePaymentInfoID>
    <Milestone_Sequence></Milestone_Sequence>
    <Milestone_Begin_Date></Milestone_Begin_Date>
    <Milestone_Due_Date></Milestone_Due_Date>
    <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
    <Milestone_Notes></Milestone_Notes>
    <Percentage></Percentage>
    <PaymentAmount></PaymentAmount>
    <InvoiceSentDate></InvoiceSentDate>
    <InvoiceReceivedDate></InvoiceReceivedDate>
    <Checklist_Payments_Complete></Checklist_Payments_Complete>
    <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
    <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
 
    <PaymentSplitID></PaymentSplitID>
    <Project_Details_ID></Project_Details_ID>
    <Milestone_UserID></Milestone_UserID>
    <Milestone_Status_ID></Milestone_Status_ID>
    <MilestonePaymentInfoID></MilestonePaymentInfoID>
    <Milestone_Sequence></Milestone_Sequence>
    <Milestone_Begin_Date></Milestone_Begin_Date>
    <Milestone_Due_Date></Milestone_Due_Date>
    <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
    <Milestone_Notes></Milestone_Notes>
    <Percentage></Percentage>
    <PaymentAmount></PaymentAmount>
    <InvoiceSentDate></InvoiceSentDate>
    <InvoiceReceivedDate></InvoiceReceivedDate>
    <Checklist_Payments_Complete></Checklist_Payments_Complete>
    <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
    <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
 
    <PaymentSplitID></PaymentSplitID>
    <Project_Details_ID></Project_Details_ID>
    <Milestone_UserID></Milestone_UserID>
    <Milestone_Status_ID></Milestone_Status_ID>
    <MilestonePaymentInfoID></MilestonePaymentInfoID>
    <Milestone_Sequence></Milestone_Sequence>
    <Milestone_Begin_Date></Milestone_Begin_Date>
    <Milestone_Due_Date></Milestone_Due_Date>
    <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
    <Milestone_Notes></Milestone_Notes>
    <Percentage></Percentage>
    <PaymentAmount></PaymentAmount>
    <InvoiceSentDate></InvoiceSentDate>
    <InvoiceReceivedDate></InvoiceReceivedDate>
    <Checklist_Payments_Complete></Checklist_Payments_Complete>
    <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
    <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
 
    <PaymentSplitID></PaymentSplitID>
    <Project_Details_ID></Project_Details_ID>
    <Milestone_UserID></Milestone_UserID>
    <Milestone_Status_ID></Milestone_Status_ID>
    <MilestonePaymentInfoID></MilestonePaymentInfoID>
    <Milestone_Sequence></Milestone_Sequence>
    <Milestone_Begin_Date></Milestone_Begin_Date>
    <Milestone_Due_Date></Milestone_Due_Date>
    <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
    <Milestone_Notes></Milestone_Notes>
    <Percentage></Percentage>
    <PaymentAmount></PaymentAmount>
    <InvoiceSentDate></InvoiceSentDate>
    <InvoiceReceivedDate></InvoiceReceivedDate>
    <Checklist_Payments_Complete></Checklist_Payments_Complete>
    <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
    <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
 
    <PaymentSplitID></PaymentSplitID>
    <Project_Details_ID></Project_Details_ID>
    <Milestone_UserID></Milestone_UserID>
    <Milestone_Status_ID></Milestone_Status_ID>
    <MilestonePaymentInfoID></MilestonePaymentInfoID>
    <Milestone_Sequence></Milestone_Sequence>
    <Milestone_Begin_Date></Milestone_Begin_Date>
    <Milestone_Due_Date></Milestone_Due_Date>
    <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
    <Milestone_Notes></Milestone_Notes>
    <Percentage></Percentage>
    <PaymentAmount></PaymentAmount>
    <InvoiceSentDate></InvoiceSentDate>
    <InvoiceReceivedDate></InvoiceReceivedDate>
    <Checklist_Payments_Complete></Checklist_Payments_Complete>
    <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
    <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
 
  </Milestone>
</DocumentElement>

Open in new window

0
 
Small_BallsAuthor Commented:
I think I may need to change the structure of this xml otherwise sql may not see it as multiple row records??
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
GuitarRichCommented:
yeah the same will work for any extra items in the schema as long as you know which schema you are using.
To have multiple rows you simply need to add a couple of extra tags in:

<DocumentElement>
  <Milestones>
    <Milestone> 
      <PaymentSplitID></PaymentSplitID>
      <Project_Details_ID></Project_Details_ID>
      <Milestone_UserID></Milestone_UserID>
      <Milestone_Status_ID></Milestone_Status_ID>
      <MilestonePaymentInfoID></MilestonePaymentInfoID>
      <Milestone_Sequence></Milestone_Sequence>
      <Milestone_Begin_Date></Milestone_Begin_Date>
      <Milestone_Due_Date></Milestone_Due_Date>
      <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
      <Milestone_Notes></Milestone_Notes>
      <Percentage></Percentage>
      <PaymentAmount></PaymentAmount>
      <InvoiceSentDate></InvoiceSentDate>
      <InvoiceReceivedDate></InvoiceReceivedDate>
      <Checklist_Payments_Complete></Checklist_Payments_Complete>
      <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
      <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
    </Milestone>
    <Milestone> 
      <PaymentSplitID></PaymentSplitID>
      <Project_Details_ID></Project_Details_ID>
      <Milestone_UserID></Milestone_UserID>
      <Milestone_Status_ID></Milestone_Status_ID>
      <MilestonePaymentInfoID></MilestonePaymentInfoID>
      <Milestone_Sequence></Milestone_Sequence>
      <Milestone_Begin_Date></Milestone_Begin_Date>
      <Milestone_Due_Date></Milestone_Due_Date>
      <Checklist_Milestones_Complete></Checklist_Milestones_Complete>
      <Milestone_Notes></Milestone_Notes>
      <Percentage></Percentage>
      <PaymentAmount></PaymentAmount>
      <InvoiceSentDate></InvoiceSentDate>
      <InvoiceReceivedDate></InvoiceReceivedDate>
      <Checklist_Payments_Complete></Checklist_Payments_Complete>
      <ProjectMilestones_EditedBy></ProjectMilestones_EditedBy>
      <ProjectMilestones_EditedDate></ProjectMilestones_EditedDate>
    </Milestone>
    etc... 
  </Milestones>
</DocumentElement>

Open in new window

0
 
Small_BallsAuthor Commented:

var dataRow = dataTable.NewRow();
 
dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
dataRow["Milestone_UserID"] = Session["Geek.UserID"];

dataRow1["Project_Details_ID"] = Session["Geek.ProjectID"];
dataRow1["Milestone_UserID"] = Session["Geek.UserID"];

dataRow2["Project_Details_ID"] = Session["Geek.ProjectID"];
dataRow2["Milestone_UserID"] = Session["Geek.UserID"];
etc....
0
 
Small_BallsAuthor Commented:
is this how I would get the other rows populating with the same data and if I am using the sqlbulkcopy class, will the class know that there are multiple rows??
0
 
GuitarRichCommented:
yes you could do it like that or do it in a loop like the snipped below. I would have thought the sqlbulkcopy class would handle multiple rows fine.
for (int n = 1;n < <some count here>;n++)
{
var dataRow = dataTable.NewRow();
dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
dataRow["Milestone_UserID"] = Session["Geek.UserID"];
dataTable.Rows.Add(dataRow);
}

Open in new window

0
 
Small_BallsAuthor Commented:
thank heaps what do you mean by some count  "for (int n = 1;n < <some count here>;n++)??"
0
 
Small_BallsAuthor Commented:
ahh I think  "for (int n = 1;n < <some count here>;n++)" == "for (int n = 1;n == 5;n++)??"
0
 
GuitarRichCommented:
I'm not sure how you are getting your multiple rows, so thats just the loop to add in multiple rows to your datatable - you can do it that way or the way you originally suggested.
<some count here> is just a way of saying - put your max times you want to loop here.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now