Solved

Save XML to SQL Server database - C# - CRASH - Please Help

Posted on 2010-09-23
22
2,164 Views
Last Modified: 2012-05-10
I am trying to save XML to SQL Server 2005 database through C# in a ASP.Net Application ?

It's crashing on
 myCommand.ExecuteNonQuery();

Error getting while debugging is - "Failed to convert parameter value from a XmlDocument to a String."
Error showing in error page is "Object must implement IConvertible"

What I am doing wrong here ?

Appreciates your suggestion that could resolve this issue!

Thanks
Raj


This is the Stored Procedure
 
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go





ALTER PROCEDURE [dbo].[uspS_WorkspeedXML]

(

	@WorkspeedTransDetailsID	BIGINT,

	@RequestXML					XML,

	@RequestTime				DATETIME,

	@ResponseXML				XML,

	@ResponseTime				DATETIME

)

AS

BEGIN



	DECLARE @ATTEMPT INT

	SET @ATTEMPT = 0



	SELECT @ATTEMPT = COUNT(*) FROM TBL_WORKSPEED_XMLs

		WHERE WorkspeedTransDetailsID = @WorkspeedTransDetailsID



	INSERT INTO TBL_WORKSPEED_XMLs

	(

		WorkspeedTransDetailsID,

		AttemptNumber,

		RequestXML,

		ResponseXML,

		RequestTime,

		ResponseTime

	)

	VALUES

	(

		@WorkspeedTransDetailsID,

		@ATTEMPT + 1,

		@RequestXML,

		@ResponseXML,

		@RequestTime,

		@ResponseTime

	)



END

Open in new window


Here is the C# Code
 
private void SaveXMLToDatabase(XmlDocument xmlRequest, XmlDocument xmlResponse, DateTime requestTime,

                    DateTime responseTime, SqlConnection mySQLConnection)

    {

        SqlCommand myCommand = default(SqlCommand);



        if (mySQLConnection.State == 0)

            mySQLConnection.Open();





        myCommand = new SqlCommand("uspS_WorkspeedXML", mySQLConnection);

        myCommand.CommandType = CommandType.StoredProcedure;



        // @WorkspeedTransDetailsID	    BIGINT

        myCommand.Parameters.Add(new SqlParameter("@WorkspeedTransDetailsID", SqlDbType.BigInt));

        myCommand.Parameters["@WorkspeedTransDetailsID"].Value = objUser.SubmitTransMasterID; // TBL_WORKSPEED_DETAILS.WorkspeedTransDetailsID



        // @RequestXML	XML

        myCommand.Parameters.Add(new SqlParameter("@RequestXML", SqlDbType.Xml));

        myCommand.Parameters["@RequestXML"].Value = xmlRequest;



        // @RequestTime				DATETIME

        myCommand.Parameters.Add(new SqlParameter("@RequestTime", SqlDbType.DateTime));

        myCommand.Parameters["@RequestTime"].Value = requestTime;



        // @ResponseXML				XML

        myCommand.Parameters.Add(new SqlParameter("@ResponseXML", SqlDbType.Xml));

        myCommand.Parameters["@ResponseXML"].Value = xmlResponse;

        

        // @ResponseTime				DATETIME

        myCommand.Parameters.Add(new SqlParameter("@ResponseTime", SqlDbType.DateTime));

        myCommand.Parameters["@ResponseTime"].Value = responseTime;



        myCommand.ExecuteNonQuery();



        myCommand.Dispose();



        if (mySQLConnection.State == ConnectionState.Open)

            mySQLConnection.Close();



    }

Open in new window

0
Comment
Question by:Rajkumar Gs
  • 17
  • 5
22 Comments
 
LVL 15

Expert Comment

by:crisco96
ID: 33743356
I think you need to convert your xmlDocument to a string representation of the xml.

Use the function below like so:
        myCommand.Parameters["@RequestXML"].Value = GetXmlString(xmlRequest);
        myCommand.Parameters["@ResponseXML"].Value = GetXmlString(xmlResponse);

public string GetXmlString(XmlDocument doc)

{

	StringWriter sw = new StringWriter();

	XmlTextWriter xw = new XmlTextWriter(sw);

	doc.WriteTo(xw);

	return sw.ToString();

}

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743442
Thanks crisco96.

It is not working. When I implement this
"XML parsing: line 1, character 38, unable to switch the encoding"

The code you posted above & xmlDocument.InnerXML - both are returning the same result - the XML
When I tried your code & xmlDocument.InnerXML - both are returning the above mentioned error

Any idea ?
Raj
0
 
LVL 15

Expert Comment

by:crisco96
ID: 33743544
So where is the error happening? Is it happening in my function or is it happening when you try to send the xml to the database?

Try changing the second line of my function to:

            XmlTextWriter xw = new System.Xml.XmlTextWriter(sw, System.Text.Encoding.Unicode);

So now you'll have
public string GetXmlString(XmlDocument doc)

{

    StringWriter sw = new StringWriter();

    XmlTextWriter xw = new System.Xml.XmlTextWriter(sw, System.Text.Encoding.Unicode);

    doc.WriteTo(xw);

    return sw.ToString();

}

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743624
@crisco96,

This error <b>"XML parsing: line 1, character 38, unable to switch the encoding"</b> is happening NOT in your function, but while save to database - myCommand.ExecuteNonQuery();

When I debug and checked, myCommand.Parameters["@RequestXML"].Value is having the right XML.

I tried the suggestion you posted just now. It is crashing - Please click and view the attachment.

Raj
XMLSaveError.JPG
0
 
LVL 15

Assisted Solution

by:crisco96
crisco96 earned 500 total points
ID: 33743690
Looking at this website: http://geekswithblogs.net/vkamat/archive/2006/01/20/66561.aspx

I think I've found a better solution, remove my function and do the following:
 myCommand.Parameters["@RequestXML"].Value = new SqlXml(new XmlTextReader(xmlRequest, XmlNodeType.Document, null));

 myCommand.Parameters["@ResponseXML"].Value = new SqlXml(new XmlTextReader(xmlResponse, XmlNodeType.Document, null));

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743782
I changed the datatype of Stored Procedure from XML to NVARCHAR(2000) and tried with the function you posted in http:#33743356

Same crash - "XML parsing: line 1, character 38, unable to switch the encoding"
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743798
Ok. I will try your last suggestion now - http:#33743690
Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743915
Re: http:#33743690

Oops! It is also crashing! - See attachment

It is because the first parameter of XmlTextReader is expecting stream, but we are passing XMLDocument.
XmlTextReader(xmlRequest, XmlNodeType.Document, null));

Raj

XMLSaveError-2.JPG
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743925
I think there is a good news. I will be back soon.!

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744019
Re: Your comment - http:#33743690

Can you plz tell me, from the link you mentioned in that comment, where you got that code ?

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744067
Eventhough the code you posted in http:#33743690 not worked, I got the working code from the link you posted.
http://geekswithblogs.net/vkamat/archive/2006/01/20/66561.aspx

In that link, there is a comment starting with "This worked for me", right ? That's it :)

Attached one is the suggestion that worked out!
Raj
StringReader transactionXml = new StringReader(myXmlString);

XmlTextReader xmlReader = new XmlTextReader(transactionXml);

SqlXml sqlXml = new SqlXml(xmlReader);



// Then use the sqlXml in your insert/update statement

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744091
When checked database, the XML's top portion where the encoding is specifying is not there.
So I think that code is filtering out first line and passing the remaining XML code that SQL Server accepts.

Raj
<?xml version="1.0" encoding="utf-8"?>

Open in new window

0
 
LVL 15

Assisted Solution

by:crisco96
crisco96 earned 500 total points
ID: 33744186
Alright I think this will do what we need:

 myCommand.Parameters["@RequestXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(GetXmlString(xmlRequest), System.Xml.XmlNodeType.Document, null));
 myCommand.Parameters["@ResponseXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(GetXmlString(xmlResponse), System.Xml.XmlNodeType.Document, null));

public string GetXmlString(XmlDocument doc)

{

	StringWriter sw = new StringWriter();

	XmlTextWriter xw = new XmlTextWriter(sw);

	doc.WriteTo(xw);

	return sw.ToString();

}

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744312
I have a doubt.

What is the difference between the result that the function you posted above returns ?
with
xmlResponse.InnerXml ?

I mean
GetXmlString(xmlResponse)   Vs  xmlResponse.InnerXml

I have mentioned about this in my comment - http:#33743442

Thanks!
Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744380
If there is no difference, I can simplify the code like attached, right ? (Without using the function)

Raj

myCommand.Parameters["@RequestXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(xmlRequest.InnerXml, System.Xml.XmlNodeType.Document, null));

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744399
Your code using function and without function with .innerXML works in the same manner!

Thanks great for your help
Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744407
Great dude!! - You hit it at last :)

So now I have two solutions :-)
Solution #1:- http:#33744186
Solution #2:- http:#33744067

Raj
0
 
LVL 15

Assisted Solution

by:crisco96
crisco96 earned 500 total points
ID: 33745021
I think #33744380 would work just fine. To be sure try saving the xml to the database then try loading it back into an xml document from the database. If that all works fine you should be good to go.
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33745556
I will check reloading XML from database tomorrow and let you know.

Thanks
Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 0 total points
ID: 33751658
What I understood is that if we try to save an XML to SQL Server, that contains the line that specifies the encoding
<?xml version="1.0" encoding="utf-8"?>
SQL Server WILL NOT allow to save it in database. We need to do some type of casting to convert the XML in a format that is acceptable by SQL Server (ie., removing the encoding line)

The datatype of parameters for RequestXML & ResponseXML used in C# code and Stored Procedure is XML

C# Code:-
// @RequestXML      XML
myCommand.Parameters.Add(new SqlParameter("@RequestXML", SqlDbType.Xml));
myCommand.Parameters["@RequestXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(xmlRequest.InnerXml, System.Xml.XmlNodeType.Document, null));

Stored Procedure:-
ALTER PROCEDURE [dbo].[uspS_WorkspeedXML]
(
      @WorkspeedTransDetailsID      BIGINT,
      @RequestXML            XML,
      @RequestTime            DATETIME,
      @ResponseXML            XML,
      @ResponseTime            DATETIME
)
AS
...

I have also tested it by reading from database back and show it in webpage.

@crisco96,
Highly appreciating your assistance that helped me to solve it out :-)

Thanks Again
Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33751673
THIS IS ANOTHER SOLUTION - http:#33744067

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33753745
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

22 Experts available now in Live!

Get 1:1 Help Now