ObjectDataSource's and Changing Table Definitions

I'm using an ObjectDataSource to interface an edit-able DetailsView control with an MS SQL Table.  I understand the fundamentals of how this works, and have a lot of experience with using at least the select part of this method.  I'm confident that I could have update, insert & delete functions working fine too, except for one thing - The fields in this table are constantly changing.  For example, a week from now they may add a column, then a month later take one away.  Obviously changing the code to work with this every single time the table is changed would be quite frustrating and undesirable.  How would I go about making the "writing" functions change with the table definition?

Here's the function I've been experimenting with:

Public Sub nonQuery(ByVal lsQueryType As String, ByVal loDT As DataTable)
            Try
                Dim lsSql As String = ""
                Select Case lsQueryType
                    Case "insert"
                        Dim loTmpRow As DataRow
                        loTmpRow = loDT.Rows(0)
                        lsSql &= "INSERT INTO " & loDT.TableName & " ("
                        Dim loTmpCol As DataColumn
                        For Each loTmpCol In loDT.Columns
                            lsSql &= loTmpCol.ColumnName & ", "
                        Next

                        lsSql = Left(lsSql, lsSql.Length - 2)

                        lsSql &= ") VALUES ("

                        Dim i As Integer

                        For i = 1 To loTmpRow.ItemArray.Length
                            lsSql &= loTmpRow.Item(i - 1) & ", "
                        Next

                        lsSql = Left(lsSql, lsSql.Length - 2)

                        lsSql &= ");"
                    Case "update"
                        Dim loTmpRow As DataRow
                        loTmpRow = loDT.Rows(0)

                        lsSql &= "UPDATE " & loDT.TableName & " SET "

                        Dim loTmpCol As DataColumn
                        Dim j As Integer = 0
                        For Each loTmpCol In loDT.Columns
                            lsSql &= loTmpCol.ColumnName & " = '" & _
                            loTmpRow.Item(j) & "', "
                            j += 1
                        Next

                        lsSql = Left(lsSql, lsSql.Length - 2)

                        lsSql &= " WHERE vcLocationNum = '" & _
                        loTmpRow.Item("vcLocationNum") & "';"
                    Case "delete"
                        lsSql &= "DELETE FROM " & loDT.TableName & _
                        "WHERE vcLocationNum = '" & _
                        loDT.Rows(0).Item("vcLocationNum") & "'"
                End Select

                Throw New Exception(lsSql)

                loDA.ExecuteNonQuery( _
                ConfigurationManager.ConnectionStrings("EmploymentConnStr").ConnectionString, _
                CommandType.Text, _
                lsSql)
            Catch ex As Exception
                ExceptionMgr.Publish(ex, Err.Number, Err.Description, "LocationsManagerDataObj", "query")
            End Try
        End Sub
nate_0000Asked:
Who is Participating?
 
nate_0000Connect With a Mentor Author Commented:
I ended up convincing them to move the fields to an attributes table.  Problem solved.  Thanks though.
0
 
CmdoProg2Commented:
I recommend you use stringbuilder instead of string...

  Public Sub nonQuery(ByVal lsQueryType As String, ByVal loDT As DataTable)
    Try
      Dim lsSql As New StringBuilder()
      Select Case lsQueryType
        Case "insert"
          Dim loTmpRow As DataRow
          Dim i As Integer
          Dim lsValues As New StringBuilder()
          loTmpRow = loDT.Rows(0)

          lsSql.AppendFormat("INSERT INTO {0} (", loDT.TableName)
          Dim loTmpCol As DataColumn
          ' Minus 2 for zero based offset and not for the last column
          For i = 0 To loDT.Columns.Count - 2
            lsSql.AppendFormat("{0}, ", loDT.Columns(i).ColumnName)
            lsValues.AppendFormat("'{0}', ", loTmpRow.Item(i).ToString())
          Next
          ' i is now indexing the last column (.count - 1)
          lsSql.AppendFormat("{0}) VALUES (", loDT.Columns(i).ColumnName)
          lsValues.AppendFormat("{0},'{1}');", lsValues.ToString(), loTmpRow.Item(i).ToString())

        Case "update"
          Dim loTmpRow As DataRow
          loTmpRow = loDT.Rows(0)

          lsSql.AppendFormat("UPDATE {0} SET ", loDT.TableName)

          Dim loTmpCol As DataColumn
          Dim j As Integer = 0
          ' Minus 2 for zero based offset and not for the last column
          For j = 0 To loDT.Columns.Count - 2
            lsSql.AppendFormat("{0} = '{1}', ", loDT.Columns(j).ColumnName, loTmpRow.Item(j).ToString())
          Next
          ' j is now indexing the last column (.count - 1)
          lsSql.AppendFormat("{0} = '{1}' ", loDT.Columns(j).ColumnName, loTmpRow.Item(j).ToString())
          lsSql.AppendFormat(" WHERE vcLocationNum = '{0}';", loTmpRow.Item("vcLocationNum"))
        Case "delete"
          lsSql.AppendFormat("DELETE FROM {0} WHERE vcLocationNum = '{1}'", loDT.TableName, loDT.Rows(0).Item("vcLocationNum"))
      End Select


      Throw New Exception(lsSql)

      loDA.ExecuteNonQuery( _
      ConfigurationManager.ConnectionStrings("EmploymentConnStr").ConnectionString, _
      CommandType.Text, _
      lsSql)
    Catch ex As Exception
      ExceptionMgr.Publish(ex, Err.Number, Err.Description, "LocationsManagerDataObj", "query")
    End Try
  End Sub
0
 
CmdoProg2Commented:
I did not include code to prevent SQL injection attacks or SQL format errors.  A simple method is to use the replace method to replace tick marks with two tick marks. You can also build parameterized queries.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nate_0000Author Commented:
Perhaps I should have clarified.  My problem is not with the queries, my problem is with the data.  I need to get all the data from a DetailsView control when the fields are not clearly defined.
0
 
nate_0000Author Commented:
To add more clarification, I don't know the number, or the names of most fields.  I'm trying to figure out a way to do this without leaving my ObjectDataSource, but am unsure of how to do this through a custom object or through catching the DetailsView Updating event and modifying the data it contains before it gets to the data source.
0
 
CmdoProg2Commented:
Sorry, I don't know a specific solution.  I can only suggest you may want to persue using the fields collection of the DetailsView to get number and their Field type, i.e., BoundField, HyperLinkField.  For the BoundField type, you can then access the DataField property for the name of the field which you could use in either the e.NewValues or e.Keys collection.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.