returntable.vb

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

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class UserDefinedFunctions
    '定义一个保存产品记录的结构
    Structure ProductRecord
        Dim Productid As Integer
        Dim ProductName As String
        Dim CategoryID As Integer
    End Structure


    '创建一个以指定字符为分隔符的分解函数，将一个字符串转换为数据表的格式；
    '并返回该数据表
    <SqlFunction(FillRowMethodName:="strToTable", _
       DataAccess:=DataAccessKind.Read, _
       TableDefinition:="pid int, pName NVARCHAR(40),cid int ")> _
    Public Shared Function ReturnTable(ByVal pCategoryID As Integer) As IEnumerable

        Dim conn As SqlConnection = New SqlConnection("context connection = true")
        conn.Open()
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT Productid,ProductName,CategoryId FROM Products WHERE Categoryid =" + pCategoryID.ToString
        Dim rd As SqlDataReader = cmd.ExecuteReader()
        Dim lrecord As New ProductRecord

        Dim Returnedtable As New List(Of ProductRecord)
        Dim i As Integer
        While rd.Read()

            lrecord.Productid = Val(rd.GetValue(0).ToString)
            lrecord.ProductName = rd.GetValue(1).ToString
            lrecord.CategoryID = Val(rd.GetValue(2).ToString)
            Returnedtable.Add(lrecord)

        End While

        rd.Close()
        Return Returnedtable
    End Function

    Public Shared Sub strToTable( _
    ByVal obj As Object, <Runtime.InteropServices.Out()> ByRef Pid As Integer, _
      <Runtime.InteropServices.Out()> ByRef pName As String, <Runtime.InteropServices.Out()> ByRef Cid As Integer)
        Pid = CType(obj, ProductRecord).Productid
        pName = CType(obj, ProductRecord).ProductName
        Cid = CType(obj, ProductRecord).CategoryID
    End Sub

End Class