strsplit.vb

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

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



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.None, _
       TableDefinition:="pid int, pName NVARCHAR(40),cid int ")> _
    Public Shared Function fn_SplitString(ByVal inputStr As SqlString, _
      ByVal pSeparator As SqlString) As IEnumerable
        Dim lStr As String
        Dim lsplitedStr() As String
        Dim lSeparator(0) As Char
        lSeparator(0) = CChar(pSeparator.Value(0))
        If (inputStr.IsNull) Then
            lStr = ""
        Else
            lStr = inputStr.Value
        End If
        lsplitedStr = lStr.Split(lSeparator, _
        StringSplitOptions.RemoveEmptyEntries)
        Dim Splitedtable As New List(Of ProductRecord)
        Dim Str As SqlString
        Str = ","
        Dim Splitter(0) As Char
        Splitter(0) = CChar(Str.Value(0))
        For Each s As String In lsplitedStr
            Dim recordStr() As String
            Dim lrecord As New ProductRecord
            recordStr = s.Split(Splitter, StringSplitOptions.RemoveEmptyEntries)
            lrecord.Productid = Val(recordStr(0))
            lrecord.ProductName = recordStr(1)
            lrecord.CategoryID = Val(recordStr(2))
            Splitedtable.Add(lrecord)
        Next
        Return Splitedtable
    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