Solved

SQL Bulk copy and multiple Template tables

Posted on 2009-05-13
12
973 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
  • 7
  • 4
12 Comments
 

Author Comment

by:Small_Balls
Comment Utility
please help
0
 
LVL 96

Expert Comment

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

Author Comment

by:Small_Balls
Comment Utility
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 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Small_Balls
Comment Utility
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
Comment Utility
What information are you writing to the destination SQL Server table?
0
 

Author Comment

by:Small_Balls
Comment Utility
it is in the xml above

0
 
LVL 96

Expert Comment

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

Author Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
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:…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

12 Experts available now in Live!

Get 1:1 Help Now