StrataFrame Forum

Setting BO's Default values from SQL Server's defaults

http://forum.strataframe.net/Topic10223.aspx

By Peter Denton - 7/11/2007

G'day

We are redeveloping a large system, and our database already had the defaults for columns, and we really wanted defaults to be defined in the database, so I developed the following code to set a BO's defaults from the database's schema. We have been using it for a couple of months and it has worked without a problem, and we decided to post it here because others may find it useful, someone might spot a flaw that may later bite us, and also to suggest that such a facility become part of SF.

    ''' <summary>
    ''' Sets the default values for a new row from the information obtained
    ''' the INFORMATION_SCHEMA.COLUMNS system view for the table of interest
    ''' in SQL Server 2005.
    ''' </summary>
    ''' <remarks>
    ''' Rules:
    ''' A column that is nullable will not have a default set
    ''' A column that is not nullable but has no default in the database will not have a default set
    ''' A uniqueidentifier can only have a default value that is a new guid, it can not be assigned a literal
    ''' A column of type "binary","image","timestamp","varbinary" will not be set a default value
    ''' A column of type "sql_variant" will not be set a default value
    ''' </remarks>
    Private Sub boBase_SetDefaultValues() Handles Me.SetDefaultValues
        ' Only execute once when the BO is instatiated.
        Static infoSchema As DataTable
        If infoSchema Is Nothing Then
            infoSchema = GetDataTable("select * from information_schema.columns where table_name = '" & Me.TableName & "'")
        End If

        Dim braces() As Char = {"("c, ")"c}             ' Used to trim brackets from column defaults
        Dim pk(1) As DataColumn

        pk(0) = infoSchema.Columns.Item(3)
        infoSchema.PrimaryKey = pk
        For Each field As System.Collections.Generic.KeyValuePair(Of String, System.Data.DbType) In Me.FieldDbTypes
            Dim row As DataRow = infoSchema.Rows.Find(field.Key)
            If row.Item(6).ToString = "NO" Then          ' i.e. not nullable
                Dim defaultValue As String
                Dim trimmedDefaultValue As String
                If Not IsDBNull(row.Item(5)) Then
                    defaultValue = row.Item(5).ToString
                    If defaultValue = "NULL" Then
                        defaultValue = ""
                    End If
                Else
                    defaultValue = ""
                End If
                ' MsgBox(field.Key.ToString & " " & defaultValue)
                trimmedDefaultValue = defaultValue.Trim(braces).Trim("'"c)
                Select Case field.Value
                    Case DbType.AnsiString              ' Corresponds to SQL Server 2005 "text","varchar"
                        Me.Item(field.Key.ToString) = trimmedDefaultValue
                    Case DbType.AnsiStringFixedLength   ' Corresponds to SQL Server 2005 "char"
                        Me.Item(field.Key.ToString) = trimmedDefaultValue
                    Case DbType.Binary                  ' Corresponds to SQL Server 2005 "binary","image","timestamp","varbinary"
                        ' No default allowed to be set.
                    Case DbType.Boolean                 ' Corresponds to SQL Server 2005 "bit"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Boolean)
                        ElseIf defaultValue.ToLower = "true" Then
                            Me.Item(field.Key.ToString) = True
                        ElseIf defaultValue.ToLower = "false" Then
                            Me.Item(field.Key.ToString) = False
                        End If
                    Case DbType.Byte                    ' Corresponds to SQL Server 2005 "tinyint"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Integer)
                        End If
                    Case DbType.Currency                ' Corresponds to SQL Server 2005 "money","smallmoney"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Decimal)
                        End If
                    Case DbType.Date                    ' No SQL Server 2005 Equivalent
                    Case DbType.DateTime                ' Corresponds to SQL Server 2005 "datetime","smalldatetime"
                        If trimmedDefaultValue = "getdate" Then
                            Me.Item(field.Key.ToString) = Now
                        ElseIf IsDate(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, DateTime)
                        Else
                            ' Database default maybe something like (getdate()-(7)). Do something here!
                            If trimmedDefaultValue.StartsWith("getdate()") Then
                                ' remove the getdate() and all braces and assume that gives us a single number we can add
                                trimmedDefaultValue = Mid(trimmedDefaultValue, 9).Replace("(", String.Empty).Replace(")", String.Empty)
                                If IsNumeric(trimmedDefaultValue) Then
                                    Me.Item(field.Key.ToString) = Now.AddDays(Val(trimmedDefaultValue))
                                End If
                            End If
                        End If
                    Case DbType.Decimal                 ' Corresponds to SQL Server 2005 "decimal","numeric"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Decimal)
                        End If
                    Case DbType.Double                  ' Corresponds to SQL Server 2005 "float"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Double)
                        End If
                    Case DbType.Guid                    ' Corresponds to SQL Server 2005 "uniqueidentifier"
                        If trimmedDefaultValue = "newid" Then
                            Me.Item(field.Key.ToString) = Guid.NewGuid
                        End If
                    Case DbType.Int16                   ' Corresponds to SQL Server 2005 "smallint"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Integer)
                        End If
                    Case DbType.Int32                   ' Corresponds to SQL Server 2005 "int"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Integer)
                        End If
                    Case DbType.Int64                   ' Corresponds to SQL Server 2005 "bigint"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Long)
                        End If
                    Case DbType.Object                  ' Corresponds to SQL Server 2005 "sql_variant"
                        ' No default allowed to be set.
                    Case DbType.SByte                   ' No SQL Server 2005 Equivalent
                    Case DbType.Single                  ' Corresponds to SQL Server 2005 "real"
                        If IsNumeric(trimmedDefaultValue) Then
                            Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Decimal)
                        End If
                    Case DbType.String                  ' Corresponds to SQL Server 2005 "ntext","nvarchar"
                        Me.Item(field.Key.ToString) = defaultValue.Trim(braces).Trim("N"c).Trim("'"c)
                    Case DbType.StringFixedLength       ' Corresponds to SQL Server 2005 "nchar"
                        Me.Item(field.Key.ToString) = defaultValue.Trim(braces).Trim("N"c).Trim("'"c)
                    Case DbType.Time                    ' No SQL Server 2005 Equivalent
                    Case DbType.UInt16                  ' No SQL Server 2005 Equivalent
                    Case DbType.UInt32                  ' No SQL Server 2005 Equivalent
                    Case DbType.UInt64                  ' No SQL Server 2005 Equivalent
                    Case DbType.VarNumeric              ' No SQL Server 2005 Equivalent
                    Case DbType.Xml                     ' Corresponds to SQL Server 2005 "xml"
                        Me.Item(field.Key.ToString) = trimmedDefaultValue
                    Case Else
                End Select
            End If
        Next
    End Sub

We have implemented this in a class boBase that Inherits MicroFour.StrataFrame.Business.BusinessLayer. We then create all our BOs by inheriting boBase, so we have it in every BO, but it could be pasted into BO's as required.

I would be pleased to hear your thoughts on it.

Enjoy!

Peter

By Greg McGuffey - 7/12/2007

Peter,



Wow, this is very cool. I have need of this functionality, but haven't had time to even consider how to implement it. I'll be looking this over in the next few weeks and will let you know if I find anything or have any suggestions or alternate implementation ideas.



Thanks!



P.S. This post would go perfectly in the forum/area Ben Hayat suggested, one for user examples/samples. See the original post at:



http://forum.strataframe.net/Topic10169-10-1.aspx#bm10179
By StrataFrame Team - 7/12/2007

That would work quite well, Peter, thanks for sharing Smile

FYI, by default, a business object initializes it's fields within a new row to the default values for the specific data type, but, if you turn the AllowNullValuesOnNewRow property to True (defaults to False) on a business object, it will not initialize the fields within the business object, but rather leave them as null values (DBNull.Value).  When business object is saved, these null values are pushed to the database, and the default constraint on the field is used instead. 

What you're solution buys you over this, is that this solution requires you to re-retrieve the record after a save in order to get the correct values from the server; yours does it all up front.

By Trent L. Taylor - 7/12/2007

There is now a forum section for samples to be added off of the home page titled "StrataFrame Users Contributed Samples" where items such as this can be added.  Thanks for sharing!
By Peter Denton - 7/29/2007

G'day

I've found a slight problem in the subroutine that causes an InvalidCastException when dealing with the default of a real column in a database. To fix it the section of code in the case statement dealing with DbType.Single needs to be changed as follows:

Case DbType.Single                      ' Corresponds to SQL Server 2005 "real"

    If IsNumeric(trimmedDefaultValue) Then

        Me.Item(field.Key.ToString) = CType(trimmedDefaultValue, Single)

    End If

The CType now converts to Single not Decimal as was originally shown. Sorry for any inconvenience caused.

Peter

By StrataFrame Team - 7/30/2007

Good catch, Peter Smile
By Fabian R Silva, - - 1/27/2012

I have a question about the fieldNativeDbTypes generation from the BOM on http://forum.strataframe.net/Topic30830.aspx. if someone can answer what is the best aproach to avoid have to read schema from sql and use fields that BOM fills, please answer this post. thanks!