Technorati Tags: ,,

In Part 1,  we talked about creating LINQ objects  and getting data.  This time, we are going to show how to use XML with LINQ to test a SQL database.  This part is really not difficult, we are just going to cover the basics of getting started. I have made the conscious decision that I am going to use a SQL database during my development process.  You may decide that you want to do use a different methodology.  These are some of your options:

Step 1: Generate Data
First, you want to create some test data.  Since, I did not really wanted to do this by hand I found a site www.generatedata.com, that has an online utility for generating data.  The best part was the different formats that it supports.

Step 2: Create Unit Test Projectimage
Now that I have data, I created a Unit Test project and now I am ready to really get some  work done.  In this project, I created a "Data" directory and put my generated XML from Step 1.  To the right is an example of what my Unit Testing file structure currently looks like.

Step 3: Create Helper
The next thing we want to do is create a helper that will be used to populate XML data to SQL. Next, lets create a class, and name it CustomerHelper.  In my class, I made the following adjustments.

  • References
    • Business Library where my model LINQ objects are held.
    • System.Linq
    •  System.Xml.Linq
  • Make the class static. (See below sample)

Next, we need to add some constants to hold the XML and resulting collections that will get loaded.  Mine looks like the following:

    public static class CustomerHelper
    {
        static XDocument xmlCustomers;
        static List<Customer> customers;
 
        public static List<Customer> Customers
        {
            get { return customers; }
        }
     }
 

Next we need to automatically load the data in the constructor.

            xmlCustomers = XDocument.Load("Customers.xml");
            customers = (from c in xmlCustomers.Descendants("Customer")
                         select new Customer
                         {
                             FirstName = (string)c.Element("FirstName"),
                             LastName = (string)c.Element("LastName"),
                             MiddleName = string.Empty,
                             Apartment = string.Empty,
                             Address = (string)c.Element("Address"),
                             City = (string)c.Element("City"),
                             Country = "USA",
                             Email = (string)c.Element("Email"),
                             PostalCode = (string)c.Element("PostalCode"),
                             State = (string)c.Element("State"),
                             Telephone = (string)c.Element("Telephone"),
                         }
                     ).ToList();

So some explaining, need to be done about what is happening here.  First we are loading the XML file into an XDocument.  That XDocument will then act just like SQL where we can query against it like any other data.  LINQ looks at the object just like it would for SQL except there are some additional properties and methods.  An example of the data is below. We query the data and turn it into LINQ SQL objects.  The query is doing the following:

  1. Query the xmlCustomer object looking for the "Customer" nodes.
  2. In each customer node, we want to take the selected elements and populate them into our Customer object.
  3. After building the query, execute the code now and return a list of Customers.
 <Customers>
   <Customer>
     <FirstName>Bevis</FirstName> 
     <LastName>Dalton</LastName> 
     <Address>5394 Dis Ave</Address> 
     <City>Tamuning</City> 
     <State>Wisconsin</State> 
     <PostalCode>04338</PostalCode> 
     <Telephone>(772) 462-2385</Telephone> 
     <Email>lorem.luctus.ut@elitEtiam.ca</Email> 
   </Customer>
 </Customers>

If we try and run this now, it will not work because we need to do a little bit of plumbing first.

Step 4: Start Testing

After creating my unit test file, I add a reference to my LINQ Database Context, which in my case is called InventoryDBDataContext.

        InventoryDBDataContext db;
In my Test Initialize I create an instance of my database context, 
so each test will have a new one.
        [TestInitialize()]
        public void MyTestInitialize()
        {
            db = InventoryDBDataContext.Create();
        }
Now I just want to do a simple test to make sure that I can get 
data from XML and into SQL.
        [TestMethod]
        [DeploymentItem(@"\Data\")]
        public void ShouldCreateAGroupOfCustomers()
        {
            ShouldDeleteAllCustomers();
            foreach (Customer cust in CustomerHelper.Customers)
            {
                db.Customers.InsertOnSubmit(cust);
                db.SubmitChanges();
            }
            Assert.IsTrue(db.Customers.Count() >= 5);
        }

        public void ShouldDeleteAllCustomers()
        {
            foreach (Customer c in db.Customers)
            {
                db.Customers.DeleteOnSubmit(c);
            }
            db.SubmitChanges();
            Assert.IsTrue(db.Customers.Count() == 0);
            db.ExecuteCommand("DBCC CHECKIDENT ('Customer', RESEED, 0)");
        }

The key to this whole process is the [DeploymentItem(@"\Data\"] 
attribute. This takes the XML files and makes sure that they
are deployed with the rest of the files. The other key is
that I want to clean up my SQL database after each test, so
I clear out my data and make sure that the Identity field
gets reset so it matches the other XML data which will
be used later.
So, now we are done.  You should have working test, that can

take XML data and put it into SQL for Data Populate, but
you also have a set of data that you can then run test against
and test that you got correct results.
In the next installment, we will examine the full completed LINQ 
middle tier of our application.
Have fun.