Extracting XML and Insert Bulk Data 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:

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.

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.");
    }
}

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.

Here are the inserted records:

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.