代码之家  ›  专栏  ›  技术社区  ›  momo

将XML格式的Web数据传递到SQL Server数据库的合理方法

asp-classic xml sql-server javascript
  •  2
  • momo  · 技术社区  · 15 年前

    在探索了几种不同的方法将web数据传递到数据库以进行更新之后,我想知道XML是否是一种好的策略。该数据库目前是SQL 2000。在几个月内,它将移动到SQL 2005,我将能够改变东西,如果需要的话,但我现在需要一个SQL 2000解决方案。

    EAV model . 我知道,这种数据库通常是高度反对的,所以对于这个问题,请接受这是不会改变的。

    到目前为止,一次只需要更新一个元素。但是现在,需要能够一次编辑多个元素,还需要支持层次元素,每个层次元素都可以有自己的属性列表。下面是一些我手工输入的XML示例,以演示我的想法。

    注意,在这个数据库中,实体是Element,ID为0表示“create”,即插入一个新项。 

    <Elements>
  <Element ID="1234">
    <Attr ID="221">Value</Attr>
    <Attr ID="225">287</Attr>
    <Attr ID="234">
      <Element ID="99825">
        <Attr ID="7">Value1</Attr>
        <Attr ID="8">Value2</Attr>
        <Attr ID="9" Action="delete" />
      </Element>
      <Element ID="99826" Action="delete" />
      <Element ID="0" Type="24">
        <Attr ID="7">Value4</Attr>
        <Attr ID="8">Value5</Attr>
        <Attr ID="9">Value6</Attr>
      </Element>
      <Element ID="0" Type="24">
        <Attr ID="7">Value7</Attr>
        <Attr ID="8">Value8</Attr>
        <Attr ID="9">Value9</Attr>
      </Element>
    </Attr>
    <Rel ID="3827" Action="delete" />
    <Rel ID="2284" Role="parent">
      <Element ID="3827" />
      <Element ID="3829" />
      <Attr ID="665">1</Attr>
    </Rel>
    <Rel ID="0" Type="23" Role="child">
      <Element ID="3830" />
      <Attr ID="67"
    </Rel>
  </Element>
  <Element ID="0" Type="87">
    <Attr ID="221">Value</Attr>
    <Attr ID="225">569</Attr>
    <Attr ID="234">
      <Element ID="0" Type="24">
        <Attr ID="7">Value10</Attr>
        <Attr ID="8">Value11</Attr>
        <Attr ID="9">Value12</Attr>
      </Element>
    </Attr>
  </Element>
  <Element ID="1235" Action="delete" />
</Elements>

    有些属性是直接值类型,比如attrid221。但是attrid234是一种特殊的“多值”类型,它下面可以有一个元素列表,每个元素可以有一个或多个值。类型只需要在创建新项时显示,因为如果类型已经存在,ElementID将完全暗示该类型。我可能只支持传入更改的项(由javascript检测)。而且在Attr元素上也可能有Action=“Delete”,因为null被视为“未选择的”--有时知道一个Yes/No问题是否被故意回答为No或者是否还没有人愿意说Yes是非常重要的。

    还有一种不同的数据,一种关系。此时,在UI中编辑内容时,这些内容会通过单独的AJAX调用进行更新,但我希望包含这些内容,以便可以取消对关系的更改(现在,一旦您更改了关系,就完成了更改)。所以这些元素也是元素,但是它们被称为Rel而不是Element。关系被实现为ElementID1和ElementID2,因此上面XML中的RelID 2284在数据库中是:

    元素ID 2284元素ID 1 1234元素ID 2 3827

    这个策略和示例XML有意义吗?有没有更明智的办法?我只是在寻找一些广泛的批评来帮助我避免走上一条不好的道路。任何你想评论的方面都会有帮助。

    web语言碰巧是经典的ASP,但在某个时候可能会变成ASP.Net。像Linq或nHibernate这样的持久性引擎现在可能是不可接受的——我只想在不占用大量开发时间的情况下增强这个已经可以工作的应用程序。

    我会选择一个能显示经验的答案,并且能很好地警告我不要做什么,确认我计划做什么,以及建议我做其他事情。我会尽量客观。

    另外,我想处理unicode字符以及非常长的字符串(10k+)。

    我已经让它工作了一段时间,我使用ADO记录集保存到流的技巧使创建XML变得非常简单。结果似乎相当快,但如果速度成为一个问题,我可能会重新讨论这个问题。

    同时,我的代码可以一次处理页面上任意数量的元素和属性,包括一次性更新、删除和创建新项。

    • 现有数据元素

      示例:输入名称e12345_a678(元素12345,属性678),输入值是属性的值。

    • Javascript将该类型所需的HTML元素集的隐藏模板复制到页面上的正确位置,增加一个计数器以获取该项的新ID,并在表单项的名称前面加上数字。 

      var newid = 0;

function metadataAdd(reference, nameid, value) {
   var t = document.createElement('input');
   t.setAttribute('name', nameid);
   t.setAttribute('id', nameid);
   t.setAttribute('type', 'hidden');
   t.setAttribute('value', value);
   reference.appendChild(t);
}

function multiAdd(target, parentelementid, attrid, elementtypeid) {
   var proto = document.getElementById('a' + attrid + '_proto');
   var instance = document.createElement('p');
   target.parentNode.parentNode.insertBefore(instance, target.parentNode);
   var thisid = ++newid;
   instance.innerHTML = proto.innerHTML.replace(/{prefix}/g, 'n' + thisid + '_');
   instance.id = 'n' + thisid;  
   instance.className += ' new';
   metadataAdd(instance, 'n' + thisid + '_p', parentelementid);
   metadataAdd(instance, 'n' + thisid + '_c', attrid);
   metadataAdd(instance, 'n' + thisid + '_t', elementtypeid);
   return false;
}

      示例:Template input name\u a678变成n1\u a678(一个新元素,页面上的第一个元素,属性678)。这个新元素的所有属性都用相同的前缀n1标记。下一个新项目将是n2,依此类推。创建了一些隐藏的表单输入:

      n1\u t,value是要创建的元素的elementtype n1\u p,value是元素的父id(如果是关系) n1\u c,value是元素的子id(如果是关系)

    • 删除元素

      在值设置为0的表单e12345\t中创建了一个隐藏输入。显示该属性值的现有控件被禁用,因此不包括在表单post中。所以“将类型设置为0”被视为删除。

    使用此方案,页面上的每个项目都有一个唯一的名称,可以正确地进行区分,并且可以正确地表示每个操作。

    发布表单时,以下是构建所用两个记录集之一的示例(经典ASP代码): 

    Set Data = Server.CreateObject("ADODB.Recordset")
Data.Fields.Append "ElementID", adInteger, 4, adFldKeyColumn
Data.Fields.Append "AttrID", adInteger, 4, adFldKeyColumn
Data.Fields.Append "Value", adLongVarWChar, 2147483647, adFldIsNullable Or adFldMayBeNull
Data.CursorLocation = adUseClient
Data.CursorType = adOpenDynamic
Data.Open

    这是值的记录集,另一个是元素本身的记录集。

    我逐步浏览发布的表单,并为元素记录集使用Scripting.Dictionary,其中填充了具有所需属性的自定义类的实例,这样我就可以逐段添加值,因为它们并不总是按顺序排列的。新元素被添加为负数,以区别于常规元素(而不是要求一个单独的列来指示它是新的还是处理现有的元素)。我使用正则表达式来分解表单键:“^(e | n)([0-9]{1,10})|(a | p | t | c)([0-9]{0,10})$”

    然后,添加一个属性如下所示。 

    Data.AddNew
ElementID.Value = DataID
AttrID.Value = Integerize(Matches(0).SubMatches(3))
AttrValue.Value = Request.Form(Key)
Data.Update

    ElementID、AttrID和AttrValue是对记录集字段的引用。这种方法比每次使用Data.Fields(“ElementID”).Value要快得多。 

    Set Cmd = Server.CreateObject("ADODB.Command")
With Cmd
   Set .ActiveConnection = MyDBConn
   .CommandType = adCmdStoredProc
   .CommandText = "DataPost"
   .Prepared = False
   .Parameters.Append .CreateParameter("@ElementMetadata", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Element))
   .Parameters.Append .CreateParameter("@ElementData", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Data))
End With
Result.Open Cmd ' previously created recordset object with options set


    Private Function XMLFromRecordset(Recordset)
   Dim Stream
   Set Stream = Server.CreateObject("ADODB.Stream")
   Stream.Open
   Recordset.Save Stream, adPersistXML
   Stream.Position = 0
   XMLFromRecordset = Stream.ReadText
End Function

    为了防止web页面需要知道,SP返回一个新元素的记录集,显示它们的页面值和创建的值(例如,我可以看到n1现在是e12346)。

    下面是存储过程中的一些关键片段。请注意,目前这是SQL 2000,不过我很快就能切换到2005: 

    CREATE PROCEDURE [dbo].[DataPost]
   @ElementMetaData ntext,
   @ElementData ntext
AS
DECLARE @hdoc int

--- snip ---

EXEC sp_xml_preparedocument @hdoc OUTPUT, @ElementMetaData, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" />'
INSERT #ElementMetadata (ElementID, ElementTypeID, ElementID1, ElementID2)
SELECT *
FROM
   OPENXML(@hdoc, '/xml/rs:data/rs:insert/z:row', 0)
   WITH (
      ElementID int,
      ElementTypeID int,
      ElementID1 int,
      ElementID2 int
   )
ORDER BY ElementID -- orders negative items (new elements) first so they begin counting at 1 for later ID calculation

EXEC sp_xml_removedocument @hdoc

--- snip ---

UPDATE E
SET E.ElementTypeID = M.ElementTypeID
FROM
   Element E
   INNER JOIN #ElementMetadata M ON E.ElementID = M.ElementID
WHERE
   E.ElementID >= 1
   AND M.ElementTypeID >= 1

    以下查询将负新元素ID与新插入的元素ID进行关联: 

    UPDATE #ElementMetadata -- Correlate the new ElementIDs with the input rows
SET NewElementID = Scope_Identity() - @@RowCount + DataID
WHERE ElementID < 0

    其他基于集合的查询执行所有其他工作,包括验证属性是否被允许、数据类型是否正确,以及插入、更新和删除元素和属性。

    我希望这篇简短的总结有一天对其他人有用!将ADO记录集转换为XML流对我来说是一个巨大的赢家,因为它节省了各种时间,并且已经定义了一个名称空间和模式,可以正确地显示结果。

    使用带有2个输入的更平坦的XML格式也比坚持将所有内容都放在一个XML流中的理想方式容易得多。

    2 回复  |  直到 14 年前
        1
  •  4
  •   Remus Rusanu    15 年前

    格式 数据库和应用程序(在本例中是一个web应用程序)之间的数据。

    好的 想想看。但最重要的优点是,您可以利用数据库性能的圣杯: 面向集合的处理 nodes 和 value ,结合一些适度的XPath fu技巧,可以将从应用程序接收的整个XML参数分解为关系集,并使用面向集的操作来编写数据库。

    以帖子中的XML为例,假设它是作为XML类型的@x参数传递的。您可以将其分解为属性以合并到现有元素中: 

    select x.value(N'@ID', N'int') as ID,
  x.value(N'.', N'varchar(max)') as [Value]
from  @x.nodes('//Element[not(@Action="delete") and not (@ID=0)]/Attr') t(x)


    select x.value(N'@ID', N'int') as ID,
  x.value(N'.', N'varchar(max)') as [Value]
from  @x.nodes('//Element[@ID=0]/Attr') t(x);

    你可以切碎要删除的元素: 

    select x.value(N'@ID', N'int') as ID
from  @x.nodes('//Element[@Action="delete"]') t(x);

    套 可以通过普通的SQLDML进行操作:插入、删除、更新或 merged 进入EAV表格,一次完成。请注意,我在这里展示的XML分解只是一些琐碎的东西,对您来说可能是不正确的,但只是为了说明如何进行分解。

    现在不管这是不是 最好的 相当地 (有时很混乱)XPath、XQuery、名称空间、编码、cdata等层。

        2
  •  0
  •   John Saunders    15 年前

    我不认为有任何理由不在SQLServer2005中使用XML列,而是通过存储过程来完成所有工作。

    现在我想起来了,您仍然可以在ASP页面和数据库之间添加一层抽象。这将允许您将来使用XSLT将XML的结构转换为在数据库中性能更好的格式。

    推荐文章
    K.Z  ·  无法使用ADO获取Insert命令的记录Id。NET 4.0中的NET
    2 年前
    Duvan  ·  将逗号(,)替换为点(.)[副本]
    2 年前
    Yara1994  ·  如果另一列具有特定值-SQL Server,则删除具有唯一Id的所有行
    2 年前
    Simon Worth  ·  是否可以使用SqlClient自动获取查询字符串中定义的参数列表
    2 年前
    Mateen Bagheri  ·  选择表的计数并选择其自身
    2 年前
    SoT  ·  SQL Server中求和函数的工作方式
    2 年前
    rapha123  ·  如何使用REPLACE函数使用SQL更改这两个单词?
    2 年前
    NKAT  ·  将列值聚合到列表中会产生错误
    2 年前
    deanpillow  ·  返回两列中有一个匹配值的记录
    2 年前
    snowflakes74  ·  在Dapper中异步查询多个结果
    2 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号