?
Solved

ObjectDataSource's and Changing Table Definitions

Posted on 2007-07-31
6
Medium Priority
?
197 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:nate_0000
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 19602659
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
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 19603907
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
 

Author Comment

by:nate_0000
ID: 19608167
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:nate_0000
ID: 19616772
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
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 19617650
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
 

Accepted Solution

by:
nate_0000 earned 0 total points
ID: 22152162
I ended up convincing them to move the fields to an attributes table.  Problem solved.  Thanks though.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question