<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
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
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
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
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
''' <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. ::..
”’ <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. ::..
Sub RecursiveWalkThroughXpath(ByVal oNavigator As XPathNavigator)
Select Case oNavigator.NodeType
Case XPathNodeType.Element
If oNavigator.Prefix = String.Empty Then
Console.WriteLine(“<{0}>”, oNavigator.LocalName)
Else
Console.Write(“<{0}:{1}>”, oNavigator.Prefix, oNavigator.LocalName)
Console.WriteLine(vbTab + oNavigator.NamespaceURI)
End If
Case XPathNodeType.Text
Console.WriteLine(vbTab + oNavigator.Value)
End Select
If oNavigator.MoveToFirstChild() Then
Do
RecursiveWalkThroughXpath(oNavigator)
Loop While (oNavigator.MoveToNext())
oNavigator.MoveToParent()
If (oNavigator.NodeType = XPathNodeType.Element) Then
Console.WriteLine(“</{0}>”, oNavigator.Name)
End If
Else
If oNavigator.NodeType = XPathNodeType.Element Then
Console.WriteLine(“</{0}>”, oNavigator.Name)
End If
End If
End Sub
..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub spLBgenerateInstallscript( _
ByVal sTablename As String, _
<Out()> ByRef iReturnvalue As SqlInt32)
Dim oCommand As SqlCommand
Dim oDom As New Xml.XmlDocument
Dim oDom2 As New Xml.XmlDocument
Dim oReader As Xml.XmlReader
Dim sXML As String = String.Empty
Dim oSB As New StringBuilder
Dim sOutputfilename As String
Using oConnection As New SqlConnection(“context connection=true”)
Try
oConnection.Open()
oCommand = oConnection.CreateCommand
oCommand.CommandText = “SELECT * FROM ” & sTablename & ” FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS, ROOT (‘” & sTablename & “‘)”
oCommand.CommandType = CommandType.Text
oReader = oCommand.ExecuteXmlReader
oReader.Read()
Do While oReader.ReadState <> Xml.ReadState.EndOfFile
sXML &= oReader.ReadOuterXml()
Loop
oReader.Close()
oDom.LoadXml(sXML)
Dim schema As Xml.XmlNode = oDom.ChildNodes(0).FirstChild
Dim mynode As Xml.XmlNode = oDom2.ImportNode(schema, True)
oDom2.AppendChild(mynode)
‘write schema only
sOutputfilename = “C:\” & sTablename & “_” & String.Format(“{0:” & “yyyyMMddHHmmss” & “}”, Now)
oDom2.Save(sOutputfilename & “.xsd”)
‘write xml with inline schema
oDom.Save(sOutputfilename & “.xml”)
iReturnvalue = 0
Catch SQLex As Exception
#If DEBUG Then
EmitDebugMessage(“spLBgenerateInstallscript exeption SQLex: ” & SQLex.Message)
#End If
iReturnvalue = 1
Finally
If oConnection.State <> ConnectionState.Closed Then
oConnection.Close()
End If
End Try
End Using
End Sub
..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..
”’ <summary>
”’ XmlReader Validation Handler
”’ </summary>
”’ <param name=”sender”></param>
”’ <param name=”e”></param>
”’ <remarks>not used, not ready</remarks>
Sub oXmlReaderSettingsValidationEventHandler(ByVal sender As Object, ByVal e As ValidationEventArgs)
If e.Severity = XmlSeverityType.Warning Then
Console.Write(“WARNING: “)
Console.WriteLine(e.Message)
ElseIf e.Severity = XmlSeverityType.Error Then
Console.Write(“ERROR: “)
Console.WriteLine(e.Message)
End If
End Sub
..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..
Herbee 