Avatar of dearnemo
dearnemo
 asked on

C# :INSERT xml attribute values in a SQL table?

1.) I have database and table created using SMO in C# console application.

Server server = new Server("pwd12"); 
 if (server.Databases["MyDatabaseName"] != null) 
     server.Databases["MyDatabaseName"].Drop(); 
 Database db = new Database(server, "MyDatabaseName"); 
     db.Create(); 
 
if (db.Tables["TechOpsProjectTracker"] != null) 
    db.Tables["TechOpsProjectTracker"].Drop(); 
    Table TechOpsProjectTracker = new Table(db, "TechOpsProjectTracker");

Open in new window


2.) Using SMO, created columns.For simplicity I just showed two columns here.

DataType dt = new DataType(SqlDataType.Int); 
Column idColumn = new Column(TechOpsProjectTracker, "ID", dt); 
TechOpsProjectTracker.Columns.Add(idColumn); 
 
DataType dt2 = new DataType(SqlDataType.NVarChar, 50); 
Column Column2 = new Column(TechOpsProjectTracker, "Project Name", dt2); 
TechOpsProjectTracker.Columns.Add(Column2); 

Open in new window


I m using the SharePoint service using a web reference to access a SharePoint 2010 list. I have a C# console application that returns SharePoint list data in the form of xml.


3. small fraction of xml returned by my code:
rs:data>
   <z:row ows_ID='360' ows_LinkTitle='GEI Survey data to Sharepoint' ows_Project_x0020_Priority='0' ows_AssignedTo='615;#Jeremy, Ron' ows_Status='In Progress' ows_Priority='(2) Normal' ows_DueDate='2012-04-27 00:00:00' ows_PercentComplete='0.700000000000000' ows_Modified='2012-04-30 10:44:15' ows_Alignment='TSS Delivery Mgmt' ows_SME='44;#Lewis, Clark' />

   <z:row ows_ID='378' ows_LinkTitle='Create back end and environment to support User demographic reporting' ows_Project_x0020_Priority='0' ows_AssignedTo='615;#Sam, Johns' ows_Status='In Progress' ows_Priority='(2) Normal' ows_DueDate='2012-05-11 00:00:00' ows_PercentComplete='0.800000000000000' ows_Modified='2012-05-09 13:50:17' ows_Alignment='Team Internal' ows_SME='7;#CORP\sscer;#9;#CORP\vreer' />

   <z:row ows_ID='249' ows_LinkTitle='Training Material to Muti Media' ows_AssignedTo='620;#Jenkins, Kristen' ows_Status='Not Started' ows_Priority='(2) Normal' ows_DueDate='2012-08-10 00:00:00' ows_PercentComplete='0' ows_Modified='2012-05-16 11:20:29' ows_Alignment='Diver Support' ows_SME='1;#CORP\vsswer;#7;#CORP\adder' />

</rs:data>

Open in new window


4.)I need to fill in my table with the values of the attributes returned by the above xml

string connectionString = "Server=pwd12;Database=MyDatabaseName;Trusted_Connection=True;";
           
            System.Data.SqlClient.SqlConnection sqlConnection1 =
            new System.Data.SqlClient.SqlConnection(connectionString);

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT TechOpsProjectTracker (ID,[Project Name]) VALUES (@ID, @ProjectName)";

Open in new window


5.)What I m doing is in the code below:

foreach (System.Xml.XmlNode node in nodes)
            {
                if (node.Name == "rs:data")
                {
                    for (int i = 0; i < node.ChildNodes.Count; i++)
                    {
                        if (node.ChildNodes[i].Name == "z:row")
                        {
			    
                            //Console.Write(node.ChildNodes[i].Attributes                             ["ows_Title"].Value);
                            //Console.Write(node.ChildNodes[i].Attributes["ows_ID"].Value);
			
// I want to be able to store the values of attributes ["ows_ID"] and ["ows_Title"] into some sort of arrays and be able to pass them as parameters while inserting data to the table TechOpsProjectTracker.

//Please let me know how is it possible.

                        }
                    }
                }
            } 

Open in new window


TIA!
C#.NET ProgrammingMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Christopher Kile

8/22/2022 - Mon
Christopher Kile

Well, SQL Server doesn't accept arrays from ADO.NET as input.  You'll have to insert each record one at a time, or pass your "array" as a delimited string.  What are you planning to store, what's the table(s) you're planning to store it in?
dearnemo

ASKER
Thanks for your suggestion but I have found a solution to my problem. Using table value parameter in C# and Sql SErver, you can  achieve it. Thanks.
ASKER CERTIFIED SOLUTION
Christopher Kile

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dearnemo

ASKER
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Christopher Kile

LOL I think you should get the points, but I don't think they ever give them to the asker.  Thanks very much.