Solved

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

Posted on 2010-09-23
22
2,329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 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