{ Duane.Wingett }

VB.Net Nullable Date Object - Part 4

Duane Wingett — Sat, 07 Jan 2012 09:00:42 GMT

Operators for System.DateTime, continued from Part 3

In this article I shall continue to deal with operators, but those specifically which will allow assignment of System.DateTime values and System.Data.SqlTypes.SqlDateTime values to the DateTimeNull object or vice-versa.

#Region "Operators
for System.DateTime"     '''
<summary>     '''
Converts a System.DateTime structure to a DateTimeNull object.     '''
</summary>     '''
<param name="value">A DateTime structure. </param>     '''
<returns>A DateTimeNull object whose Value is equal to the combined Date and
TimeOfDay properties of the supplied System.DateTime structure.</returns>     Public Shared Widening
Operator CType(ByVal value As System.DateTime) As DateTimeNull
        Return New DateTimeNull(value)
    End Operator
    '''
<summary>     '''
Converts a DateTimeNull object to a System.DateTime structure.     '''
</summary>     '''
<param name="value">a DateTimeNull</param>     '''
<returns>A System.DateTime structure whose Value is equal to the combined Date
and TimeOfDay properties of the supplied DateTimeNull object.</returns>     Public Shared Widening
Operator CType(ByVal value As DateTimeNull) As System.DateTime
        If value.HasValue Then             Return value.InternalDate
        Else             Throw New InvalidCastException("Cannot
convert Null DateTimeNull to System.DatetTime.")         End If     End Operator
#End Region

These two widening operators allow us to write the following code without compile errors:

Dim nullableDate As DateTimeNull = Nothing Dim systemDate As System.DateTime
nullableDate = systemDate
systemDate = nullableDate

The first widening operator lets us cast from a System.DateTime to our DateTimeNull object, and the second one back again. Even with these two operators we cant yet do comparison checks between the two types. The code below will not compile.

If nullableDate = systemDate Then End If If systemDate = nullableDate Then End If

For that to happen we need to add an equality operators. The code below will allow the first equality check to compile. Please note for every "equals" operator, you must provide a "not equals" operator too.

 '''
<summary> '''
Determines whether one specified DateTimeNull is equal to another specified DateTime. '''
</summary> '''
<param name="d1">A DateTimeNull.</param> '''
<param name="d2">A System.DateTime.</param> '''
<returns><c>true</c> if d1 is not null and d1 is equal to d2; otherwise,
false.</returns> Public Shared Operator =(ByVal d1 As DateTimeNull, ByVal d2 As System.DateTime) As Boolean If d1.HasValue Then Return (d1.InternalDate = d2) Else Return False End If End Operator '''
<summary> '''
Determines whether one specified DateTimeNull is not equal to another specified DateTime. '''
</summary> '''
<param name="d1">A DateTimeNull.</param> '''
<param name="d2">A System.DateTime.</param> '''
<returns><c>true</c> if d1 is not null and d1 is not equal to d2;
otherwise, false.</returns> Public Shared Operator
<>(ByVal d1 As DateTimeNull, ByVal d2 As System.DateTime) As Boolean If d1.HasValue Then Return Not (d1.InternalDate = d2) Else Return False End If End Operator

We now need another pair of operators for the second condition.

 '''
<summary> '''
Determines whether one specified System.DateTime is equal to another specified DateTimeNull. '''
</summary> '''
<param name="d1">A System.DateTime.</param> '''
<param name="d2">A DateTimeNull.</param> '''
<returns><c>true</c> if d1 is not null and d1 is equal to d2; otherwise,
false.</returns> Public Shared Operator =(ByVal d1 As System.DateTime, ByVal d2 As DateTimeNull) As Boolean If d2.HasValue Then Return (d1 = d2.InternalDate) Else Return False End If End Operator '''
<summary> '''
Determines whether one specified System.DateTime is not equal to another specified
DateTimeNull. '''
</summary> '''
<param name="d1">A System.DateTime.</param> '''
<param name="d2">A DateTimeNull.</param> '''
<returns><c>true</c> if d1 is not null and d1 is not equal to d2;
otherwise, false.</returns> Public Shared Operator
<>(ByVal d1 As System.DateTime, ByVal d2 As .DateTimeNull) As Boolean If d2.HasValue Then Return Not (d1 = d2.InternalDate) Else Return False End If End Operator

We may now wish to take the time to add in some "greater-than-or-equal-to" and "less-than-or-equal-to" operators for these two data types.

    '''
<summary>     '''
Determines whether one specified DateTimeNull is less than or equal to another specified
System.DateTime.     '''
</summary>     '''
<param name="d1">A DateTimeNull.</param>     '''
<param name="d2">A System.DateTime.</param>     '''
<returns><c>true</c> if d1 is not null and d1 is less than or equal
to d2; otherwise, false.</returns>     Public Shared Operator
<=(ByVal d1 As DateTimeNull, ByVal d2 As System.DateTime) As Boolean         If d1.HasValue Then             Return (d1.InternalDate
<= d2)         Else             Return False         End If     End Operator
    '''
<summary>     '''
Determines whether one specified DateTimeNull is greater than or equal to another
specified System.DateTime.     '''
</summary>     '''
<param name="d1">A DateTimeNull.</param>     '''
<param name="d2">A System.DateTime.</param>     '''
<returns><c>true</c> if d1 is not null and d1 is greater than or
equal to d2; otherwise, false.</returns>     Public Shared Operator
>=(ByVal d1 As DateTimeNull, ByVal d2 As System.DateTime) As Boolean         If d1.HasValue Then             Return (d1.InternalDate
>= d2)         Else             Return False         End If     End Operator

Remember to provide operators for both left and right evaluation of the DateTimeNull object.

Casting to and from System.Data.SqlTypes.SqlDateTime

To allow cross casting between our DateTimeNull object and the System.Data.SqlTypes.SqlDateTime we just follow the same process and add in the widening and comparison operators as we did for the System.DateTime, but we do need to keep in mind the upper and lower date limits of the System.Data.SqlTypes.SqlDateTime structure or an out of range error could happen at run-time. So evaluate the value and throw an appropriate exception as necessary in the operator.

In Part 5, we will look at some of the methods we may need to implement

VB.Net Nullable Date Object - Part 3

Duane Wingett — Fri, 06 Jan 2012 13:20:15 GMT

Operators for DBNull.Value, continues from Part 2

In this article I shall deal with operators, specifically the ones which will allow assignment of a value to the DateTimeNull object or vice-versa. For example I want to be able to run this code:

Dim nullableDate As DateTimeNull
= Nothing nullableDate = DBNull.Value

To do this I need to create a couple of shared (non-instance) 'Widening Operator' for DBNull which will allow the compiler to cast the values.

#Region "Operators
for DBNull"     '''
<summary>     '''
Converts a System.DBNull object to a DateTimeNull object.     '''
</summary>     '''
<param name="value"></param>     '''
<returns>Returns a DateTimeNull with a Null value.</returns>     '''
<remarks></remarks>     Public Shared Widening
Operator CType(ByVal value As System.DBNull) As DateTimeNull
        Return New DateTimeNull(value)
    End Operator
    '''
<summary>     '''
Converts a DateTimeNull object to a System.DBNull object.     '''
</summary>     '''
<param name="value"></param>     '''
<returns></returns>     '''
<exception cref="InvalidCastException">Thrown if the DateTimeNull is not null.</exception>     Public Shared Widening
Operator CType(ByVal value As DateTimeNull) As System.DBNull
        If value.IsNull Then             Return System.DBNull.Value
        End If         Throw New InvalidCastException("A
non null DateTimeNull cannot be cast to DBNull.")     End Operator
#End Region

The first operator will return a DBNull.Value to be asigned to the DateTimeNull object, like so

nullableDate = DBNull.Value

The second widening operator is required when we want to do some comparisions of the DateTimeNull to DBNull.Value. To see what I mean, we first need to add an equality operator.

    '''
<summary>     '''
Performs a logical comparison between a DateTimeNull object      '''
and a System.DBNull object to determine whether they are equal.     '''
</summary>     '''
<param name="d1">A DateTimeNull object.</param>     '''
<param name="d2">A System.DBNull object.</param>     '''
<returns>Returns <c>true</c> if d1 is null.</returns>     Public Shared Operator =(ByVal d1 As DateTimeNull, ByVal d2 As System.DBNull) As Boolean         Return d1.IsNull
    End Operator

This will allow us to as this question...

    If (nullableDate = DBNull.Value) Then         '
Do something...     End If

But without the second Widening operator, we can not ask this question, without the compiler complaining...

    If (DBNull.Value = nullableDate) Then         '
Do something...     End If

Try it and see. If you comment out the second widening operator, then the equality test with DBNull.Value as the left hand value will not compile. Uncomment it and all is well!

Finaly, we'll add in a not equal operator.

    '''
<summary>     '''
Performs a logical comparison between a DateTimeNull object      '''
and a System.DBNull object to determine whether they are not equal.     '''
</summary>     '''
<param name="d1">A DateTimeNull object.</param>     '''
<param name="d2">A System.DBNull object.</param>     '''
<returns>Returns <c>true</c> if d1 is not null.</returns>     Public Shared Operator
<>(ByVal d1 As DateTimeNull, ByVal d2 As System.DBNull) As Boolean         Return Not d1.IsNull
    End Operator

In the next artical we'll cover using widening operators for casting to and from System.Datetime and SqlDateTime. (TBC)...

VB.Net Nullable Date Object - Part 2

Duane Wingett — Thu, 05 Jan 2012 13:21:05 GMT

Constructors Continued from Part 1...

We will need a series of constructors which will be used when we create Widening Operators, later. The first we aill use will accept a parameter value for ticks as an Int64. We will set the date using our InternalDate property as this will handle setting the inherited _isNull field for us.

    '''
<summary>     '''
Initializes a new instance of the <see cref="DateTimeNull" /> class to a specified
number of ticks.     '''
</summary>     '''
<param name="ticks">The ticks.</param>     Public Sub New(ByVal ticks As Long)
        MyBase.New()
        Me.InternalDate = New System.DateTime(ticks)
    End Sub

The next constructor will take a System.DateTime parameter to construct our object.

    '''
<summary>     '''
Initializes a new instance of the <see cref="DateTimeNull" /> class using the
specified System.DateTime value.     '''
</summary>     '''
<param name="value">The value.</param>     Public Sub New(ByVal value As System.DateTime)
        Me.New(value.Ticks)
    End Sub

The following constructor will take a System.Data.SqlTypes.SqlDateTime parameter to construct our object.

    '''
<summary>     '''
Initializes a new instance of the <see cref="DateTimeNull" /> class using the
specified System.Data.SqlTypes.SqlDateTime value.     '''
</summary>     '''
<param name="value">The value.</param>     Public Sub New(ByVal value As System.Data.SqlTypes.SqlDateTime)
        MyBase.New()
        Dim dt As System.DateTime
        dt = Convert.ToDateTime(value)
        Me.InternalDate = dt
    End Sub

The last constructor will take a System.DBNull to construct or object.

    '''
<summary>     '''
Initializes a new instance of the <see cref="DateTimeNull" /> class using a
system.DBNull object.     '''
</summary>     '''
<param name="value">The value.</param>     Public Sub New(ByVal value As System.DBNull)
        Me.New(System.DateTime.MinValue)
        If Not Me.IsNull Then             Me.SetIsNull(True)
        End If     End Sub

With our constructors done we will take a quick look at some of the properties which may be useful. Each property will reflect one of the properties that the .Net System.Datetime object has. Reflector is a useful tool for investigating these. Each property will expose the underlying InternalDate's properties, but first it must check if our object is in the NULL state. I have included just a few properties so you can see the pattern used.

    '''
<summary>     '''
Gets the date component of this instance.     '''
</summary>     '''
<value>A new DateTime with the same date as this instance, and the time value
set to 12:00:00 midnight (00:00:00).</value>     '''
<exception cref="ArgumentNullException">Thrown</exception>     Public ReadOnly Property [Date]() As DateTimeNull
        Get             If Me.HasValue Then                 Return New DateTimeNull(Me.InternalDate.Date)
            Else                 Throw New ArgumentNullException("Date
cannot be returned for a null DateTimeNull")             End If         End Get     End Property     '''
<summary>     '''
Gets the day of the month represented by this instance.     '''
</summary>     '''
<remarks>The day component, expressed as a value between 1 and 31.</remarks>     Public ReadOnly Property Day() As System.Int32
        Get             If Me.HasValue Then                 Return Me.InternalDate.Day             Else                 Throw New NullReferenceException("Day
cannot be expressed for a null DateTimeNull object. ")             End If         End Get     End Property     '''
<summary>     '''
Gets the hour component of the date represented by this instance.     '''
</summary>     '''
<returns>The hour component, expressed as a value between 0 and 23, inclusive.</returns>     Public ReadOnly Property Hour() As System.Int32
        Get             If Me.HasValue Then                 Return Me.InternalDate.Hour             Else                 Throw New NullReferenceException("Hour
cannot be expressed for a null DateTimeNull object. ")             End If         End Get     End Property     '''
<summary>     '''
Gets a System.DateTime object that is set to the current date and time on this computer,
expressed as the local time.     '''
</summary>     '''
<returns>A DateTime whose value is the current local date and time.</returns>     Public Shared ReadOnly Property Now() As DateTime
        Get             Return DateTime.UtcNow.ToLocalTime
        End Get     End Property     '''
<summary>     '''
Gets the current date.     '''
</summary>     '''<returns>A
DateTime set to today's date, with the time component set to 00:00:00.</returns>     Public Shared ReadOnly Property Today() As DateTime
        Get             Return DateTime.Now.Date         End Get     End Property

Over time I will add more properties to my DateTimeNull object, as they are required. Next however, it is time to look at Operators. This will be in Part 3.

VB.Net Nullable Date Object - Part 1

Duane Wingett — Wed, 04 Jan 2012 13:20:09 GMT

Background

Something I have always been a bit disappointed with in .net is the handling for dates with null values. I know you can use Nullable(Of Date) or Nullable(Of DateTime) (or i C# date? or Datetime?), but what I want is a date object that I can set a date value, set to Nothing or set to be equal to DBNull.Value.

So I thought I would document my path to creating the object I need. The code will be in VB.Net as that is what I curently have to use at my place of work.

Nullable Base Object.

First of all as I may need other objects like Int32s and Strings to be nullable in the future I'll create a nullable base object which all my new objects can inherit from and will contain some basic implementation. First I am going to create a class and declare it MustInherit (abstract)so that cannot be used on its own. This will be the base object that any or all of my nullable objects will inherit from.

Option Explicit On Option Strict On '''
<summary> '''
This object is the object that all nullable objects should inherit from. '''
</summary> Public MustInherit Class NullableBaseObject Implements System.Data.SqlTypes.INullable
#Region "Declarations" Protected _isNull As Boolean #End Region
#Region "Properties" '''
<summary> '''
Gets a value indicating whether this instance has value. '''
</summary> '''
<value><c>true</c> if this instance has value; otherwise, 

false

.</value> Public ReadOnly Property HasValue() As Boolean Get Return Not Me._isNull End Get End Property '''
<summary> '''
Gets a value indicating whether this instance is null. '''
</summary> '''
<value><c>true</c> if this instance is null; otherwise, 

false

.</value> Public ReadOnly Property IsNull() As Boolean Implements System.Data.SqlTypes.INullable.IsNull Get Return Me._isNull End Get End Property #End Region
#Region "Methods" '''
<summary> '''
Sets the internal is-null flag. '''
</summary> '''
<param name="value">if set to <c>true</c> [value]. Protected Sub SetIsNull(ByVal value As Boolean) Me._isNull = value End Sub  #End Region End Class

There is a private boolean field _isNull which will hold the nullable state of the object. There are two properties that refer to this; IsNull and HasValue. These are in essence opposites of each other but have both been implemented to make the code more English when using. The IsNull property is implementation required from the "System.Data.SqlTypes.INullable" interface that the class is going to implement.

There is one method, The SetIsNull which sets the object state Null and can be called from inside any classes that inherit from this object.

Nullable DateTime Object

The nullable DateTime object will inherit from the NullableBaseObject. There will be a single private field _internalDate of type System.DateTime. This will hold our DateTime information when the object is not Null. This will be accessed by all internal code using a private property InternalDate. Unlike the System.DateTime which is Structure, I want to be able to set this object to Nothing, so I will use a Class, not a Structure.

Option Explicit On Option Strict On '''
<summary> '''
Represents nullable an instance in time, typically represented as a date and time. '''
</summary> <serializable()> _ Public Class DateTimeNull Inherits NullableBaseObject
#Region "Declarations" Private _internalDate As System.DateTime
#End Region
#Region "Properties" '''
<summary> '''
Privately gets or sets the internal date. When set IsNull property is set to false. '''
</summary> '''
<value>The internal date.</value> Private Property InternalDate() As System.DateTime Get Return Me._internalDate End Get Set(ByVal value As System.DateTime) Me._internalDate = value If Me.IsNull Then Me.SetIsNull(False) End If End Set End Property #End Region End Class

We will provide an overridden ToString() method which will return the default ToString() value for either the System.DateTime object or the System.DBNull.Value, depending if our object is in a Null state or not.

#Region "Methods" '''
<summary> '''
Converts the value of this instance to its equivalent string representation. '''
</summary> '''
<returns> '''
A <see cref="System.String"> that represents this instance. '''
</returns> Public Overrides Function ToString() As String If Me.HasValue Then Return Me.InternalDate.ToString Else Return DBNull.Value.ToString End If End Function #End Region

We will now look at the constructors we wish to use to allow creation of our object. First we'll look at the default constructor.This constructor initialises the base object, and then calls the base object's SetIsNull() method to set a null state.

#Region "Constructors" '''
<summary> '''
Initializes a new instance of the 

class, with IsNull = true.

 '''
</summary> Public Sub New() MyBase.New() Mybase.SetIsNull(True) End Sub #End Region

In Part 2, we'll look at adding more constructors that we will need when creating casting operators later.

Dual running SQL Server 2005 and 2008 Express on Windows 7

Duane Wingett — Sat, 24 Sep 2011 07:56:43 GMT

Background

Over the last week I have been periodically trying to get SQl Server 2005 express and SQL Server 2008 express to run side by side. One of the issues is there is a restriction when trying to instal SQL 2008 Express over the top of 2005 with the "SQL 2008 Install Blocked..." message

To get around this I managed to find information on a blog post (I will try to find and post a link to it) explaining how to move the registry entry for 2005, just while installing 2008.

The all inportant registry entry is:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server

Before installing SQL Server 2008 Express rename the '90' node to '90_old' and then install as normal. Once the installation has taken place refersh the node list and yiou will see a new '90' has appeared. Rename that to something else. I chose'70' as I will never be installing SQl Server 7 on this machine! Rename yiour '90_old' back to '90' and restart the machine.

When you install 2008 remember to choose a different instance name than the one you choose for SQL 2005

One thing to note is you may wish to detach your SQL Server 2005 databases first as I now seem to be unable to connect to one of them on my SQL 2005 instance. I need to look into how to reconnect this database!

Update: It turns out that I had to give some more permissions to the database mdf files. I had to add "NETWORK SERVICE" and give it full control. I was then able to detach and reattach the databse and I can now see all databases in both 2005 and 2008 Management Studio Express versions.

Comment Abuse

Duane Wingett — Wed, 20 Apr 2011 17:29:54 GMT

Due to comment abuse, comments have now been disabled.

Sorry.

Create VB.Net class from Sql Server table

Duane Wingett — Tue, 12 Apr 2011 19:29:18 GMT

Create VB.Net class from Sql Server table

Have you ever wanted to be able to create VB.Net classes that have fields that emulate the columns in your SQL server tables?

Well if you have, then read on...

A little project I am working on at home has a table with a lot of columns, and the architecture I am writing the application to requires an entity which will emulate this table. I couldn't bear the idea of writing all those private fields and public properties out, even with Visual Studios "Property" snippet.

So I got to thinking, can I query the database and output a class structure in VB.Net language. Well with the help of SQL Server Cursors, yes I can.

---------------------------------- --
DW: 11/04/2011 --
This script will build a class file with  --
private fields and public properties from --
the table specified in the @TableName variable. ---------------------------------- DECLARE @TableName varchar(50) SET @TableName
= 'MyTable' SET NOCOUNT ON; --
Hide row count so printed output is not affected DECLARE @DataTypeName varchar(50) DECLARE @NewLine char DECLARE @ColumnName varchar(50) DECLARE @DataType varchar(50) DECLARE @FieldName varchar(50) --SET @NewLine
= char(13) --
Start Output --PRINT
'' + @NewLine; PRINT 'Public
Class ' + @TableName; PRINT ''; PRINT '#Region
"Declarations"'; PRINT ''; --
Declarations DECLARE DeclarationCursor CURSOR SCROLL FOR     SELECT         columns.name
[ColumnName],         CASE             WHEN columns.system_type_id
= 34 THEN 'Byte[]'             WHEN columns.system_type_id
= 35 THEN 'String'             WHEN columns.system_type_id
= 36 THEN 'System.Guid'             WHEN columns.system_type_id
= 48 THEN 'Byte'             WHEN columns.system_type_id
= 52 THEN 'Short'             WHEN columns.system_type_id
= 56 THEN 'Integer'             WHEN columns.system_type_id
= 58 THEN 'System.DateTime'             WHEN columns.system_type_id
= 59 THEN 'float'             WHEN columns.system_type_id
= 60 THEN 'Decimal'             WHEN columns.system_type_id
= 61 THEN 'System.DateTime'             WHEN columns.system_type_id
= 62 THEN 'double'             WHEN columns.system_type_id
= 98 THEN 'Object'             WHEN columns.system_type_id
= 99 THEN 'String'             WHEN columns.system_type_id
= 104 THEN 'Boolean'             WHEN columns.system_type_id
= 106 THEN 'Decimal'             WHEN columns.system_type_id
= 108 THEN 'Decimal'             WHEN columns.system_type_id
= 122 THEN 'Decimal'             WHEN columns.system_type_id
= 127 THEN 'long'             WHEN columns.system_type_id
= 165 THEN 'Byte[]'             WHEN columns.system_type_id
= 167 THEN 'String'             WHEN columns.system_type_id
= 173 THEN 'Byte[]'             WHEN columns.system_type_id
= 175 THEN 'string'             WHEN columns.system_type_id
= 189 THEN 'Long'             WHEN columns.system_type_id
= 231 THEN 'String'             WHEN columns.system_type_id
= 239 THEN 'String'             WHEN columns.system_type_id
= 241 THEN 'String'             WHEN columns.system_type_id
= 241 THEN 'String'         END [DataType] FROM sys.tables
tables INNER JOIN sys.schemas
schemas ON (tables.schema_id
= schemas.schema_id ) INNER JOIN sys.columns
columns ON (columns.object_id =
tables.object_id) WHERE     tables.name
= @TableName ORDER BY     columns.object_id ASC; OPEN DeclarationCursor; FETCH NEXT FROM DeclarationCursor INTO @ColumnName,
@DataType; WHILE @@FETCH_STATUS =
0 BEGIN     SET @FieldName
= '_' + LOWER(SUBSTRING(@ColumnName,
1,1)) + SUBSTRING(@ColumnName,
2, LEN(@ColumnName)-1) PRINT '
Private ' + @FieldName + '
As ' + @DataType;     FETCH NEXT FROM DeclarationCursor
    INTO @ColumnName,
@DataType; END PRINT ''; PRINT '#End
Region'; PRINT ''; PRINT '#Region
"Properties"'; PRINT ''; FETCH FIRST FROM DeclarationCursor INTO @ColumnName,
@DataType; WHILE @@FETCH_STATUS =
0 BEGIN     SET @FieldName
= '_' + LOWER(SUBSTRING(@ColumnName,
1,1)) + SUBSTRING(@ColumnName,
2, LEN(@ColumnName)-1) PRINT '
Public Property ' + @ColumnName + '
As ' + @DataType;     PRINT '
Get';     PRINT '
Return ' + @FieldName;     PRINT '
End Get';     PRINT '
Set';     PRINT '
' + @FieldName + '
= value';     PRINT '
End Set';     PRINT '
End Property';     PRINT '';
    FETCH NEXT FROM DeclarationCursor
    INTO @ColumnName,
@DataType; END PRINT ''; PRINT '#End
Region'; PRINT ''; DECLARE @FIELDS varchar(max) SET @FIELDS
= '' FETCH FIRST FROM DeclarationCursor INTO @ColumnName,
@DataType; WHILE @@FETCH_STATUS =
0 BEGIN     SET @FieldName
= LOWER(SUBSTRING(@ColumnName,
1,1)) + SUBSTRING(@ColumnName,
2, LEN(@ColumnName)-1)
    SET @FIELDS
= @FIELDS + 'byval
' + @FieldName + '
as ' + @DataType + ',
'     FETCH NEXT FROM DeclarationCursor
    INTO @ColumnName,
@DataType; END SET @FIELDS
= SUBSTRING(@FIELDS,
1, (LEN(@FIELDS)
- 1)) PRINT '#Region
"Constructors"'; PRINT ''; PRINT '
Public Sub New()'; PRINT '
'; PRINT '
End Sub'; PRINT ''; PRINT '
Public Sub New(' + @FIELDS + ')' PRINT '
Me.New()'; PRINT '
End Sub'; PRINT ''; PRINT '#End
Region'; PRINT ''; CLOSE DeclarationCursor; DEALLOCATE DeclarationCursor; --
End of Class PRINT 'End
Class';

The class also includes a parametricised public constructor, too.

( Feel free to use all or any of this code how ever you choose. Feel free to post it on your own blog, as long as you credit this page with a back link. )

I hope that this snippet of SQL will help you as much as it has helped me today.

Best regards,
Duane.

Mixed Scope Properties in .Net

Duane Wingett — Thu, 02 Sep 2010 18:06:44 GMT

Mixed Scope Properties in .Net

There may be a time when you want to have mixed scope for your "Getters" and "Setters" in a property. For those of you who are not familiar with the syntax here it is

VB:

Public Property MyProperty as String Get Return _myField End Get Private Set _myField = Value End Set End Property

This example allows a public "Getter" and a private "Setter".

The same can be done in C#:

public string MyProperty
{ get { return _myField;
} private set {
_myField = value;
} }

Hope you find that useful.

Regards,

Dib.

VB.Net Single Instance application on Terminal Server

Duane Wingett — Fri, 30 Jul 2010 07:21:34 GMT

VB.Net Single Instance application on Terminal Server

Yesterday at work I spent nearly the whole day bashing my head against a wall trying to replicate functionality that our original VB6 application had into the new .Net conversion. The orginal VB6 application used "App.PrevInstance" to determine if another instance was running or not. This appeared to work for multiple users on Terminal Servers, so each user could run an instance of the app, but it would warn if they tried to open another.

VB.Net does not appear to have such direct functionality. Looking on the web provided various 'solutions' to the problem. However each one seemed to have an 'issue' with our scenario. By the afternoon I was looking at the Mutex objects which a lot of people seemed to find worked for them. However for me I was getting an "Object synchronization method was called from an unsynchronized block of code" error when closing the second instance onwards.

Looking into the Mutex object in a bit more depth it seemed that maybe it was not the right object for our purpose but maybe the Semaphore object would be better.

In the end the solution I used was to use a named Semaphore. When the application first instance runs it would attempt to open the named Semaphore and if it did not exist, assume it was the first instance and create one. The second instance when run would again look for and open the named Semaphore and if it exists and it manages to open it then it would assmue it is a second instance of the application.

By prefixing the Semaphorename with "Local\" it will allow each Terminal Server session to have it's own Semaphore so each remote user would not affected by another.

The elements of the code I used are below.

Imports:

Imports System.Threading

Declarations:

Private SingleInstanceSemaphore As Semaphore = Nothing '
This needs to live for the life of the application Private Const MAX_SESSION_INSTANCES As Integer = 1 Private Const SEMAPHORE_NAME As String = "{9A49BB2C-45D2-4bb9-B38E-74F9DB612B96}" '
Unique for each application

Main Method:

'''
<summary> '''
This is the main application entrance point. '''
</summary> Public Sub Main() '
We need a name for the semaphore that is specific to to the Application / User combination '
We prefix the semaphoreName with "Local\" to allow this to run under terminal services.  '
The "Local\" prefix forces this into local user space.  '
If we want to forbid this in TS, use the "Global\" prefix. Dim semaphorePrefix As String = "Local" '
Or "Global" if required Dim semaphoreName As String = String.Format("{0}\{1}{2}",
semaphorePrefix, SEMAPHORE_NAME, Environment.UserName) Dim anotherInstanceRunningForUser As Boolean = True Dim runAnotherDialogResult As DialogResult = DialogResult.No Dim mainForm As Form1 = Nothing Try Try SingleInstanceSemaphore = Semaphore.OpenExisting(semaphoreName) Catch ex As Exception
anotherInstanceRunningForUser = False End Try If (anotherInstanceRunningForUser = False) Then SingleInstanceSemaphore = New Semaphore(0,
MAX_SESSION_INSTANCES, semaphoreName) End If If (anotherInstanceRunningForUser) Then runAnotherDialogResult = MessageBox.Show("Another
instance is already running. Do you want to run another?", _

"Already
Running...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) End If '
If this is the first instance of the application, or a further instance but the user
wants more than one... If (anotherInstanceRunningForUser = False) Or _
((anotherInstanceRunningForUser = True) And (runAnotherDialogResult = DialogResult.Yes)) Then '
Run the application mainForm = New Form1(mutexName)
Application.Run(mainForm) End If Catch ex As Exception MsgBox(ex.Message) End Try

I hope that mayhelp someone in the future and save them a day trawling through the web sticking examples of code together and getting failed results like I did.

Regards,
Dib.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Duane Wingett — Tue, 08 Jun 2010 18:35:03 GMT

Today I hit hit this error message while filling a typed DataSet Datatable. The odd thing was I could preview the data in the DataSet designer and the data looked fine.

I had to resort to Google and that returned this page, which if you hit this error, I would definately recommend. Look specifically for Sanjeys post about six posts down.

Roy Osherove's Blog - Dataset Hell