Using LINQ to SQL with LINQ to XML C# Help

When working with LINQ to SQL or LINQ to XML, you are limited to working with the specific data source for which it was designed. In fact, you are able to mix multiple data sources together when working with LINQ, For an example of this, this section uses LINQ to SQL to query the customers in the Northwind database and turn the results pulled into an XMLdocument.

Setting up the LINQ to SQL Components

The first step for this to work is to add the Northwind SQLServer Express Edition database file to your project. From there, right-click the project to add a new LINQ to SQLclass file to your project. Name the file Northwind, dbml.

This operation will give you a design surface that you are able to work with. From the Server Explorer, drag and c1roptables from the database onto this design surface. You want to drag and drop both the Customers and the Orders tables onto the design surface. Bydoing this, you will notice that there is then a relationship established between these two tables. Once to this point, your view in the IDE should look as is illustrated in Figure 29-7.

Figure 29-7

Figure 29-7

Now that you have your Northwind, dbml in place, you are ready to query this database structure and output the results as an XML file.

Querying the Database and Outputting XML

The next step in your console application is to put the following code in your Program. cs file


This example creates a new instance of the NorthwindDataContext object that is created for you automatically with the LINQ to SQL class you created. Then instead of doing the normal var query, you populate the query performed in an XElement object called xe, Within the select statement of the query, you also create an iteration of Customers objects with the nested elements of <Customer>, <CustomerId>, <CompanyName>, <Country>, and <OrderNum>. Once queried, the xe instance is then saved to disk using xe. Save ( ) . When you go to disk and look at the myCustomers. xml file, you will see the following results (shown only partially here):

~?xml version=”l.O’ encoding”‘utf-S”?>
<Customer>
<Customer>
<CustomerId>ALFKI</Customerld> .
<CompanyName>Alfreds Futterkiste</CompanyName>
<Country>Germany</Country>
<OrderNum>6</OrderNum>
</CUstomer>
<Customer>
<CustomerId>ANATR</CUstomerld>
<CompanyName>AnA Trujillo -.redados y helados</CaapanyName>

<Country>Mexico</Country>
<Order~4</OrderNum>
</Customer>
<!– XML removed for clarity –>
<Customer>
<Customerld>WILMK</Customerld>
<CompanyName>Wilman Kala</CompanyName>
<Country>Finland</Co~try>
<OrderNum>7</OrderNum>
</Customer>
<Customer>
<Customerld>WOLZA</Customerld>
<CompanyName>Wolski Zajazd</CompanyName>
<Country>Poland</Country>
<OrderNum>7</OrderNum>
</CUstomer>
</CUstomer>

From this, you can see just how easy it is to mix the two data sources using LINQ, Using LINQ to SQL, the customers were pulled from the database, and then using LINQ to XML, an XML file was created and output to disk.

Posted on October 31, 2015 in LINQ to XML

Share the Story

Back to Top