Lookup defined default value for columns from database schema RSS

• • Reply
  

  MdV

  Member

  199 Points

  254 Posts

  Lookup defined default value for columns from database schema

  Jun 11, 2009 11:15 AM|LINK

  For many columns in our database we have defined default values, and we would like to lookup those default values to display them in textboxes etc. in DetailsViews in insert mode.

  The DataTableReader.GetSchemaTable method should return a datatable with, among other things, a column named 'DefaultValue' (see http://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable.aspx).
  
  I've tried that, but the value reported for DefaultValue is always System.DBNull, even if I know for sure that I should get a "real" default value as defined in the database (like 0 or sometimes 99 for numeric fields).
  
  Any ideas how to solve this?

  Some of my VB.NET source code:

  Public Function GetColumnDefaultValue(ByVal p_sTable As String, ByVal p_sColumn As String)
          Trace.WriteLine("wsDataProvider.GetColumnDefaultValue: " & p_sTable & "." & p_sColumn)
          Dim l_vDefault As Object = Nothing, l_bError As Boolean = False
          If (IsColumn(p_sTable, p_sColumn)) Then
              Dim l_sSql As String
              If (UsesAccessDB()) Then
                  l_sSql = "select top 1 `" & p_sColumn & "` from `" & p_sTable & "`"
              Else
                  l_sSql = "select `" & p_sColumn & "` from `" & p_sTable & "` limit 1"
              End If
              Try
                  Dim l_oDataSet As DataSet = FillDataSet(l_sSql, "DataColumns")
                  Dim l_oDataTableReader As DataTableReader = l_oDataSet.CreateDataReader(l_oDataSet.Tables(0))
                  Dim l_oSchema As DataTable = l_oDataTableReader.GetSchemaTable()
                  l_vDefault = l_oSchema.Rows(0)("DefaultValue")
              Catch ex As Exception
                  System.Diagnostics.Debug.WriteLine("wsDataProvider.GetColumnDefaultValue: " & ex.Message)
                  '
                  l_bError = True
                  _oErrorHandler.LogError()
              End Try
          End If
          If l_bError = True Then
              _oErrorHandler.DisplayErrorPage()
          End If
          Return l_vDefault
      End Function

• • Reply
  

  MdV

  Member

  199 Points

  254 Posts

  Re: Lookup defined default value for columns from database schema

  Dec 04, 2009 08:41 PM|LINK

  Yes, I've managed to solve this. I now use the following method on my DataProvider class:

  Public Function GetColumnDefaultValue(ByVal p_sTable As String, ByVal p_sColumn As String)
          'wsUtils.Trace("wsDataProvider.GetColumnDefaultValue: " & p_sTable & "." & p_sColumn)
          Dim l_vDefault As Object = Nothing, l_bError As Boolean = False
          Dim l_oErrorHandler As New wsErrorHandler
          If (IsColumn(p_sTable, p_sColumn)) Then
              If UsesMySQL() Then
                  Try
                      Dim l_sDBname As String = ConfigurationManager.AppSettings("wsDBname")
                      Dim l_sSql As String = "SHOW COLUMNS FROM " & p_sTable & " FROM " & l_sDBname
                      Dim l_oDTcolumns As DataTable = FillDataTable(l_sSql)
                      If l_oDTcolumns IsNot Nothing AndAlso l_oDTcolumns.Rows.Count > 0 Then
                          ' loop through DB table columns for definition
                          Dim l_sColumnName As String
                          For Each l_oDTcolumn As DataRow In l_oDTcolumns.Rows
                              l_sColumnName = l_oDTcolumn(0)
                              If l_sColumnName.ToLower = p_sColumn.ToLower Then
                                  l_vDefault = l_oDTcolumn(4)
                                  Exit For
                              End If
                          Next
                          ' clean up
                          l_oDTcolumns.Dispose()
                      End If
                  Catch ex As Exception
                      wsUtils.Warn("wsDataProvider.GetColumnDefaultValue: " & ex.Message)
                      l_bError = True
                      l_oErrorHandler.LogError(ex)
                  End Try
              Else
                  ' Access DB
                  Try
                      Dim l_oCatalog As New ADOX.Catalog
                      l_oCatalog.ActiveConnection = GetConnection()
                      Dim l_oColumn As ADOX.Column = l_oCatalog.Tables(p_sTable).Columns(p_sColumn)
                      If l_oColumn IsNot Nothing Then
                          l_vDefault = l_oColumn.Properties("Default").Value
                      End If
                  Catch ex As Exception
                      wsUtils.Warn("wsDataProvider.GetColumnDefaultValue: " & ex.Message)
                      l_bError = True
                      l_oErrorHandler.LogError(ex)
                  End Try
              End If
          End If
          If l_bError = True Then
              l_oErrorHandler.DisplayErrorPage()
          End If
          Return l_vDefault
      End Function

  
  Maybe this is of some use to others as well.

  • Marked as answer by MdV on Dec 04, 2009 08:42 PM