Thursday, 30 September 2010

Bite Sized Web Server

A few months back I needed a way of pulling information off of a machine that wasn't capable of supporting IIS. I was going to go down the route of some bespoke client server protocol, but then I thought that since it's only basic information and files that I need to send I'd create my own small web server. After doing some research on the subject and having messed with TCP components in a previous project on windows mobile I had a pretty good foundation to start moving.

First off we need a TCP Listener, this helpful object sits there and tells us when someone is connecting on a specified port, the actual transmitting of data is handled via another object known as the socket but we'll get to that shortly

Public Class WebServer
    Dim mthdServer As Thread
    Dim mwebSession As WebSession
    Dim mtcpListener As TcpListener

    Public Sub New()
        Try
            ' Get the IP address of the machine
            Dim strHostName As String = Dns.GetHostName()
            Dim ipServer As IPAddress = Dns.GetHostEntry(strHostName).AddressList(0)
            ' Specify which Port we'll be using.
            Dim strPort As String = "81"
            ' Open a tcp listener on the port we've specified
            mtcpListener = New TcpListener(ipServer, Int32.Parse(strPort))
            ' Start it up
            mtcpListener.Start()
            ' Some internal info, you could also log this to a file or the event log.
            Console.WriteLine("Web server started at: " & ipServer.ToString() & ":" & strPort)
            ' Start up the worker thread and pass it our listening item
            mwebSession = New WebSession(mtcpListener)
            mthdServer = New Thread(New ThreadStart(AddressOf mwebSession.ProcessThread))
            mthdServer.Start()
        Catch ex As Exception
            ' Bomb
            Console.WriteLine(ex.StackTrace.ToString())
        End Try
    End Sub
    ' Clean up
    Protected Overrides Sub Finalize()
        MyBase.Finalize()
        ' clean up the thread
        mthdServer.Abort() : mthdServer = Nothing
        mwebSession = Nothing
        ' Stop listening on the port
        mtcpListener.Stop() : mtcpListener = Nothing
    End Sub
End Class

You'll notice above that we have a second class known as the webServer this is the core of the service and handles the transmission of the file data and is basically the work horse.

Public Class WebSession
    ' Internal store for the listener and also the socket for data movement
    Private mtcpListener As System.Net.Sockets.TcpListener
    Private msktClient As System.Net.Sockets.Socket

    ' We're passed the listener so store that away.
    Public Sub New(ByVal tcpListener As System.Net.Sockets.TcpListener)
        Me.mtcpListener = tcpListener
    End Sub

    ' Since we're a thread this is a starting point, we're just gonna sit in a loop
    ' get a request for a file and then return it
    Public Sub ProcessThread()
        While (True)
            Try
                ' Wait for a connection from the outside
                msktClient = mtcpListener.AcceptSocket()
                ' Get information on the client using the IPEndPoint
                Dim ipepClientInfo As IPEndPoint = CType(msktClient.RemoteEndPoint, IPEndPoint)
                ' Start a new thread for the request then the loop can go to process additional
                ' requests
                Dim mthdClient As New Thread(New ThreadStart(AddressOf ProcessRequest))
                mthdClient.Start()

                ' Fault? oh dear, let's bomb the current thread, the client will just
                ' get a connection fault and they'll have to try again.
            Catch ex As Exception
                Console.WriteLine(ex.StackTrace.ToString())
                If msktClient.Connected Then
                    msktClient.Close()
                End If
            End Try
        End While
    End Sub

    ' This is where we figure what file the client wants and then throw it back via a stream
    Protected Sub ProcessRequest()
        Dim recvBytes(1024) As Byte
        Dim strHTMLReq As String = Nothing
        Dim intBytes As Int32

        Try
            ' Receive the request from the client, we'll need some buffer space to shove
            ' it into
            intBytes = msktClient.Receive(recvBytes, 0, msktClient.Available, SocketFlags.None)
            ' Transfer the buffer to a readable format
            strHTMLReq = Encoding.ASCII.GetString(recvBytes, 0, intBytes)

            ' Could log this but I'm just using it to display what the request is
            Console.WriteLine("HTTP Request: ")
            Console.WriteLine(strHTMLReq)

            ' Get the path of to where our files are stored and the default page name
            Dim strDefaultPage As String = "index.html"
            Dim strPath As String = Directory.GetCurrentDirectory() & "\WWWRoot\"
            ' Some buffers we'll be using
            Dim strArray() As String
            Dim strRequest As String

            ' Chop up the request so it's eaier to Interpret
            strArray = strHTMLReq.Trim.Split(" ")
            ' Determine the HTTP method, we only use GET, in here we use our
            ' HTMLResponse function which is where the data is streamed
            If strArray(0).Trim().ToUpper.Equals("GET") Then
                strRequest = strArray(1).Trim
                ' Do we have a file name?
                If (strRequest.StartsWith("/")) Then
                    strRequest = strRequest.Substring(1)
                End If
                ' No file Name then default page.
                If (strRequest.EndsWith("/") Or strRequest.Equals("")) Then
                    strRequest = strRequest & strDefaultPage
                End If
                ' Send the data
                strRequest = strPath & strRequest
                HTMLResponse(strRequest)
            Else ' Not GET method, we'll go away passing back the errorpage.
                strRequest = strPath & "Error\" & "400.html"
                HTMLResponse(strRequest)
            End If

            ' Fault? Just kill the connection
        Catch ex As Exception
            Console.WriteLine(ex.StackTrace.ToString())
            If msktClient.Connected Then
                msktClient.Close()
            End If
        End Try
    End Sub

    ' This procedure will send back the file specified in a stream back to the client.
    Private Sub HTMLResponse(ByVal httpRequest As String)
        Try
            ' Open the file we've been given, we'll use the streamreader to pull everything
            ' into a string for easy transport.
            Dim strData As StreamReader = New StreamReader(httpRequest)
            Dim strBuff As String = strData.ReadToEnd()
            strData.Close() : strData = Nothing

            ' Push the string into a byte array
            Dim respByte() As Byte = Encoding.ASCII.GetBytes(strBuff)

            ' Set HTML Header, this is taken from the W3S consortium site, only thing
            ' you need to take note of is the GetContentType function used to pass
            ' back what type of document we're returning.
            Dim htmlHeader As String = _
                "HTTP/1.0 200 OK" & ControlChars.CrLf & _
                "Server: Stews Webserver 1.0" & ControlChars.CrLf & _
                "Content-Length: " & respByte.Length & ControlChars.CrLf & _
                "Content-Type: " & getContentType(httpRequest) & _
                ControlChars.CrLf & ControlChars.CrLf

            ' Again encode the header to bytes.
            Dim headerByte() As Byte = Encoding.ASCII.GetBytes(htmlHeader)

            ' Send HTML Header back to Web Browser via our socket
            msktClient.Send(headerByte, 0, headerByte.Length, SocketFlags.None)
            ' Send Contents back to Web Browser via our socet
            msktClient.Send(respByte, 0, respByte.Length, SocketFlags.None)
            ' Close HTTP Socket connection
            msktClient.Shutdown(SocketShutdown.Both)
            msktClient.Close()

            ' Error? Bomb
        Catch ex As Exception
            Debug.Print(ex.Message)

            If msktClient.Connected Then
                msktClient.Close()
            End If
        End Try
    End Sub

    ' Determines the type of returned content.
    Private Function GetContentType(ByVal httpRequest As String) As String
        If (httpRequest.EndsWith("html")) Then
            Return "text/html"
        ElseIf (httpRequest.EndsWith("htm")) Then
            Return "text/html"
        ElseIf (httpRequest.EndsWith("txt")) Then
            Return "text/plain"
        ElseIf (httpRequest.EndsWith("xml")) Then
            Return "text/xml"
        ElseIf (httpRequest.EndsWith("gif")) Then
            Return "image/gif"
        ElseIf (httpRequest.EndsWith("jpg")) Then
            Return "image/jpeg"
        ElseIf (httpRequest.EndsWith("jpeg")) Then
            Return "image/jpeg"
        ElseIf (httpRequest.EndsWith("pdf")) Then
            Return "application/pdf"
        ElseIf (httpRequest.EndsWith("pdf")) Then
            Return "application/pdf"
        ElseIf (httpRequest.EndsWith("doc")) Then
            Return "application/msword"
        ElseIf (httpRequest.EndsWith("xls")) Then
            Return "application/vnd.ms-excel"
        ElseIf (httpRequest.EndsWith("ppt")) Then
            Return "application/vnd.ms-powerpoint"
        Else
            Return "text/plain"
        End If
    End Function
End Class

And that's about it, as you can see it's all a lot more simpler than it used to be in C++ but that's the power of .NET for you. You can always make changes to the HTMLResponse proc if you want to implement your own type of data response.

And just before I go some additional information, we use the following imports
Imports System.Net
Imports System.Net.Sockets
Imports System.Threading
Imports System.Text
Imports System.IO

and to run the webserver tis easy.
Dim myWebServer As WebServer = New WebServer

Tuesday, 21 September 2010

Common Table Expressions

Producing Dates – Sometimes a list of dates is required for you to cross join against, I used to do this via a large table filled with dates which used to work well. Since the introduction of SQL Server 2005 however I now use a common table expression (CTE) to create a list of dates, the advantage here is speed (no table needs to be scanned) as well as storage space (no data is stored down). The following code will generate a list of dates between 01/01/2010 and 31/12/2010

WITH myDates AS
(
 SELECT CAST('01/Jan/2010' AS DATETIME) DateValue
 UNION ALL
 SELECT DateValue + 1
 FROM myDates
 WHERE DateValue + 1 <= N'31/Dec/2010'
)
-- Immediately use the above CTE table, the table will no longer exist after -- it’s first use
SELECT DateValue FROM myDates OPTION (MAXRECURSION 0)
Please note the OPTION (MAXRECURSION 0) statement, when using CTEs the default maximum number of recursions is 100, here we set it to 0 to stipulate that the loop must continue until it naturally completes. The key principle of the CTE is its ability to self reference allowing you to perform tasks on the data that is already present in the table without complex join scenarios. Also the ability to use recursion as shown above allows you to programmatically create large tables without the use of stored procedures. The only downside to CTEs appears to be the fact that the table itself only exists for the first statement use outside of the WITH statement. Anywho back to our statement, you’ll probably want this as part of another SQL Statement let’s say a list of dates in September and a list of sales against those dates? First of let’s create a function that will allow us to get the dates we need and using the code above.
CREATE FUNCTION DateList
( 
   @StartDate AS DATETIME, @EndDate as DATETIME
)
RETURNS @tabDates TABLE
(
   DateValue DATETIME
)
AS
BEGIN
   WITH myDates AS
   (
      SELECT @StartDate DateValue
      UNION ALL
         SELECT DateValue + 1
            FROM myDates
            WHERE DateValue + 1 <= @EndDate
   )
   INSERT INTO @tabDates SELECT DateValue FROM myDates OPTION (MAXRECURSION 0)
   RETURN
END
Now with the above function we can link this to anytable and retrieve the results so we’ll get a full list of dates for September and then link the total sales per date
DECLARE @Sales TABLE (ID INTEGER, [Date] DATETIME)
INSERT INTO @Sales VALUES (1, N'02/Sep/2010')
INSERT INTO @Sales VALUES (2, N'12/Sep/2010')
INSERT INTO @Sales VALUES (3, N'19/Sep/2010')
INSERT INTO @Sales VALUES (4, N'02/Sep/2010')
INSERT INTO @Sales VALUES (5, N'04/Sep/2010')
INSERT INTO @Sales VALUES (6, N'01/Sep/2010')
INSERT INTO @Sales VALUES (7, N'02/Sep/2010')



SELECT d.DateValue, COUNT(s.ID) 
FROM DateList(N'01/Sep/2010', N'30/Sep/2010') d
LEFT OUTER JOIN @Sales AS s ON d.DateValue = s.[Date]
GROUP BY d.DateValue

This is just a brief overview of CTEs and i'll create a more in depth example later on.

Thursday, 16 September 2010

Replication monitoring

Got fed up of looking at the replication monitor day after day so created this simple stored proc to do it for me.

CREATE PROCEDURE dba_CheckSubscriptions
AS BEGIN
SELECT [Subscriber_server] + '/' + [db_name] AS [Server], [last_sync_date] AS [SyncTime], CASE WHEN [last_sync_date] > DATEADD(minute,-10,GETDATE()) THEN 'True' ELSE 'False' END AS [IsSync]
      FROM dbo.sysmergesubscriptions
      WHERE [last_sync_date] IS NOT NULL
END

This routine will populate a list of subscriptions attached to a merge replication (if you're using transactional or snapshot replication there are two different tables, not too sure which but just look under system tables)

You can then set up a job to email you if the InSync column comes up as false... Of course I don't use SQLMail as it poses security issues so I have instead went for the .NET way of notifying me if there's an issue, below is the code I used in a console app, notice I'm also using the DBConnection and DBRecordset classes that you can look at here.

Sub Main()
        Dim strData As String = ""
        Dim blnContinue As Boolean = True
        Dim strServer As String = ""
        Dim strDatabase As String = ""
        Dim strEmailServer As String = ""
        Dim strEmailAddress As String = ""
        Dim blnFailed As Boolean = False
        Dim myConnection As DBConnection = New DBConnection

        ' Check the arguments given are valid, we need the following
        ' %1 - Name of the database server
        ' %2 - Name of the Database
        ' %3 - SMTP Server to connect to
        ' %4 - Email address to send to
        If (blnContinue) Then
            Dim strCommands As String() = Command.Split(",")
            If (strCommands.Length <> 4) Then
                Console.WriteLine("Parameters Incorrect")
                Console.WriteLine("Usage ReplMon [Database Server],[Server Instance],[Email Server],[Email Address]")
                Console.WriteLine("   e.g. ReplMon 'BACKUP\BackupLogs', 'Logs', 'TAG', 'dba@mymail.co.uk'")
                blnContinue = False
            Else
                ' Get the details into our vars, notice I strip any quotes
                strServer = strCommands(0).Replace("""", "").Replace("'", "")
                strDatabase = strCommands(1).Replace("""", "").Replace("'", "")
                strEmailServer = strCommands(2).Replace("""", "").Replace("'", "")
                strEmailAddress = strCommands(3).Replace("""", "").Replace("'", "")
            End If
        End If
        ' Connect to the database with the details given
        If (blnContinue) Then
            myConnection.DatabaseName = strDatabase
            myConnection.Server = strServer
            blnContinue = myConnection.ConnectDatabase()
            If (Not blnContinue) Then
                Call Console.WriteLine("Unable to connect to the database " + strServer + "\" + strDatabase)
            End If
        End If
        ' Get the data from the stored proc and produce the email
        If (blnContinue) Then
            Dim recSet As DBRecordSet = New DBRecordSet(myConnection)
            recSet.OpenRecordSet("EXEC dba_CheckSubscriptions")
            If (recSet.EOF) Then
                Console.WriteLine("No records retrieved from database, replication maybe faulty")
                blnContinue = False
            Else
                ' Put the information from the database into a text object to send in the email
                strData = "Location            Last Sync Time      In Sync?" + vbCrLf
                Do Until (recSet.EOF)
                    strData += recSet.Fields("Computer").ToString.PadRight(20, " ")
                    strData += recSet.Fields("SyncTime").ToString.PadRight(20, " ")
                    strData += recSet.Fields("InSync").ToString + vbCrLf
                    ' If we have a sync issue, mark the failed flag we'll use this to change the header on the email
                    If (recSet.Fields("InSync") <> "True") Then
                        blnFailed = True
                    End If
                    ' Move to the next record
                    recSet.MoveNext()
                Loop
            End If
            ' CLose up the recordSet
            recSet.Close() : recSet = Nothing
        End If
        ' Close the Database COnnection
        myConnection = Nothing

        ' Send the mail
        If (blnContinue) Then
            Dim mailMsg As Net.Mail.MailMessage = New Net.Mail.MailMessage
            mailMsg.From = New Net.Mail.MailAddress(strEmailAddress)
            mailMsg.To.Add(New Net.Mail.MailAddress(strEmailAddress))
            mailMsg.Subject = IIf(blnFailed, "DATABASE REPLICATION SYNC FAILED", "Database Replication is in sync")
            mailMsg.Body = strData
            mailMsg.IsBodyHtml = False
            ' Set priority if we have a fail
            mailMsg.Priority = IIf(blnFailed, Net.Mail.MailPriority.High, Net.Mail.MailPriority.Normal)
            Dim smtpClient As New Net.Mail.SmtpClient
            smtpClient.Host = strEmailServer
            smtpClient.Send(mailMsg)
            mailMsg = Nothing
        End If
    End Sub

Notice no error logging??? so did I, I know my email and SQL server work so I don't care, you may want to put some in.

With the app compiled I then just fill in the arguments as described and then set it up as a scheduled task to run every four hours. I then get and email informing me of the up to date status of the database replication... Simples.

Tuesday, 14 September 2010

Connecting to an SQL Server Database

Coming from working with ADO and DAO, I've found it hard adapting to the naff way in which .net handles recordset, to deal with these shortcomings I made these two classes which I use in nearly all my projects.. Enjoy.

DBConnection - Used to connect to an SQL Server, allows you to open recordsets and transactions as well as executing SQLs with a single instruction.
Imports Microsoft.SqlServer

Public Class DBConnection
    Private mstrDatabaseName As String
    Private mstrServer As String
    Private mstrUserName As String
    Private mstrPassword As String
    Private mTransaction As SqlClient.SqlTransaction = Nothing
    Friend SQLDatabase As SqlClient.SqlConnection
    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()

    End Sub
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Databse Name
    Public Property DatabaseName() As String
        Get
            Return mstrDatabaseName
        End Get
        Set(ByVal value As String)
            mstrDatabaseName = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Server
    Public Property Server() As String
        Get
            Return mstrServer
        End Get
        Set(ByVal value As String)
            mstrServer = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' UserName
    Public Property Username() As String
        Get
            Return mstrUserName
        End Get
        Set(ByVal value As String)
            mstrUserName = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Password
    Public Property Password() As String
        Get
            Return mstrPassword
        End Get
        Set(ByVal value As String)
            mstrPassword = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Set All 4 properties
    Public Sub SetDatabase(ByVal strServer As String, ByVal strDatabase As String, ByVal strUsername As String, ByVal strPassword As String)
        mstrServer = strServer
        mstrDatabaseName = strDatabase
        mstrPassword = strPassword
        mstrUserName = strUsername
    End Sub
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Connects to the database that we've been given the details for
    Public Function ConnectDatabase() As Boolean
        Dim strConnectionString As String
        Dim dbTest As SqlClient.SqlConnection

        '  Create a connection string.
        strConnectionString = "Data Source=" + mstrServer + "; Initial Catalog=" + mstrDatabaseName + ";"
        If (mstrUserName <> "" And mstrPassword <> "") Then
            strConnectionString = strConnectionString + "User Id=" + mstrUserName + ";"
            strConnectionString = strConnectionString + "Password=" + mstrPassword + ";"
        Else
            strConnectionString = strConnectionString + "Trusted_Connection=Yes;"
        End If
        ' Connect to the database
        dbTest = New SqlClient.SqlConnection(strConnectionString)
        dbTest.Open()
        ' If we've opened then store the connection otherwise, exit
        If (dbTest.State = ConnectionState.Open) Then
            If (Not SQLDatabase Is Nothing) Then
                SQLDatabase.Close()
            End If
            SQLDatabase = Nothing
            SQLDatabase = dbTest
            dbTest = Nothing
            Return True
        Else
            dbTest = Nothing
            Return False
        End If
    End Function
    ' /////////////////////////////////////////////////////////////////////
    ' Begins a tranasction
    Public Sub BeginTransaction()
        If (mTransaction Is Nothing) Then
            mTransaction = SQLDatabase.BeginTransaction()
        Else
            Throw New Exception("Transaction is already open!")
        End If
    End Sub
    Public Sub CommitTransaction()
        If (mTransaction Is Nothing) Then
            Throw New Exception("There is no transaction open!")
        Else
            mTransaction.Commit()
            mTransaction = Nothing
        End If
    End Sub
    Public Sub RollbackTransaction()
        If (mTransaction Is Nothing) Then
            Throw New Exception("There is no transaction open!")
        Else
            mTransaction.Rollback()
            mTransaction = Nothing
        End If
    End Sub
    ' /////////////////////////////////////////////////////////////////////
    ' Open recordSet Opens a recordSet Object and then does stuff with it.
    Public Function OpenRecordSet(ByVal strSQL As String) As DBRecordSet
        Dim retData As DBRecordSet = New DBRecordSet

        retData.Database = Me
        retData.OpenRecordSet(strSQL)
        Return retData
    End Function
    ' /////////////////////////////////////////////////////////////////////
    ' Executes an SQL that Needs Executing
    Public Function Execute(ByVal strSQL As String, Optional ByRef RecordsAffected As Long = 0) As Boolean
        Dim cmdCommand As SqlClient.SqlCommand

        cmdCommand = New SqlClient.SqlCommand(strSQL, SQLDatabase, mTransaction)
        RecordsAffected = cmdCommand.ExecuteNonQuery()
        Execute = True
        ' Clean and Exit
        cmdCommand = Nothing
    End Function
    ' /////////////////////////////////////////////////////////////////////
    ' Clean up and Exit
    Protected Overrides Sub Finalize()
        If (Not mTransaction Is Nothing) Then
            mTransaction.Rollback()
            mTransaction = Nothing
        End If
        SQLDatabase = Nothing
        MyBase.Finalize()
    End Sub
End Class

DBRecordSet - Used to manipulate a recordset handed back by DBConnection, similar to the ADO recordset object it will allow you to move forward and backward through the records as well as giving you a way to determine BOF & EOF.

Public Class DBRecordSet
    Private mdbConn As DBConnection
    Private mdbData As DataTable
    Private mlngCounter As Long
    '///////////////////////////////////////////////////////////////////////////////
    ' Pass the database through
    Default Public ReadOnly Property Fields(ByVal FieldName As String) As Object
        Get
            If (mdbData Is Nothing) Then
                Return Nothing
            Else
                If (mlngCounter >= 0) Then
                    Return mdbData.Rows(mlngCounter).Item(FieldName)
                Else
                    Return Nothing
                End If
            End If
        End Get
    End Property
    Default Public ReadOnly Property Fields(ByVal FieldIndex As Integer) As Object
        Get
            If (mdbData Is Nothing) Then
                Return Nothing
            Else
                If (mlngCounter >= 0) Then
                    Return mdbData.Rows(mlngCounter).Item(FieldIndex)
                Else
                    Return Nothing
                End If
            End If
        End Get
    End Property
    Public Property Database() As DBConnection
        Get
            Return mdbConn
        End Get
        Set(ByVal value As DBConnection)
            mdbConn = Nothing
            mdbConn = value
        End Set
    End Property
    '///////////////////////////////////////////////////////////////////////////////
    Public Sub New()
    End Sub
    Public Sub New(ByRef Connection As DBConnection)
        Me.Database = Connection
    End Sub
    Friend Function OpenDataSetFromSP(ByVal StoredProcName As String) As DataSet
        Dim varReturn As Long
        Return OpenDataSetFromSP(StoredProcName, varReturn, Nothing)
    End Function
    '///////////////////////////////////////////////////////////////////////////////
    ' Open the dataset
    Friend Function OpenDataSetFromSP(ByVal StoredProcName As String, ByRef ReturnValue As Object, ByVal ParamArray Args() As Object) As DataSet
        Dim dbData As SqlClient.SqlDataAdapter
        Dim dbCommand As SqlClient.SqlCommand
        Dim parameter As SqlClient.SqlParameter
        Dim lngCounter As Long
        Dim ds As DataSet

        OpenDataSetFromSP = Nothing
        Try
            dbCommand = New SqlClient.SqlCommand(StoredProcName, Me.Database.SQLDatabase)
            dbCommand.CommandType = CommandType.StoredProcedure
            LoadParams(dbCommand)
            lngCounter = 0
            If (dbCommand.Parameters.Count > 0) Then
                For Each parameter In dbCommand.Parameters
                    If (Not parameter.Direction = ParameterDirection.ReturnValue) Then
                        If (Args.Length >= lngCounter) Then
                            parameter.Value = Args(lngCounter)
                        End If
                        lngCounter = lngCounter + 1
                    End If
                Next
            End If
            ' Execute what we need
            dbData = New SqlClient.SqlDataAdapter(dbCommand)
            ds = New DataSet
            ' Shove indataSet
            dbData.Fill(ds)
            OpenDataSetFromSP = ds
            ' Retrieve the Result?
            For Each parameter In dbCommand.Parameters
                If (parameter.Direction = ParameterDirection.ReturnValue) Then
                    ReturnValue = parameter.Value
                End If
            Next
            ' Exit

        Catch ex As Exception

            'DB.GenerateError("DbRecordSet::OpenDataSetFromSP : " + StoredProcName, ex)
            OpenDataSetFromSP = Nothing
        End Try
        ' Clean up
        dbCommand = Nothing
        dbData = Nothing
    End Function
    ' //////////////////////////////////////////////////////////////////////////////////////////////
    ' Caches and loads cached params for a stored procedure.... saves on the call backs
    Private Sub LoadParams(ByRef dbCommand As SqlClient.SqlCommand)
        Dim strProcName As String = dbCommand.CommandText
        Dim dbparameter As Collection = Nothing
        Dim parameter As SqlClient.SqlParameter = Nothing
        Static dicParams As Collection
        If (dicParams Is Nothing) Then
            dicParams = New Collection
        End If

        ' Find this itme in the list
        If (dicParams.Contains(strProcName)) Then
            dbparameter = dicParams(strProcName)
            If (Not dbparameter Is Nothing) Then
                For Each parameter In dbparameter
                    dbCommand.Parameters.Add(CType(parameter, ICloneable).Clone)
                Next
            End If
        Else
            SqlClient.SqlCommandBuilder.DeriveParameters(dbCommand)
            ' now record the params
            For Each parameter In dbCommand.Parameters
                If (dbparameter Is Nothing) Then
                    dbparameter = New Collection
                End If
                dbparameter.Add(CType(parameter, ICloneable).Clone)
            Next
            dicParams.Add(dbparameter, strProcName)
        End If
    End Sub
    '///////////////////////////////////////////////////////////////////////////////
    ' The Dataset object, just return tis all.
    Friend Function OpenDataSet(ByVal strSQL As String) As DataSet
        Dim datData As DataSet = Nothing
        Dim objCommand As SqlClient.SqlCommand
        Dim objAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()

        Try
            objCommand = New SqlClient.SqlCommand(strSQL, mdbConn.SQLDatabase)
            objAdapter.SelectCommand = objCommand
            datData = New DataSet
            objAdapter.Fill(datData)
            objAdapter.Dispose()
            objCommand.Dispose()
        Catch ex As Exception

        End Try

        objAdapter = Nothing
        objCommand = Nothing
        Return datData
    End Function
    '///////////////////////////////////////////////////////////////////////////////
    ' Opens the recordSByVal strSQL As String) As Boolean
    Friend Function OpenRecordSet(ByVal strSQL As String) As Boolean
        Dim objAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()
        Dim objCommand As SqlClient.SqlCommand

        OpenRecordSet = False
        Try
            mlngCounter = -1
            mdbData = Nothing
            objCommand = New SqlClient.SqlCommand(strSQL, mdbConn.SQLDatabase)
            objAdapter.SelectCommand = objCommand
            mdbData = New DataTable
            objAdapter.Fill(mdbData)
            objAdapter.Dispose()
            objCommand.Dispose()
            If (mdbData.Rows.Count > 0) Then
                mlngCounter = 0
            End If
            OpenRecordSet = True
        Catch ex As Exception
            Throw
        End Try
        objCommand = Nothing
        objAdapter = Nothing
    End Function
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' MoveNext, MovePrevious, MoveFirst and MoveLast, self explanatory
    Public Function MoveLast() As Long
        If (mdbData Is Nothing) Then
            Return 0
        Else
            mlngCounter = mdbData.Rows.Count - 1
            Return mlngCounter
        End If
    End Function
    Public Sub MoveFirst()
        mlngCounter = 0
    End Sub
    Public Function MoveNext() As Long
        If (mdbData Is Nothing) Then
            Return 0
        Else
            mlngCounter = mlngCounter + 1
            If (mlngCounter > mdbData.Rows.Count) Then
                mlngCounter = mdbData.Rows.Count
            End If
            Return mlngCounter
        End If
    End Function
    Public Function MovePrevious() As Long
        mlngCounter = mlngCounter - 1
        If (mlngCounter < 0) Then
            mlngCounter = 0
        End If
        Return mlngCounter
    End Function
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' EOF & BOF, checks to see if we're at the end or begining of the recordSet
    Public Function EOF() As Boolean
        EOF = False
        If (Not mdbData Is Nothing) Then
            If (mlngCounter >= mdbData.Rows.Count Or mdbData.Rows.Count = 0) Then
                ' DO the move last thing incase we are over the top on our recordcount
                EOF = True
            End If
        End If
    End Function
    Public Function BOF() As Boolean
        BOF = False
        If (Not mdbData Is Nothing) Then
            If (mlngCounter <= 0 And mdbData.Rows.Count > 0) Then
                mlngCounter = 0
                BOF = True
            End If
        End If
    End Function
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' SImilar to the .close command in recordSets, just frees up the data.
    Public Sub Close()
        mdbData = Nothing
    End Sub
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' Clean up and Exit
    Protected Overrides Sub Finalize()
        mdbData = Nothing
        mdbConn = Nothing
        MyBase.Finalize()
    End Sub
End Class


That is basically that with these two classes you can go around and do what you want on the SQL server... for example

' Open a connection to the database
        Dim myConnection As DBConnection = New DBConnection
        myConnection.SetDatabase("MYSERVER\Instance", "DatabaseName", "", "")
        ' Execute an SQL Directly
        myConnection.Execute("UPDATE [Article] SET [Data] = [Data] + 1")

        ' Open a recordSet
        Dim myRecordSet As New DBRecordSet(myConnection)

        ' Execute Straight record return SQLs
        myRecordSet.OpenRecordSet("SELECT * FROM [Article]")
        If (Not myRecordSet.EOF) Then
            Dim lngData As Long = myRecordSet.Fields("Data")
        End If

        ' Return Datasets from SQLs
        Dim myData As DataSet = myRecordSet.OpenDataSet("SELECT * FROM [Article]")

        ' Exeute Stored Procedures
        Dim lngReturn As Integer
        Dim myData As DataSet = myRecordSet.OpenDataSetFromSP("dba_CheckData", lngReturn, 1, 2, 3, "Some Parameter", 2341)

' That's that

Tuesday, 7 September 2010

Using the "Instead Of" triggers

Found a nice little SQL instruction the other day called INSTEAD OF, this gem allows you to override the INSERT, UPDATE or DELETE methods on a view or table and implement your own code.... Lets say for example you have a table of employees and you want to make sure that when the employees are deleted from the database they aren't actually deleted only flagged so they don't show up?  You could move the items into another table using a DELETE trigger or you could write something up in the calling app to do it for you but for supreme ease of use the INSTEAD OF trigger comes up trumps... behold

CREATE TRIGGER myDelete ON dbo.Employees INSTEAD OF DELETE
AS
BEGIN
   -- The table [Deleted] will contain all the records that have been set for deletion although you can't alter
   -- these directly so we join them to the real table
    UPDATE dbo.[Employees] SET dbo.[Employees].[Deleted] = 1
       FROM dbo.[Employees] INNER JOIN [Deleted] ON dbo.[Employees].[ID]=[Deleted].[ID]
END

As usual you are given a table (in this case DELETED) which holds the records of the items that have been marked for deletion. You're not allowed to modify this table directly so you have to inner join back to the original table and do what you need to. As you can see from the example above we are merely setting a flag column called [Deleted] rather than actually deleting the record, something that would not be possible with the DELETE Trigger alone, all hail INSTEAD OF.