Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Bulk copy and multiple Template tables

Posted on 2009-05-13
12
Medium Priority
?
985 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:Small_Balls
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 

Author Comment

by:Small_Balls
ID: 24401382
please help
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24402253
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
 

Author Comment

by:Small_Balls
ID: 24405291
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Small_Balls
ID: 24405851
and in answer to your other comment yes I do know a little about LINQ but i do not wish to implement

0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1500 total points
ID: 24408263
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
 

Author Comment

by:Small_Balls
ID: 24479752
sorry  for not replying as I have been out of town.. how can I use the selected in my existing code posted above??

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24483151
What information are you writing to the destination SQL Server table?
0
 

Author Comment

by:Small_Balls
ID: 24489962
it is in the xml above

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24492737
So, you are writing <Milestone> and <CMAID>?
0
 

Author Comment

by:Small_Balls
ID: 24679171
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
 

Author Closing Comment

by:Small_Balls
ID: 31581323
Thankyou...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

636 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