XML File to SQL Database

Altaf Ansari

3.94/5 (8 votes)

6 Jun 2018CPOL4 min read

33.3K

549

Dynamic creation of an SQL table by using an XML file, and importing data from an XML file to an SQL table.

Download XMLtoDatabase.zip - 65.1 KB

Introduction

In this article, I will share some tricks, namely, creation of an SQL table by using an XML file, and importing data from an XML file to an SQL table.

These tricks can be easily implemented in everyday coding like ‘creating a DataTable using XML file’, ‘creating an SQL table using DataTable’, ‘importing rows from DataTable’ and ‘inserting row data into the SQL table’.

There are two ways in which we can import Data from DataTable to SQL table. The first and the most common method is to insert data individually row by row; executing the query for each respective row, one at a time. The second option is to use the ‘BulkCopy’ feature of SQL. The BulkCopy feature allows us to copy all the rows present in the DataTable and add them to the SQL table at one go. We will use this feature of SQL and import data from an XML file to an SQL table.

Targeted Audiences

The targeted audience is: people with basic knowledge of C# Windows Application.

To-do List

Make an ASP.NET C# Winform application
Create a Windows Form
Add the controls
Code

Explanation

We will create a New Windows Form Application in C# and give it a suitable name.

In this example, we gave the project name as “XMLtoDatabase”.
After creating project, we will get Windows Application Form, ‘Form1’, by default in our solution explorer. Since we are creating a single form application, we do not need to create another new form.
Now, add controls to the form as shown in the below image:

In this example, we added a TextBox for XML file path, a Button to browse and select the XML file from our local drive, an OpenFileDialog component to handle the file selection, an Import Button which will perform the main functionality of our project, and a ProgressBar to show the progress rate of our application function.

After designing the form, we can start coding. We will follow some simple steps to get a better understand of our project.
Before starting the code, we need to configure Database connection:
SQL
```
Create DataBase SampleDB
```
We initially created a database in SQL Server with the name of “SampleDB”.
After creating the Database, we will add the following ConnectionString in our App.config file.
XML
```
<connectionStrings>
  <add name="strcon" connectionString="Data Source=YourSqlServerName;
   Initial Catalog=SampleDB;Integrated Security=True"
  providerName="System.Data.SqlClient" />
</connectionStrings>
```
We will refer to the same ConnectionString while writing the code for our WinForm. We also changed the Data Source (Server Name) with our local database server name.

The next step is adding the required Namespace in code:

using System.IO;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;

After adding the controls and the Namespace, we will start writing the code for each of our controls. First, we will create an event of the Browse button.
C#
```
// File Browser Button Click
private void btnBrowse_Click(object sender, EventArgs e)
{
    if (OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    txtFilePath.Text = OFD.FileName;
}
```
The above code displays the selected file with its path in the FilePath TextBox.

We will then create an Event for the Import Button, and write the code for checking the XML file location. We will generate a DataTable by using that file. Once we get the DataTable, we will assign it a name similar to the name of our XML file.

private void btnImport_Click(object sender, EventArgs e)
  {
      string XMlFile = txtFilePath.Text;
      if (File.Exists(XMlFile))
      {
          // Conversion Xml file to DataTable
          DataTable dt = CreateDataTableXML(XMlFile);
          if (dt.Columns.Count == 0)
              dt.ReadXml(XMlFile);

          // Creating Query for Table Creation
          string Query = CreateTableQuery(dt);
          SqlConnection con = new SqlConnection(StrCon);
          con.Open();

          // Deletion of Table if already Exist
          SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." +
          dt.TableName + "', 'U') IS NOT NULL DROP TABLE dbo." + dt.TableName + ";", con);
          cmd.ExecuteNonQuery();

          // Table Creation
          cmd = new SqlCommand(Query, con);
          int check = cmd.ExecuteNonQuery();
          if (check != 0)
          {
          // Copy Data from DataTable to Sql Table
          using (var bulkCopy = new SqlBulkCopy
                (con.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
          {
              // my DataTable column names match my SQL Column names,
              // so I simply made this loop.
              //However if your column names don't match,
              //just pass in which datatable name matches the SQL column name in Column Mappings
              foreach (DataColumn col in dt.Columns)
              {
                  bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
              }

              bulkCopy.BulkCopyTimeout = 600;
              bulkCopy.DestinationTableName = dt.TableName;
              bulkCopy.WriteToServer(dt);
          }

              MessageBox.Show("Table Created Successfully");
          }
          con.Close();
      }
  }

In the below function, we will generate the DataTable from the XML file by using simple XmlDocument code:

// Conversion XML file to DataTable
   public DataTable CreateDataTableXML(string XmlFile)
   {
       XmlDocument doc = new XmlDocument();

       doc.Load(XmlFile);

       DataTable Dt = new DataTable();

       try
       {
           Dt.TableName = GetTableName(XmlFile);
           XmlNode NodoEstructura = doc.DocumentElement.ChildNodes.Cast<XmlNode>().ToList()[0];
           progressBar1.Maximum = NodoEstructura.ChildNodes.Count;
           progressBar1.Value = 0;
           foreach (XmlNode columna in NodoEstructura.ChildNodes)
           {
               Dt.Columns.Add(columna.Name, typeof(String));
               Progress();
           }

           XmlNode Filas = doc.DocumentElement;
           progressBar1.Maximum = Filas.ChildNodes.Count;
           progressBar1.Value = 0;
           foreach (XmlNode Fila in Filas.ChildNodes)
           {
               List<string> Valores = Fila.ChildNodes.Cast<XmlNode>().
                                      ToList().Select(x => x.InnerText).ToList();
               Dt.Rows.Add(Valores.ToArray());
               Progress();
           }
       }
       catch (Exception ex)
       {

       }
       return Dt;
   }

In this function, we are extracting XML nodes and by using these nodes, we are structuring the DataTable. We are using XML node’s value to creating XML rows. Also, we are showing progress of this function in the ProgressBar.

After generation of DataTable, we have to check whether the same table already exists in database. If it already exists, then we will have to delete the table and re-create the same. To do this, we need the table creation query that we are getting from the following function:

// Getting Query for Table Creation
public string CreateTableQuery(DataTable table)
{
    string sqlsc = "CREATE TABLE " + table.TableName + "(";
    progressBar1.Maximum = table.Columns.Count;
    progressBar1.Value = 0;
    for (int i = 0; i < table.Columns.Count; i++)
    {
        sqlsc += "[" + table.Columns[i].ColumnName + "]";
        string columnType = table.Columns[i].DataType.ToString();
        switch (columnType)
        {
            case "System.Int32":
                sqlsc += " int ";
                break;
            case "System.Int64":
                sqlsc += " bigint ";
                break;
            case "System.Int16":
                sqlsc += " smallint";
                break;
            case "System.Byte":
                sqlsc += " tinyint";
                break;
            case "System.Decimal":
                sqlsc += " decimal ";
                break;
            case "System.DateTime":
                sqlsc += " datetime ";
                break;
            case "System.String":
            default:
                sqlsc += string.Format(" nvarchar({0}) ",
                table.Columns[i].MaxLength == -1 ? "max" :
                table.Columns[i].MaxLength.ToString());
                break;
        }
        if (table.Columns[i].AutoIncrement)
            sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() +
            "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
        if (!table.Columns[i].AllowDBNull)
            sqlsc += " NOT NULL ";
        sqlsc += ",";

        Progress();
    }
    return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
}

In the above function, we are generating a query for creating a table using a DataTable. We have then assigned the same name to our SQL table as our DataTable's name. Also, SQL table's column names and their data types are assigned according to the DataTable’s column names and data types.

After the creation of table, we will add the XML data to the SQL table. From the two options mentioned in the beginning, the preferable one is using the “BulkCopy” feature of SQL.

// Copy Data from DataTable to Sql Table
              using (var bulkCopy = new SqlBulkCopy
              (con.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
              {
                  // my DataTable column names match my SQL Column names,
                  // so I simply made this loop.
                  // However if your column names don't match, just pass in which datatable name
                  // matches the SQL column name in Column Mappings
                  foreach (DataColumn col in dt.Columns)
                  {
                      bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                  }

                  bulkCopy.BulkCopyTimeout = 600;
                  bulkCopy.DestinationTableName = dt.TableName;
                  bulkCopy.WriteToServer(dt);
              }

In Bulk Copy function, first we are checking the column name by mapping the columns and assigning the SQL table name as per the DataTable’s Table Name.

Output

The final output will appear as shown below:

Conclusion

In this article, you learnt the basic concepts of XML, DataTable, SQL Database connectivity, and Progress Bar Integration and the working of a Progress Bar.

This project can act as a sub-project or a module to any other project. You can integrate the concept within any of your projects where you are creating an SQL table dynamically by using XML files. This will erase the need to write the code manually each time. You simply have to make a library of the above code that you can implement with your project. This will give you the flexibility to easily utilize the functions and/or classes as per your requirements.

Hope this article helps you and you would like it. I have also attached the project source code, which you can download for your reference.

Thank you for reading.

Don’t forget to give your valuable feedback in the comments section.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)