用XML数据类型调用存储过程

问题描述

我只是尝试使用C#调用存储过程(SQL Server 2008)，并将XMLDocument传递给采用SqlDbType.Xml数据类型的存储过程参数.我收到错误消息:无法将参数值从XmlDocument转换为字符串.下面是代码示例.如何将XML文档传递到需要XML数据类型的存储过程?谢谢.

I am simply trying to call a store procedure (SQL Server 2008) using C# and passing XMLDocument to a store procedure parameter that takes a SqlDbType.Xml data type. I am getting error: Failed to convert parameter value from a XmlDocument to a String. Below is code sample. How do you pass an XML Document to a store procedure that is expecting an XML datatype? Thanks.

        XmlDocument doc = new XmlDocument();
        //Load the the document with the last book node.
        XmlTextReader reader = new XmlTextReader(@"C:\temp\" + uploadFileName);
        reader.Read();
        // load reader
        doc.Load(reader);

        connection.Open();

        SqlCommand cmd = new SqlCommand("UploadXMLDoc", connection);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@Year", SqlDbType.Int);
        cmd.Parameters["@Year"].Value = iYear;
        cmd.Parameters.Add("@Quarter", SqlDbType.Int);
        cmd.Parameters["@Quarter"].Value = iQuarter;
        cmd.Parameters.Add("@CompanyID", SqlDbType.Int);
        cmd.Parameters["@CompanyID"].Value = iOrganizationID;
        cmd.Parameters.Add("@FileType", SqlDbType.VarChar);
        cmd.Parameters["@FileType"].Value = "Replace";
        cmd.Parameters.Add("@FileContent", SqlDbType.Xml);
        cmd.Parameters["@FileContent"].Value = doc;
        cmd.Parameters.Add("@FileName", SqlDbType.VarChar);
        cmd.Parameters["@FileName"].Value = uploadFileName;
        cmd.Parameters.Add("@Description", SqlDbType.VarChar);
        cmd.Parameters["@Description"].Value = lblDocDesc.Text;
        cmd.Parameters.Add("@Success", SqlDbType.Bit);
        cmd.Parameters["@Success"].Value = false;
        cmd.Parameters.Add("@AddBy", SqlDbType.VarChar);
        cmd.Parameters["@AddBy"].Value = Page.User.Identity.Name;

        cmd.ExecuteNonQuery();
        connection.Close();

推荐答案

您需要将xml作为字符串传递.

You need to pass the xml as a string.

但是，如果您不需要数据库中的xml函数，则可以考虑使用varbinary存储文件.

But if you don't need the xml functions in the database, you might consider using varbinary to store the files.

更新!!!!!

谢谢.我知道了.添加了以下代码:

Thanks. I got it to work. Added the following coded:

StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
doc.WriteTo(xw);
StringReader transactionXml = new StringReader(sw.ToString());
XmlTextReader xmlReader = new XmlTextReader(transactionXml);
SqlXml sqlXml = new SqlXml(xmlReader);

仅将其转换为字符串是不够的.我收到以下错误:XML解析:第1行，字符38，无法切换编码".因此，我转换为字符串，然后将其转换为SqlXml并成功了.

Converting it to a string was not enough. I got the following error: XML parsing: line 1, character 38, unable to switch the encoding". So, I converted to string then coverted it to SqlXml and it worked.

这篇关于用XML数据类型调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！