65.9K
CodeProject is changing. Read more.
Home

Basic App to Insert the Contents of an XML File into a SQL Table with SQLBulkCopy

MarcusCole6833
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.20/5 (2 votes)

Nov 16, 2015

CPOL

2 min read

viewsIcon

12718

downloadIcon

231

Making XML SQL .NET style!

Introduction

In a previous article, I had turned SQL into XML using some of the basic methods available in Visual Studio, now I would like to show one how to do the opposite!

Background

I work on maintaining Business Applications and want to learn how to handle XML, this is just an illustration on how to use .NET to utilize XML.

Using the Code

The Form App consists of a Button and a DataGridview. The button just sets in motion my events. I will be taking XML formatted below and placing it into the DataGrid and the SQL Table created below.

<NewDataSet>
  <Table>
    <LName>Fellani</LName>
    <FName>Morouanne</FName>
    <Position>Center Midfield</Position>
  </Table>

Outside of the form, I need a table that receives this Data has the columns LName, FName and Position. In this case, I created like so:

CREATE TABLE [dbo].[Midfield]
(
    LName varchar(24) not null,
    FName varchar(24) not null,
    Position Varchar(16) not null
)

My first part of code was to create a DataSet to receive the XML form an XML file. The DataSet is populated by using the ReadXML XML method of the Dataset class. This method reads both the Schema and data and places the XML into the DataSet with just one parameter the filename. The Data Table .readXML requires an extra parameter, and some other tweaking. For simplicity, I used the DataSet and made the first table of the set a DataTable. On the fourth line of code, the DataTable is given a table name for usage in the SQLBulkCopy code.

 DataSet mcsc = new DataSet();
 mcsc.ReadXml( @"C:\dir\dir\dir\xmlexcelthing.xml");
 DataTable mcsc1 = mcsc.Tables[0];
 mcsc1.TableName = "Midfield"

To test the above work, I made the DataTable the DataSource for my DataGridview. On the pressing of the event Button, this should populate.

dataGridView1.DataSource = mcsc1;

The last part is the Data Insertion which consists of a few lines of C# utilizing the methods and properties of SqlBulkCopy. I have placed SQL code in a Try/Catch block to gather any errors relating to the SQL. I firstly open a SqlConnection for the localDB, then I used the SqlBulkCopy properties to map in the DataTable to the Columns to the table, after this mapping the DataTable is placed 'en masse'.

try
            {
                using (SqlConnection connex = new SqlConnection
                (@"Data Source=(LocalDB)\v11.0;Integrated Security=True;
                AttachDbFilename=c:\dir\dir\MF.mdf"))
                {
                    connex.Open();
                    using (SqlBulkCopy s = new SqlBulkCopy(connex))
                    {
                        s.DestinationTableName = mcsc1.TableName;
                        foreach (var column in mcsc1.Columns)
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
                        s.WriteToServer(mcsc1);
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString(), "SQL Error");
            }

Points of Interest

This piece is a small program to accomplish my goal of utilizing XML in .NET applications. I have made an HTML Table from XML, turned SQL into XML and now passed XML into a SQL table. I guess it is not to the scale of a George Lucas or Tolkien trilogy but Rome or New York was not built in a day!

References

History

  • 16th November, 2015: Initial version