|
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Imports System.Text.RegularExpressions
Partial Public Class clr_sp_hyperlink
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub clr_sp_hyperlink(ByVal sInputFile As SqlString, ByVal tbl As SqlString, ByVal col As SqlString)
Dim sContents As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim qry As New SqlCommand()
Dim stmReader As New StreamReader(sInputFile.ToString)
Try
'Create a string reader and pass it the input parameter of the
'file path. The sContents variable will receive the content of
'the stream reader
sContents = stmReader.ReadToEnd()
stmReader.Close()
Dim regExPattern As String
regExPattern = "<a\s+href\s*=\s*""?([^"" >]+)""?>(.+)</a>"
Dim re As New Regex(regExPattern, RegexOptions.IgnoreCase)
Dim m As Match = re.Match(sContents)
'sp.Send(m.Groups(1).Value & " " & sContents)
While m.Success
Dim str As String
str = m.Groups(1).Value
'string variable is trimmed from trailing and ending white space to
'see if single quotes are present
str.TrimStart()
str.TrimEnd()
'If single quotes are not the first and last character than they are
'added for the insert statement
If str.Substring(0, 1) <> "'" Then
str = "'" & str
End If
If str.Substring(str.Length - 1, 1) <> "'" Then
str = str + "'"
End If
'Build the query using the str variable
qry.CommandText = " INSERT " & tbl.ToString & "(" & col.ToString & _
") VALUES ( " & str & ")"
'This will send the insert command to the Messages tab which will make it
'easier to troubleshoot
sp.Send(" INSERT hyper VALUES ( " & str & _
")")
'Execute the command object
sp.ExecuteAndSend(qry)
'Reset the str variable to nothing
str = ""
m = m.NextMatch()
End While
Catch ex As Exception
sp.Send(ex.Message.ToString.Substring(1, 7000))
End Try
End Sub
End Class
SQL CODE HERE
CREATE TABLE dbo.hyper(
id int IDENTITY(1,1) NOT NULL,
link varchar(4000) NULL)
USE AdventureWorks
EXEC clr_sp_hyperlink 'c:\links.htm',
'hyper',
'link'
SELECT *
FROM hyper
|