form1.vb

SQL Server 业已成为最为流行的几大数据库之一

Imports Microsoft.Data.SqlXml
Imports System
Imports System.IO

Public Class Form1

    Dim mainQueryStr As String
    Dim subQueryStr As String

    Dim cmd As SqlXmlCommand
    Dim Param As SqlXmlParameter


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim ConnectString As String

        subQueryStr = "FOR XML AUTO"
        mainQueryStr = "SELECT CategoryID, CategoryName,Description FROM Categories "

        ConnectString = _
        "Provider=SQLOLEDB;Server=(local);database=Northwind;UID=sa;PWD=19zhoulian7172"
        cmd = New SqlXmlCommand(ConnectString)
        Param = cmd.CreateParameter()
        DoSearch()

    End Sub

    Private Sub btDoSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btDoSearch.Click

        subQueryStr = "FOR XML AUTO"
        subQueryStr = "WHERE CategoryID=? " + subQueryStr
        DoSearch()

    End Sub
    Private Sub DoSearch()

        Dim MyStrm As Stream
        tbResults.Text = ""

        Param.Value = tbCategoryID.Text
        cmd.CommandText = mainQueryStr + subQueryStr


        Try
            MyStrm = cmd.ExecuteStream
            MyStrm.Position = 0
            Dim StrRdr As StreamReader = New StreamReader(MyStrm)
            Me.tbResults.Text = StrRdr.ReadToEnd()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try

    End Sub

    Private Sub btUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btUpdate.Click

        Dim ds As DataSet = New DataSet
        Dim Adapt As SqlXmlAdapter

        cmd.SchemaPath = "D:\SqlDemo\Categories.xsd"

        Adapt = New SqlXmlAdapter(cmd)
        Adapt.Fill(ds)
        Dim record As DataRow
        record = ds.Tables(0).Rows(0)
        record("Description") = tbDescription.Text
        Adapt.Update(ds)
        DoSearch()
    End Sub
End Class