My first .NET Podcast
Subscribe
To Download: http://aspnetpodcast.com/CS11/blogs/asp.net_podcast/archive/2005/11/08/first-podcast.aspx
Show Notes:
I've got an interview with Paul Wilson and then me doing some technical discussion of writing Sql Server CLR Objects.
Special thanks to Jason Salas: http://weblogs.asp.net/jasonsalas
Paul Wilson's Blog: http://weblogs.asp.net/pwilson
Atlanta Code Camp: http://www.atlantacodecamp.com
Debugging Example: http://weblogs.asp.net/wallym/archive/2004/12/08/278081.aspx
Trigger:
<SqlTrigger(Name:="tr_tblSearchResults_InsertUpdate", Event:="FOR INSERT, UPDATE", Target:="tblSearchResults")> _
Public Shared Sub InsertUpdateSearchResultsTrigger()
Dim i As Integer
Dim iSearchCode As Long
Dim strServerName As String
Dim strSql As String = String.Empty
Dim glResults As New Generic.List(Of String)
Dim sqlCn As New SqlConnection("Context Connection=true")
Dim sqlCmd As New SqlCommand
Dim sqlTrCtx As SqlTriggerContext = SqlContext.TriggerContext
Dim dsData As New DataSet()
Dim dtData As New DataTable
Dim sqlDr As SqlDataReader
Try
strSql = "select SearchUrl, tblSearchResultsId from inserted"
sqlCmd.Connection = sqlCn
sqlCn.Open()
sqlCmd.CommandText = strSql
sqlCmd.CommandType = CommandType.Text
sqlDr = sqlCmd.ExecuteReader()
While sqlDr.Read()
iSearchCode = CalculateSearchCode(Convert.ToString(sqlDr("SearchUrl")))
strServerName = CalculateServerName(Convert.ToString(sqlDr("SearchUrl")))
strSql = "update tblSearchResults set SearchCode=" & iSearchCode.ToString() & ", ServerName='" & _
strServerName.Replace("'", "''") & "', DateUpdated=getdate() where tblSearchResultsId=" & _
Convert.ToString(sqlDr("tblSearchResultsId"))
If sqlTrCtx.TriggerAction = TriggerAction.Insert Then
strSql = strSql & ";update tblSearchResults set DateEntered=getdate() where tblSearchresultsId=" & _
Convert.ToString(sqlDr("tblSearchResultsId"))
End If
glResults.Add(strSql)
End While
If Not (sqlDr.IsClosed) Then
sqlDr.Close()
End If
For i = 0 To (glResults.Count - 1)
sqlCmd.CommandText = glResults.Item(i)
sqlCmd.ExecuteNonQuery()
Next
sqlDr.Dispose()
Catch sqlExc As SqlException
Dim sqlPipe As SqlPipe = SqlContext.Pipe
sqlPipe.Send("Error: " & sqlExc.Message.ToString())
sqlPipe = Nothing
Throw
Finally
If sqlCn.State <> ConnectionState.Closed Then
sqlCn.Close()
End If
sqlCmd.Dispose()
End Try
End Sub
Stored Procedure #1:
Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<SqlProcedure(Name:="spFunctionCount")> _
Public Shared Sub spFunctionCount()
' Add your code here
Dim iReturn As Integer = 0
Dim strSql As String
Dim sqlCn as New SqlConnection("Context Connection=true")
Dim sqlCm As New SqlCommand
Dim sqlP As SqlPipe = SqlContext.Pipe
Try
strSql = "select count(*) as RecordCount from tblSearchResults"
sqlCn.Open()
sqlCm.Connection = sqlCn
sqlCm.CommandText = strSql
sqlCm.CommandType = CommandType.Text
sqlP.ExecuteAndSend(sqlCm)
Finally
If sqlCn.State <> ConnectionState.Closed Then
sqlCn.Close()
End If
sqlCn.Dispose()
sqlCm.Dispose()
sqlCm = Nothing
sqlP = Nothing
End Try
End Sub
End Class
Stored Procedure #2:
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<SqlProcedure(Name:="spSimple")> _
Public Shared Sub spSimple(ByVal pdtClientDate As DateTime)
' Add your code here
Dim iReturn As Integer = 0
Dim strSql As String
Dim sqlCn As New SqlConnection("Context Connection=true")
Dim sqlCm As New SqlCommand
Dim sqlP As SqlPipe = SqlContext.Pipe
Try
strSql = "insert into tblAsyncExecuteNonQuery (ClientDateEntered) values ('" & pdtClientDate.ToString() & "');select scope_identity()"
sqlCn.Open()
sqlCm.Connection = sqlCn
sqlCm.CommandText = strSql
sqlCm.CommandType = CommandType.Text
'sqlCm.ExecuteNonQuery()
sqlP.ExecuteAndSend(sqlCm)
Finally
If sqlCn.State <> ConnectionState.Closed Then
sqlCn.Close()
End If
sqlCn.Dispose()
sqlCm.Dispose()
sqlCm = Nothing
sqlCn = Nothing
End Try
End Sub
End Class
Aggregate:
Imports System
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server
Namespace SampleCode
<Serializable()> _
<StructLayout(LayoutKind.Sequential)> _
<SqlUserDefinedAggregate(Format.Native, IsInvariantToDuplicates:=True, IsInvariantToNulls:=True, isinvariantToOrder:=True, IsNullIfEmpty:=True, Name:="SecondMax")> _
Public Structure aggExample
Private gDblHigh As Double
Private gDblNextHigh As Double
Public Sub Init()
' Put your code here
gDblHigh = Nothing
gDblNextHigh = Nothing
End Sub
Public Sub Accumulate(ByVal value As Double)
' Put your code here
If gDblHigh = Nothing Then
gDblHigh = value
ElseIf gDblNextHigh = Nothing Then
If value > gDblHigh Then
gDblNextHigh = gDblHigh
gDblHigh = value
Else
gDblNextHigh = value
End If
ElseIf (Not (IsNothing(gDblHigh)) And Not (IsNothing(gDblNextHigh))) Then
If value > gDblHigh Then
gDblNextHigh = gDblHigh
gDblHigh = value
ElseIf (value > gDblNextHigh) And (gDblHigh <= value) Then
gDblNextHigh = value
End If
End If
End Sub
Public Sub Merge(ByVal value As aggExample)
' Put your code here
If Not (IsNothing(value.GetHighValue)) Then
Me.Accumulate(Convert.ToDouble(value.GetHighValue))
End If
If Not (IsNothing(value.GetNextValue)) Then
Me.Accumulate(Convert.ToDouble(value.GetNextValue))
End If
End Sub
Public Function Terminate() As SqlDouble
' Put your code here
Return New SqlDouble(gDblNextHigh)
End Function
Public Function GetHighValue() As SqlDouble
Return (New SqlDouble(gDblHigh))
End Function
Public Function GetNextValue() As SqlDouble
Return (New SqlDouble(gDblNextHigh))
End Function
End Structure
End Namespace
Image of the aggregate results.
