in

vbCity Blogs

New (temp) place for vbCity Blogs

Mike McIntyre's .NET Journal

December 2008 - Posts

  • Welcome 2009! Create a Calendar with Microsoft Transact SQL (How to iterate days in a series)

    One puzzle SQL developers face from time to time is creating a calendar containing a series of days.

    Below is some SQL code that will create an in-memory calendar for every day from a begin date to an end date.

    By adding more columns to the #Calendar table you could store more information about each day, store hours and pay rate to do pay calculations, or other data you need to capture and process in a series of days.

    The example creates a calendar for 2009.  Happy New Year!

    Example

    DROP TABLE #Calendar

    CREATE TABLE #Calendar

    (

    CalendarDate DATETIME

    )

     

    DECLARE @StartDate DATETIME

    DECLARE @EndDate DATETIME

    SET @StartDate = '1/1/2009'

    SET @EndDate = '12/31/2009'

     

    WHILE @StartDate <= @EndDate

          BEGIN

                 INSERT INTO #Calendar

                 (

                       CalendarDate

                 )

                 SELECT

                       @StartDate

     

                 SET @StartDate = DATEADD(dd, 1, @StartDate)

          END

     

    -- See the results

    SELECT * FROM #Calendar

     

     

  • Delimit Strings with the Visual Basic Join Function

    VIsual Basic contains some useful functions not available in the .NET class library.

    This post is about one of those functions, the Join(string array,string delimiter) function.

    The Join function concatenates an array of strings into a delimited string using a specified delimiter.

    Here is a method that includes the Join function:

        Private Function GetDelimitedString(ByVal strings As String(), ByVal delimiter As String) As String

            Return Join(strings, delimiter)

        End Function

    Here is an example that demonstrates using the method:

    Dim strings As String() = {"red", "green", "blue"}

    Console.WriteLine(GetDelimitedString(strings, ":"))

    Result:      red:green:blue

  • Visual Basic 2008 Pop Quiz - How can I add a Function to the String type? Part 3

    Extension methods are new in Visual Basic 2008.  This short blog series explores how extension methods can be used to add Subs and Functions to the .NET String type.

    Click here for an index of this blog series.

    Here's this post's sample code:

    Module StringExtensions

        ' Declare a String extension method named Browse

        '  that uses the string assigned to a variable of type String as a url

        '  to open a browser and navigate to the url.

        <Extension()> _

        Public Sub Browse(ByVal url As String)

            System.Diagnostics.Process.Start(url)

        End Sub

     

        ' Declare a String extension method named SplitCommaDelimited

        '  that splits a comma delimited string assigned to a variable of type String,

        '  splits it into a String array, then calls the String array's ToList method to

        '  return a List(Of String)

        <Extension()> _

        Public Function SplitCommaDelimited(ByVal commaDelimitedString As String) As List(Of String)

            Dim strings = commaDelimitedString.Split(",")

            Return strings.ToList

        End Function

     

        ' Delcare a String extension method name ValidateEmailAddress

        '  that uses a regular expression to validate a string is an email address.

        <Extension()> _

        Public Function ValidateEmailAddress(ByVal source As String) As Boolean

            Dim EmailRegex As Regex = New Regex("(?<user>[^@]+)@(?<host>.+)")

            Dim IsValid As Match = EmailRegex.Match(source)

            Return If(IsValid.Success, True, False)

        End Function

     

    End Module

     

    A new extension method, ValidateEmailAddress, has been added to the StringExtensions module created in a previous post.  The comments in the code explain what this new extension method does.

    Here is an example that tests the new method:

    Dim emailAddress = "mikemc@getdotnetcode.com"

    ' The following line will display True if the string contains and email address;

    '   False if it does not.

    MessageBox.Show(emailAddress.ValidateEmailAddress)

     

     

     

     

  • Get the Right Part of a String with Visual Basic's Right Function

    VIsual Basic contains some useful functions not available in the .NET class library.

    This post is about one of those functions, the Right(str,length) function.

    The Visual Basic 'Right' function is useful when you need to get 1 or more characters from the right side of a string. It returns a string containing a specified number of characters from the right side of a string.

    Syntax:  Right(str,length)

    Parameters

    str

    Required. String expression from which the rightmost characters are returned.

    length

    Required. Integer. Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in str, the entire string is returned.

    Example

    Dim TestString As String = "Hello World!"
    ' Returns "World!".

     

  • Calculate an Annunity Payment with Visual Basic 2008's Pmt Function

    I recently had a task to create code to calculate an annuity payment.

    Visual Basic has a builit in function - Pmt - for this purpose.

    Here's a method I created that calculates an annuity payment with Visua Basic's Pmt function:

    Public Function CalculateAnnuityPayment(ByVal rate As Double, _

            ByVal paymentPeriods As Double, _

            ByVal presentValue As Double, _

            Optional ByVal futureValue As Double = 0, _

            Optional ByVal paymentDueDate As DueDate = DueDate.EndOfPeriod)

     

            ' Call the Visual Basic Pmt function to calculate the annunity payment.

            Return Pmt(rate, paymentPeriods, presentValue, futureValue, paymentDueDate)

    End Function

    Mike McIntyre http://www.getdotnetcode.com

     

     

Copyright 1998-2009 vbCity.com LLC
Powered by Community Server (Non-Commercial Edition), by Telligent Systems