Inserting new records with the ListView & LinqDataSource

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.

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.

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.

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.