Click here to Skip to main content
15,867,704 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.20/5 (2 votes)
16 Nov 2015CPOL2 min read 11.9K   208   6   3
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.

HTML
<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:

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

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

C++
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'.

C++
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

License

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


Written By
Software Developer (Junior) ABB
United States United States
On career 2.0 mainly work in the dot net environment predominantly SQL and SSMS.

Comments and Discussions

 
QuestionNice Article Pin
DumpsterJuice18-Nov-15 1:39
DumpsterJuice18-Nov-15 1:39 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun17-Nov-15 0:12
Humayun Kabir Mamun17-Nov-15 0:12 
GeneralRe: My vote of 5 Pin
MarcusCole683317-Nov-15 2:42
professionalMarcusCole683317-Nov-15 2:42 

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.