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!
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.
- 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.
- WPFToolkit (http://wpftoolkit.codeplex.com/)
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:
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:
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")