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

Question

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!

Thanks.

Answer 1

Usually one uses the SQL Server Import and Export Wizard for this.

It's a separate tool that is installed with SQL Server Management Studio (SSMS).

Answer 2

ANSWER

• 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
  
  Try 
    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
  
  Next
  
  commit transaction
  
  Catch ex As Exception
  
  Throw
  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

Answer 3

You can use the following class to import table(s) in access to sql server.

You need: - The connection string of the source (including access file name) and the target one. - The Source Table ,target tatble (if null it is the same as the source table)

Class ImportHelper

'modify connectionstring as needed
Public Property SourceConnectionString() As String
    Get
        Return m_SourceConnectionString
    End Get
    Set
        m_SourceConnectionString = Value
    End Set
End Property
Private m_SourceConnectionString As String
Public Property DestinationConnectionString() As String
    Get
        Return m_DestinationConnectionString
    End Get
    Set
        m_DestinationConnectionString = Value
    End Set
End Property
Private m_DestinationConnectionString As String

Public Sub New(sourceConnectionString__1 As String, destinationConnectionString__2 As String)
    SourceConnectionString = sourceConnectionString__1
    DestinationConnectionString = destinationConnectionString__2
End Sub
Public Sub Import(sourceTable As String, Optional targetTable As String = Nothing)
    Using sourceConnection = New OleDbConnection(SourceConnectionString)
        If String.IsNullOrEmpty(targetTable) Then
            targetTable = sourceTable
        End If
        sourceConnection.Open()

        ' Perform an initial count on the destination table.
        Dim commandRowCount = New OleDbCommand(Convert.ToString("SELECT COUNT(*) FROM ") & sourceTable, sourceConnection)
        Dim countStart As Long = Convert.ToInt32(commandRowCount.ExecuteScalar())
        Console.WriteLine("Source Table [{0}] has {1} rows", sourceTable, countStart)

        ' Get data from the source table  
        Dim commandSourceData = New OleDbCommand(Convert.ToString("SELECT * FROM ") & sourceTable, sourceConnection)
        Dim reader = commandSourceData.ExecuteReader()

        '---------------
        Using destinationConnection As New SqlConnection(DestinationConnectionString)
            destinationConnection.Open()

            Using bulkCopy As New SqlBulkCopy(destinationConnection)
                bulkCopy.DestinationTableName = targetTable

                Try
                    ' Write from the source to the destination.
                    bulkCopy.WriteToServer(reader)
                    Console.WriteLine(Convert.ToString("Sucess Importing ") & sourceTable)
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                Finally
                    reader.Close()
                End Try
                'using
            End Using
            'using
        End Using
    End Using
    'using
 End Sub
End Class

How to use:

Private Sub Test()
    'modify connectionstring as needed
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=; //access 97..2000
    Dim SourceConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\database1.accdb;Persist Security Info=False;"
    Dim DestinationConnectionString As String = "Data Source=xxxx;Initial Catalog=test;user=xxx;password=xxx;"
    New ImportHelper(SourceConnectionString, DestinationConnectionString).Import("table1", "test1")
End Sub