代码之家 › 专栏 › 技术社区 › Will Rickards

如何为odbccommand对象的sql server xml数据类型创建odbcparameter?

odbc ado.net xml sql-server sql

Will Rickards · 技术社区 · 17 年前

因此,我需要将一组数据传递给服务器,以创建一组记录。所以我读了 this article 并选择了XML方法。所以我得到了所有在SQLServer2005中创建的存储过程。但是现在我需要从asp.net代码中调用它。数据库连接只有ODBC连接。我看到sqlclient似乎有一个原生的sqldbtype.xml。如果使用odbc,我该怎么办?我是索尔吗?

3 回复 | 直到 17 年前

shahkalpesh 17 年前

话虽如此,您可以使用VS.net服务器资源管理器,使用ODBC连接到SQL Server,使用存储过程展开树,并使用XML参数查看存储过程。

编辑:看看这是否正确 link 有帮助。请参阅此链接上的DataTypeCompatibility word。

Will Rickards 17 年前

ADO.Net代码

Private Sub cmdXMLTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdXMLTest.Click

   Dim objConnection As System.Data.Odbc.OdbcConnection
   Dim cmdTest As System.Data.Odbc.OdbcCommand
   Dim drTest As System.Data.Odbc.OdbcDataReader
   Dim intCount As System.Int32
   Dim strMatter As System.String = ""
   Dim strXML As System.String = "<Root><Matter mmatter=""11415.09951"" /><Matter mmatter=""06625.06771"" /><Matter mmatter=""00931.09846"" /></Root>"

   ' build odbc command for getting parties
   cmdTest = New System.Data.Odbc.OdbcCommand
   cmdTest.CommandType = CommandType.StoredProcedure
   cmdTest.CommandTimeout = 60
   cmdTest.CommandText = "{CALL uspXMLTest (?)}"
   cmdTest.Parameters.Add("@list", System.Data.Odbc.OdbcType.NText).Value = strXML

   objConnection = New System.Data.Odbc.OdbcConnection("Driver={SQL Server};Server=mysqlserver;Database=son_db;Uid=sa;Pwd=mypassword;")
   objConnection.Open()
   cmdTest.Connection = objConnection
   drTest = cmdTest.ExecuteReader(CommandBehavior.CloseConnection)

   Do While drTest.Read()

      strMatter = drTest.GetString(0)
      intCount = intCount + 1

   Loop

   MsgBox("Found " & intCount.ToString() & " Matters - Last One = " & strMatter)

End Sub

CREATE PROCEDURE uspXMLTest
    @list xml
AS

SET NOCOUNT ON

SELECT matter.mmatter, matter.mdesc1
FROM matter
     INNER JOIN @list.nodes('/Root/Matter') AS ml(mmatter) ON (matter.mmatter = ml.mmatter.value('@mmatter', 'varchar(15)'))

GO

Will Rickards 17 年前

对于使用XMLTextWriter创建XML的用户,需要使用UTF-16编码(Unicode在.net中)。下面是创建XML的代码。

  ' create xml file for folders
  strmFolderList = New System.IO.MemoryStream()
  wrtFolderList = New System.Xml.XmlTextWriter(strmFolderList, System.Text.Encoding.Unicode)

  ' start document and add root element
  wrtFolderList.WriteStartDocument()
  wrtFolderList.WriteStartElement("Root")

  ' cycle through folders
  For intIndex = 0 To m_intAdditionalFolderQuantity - 1

     ' figure out folder description
     strFolderDesc = m_arrFolderDesc(intIndex)

     ' add element to xml
     wrtFolderList.WriteStartElement("Folder")
     wrtFolderList.WriteAttributeString("folderdesc", strFolderDesc)
     wrtFolderList.WriteAttributeString("insertfolder", ((intIndex + 1) * -1).ToString())
     wrtFolderList.WriteEndElement()

  Next

  ' close root element and document
  wrtFolderList.WriteEndElement()
  wrtFolderList.WriteEndDocument()
  wrtFolderList.Close()
  'System.Text.Encoding.Unicode.GetString(strmFolderList.ToArray())

下面是调用存储过程的代码

  cmdAddRequest = New System.Data.Odbc.OdbcCommand
  cmdAddRequest.CommandType = CommandType.StoredProcedure
  cmdAddRequest.CommandTimeout = 60
  cmdAddRequest.CommandText = "{CALL uspAddRequest ( ?, ?, ?, ?, ?, ?, ?, ?, ?)}"

  ' add parameters to odbc command
  cmdAddRequest.Parameters.Add("@ruserid", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strUID)
  cmdAddRequest.Parameters.Add("@rmatter", OdbcType.VarChar, 15).Value = SafeODBCParamString(m_strMatterNumber)
  cmdAddRequest.Parameters.Add("@req_tkinit", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strRequesting)
  cmdAddRequest.Parameters.Add("@ret_tkinit", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strReturnNumber)
  cmdAddRequest.Parameters.Add("@requestor", OdbcType.VarChar, 20).Value = SafeODBCParamString(m_strRequestor)
  cmdAddRequest.Parameters.Add("@labels_only", OdbcType.Bit).Value = m_blnLabelsOnly
  cmdAddRequest.Parameters.Add("@folder_quantity", OdbcType.SmallInt).Value = m_intAdditionalFolderQuantity
  cmdAddRequest.Parameters.Add("@folder_list", OdbcType.NText).Value = System.Text.Encoding.Unicode.GetString(strmFolderList.ToArray())
  cmdAddRequest.Parameters.Add("@insert_list", OdbcType.NText).Value = System.Text.Encoding.Unicode.GetString(strmInsertList.ToArray())