Archives

Archives / 2006 / June
  • Free Patterns and Practices Guidance Explorer for ASP.NET and .NET

    JD Meier from the Prescriptive Architecture Guidance Group (aka PAG) at Microsoft stopped by my office this morning to show me the new Guidance Explorer tool he has been working on.  This is a cool WinForms client application that aggregates PAG guidance and suggestions, and allows you to search and sort across it.  This makes it ideal for quickly reviewing checklists of suggestions for ASP.NET projects.  Each guidance rule covers why it should be followed, and includes instructions on how to implement it.

    (Note: Click the screen-shots to enlage them)

    A nice feature the tool provides is the ability to create and save "custom views" of just the guidance you care about.  For example, in the screen-shot below I created a new custom view called "Scott Team" and then dragged/dropped a subset of the guidelines/suggestions I cared about into it:

    Even better, you can optionally right-click on either a guideline or a view made up of guidelines and export them to either a word document or html file:

    You can learn more about the tool and download it for free here (the download location is in the bottom right of the page).  There is also a Wiki on Channel9 with more information here, and JD has a great blog to subscribe to here. The plan is to continually update the tool with additional guidance rules and checklists over the next few months (they've been doing 3-4 updates a month lately).  I definitely recommend checking it out.

    Hope this helps,

    Scott

     

  • 15 Great ASP.NET 2.0 Data Tutorials Published

    Earlier this year I posted two long data access tutorial posts.  My goal was to then follow up these popular posts (90,000+ views and counting!) with a whole series around them.  Unfortunately work sidetracked me and I found I didn't have the time to-do them justice...

    Thankfully Scott Mitchell kindly offered to help write them instead, and has put together an awesome data access tutorial series that is now online here under the "Learn" tab of the http://www.asp.net website.  15 of the data tutorial segments can now be read online, with another 25 still on their way.  Each of the tutorials is available in both VB and C#, and in both HTML and PDF format.  Full source code is also provided for all samples.

    The data series begins by creating a DAL using the built-in data TableAdapter designer in Visual Web Developer Express and Visual Studio.  Scott then create a business class layer that encapsulates the DAL and adds additional business logic and validation to it.  All of the samples then use the ASP.NET 2.0 ObjectDataSource control to bind against the business class layer, and show how to perform common binding operations against it.  Note that if you want you can easily substitute the table adapter DAL for your own DAL implementation with the business class layer -- all of the 39 other data tutorials will then work against it the same way.

    The list of currently published data articles in the series includes:

    Introduction Articles

    • Creating a Data Access Layer using Typed DataSets
    • Creating a Business Logic Layer
    • Using Master Pages and Site Navigation for Consistent Look and Feel of the Data Samples

    Basic ASP.NET Data Reporting

    • Displaying Data with the ObjectDataSource
    • Using ObjectDataSource Declarative Parmeters
    • Programmatically Setting ObjectDataSource Parameter Values

    Master/Detail Reporting

    • Master/Detail Filtering with a DropDownList Control
    • Master/Detail Filtering with two DropDownList Controls
    • Master/Detail Filtering across two pages
    • Master/Detail Using a Selectable Master GridView with a Details DetailView

    Custom Formatting

    • Custom Formatting Based Upon Data
    • Using TemplateFields in a Gridview Control
    • Using TemplateFields in a DetailsView Control
    • Using the FormView's templates
    • Displaying Summary Information in a GridView's Footer

    Check out all of these great tutorials on the www.asp.net site here

    Over the next few weeks 25+ more tutorials will appear covering paging, sorting, editing, deleting, validation and much, much more.

    Hope this helps,

    Scott

     

  • More Atlas Resources (Videos, Articles, WebCasts and Books)

    A bunch of cool Atlas content has recently been posted to the web.  Below are a few nice links to check out:

    New Articles

    Videos/Screencasts

    Updated: Free Training CDs

    Books

    Despite being a pretty fast moving technology, some great Atlas books are starting to appear already.  Here are pointers to a few you can buy immediately:

    Contests

    Older Posts of Mine

    For tons of other cool Atlas samples and content also make sure to check out this slightly older blog post from me about Atlas content here.

    Hope this helps,

    Scott

     

  • ASP.NET 2.0 Security, Membership and Roles Tutorials

    Scott Mitchell recently published part 5 of his nice series on using the new ASP.NET 2.0 Membership and Roles features.  You can read the series here:

    -- Part 1 - Learn about how the membership features make providing user accounts on your website a breeze. This article covers the basics of membership, including why it is needed, along with a look at the SqlMembershipProvider and the security Web controls.

    -- Part 2 - Learn how to create roles and assign users to roles. This article shows how to setup roles, using role-based authorization, and displaying output on a page depending upon the visitor's roles.

    -- Part 3 - See how to add the membership-related schemas to an existing database using the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe).

    -- Part 4 - Improve the login experience by showing more informative messages for users who log on with invalid credentials; also, see how to keep a log of invalid login attempts.

    -- Part 5 - Learn how to customize the Login control. Adjust its appearance using properties and templates; customize the authentication logic to include a CAPTCHA.

    The Microsoft Patterns and Practices Group also recently released a reference sample application that shows best practices on how to implement Internet based security with ASP.NET 2.0.  You can download the sample and associated documentation here.

    For other great resources on ASP.NET 2.0 Security, please check out this ASP.NET Security Resources link that I regularly update with links to tutorials, how-to articles, and books.  I also highly recommend buying Stefan Shackow's ASP.NET Security, Membership and Role Management book:

    Stefan works on the ASP.NET team and drove much of the design for the ASP.NET 2.0 Security features.  His book does an awesome job of diving into how to maximize them.  It is one of the books I pull down from my bookshelf to regularly consult when answering questions on the forums, and it contains a wealth of knowledge to leverage.

    Hope this helps,

    Scott

     

  • DLINQ with Stored Procedures

    I've blogged a few times about LINQ and DLINQ over the last few weeks (and will be blogging a lot more about them in the future).  You can read my overview post on LINQ here, and my introduction to DLINQ with ASP.NET here.

    Sahil Malik has also been working on a great LINQ/DLINQ tutorial series on his blog here (he is also the author of the very popular Pro ADO.NET 2.0 book).  Recently he has posted several good posts about how to call SPROCs and UDFs using LINQ and DLINQ.  You can read them here:

    To quickly summarize at a high-level, to enable SPROCs with DLINQ you should pass the "/sprocs" flag to SQLMetal when you create your DAL classes:

       "c:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:. /database:Northwind /pluralize /sprocs /code:Northwind.cs

    Then assuming you have a SPROC defined like so in the Northwind database like so:

    CREATE PROCEDURE GetCustomersInCity
          @City NVARCHAR(30)
    AS
    SELECT 
          CustomerID, ContactName, Address
    FROM 
          Customers
    WHERE City = @City

    GO

    You can invoke the SPROC and get back and print out a list of name results using the below LINQ/DLINQ code:

       Northwind db = new Northwind(connectionString); 

       var contactNames = from customer in db.GetCustomersInCity("London")
                          select customer.ContactName;

       foreach (string contactName in contactNames) {
           Response.Write("Contact: " + contactName + "<br>") ;
       }

    You could also obviously databind the list of customers to an ASP.NET control like so as well (the below result will output a GridView with 3 columns):

       Northwind db = new Northwind(connectionString); 

       GridView1.DataSourcefrom customer in db.GetCustomersInCity("London")
                              select customer;

       GridView1.DataBind();   

    Subscribe to Sahil's blog to learn more.  And if you haven't yet, make sure to read my LINQ and DLINQ overview posts for ASP.NET.

    Hope this helps,

    - Scott

     

  • PageStatePersister Extensibility with ASP.NET 2.0

    One of the new extensibility features in ASP.NET 2.0 is the ability to override and customize how page viewstate is stored and persisted.  With ASP.NET V1.x, viewstate is always stored in a hidden field within the ASP.NET page, and doesn't expose an easy way to place it elsewhere.  With ASP.NET V2.0 you can optionally choose to store it anywhere.  A built-in SessionPageStatePersister is even included built-in to the product if you want to store it on the server in session state.

    Milan Negovan has a good post that talks a little more about the extensibility opportunities to use this on his blog here, as well as some of the pros/cons of the different options.

    Hope this helps,

    Scott

     

  • My TechEd Talk Now Available for Download

    Many thanks to the 1200+ people who showed up to my TechEd breakout talk today.  I had a blast and hope you did too.

    As promised, you can now download the slides from my talk here.  And you can download my demos here.  There is a "Readme.txt" file in the root of the demos .zip file that walks through how to setup the demos. 

    I have included two versions of the app I built from scratch today -- one is in the "IIS7" directory and allows to you run the application using IIS7 (and use all of the features I showed today -- including URL Rewriting).  The other is a slightly modified version of the sample that also works on IIS5, IIS6 and the built-in VS 2005 Web-Server.  It doesn't use Url-Rewriting -- but the other core features I used are all there.

    Hope this helps,

    Scott

     

  • Free Atlas Control Toolkit Test Automation Harness Published

    If you are reading this, hopefully by now you've checked out both Atlas and the Atlas Control Toolkit

    I blogged about the Atlas Control Toolkit a few weeks back.  I think it is exciting because:

    1. It is the start of what is going to be a great control toolkit of 50-100 controls built for Atlas and ASP.NET
    2. All source code for the controls is shipped free with a reusable modification license.  This makes it really easy to learn how to build new Atlas enabled controls
    3. We are using a collaborative, open source model with it and now allow community checkins and code contributions

    You can run the Atlas Control Toolkit controls online, and download it here.

    The Atlas Control Toolkit project recently moved to the new CodePlex site, which provides a free collaborative environment for building shared projects.  You can visit the Atlas Control Toolkit project page here, and can now always download the most recent source of it from this project page (one nice thing about CodePlex is that it automatically creates a new .zip file containing the latest source of a project anytime someone does a checkin or bug-fix).  You can also file and track issues and bugs here

    The Atlas Control Toolkit team is now composed of several Microsoft developers, as well as several non-Microsoft developers from the .NET community.  You can ask questions from the team and find answers on the forums here.

    One of the steps the Atlas Control Toolkit team recently took was to migrate their Javascript test framework and harness to a new custom test framework they built (among other things they wanted to make sure it could easily run on developers machines, and didn't require a specific version of VS to be installed).  They recently published it on the Atlas Control Toolkit project site with full-source, and under a flexible permissive re-use license:

    Shawn Burke has a nice write-up about it and how you can use it to perform automated testing of Atlas controls on his blog here.  You might find it useful to help unit-test your own Atlas and Ajax code.  Feel free to re-use the code and modify it however you want.

    Hope this helps,

    Scott

     

  • LLBLGen Pro Example Using ASP.NET 2.0

    Frans Bouma is the creator of the very popular LLBLGen Pro O/R mapper for .NET.  He just posted a nice walkthrough showing how to use the new version of LLBLGen (which includes some really nice new ASP.NET datasource controls for UI binding) to perform the same scenario I showed in my DLINQ post last weekend

    You can step-through his sample here, and watch a video of him doing it live here.

    June 10th Update: Ayende also wrote a blog post that also followed my steps using the .NET version of ActiveRecord.  You can read his post here.

    One of the nice things about the LINQ project is that it decouples storage and data modelling semantics from the LINQ query semantics and language support.  What this means is that you'll be able to use LINQ to perform queries against multiple data-sources -- and any O/R implementation.  So you will be able to use the elegent and productive LINQ programming model with LLBGen Pro, NHibernate, WilsonORMapper, DLINQ, or any other O/R data model you want.  You can also use LINQ against your own objects/business classes like I showed in my first LINQ post here.

    This provides developers with a tremendous amount of flexibility and knowledge re-use, and also provides O/R and data providers with great opportunities to innovate even further, and easily go after and target developers who understand the LINQ query language.

    Hope this helps,

    Scott

    P.S. Paul Wilson posted a cool control on his blog a few weeks back that showed a nice ASP.NET datasource control that he built that allows you to easily databind ASP.NET controls against his WilsonORMapper.  You can read about it here.

    P.P.S. Make sure to also check out the .netTiers templates for CodeSmith too. :-)

     

  • VS 2005 Team Edition for Database Professionals

    At TechEd this week Microsoft is going to be showing off the new VS 2005 Team Edition for Database Professional tool which is now part of the Visual Studio Team System line of products.  You can learn more about it here.  The first CTP build will be available to MSDN subscribers on June 11th. 

    It provides a number of pretty cool features including:

    • Integrated source code control for database schema, scripts and sprocs
    • Refactoring support in the database (allowing you to change objects and have everything update)
    • Automated unit test support for SPROCs, T-SQL, etc
    • T-SQL code editor support (including support for viewing execution plans)
    • Schema and data compare support for quickly comparing databases
    • Data generators for loading up databases with pre-populated test data

    All the above features are also now integrated within the Visual Studio IDE, and can be used with the team collaboration features of Visual Studio Team System.  Learn more about them all here.

    Hope this helps,

    Scott

     

  • Visual Studio Tips/Tricks (Free Articles + Book)

    Steve Smith published two cool articles on the ASPAlliance site last week that show how to get more out of Visual Studio and Visual Web Developer.  You can read them both here:

    If you are looking for a good book that shows you how to get even more out of Visual Studio 2005, I'd recommend you also check out James Avery's Visual Studio Hacks: Tips & Tricks for Turbocharging the IDE.  It has 4.5 (out of 5) stars on Amazon with 21 comments (always a very good sign for a book), and is packed with useful tips/tricks to get the most out of VS.  You can read 5 of the book's tips/tricks online for free on the O'Reilly web-site here, and learn more about the book on its web-site here.

    Hope this helps,

    Scott

     

  • Using DLINQ with ASP.NET (Part 2 of my LINQ series)

     

    Last month I wrote about the new LINQ language features for VB and C#.  My blog post introduced some of the core concepts of LINQ, and demonstrated how you can use LINQ to perform rich queries over standard .NET business classes and object collections.  I also showed how you could easily bind the results of these queries against ASP.NET controls.  You can read all about how to-do this here.

     

    One of the really powerful aspects of LINQ is that it can be used against any type of object or data source, and provides a consistent programming model for doing this.  LINQ ships with implementations that support LINQ against regular object collections, databases, entities, and XML sources.  Because LINQ supports rich extensibility, developers can also easily integrate LINQ with other data sources and providers as well. 

     

    In this blog post I’m going to provide a quick overview of some of the key LINQ for databases concepts (aka DLINQ), and then walk through a sample that shows how you can start to use LINQ against a database within an ASP.NET 2.0 application. You can download the LINQ May CTP from here and use the steps below with VS 2005 or the free Visual Web Developers Express edition to follow along (note: the LINQ May CTP runs in VS 2005 and does not touch any existing binaries).  You can also download the completed samples I build below from here.

     

    Reviewing some DLINQ concepts

     

    DLinq provides a run-time framework for managing relational data as objects. It does this by translating LINQ queries into SQL for execution by the database and then translates the tabular results back into objects you define. Your application can then manipulate these objects however you want to.  While you do this DLinq will track any changes you make to them, so that you can optionally submit changes back to the database (for update, insert, and delete operations).

     

    DLINQ allows you to define the object model and class structure that best represents your application data.  For example, if you have a Customers database table with columns “CustomerId”, “City”, and “CustomerName”, you could optionally create a “Customer” class that represented it like so:

     

    public class Customer

    {

        public string CustomerId;

        public string City;

        public string CustomerName;

    }

     

    Note that you can use whatever naming pattern you want in your classes -- you aren't required to have the same names as in the database. 

     

    DLINQ allows you to map classes to a database schema in two ways.  The first option is by adding attributes to the classes that indicate where and how they should be stored in the database (this is called “Attribute Based Mapping”).  Alternatively you can specify the mapping using an external XML file.  The external XML file enables the underlying database schema to be kept separate from the code, and even allows runtime schema mapping changes on the fly (meaning you do not need to recompile an assembly using DLINQ to modify the database storage schema being used).  Developers can choose whichever mapping approach works best for them and their projects. 

     

    In addition to supporting single table mappings, it is also possible to easily define relationships between tables using DLINQ.  In a relational database this is typically modeled using foreign-keys referring to primary keys in other tables.  DLINQ allows developers to define “Association Relationships” between classes to express relationships.  This allows a developer, for example, to write “Customer.Orders” to reference the collection of orders for the customer instance.

     

    Important: It is not necessary to manually define your table mappings or relationships.  The May CTP drop of DLINQ ships with both a command-line utility and a Visual Studio data designer to make defining these relationships and mappings simple (it will take us less than 30 seconds to define a complete DLINQ mapping for the Northwind database in our sample below).

     

    Once data mappings and relationships are defined, developers can then easily write LINQ code to perform queries and updates against a database.  For example, the below code uses the SQL Northwind database to retrieve all customers from London, and then prints out the Customer Name, as well as a hierarchical sub-listing of each of the customer’s orders to a page:

     

    Northwind db = new Northwind(connectionString);

     

    // Query for all customers in london

    IEnumerable<Customer> customers = from cust in db.Customers

                                      where cust.City == "London"

                                      select cust;

     

    // Fetch each customer and output its name and order history

    foreach (Customer cust in customers) {

     

        Response.Write ("Customer = " + cust.CustomerName);

     

        foreach (Order order in cust.Orders) {

            Response.Write("---- OrderID: " + order.OrderId);

        }

    }

     

    Note how the Customer and Order classes are strongly-typed, and how the Customer class has an “Orders” association relationship that uses the ForeignKey/PrimaryKey relationship between the Customers and Orders tables in the database.

     

    We can also then easily write code to retrieve an individual Customer and simultaneously update both its ContactName and add a new Order for it in the system like so:

     

    Northwind db = new Northwind(connectionString);

     

    // Fetch a specific customer

    Customer cust = db.Customers.Single(c => c.CustomerID == "ALFKI");

     

    // Change the name of the contact

    cust.ContactName = "ScottGu";

     

    // Create and add a new Order to the customer's orders collection

    Order order = new Order();

    order.OrderDate = DateTime.Now;

    order.ShipCity = "London";

     

    cust.Orders.Add(order);

     

    // Save all the changes to database

    db.SubmitChanges();

     

    We do not need to write any additional data access code or define any SQL statements for the above two samples to work.  Instead, I can program and work with these objects for my data access, and have DLINQ perform the underlying data statements for me.  The compiler will automatically perform syntax checking against my queries/code and will give me warnings/errors if I do things wrong (for example: mistype a column name, misuse a double as an integer, try to convert an inappropriate string to a date, etc).  I can use the debugger to inspect any value.  And with the next release of VS I will get full intellisense completion when writing these queries and operations.

     

    DLINQ supports Stored Procedures, Views, and User-Defined Functions, and allows developers to drop-down and add custom SQL where necessary.  Developers can add additional, non-database based, properties and methods to data classes to extend their semantics, and can also add both property and entity validation/business rules to them as well (the partial class feature in VB and C# makes doing this clean and easy).  Transactions are also fully supported, including two-phase commit transactions where the DLINQ operations can be enlisted in a broader transaction with multiple databases or storage providers.

     

    In short – there is a lot of cool stuff there.  There is a lot of documentation that comes with the May LINQ CTP that you can read to learn more. 

     

    What I’m going to do below for the rest of this post is walk through a few step-by-step instructions on how to start using the May LINQ CTP with DLINQ in an ASP.NET app.

     

    Step 1: Create an ASP.NET LINQ Project

     

    To begin with, we’ll create a new LINQ-enabled ASP.NET web site in VS 2005 or the free Visual Web Developer Express tool.  To-do this, make sure the May CTP build of LINQ is installed, and then choose File->New Web Site and select the “LINQ ASP.NET Web Site” template:

     

     

    We now have a project created that references the LINQ and DLINQ assemblies, and can use the C# or VB LINQ-enabled compilers.

     

    Step 2: Create a DLINQ enabled object model for the Northwind database

     

    We’ll use the “Northwind” database sample that comes with SQL Server for our LINQ/DLINQ code below. 

     

    We’ll start by creating an object model that maps to the Northwind database.  We can do this one of three ways:

     

    1)     Write this by hand

    2)     Use the new VS DLINQ designer to graphically define the class organization and relationships

    3)     Use the built-in “SQLMetal.exe” command-line utility to create it for us

     

    For this sample walkthrough I’m going to use #3.  To create the mapping I simply need to type the below statements in a command-prompt to create this for the Northwind database on my local box:

     

        >> cd c:\Program Files\LINQ Preview\Bin

        >> sqlmetal /database:Northwind /pluralize /namespace:Northwind /code:Northwind.cs

     

    SqlMetal will infer the appropriate classes from the database metadata, and by default add appropriate validation constraint checks (NOT NULL, Column Types and Size Limits, etc) to the object model.  Because I’ve specified the /pluralize parameter option, it will use some default naming logic conventions to create the property and table names – for example creating a “Customer” class that maps to the “Customers” table (obviously you can go in and override these if you don’t like the defaults). 

     

    We could optionally also specify a /map:[filename] parameter to control whether the database mappings are stored using code attributes or in an XML metadata file.  We could also indicate that stored procedures, functions and views should be mapped via the /views, /sprocs, /functions parameter switches.

     

    The result of the SQLMetal’s code generation will be saved within the “Northwind.cs” file we specified and scoped within the “Northwind” code namespace above.  We’ll then want to copy this file under the “/app_code” directory within our new ASP.NET project:

     

     

    The last step we’ll then want to do is to add a “<connectionStrings>” section within the web.config of our application to configure our database connection information:

     

      <connectionStrings>

        <add name="Northwind"

             connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"

             providerName="System.Data.SqlClient"/>

      </connectionStrings>

     

    This will allow us to easily change the database deployment location later.  With ASP.NET 2.0 we can also now optionally encrypt the web.config values for secure deployment.  ASP.NET 2.0 also installs a built-in admin extension to the IIS admin tool that allows IT administrators easily manage the connection strings during and after deployment as well.

     

    Once the above steps are done, we are ready to use our newly created Northwind DLINQ data layer anywhere within our application.

     

    Step 3: Build a page that uses our DLINQ layer

     

    To start off with, we’ll create a new page within our project called “Sample1.aspx”. 

     

    Within the Sample1.aspx file we’ll add a GridView server control and explicitly define a few columns to display:

     

    <%@ Page Language="C#" CodeFile="Sample1.aspx.cs" Inherits="Sample1" %>

     

    <html>

    <body>

        <form id="form1" runat="server">

       

        <h1>Northwind Customers</h1>

       

        <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">

           <Columns>

              <asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />

              <asp:BoundField HeaderText="Name" DataField="CompanyName" />

              <asp:BoundField HeaderText="City" DataField="City" />

              <asp:BoundField HeaderText="State" DataField="Region" />

           </Columns>

        </asp:GridView>

       

        </form>

    </body>

    </html>

     

    Within the Sample1.aspx.cs code-behind file we’ll add a “using System.Query” statement at the top of the file, and then add LINQ code within the Page_Load() event to retrieve all US customers from the Northwind database, sorted by CompanyName, and programmatically bind the results to the GridView control:

     

    using System;

    using System.Configuration;

    using System.Web.UI;

    using System.Query;

     

    public partial class Sample1 : System.Web.UI.Page {

     

        protected void Page_Load() {

     

            string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

           

            Northwind.Northwind db = new Northwind.Northwind(connectionString);

     

            GridView1.DataSource = from customer in db.Customers

                                   where customer.Country == "USA"

                                   orderby customer.CompanyName

                                   select customer;

     

            GridView1.DataBind();

        }

    }

     

    When we save the page and access it from a browser we’ll get this output:

     

     

    Disclaimer: I will leave it as an exercise to the reader to write a nice CSS stylesheet to make it look pretty – but hopefully you get the point of how the functionality works. J

     

    Step 4: Data Shaping and Projections

     

    In the step above we queried the database for Customers and returned back a sequence of “Customer” objects.  If you look at the underlying SQL statement executed (just attach the SQL Profiler tool while you run the app to inspect every data access statement), you’ll notice that it is returning all of the columns from the Customer table – including the ones we aren’t displaying.  This makes sense given that our query above asked for a sequence of Customer objects, although it isn’t ideal for this particular page scenario since we only care about four of the Customer columns.  In cases where you have columns containing a lot of data (for example: blobs of images, or large text strings, etc) this can lead to performance issues since you might end up pulling back more data than you really want/need.

     

    One of the nice things about LINQ and DLINQ is that we aren’t constrained to having to always return fully populated entity objects.  Instead we can shape and transform the data however we want, without having to drop-down to write SQL directly.  LINQ and DLINQ use the new “anonymous type” features in C# and VB to provide an elegant way to express this (to learn more about anonymous types in LINQ read my previous LINQ blog post).

     

    For example, I could modify our query above like so to just fetch the 4 columns we are going to display like so:

     

    GridView1.DataSource = from customer in db.Customers

                           where customer.Country == "USA"

                           orderby customer.CompanyName

                           select new {

                               CustomerID = customer.CustomerID,

                               CompanyName = customer.CompanyName,

                               City = customer.City,

                               Region = customer.Region

                           };

     

    If you re-run the page now and look at the SQL Profiler, you’ll notice that DLINQ has automatically changed the SQL executed to a more optimized statement that only returns the 4 column values we need from the database:

     

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region]

    FROM [Customers] AS [t0]

    WHERE [t0].[Country] = @p0

    ORDER BY [t0].[CompanyName]',N'@p0 nvarchar(3)',@p0=N'USA'

     

    We can also use this database shaping technique to run aggregate functions and add computed values to our result as well.  For example, I could modify the query above to also add the number of orders the customer has made, and the most recent order date by writing it like so:

     

    GridView1.DataSource = from customer in db.Customers

                           where customer.Country == "USA"

                           orderby customer.CompanyName

                           select new {

                               CustomerID = customer.CustomerID,

                               CompanyName = customer.CompanyName,

                               City = customer.City,

                               Region = customer.Region,

                               NumOrders = customer.Orders.Count,

                               LastOrder = customer.Orders.Max(o => o.OrderDate)

                           };

     

    Note that both of these new values are computed off of the rows in the Orders table specific to the referenced Customer (the Order table has a FK relationship to Customers that SQLMetal automatically detected and used to setup an association).  The above LINQ query will cause the below SQL statement to execute in the database:

     

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region], (

        SELECT COUNT(*)

                FROM [Orders] AS [t1]

                WHERE [t1].[CustomerID] = [t0].[CustomerID]

        ) AS [NumOrders], (

        SELECT MAX([t2].[OrderDate])

                FROM [Orders] AS [t2]

                WHERE [t2].[CustomerID] = [t0].[CustomerID]

        ) AS [LastOrder]

    FROM [Customers] AS [t0]

    WHERE [t0].[Country] = @p0

    ORDER BY [t0].[CompanyName]',N'@p0 nvarchar(3)',@p0=N'USA'

     

    Notice how DLINQ automatically translated the count and Max date functions into the single SQL query to execute -- it did not need to pull down all of the records to compute it (making it very efficient).

     

    And now when we run this page it will generate this html output:

     

     

    Step 5: Hierarchical Binding

     

    Data shaping isn’t limited to adding only scalar values to the anonymous classes we return.  We can also return additional sub-collections of objects as part of our LINQ query.  For example, we could modify our query like so to return a sub-collection of the most recent 5 orders for each customer (note how the Take() LINQ aggregate method can be used to return only 5 results from the database):

     

    GridView1.DataSource = from customer in db.Customers

                           where customer.Country == "USA"

                           orderby customer.CompanyName

                           select new {

                               CustomerID = customer.CustomerID,

                               CompanyName = customer.CompanyName,

                               City = customer.City,

                               Region = customer.Region,

                               NumOrders = customer.Orders.Count,

                               LastOrder = customer.Orders.Max(o => o.OrderDate),

                               Orders = customer.Orders.OrderByDescending(o => o.OrderDate).Take(5)

                           };

     

    This query returns a collection of anonymous objects with each instance containing 7 properties – one of which is a sub-collection of at most 5 Orders associated with the customer.  I can then update my GridView like below to hierarchically bind the Customer’s orders within a templated column of the Grid to generate a bulleted list of “Recent Orders”:

     

    <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">

     

      <Columns>

     

         <asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />

         <asp:BoundField HeaderText="Name" DataField="CompanyName" />

         <asp:BoundField HeaderText="City" DataField="City" />

         <asp:BoundField HeaderText="State" DataField="Region" />

         <asp:BoundField HeaderText="NumOrders" DataField="NumOrders" />

               

         <asp:TemplateField HeaderText="Recent Orders">

               

            <ItemTemplate>

                       

              <ul> 

                 <asp:Repeater datasource='<%# Eval("Orders") %>' runat="server">                   

                    <ItemTemplate>

                       <li>

                         <%# Eval("OrderID") %>

                         (<%# Eval("OrderDate", "{0:dd MMM yyyy}")%>)

                       </li>

                    </ItemTemplate>

                           

                 </asp:Repeater>               

              </ul>

                   

           </ItemTemplate>

                                                          

        </asp:TemplateField>

               

      </Columns>

     

    </asp:GridView>

     

    Which will cause this page to be output like so:

     

     

    DLINQ in the sample above was smart and optimized the data access to only hit the database twice – once to retrieve the top-level customer data, and then once to retrieve all of the needed orders for our particular customers (DLINQ then split this orders result and associated each order correctly with the appropriate customer).  This perf optimization avoided us having to hit the database separately to populate and display each individual customer order collection (note that we could also have alternatively expressed to DLINQ to lazy populate the Orders if we wanted this behavior instead).

     

    Step 6: Enable Basic Pagination

     

    The previous step is nice because we can now see the 5 most recent orders for each customer, but the downside is that it has expanded the html height quite a bit.  To make the listing a little cleaner we’ll go ahead and enable paging support on the GridView, set the pagesize to 3 rows per page, and handle the appropriate page event handler in our code-behind to re-bind the Grid as appropriate when users click on the new page index at the bottom of the grid.

     

    For completeness, here is what the entire .aspx file looks like with the Gridview with hierarchical binding and paging enabled:

     

    <%@ Page Language="C#" CodeFile="Sample4.aspx.cs" Inherits="Sample4" %>

     

    <html>

    <body>

        <form id="form1" runat="server">

       

        <h1>Northwind Customers</h1>

       

        <asp:GridView ID="GridView1" AllowPaging="true" PageSize="3"

                      AutoGenerateColumns="false" runat="server"

                      OnPageIndexChanging="GridView1_PageIndexChanging">

     

           <Columns>

     

              <asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />

              <asp:BoundField HeaderText="Name" DataField="CompanyName" />

              <asp:BoundField HeaderText="City" DataField="City" />

              <asp:BoundField HeaderText="State" DataField="Region" />

              <asp:BoundField HeaderText="NumOrders" DataField="NumOrders" />

                   

              <asp:TemplateField HeaderText="Recent Orders">

                   

                  <ItemTemplate>                       

                     <ul> 

                        <asp:Repeater datasource='<%# Eval("Orders") %>' runat="server">

                               

                           <ItemTemplate>

                              <li>

                                 <a href="todo"><%# Eval("OrderID") %></a>

                                 (<%# Eval("OrderDate", "{0:dd MMM yyyy}")%>)

                              </li>

                           </ItemTemplate>

                               

                        </asp:Repeater>               

                     </ul>

                       

                  </ItemTemplate>

                                                               

             </asp:TemplateField>

                   

          </Columns>

     

        </asp:GridView>

      

        </form>

    </body>

    </html>

     

    And here is then the entire code-behind:

     

    using System;

    using System.Configuration;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Query;

     

    public partial class Sample4 : System.Web.UI.Page {

     

        void BindData() {

     

            string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

           

            Northwind.Northwind db = new Northwind.Northwind(connStr);

     

            GridView1.DataSource = (from customer in db.Customers

                                   where customer.Country == "USA"

                                   orderby customer.CompanyName

                                   select new {

                                      CustomerID = customer.CustomerID,

                                      CompanyName = customer.CompanyName,

                                      City = customer.City,

                                      Region = customer.Region,

                                      NumOrders = customer.Orders.Count,

                                      LastOrder = customer.Orders.Max(o => o.OrderDate),

                                      Orders = customer.Orders.OrderByDescending(o => o.OrderDate).Take(5)

                                   }).ToList();

     

            GridView1.DataBind();

        }

     

        protected void Page_Load() {

     

            if (Page.IsPostBack == false)

                BindData();

        }

     

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) {

     

            GridView1.PageIndex = e.NewPageIndex;

            BindData();

        }

    }

     

    And now I have a pageable GridView, showing both relational data and calculated aggregate data, both in a tabular and hierarchical way:

     

     

    Summary

     

    The combination of LINQ and DLINQ provide a really powerful way to-do data access.  As you’ve seen above, it takes the grunge work out of data access code, and leaves you with an elegant way to easily perform data operations.  You also get compiler type-checking, code intellisense and debug support for everything (along with a WYSIWYG designer for both DLINQ relationship mapping and obviously ASP.NET pages).  The combination of LINQ/DLINQ and the rich control event model in ASP.NET provides a really productive and clean way to quickly build data web apps that also scale really well. 

     

    In my next few LINQ posts I’ll show how we will be able to build on top of the concepts I demonstrated above to easily add sorting, in-line editing, deleting, and selection support over our customer data – and also show how to easily Ajax enable it with Atlas.

     

    Hope this helps,

     

    Scott

     

    P.S. You can download the LINQ May CTP from here and use it with both VS 2005 and the free Visual Web Developers Express edition.  You can also download the completed samples I built above here.

     

     

     

  • Supporting Templates with ASP.NET User Controls

    ASP.NET User Controls (those files with the .ascx extensions) provide a really easy way to encapsulate functionality within an application, and allow it to be re-used across multiple pages and projects (note: for a tutorial on how to create re-usable user control libraries with the VS 2005 Web Application Project check out the C# one here, and the VB one here).

    Most developers who create user-controls know that it is possible to easily expose public properties from them so that you can customize them from a page that is using them.  This allows a page developer to declare properties like so on the user-control tag (as well as access them from the code-behind):

       <MyLibrary:MyUserControl id="UC1" someproperty="somevalue" runat="server"/>

      

    What is less well known is that in addition to exposing standard properties from user controls, you can also expose template properties.  This allows a page developer to pass templates to the user control like so:

     

     

       <MyLibrary:MyUserControl id="UC1" someproperty="somevalue" runat="server">

      

          <TitleTemplate>

               Some custom content I want rendered in the title...

          </TitleTemplate>

     

          <ItemTemplate>

               Here is a calendar: <asp:calendar id="cal1" runat="server" />

          </ItemTemplate>

     

       </MyLibrary:MyUserControl>

     

    This allows you to provide much richer UI customization and further re-use.

    Robert Seder has posted a nice blog post here that describes how to-do this.

    Update: Kris also pointed me at some MSDN documentation that shows the syntax to support this with VB.  You can read it here.

    Hope this helps,

    Scott

     

  • VS 2005 Web Application Projects, MSBuild, and Continuous Integrations

    One thing that I thought would be worth spending a few minutes writing about is the built-in MSBuild support provided with the VS 2005 Web Application Project Model option.

    The project file used with VS 2005 Web Application Projects is a standard XML-based MSBuild file (just like a class library or any other project type within VS 2005), and allows you to add any custom MSBuild action with it.  Here is a pointer to an old blog post I wrote that listed a number of free MSBuild actions you can download and incorporate (including ones to create IIS application pools, FTP content, and provision databases).  When you do a build within the Visual Studio IDE, it will use the MSBuild Project file and launch the appropriate actions as part of the build.

    The .NET Framework Redist actually includes and installs the core MSBuild utility system, as well as the VB and C# command-line compilers.  This means that you can copy a MSBuild based solution or project file to any machine that has the .NET Framework installed and build it from the command-line (you can just type: "msbuild mysolution.sln" to build every project within the solution from the command-line).  This does not require that Visual Studio is installed on the machine.  Two common scenarios where this is very useful are for centralized build servers, and for continuous integration scenarios. 

    If you aren't familiar with the concept of continuous integrations, I'd definitely recommend spending time reading up on it (especially if you are in a team development environment where I think it is critical).  In a continuous integration environment you setup a machine that monitors your source code repository and automatically syncs and builds the full code-base, and runs all unit tests against the produced build, whenever a checkin is made.  It then sends an email report to the developer (or entire team) on the results.  The benefit with this is that it helps ensure the source tree is kept in an always clean/buildable state, and avoids issues where developers forget to checkin some files, or have other changes on their machines that impact the stability or success of the build.  We use this model heavily within Microsoft. No checkin is allowed to reach the ASP.NET feature team branch until it has been synched, built and tested on a centralized machine (which we affectionately call our "buddy machine") first.

    The new Team Foundation Server that ships as part of the Visual Studio Team System family can be configured to enable continuous integration support.   Alternatively, you can also use the very popular CruiseControl.NET to achieve this, which is a free open-source product that is hosted here.  You can use both in conjunction with MSBuild files to launch and automate the build process from the command-line without needing Visual Studio installed on the machine.

    One thing to be aware of with VS 2005 Web Application Projects is that the MSBuild project files created have an import reference to a VS 2005 Web Application Project .targets file.  You'll want to make sure you copy this .targets file to any build servers that don' t have VS 2005 and the Web Application Project option installed to ensure that command-line builds of these project types work fine. 

    Scott Allen talks about the specifics of copying this VS 2005 Web Application Project .targets file (including the disk location where it is stored) in more detail on his excellent blog here.

    Hope this helps,

    Scott