How would insert records into a SQL Server table based on a string value retuned from a Web Service call ?

zimmer9
zimmer9 used Ask the Experts™
on
I am writing a C# application using Visual Studio 2005 and I call a Web Service method in the Code: section that follows.

I want to populate a table named tblGetListOfDocuments with the following field layout:

MailID
NoOfPages
NoOfAttributes

The value returned in strResponse from calling a Web Service Method is as follows:

"<PWMDocuments>\n <Document>\n <MailID>20080930163836771214506</MailID>\n <NoOfPages>4
</NoOfPages>\n  <NoOfAttributes>9</NoOfAttributes>\n </Document>\n
                                   <Document>\n <MailID>20080930172853771294506</MailID>\n <NoOfPages>3 </NoOfPages>\n  <NoOfAttributes>9</NoOfAttributes>\n </Document>\n
                                   <Document>\n <MailID>20080943172853746294842</MailID>\n <NoOfPages>2 </NoOfPages>\n  <NoOfAttributes>5</NoOfAttributes>\n </Document>\n

Do you know how to write a routine to parse this data to insert the records into the SQL Server table tblGetListOfDocuments ?

The routine should be able to handle any number of records returned to the string
strResponse.


private void button1_Click(object sender, EventArgs e)
        {
            string strResponse; 
            try
            {
                //Create the Service object and give it proper credentials
                D3Interface.Service ws = new D3Interface.Service();
                ws.Credentials = new NetworkCredential("mj", "M2");
                // Do the request to get the response
                strResponse = ws.GetListOfDocuments();
            }
            catch (Exception ex)
            {
                strResponse = ex.Message;
            }
        }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Alexandre SimõesManager / Solutions Architect

Commented:
Try using this code


			string xml =
				"<PWMDocuments>\n" +
				"<Document>\n <MailID>20080930163836771214506</MailID>\n <NoOfPages>4</NoOfPages>\n  <NoOfAttributes>9</NoOfAttributes>\n </Document>\n" +
				"<Document>\n <MailID>20080930172853771294506</MailID>\n <NoOfPages>3 </NoOfPages>\n  <NoOfAttributes>9</NoOfAttributes>\n </Document>\n" +
				"<Document>\n <MailID>20080943172853746294842</MailID>\n <NoOfPages>2 </NoOfPages>\n  <NoOfAttributes>5</NoOfAttributes>\n </Document>\n " +
				"</PWMDocuments>";
 
			using (System.Data.DataSet ds = new System.Data.DataSet())
			{
				using (System.IO.StringReader sr = new System.IO.StringReader(xml))
				{
					ds.ReadXml(sr);
				}
 
 
				using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("your connection string"))
				{
					conn.Open();
					System.Data.SqlClient.SqlCommand comm = conn.CreateCommand();
					comm.CommandType = System.Data.CommandType.Text;
 
					foreach (System.Data.DataRow row in ds.Tables[0].Rows)
					{
						comm.CommandText = 
							string.Format("INSERT INTO tblGetListOfDocuments (MailID,NoOfPages,NoOfAttributes) VALUES ({0},{1},{2})", row["MailID"].ToString(), row["NoOfPages"].ToString(), row["NoOfAttributes"].ToString());
						comm.ExecuteNonQuery();
					}
 
					conn.Close();
				}
			}

Open in new window

Marcus KeustermansSolutions Architect

Commented:
here is an implemetation using Linq to XML
private void InsertIntoDatabase(string xml)
        {
                XDocument doc = XDocument.Parse(xml);

                var query = from records in doc.Elements("PWMDocuments").Elements("Document")
                            select records;

                if (null != query)
                {
                    foreach (XElement record in query)
                    {
                       //Do database insert here                    }
                }
        }

Author

Commented:
How would you revise the following code so that the xml string values are not hardcoded into the source code ?

The records read in are not the same each time the application is executed. There is no way of telling in advance how many records will be read into the application. I gave a specific example of 3 records read in, purely as an example so that you could see the record layout. Thanks for your time.

                   string xml =
                        "<PWMDocuments>\n" +
                        "<Document>\n <MailID>20080930163836771214506</MailID>\n <NoOfPages>4</NoOfPages>\n  <NoOfAttributes>9</NoOfAttributes>\n </Document>\n" +
                        "<Document>\n <MailID>20080930172853771294506</MailID>\n <NoOfPages>3 </NoOfPages>\n  <NoOfAttributes>9</NoOfAttributes>\n </Document>\n" +
                        "<Document>\n <MailID>20080943172853746294842</MailID>\n <NoOfPages>2 </NoOfPages>\n  <NoOfAttributes>5</NoOfAttributes>\n </Document>\n " +
                        "</PWMDocuments>";
 
                  using (System.Data.DataSet ds = new System.Data.DataSet())
                  {
                        using (System.IO.StringReader sr = new System.IO.StringReader(xml))
                        {
                              ds.ReadXml(sr);
                        }
 
 
                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("your connection string"))
                        {
                              conn.Open();
                              System.Data.SqlClient.SqlCommand comm = conn.CreateCommand();
                              comm.CommandType = System.Data.CommandType.Text;
 
                              foreach (System.Data.DataRow row in ds.Tables[0].Rows)
                              {
                                    comm.CommandText =
                                          string.Format("INSERT INTO tblGetListOfDocuments (MailID,NoOfPages,NoOfAttributes) VALUES ({0},{1},{2})", row["MailID"].ToString(), row["NoOfPages"].ToString(), row["NoOfAttributes"].ToString());
                                    comm.ExecuteNonQuery();
                              }
 
                              conn.Close();
                        }
                  }
 
Manager / Solutions Architect
Commented:
Just create a method that receives that xml variable as a input parameter.

public void ImportData(string xml)
{
 
                        using (System.Data.DataSet ds = new System.Data.DataSet())
                        {
                                using (System.IO.StringReader sr = new System.IO.StringReader(xml))
                                {
                                        ds.ReadXml(sr);
                                }
 
 
                                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("your connection string"))
                                {
                                        conn.Open();
                                        System.Data.SqlClient.SqlCommand comm = conn.CreateCommand();
                                        comm.CommandType = System.Data.CommandType.Text;
 
                                        foreach (System.Data.DataRow row in ds.Tables[0].Rows)
                                        {
                                                comm.CommandText = 
                                                        string.Format("INSERT INTO tblGetListOfDocuments (MailID,NoOfPages,NoOfAttributes) VALUES ({0},{1},{2})", row["MailID"].ToString(), row["NoOfPages"].ToString(), row["NoOfAttributes"].ToString());
                                                comm.ExecuteNonQuery();
                                        }
 
                                        conn.Close();
                                }
                        }
}

Open in new window

Author

Commented:
comm.CommandText = string.Format("INSERT INTO tblGetListOfDocuments (MailID,NoOfPages,NoOfAttributes) VALUES ({0},{1},{2})", row["MailID"].ToString(), row["NoOfPages"].ToString(), row["NoOfAttributes"].ToString());

comm.ExecuteNonQuery();
---------------------------------------------------------------

I am able to insert the following 2 records that start with a MailID of "20080930..."
Then when an attempt is made by the system to Insert the record that starts with a MailID of 'VAKO100120080945000000014B' , I get the following message:

"The name 'VAKO100120080945000000014B' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

Do you know how I can resolve this error ?  

"<PWMDocuments>\n <Documents>\n <MailID>20080930163836771214506 </MailID>\n <NoOfPages> 4
</NoOfPages>\n <NoOfAttributes>9 </NoOfAttributes>\n </Document>\n <Document>\n
<MailID> 20080930172853771295406 </MailID>\n <NoOfPages> 3
</NoOfPages>\n <NoOfAttributes>9 </NoOfAttributes>\n </Document>\n <Document>\n
<MailID> VAKO100120080945000000014B </MailID>\n <NoOfPages> 4
</NoOfPages>\n <NoOfAttributes>9 </NoOfAttributes>\n </Document>\n <Document>\n

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