none
Alter Access table to reflect dataset table RRS feed

  • Question

  • In my application I am using a typed dataset.  I have added a couple of columns to my test db (which is an access db) and have updated my dataset accordingly.  When I roll this out to my users I want to alter their db to match the dataset.

     

    Is there a known class, or an easy way, of comparing an mdb file to the appliations dataset and updating the mdb file accordingly? (I plan on writing some code so that it only compares the database files after they upgrade the application.)

     

    I just started looking into the ALTER sql and have come up with this code to add a column. 

     

    Is there a better way to acomplish this??

    Code Snippet

    Public Class cAlterTable

    Public Sub TableColumns(ByVal connstring As String, ByVal Table As DataTable, ByVal datagridview1 As DataGridView)

     

    Dim OleDbConnection1 As New System.Data.OleDb.OleDbConnection(connstring)

    OleDbConnection1.Open()

     

    Dim schemaTable As DataTable = OleDbConnection1.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, Table.TableName, Nothing})

    datagridview1.DataSource = schemaTable

     

    For Each col As DataColumn In Table.Columns

    Dim i As Boolean = False

    For Each row As DataRow In schemaTable.Rows

    If row("COLUMN_NAME") = col.ColumnName Then

    i = True

    Exit For

    End If

    Next

     

    If i = False Then

    Dim Command As System.Data.OleDb.OleDbCommand

    Command = OleDbConnection1.CreateCommand()

    Command.CommandText = "Alter Table " & Table.TableName & " Add " & col.ColumnName & " " & ColumnType(col.DataType.ToString, col.MaxLength.ToString)

    Command.ExecuteNonQuery()

    End If

    Next

    OleDbConnection1.Close()

    OleDbConnection1.Dispose()

    End Sub

     

    Private Function ColumnType(ByVal AdoDataType As String, ByVal MaxLength As String)

    Select Case AdoDataType

    Case "System.DateTime"

    Return "DateTime"

    Case "String"

    If MaxLength <= 255 Then

    Return "Text"

    Else

    Return "Memo"

    End If

    Case "System.Int16", "System.int32", "System.int64"

    Return "Number"

    Case "System.Boolean"

    Return "Yes/No"

    Case Else

    Return "Text"

    End Select

    End Function

     

     

    Monday, June 9, 2008 4:37 AM

Answers

  • In my opinion, it is not recommended to alter tables at runtime. We have two kinds of designs to choose:

     

    Design 1:

    Table Object: Id, Name, PropertyInt1, PropertyInt2, PropertyInt3, ... PropertyInt10, PropertyString1, PropertyString2, PropertyString3, ...

     

    Design 2:

     

    Table Object:  Id, Name, Basic Property1, Basic Property 2, ...

    Table Property: Id, Name, DataType, ...

    Table PropertyValue: ObjectId, PropertyId, ValueNumber, ValueText, ValueDateTime, ValueString

     

     

    For the first design, you could access the data easily but it has a limit of columns.

    For the second design, you could have infinite properties but it would be a little bit more complicated to get the value, but possible.

     

    Then you just need to maintain the data, not the columns.

    Tuesday, June 10, 2008 8:35 AM

All replies

  • In my opinion, it is not recommended to alter tables at runtime. We have two kinds of designs to choose:

     

    Design 1:

    Table Object: Id, Name, PropertyInt1, PropertyInt2, PropertyInt3, ... PropertyInt10, PropertyString1, PropertyString2, PropertyString3, ...

     

    Design 2:

     

    Table Object:  Id, Name, Basic Property1, Basic Property 2, ...

    Table Property: Id, Name, DataType, ...

    Table PropertyValue: ObjectId, PropertyId, ValueNumber, ValueText, ValueDateTime, ValueString

     

     

    For the first design, you could access the data easily but it has a limit of columns.

    For the second design, you could have infinite properties but it would be a little bit more complicated to get the value, but possible.

     

    Then you just need to maintain the data, not the columns.

    Tuesday, June 10, 2008 8:35 AM
  • Does my reply answered your question or anything else you want to know?

     

    Monday, June 16, 2008 12:09 PM