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.
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:
Date. Since it is for demonstration, let us stick to this simple structure. Here, I will be using a console application for clarity.
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.
- 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:
static void Main(string args)
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)
Console.WriteLine("Successfully inserted, Check your database.");
- Figure 03 -
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
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.
- Figure 04 -
Here are the inserted records:
- Figure 05 -
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
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.