asked on

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

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

Alexandre Simões

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 Keustermans

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 }
}
}

zimmer9

ASKER

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();
                        }
                  }

ASKER CERTIFIED SOLUTION

Alexandre Simões

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zimmer9

ASKER

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