VB.Net Web Applications with SQL backends

  • I'm not a VB.Net web app dev, so bear with me here, this may be a stupid question.

    Our dev group has created a web app that is updating records in the database when the user hasnt changed anything. They claim this is the way it is and it's too much work to not issue an update when nothing changed.

    Is this correct? Is there no:

    if record is new

    insert

    else

    if record is modified

    update

    end if

    end if

    Logic available?

  • I have to say it isn't really all that hard to at least keep a variable that says *something* has changed. However, IF the best practice of using a sproc for data access/modification was used, the sproc could be modified to check for actual changes and not perform an update if there isn't one. This keeps the front-end out of it. However, it would be a better practice for the front-end to actually keep track of whether something has changed, not necessarily which field, just some field.

    Does the web page generate dynamic SQL to do inserts/updates?

    CEWII

  • Thanks for the quick response.

    The application is calling a stored procedure that has an insert flag parameter. If set to true the sp does an insert, if not does an update.

    My problem is calling the sp from the front end even when nothing changes. It seems trivial to me to only call the sp when something changes.....otherwise do nothing. We're talking about a system with 8 tables and a similar amount of application windows. I'm getting A LOT of push back from the dev group over this...I dont understand why....

  • It's lazy developers. They think their time is too valuable to optimize this instead of fixing it right so that network and database resources are not wasted. A simple checksum often will determine if something has changed, and they can skip a call to the db if it's not needed.

    I'd ask to see their code, and make a judgment if this is really that hard. Or I might try to talk to one of them and get a real estimate of what the change requires in resources (time/effort). It might be pervasive in the system, in which case it's not a simple or easy change. But it can be refactored over time and fixed in pieces.

    Overall if this isn't heavily impacting the performance of the database, it's not critical, but it is something that you want to fix over time. As the server ages, data volumes go up, this will become more of an issue.

    Note this also impacts backup times and space. More logs, larger diffs, etc. It's worth fixing.

  • Agree with the comments about lazy developers, just a word of warning, i was faced with the exact same situaiton last year where the devs thought it was easier to just update everything all the time, they tested with a couple of records and it worked fine... once in production and being used for hundreds of changes per minute it was a very different story and our websites crashed big time..

  • Thanks for the feedback everyone. I suspected this isnt as big a deal as they were making it out to be, but didnt want to dig in my heels on this without some input.

    The system is small, not yet live, so now is the time to fix it. The breaking point was the audit trail. Triggers were capturing the inserts and updates, to audit tables. A solid audit trail is a requirement of the app as it could potentially be used as evidence in a trial. I refused to put code in the triggers to determine whether something changed or not - you did an update, it's getting captured, if it is screwing up the audit requirements, stop updating when you dont need to 😉

    Problem (hopefully) solved.

  • a datatable in .NET has a flag on each row that signifies whether the row has been changed;

    typically at our shop, we test a datatable,(usually from a types dataset, but it doesn't matter),

    and only if there are changes, do we perfrom the update:

    example:

    Public Function hasChanges(ByVal table As DataTable) As Boolean

    For Each row As DataRow In table.Rows

    If hasChanges(table, row) Then Return True

    Next

    Return False

    End Function

    Private Sub Somecode()

    If hasChanges(MyDataset.SomeTable) Then

    MyDataAdapter.UpdateDataTable(SomeTable)

    End If

    Public Function hasChanges(ByVal table As DataTable, ByVal row As DataRow) As Boolean

    If row.RowState <> DataRowState.Unchanged And _

    row.RowState <> DataRowState.Detached Then

    If row.RowState = DataRowState.Deleted Then

    Return True

    End If

    If row.RowState = DataRowState.Added Then

    Return True

    End If

    For Each col As DataColumn In table.Columns

    If Not (col.Caption.ToUpper.Contains("_CALC")) Then

    Select Case col.DataType.ToString

    Case "System.Int32"

    If GetInteger(row(col, DataRowVersion.Original)) <> GetInteger(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.Double"

    If GetDouble(row(col, DataRowVersion.Original)) <> GetDouble(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.Decimal"

    If GetDecimal(row(col, DataRowVersion.Original)) <> GetDecimal(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.DateTime"

    If GetDate(row(col, DataRowVersion.Original)) <> GetDate(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.String"

    Dim oldColValue As String = Trim(GetString(row(col, DataRowVersion.Original)))

    Dim newColValue As String = Trim(GetString(row(col, DataRowVersion.Current)))

    If IsRTFColumn(col.MaxLength, newColValue) Then

    If RTFColumnValueChanged(oldColValue, newColValue) Then Return True

    Else

    If oldColValue <> newColValue Then Return True

    End If

    Case Else

    If GetString(row(col, DataRowVersion.Original)) <> GetString(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    End Select

    End If

    Next

    End If

    Return False

    End Function

    Public Function hasChanges(ByVal table As DataTable, ByVal row As DataRow, ByVal FieldsToIgnore() As String) As Boolean

    If row.RowState <> DataRowState.Unchanged And _

    row.RowState <> DataRowState.Detached Then

    If row.RowState = DataRowState.Deleted Then

    Return True

    End If

    If row.RowState = DataRowState.Added Then

    For Each col As DataColumn In table.Columns

    Dim IgnoreThisColumn As Boolean = False

    For Each sFieldname As String In FieldsToIgnore

    If col.ColumnName = sFieldname Then

    'field was found

    IgnoreThisColumn = True

    End If

    Next

    If Not IgnoreThisColumn Then

    Select Case col.DataType.ToString

    Case "System.Int32"

    If GetInteger(row(col, DataRowVersion.Current)) <> 0 Then

    Return True

    End If

    Case "System.Double"

    If GetDouble(row(col, DataRowVersion.Current)) <> 0 Then

    Return True

    End If

    Case "System.Decimal"

    If GetDecimal(row(col, DataRowVersion.Current)) <> 0 Then

    Return True

    End If

    Case "System.DateTime"

    If GetDate(row(col, DataRowVersion.Current)) <> Date.MinValue Then

    Return True

    End If

    'Case "System.String"

    ' Dim oldColValue As String = ""

    ' Dim newColValue As String = Trim(GetString(row(col, DataRowVersion.Current)))

    ' If IsRTFColumn(col.MaxLength, newColValue) Then

    ' If RTFColumnValueChanged(oldColValue, newColValue) Then Return True

    ' Else

    ' If oldColValue <> newColValue Then Return True

    ' End If

    Case Else

    If GetString(row(col, DataRowVersion.Current)) <> "" Then

    Return True

    End If

    End Select

    End If

    Next

    'if it made it here, return false, as all the data was empty and undesired to save

    Return False

    End If

    For Each col As DataColumn In table.Columns

    Select Case col.DataType.ToString

    Case "System.Int32"

    If GetInteger(row(col, DataRowVersion.Original)) <> GetInteger(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.Double"

    If GetDouble(row(col, DataRowVersion.Original)) <> GetDouble(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.Decimal"

    If GetDecimal(row(col, DataRowVersion.Original)) <> GetDecimal(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.DateTime"

    If GetDate(row(col, DataRowVersion.Original)) <> GetDate(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    Case "System.String"

    Dim oldColValue As String = Trim(GetString(row(col, DataRowVersion.Original)))

    Dim newColValue As String = Trim(GetString(row(col, DataRowVersion.Current)))

    If IsRTFColumn(col.MaxLength, newColValue) Then

    If RTFColumnValueChanged(oldColValue, newColValue) Then Return True

    Else

    If oldColValue <> newColValue Then Return True

    End If

    Case Else

    If GetString(row(col, DataRowVersion.Original)) <> GetString(row(col, DataRowVersion.Current)) Then

    Return True

    End If

    End Select

    Next

    End If

    Return False

    End Function

    End Sub

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply