Tutorials

The majority problems which a web / software developer has to face every day, are often tied to monotonous and ricursive operations, which must be repeated an undeterminated number N of times for as many X customers. One of this operations result to be tied to import and migration procedures given by an origin database to one of destination. The most eviden example of such application, is that in which a client holder of a site website / software developed to the times of the Carousel, decides a beautiful day to wake up him and to sustain to the footstep with the technology aiming at more database performantis and professional of their darling and unfortunately by now obsolete MS Access. The question now rises spontaneous: "As to be able to reach such objective in the best of the ways, using the least one some available resources and reentering in the anticipated brief times of development?". In this Tutorial you will find an exhaustive explanation and detailed on as to effect this operation in few and simple steps.

Before starting with the operations of export / importation, we give a glance to the table that we want to migrate from our MS Access Database of origin studying his structure and the fields of which this results to be composed of it.
This Tutorial will for instance take a hypothetical table that it contains the registry Users structured as it follows.

As you can see by the image, the table from us in analysis doesn't have a particularly complex structure, this is composed in fact from about ten employed fields to identify every single user in our Database through the most classical information like name, last name, born date, etc. and from the access parameters such username and password.

To reach the purpose we have to exactly know how to proceed and what passages must have effected.
The solution that this Tutorial proposes, is divided by 3 simple steps:

• Conversion of the table "Users" of the origin MS Access Database into XML flow.
• Creation of the XML scheme of mapping.
• Importation of the XML flow previously produced, inside the contained table in our SQL Server destination Database through XML BULK LOAD COM object, using it mapping scheme opportunely realized in the previous step.

Well, now we have to follow this process order, so let's take a deep look to every single step we have to pass through.

Finally has arrive the moment to get into the Tutorial for real!
Before everything, we have to include all the necessary libraries needed to let our page correctly work.

Including all the necessary libraries

   1:  using System.Data;

   2:  using System.Data.OleDb;

   3:  using System.Xml;

   1:  Imports System.Data

   2:  Imports System.Data.OleDb

   3:  Imports System.Xml

Now, the first operation do do in to estabilish a connection with our origin MS Access database that we will suppose to be located in "C:\Database\UsersDatabase.mdb". To do this we have to proceed as follow:

Connecting with the origin MS Access database

   1:  //Definition of the object able to establish a connection with the ACCESS database

   2:  OleDbConnection connection = new OleDbConnection();

   3:  //Connection string setup

   4:  connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Database\\DatabaseCliente.mdb";

   5:  //Estabilishing a connection to the ACCESS database

   6:  connection.Open();

   1:  'Definition of the object able to establish a connection with the ACCESS database

   2:  Dim connection As New OleDbConnection

   3:  'Connection string setup

   4:  connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\DatabaseCliente.mdb"

   5:  'Estabilishing a connection to the ACCESS database

   6:  connection.Open()

Now that we have estabilish an active connection with our origin database, we have to export all the records that it contains into an XML flow. I assure you that this operation is most easy to do that to say it. Don't you belive it? Let's take a look at the code below and i'm sure you will agree with me.

Generating the XML flow document

   1:  //Retriving all the records from Users table

   2:  string sql = "SELECT * FROM Utenti ORDER BY id ASC";

   3:  //Executing the query and filling a new DataAdapter

   4:  OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);

   5:  //Filling a DataSet with all the data conteined inside the DataAdapter

   6:  DataSet dataset = new DataSet();

   7:  adapter.Fill(dataset);

   8:  //Assigning the root node name of the XML document that will be generated

   9:  dataset.DataSetName = "ListaUtenti";

  10:  //Setting up the root node name of every single record

  11:  dataset.Tables[0].TableName = "Utente";

  12:  //Generating the XML document retriving the data from the DataSet previusly filled

  13:  XmlDocument xmlDoc = new XmlDataDocument(dataset);

  14:  //Saving the XML flow Document, filled with all the reccords contained by the Users ACCESS table

  15:  xmlDoc.Save(Server.MapPath("dati/Utenti.xml"));

   1:  'Retriving all the records from Users table

   2:  Dim sql As String = "SELECT * FROM Utenti ORDER BY id ASC"

   3:  'Executing the query and filling a new DataAdapter

   4:  Dim adapter As New OleDbDataAdapter(sql, connection)

   5:  'Filling a DataSet with all the data conteined inside the DataAdapter

   6:  Dim dataset As New DataSet

   7:  adapter.Fill(dataset)

   8:  'Assigning the root node name of the XML document that will be generated

   9:  dataset.DataSetName = "ListaUtenti"

  10:  'Setting up the root node name of every single record

  11:  dataset.Tables(0).TableName = "Utente"

  12:  'Generating the XML document retriving the data from the DataSet previusly filled

  13:  Dim xmlDoc As New XmlDataDocument(dataset)

  14:  'Saving the XML flow Document, filled with all the reccords contained by the Users ACCESS table

  15:  xmlDoc.Save(Server.MapPath("dati/Utenti.xml"))

Reached this point, we will find inside the "flows" directory our produced Users.xml document, filled with all the records the origin table contains. As you surely notice, this script request just few seconds to generate the xml flow.