David David - 1 year ago 41
Vb.net Question

Creating an imports program to clear a database and migrate new data

Over the last few days, I was asked to move a company program over from an Access back-end, to SQL Server.

There are 2 copies of the program, the live data version, on the server, and the local version on my PCs C: Drive, to ensure if I make a mistake, it doesn't affect the live data.

So, I managed to migrate the Access database, tables and data over to SQL Server 2008, and the local version of the program now works.

The easiest way, or so I'm informed, to now do the same to the live version of the program, is to write an imports program, which wipes all of the data from each table in the SQL Server database, and then copies over the data from the live Access database. However, I've never done this before, so I'm not really even sure where to begin.

Could anybody point me in the right direction on how to begin or do this, so that I only have to change the connection path in the program, rather than go through the whole process again?

PS, I work in vb.net, so that's the language I would need any responses in!


Answer Source


  • Step 1;

    I added a new path to the ini file for the database to read. This connected to the live database. Once this connection is open in the project, proceed to step 2.

  • Step 2;

    Create a new class, where the imports and exports will happen.

  • Step 3;

    Put a button, or some sort of control in the program to initiate the import/export. For example, I had a button which, when clicked, asked the user to confirm that they wanted to import a new database and overwrite the existing one. If yes, call the function which does this, in the newly made imports class.

  • Step 4;

    Now that you know how to get this set up, the code would be something like

    Public Function importdatabase(/connections go in here/)
    Declare transaction
    Create sql variable
      Begin the transaction here
    sql to delete the data from one table
    sql to select all data from database that is being imported
    For loop to iterate over each record in the database table
    Declare a variable for each field in the database
    variable1 = ("fieldname1")
    variable2 = ("fieldname2")
    sql statement to insert the new values
    call to the function which runs the sql query
    commit transaction
    Catch ex As Exception
    End Try
  • Step 5; Repeat the delete/insert process for each database table

Below this, I have other functions.

One function created a new datatable, this is referenced as For each dr as datarow in /functionname(parameters).Rows

Next one is to execute the sql statement (not required, any command to execute it will do)

Next one is used for parameterising my SQL query

The rest are to replace null values in the database with empty strings, set dates, etc