CLR: using regular expressions in T-SQL

<SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function udfRegExMatch(ByVal pattern As String, _
ByVal matchString As String) As Boolean

Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing))
Return r1.Match(matchString.TrimEnd(Nothing)).Success

End Function

Advertisements

CLR: run a SQL script from a .sql file

Public Function RunSQLScript(ByVal Filename As String, ByRef Errortext As String) As Boolean
Dim file As New FileInfo(Filename)
Dim script As String = String.Empty
Dim command As SqlCommand = Nothing
Try
script = file.OpenText().ReadToEnd()
Catch ex As Exception
Errortext = ex.Message
RunSQLScript = False
Exit Function
End Try
script = script.Replace(“GO”, “”)
‘Optional to Replace Comments with empty string
script = Regex.Replace(script, “([/*][*]).*([*][/])”, “”)
‘Optional to Replace Chain of spaces with one Space
script = Regex.Replace(script, “\\s{2,}”, ” “)
Try
command = DBCnn.CreateCommand
command.CommandType = CommandType.Text
command.CommandText = script
command.ExecuteNonQuery()
RunSQLScript = True
Catch ex As SqlException
Errortext = ex.Message
RunSQLScript = False
End Try
DBCnnClose()
End Function

Public Function RunSQLScript(ByVal Filename As String, ByRef Errortext As String) As Boolean
Dim file As New FileInfo(Filename)        Dim script As String = String.Empty        Dim command As SqlCommand = Nothing
Try
script = file.OpenText().ReadToEnd()
Catch ex As Exception
Errortext = ex.Message            RunSQLScript = False            Exit Function
End Try
script = script.Replace(“GO”, “”)        ‘Optional to Replace Comments with empty string        script = Regex.Replace(script, “([/*][*]).*([*][/])”, “”)        ‘Optional to Replace Chain of spaces with one Space        script = Regex.Replace(script, “\\s{2,}”, ” “)
Try
command = DBCnn.CreateCommand            command.CommandType = CommandType.Text            command.CommandText = script            command.ExecuteNonQuery()            RunSQLScript = True
Catch ex As SqlException
Errortext = ex.Message            RunSQLScript = False
End Try
DBCnnClose()
End Function

CLR: strip discouraged characters from a string for xml vb.net

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
”’ <summary>
”’ removes discouraged characters
”’ obsolete, sobald LogicBase.CLR überall aktualisiert wurde => Version > 1.0.3804.21154
”’ </summary>
”’ <param name=”Content”>string for xml</param>
”’ <returns>clean SqlString</returns>
”’ <remarks>
”’ Response is not well-formed XML System.Xml.XmlException XML System.Xml.XmlException , hexadecimal value 0x2, is an invalid character.
”’ see http://www.w3.org documentation:
”’ </remarks>
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udfLBXMLRemoveDiscouragedCharacters(ByVal Content As String) As SqlString
Dim result As New System.Text.StringBuilder()
‘current character
Dim cC As Char
If Content Is Nothing OrElse Content = String.Empty Then
Return String.Empty
End If
For i As Integer = 0 To Content.Length – 1
cC = Content(i)
If (AscW(cC) = &H9 OrElse AscW(cC) = &HA OrElse AscW(cC) = &HD) _
OrElse ((AscW(cC) >= &H20) AndAlso (AscW(cC) <= &HD7FF)) _
OrElse ((AscW(cC) >= &HE000) AndAlso (AscW(cC) <= &HFFFD)) _
OrElse ((AscW(cC) >= &H10000) AndAlso (AscW(cC) <= &H10FFFF)) Then
result.Append(cC)
End If
Next
Return New SqlString(result.ToString())
End Function
End Class

Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions    ”’ <summary>    ”’ removes discouraged characters     ”’ obsolete, sobald LogicBase.CLR überall aktualisiert wurde => Version > 1.0.3804.21154    ”’ </summary>    ”’ <param name=”Content”>string for xml</param>    ”’ <returns>clean SqlString</returns>    ”’ <remarks>    ”’ Response is not well-formed XML System.Xml.XmlException XML System.Xml.XmlException , hexadecimal value 0x2, is an invalid character.    ”’ see http://www.w3.org documentation:    ”’ http://www.w3.org/TR/2008/REC-xml-20081126/#charsets    ”’ http://www.w3.org/TR/xml11/#charsets    ”’ </remarks>    <Microsoft.SqlServer.Server.SqlFunction()> _    Public Shared Function udfLBXMLRemoveDiscouragedCharacters(ByVal Content As String) As SqlString
Dim result As New System.Text.StringBuilder()
‘current character        Dim cC As Char
If Content Is Nothing OrElse Content = String.Empty Then            Return String.Empty        End If
For i As Integer = 0 To Content.Length – 1
cC = Content(i)
If (AscW(cC) = &H9 OrElse AscW(cC) = &HA OrElse AscW(cC) = &HD) _             OrElse ((AscW(cC) >= &H20) AndAlso (AscW(cC) <= &HD7FF)) _             OrElse ((AscW(cC) >= &HE000) AndAlso (AscW(cC) <= &HFFFD)) _             OrElse ((AscW(cC) >= &H10000) AndAlso (AscW(cC) <= &H10FFFF)) Then                result.Append(cC)            End If
Next
Return New SqlString(result.ToString())
End FunctionEnd Class

CLR: arithmetic overflow while insert into a table containing a datetime column

problem: you get an arithmetic overflow error while you want in your CLR stored procedure to insert into a table containing a datetime column
reason: datetime-format on sql serveris set up differently than you use it
workaround:  you can use SET DATEFORMAT before you call you CLR stored procedures.

example:

USE Database
GO

DECLARE    @iReturnvalue int
–set datetime format you want
SET DATEFORMAT ymd;
GO

— call CLR stored procedure
EXEC   [dbo].[sp] @iMandant = 1, @iReturnvalue = @iReturnvalue OUTPUT

vba: convert a datetime-string yyyymmddhhmmss to date

Public Function Conv2Datetime(sDatetimestamp As String) As Date
‘Format of sDatetimestamp:= yyyymmddhhmmss

Dim dtm As Date
Dim dS As Date
Dim dT As Date

dS = DateSerial(CInt(Left(sDatetimestamp, 4)), CInt(Mid(sDatetimestamp, 5, 2)), CInt(Mid(sDatetimestamp, 7, 2)))
dT = TimeSerial(CInt(Mid(sDatetimestamp, 9, 2)), CInt(Mid(sDatetimestamp, 11, 2)), CInt(Mid(sDatetimestamp, 13, 2)))
dtm = CDate(Str(dS) + ” ” + Str(dT))

Conv2Datetime = dtm

End Function

Public Function Conv2Date(sDatetimestamp As String) As Date
‘Format of sDatetimestamp:= yyyymmddhhmmss

Dim dS As Date

dS = DateSerial(CInt(Left(sDatetimestamp, 4)), CInt(Mid(sDatetimestamp, 5, 2)), CInt(Mid(sDatetimestamp, 7, 2)))

Conv2Date = dS

End Function

Public Function Conv2Time(sDatetimestamp As String) As Date
‘Format of sDatetimestamp:= yyyymmddhhmmss

Dim dT As Date

dT = TimeSerial(CInt(Mid(sDatetimestamp, 9, 2)), CInt(Mid(sDatetimestamp, 11, 2)), CInt(Mid(sDatetimestamp, 13, 2)))

Conv2Time = dT

End Function

get imported namespaces of a schema and their schema location


''' <summary>
''' get all imported namespaces of a schema and their schema location
''' </summary>
''' <param name="oSchema">schmema</param>
''' <returns>Dictionary(imported namespace, schema location) </returns>
''' <remarks></remarks>
Function RecurseExternalSchemas(ByVal oSchema As XmlSchema) As Dictionary(Of String, String)
Dim oResult As New Dictionary(Of String, String)
For Each oExternal As XmlSchemaExternal In oSchema.Includes
If oExternal.GetType() Is GetType(XmlSchemaImport) Then
Dim import As XmlSchemaImport = CType(oExternal, XmlSchemaImport)
If Not oExternal.SchemaLocation = Nothing Then
oResult.Add(import.Namespace, oExternal.SchemaLocation)
Else
oResult.Add(import.Namespace, String.Empty)
End If
End If
If Not oExternal.Schema Is Nothing Then
RecurseExternalSchemas(oExternal.Schema)
End If
Next
RecurseExternalSchemas = oResult
End Function

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..

how to check if a column in a datareader exists

”’ <summary>
”’ checks if a column in a datareader exists
”’ </summary>
”’ <param name=”reader”>SqlDataReader</param>
”’ <param name=”sColumnName”>columnname</param>
”’ <returns>true, column exists else false</returns>
”’ <remarks></remarks>
Public Function ColumnExists(ByVal reader As SqlDataReader, ByVal sColumnName As String) As Boolean
reader.GetSchemaTable().DefaultView.RowFilter = “ColumnName= ‘” + sColumnName + “‘”
Return (reader.GetSchemaTable().DefaultView.Count > 0)
End Function

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..