asked on

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

CmdoProg2

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

CmdoProg2

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.

nate_0000

ASKER

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.

nate_0000

ASKER

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.

CmdoProg2

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.

ASKER CERTIFIED SOLUTION

nate_0000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial