Fetching Data From XML File To SQL Database

In this article, I will share some tricks for the creation of a 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 Applications.

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 “XMLtoDatabase”.

After creating a project, we will get the 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 progress bar 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 the Database connection.

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.

<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.

// 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 create XML rows. Also, we are showing the progress of this function in the ProgressBar.

After the Generation of DataTable, we have to check whether the same table already exists in the 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, the 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 the 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 the 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 learned 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 for 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 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 comment section.