SQL Bulk copy and multiple Template tables

Hello,

I have a bit of a problem..... currently I am using SqlBulkCopy class with XML templates to automate milestone and Payment insertion in to a sql 2005 database..

for multiple different companies (each do the same thing different)

Problem::

I am Currently using XML but will use SQL tables instead.. the Company Identifier is kept in session to filter the row records.. so there is a risk matrix that compares a series of values to get a result 1-9 based on this result the sepcified table will get copied to the destination.. my issue is I do not know how to instead of copying the entire table, copy only the records the match the company id. ??

the example below is using xml.. just for now how can i get this matrix to loop through the nodes on the required xml schema to only copy the data that has <CMAID>2</CMAID>??
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");
 
            for (int n = 1;n == 5;n++)
            {
                var dataRow = dataTable.NewRow();
                dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
                dataRow["Milestone_UserID"] = Session["Geek.UserID"];
                dataRow["ProjectMilestones_EditedBy"] = Session["Geek.UserName"];
                dataRow["ProjectMilestones_EditedDate"] = DateTime.Now;
                dataTable.Rows.Add(dataRow);
            }
            if (dataTable.Rows.Count > 0)
            {
                dataTable.WriteXml(Server.MapPath("~/XML/RiskMatrix/5milestone.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
            #region test
            // the two line below work for defining the columns from and to....
 
            //sbc.ColumnMappings.Add("campaign", "campaign_id");
            //sbc.ColumnMappings.Add("cost", "cost_USD");
            #endregion
            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))
        {
            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");
 
            for (int n = 1; n == 4; n++)
            {
                var dataRow = dataTable.NewRow();
                dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
                dataRow["Milestone_UserID"] = Session["Geek.UserID"];
                dataRow["ProjectMilestones_EditedBy"] = Session["Geek.UserName"];
                dataRow["ProjectMilestones_EditedDate"] = DateTime.Now;
                dataTable.Rows.Add(dataRow);
            }
 
            if (dataTable.Rows.Count > 0)
            {
                dataTable.WriteXml(Server.MapPath("~/XML/RiskMatrix/4milestone.xml"));
            }
 
            var MilestoneData = new DataSet();
            MilestoneData.ReadXml(Server.MapPath("~/XML/RiskMatrix/4milestone.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
 
            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 >= v) || (a + b + c <= w))
        {
            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");
 
            for (int n = 1; n == 3; n++)
            {
                var dataRow = dataTable.NewRow();
                dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
                dataRow["Milestone_UserID"] = Session["Geek.UserID"];
                dataRow["ProjectMilestones_EditedBy"] = Session["Geek.UserName"];
                dataRow["ProjectMilestones_EditedDate"] = DateTime.Now;
                dataTable.Rows.Add(dataRow);
            }
 
            if (dataTable.Rows.Count > 0)
            {
                dataTable.WriteXml(Server.MapPath("~/XML/RiskMatrix/3milestone.xml"));
            }
 
            var MilestoneData = new DataSet();
            MilestoneData.ReadXml(Server.MapPath("~/XML/RiskMatrix/3milestone.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
 
            connection.Open();
 
            //table 4 is the main table in this dataset
            sbc.WriteToServer(MilestoneData.Tables[0]);
            connection.Close();
 
            //remove the xml file
 
        }
            //compare the the values of add the values of a, b and c together and compare
            //the total with a predefined variable then do something
        else if ((a + b + c >= t) || (a + b + c <= u))
        {
            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");
 
            for (int n = 1; n == 2; n++)
            {
                var dataRow = dataTable.NewRow();
                dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
                dataRow["Milestone_UserID"] = Session["Geek.UserID"];
                dataRow["ProjectMilestones_EditedBy"] = Session["Geek.UserName"];
                dataRow["ProjectMilestones_EditedDate"] = DateTime.Now;
                dataTable.Rows.Add(dataRow);
            }
 
            if (dataTable.Rows.Count > 0)
            {
                dataTable.WriteXml(Server.MapPath("~/XML/RiskMatrix/2milestone.xml"));
            }
 
            var MilestoneData = new DataSet();
            MilestoneData.ReadXml(Server.MapPath("~/XML/RiskMatrix/2milestone.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
 
            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 != r)
        {
            //This is creating the datatable in memory
            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");
            //adding rows
            var dataRow = dataTable.NewRow();
 
            dataRow["Project_Details_ID"] = Session["Geek.ProjectID"];
            dataRow["Milestone_UserID"] = Session["Geek.UserID"];
            dataRow["ProjectMilestones_EditedBy"] = Session["Geek.UserName"];
            dataRow["ProjectMilestones_EditedDate"] = DateTime.Now;
            dataTable.Rows.Add(dataRow);
            //looping to check that the rows are greater than 0 if yes then write xml
            if (dataTable.Rows.Count > 0)
            {
                dataTable.WriteXml(Server.MapPath("~/XML/RiskMatrix/1milestone.xml"));
            }
            //reading xml and using it as a dataset
            var MilestoneData = new DataSet();
            MilestoneData.ReadXml(Server.MapPath("~/XML/RiskMatrix/2milestone.xml"));
            //open SQL connection
            var connection = new SqlConnection("CMA_project");
            //copy data to sql tables
            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
            //open connection
            connection.Open();
 
            //table 4 is the main table in this dataset
            sbc.WriteToServer(MilestoneData.Tables[0]);
            connection.Close();
 
            //remove the xml file
 
        }
 
    }
    #endregion

Open in new window

Small_BallsAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
Simple solution with XmlDocument:

XmlDocument document = new XmlDocument();
document.Load(fileName);

XmlNodeList nodeList = document.SelectNodes("//CMAID");

foreach (XmlNode node in nodeList)
{
    string cmaID = node.InnerText;
}
0
 
Small_BallsAuthor Commented:
please help
0
 
Bob LearnedCommented:
1) This is ASP.NET 3.5.

2) Do you know anything about LINQ?

3) I see where you are using DataSet.ReadXml to infer a DataSet from the XML file.

4) What DataTable does that element exist in?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Small_BallsAuthor Commented:
as mentioned in description;
"the example below is using xml.. just for now how can i get this matrix to loop through the nodes on the required xml schema to only copy the data that has <CMAID>2</CMAID>??"

please find attached a copy of the xml p.s I do not know if I done the xml correctly.. and CMAID does not get coppied one the node between <Milestone>
<?xml version="1.0" encoding="utf-8" ?>
<DocumentElement>
  <Milestones>
    <CMA>
      <CMAID>2</CMAID>
      <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>
 
      <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>
 
      <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>
    </CMA>
    <CMA>
      <CMAID>6</CMAID>
      <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>
 
      <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>
 
      <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>
    </CMA>
  </Milestones>
</DocumentElement>

Open in new window

0
 
Small_BallsAuthor Commented:
and in answer to your other comment yes I do know a little about LINQ but i do not wish to implement

0
 
Small_BallsAuthor Commented:
sorry  for not replying as I have been out of town.. how can I use the selected in my existing code posted above??

0
 
Bob LearnedCommented:
What information are you writing to the destination SQL Server table?
0
 
Small_BallsAuthor Commented:
it is in the xml above

0
 
Bob LearnedCommented:
So, you are writing <Milestone> and <CMAID>?
0
 
Small_BallsAuthor Commented:
Hello TheLearnedOne,
So, you are writing <Milestone> and <CMAID>?
Yes + the associated Milestone Elements and their content of which the example above contains no content.
0
 
Small_BallsAuthor Commented:
Thankyou...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.