programming4us
programming4us
DATABASE

Microsoft Visual Basic 2008 : Processing and Storing Data in SQL Server 2005 - Optimizing the LINQSQL Class

9/13/2012 12:53:20 AM

Reporting Processing Failures

As with the ProcessFiles method, a record might fail to be migrated into our destination table. Depending on the mechanism you use to track which records have or have not been successfully inserted into the destination table, you may not have to create any cleanup code.

For instance, if you have a flag on the source table to specify whether a record has been processed, and you fail to migrate the record, you most likely didn’t mark it as complete. If you did, it is probably a bug in your service or database implementation because record duplication should not occur. Therefore the only thing to do is log an error, because the next time you query unprocessed records, any that had failed previously will be attempted again.

The flaw here is that if a record really is broken, you will just keep querying that same record. Therefore you may want to mark records as complete with a specific status and then have an offline process monitor for any records that have been completed but completed with a failure.

Optimizing the LINQSQL Class

The LINQSQL class provides a wrapper around the Users table implementation in both the source and destination databases. Using the <InsertRecord> methods, we can migrate records into our destination database or create records in our source table.

However, look at our current implementations: We’re creating a new instance of the User class and then mapping the values from the source to the destination through the instance itself.

Recall that when we created Users.dbml you implemented the SaveUser stored procedure. The reason for that will now be clear. I want to use the stored procedure call on our LINQ To SQL implementation instead of creating a new instance of the user record, mapping the values, and then calling Commitchanges.

What’s nice is that although the code shown in Listing 1 uses the stored procedure call instead of the user mappings, the procedure implementation is exactly the same—there is no change to the calling methods.

Listing 1. New <InsertRecord> implementations.
Public Function InsertRecord(ByVal pszRecord As String) As Boolean
    Try
        Dim pszQuery As String = Nothing
        Dim pszVars() As String = Split(pszRecord, ",")

        m_UDC.SaveUser(pszVars(0), pszVars(1), _
                       pszVars(2), pszVars(3), _
                       pszVars(4), pszVars(5), _
                       pszVars(6), pszVars(7), _
                       Guid.NewGuid())
        m_UDC.SubmitChanges()

        Return True
    Catch sqlex As SqlClient.SqlException
        If sqlex.Number = 2627 Then
            Return True
        End If
    Catch ex As Exception
        Throw New Exception(ex.ToString)
    End Try
End Function

Public Function InsertRecord( _
                            ByVal First As String, _
                            ByVal Last As String, _
                            ByVal Address1 As String, _
                            ByVal Address2 As String, _
                            ByVal City As String, _
                            ByVal State As String, _
                            ByVal Zip As String, _
                            ByVal phone As String, _
                            ByVal UserID As Guid _
                            ) As Boolean
    Try
        m_UDC.SaveUser(First, Last, Address1, _
                       Address2, City, State, Zip, phone, UserID)

        m_UDC.SubmitChanges()

        Return True
    Catch sqlex As SqlClient.SqlException
        If sqlex.Number = 2627 Then
            Return True
        End If
    Catch ex As Exception
        Throw New Exception(ex.ToString)
    End Try
End Function

					  

To the calling methods there is no difference. However, within the method there is one important difference: I no longer specify the UserID because it will be created automatically when the stored procedure inserts a record into the database. This is different than creating an instance of the user class, which expects you to populate the data.

This gives less to debug, and more control over what happens to the data inserted by the stored procedure, without having to do any modifications to the method that inserts the data.

Install and Verify

Although it may seem simple in this case to migrate from one data store to another, you might find dozens of practical uses when the source is not a database. It could very well be an XML file, a flat file, a totally different data store, or any combination of these.

The ability to easily monitor for and successfully and automatically migrate this data is key to the use of a service.

Install and run the new service. Create files with some sample records, which—after you insert them into the primary database and table—will be replicated into the secondary data store.

Other  
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 4) - Direct Data Access - The DataReader
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 3) - Direct Data Access - Creating a Connection, The Select Command
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 2) - SQL Basics
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 1) - Understanding Databases, Configuring Your Database
  •  SQL Server 2008 : Failover clustering - Installing a clustered SQL Server instance
  •  SQL Server 2008 : Failover clustering - Clustering topologies and failover rules
  •  SQL Server 2008 : Failover clustering - Clustering overview
  •  Exploiting SQL Injection : Stealing the Password Hashes
  •  
    video
     
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us
    programming4us
     
     
    programming4us