当前位置：文江博客话题详情

如何创建一个接受 XML 并使用该 xml 作为更新的存储过程 +使用 ado.net 调用此存储过程？

发布于 2024-09-02 07:48:08 字数 3555 浏览 4 评论 0 原文

我正在使用 ms sql server 2005，我想进行批量更新。我想我可以通过将 xml 文档发送到存储过程来做到这一点。

所以我看到了很多关于如何插入的示例，

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData XML)
AS 
   INSERT INTO 
      dbo.UserTable(CreateDate)
      SELECT
         @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')

但我不确定更新会是什么样子。

我也不确定如何通过 ado.net 传入 xml？我是通过参数将其作为字符串传递还是什么？

我知道 sqlDataApater 有批量更新方法，但我正在使用 linq to sql。所以我宁愿继续使用它。因此，如果这有效，我将能够使用 linq to sql 获取所有记录并将它们作为对象。然后操作对象并使用 xml 序列化。

最后我可以使用 ado.net simple 将 xml 发送到服务器。这可能比 sqlDataAdapter 慢，但如果我可以继续使用对象，我愿意接受这种打击。

编辑

好的，到目前为止我已经有了

这是我的 XML

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfUserTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <UserTable>
    <CreateDate>2011-05-21T11:04:55.0584669-07:00</CreateDate>
    <id>0</id>
    <AnotherField>false</AnotherField>
  </UserTable>
  <UserTable>
    <CreateDate>2015-05-21T11:04:55.061467-07:00</CreateDate>
    <id>0</id>
    <AnotherField>true</AnotherField>
  </UserTable>
</ArrayOfUserTable>

出现两个问题，第一个是

XML解析：第1行，第39个字符，无法切换编码

第二个问题是日期。

转换时转换失败来自字符串的日期时间。

这是我的 C# 代码。

using (TestDataContext db = new TestDataContext())
{
   UserTable[] testRecords = new UserTable[2];
   for (int count = 0; count < 2; count++)
   {
      UserTable testRecord = new UserTable();

      if (count == 1)
      {
         testRecord.CreateDate = DateTime.Now.AddYears(5);
         testRecord.AnotherField = true;
      }
      else
      {
         testRecord.CreateDate = DateTime.Now.AddYears(1);
         testRecord.AnotherField = false;
      }

      testRecords[count] = testRecord;
  }

  StringBuilder sBuilder = new StringBuilder();
  System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
  XmlSerializer serializer = new XmlSerializer(typeof(UserTable[]));
  serializer.Serialize(sWriter, testRecords);             

  using (SqlConnection con = new SqlConnection(connectionString))
  {
      string sprocName = "spTEST_UpdateTEST_TEST";

      using (SqlCommand cmd = new SqlCommand(sprocName, con))
      {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
         param1.Value = sBuilder.ToString();
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
      }
   }
}

因此，为了解决这两个问题，我只是手工编写了一个小的 xml 文件，该文件顶部没有 xml 标签，并且所有日期只有 MM/DD/YYYY 才能使之满意。

但它仍然不起作用

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[spTEST_UpdateTEST_TEST]    Script Date: 05/21/2010 11:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spTEST_UpdateTEST_TEST](@UpdatedProdData XML)
AS 
   UPDATE dbo.UserTable
   SET CreateDate =  @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')
   WHERE AnotherField =  @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/AnotherField)[1]', 'bit')

这甚至没有更新任何记录。另外，我仍然认为这只能处理一条记录，所以我不确定如何更改它以更新许多记录。

原文

I am using ms sql server 2005 and I want to do a mass update. I am thinking that I might be able to do it with sending an xml document to a stored procedure.

So I seen many examples on how to do it for insert

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData XML)
AS 
   INSERT INTO 
      dbo.UserTable(CreateDate)
      SELECT
         @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')

But I am not sure how it would look like for an update.

I am also unsure how do I pass in the xml through ado.net? Do I pass it as a string through a parameter or what?

I know sqlDataApater has a batch update method but I am using linq to sql. So I rather keep using it. So if this works I would be able to grab all records with linq to sql and have them as objects. Then manipulate the objects and use xml seralization.

Finally I could just use ado.net simple to send the xml to the server. This might be slower then the sqlDataAdapter but I am willing to take that hit if I can keep using objects.

Edit

Ok I have this so far

This is my XML

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfUserTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <UserTable>
    <CreateDate>2011-05-21T11:04:55.0584669-07:00</CreateDate>
    <id>0</id>
    <AnotherField>false</AnotherField>
  </UserTable>
  <UserTable>
    <CreateDate>2015-05-21T11:04:55.061467-07:00</CreateDate>
    <id>0</id>
    <AnotherField>true</AnotherField>
  </UserTable>
</ArrayOfUserTable>

Two problems occurs with this the first one is

XML parsing: line 1, character 39,
unable to switch the encoding

Second problem is with dates.

Conversion failed when converting
datetime from character string.

Here is my C# code.

using (TestDataContext db = new TestDataContext())
{
   UserTable[] testRecords = new UserTable[2];
   for (int count = 0; count < 2; count++)
   {
      UserTable testRecord = new UserTable();

      if (count == 1)
      {
         testRecord.CreateDate = DateTime.Now.AddYears(5);
         testRecord.AnotherField = true;
      }
      else
      {
         testRecord.CreateDate = DateTime.Now.AddYears(1);
         testRecord.AnotherField = false;
      }

      testRecords[count] = testRecord;
  }

  StringBuilder sBuilder = new StringBuilder();
  System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
  XmlSerializer serializer = new XmlSerializer(typeof(UserTable[]));
  serializer.Serialize(sWriter, testRecords);             

  using (SqlConnection con = new SqlConnection(connectionString))
  {
      string sprocName = "spTEST_UpdateTEST_TEST";

      using (SqlCommand cmd = new SqlCommand(sprocName, con))
      {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
         param1.Value = sBuilder.ToString();
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
      }
   }
}

So to get around those 2 issues I just hand coded a small xml file that did not have the xml tag on top of it and only had MM/DD/YYYY for all dates to make that happy.

But it still does not work

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[spTEST_UpdateTEST_TEST]    Script Date: 05/21/2010 11:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spTEST_UpdateTEST_TEST](@UpdatedProdData XML)
AS 
   UPDATE dbo.UserTable
   SET CreateDate =  @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')
   WHERE AnotherField =  @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/AnotherField)[1]', 'bit')

This does not even update any records. Also I still think this can only handle one record so I am not sure how to alter it to update many records.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

百变从容 2024-09-09 07:48:08

要从直接 ADO.NET 调用存储过程，您可以使用标准的 ADO.NET 内容，因为任何有关 .NET 数据访问的编程书籍或 ADO.NET 教程（仅限 Google！）都会教您：

using(SqlConnection con = new SqlConnection(your-connection-string-here))
{
     string sprocName = "spTEST_InsertXMLTEST_TEST";

     using(SqlCommand cmd = new SqlCommand(sprocName, con))
     {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
         param1.Value = YourXmlValueHere;
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
     }    
}

当然，您可能想将其包装到 try...catch 块中以进行异常处理等 - 但这基本上是您需要使用直接 ADO.NET 调用该存储过程的代码。

更新：为了从 XML 更新表，您应该查看 XQuery 中的 .nodes() 函数并编写如下更新语句：

UPDATE 
   dbo.UserTable
SET 
   CreateDate =  tbl.UPD.value('(CreateDate)[1]', 'DATETIME')
FROM
    @UpdatedProdData.nodes('/ArrayOfUserTable/UserTable') AS tbl(UPD)
WHERE 
    AnotherField =  tbl.UPD.value('(AnotherField)[1]', 'bit')

基本上，您将 XML 分解为名为 tbl(UPD) 的“虚拟”表 - 标记的每个条目现在都是该虚拟表中的一个“行” （因此您可以处理许多行），然后从该虚拟行获取数据来更新您的基表。

有关 SQL Server 2005 及更高版本中 SQL-XML XQuery 的真正精彩介绍，请查看这篇 15 秒 - 它极大地帮助我掌握了 XQuery 的可能性，以及如何在 SQL Server 的 XQuery 实现中做到这一点。

To call the stored proc from straight ADO.NET, you'd use the standard ADO.NET stuff as any programming book on .NET data access or ADO.NET tutorial (just Google for that!) will teach you:

using(SqlConnection con = new SqlConnection(your-connection-string-here))
{
     string sprocName = "spTEST_InsertXMLTEST_TEST";

     using(SqlCommand cmd = new SqlCommand(sprocName, con))
     {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
         param1.Value = YourXmlValueHere;
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
     }    
}

Of course, you might want to wrap this into a try...catch block for exception handling and so forth - but that's basically the code you'd need to call that stored proc using straight ADO.NET.

UPDATE: in order to update your table from the XML, you should check out the .nodes() function in XQuery and write your update statement something like this:

UPDATE 
   dbo.UserTable
SET 
   CreateDate =  tbl.UPD.value('(CreateDate)[1]', 'DATETIME')
FROM
    @UpdatedProdData.nodes('/ArrayOfUserTable/UserTable') AS tbl(UPD)
WHERE 
    AnotherField =  tbl.UPD.value('(AnotherField)[1]', 'bit')

Basically, you're shredding your XML into a "virtual" table called tbl(UPD) - each entry of an <UserTable> tag now is a "row" in that virtual table (thus you can handle many rows), and you grab data from that virtual row to update your base table.

For a really good introduction to SQL-XML XQuery in SQL Server 2005 and up, check out this article on 15 Seconds - it's helped me tremendously to get a grasp on what is possible with XQuery, and how to do it in SQL Server's XQuery implementation.

回复收藏 0 原文

初见 2024-09-09 07:48:08

我认为您可以将 XML 放入临时表中，然后将其用于存储过程的更新部分。但不确定这是否能回答问题！

回复收藏 0 原文

梦萦几度 2024-09-09 07:48:08

为此，将 XMLDOC1 作为过程中的参数传递。
在你的.net代码中写入dataset.Writexml
这将赋予字符串变量并将该字符串作为参数传递给过程。
下面是如何在过程中获取数据的示例。
@XMLDoc1 as text

声明 @idoc1 as int

EXEC sp_xml_preparedocument @idoc1 OUTPUT, @XMLDoc1

Select * into #TableName
FROM OPENXML(@idoc1,'/NewDataSet/Tablename',2)
WITH（表结构如下）
(PrefDetailID int
,PrefID int )

--select * from #TableName

EXEC sp_xml_removedocument @idoc1
现在您已在 #TableName 中获取了所有数据，并按照您喜欢的方式在程序中进行任何插入或更新操作
或查看下面的链接这是我自己的博客
http://sqlmca.wordpress.com/2009/07/29/how-to-get-data-from-dataset-into-sqlserver-table-by-using-openxml-method/