RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Building Database Web Applications Using ASP.NET 3.5, LINQ, and SQL Server 2005 Reporting Services : Page 6

ASP.NET 3.5 and LINQ provide the greatest amount of functionality yet for building data-aware web applications. Even if you prefer to write stored procedures, you can still leverage some of LINQ to SQL functionality for accessing stored procedures inside .NET.

Tip 12: Using LINQ to XML to Handle Large XML Files
By now, you've likely seen at least one example of using LINQ to XML. Here's a very simple example for using LINQ syntax to query XML data using C#:

   XElement XmlAddr = new XElement("Addresses",
      new XElement("AddressRec",
      new XAttribute("EmployeeID", 1),
      new XElement("FirstName", "Kevin"),
      new XElement("LastName", "Goff"),
      new XElement("Address", "111 Main Street"),
      new XElement("City", "Philadelphia"),
      new XElement("State", "PA"),
      new XElement("Zip", "11111")));
   XDocument oDoc = new XDocument(XmlAddr);
   var xResults = 
      from oXml in oDoc.Descendants("AddressRec")
      where (string)oXml.Element("State") == "PA"
      orderby (string)oXml.Element("Zip")
      select oXml;
If you use Visual Basic, here's the equivalent code. Note that you can create XML literals in Visual Basic, as follows:

   Dim XmlAddr = _
     <?xml version="1.0" encoding="utf-8"
       <AddressRec EmployeeID="1">
         <Address>111 Main Street</Address>
Then you can use Visual Basic syntax, which is considerably less complicated than the C# equivalent:

   Dim Xdocument As XDocument = _
      New XDocument(XmlAddr)
   Dim query = 
      From c In Xdocument.Descendants("AddressRec") _
      Where c.<State>.Value = "PA" _
      Order By c.<State>.Value _
      Select New With {c.<FirstName>.Value}
   Me.DataGridView1.DataSource = query.ToList()  
That's certainly "nice", but the proverbial 500-pound elephant in the room is deciding how you would handle large XML files. Certainly, you don't want to load an entire large XML file into memory. What you can do is set up an extension method to perform a streaming function (see Listing 5), and then refer to the extension method in the LINQ to XML IN syntax (see Listing 6 and Listing 7 for C# and Visual Basic, respectively).

Author's Note: The code in Listing 5 comes from the Microsoft XML Team WebLog, where you'll find the code and further explanation. Kudos to the Microsoft XML Team for providing this!

At the beginning of Listing 5, you can refer to the extension method (StreamElements) as your source for the query. The extension method will perform the streaming of the large XML for you:

   from oXML in StreamElements(xmlFile,"CustOrders") 
If you actually set a breakpoint and stepped through the code, you'd observe that the code:

  • Loops through the code in the extension method (in the while (reader.Read()) statement).
  • Creates an individual XElement object, reads an element from the XmlReader, and then returns the XElement object.
  • Comes back to the LINQ to XML code block (specifically the WHERE clause) and evaluates the XElement object (implicitly returned from the extension method) to see if it matches the WHERE statement.
In this context, you can almost think of the extension method as a delegate to handle the element-by-element streaming of the large XML file.

Tip 13: Baker's Dozen Potpourri—Some Miscellaneous Tidbits for SQL Server Reporting Services 2005
Did you just install SharePoint in a server environment where SQL Server Reporting Services 2005 resides? Did you also notice afterwards that SSRS stopped working? That's because SharePoint can take over the port number for the default web site. You can address that situation by going into IIS and changing the port number for your default web site. (I changed mine to 8080.)

Or...are you having problems trying to run a report for a specific parameter, and also trying to send it directly to a PDF file? Here's the "magic" URL syntax (note that I'm using Port 8080!):

   int  MyCustomerID = 1001;
   string OutURL =
   OutURL += "MyReportProject/MyReport";
   OutURL += "&rs:Command=Render&rs:Format=PDF";
   OutURL += "&CustomerID=" +
Or have you ever tried to drag data from a report DataSet into a report header or footer? SSRS doesn't allow this. You can get around it by doing the following:

  • Define a report parameter associated with the data you want to display in the header/footer.
  • Set the hidden property to true for the parameter.
  • Define the default value for the parameter as the dataset/column associated with the actual database value.
  • Finally, in the report heading, reference the parameter name instead of the dataset name.
Haven't helped you yet? How about this one? You need to develop SSRS reports so that users will only see data associated with their rights. (For example, you might have database rules in the form of tables that define User XYZ as being allowed to see only data for specific product categories or specific regions.) The key is to identify the current user running the report so that you can pass that on to any database query/stored procedure. You can obtain that information either from the User!UserId collection property in SSRS, or from the System_User system variable in SQL Server.

Final Thoughts
The next Baker's Dozen article will present an article I've been planning for a long time and will finally put together: 13 Tips for Implementing Common Design Patterns in C# 3.0. Check my blog for any follow-up tips, additional resources, and notes on this and previous Baker's Dozen articles. You can find the entire source code for this article on my web site.

Kevin S. Goff is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom web and desktop software solutions in .NET, Visual FoxPro, SQL Server, and Crystal Reports. Kevin has been building software applications for 17 years. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date