Should you ever find yourself needing to write CAML queries (XML queries) against a SharePoint list, the U2U CAML tool is quite helpful. U2U member Patrick Tisseghem has a nice blog post on how to use the tool which can be downloaded form their web site.

One thing to note is that the tool wraps the query XML nodes within a <Query></Query> node.  You’ll want to delete that node in your .NET code.  As a result, your CAML query might look something like this: 

Dim strQueryDate as String = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(CDate(“05/05/2006”))

Dim strQuery as String = “<Where><Geq><FieldRef Name=””Created”” /><Value Type=””DateTime””>{0}</Value></Geq></Where>”, strQueryDate)

Note the goofy CreateISO8601DateTimeFromSystemDateTime method that is used for the date… CAML uses an ISO8601 date format so you must use this method if you want to do date comparisons.

I recently took on the project of trying to create my own Business Logic Layer (BLL) and Data Access Layer (DAL) in a personal web project so I could understand how the various layers of n-tier architecture are supposed to work.  I didn’t get very far before I realized that allowing NULL database values (System.DBNull.Value) in various fields in my database was going to give me a large headache

After doing a lot of research, I didn’t come-up with too many satisfactory methods for dealing with DBNulls.  At best, it seemed like I’d have to write all sorts of methods for converting DBNulls to either a default value for each data type (I.e., define an integer to default to zero, a string defaults to String.Empty, a date defaults to DateTime.MinValue, etc.) or use the new VB.NET 2.0 Nullable Types and set each data type to Nothing.  This sounded like a lot of work and a lot of code… more code = more errors!

I read a few articles on using Generic Types in VB.NET 2.0 and it appeared to me as though they could be the answer to my prayers:  Create one generic method that can check for DBNulls for all data types.  A blog post by Saravana Kumar shows a way to accomplish this using C#. However, C# has a keyword default which doesn’t exist in VB.NET (at least not that I could find.)  This keyword apparently returns the default value for each data type, just like I wanted!

After much tinkering, here is what I came-up after trying to convert Saravana’s code to VB.NET:

Public Shared Function ConvertDBNull(Of T)(ByVal obj As Object) As T
    If (obj Is System.DBNull.Value) Then
        obj = Nothing
    End If
    Return CType(obj, T)
End Function

So far, it seems to work!  For example, with the following code:

Dim oSomeTestObject As Object = System.DBNull.Value
Dim strResult As String = ConvertDBNull(Of Integer)(oSomeTestObject)

The value of strResult is returned as a zero.  Another example:

Dim oSomeTestObject As Object = System.DBNull.Value
Dim strResult As String = ConvertDBNull(Of Date)(oSomeTestObject)

The value of strResult is returned as a “12:00:00 AM”. For most dates that I display, I always format them as MM/dd/yyyy, so a date value only exposing time won't show.

I tested this new method on my BLL, and so far it works great! w00t! If you have a better method for dealing with DBNulls in your Business Logic, let me know… I’m all ears!

The .NET 2.0 Framework supports a new string method for VB that should prove to be useful:

IsNullOrEmpty is a convenience method that enables you to simultaneously test whether a String is a null reference (Nothing in Visual Basic) or its value is Empty.

Who says that developers are boring and weird?  Well, they are weird, but sometimes they can be fun:

http://www.microsoft.com/korea/events/ready2005/vs_song.asp

I’m not sure if I think the video is funny because of the content or because of the Korean language that I don’t understand!

The lyrics are very good too when translated into English... they seem to relate to my job pretty well!

I’ve been told time and time again that Microsoft’s Visual Source Safe 6.0 is not a very good application to use for managing project files, but it’s all I have and it has been working pretty well… until today.   One of my project folders apparently got corrupt; I couldn’t open the folder, delete it, rename it… nuttin’.

After trying a gazillion stupid things just short of creating a whole new VSS database and starting over, I finally discovered the ANALYZE command line tool.  Sure enough, that’s all I needed to fix the corrupted database.  The analyze tool is located in the C:\Program Files\Microsoft Visual Studio\VSS\win32 folder.

Running the following command was all I had to do to fix the problematic folder:

C:\Program Files\Microsoft Visual Studio\VSS\win32>ANALYZE –F \\server\vss\dbname\data

Here is a list of a few other VSS Utilities. Since I now know how to repair a corrupt VSS database, I’m going to continue to ignore everyone’s advice and keep using VSS in the hopes that the 2005 version will be a cure-all (yah right!) J

In case you didn’t already know, you can run SQL scripts right within Visual Studio.NET which is much nicer than having to fire-up Query Analyzer all of the time.  This tutorial assumes you already know what a Database project is in Visual Studio.

Instructions:

The first thing you must do is create a Database Reference in your database project. In the Visual Studio Solution Explorer, open the database project and right click on the Database References folder.  Select New Database Reference… and the following window should pop-up:

If the database you’d like to connect to is not listed, click Add New Reference… and you will be prompted to setup a connection to the database of your choice.  Items are added to this list via the Server Explorer. 

You can add several database references to a project.  I would suggest making a reference to your local, development SQL database and one to the live web server’s SQL database.

Once the database you’d like to connect to is listed in the Add Database Reference list, select it and click OK.  You notice that this reference is now listed under the Database Reference section in the Solution Explorer:

If you have several databases listed, you can right-click on the one you want as the default (usually your local SQL database) and select Set as Project Default.

To run a SQL Script against a selected database, right-click one of the SQL scripts stored in the database project and select Run On…  You’ll be prompted with a window like this:

Select the database you’d like to run the script against, click OK, and the script runs!  Hopefully you’ll find this a useful and time-saving tip J

It seems easier than it is… looping through an array of items (collection) and removing items you don’t want.  However, when you remove an item from a collection while looping through it, the size of the collection changes and the loop may fail… depending on how you are looping.

The correct way to loop through a collection is to essentially loop through it backwards, starting with the item with the maximum ID and working down to zero:

    1  For i As Integer = MyList.Count - 1 To 0 Step -1
    2      If MyList.Items(i).Value <> SomeValue Then
    3          MyList.Remove(MyList(i))
    4      End If
    5  Next

Admit it, you do it to.  You can’t remember the .NET way to do something, so you give-in and put Imports Microsoft.VisualBasic at the top of your .vb file.  It’s time to stop the madness! Where there is help, there is hope.

As of recently, a co-worker explained to me the virtues of not using the old-school VB namespace in favor of learning the newer VB.NET way of doing things.  One of the benefits is that VB.NET code is typically optimized to run faster… and we all love speed, don’t we?    There were two places in particular that I kept resorting to the old VB way: date manipulation and formatting.

For example, here is the old VB way to add 7 days to the current date:

DateAdd(DateInterval.Day, 7, Date.Now)

And here is the VB.NET way:

Date.Now.AddDays(7)

That’s not so bad, is it? Now let’s look at formatting.  Sure, there are quite a few ways to do the same thing, but here’s an example.   If you want to format a date type variable in VB:

Format(Date.Now.ToString, "MM/dd/yyyy") 

… or …

FormatDateTime(Date.Now, DateFormat.ShortDate)

And in VB.NET:

Date.Now.ToString("MM/dd/yyyy")

Actually, the VB.NET way seems easier! I wish I would’ve explored the VB.NET method sooner.

Here's some more handy conversions:

To subtract to dates:

Dim dblDateDiff As Double = datEndDate.Subtract(datStartDate).Days

So far, I’ve only come across one VB function (but I’m sure there are more) that I haven’t been able to find a VB.NET replacement for: vbTab.  I found the replacement for vbCrLf is Environment.NewLine (one of the new VB.NET classes that is actually less convenient than its VB counterpart), but I have yet to find a way to put a simple tab space into a string.  Not a huge problem, but odd in my opinion since that means I can’t completely lay the Visual Basic namespace to rest!

To help yourself cure your addiction to Visual Basic (the old-school kind), set the filter in the Microsoft Visual Studio.NET 2003 Documentation library to ‘.NET Framework’ instead of ‘Visual Basic’.  I was under the impression that the ‘Visual Basic’ filter meant VB.NET, but it does not.