Fast Development Series #1: Data Importer

The fast development series is about taking a real world project and highlighting some of the more useful features of that project and detailing how I accomplished them. You will NOT see the whole project from start to finish but snippets of the more useful bits!

Language: VB.NET

Client has expressed a preference for VB.NET due to having somebody on site who may be able to tinker with/improve the code in future.

Project Scope:

  • Imports into a specific database (ProspectSoft CRM) from any datasource.
  • Easy wizard interface so users can use.
  • A focus on spreadsheets.
  • Have the ability to save projects so imports can be saved and run multiple times.
  • While technical ability will be required to CREATE an import project any standard user should then be able to open and run the saved project.

Considerations for the Future:

  • Ideally I’d like to continue this product on to use a “modular” system, allowing the open-source community to expand the products it supports.

Libraries Used:

Introduction:

I have built the skeleton for this project which just allows the user to select an ODBC and provide a username and password and perform a test connection. The code for this is relatively simple – at the end of the project I will provide full source code. Below is how that first page looks:

First Page (New Project Wizard)

First Page (New Project Wizard)

Some of the key UI changes I have made since this screenshot was taken is showing the two groups as unexpanded initially and also the progress bars are only visible when the connection test is being run.

The configure buttons show a stylish popup window to the user allowing them to select their desired settings:

Popup Configuration Windows

Allow the user to input their ODBC details.

I have made a note in my development roadmap to expand this selection to include a FULL range of ODBC selection options, the less configuration the user has to do from within odbcad32 the better. Will not make this initial scrum though.

What am I adding this session?:

  • The code that performs the test connection.
  • How to gather schema from an ODBC connection (does not work for some connections)
  • Basic error handling
  • Asynchronous operations (using background worker).

Watch the video here of how I stumbled through the process (I blame a preference for C#.NET over VB.NET), or make use of the snippet below in any way you wish! :). Don’t hesitate to make revisions/comment me any tips or pointers! I am always looking to learn something new from a veteran.


Dim ViewCount As Integer 'Used for summary at the end
 Dim TableCount As Integer 'Used for summary at the end

'There are 3 stages to this connection test, the 1st is the initial connection.
 'For this we simply need a connection object:
 Dim DestCon As New OdbcConnection(Me.DestinationDSN & "pwd=" & Me.DestinationPassword & ";")

Try
 DestCon.Open()

Select Case DestCon.State
 Case ConnectionState.Open
 bWorkerDestinationTest.ReportProgress(WarningLights.SuccessLight, "Connection opened successfully - attempting to download schema.")
 Case ConnectionState.Closed
 bWorkerSourceTest.ReportProgress(WarningLights.ErrorLight, "Connection cannot be opened please check your ODBC settings.")
 Case Else
 bWorkerDestinationTest.ReportProgress(WarningLights.ErrorLight, "Unsupported state detected: " & DestCon.State)
 End Select

Catch ex As Exception
 e.Cancel = True

bWorkerDestinationTest.ReportProgress(WarningLights.ErrorLight, "Error detected - " & ex.Message)
 Return
 End Try

System.Threading.Thread.Sleep(2000) ' Allow user to soak in information.

'2nd stage is to attempt to gather schema from the database
 Try
 Dim TableSchema As DataTable = DestCon.GetSchema(OdbcMetaDataCollectionNames.Tables)
 TableCount = TableSchema.Rows.Count

Select Case TableCount
 Case Is > 0

bWorkerDestinationTest.ReportProgress(WarningLights.SuccessLight, TableCount & " Tables detected.")

Case Is <= 0

e.Cancel = True
 bWorkerDestinationTest.ReportProgress(WarningLights.ErrorLight, "0 Tables detected, or an unexpected error ocurred")
 Return

End Select

Catch ex As Exception
 e.Cancel = True

bWorkerDestinationTest.ReportProgress(WarningLights.ErrorLight, "Error detected - " & ex.Message)
 Return

End Try

System.Threading.Thread.Sleep(2000) ' Allow user to soak in information.

'3rd Stage is to gather the views (people may use views to do their import more efficiently)

Try
 Dim ViewSchema As DataTable = DestCon.GetSchema(OdbcMetaDataCollectionNames.Views)
 ViewCount = ViewSchema.Rows.Count

Select Case ViewCount
 Case Is > 0

bWorkerDestinationTest.ReportProgress(WarningLights.SuccessLight, ViewCount & " Views detected.")

Case Is <= 0

bWorkerDestinationTest.ReportProgress(WarningLights.CautionLight, "0 Views detected, this may be by design.")

End Select

Catch ex As Exception
 e.Cancel = True

bWorkerDestinationTest.ReportProgress(WarningLights.ErrorLight, "Error detected - " & ex.Message)
 Return

End Try
 System.Threading.Thread.Sleep(2000) ' Allow user to soak in information.
 bWorkerDestinationTest.ReportProgress(WarningLights.SuccessLight, "Table Count: " & TableCount.ToString & vbCrLf _
 & "View Count: " & ViewCount.ToString & vbCrLf _
 & "Destination database details gathered successfully")
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s