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