Archive for the ‘Linq’ Category

Inserting new records with the ListView & LinqDataSource

August 16, 2007

Inserting new records with the LDS and LV is a snap… if you want to stick with an out-of-the box deployment.  Customizing it can be somewhat more challenging than advertised, though, which is what separates the programmers from the script-kiddies, I suppose.   It was in pursuit of this noble distinction that I may have fell upon an idea that may someday lead to a ‘best practice’ for LINQ.

 The out-of-the box way to do an insert is quite straightforward.  You simply enable inserts on your LinqDataSource via the EnableInsert=’true’ attribute (or through the GUI wizard if that’s how you roll); you then define an <InsertItemTemplate> that calls a bind for each of the fields you require and have a button with the CommandName set to “Insert” and you’re down the road.  I’m not going to bother with the sample here, because it’s really easy and if you need a code sample, just do it once with the wizard and you’re questions will be answered… for simple scenarios.

What about non-trivial examples?

I define a trivial example as a scenario where you’re inserting to a single table that has no foreign key relationships and take all of the input from on the page.  In other words, a scenario that’s really unlikely to happen in a production environment.  I don’t know about you guys, but I like to keep my databases normalized where possible for performance and extensibility. I also make use of things like ASP.Net Profiles which don’t (out-of-the box; I know creating a custom profile provider is an option) map well to simple SQL queries. 

For the more complex situations, you have a few options which I’ll cover, and I’ll round out with a fairly elegant solution that I’ve come up with that I like quite a bit.

  1. Use the LinqDataSource’s OnInserting partial method to inject some logic.  This works pretty well, but you’re still a bit limited.  The main object you have access to is whatever data object you defined.  For example, in the previous article I used a Contact object which was mapped to my Contacts table from the NWdb.  In that example, the OnInserting signature has to look like this: void LinqDataSource1_Inserting(object sender, LinqDataSourceInsertEventArgs e) where e defines a property called NewObject that can be cast to your data object (Contact in my case).  The downside is that you may have to go through some convolutions to get ahold of one of your page controls, depending on how deeply nested it is in the control tree.  This makes for some developer angst and some ugly code.  Further, you’re making a page-level solution to something that may or may not be a page level issue.
  2. Use the ListView’s ItemInserting event.  This is another page-specific option (which makes me like it less, though there are times when the insert action needs to be page-specific I suppose), but it has the advantage that its signature defines a ListViewInsertEventArgs parameter which in turn has an Item property which maps specifically to the container of your insert item temnplate.  It has a control tree that gives you more direct access to your input controls which can be useful in some scenarios.  You also get access to a Values property, which is a dictionary that lets you pass either an index or a name of a field and get the value back, similar to the Session variable. 
  3. Override the data object’s insert behavior.  This is by far my favorite method as a baseline and I’ll tell you why shortly.  You can easily override it using the DBML document, by right-clicking the table name and selecting “Configure Behavior”. You then have the option to replace the runtime generated Insert, Update or Delete behiavior with a stored procedure that you’ve already defined and added to the DBML.  You can also do it using a partial class by using a partial method called InsertComment, which has the added advantage of being more impervious to changes / rebuilds of your DBML. 

Ok, so why does #3 make the most sense to me?

Well, this may be me coming from a database background in my twisted youth, but I feel that most developers these days don’t separate their concerns well enough when it comes to the data layer.  The db engine is seen mainly as a means to store stuff, and no logic should go in there. Where normalization occurs, it’s generally seen as a necessary evil.  In recent examples in LINQ, I’ve seen a lot of folks use EntityRefs thing to get access to the foreign keyed tables, which is cool, but the LINQ designer by default will still include the foreign key column as a property of the data class which (to me) is bringing the dirty laundry of the Database up into the data access  and possibly business logic or even presentation tiers.  bleh, I say.

Instead, what I’ve taken to doing is basing my DBML (Ling to Sql ) entities on views instead of on tables.  I can create a view based on the data that the application needs / expects, without pushing things that really are database-specific into the mix.   The only exception to that is that I’ll sometimes include the primary key of the main database table that’s involved in the view if I’m going to use the view for inserts, updates, and deletes.  The DBML designer complains if you haven’t identified a primary key and you want to do a CUD operation, but interestingly the primary key (single or multiple column) can be defined as properties of your DBML object, and they do not have to map to the underlying db’s primary key, so long as whatever combination you come up with ends up being unique in all cases.  Given that, my preference is to plan to have that unique combination so that I can keep *all* db-specific info out of the application. 

Using a view really helps decouple that highly normalized table structure from the application that’s decidedly abnormal, but I’ve heard many folks ask, “Well, why not just use a stored procedure to retrieve your data?”.   The answer is,  I might do that too, especially in cases where I want my selects to have ‘side effects’ (e.g. counting hits).  But one thing I really dig about LINQ is that deferred execution model.  If I execute a sproc I end up immediately dumping the output into a C# collection of some sort, because sproc output is not table-valued inside the database.  That means, then, that any further sorting, filtering, customizing of the data I want to do will be done on the procedural side of the house; with set-based operations I’d really prefer that to happen in the database where possible.  Using a view gives me the option of going either way. I can query/refine/refit the information pretty dynamically via LINQ code that gets pushed into the db for execution, and then when I’m ready to manipulate my result set I can call the To<whatever> function to pull the data out of the db and into my C# object, or whatever. 

As many of you know, views can also be indexed, which makes those subsequent queries are also that much more efficient (so long as I know my index business), and you can secure them independantly of the underlying tables, much like Sprocs, so they can do that layer for you as well.

The generalized pattern for me, then, is to have the application define its needs independantly of the storage form.  The db design is then based on the optimized form of that, using a view as the outgoing interface of the db in most cases, and sprocs as the inbound interface in most cases.  This means that generally, any given set of data that the app needs will have one view and three sprocs, though as stated before there are cases where I’d use sprocs for selecting as well, I just wouldn’t use them as the default.

The only real point of coupling between the db and the app, then, is in understanding what columns need to be typed as, and what combination of columns in a given set will define uniqueness for that set. 

The db design team then goes about his business of building the tables, views, sprocs, and indices for the db based on those guidelines.  He lets the app team verify that all the needs are met (at this round anyway) and the app team uses the Linq to Sql file (dbml) to pull all the views and relevant sprocs into the app.  The views then get their unique column combinations marked as Primary Keys in their properties (either via the Column(id=true) attribute or in the properties pane of the designer), and have their behavior overridden as described earlier to map inserts, updates, and deletes to their relevant sprocs. 

In Conclusion…

I hope that I”ve been clear and that I’ve made you think a bit.  I feel that LINQ has some great potential for speeding up designs and code, but that doesn’t preclude good n-Tier design and planning.  Databases are very good and very efficient at set based operations, so let them handle that piece of it by pushing as much of those types of operations into the db engine.  In a later article, I plan on showing some performance measures to back up my intuition.

<asp:ListView>,possibly the perfect server control

August 1, 2007

Well, this is my first post, so please be gentle.  I have had this blog open for a while now, but didn’t want to lead off with the usual “who I am and why I’m here” stuff, so I’ve been waiting for the right topic to come along to blog about. 

This morning, I found it.

I finally downloaded the latest Visual Web Developer (VWD from here) 2008 beta from Microsoft this morning and started using it, playing around with the new server controls for ASP.Net and so on.  I normally use Visual Studio 2005 rather than VWD, but I had problems getting a clean download of that which didn’t have corrupted files after burning to CD.  So, let me get to the meat of what my title brought you here for.

 After reading Scott Guthrie’s excellent series on Linq and in particular the one on the <asp:LinqDataSource>, and building the example out myself, I was motivated to try out the new <asp:ListView> which he hints about at the end of that article.  The bottom line is that this control will take the .Net community like the Gridview did back when 2.0 first came out.  You heard it here first, and mark my words.  I considered trying to grab the domain name but thought better of it.

 A little overview…

What is the ListView?  Well, the best way I can describe it is that it’s a GridView that doesn’t force you into a table.  It has all the paging, updating, inserting, deleting features of the GridView as well as the now familiar ItemTemplate, EditItemTemplate, AlternatingItemTemplate kinds of nested structures, BUT it confers no markup of its own, that’s entirely up to you as the developer.  In short, it’s an asp:Repeater on GridViewSteroids.

Where’s my Elephant? (or, how ’bout an example, Raptor?)

OK, so an example is in order.  Let’s say you want to do the unthinkable (ha!) and build semantically meaningful markup to, for example, have a contacts list.  Let’s say I wanted to build a simple contact list, but I wanted to use a microformat to do it, rather than something I created on my own.

So to be a touch retro, I’ll use the Northwind db as my database for this, I’ll use a LinqDatasource to access that data over a Linq designer document (all that’s covered in Scott’s blog referenced above, so I’ll skip it).  Suffice to say that my DBML diagram looks like this:
Contacts dbml
Yeah, simple, I know. 

Anyway, so I’m going to go with a simple hCard microformat to represent each of these guys:

<div class=”vcard”>
<div class=”fn org”>/*Organization Name*/</div>
<span class=”fn n” lang=”en”>
<span class=”given-name”>/*first name*/</span>
<span class=”additional-name”>/*middle name*/</span>
<span class=”family-name”>/*last name*/</span>
    <div class=”adr”>
<div class=”street-address”>/*Full street addr.*/</div>
<span class=”locality”>/*City*/</span>,
<abbr class=”region” title=”/*State name*/”>/*state abbreviation*/</abbr> <span class=”postal-code”>/*zip*/</span>
<div class=”country-name”>/*country*/</div>
<div>Phone: <span class=”tel”>/*telephone*/</span></div>
<div>Email: <span class=”email”>/*email addr*/</span></div>
Ok, so that’s how I want to represent one contact. A list of contacts, to me, would be an ordered list.  Why an ol?  well, b/c I plan on enabling paging and sorting, which means that it has  an ordered structure to it. So, planning is done, onto the actual implementation!  Starting with an open project in VWD 2k8 with .Net framework level set to 3.5, I do the following.

  1. Add LinqDataSource.  Simply put, open my toolbox, double click on the LinqDataSource and it adds it to the page.  To make it easy, I even used their Configure Datasource wizard (one of the smart tags in Design or Split view) to point it at the Contacts data object, to name it ContactsDatasource, and to enable Insert, Delete, and Update.  Otherwise I took the defaults.
  2. Add a ListView.  Again, keeping it simple, I just dropped a Listview from the toolbox over to the page.  I am going to go into the markup now to show you how easy it is, even without wizards (though their wizards are pretty cool).  So, to begin I add in the base tag information:
    <asp:ListView id=’ContactsList’ DataKeyNames=’ContactID’ InsertItemPosition=’LastItem’ DataSourceID=’ContactDatasource’ runat=’server’><
    Straightforward and simple, just like the basic GridView tag. The main thing that’s new here is the InsertItemPosition, and that simply refers to where the option to insert a new data item will go, where that template will go in the flow of the list, either first or last.
  3. Add the Layout Template.  The Layout template specifies what the container tags will look like, and also if you want to have anything before or after it.  this is a page-level thing, so if you have paging set up, then the LayoutTemplate is where you’ll put your pager control.   
    <ol id="itemContainer" runat="server"></ol>
    <div><asp:datapager ID="DataPager1" runat="server">
    <asp:nextpreviouspagerfield ButtonType="Button" FirstPageText="First"
    LastPageText="Last"  NextPageText="Next" 
    PreviousPageText=”Previous”ShowFirstPageButton=”True” ShowLastPageButton=”True” />
    </LayoutTemplate>So, what we’re showing here is the layout of essentially each ‘page’ of data. I want an ordered list, so I make an ol with the ID of itemContainer.  As near as I can tell that id is required for the control to key off of.  The datapager is a new control which sends paging commands to the datasource control to get the datasource to fetch the next page of data. In this case, I used one of their out of the box impllementations, but this control also supports a templated control which means you can certainly roll your own if you like.
  4. Add an ItemTemplate.  Just like the .net 2.0 databound controls, the ListView has a number of templates depending on what you’re trying to do.  I’m just going to show the ItemTemplate today, b/c I’m running out of gas on the typing here, but basically in the item template you put whatever markup and Evals that you want to make the information lay out the way you wanted to. Here’s where my microformat comes into play:
    <div class=”vcard”>
    <div class=”fn org”><%# Eval(“CompanyName”) %></div>
    <span class=”fn n” lang=”en”><%# Eval(“ContactName”) %></span>
    <div class=”adr”>
    <div class=”street-address”><%# Eval(“Address”) %></div>
    <span class=”locality”><%# Eval(“City”) %></span>, <abbr class=”region”><%# Eval(“Region”) %></abbr><span class=”postal-code”><%# Eval(“PostalCode”) %></span></div>
    <div class=”country-name”><%# Eval(“Country”) %></div>
    <div>Phone: <span class=”tel”><%# Eval(“Phone”) %></span></div>
    <asp:Button ID=”EditButton” runat=”server” CommandName=”Edit” Text=”Edit” />
    <asp:Button ID=”DeleteButton” runat=”server” CommandName=”Delete” Text=”Delete” />
    This should look familiar to you if you’ve ever worked with a Repeater or DataList, but the key difference is that you also get built-in paging support as well as the automatic alternating item support and insert, delete, edit functionality.

In Conclusion

I have to say that I planned on doing the whole thing in one shot, but it’s gotten pretty long.  We’ll call this part 1, then and I’ll move onto part 2 at a later date.  I’ll say in conclusion that I’m already a pretty big fan of this control. It’s nice and lightweight in terms of coding time and markup, and really allows a developer the kind of flexibility that he or she needs in developing apps these days.