Blogs

Paul Kimmel's Blog

May 2009 - Posts

  • Converting SQL with Groups to XML Using LINQ and Functional Construction

         

    There is a reason technical titles are so boring: the title is trying to tell you what the technical content is about. Its still boring. If Google had been called web crawling, indexing search engine, or Starbucks had been called hip, expensive coffee shop I doubt they would have been as popular as they are. Same thing with Amazon.com and a bunch of other stuff. The difference between going to Starbucks and a technical article is that people will eventually get curious about what Starbucks might be and it catches on and then the coolness sells itself. If that approach were used with a technical article by the time, if ever, people discovered what the technical article is about the technology would have changed and no one would ever come. The equivalent in consumer product time would be—Seattle’s Pike Place opens Starbucks in 1974 and in the fall of 1974 people never went back to coffee except for three guys who also still love FoxPro. (I like to pick on the Fox guys because they get so irate and write the funniest hate mail. No, really, I love you FoxPro guys. FoxPro rocks.) I think the concept is known as branding. With consumer products there is time to associate McDonalds with hamburger. In Internet time and with technical articles branding probably doesn’t work. So, instead of something clever, like “The Inmates are Running the Asylum” (which Alan Cooper swiped from me-just kidding) this article is called the equivalent of “tasty, inexpensive hamburger place that sells cheap toys your kids will beg for, play with once, and then leave for you to step on while you are getting up to pee late at night”, but at least with this blog you know what you are getting into.

    If you need to convert SQL data to XML with minimal fuss, pomp, and circumstance, and that data has some natural grouping arrangement and you are bi-curious about LINQ then this is your Erewhon—see Erewhon tells you I like Sam Butler but it tells you nothing about the technical content.

    Suppose you have a process that uses an XML file. Further suppose that you want to update that file occasionally from a database. A technology exists that makes this easy. Its called function construction. Functional construction is where you can create an XML document from data using classes in the System.Xml.Linq namespace, specifically XElement and XAttribute. If the data comes from a database then you can use something like LINQ to DataSets to easily get the data from the database to LINQ and from LINQ to XML. Now, add a twist. What if the data represents natural groups like Order Details from the Northwind database. the challenge is not that you have SQL data and you want to produce a hierarchical XML document. That’s ok. LINQ supports grouping and you can mix grouping in with your XML and functional construction and produce the XML document. All of this can be done with relatively few lines of code. (For more on LINQ groups and functional construction check out my book LINQ Unleashed for C# from Sams.) Listing 1 shows the flat table structure of Northwind database’s order Details table. Listing 2 shows the XML structure we’d like to get it to.

    Listing 1: A CREATE TABLE statement that shows the stripped down SQL script that will create the Northwind.[Order Details] table, including schema information.

    USE [Northwind]
    CREATE TABLE [dbo].[Order Details](
        [OrderID] [int] NOT NULL,
        [ProductID] [int] NOT NULL,
        [UnitPrice] [money] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice]  DEFAULT ((0)),
        [Quantity] [smallint] NOT NULL CONSTRAINT [DF_Order_Details_Quantity]  DEFAULT ((1)),
        [Discount] [real] NOT NULL CONSTRAINT [DF_Order_Details_Discount]  DEFAULT ((0)),
    CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
    (
        [OrderID] ASC,
        [ProductID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Listing 2: The target XML showing the order detail items grouped by OrderID (with just a few records selected to keep the listing short).

    <?xml version="1.0" encoding="utf-8"?>
    <Orders>
      <Order ID="10248">
        <Order_Detail>
          <ProductID>11</ProductID>
          <UnitPrice>14.0000</UnitPrice>
          <Quantity>12</Quantity>
          <Discount>0</Discount>
        </Order_Detail>
        <Order_Detail>
          <ProductID>42</ProductID>
          <UnitPrice>9.8000</UnitPrice>
          <Quantity>10</Quantity>
          <Discount>0</Discount>
        </Order_Detail>
        <Order_Detail>
          <ProductID>72</ProductID>
          <UnitPrice>34.8000</UnitPrice>
          <Quantity>5</Quantity>
          <Discount>0</Discount>
        </Order_Detail>
      </Order>
      <Order ID="10249">
        <Order_Detail>
          <ProductID>14</ProductID>
          <UnitPrice>18.6000</UnitPrice>
          <Quantity>9</Quantity>
          <Discount>0</Discount>
        </Order_Detail>
        <Order_Detail>
          <ProductID>51</ProductID>
          <UnitPrice>42.4000</UnitPrice>
          <Quantity>40</Quantity>
          <Discount>0</Discount>
        </Order_Detail>
      </Order>
      <Order ID="10250">
        <Order_Detail>
          <ProductID>41</ProductID>
          <UnitPrice>7.7000</UnitPrice>
          <Quantity>10</Quantity>
          <Discount>0</Discount>
        </Order_Detail>
        <Order_Detail>
          <ProductID>51</ProductID>
          <UnitPrice>42.4000</UnitPrice>
          <Quantity>35</Quantity>
          <Discount>0.15</Discount>
        </Order_Detail>
        <Order_Detail>
          <ProductID>65</ProductID>
          <UnitPrice>16.8000</UnitPrice>
          <Quantity>15</Quantity>
          <Discount>0.15</Discount>
        </Order_Detail>
      </Order>
      <Order ID="10251">
        <Order_Detail>
          <ProductID>22</ProductID>
          <UnitPrice>16.8000</UnitPrice>
          <Quantity>6</Quantity>
          <Discount>0.05</Discount>
        </Order_Detail>
        <Order_Detail>
          <ProductID>57</ProductID>
          <UnitPrice>15.6000</UnitPrice>
          <Quantity>15</Quantity>
          <Discount>0.05</Discount>
        </Order_Detail>
      </Order>
    </Orders>

    The solution comes in two parts. The first part is plain vanilla ADo.NET that selects some order detail items from the Northwind database and returns those items in a DataTable. DataTables can be used as a source for LINQ queries. The next part of the solution is a slightly longish LINQ query that demonstrates the functional construction. I won’t elaborate on the GetData function because many of you probably already know ADO.NET quite well. (You can just copy the code if you and to experiment with it. Note that the XML we are trying to produce is organized by all of the detail items that are part of a single order id, a grouping relationship. Listing 3 contains the complete solution. An explanation describing how the LINQ query works follows the listing.

    Listing 3: Some ADO.NET that reads a table and uses LINQ and functional construction to produce an XML file.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Xml.Linq;

    namespace SqlLinqXmlFuncConstruction
    {
      class Program
      {
        static void Main(string[] args)
        {
          DataTable orders = GetData();
          XElement doc =
              new XElement("Orders",
                    from book in orders.AsEnumerable()
                    group book by book.Field<int>("OrderID") into g
                    select
                      new XElement("Order",
                        new XAttribute("ID", g.Key),
                          from item in g
                          select new XElement("Order_Detail",
                              new XElement("ProductID",
                                item.Field<int>("ProductID")),
                            new XElement("UnitPrice",
                              item.Field<decimal>("UnitPrice")),
                            new XElement("Quantity",
                              item.Field<Int16>("Quantity")),
                            new XElement("Discount",
                              item.Field<float>("Discount"))
                            )));
          doc.Save("..\\..\\Orders.xml");
          Console.WriteLine(doc);
          Console.ReadLine();
        }

        private static DataTable GetData()
        {
          string connectionString =
            @"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;" +
            "Integrated Security=True";

          string SQL = "SELECT TOP 10 * FROM [Order Details]";
          DataTable data = new DataTable();
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
            connection.Open();
            SqlCommand command =
              new SqlCommand(SQL, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(data);
          }

          return data;
        }

      }
    }

    The code we are interested in is defined in Main.

    Main calls GetData and keeps local copy of the DataTable returned by that method. the next statement beginning with XElement doc = new XElement(“Orders” starts what is referred to as functional construction. That is, the XML will be constructed with function calls. Instead of of entirely literal calls to XElement and XAttribute the second argument to the XElement constructor is a LINQ query.

    The statement from book in orders.AsEnumerable() defines the range variable book, which is like an iterator control variable. The clause in orders.AsEnumerable() uses an extension method to convert the DataTable to an EnumerableRowCollection<DataRow>, allowing LINQ to query the table data. The clause group book in book.Field<int>(“OrderID”) into g tells LINQ to group on ORDERID and store the results in g. Following the group by is the select. The select clause plays roughly the same role as a SQL select. In LINQ select establishes an iterator over the data in the from clause. The first select creates the <Order> element with the ID attribute for each order in the group g.

    The inner from clause—from item in g—starts a new LINQ query, a nested query, defining the range variable as item from the collection of group items represented by g. Finally, the nested select returns the results of the functional construction method calls to XElement adding a sub-item <Order_Detail> for each item in the order and subordinate to the <Order_Detail> item are the actual details. Finally the XElement is saved as an XML file. (The XML declaration<?xml version=”1.0” encoding=”utf-8” ?>—is added automagically when you save the XElement (document). the XML is also written to the console for sanity checking.

    Writing LINQ queries like the one in listing 3 take a little practice but you can get a lot of work done with a relatively little bit of code, and, everything you learn about LINQ can be used with SQL, XML, XSD, Entities and so much more. LINQ is a learn-once-leverage-many-times kind of tool and is being routinely extended into many existing technology areas like SharePoint and Active Directory.

    Got to go back to the book—Professional DevExpress ASP.NET Controls—it doesn’t seem to be writing itself. Of course, the best thing about all of this .NET goodness is that you can use it with Developer Express’ ASP.NET and WinForms controls.

  • Importing External Image Files to SQL Server (Down and Dirty)

         

    There are a lot of applications that use external images for their websites. However, if you store images in a database then they get backed up with your database, they are portable with your database, and it just may prevent someone from tampering with the images. Anyway, if you have links—like ~/images/picture.jpg”—and you want to load those from your website into a database then the following code will get the job done. You of course will have to change your connection string and update the database column references.

    Listing 1 Contains a down and dirty loop that is intended to run from a Website. You could easily change it to a console application by fiddling with the path information. Listing 2 contains the implementation of the UpdateImage stored procedure. Just substitute your column names, and Listing 3 contains the script for schema used for the example.

    If you are an old hand you probably could have figured this out in a minute. Its not very robust, but it represents code you probably only need to run one time any way. Just rip it out of the Page_Load after it runs. On an interesting note: when I used literal SQL for the update it kept trying to write ‘System.Byte[]’ to the BookImage column. It seems like string.Format was struggling with stream.ToArray. What is cooler is the demo I created that used the converted database images, of course, you’ll have to wait for the book (Professional DevExpress ASP.NET Controls) for that.

    Listing 1: When the web page loads the links are read in the outer loop and the images are loaded in the inner loop; down and dirty just like I said.

    protected void Page_Load(object sender, EventArgs e)
        {
            // run once
            string connectionString =
                ConfigurationManager.ConnectionStrings["BooksConnectionString"].ConnectionString;
            using (SqlConnection connection =
                new SqlConnection(connectionString))
            {
                connection.Open();
                const string sql =
                    "SELECT ID, CoverImageLink FROM BOOKS";
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int id = (int)reader["ID"];
                    string link = (string)reader["CoverImageLink"];

                    string path = Server.MapPath(link);
                    if(File.Exists(path))
                    {
                        System.Drawing.Image bitmap = Bitmap.FromFile(path);
                        MemoryStream stream = new MemoryStream();
                        bitmap.Save(stream, ImageFormat.Jpeg);
                        byte[] data = stream.ToArray();
                        using (SqlConnection connection2 = new SqlConnection(connectionString))
                        {
                            connection2.Open();
                            SqlCommand command2 = new SqlCommand("UpdateImage", connection2);
                            command2.CommandType = CommandType.StoredProcedure;
                            command2.Parameters.AddWithValue("@ID", id);
                            command2.Parameters.AddWithValue("@BookImage", data);
                            command2.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

    Listing 2: The UpdateImage stored procedure (which you can run in Visual studio or SQL Sever Management Studio.

    CREATE PROCEDURE UpdateImage
        @ID int,
        @BookImage image
    AS
    BEGIN
        UPDATE BOOKS
        SET
            BookImage = @BookImage
        WHERE ID = @ID
    END

    Listing 3: The schema (SQL script) that describes the database I ran the code in listing 1 against.

    USE [Books]
    GO
    /****** Object:  Table [dbo].[Books]    Script Date: 05/20/2009 15:23:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Books](
        [ID] [int] NOT NULL,
        [Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Author] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Publisher] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ISBN] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Hyperlink] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Tip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Language] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PublicationDate] [datetime] NULL,
        [CoverImageLink] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [BookImage] [image] NULL,
        [Visible] [bit] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  • Upsizing an MS Access 2007 Database to SQL Server

         

    Working on the book project—Professional DevExpress ASP.NET Controls at http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470500832.html—I needed to convert an existing Access database to SQL Server 2005. Unfortunately sometimes even Google doesn’t find you a straight forward solution. Its not Google’s fault. In this case its just that there are so many security issues, Trust Center nonsense, and other glitches that some tips just take to long.

    If you are in a hurry the fastest way to upsize a database actually seems to be to go backward to go forward. Here is a quick, pretty painless way to move an Access 2007 database to SQL Serve 2005—if you are getting permission and Trust Center errors other ways.

    1. Open you Access 2007 database (*.accdb)
    2. In Access 2007 Choose Save As|Access 200 Database
    3. Pick a new file name with the .mdb extension.
    4. Click Save. (This step makes a copy of your Access database as a .MDB file)
    5. Close Access
    6. Open Microsoft SQL Server Management Studio
    7. Expand Databases
    8. Right Click Databases in the Object Explorer and click New Database
    9. Provide a database name (see Figure 1) and Click OK.
    10. Right click the new database in the Object Explorer
    11. Click Tasks|Import which will start the SQL Server Import and Export Wizard
    12. Click Next
    13. In the Choose Data Source wizard pick the Microsoft Access data source
    14. use the Browse button and brose to your Access database (see Figure 3)
    15. Click Next
    16. Chose a Destination (the database you created in steps 8 and 9) (see Figure 4)
    17. Click Next
    18. In the Specify Table Copy or Query use the default Copy option (see Figure 5)
    19. Click Next
    20. Select all of the tables you and views you want to copy
    21. Click Next
    22. Click Finish (to execute immediately)
    23. Click Finish again (too many steps again, as far as I am considered)
    24. Click Close after the wizard runs (see Figure 6)

    This is a lot of steps, but it worked on my Vista box and converted an Access 2007 database to SQL Server 2005 without any goofy Trust Center issues, no security setting changes, and the least amount of headache. Borrowing from Alan Cooper’s—creator of Visual Basic—ideas in the book The Inmates are Running the Asylum sometimes software makes us perform too many of the equivalent of “are you sure steps”. There should be one button—upgrade to SQL Server—that in one step upgrades everything. One click and I get the result wanted. Tables, views, and stuff unneeded can be removed later. A lot of times the most obvious thing is what people want, sort of the equivalent of a one-click or easy button.

    If you have to convert Access to SQL Server and you are getting warnings perhaps this approach will work best for you.

    image 
    Figure 1: Create a New SQL Server database in SQL Server Management Studio.

    image
    Figure 2: The SQL Server Import and Export Wizard.

    image 
    Figure 3: Choose Microsoft Access and brose to your Access 2000 .mdb file.

    image
    Figure 4: Pick your destination SQL Server database.

    image
    Figure 5: Indicate that you want to copy data from one or more tables or views.

    image
    Figure 6: The wizard running.

  • How Do You Add Update and Cancel Buttons to an ASPxTreeList template (or EditForm)

         

    Did you know that the ASPxTreeList has an editor form mode? Many of you probably did. Like the ASPxGridView this means you can edit a node in the tree list using a form for making changes to the data rather than plain old TextBoxes, in edit form mode you get controls that are generally more type friendly and an input layout that is a little more intuitive.

    Did you know that the ASPxTreeList lets you define custom templates? It does. While the ASPxTreeList is pretty good at figuring out how to design and edit form and the column properties and style properties will let you manage the appearance of the ASPxTreeList in a lot of ways, you can define your own edit form. To do so click the Smart tags menu, click Edit templates, choose the EditForm template and design away.

    But wait, how do you implement the equivalent of the Update and Cancel command? Ah! the easy answer is the ASPxTreeListTemplateReplacement . (That’s a mouthful. working on the book I wrote that about ten times.) Unfortunately, the ASpxTreeListTemplateReplacement control is not in the toolbox by default. You have to add it. The easy way to get the Update and Cancel commands in your ASPxTreeList template is to:

    1. Open the Toolbox in Visual Studio
    2. Expand the Developer Express Data tab
    3. Right-click to display the Toolbox context menu and click Choose Items
    4. On the .NET tab of the Choose Toolbox Items dialog scroll down until you see the ASPxTreeListTemplateReplacement control
    5. Check the box next to it and close the dialog

    Once you have the ASPxTreeListTemplateReplacement control in the Toolbox use this control to create the Update and Cancel buttons as needed for ASPxTreeList templates. To configure the update button change the ASPxTreeListTemplateReplacement.ReplacementType to UpdateButton, and to configure the Cancel button change the other ASPxTreeListTemplateReplacement.ReplacementType to CancelButton. That’s all you need to do. With these controls no code is needed to invoke the update and cancel behaviors for the ASPxTreeList in edit mode.

  • A Little Slow but Now On Twitter (@pkimmel)

         

    Sometimes I am a little slow to grab onto new technologies, but I will tell you why. In 1990 the Internet as we know it was non-existent. It was all pretty much text based, cryptic, and you could only send text. In 1990 I wasn’t even sure where things were going, but it seemed like there could be a lot of people out there. (This is a true story.)

    I had the brilliant idea (in 1990) of posting some small applications online—remember no browser, no WWW, no eBay, no Amazon, nothing. So I had some applications—I think it was actually an undelete program with code or some such thing. (Don’t laugh but in those days people paid money for batch menus made with ASCII characters. I thought I was going to be rich.)

    Sadly, I got a text notification that said the “net” is for non-commercial uses only. I remember thinking if people can’t sell anything on this dumb thing it will never catch on. Doh! I was totally sleeping when Netscape blew up. Timing is everything.

    As it turns out I sort of thought the same thing about Twitter—who wants to know when I go to the movies, the bathroom, or have my picture taken with Batgirl. In my case maybe no one, but some people want to know when some other people are doing stuff. Viola! Twitter. People want to know. Who knew?

    I am not always an early adopter—although I wish I would have timed that Internet thing better—but I am on twitter. I post stuff about Developer Express and sometimes that I am going to TaeKwonDo—maybe someone wants to know (when I go to TaeKwonDo).

    If you want to follow Developer Express on Twitter follow @DevExpress.

  • How Books Work, and See Your Name in Print

         

    So, writing is pretty egoless for me. I suppose if I were treated like a rock star, got the hot babes, and made money like Kid Rock it might be harder to remain humble but that’s not really what happens. What happens to me is I get to ride a Segway at TechEd, I have to bribe Batgirl to take a photo with me and the book, and the Microsoft teletubbies come to the booth for free. (It was pretty funny to hear their chaperone keep telling Visual Studio to hurry up.) Thus, I write pretty much because I am slightly twisted, have few social skills, and enjoy working evenings and weekends. But, sometimes I can have a little fun with it too.

    Here is basically how writing a book works in general: I pitch an idea to a publisher in the form of a proposal and outline. The publisher chooses to buy it, many painstaking months later—after learning as much as humanly possible about the subject—a book shows up on Amazon, on the publisher’s website, and in your local bookstore.

    If you haven’t heard I am—working in collaboration with many smart people at Developer Express—writing a book, Professional DevExpress ASP.NET Controls (see Wiley’s website for details.) This book is a little different. No, it's still painstaking and requires weekends and evenings and I have to learn a lot of stuff, but Developer Express is collaborating with Wrox to bring our customers the book. In short, we are writing it for a very specific audience.

    Here is the Fun Part: See Your Name in Print

    The book will be ready for PDC 2009. This means I have to finish up very quickly. However, because Developer Express customers (existing and new) are our desirable primary customers, I’d like to give you a chance to see your name in print. I will be perusing our forums and looking at blog feedback. If I see a problem that Developer Express (or I directly) can help you solve and it fits into the book—Developer Express ASP.NET controls and related topics—then I will attempt to incorporate it into the book.

    The caveats: 1) I will be sending you an email to ask permission to acknowledge you in the front of the book and you have to respond quickly, 2) I can’t use your code or text directly, books pretty much need to be original material or a lot of legal documents are required, 3) you won’t get paid (sorry) and 4) I will add as many names as the publisher and time allows. So if you want to play along then get cranking on feedback and questions and see your name in print. Add a note—I WANT TO SEE MY NAME IN PRINT—to your feedback and I will do my part.

    Finally, since I am writing this on a Sunday night the legal department may put the kibosh on the whole thing in the morning. Sometimes that happens: writers write things and lawyers say don’t do that.

  • The Cat is Out of the Bag

         

    The cat is out of the bag. If you were at Tech Ed 2009 in Los Angeles then you know Developer Express is publishing a book with Wrox Press. You couldn’t hardly miss the promotional item: a 50”x 50” book with my gynormous head on it. I tried to get them to put Mehul Harry’s head on it, but since I am writing it they put mine their for all to see.

    The book Professional DevExpress ASP.NET Controls (check out the Wiley site to pre-order a copy of it) will be out in September 2009. How do I know? I am writing it and will be burning a lot of midnight oil to get it finished in time for PDC 2009. The downside for me is there is so much material to cover. The upside for you is that there is so much material to cover.

    The book includes $250 worth of FREE Developer Express controls, over 400 pages of all new content on Developer Express’ ASP.NET controls, and all the code is on the sample CD-ROM (or can be downloaded from www.devexpress.com). I’d like to point out that the book also includes content on ASP.NET, JavaScript, LINQ, eXpress Persistent Objects (XPO), tips for CodeRush and Refactor! Pro, Ajax, and more.

    I am fortunate to get to write about a great suite products that you, our customers, told us you liked by voting for us to win 11 asp.netPRO Reader’s Choice Awards (see Julian Bucknall’s blog post) and two “Best of TechEd 2009” awards (see Julian Bucknall’s other blog post).

    Now back to work.

  • Assembling an N-Nested ASPxGridView

         

    I was standing on the surprisingly biting concrete at TechEd 2009 and a customer asked: “does your ASPxGridView support n-nesting?” My response was why, yes, yes it does. Unfortunately I didn’t have an example handy, but yesterday at the end of the day I whipped one up and the ASPX is provided here with some basic instructions as to how to create n-nesting.

    To summarize, our ASPxGridView supports nesting. Add two or more grids to a page in design mode, wire each up to a data source, and then drag and drop the grids to be nested into the DetailRow template region and you are pretty much finished. N-nesting is supported as an almost completely no code solution. You do have to correlate the keys between the parent and child grids and set a property or two, but that’s it. (For the finished result see Figure 1.)

    image
    Figure 1: The run-time view of a grid nested three levels deep. 

    Wiring up One Grid

    There are two parts to grid nesting. The first part—wiring the grid to data—is the same for all grids in the nesting relationship. Each grid in the nesting relationship has to have a source for its data. For the example right click the Smart tag associated with an ASPxGridView and select Choose Data Source|New data source to run the data source wizard. The Configure Data Source wizard will walk you through building a connection string, selecting your data source (and designing a query), and wiring up the resultant data source to associated ASPxGridView.

    batgirl
    Figure 2: Batgirl gives away Batman’s secret identity. I am Batman! (What happens at TechEd stays at TechEd.)

    Correlating Child and Parent Grids

    The only programming you need to do is a line of code that correlates a parent grid to a child grid. When you ware walking through the Configure Data Source wizard for the child grids you need to complete an extra step. Parent and child relationships can be defined with joins or WHERE clauses. For the Configure Data Source wizard use the WHERE button on the Configure the Select Statement (see Figure 3) step to specify how data are correlated to the parent data. For example, Northwind.Customers is related Northwind.Orders by CustomerID. To nest Orders in Customers click the WHERE button when configuring the Orders table and configure a WHERE condition for the foreign key CustomerID of the Orders table (use Figure 4 as a visual guide).

    image
    Figure 3:  On the Configure the Select Statement click the WHERE button to correlate the parent grids with the child grids.

    image
    Figure 4: Configure a column that represents the implicit WHERE-clause join column for child data sets.

    For the demo Orders is nested in the Customers grid and Order Details is nested in the Orders grid. You will need to configure a WHERE condition when configuring the data source for the Orders table and the Orders Detail table. The Column value (see Figure 4) needs to be the Foreign key for the child table. The Operator needs to be equal and the Source value of Session means the primary key will be stored in the Session.

    Next you need to store the primary key of the parent table in Session. So when a parent row is expanded the nested grid knows what parent row is being displayed. For the example, Customers grid contains Orders and the Orders grid contains Order Details. The only code you need to write to make the basic nesting work is a BeforePerformDataSelect event handler for the Orders grid and Order Detail grid. In each of these events the sender argument represents the parent grid. Cast sender as an ASPxGridView and invoke the GetMasterRowKeyValue method. Store the result in Session using the column defined in the Add WHERE Clause dialog (see Listing 1).

    Listing 1: BeforePerformDataSelect gets the key from the parent row and stores it for the child grid, correlating the parent and child grids.

    protected void ASPxGridView2_BeforePerformDataSelect(object sender, EventArgs e)
    {
      Session["CustomerID"] = ((ASPxGridView)sender).GetMasterRowKeyValue();
    }


    protected void ASPxGridView3_BeforePerformDataSelect(object sender, EventArgs e)
    {
      Session["OrderID"] = ((ASPxGridView)sender).GetMasterRowKeyValue();
    }

    Completing the Nesting Effect

    To complete the nesting effect select the second to the last grid. In our example, select the Orders grid. From the Smart tags menu select Edit Templates and pick the DetailRow template. Drag and drop the grid containing the Order Details into the grid containing Orders DetailRow template (shown in Figure 5). Repeat the process to drag the grid containing the Orders (which now also contains the Order Details grid) data into the DetailRow template for the Customers grid.

     

    image 
    Figure 5: The grid containing the Order Details data shown nested in the grid containing the Orders data.

    Finally, for the Orders and Order Details grids set the SettingsDetail.IsDetailGrid to true. For the Customers and Orders grids set the SettingsDetail.ShowDetailRow property to true. (Listing 2 contains all of the ASPX for the demo). That’s pretty much all there is to it. To recap, you need to:

    • Correlate every child to its parent with a WHERE clause
    • Implement a BeforePerformDataSelect method to store the parent grid’s master key in Session based on the named-value specified in the Add WHERE Clause step of the data source wizard
    • Place each child grid in the DetailRow template of its parent
    • Each grid needs its own data source
    • Every grid that has a detail grid needs the SettingsDetail.ShowDetailRow to true
    • Every grid that is a detail grid needs the SettingsDetail.IsDetailGrid to true

    Listing 2: THE ASPX  for this sample was all generated by the IDE and the Developer Express controls. Here it is.

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
    <%@ Register assembly="DevExpress.Web.ASPxGridView.v9.1, Version=9.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxGridView" tagprefix="dxwgv" %>
    <%@ Register assembly="DevExpress.Web.ASPxEditors.v9.1, Version=9.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
          <dxwgv:ASPxGridView ID="ASPxGridView1" runat="server"
            AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
            KeyFieldName="CustomerID">
            <Templates>
              <DetailRow>
                <dxwgv:ASPxGridView ID="ASPxGridView2" runat="server"
                  AutoGenerateColumns="False" DataSourceID="SqlDataSource2"
                  KeyFieldName="OrderID"
                  onbeforeperformdataselect="ASPxGridView2_BeforePerformDataSelect">
                  <Templates>
                    <DetailRow>
                      <dxwgv:ASPxGridView ID="ASPxGridView3" runat="server"
                        AutoGenerateColumns="False" DataSourceID="SqlDataSource3"
                        KeyFieldName="OrderID"
                        onbeforeperformdataselect="ASPxGridView3_BeforePerformDataSelect">
                        <Columns>
                          <dxwgv:GridViewDataTextColumn FieldName="OrderID" ReadOnly="True"
                            VisibleIndex="0">
                          </dxwgv:GridViewDataTextColumn>
                          <dxwgv:GridViewDataTextColumn FieldName="ProductID" ReadOnly="True"
                            VisibleIndex="1">
                          </dxwgv:GridViewDataTextColumn>
                          <dxwgv:GridViewDataTextColumn FieldName="UnitPrice" VisibleIndex="2">
                          </dxwgv:GridViewDataTextColumn>
                          <dxwgv:GridViewDataTextColumn FieldName="Quantity" VisibleIndex="3">
                          </dxwgv:GridViewDataTextColumn>
                          <dxwgv:GridViewDataTextColumn FieldName="Discount" VisibleIndex="4">
                          </dxwgv:GridViewDataTextColumn>
                        </Columns>
                      </dxwgv:ASPxGridView>
                    </DetailRow>
                  </Templates>
                  <Columns>
                    <dxwgv:GridViewDataTextColumn FieldName="OrderID" ReadOnly="True"
                      VisibleIndex="0">
                      <EditFormSettings Visible="False" />
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="CustomerID" VisibleIndex="1">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="EmployeeID" VisibleIndex="2">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataDateColumn FieldName="OrderDate" VisibleIndex="3">
                    </dxwgv:GridViewDataDateColumn>
                    <dxwgv:GridViewDataDateColumn FieldName="RequiredDate" VisibleIndex="4">
                    </dxwgv:GridViewDataDateColumn>
                    <dxwgv:GridViewDataDateColumn FieldName="ShippedDate" VisibleIndex="5">
                    </dxwgv:GridViewDataDateColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipVia" VisibleIndex="6">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="Freight" VisibleIndex="7">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipName" VisibleIndex="8">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipAddress" VisibleIndex="9">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipCity" VisibleIndex="10">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipRegion" VisibleIndex="11">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipPostalCode" VisibleIndex="12">
                    </dxwgv:GridViewDataTextColumn>
                    <dxwgv:GridViewDataTextColumn FieldName="ShipCountry" VisibleIndex="13">
                    </dxwgv:GridViewDataTextColumn>
                  </Columns>
                  <SettingsDetail IsDetailGrid="True" />
                  <SettingsDetail ShowDetailRow="True" />
                </dxwgv:ASPxGridView>
              </DetailRow>
            </Templates>
            <Columns>
              <dxwgv:GridViewDataTextColumn FieldName="CustomerID" ReadOnly="True"
                VisibleIndex="0">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="CompanyName" VisibleIndex="1">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="ContactName" VisibleIndex="2">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="ContactTitle" VisibleIndex="3">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="Address" VisibleIndex="4">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="City" VisibleIndex="5">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="Region" VisibleIndex="6">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="PostalCode" VisibleIndex="7">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="Country" VisibleIndex="8">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="Phone" VisibleIndex="9">
              </dxwgv:GridViewDataTextColumn>
              <dxwgv:GridViewDataTextColumn FieldName="Fax" VisibleIndex="10">
              </dxwgv:GridViewDataTextColumn>
            </Columns>
            <SettingsDetail ShowDetailRow="True" />
          </dxwgv:ASPxGridView>
          <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT * FROM [Customers]"></asp:SqlDataSource>
          <asp:SqlDataSource ID="SqlDataSource2" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT * FROM [Orders] WHERE ([CustomerID] = @CustomerID)">
            <SelectParameters>
              <asp:SessionParameter Name="CustomerID" SessionField="CustomerID"
                Type="String" />
            </SelectParameters>
          </asp:SqlDataSource>
          <asp:SqlDataSource ID="SqlDataSource3" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT * FROM [Order Details] WHERE ([OrderID] = @OrderID)">
            <SelectParameters>
              <asp:SessionParameter Name="OrderID" SessionField="OrderID" Type="Int32" />
            </SelectParameters>
          </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>

    Happy TechEd! Have a safe trip home.

  • Implementing a Windows Explorer-style Web Page with the ASPxTreeList

         

    The ASPxTreeList is a hybrid tree and grid display control. It works in unbound and bound modes and includes a virtual mode. Virtual mode—the subject or our example—is great for large data sets. A challenge related to building a tree from large amounts of data is reading the data, building all of the tree nodes and child nodes, and then transmitting that data back to the client. The example included demonstrates how the ASPxTreeList supports unbound data and large amounts of data using virtual mode.

    Virtual mode essentially works in this way: part of the tree is populated up front. When a subordinate part is requested then just that additional subordinate part is created in addition to existing parts. Translated to the ASPxTreeList this means one or more top-level nodes are created. You code two events that respond when a top level node is expanded and child nodes consequently need to be populated too. The virtual mode events populate the node-to-be-expanded at the time of the request rather than all up front.

    A good stress-test example might be using the ASPxTreeList to represent the file system of a computer. With terabyte drives the sheer amount of data would definitely confound most system’s memory and exceed available bandwidth. In this instance an entire representation of a computer’s file system would be physically as well as logically impractical. Physical impracticability exists due to hardware limitations and logically impracticable is present because a person probably wants to look at a single node at a time—for example, a person probably wants to copy files in a single folder or pick a single file to modify.

    To demonstrate, my new laptop has about 80 gigabytes of data. The data are represented as files and directories, and in this particular instance the files and directories number 574,801. Represented as a tree structure this is a tremendous number of nodes and child nodes, and doing so as a Web page is physically impractical and no human user needs to see or is patient enough to wait for all 574,801 elements. To qualify my comments, it is worth stating that some operations—like searching and sorting—may require an examination of all files, but viewing the file system in a hierarchical way does not require that all files be examined. More likely when a tree structure is needed then the general use will be drilling down into various nodes based on interest in the contents of a particular node. Subsequently only nodes of interest need be populated at the time of interest, resulting in a reduction in tree population times and bandwidth requirements.

    Listing 1 provides an example that demonstrates how to use the virtual mode feature of the ASPxTreeList to create a view-only representation of a PCs file system. Listing 2 provides the ASPX for the sample. After the listing are the steps for re-creating the sample and an explanation of the code-behind (the code in Listing 1).

    Listing 1: Using the Virtual Mode capability of the ASPxTreeList to display the contents of a file system.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.IO;
    using DevExpress.Web.ASPxTreeList;
    using System.Security.Permissions;

    public partial class _Default : System.Web.UI.Page

        private static readonly string FOLDER_ICON = "~/images/folder.gif";
        private static readonly string FILE_ICON = "~/images/file.gif";

        private static IEnumerable<FileInfo> GetFileInfoData(IEnumerable<string> files)
        {
          return from file in files
                 let info = new FileInfo(file)
                 where (info.Attributes
                   & FileAttributes.Hidden) != FileAttributes.Hidden
                 select info;
        }

        private static IEnumerable<string> GetFiles(string start)
        {
          return
            Directory.GetDirectories(start).Union(Directory.GetFiles(start));
        }

        private string GetIcon(FileInfo info)
        {
          return (info.Attributes & FileAttributes.Directory) == FileAttributes.Directory
            ? FOLDER_ICON : FILE_ICON;
        }

        protected void ASPxTreeList1_VirtualModeCreateChildren(object sender, TreeListVirtualModeCreateChildrenEventArgs e)
        {
          e.Children = new List<FileInfo>();

          if (e.NodeObject == null)
          {
            FileInfo info = new FileInfo("C:\\");
            e.Children.Add(info);
          }
          else
          {
            string path = e.NodeObject == null ? "C:\\"
              : e.NodeObject.ToString();

            if (!Directory.Exists(path)) return;

            try
            {
              var files = GetFiles(path);
              var infos = GetFileInfoData(files);
              e.Children = new List<FileInfo>();
              foreach (var info in infos)
              {
                e.Children.Add(info);
              }
            }
            catch { }
          }
        }

        protected void ASPxTreeList1_VirtualModeNodeCreating(object sender, TreeListVirtualModeNodeCreatingEventArgs e)
        {
          FileInfo info = (FileInfo)e.NodeObject;
          e.NodeKeyValue = GetNodeGuid(info.FullName);
          e.IsLeaf = !Directory.Exists(info.FullName);

          if(info.FullName == "C:\\")
            e.SetNodeValue("Name", "C:\\");
          else
            e.SetNodeValue("Name", info.Name);
          e.SetNodeValue("IconName", GetIcon(info));
          e.SetNodeValue("NodePath", info.FullName);
        }

        // need a unique key, so GUID is used
        private Guid GetNodeGuid(string path)
        {
          if (!Map.ContainsKey(path))
            Map[path] = Guid.NewGuid();
          return Map[path];
        }

        private Dictionary<string, Guid> Map
        {
          get
          {
            const string key = "DX_PATH_GUID_MAP";
            if (Session[key] == null)
              Session[key] = new Dictionary<string, Guid>();
            return Session[key] as Dictionary<string, Guid>;
          }
        }
    }

    Listing 2: The code-behind for the ASPxTreeList sample.

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <%@ Register assembly="DevExpress.Web.ASPxTreeList.v9.1, Version=9.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxTreeList" tagprefix="dxwtl" %>
    <%@ Register assembly="DevExpress.Web.ASPxEditors.v9.1, Version=9.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <dxwtl:ASPxTreeList ID="ASPxTreeList1" runat="server"
              onvirtualmodecreatechildren="ASPxTreeList1_VirtualModeCreateChildren"
              onvirtualmodenodecreating="ASPxTreeList1_VirtualModeNodeCreating"
              AutoGenerateColumns="False" EnableCallbackCompression="True">
              <Settings ShowColumnHeaders="False" />
                <SettingsBehavior ExpandCollapseAction="NodeDblClick" />
                <Templates>
                  <DataCell>
                    <dxe:ASPxImage ID="ASPxImage1" runat="server" ImageUrl='<%# Container.GetValue("IconName") %>'>
                    </dxe:ASPxImage>
                    &nbsp;<dxe:ASPxLabel ID="ASPxLabel1" runat="server" Text='<%# Container.GetValue("Name") %>'>
                    </dxe:ASPxLabel>
                  </DataCell>
                </Templates>
              <Columns>
                <dxwtl:TreeListTextColumn FieldName="Name" VisibleIndex="0">
                </dxwtl:TreeListTextColumn>
              </Columns>
              </dxwtl:ASPxTreeList>
        </div>
        </form>
    </body>
    </html>

    To re-create the example follow these steps:

    1. Create a Web site application
    2. Drop an ASPxTreeList in the <div> section of the page
    3. From the Smart tags menu for the ASPxTreeList select Edit Templates and pick the DataCell template
    4. Drop an ASpxImage followed by an ASPxLabel into the DataCell template
    5. From the Smart tags menu select End Template Editing
    6. As shown in Listing 2 bind the ASPxImage ImageUrl value to IconName and the ASPxLabel’s Text property to Name. (You provide these values in the code)
    7. Again, from the Smart tags menu click the Columns menu item and add a column. Set the FieldName property to”Name”. This will be used to represent the Name value described in step 6
    8. Close the Columns Editor Form
    9. Using Windows explorer find some icons to represent a folder and file. Create a project folder named images and copy these icons/graphics into the images folder
    10. Use the designer to stub out the VirtualModeCreateChildren and VirtualModeNodeCreating event handlers for the ASPxTreeList

    image 
    Figure 1: Add a column with a FieldName value of “Name”, which will be used to contain the folder and file information.

    That’s it. Now you need to implement the code in Listing 1. VirtualModeCreateChildren creates the child nodes and VirtualModeNodeCreating provides detail information for the child nodes. The way this example works specifically is that the first time that VirtualModeCreateChildren is called e.NodeObject will be null. This is used as a cue to create the root node. The nodes are represented as file system objects (FileInfo), and nodes are added to the event argument’s Children property. (Notice that the code itself creates a container for the Children property; in this case List<FileInfo>.) When a node is created VirtualModeNodeCreating is called. This event handler provides detail for the node. In the example in Listing 1 the IsLeaf, Name, IconName, and NodePath values are provided. IsLeaf is used to determine if there are children for this element; in the code it is assumed only non-directories are leafs. SetNodeValue(“Name”, string) is used to set the display name for the node, IconName is used to map an image to the ASPxImage control, and NodePath stores the full path name for possible use.

    When the demo runs only the root node will be created. When a user clicks on the root node—C:\—the VirtualModeCreateChildren and VirtualModeNodeCreating duo spring into action again. This time the else part of VirtualModeCreateChildren is run and C:\’s files and folders are read. GetFiles returns all of the files and directories and GetFileInfoData uses LINQ to query all of those elements, excluding hidden files, and create an enumerable collection of FileInfo objects. Each of the resultant objects are added as children to the TreeListVirtualModeCreateChildrenEventArgs event parameter’s Children property and consequently the VirtualModeNodeCreating event is called for each of these items, and their details are filled in to populate the ASPxImage and ASPxLabel. Essentially the two events are acting in tandem to produce a result that is like a recursive descent algorithm.

    The only other code of note is GetNodeGuid and the Map property. The Map property represents a dictionary mapping file paths to Guids. GetNodeGuid figures out if a node has already been created and associated with a Guid or if a pairing needs to be added to the Dictionary. This code while trivial in appearance is essential. The ASPxTreeList needs a means of distinctly differentiating between nodes and GUIDs are distinct. Unfortunately without this code expanding nodes doesn’t work. (You don’t need this code with databound uses of the ASPxTreeList, but I’d like to see it go away for unbound data too. My first instinct is that the nature of a tree-path is distinct, making the necessity of a unique key redundant. I am in negotiations right now with the grid-code owner to mitigate the unique key requirement. I suspect it is the grid-like use of the ASPxTreeList that makes keys helpful.) The results of the demo running on my laptop are shown in Figure 2.

    image
    Figure 2: The basic explorer Web page running on my laptop.

More from DevExpress
Live Chat
Have a pre-sales question?
Need assistance with your evaluation?
We are here to help.
Chat is one of the many ways you can contact members of the DevExpress Team. We are available Monday-Friday between 8:30am and 5:00pm Pacific Time.
If you need additional product information, require pre-sales assistance, or want help with your order, write to us at info@devexpress.com or call us at
+1 (818) 844-3383.