Click here to Skip to main content
Click here to Skip to main content

Extracting XML and Insert Bulk Data using LINQ to XML and LINQ to SQL in .NET 3.5

, 4 Oct 2008
Rate this:
Please Sign up or sign in to vote.
Extracting XML and SQL bulk updates using LINQ to XML and LINQ to SQL in .NET 3.5.

Introduction

This article focuses on how to read XML content and perform a bulk insert to a database using LINQ to XML, LINQ to SQL, and LINQ, and discusses the significance of LINQ.

Background

Commercial applications often require bulk data from various sources. This data is commonly available in the format of XML, and it requires the data to be updated in to the application's database. Most often, these are done as bulk operations. Furthermore, the XML content needs to be read and converted into a format aligning with the application's relational structure, before performing any bulk operations. Here, we look in to a way of achieving this using LINQ.

Of course, I am not looking into thousands of records here, since using LINQ to SQL for bulk updates takes considerable time due to its internal validations and transaction handlings. Hence, this solution will have its own limitations.

As to draw a similar scenario, let us read an XML content from an RSS feed and insert the content to a table with fields: PostID, Title, and Date. Since it is for demonstration, let us stick to this simple structure. Here, I will be using a console application for clarity.

Demonstration

Through out the demo, Visual Studio 2008 and SQL Server 2005 will be used. First, let us add a LINQ to SQL Data Context to our application. This will have a connection to the database, and will contain the data model that we will be using. To create the data model, connect to your database and drag the required tables to the data model. Here is how it looks like:

- Figure 01 -

As for the source of the XML, let me use this blog’s RSS feed. Here is the URL: http://weblogs.asp.net/scottgu/rss.aspx.

figure02.JPG

- Figure 02 -

Now, let us write some code that will read the RSS feed and write the content to a table named RssFeed in the database, and here is the code:

class Program
{

    static void Main(string[] args)
    {
        WriteRssToDb();
        Console.ReadLine();
    }

    private static void WriteRssToDb()
    {
        XDocument feed = XDocument.Load("http://weblogs.asp.net/scottgu/rss.aspx");
        SampleGayaniDataContext dbContext = new SampleGayaniDataContext();
        var feedpost = from post in feed.Descendants("item")

        select new RssFeed
        {
            Tile = post.Element("title").Value,
            Date = DateTime.Parse(post.Element("pubDate").Value)
        };

        dbContext.RssFeeds.InsertAllOnSubmit(feedpost);
        dbContext.SubmitChanges();
        Console.WriteLine("Successfully inserted, Check your database.");
    }
}
- Figure 03 -

The XDocument.Load method loads the XML content from the specified URL to a variable of type XDocument that holds an XML document in memory. Next, a reference is made to the data context, a database context of type SampleGayaniDataContext. In the LINQ query, the feed.Descendants("item") method accesses the collection of items in the XML document. By using select new RssFeed, it creates a new instance of the RssFeed class and assigns the values of the post instance to the properties of the new instance of RssFeed. Now, the extracted XML content is fed into the RssFeed object collection, and by the implicit type variable feedpost, it holds a collection of RssFeed objects.

The InsertAllOnSubmit method accepts the collection of RssFeed objects, it inserts the new records to the data context. The SubmitChanges method will commit the changes made to the data context to the database.

Running the application results in this notification, so let us check the database for the inserted records.

figure04.JPG

- Figure 04 -

Here are the inserted records:

figure05.JPG

- Figure 05 -

Summary

As seen above, the .NET Framework 3.5’s new LINQ, LINQ to SQL, and LINQ to XML are enormously powerful techniques in terms of manipulating data sources. In the above context, LINQ to SQL provides a ground to perform relational operations on the SQL Server database. It is rich enough with all the necessary operations such as insertions, updates, deletions. Plus, it validates the types, handles transactions automatically, and provides a mechanism to notify in case of concurrency violations as well.

Looking into LINQ to XML, it provides ways and means of queering and manipulating XML content. Almost all the classes belonging to LINQ to XML could be found in System.Xml.Linq.

LINQ is the most powerful rooted mechanism in .NET 3.5 and lets you query from multiple data sources. In this example, I have used an XML source as well as a relational data source in one query to extract the data in the required format.

As mentioned earlier, this solution will obviously have limitation as to the number of records that the bulk operation will support without degrading the application performance. But, due to its internal validations and transaction support, we could assure the accuracy of the operations. Overall, LINQ is very impressive in bringing an effective type safe querying plus clean code. This handy solution was possible thanks to LINQ, and in .NET Framework 3.5, it’s too good to be true.

License

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

About the Author

Gayani Devapriya
Web Developer Teranet
Sri Lanka Sri Lanka
I’m a Sri Lankan Software Engineer from a C# background and I am involved in Web Application Development in ASP.NET, AJAX and DNN. DotNetNuke is one of my favorites. In my spare time I enjoy watching nature and cooking.
My Blog: http://dilrukshidevapriya.blogspot.com

Comments and Discussions

 
QuestionMySQL Pinmembersyamimi15-Nov-11 23:19 
GeneralMy vote of 1 PinmemberMember 363359627-Dec-09 22:12 
Questionnot really bulk insert... Pinmemberamit.la7-Oct-08 2:13 
AnswerRe: not really bulk insert... PinmemberGünther M. FOIDL9-Oct-08 22:36 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140718.1 | Last Updated 4 Oct 2008
Article Copyright 2008 by Gayani Devapriya
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid