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

dearnemo
dearnemo used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Christopher KileSenior Software Analyst

Commented:
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?

Author

Commented:
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.
Senior Software Analyst
Commented:
Yes, I just found the table value parameter in relation to another task.  Please post your solution and ask the Administrator to accept your solution as the answer here, a number of the experts here have not yet studied table value parameters and all of us would benefit.  Even the links you used to find this would be beneficial.
Christopher KileSenior Software Analyst

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial