Solved

XML Populating with data from session

Posted on 2009-05-11
10
259 Views
Last Modified: 2013-12-17
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
Comment
Question by:Small_Balls
  • 6
  • 4
10 Comments
 
LVL 12

Assisted Solution

by:GuitarRich
GuitarRich earned 500 total points
Comment Utility
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
 

Author Comment

by:Small_Balls
Comment Utility
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
 

Author Comment

by:Small_Balls
Comment Utility
I think I may need to change the structure of this xml otherwise sql may not see it as multiple row records??
0
 
LVL 12

Expert Comment

by:GuitarRich
Comment Utility
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
 

Author Comment

by:Small_Balls
Comment Utility

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Small_Balls
Comment Utility
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
 
LVL 12

Accepted Solution

by:
GuitarRich earned 500 total points
Comment Utility
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
 

Author Comment

by:Small_Balls
Comment Utility
thank heaps what do you mean by some count  "for (int n = 1;n < <some count here>;n++)??"
0
 

Author Comment

by:Small_Balls
Comment Utility
ahh I think  "for (int n = 1;n < <some count here>;n++)" == "for (int n = 1;n == 5;n++)??"
0
 
LVL 12

Assisted Solution

by:GuitarRich
GuitarRich earned 500 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now