LINQ to XML Instead of a Database

By Paul Sheriff
Published in: : 2012 - September/October
Last updated: February 20, 2019

Download File

Download Code

Download File

Download Code

Published in:

Filed under:

When people think of having to store data for their applications, a database such as SQL Server immediately comes to mind. However, XML files are very handy for storing data without the overhead of a database. Using XML files to cache often-used, but seldom changed data such as US state codes, employee types and other validation tables can avoid network roundtrips and speed up your application. In addition, XML files are great for off-line applications where a user needs to add, edit and delete data when they can’t connect to a database.

To take advantage of XML within your application, you should use LINQ to XML. I am sure you have used LINQ to SQL and LINQ to iterate over other types of collections. LINQ works just as effectively over XML, too. In this article, you will get an introduction to using LINQ to read, insert, update and delete data from an XML file. You will also see how to use isolated storage to store your XML files.

The XML Data for this Article

An XML data file called Product.xml is located in the \Xml folder of the Silverlight sample project for this article. This XML file contains several rows of product data that will be used in each of the samples for this article. Each row has four attributes: ProductId, ProductName, IntroductionDate and Price.

<Products>
  <Product ProductId="1"
   ProductName="Haystack Code Generator for .NET"
   IntroductionDate="07/01/2010" Price="799" />
  <Product ProductId="2"
   ProductName="ASP.Net Jumpstart Samples"
   IntroductionDate="05/24/2005" Price="0" />
  ...
  ...
</Products>

The Product Class

Just as you create an Entity class to map each column in a table to a property in a class, you should do the same for an XML file, too. In this case, you will create a Product class with properties for each of the attributes in each element of product data. Listing 1 shows the Product class.

Just as you create an Entity class to map each column in a table to a property in a class, you should do the same for an XML file.

The CommonBase Class

Notice that each property set in the Product class calls a method named RaisePropertyChanged. This method is created in a class called CommonBase. This method will raise the PropertyChanged event to inform your XAML UI that a particular property has changed value. The XAML UI can then update the data on the screen automatically. Listing 2 shows the implementation of the CommonBase class.

Reading Data

One of the first things you will want to do with your XML file is read the data from the Product.xml file using LINQ to XML. After reading the data, you will display that data in a ListBox on a Silverlight user control. If you download the sample application that comes with this article you will be able to follow along. Create a list box to display the data from the Product XML file that looks like the following:

<ListBox ItemsSource="{Binding}"
  ItemTemplate="{StaticResource ProductTemplate}"
  Name="lstData" />

You will find the ItemTemplate in the App.Xaml file so it can be reused on each of the user controls in this application. The ItemsSource is set to "{Binding}" so it knows that you will be setting the DataContext for the data at runtime. Next, it is time to write the code to retrieve the data. You want to add a reference to the System.Xml.Linq.dll and import a couple of namespaces at the top of your user control.

using System.Linq;
using System.Xml.Linq;

In the UserControl_Loaded event procedure you will call a procedure named LoadProducts to create a collection of Product objects.

private void UserControl_Loaded(
  object sender, RoutedEventArgs e)
{
  LoadProducts();
}

In the LoadProducts procedure, you will use the Load method of the XElement class to load the XML file. Once the XML file has been loaded, you write a LINQ query to iterate over the Product descendants in the XML file. In this query you use an orderby clause to order each element by ProductName.

The select portion of the LINQ query creates a new Product object for each row in the XML file. You retrieve each attribute by passing each attribute name to the Attribute() method and retrieving the data from the Value property. The Value property will return either a null if there is no data, or will return the string value of the attribute. The Convert class is used to convert the value retrieved into the appropriate data type required by the Product class.

NOTE: There is a BIG problem with the above code (illustrated in Listing 3)! If you have any missing attributes in any of the rows in the XML file, or if the data in the ProductId or IntroductionDate is not of the appropriate type, then this code will fail! The reason? There is no built-in check to ensure that the correct type of data is contained in the XML file. You will learn how to deal with this problem in the next section.

Converting XML Values Using Extension Methods

Instead of using the Convert class to perform type conversions, you might want to create a set of extension methods that you can attach to the XAttribute class. These extension methods will perform null-checking and ensure that a valid value is passed back instead of an exception being thrown if there is invalid data in your XML file. Listing 4 shows the modified LoadProducts method using extension methods.

Create extension methods to perform null-checking and valid values when using LINQ to XML to read XML data.

Adding Extension Methods

To create an extension method you will create a class with any name you like. Listing 5 shows a class named XmlExtensionMethods. This listing just shows a couple of the available methods such as GetAsString and GetAsInt32. These methods are just like any other method you would write except when you pass in the parameter you prefix the type with the keyword this, which lets the compiler know that it should add this method to the class specified in the parameter.

Each of the methods in the XmlExtensionMethods class should inspect the XAttribute to ensure it is not null and that the value in the attribute is not null. If the value is null, then a default value will be returned such as an empty string or a 0 for a numeric value.

Working with Isolated Storage

When working with any .NET application you have the option of storing any data you want in a file located in a special folder on the hard drive. This special folder is unique for each application and user. Using isolated storage ensures that each application/user combination has a unique location, and any files placed there will not interfere with any other user files. In order to use isolated storage in your .NET application, you will add a “using” statement to the top of your file.

using System.IO.IsolatedStorage;

In the samples for this article you will create an entry into isolated storage using key/value pairs as shown in the following code fragment:

IsolatedStorageSettings.
  ApplicationSettings["Name"] = "Bruce";

This will automatically add a key called “Name” and set the value “Bruce” into that location. Using the key/value pair approach is nice because it allows you to check to see if a value exists prior to accessing it. To check if a key exists you use the Contains method on the ApplicationSettings property of the IsolatedStorageSettings class.

You can create a method named GetProductXml as shown in Listing 6 to either open the stored XML file from isolated storage or from the \Xml folder in your application. If the key name does not exist in isolated storage, you can open the file from the \Xml folder and store it into isolated storage so that the next time you call this method, it will retrieve the XML file from isolated storage. Placing the XML file into isolated storage will allow you to insert, update and delete data from that XML file. Notice that you are using a public constant from the Product class for the key name.

If you will be updating data in your XML file in isolated storage, you will first need to read the complete XML file into memory, make any changes, and then store that data back to isolated storage. You might want to create a SaveProductXml method that you can pass in an XElement object to. This method will replace the data with the data from the XElement object you pass in.

private void SaveProductXml(XElement xElem)
{
  // Create/Modify data in Isolated Storage
  IsolatedStorageSettings.
    ApplicationSettings[Product.KeyName] =
      xElem.ToString();
}

Inserting Data

Now that you have learned how to read data using LINQ to XML and how to store the data read into isolated storage, you can now learn to add new data to your collection. Inserting data does not use LINQ to XML; it uses the XElement and XAttribute classes to create a new element you add to the XElement object that contains your complete XML file. Listing 7 shows how to create a new Product object and use that new object to create a new XML element with the appropriate attributes to add to your XML file stored in isolated storage.

Updating Data

To update data in your XML file, you will first use LINQ to XML to locate the specific element you want to modify. Once you have retrieved that element, you can modify the data by setting the Value property of the individual attribute objects in that object. Listing 8 contains the code to show you how to locate a Product object with a specified ProductId and modify the data within an existing XElement object. Notice that you must call the SaveProductXml method in order to save these changes back to the XML file.

Deleting Data

Deleting data is very similar to updating as you will see in Listing 9. You first must locate the individual product element you are interested in deleting. Call the Remove method on that found XElement object. When you call the SaveProductXml method, this saves the new XML file with this row removed.

Getting XML from the Server

So far, you have learned how to read, insert, update and delete data from an XML file using the XElement class and LINQ to XML. In the sample project, you already had a Product.xml file that was contained in your project. However, sometimes you may wish to retrieve data from a database server and then store that data as XML in isolated storage. This is useful if you have a salesperson that will be downloading data prior to going out on the road where they may have weak or only intermittent connectivity.

To accomplish this, you will need to create a WCF Service, or you could use the new Web API to return some XML. In the sample project for this article you will find a WCF Service that retrieves data from a Product table in a SQL Server database and converts that data to an XML string.

The GetProductXml method shown in Listing 10 uses a SqlDataAdapter and DataSet to retrieve data from a Product table. Once the DataSet is loaded into memory, loop through each column and set the ColumnMapping property to the value “MappingType.Attribute”. Setting this property makes the XML attribute-based as opposed to element-based. Attribute-based XML will make the data transferred across the wire a little smaller.

Set the DataSetName property to the top-level element name you want to assign to the XML, in this case “Products”. Set the TableName property on the DataTable to the name you want each element to be in your XML, in this case also “Product”. The last thing you do is call the GetXml() method on the DataSet object, which will return an XML string of the data in your DataSet object. This string is what you return from the service call.

The GetProductXml() method uses a connection string from the Web.Config file, so you need to add a <connectionStrings> element to the Web.Config file in your Silverlight web project or your WCF Service project. Modify the settings shown below as needed for your server and database name.

<connectionStrings>
  <add name="Sandbox"
       connectionString="Server=Localhost;
             Database=Sandbox;
             Integrated Security=Yes"/>
</connectionStrings>

Consume and Store XML

In your Silverlight application you add a Service Reference to the WCF Service you created. Now you can write code (Listing 11) to call the WCF Service and store the returned XML string into isolated storage.

As you can see in Listing 11, this is a fairly standard call to a WCF Service. In the Completed event you get the Result property from the event argument. The Result property is the XML. You store the XML into Isolated Storage using the IsolatedStorageSettings.ApplicationSettings class. The key for the isolated storage comes from the Product class. After you have stored the XML you can now read and modify the XML in isolated storage without having an Internet connection.

More Samples

In the sample that you download with this article there are two more samples that I did not have enough room to cover in this article. The first shows you how to add a where clause to the LINQ to XML to read just a portion of the data in the XML file. The second sample is a simple add, edit and delete Silverlight user control to show you how to use the techniques discussed in this article all together. Figure 1 shows you how this sample looks.

Figure 1: A simple add, edit, delete screen showing all the techniques discussed in this article.

Summary

XML files are a great way to store data in Silverlight, WPF, Windows Forms and even ASP.NET applications. I find XML files to be especially useful for prototyping applications. I can quickly create XML files and show a user data in their application without having to worry about connections to databases or creating services. XML files work great for offline storage too, as shown in this article. LINQ to XML is a great way to retrieve data and you have the full LINQ language to work with when using XML. I have used LINQ to XML to work with XML files up to 6000 rows of data and have seen a second or less response time. I am sure you will find a lot of uses for XML in your applications.

Listing 1: The Product class contains one property for each attribute in the Product.xml file

public class Product : CommonBase
{
  public const string XmlFile = @"Xml/Product.xml";
  public const string KeyName = "ProductData";
  public const string TopElement = "Product";
      
  private string _ProductName;
  private int _ProductId;
  private DateTime _IntroductionDate;
  private decimal _Price;
      
  public string ProductName
  {
    get { return _ProductName; }
    set {
      if (_ProductName != value) {
        _ProductName = value;
        RaisePropertyChanged("ProductName");
      }
    }
  }
      
  public int ProductId
  {
    get { return _ProductId; }
    set {
      if (_ProductId != value) {
        _ProductId = value;
        RaisePropertyChanged("ProductId");
      }
    }
  }
      
  public DateTime IntroductionDate
  {
    get { return _IntroductionDate; }
    set {
      if (_IntroductionDate != value) {
        _IntroductionDate = value;
        RaisePropertyChanged("IntroductionDate");
      }
    }
  }
      
  public decimal Price
  {
    get { return _Price; }
    set {
      if (_Price != value) {
        _Price = value;
        RaisePropertyChanged("Price");
      }
    }
  }
}

Listing 2: You should always create a base class that implements the INotifyPropertyChanged interface when working with XAML applications

public class CommonBase : INotifyPropertyChanged
{
  public event PropertyChangedEventHandler PropertyChanged;
      
  protected void RaisePropertyChanged(string propertyName)
  {
    PropertyChangedEventHandler handler = this.PropertyChanged;
    if (handler != null)
    {
      PropertyChangedEventArgs args =
         new PropertyChangedEventArgs(propertyName);
      
      // Raise the PropertyChanged event.
      handler(this, args);
    }
  }
}

Listing 3: You can use LINQ to read XML files just like calling SQL or iterating over any other .NET collection

private void LoadProducts()
{
  XElement xElem = null;
      
  try
  {
    xElem = XElement.Load(Product.XmlFile);
      
    // The following will NOT work if you have missing attributes
    var products =
        from elem in xElem.Descendants("Product")
        orderby elem.Attribute("ProductName").Value
        select new Product
        {
          ProductId = Convert.ToInt32(
            elem.Attribute("ProductId").Value),
          ProductName = Convert.ToString(
            elem.Attribute("ProductName").Value),
          IntroductionDate = Convert.ToDateTime(
            elem.Attribute("IntroductionDate").Value),
          Price = Convert.ToDecimal(elem.Attribute("Price").Value)
        };
      
    lstData.DataContext = products;
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
}

Listing 4: Use extension methods instead of the Convert class to read data from your XML files

private void LoadProducts()
{
  var xElem = XElement.Load(Product.XmlFile);
      
  var products =
      from elem in xElem.Descendants("Product")
      orderby elem.Attribute("ProductName").Value
      select new Product
      {
        ProductId = elem.Attribute("ProductId").GetAsInt32(),
        ProductName = elem.Attribute("ProductName").GetAsString(),
        IntroductionDate =
           elem.Attribute("IntroductionDate").GetAsDateTime(),
        Price = elem.Attribute("Price").GetAsDecimal()
      };
      
  lstData.DataContext = products;
}

Listing 5: Adding extension methods is a great way to check for errors before they happen, and to add additional functionality to almost any .NET class

public static class XmlExtensionMethods
{
  public static string GetAsString(this XAttribute attr)
  {
    string ret = string.Empty;
      
    if (attr != null && !string.IsNullOrEmpty(attr.Value))
    {
      ret = attr.Value;
    }
      
    return ret;
  }
      
  public static int GetAsInt32(this XAttribute attr)
  {
    int ret = 0;
    int value = 0;
      
    if (attr != null && !string.IsNullOrEmpty(attr.Value))
    {
      if(int.TryParse(attr.Value, out value))
        ret = value;
    }
      
    return ret;
  }
      
  ...
  ...
}

Listing 6: Create a method to read XML from isolated storage if it exists, or grab an existing XML file from the application and store it into isolated storage

private XElement GetProductXml()
{
  XElement xElem = null;
      
  if (IsolatedStorageSettings.ApplicationSettings.
        Contains(Product.KeyName))
    xElem = XElement.Parse(IsolatedStorageSettings.
        ApplicationSettings[Product.KeyName].ToString());
  else
  {
    xElem = XElement.Load(Product.XmlFile);
      
    // Store XML into Isolated Storage
    IsolatedStorageSettings.ApplicationSettings[Product.KeyName] =
        xElem.ToString();
  }
      
  return xElem;
}

Listing 7: Use the XElement and XAttribute classes to create a new row for your Product XML file

private void btnInsert_Click(object sender, RoutedEventArgs e)
{
  Product entity = new Product();
      
  entity.ProductId = 50;
  entity.ProductName = "My new Product";
  entity.IntroductionDate = DateTime.Now;
  entity.Price = 30;
      
  Insert(entity);
}
      
public void Insert(Product entity)
{
  XElement xElem = GetProductXml();
      
  // Create new Product element
  var newElem = new XElement(Product.TopElement,
    new XAttribute("ProductId", entity.ProductId),
    new XAttribute("ProductName", entity.ProductName),
    new XAttribute("IntroductionDate", entity.IntroductionDate),
    new XAttribute("Price", entity.Price));
      
  // Add to element collection
  xElem.Add(newElem);
      
  // Save XML File
  SaveProductXml(xElem);
      
  tbMessage.Text = "Product Inserted";
}

Listing 8: Locate an existing element using LINQ to XML, then modify the individual attributes to update data in your XML file

private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
  Product entity = new Product();
      
  entity.ProductId = 50;
  entity.ProductName = "Changed My New Product";
  entity.IntroductionDate = DateTime.Now;
  entity.Price = 35;
      
  Update(entity);
}
      
private void Update(Product entity)
{
  XElement xElem = GetProductXml();
  XElement old = null;
      
  // Find the product element
  old = (from elem in xElem.Descendants(Product.TopElement)
         where elem.Attribute("ProductId").Value ==
            entity.ProductId.ToString()
         select elem).SingleOrDefault();
      
  if (old != null)
  {
    // Update the data
    old.Attribute("ProductName").Value = entity.ProductName;
    old.Attribute("IntroductionDate").Value =
       entity.IntroductionDate.ToString();
    old.Attribute("Price").Value = entity.Price.ToString();
      
    // Save XML File
    SaveProductXml(xElem);
      
    tbMessage.Text = "Product Updated";
  }
  else
  {
    tbMessage.Text = "Product 50 NOT Found";
  }
}

Listing 9: Call the Remove method after locating an element to delete and save the XML to eliminate the element from the file

private void btnDelete_Click(object sender, RoutedEventArgs e)
{
  Product entity = new Product();
      
  entity.ProductId = 50;
      
  Delete(entity);
}
      
private void Delete(Product entity)
{
  XElement xElem = GetProductXml();
  XElement old = null;
      
  // Find the product element
  old = (from elem in xElem.Descendants(Product.TopElement)
         where elem.Attribute("ProductId").Value ==
            entity.ProductId.ToString()
         select elem).SingleOrDefault();
      
  if (old != null)
  {
    // Delete the element
    old.Remove();
      
    // Save XML File
    SaveProductXml(xElem);
      
    tbMessage.Text = "Product Deleted";
  }
  else
  {
    tbMessage.Text = "Product 50 NOT Found";
  }
}

Listing 10: Create a web service to return a XML string from a table in a SQL Server database

public string GetProductXml()
{
  string ret = string.Empty;
  string sql = string.Empty;
  SqlDataAdapter da;
  DataSet ds = new DataSet();
      
  sql = "SELECT ProductId, ProductName,";
  sql += " IntroductionDate, Price";
  sql += " FROM Product";
      
  da = new SqlDataAdapter(sql, ConfigurationManager.
            ConnectionStrings["Sandbox"].ConnectionString);
      
  da.Fill(ds);
      
  // Create Attribute based XML
  foreach (DataColumn col in ds.Tables[0].Columns)
  {
    col.ColumnMapping = MappingType.Attribute;
  }
      
  ds.DataSetName = "Products";
  ds.Tables[0].TableName = "Product";
  ret = ds.GetXml();
      
  return ret;
}

Listing 11: Call a WCF Service to retrieve XML from a database and store into isolated storage

ProductServiceClient _Client = null;
      
private void GetXmlFromServer()
{
  _Client = new ProductServiceClient();
      
  _Client.GetProductXmlCompleted += new
      EventHandler<GetProductXmlCompletedEventArgs>
         (_Client_GetProductXmlCompleted);
  _Client.GetProductXmlAsync();
}
      
void _Client_GetProductXmlCompleted(object sender,
 GetProductXmlCompletedEventArgs e)
{
  // Store the XML data into Isolated Storage
  IsolatedStorageSettings.ApplicationSettings[Product.KeyName] =
      e.Result;
      
  _Client.CloseAsync();
}

This article was filed under:

This article was published in:

Have additional technical questions?

Get help from the experts at CODE Magazine - sign up for our free hour of consulting!

Contact CODE Consulting at techhelp@codemag.com.