Click here to Skip to main content
13,800,880 members
Click here to Skip to main content
Add your own
alternative version

Stats

6.9K views
144 downloads
17 bookmarked
Posted 6 Jun 2018
Licenced CPOL

XML File to SQL Database

, 6 Jun 2018
Rate this:
Please Sign up or sign in to vote.
Dynamic creation of an SQL table by using an XML file, and importing data from an XML file to an SQL table.

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

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

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

  4. Before starting the code, we need to configure Database connection:
    Create DataBase SampleDB

    We initially created a database in SQL Server with the name of “SampleDB”.

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

  6. The next step is adding the required Namespace in code:
    using System.IO;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Xml;
  7. 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.

  8. 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();
          }
      }
    
  9. 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.

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

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

Share

About the Author

Altaf Ansari
Software Developer Prothious Engineering Services
India India
Team Leader .Net Developer, Working on Microsoft Technology Asp.Net, C#, SQL, Windows Application, Web Application.

Achievements :

13th January 2018 - Article of the Day - ASP.NET Community (SignalR Chat App With ASP.NET WebForm And BootStrap - Part One)


14th February 2018 - Article of the Day - ASP.NET Community (SignalR Chat App With ASP.NET WebForm And BootStrap - Part Two)

3rd March 2018 - Article of the Day - ASP.NET Community (SignalR Chat App With ASP.NET WebForm And BootStrap - Part Three)

You may also be interested in...

Comments and Discussions

 
QuestionAnother approach Pin
Member 1166293424-Oct-18 22:28
memberMember 1166293424-Oct-18 22:28 
QuestionJust a toy Pin
Member 122516178-Jun-18 1:40
memberMember 122516178-Jun-18 1:40 
QuestionXML Format? Pin
Vinod Jangle7-Jun-18 3:00
memberVinod Jangle7-Jun-18 3:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.181215.1 | Last Updated 6 Jun 2018
Article Copyright 2018 by Altaf Ansari
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid