BusinessRx Community

These blog entries are written by industry experts and leaders. We consider this content to be a good read for any software developer or web technologist.

Browse by Tags

All Tags » Visual Studio » Data   (RSS)

• .NET
• ASP.NET
• Atlas
• Community News
• LINQ
• Security
• SQL Server
• Talks
• Tips and Tricks

• LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

  Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data. Below are the first six parts in this series: Part 1: Introduction to LINQ to SQL Part 2: Defining our Data Model Classes Part 3: Querying our Database Part 4: Updating our Database Part 5: Binding UI using the ASP:LinqDataSource Control Part 6: Retrieving Data Using Stored Procedures In part 6 I demonstrated how you can optionally use database stored procedures (SPROCs) and user defined functions (UDFs) to query and retrieve data using your LINQ to SQL data model. In today's blog post I'm going to discuss how you can also optionally use SPROCs to update/insert/delete data from the database. To help illustrate this - let's start from scratch and build-up a data access layer for the Northwind sample database: Step 1: Creating our Data Access Layer (without using SPROCs yet) In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer that is built-in to VS 2008 to create a LINQ to SQL class model like below: Adding Validation Rules to our Data Model Classes After defining our data model classes and relationships we'll want to add some business logic validation to our data model.  We can do this by adding partial classes to our project that add validation rules to our data model classes (I cover how to-do this in-depth in my Part 4: Updating our Database LINQ to SQL tutorial). For example, we could add validation rules to enforce that the Customer's phone number follows a valid phone pattern, and that we don't add Orders where the customer's RequiredDate for delivery is before the actual OrderDate of the Order.  Once defined in partial classes like below, these validation methods will automatically be enforced anytime we write code to update our data model objects in an application. VB: C#:   Adding a GetCustomer() Helper Method to our DataContext Now that we have our data model classes created, and have applied validation rules to them, we can query and interact with the data.  We can do this by writing LINQ expressions against our data model classes to query the database and populate them (I cover how to-do this in my Part 3: Querying our Database LINQ to SQL tutorial).  Alternatively we could map SPROCs to our DataContext and use them to populate the data model classes (I cover how to-do this in my Part 6: Retrieving Data using Stored Procedures LINQ to SQL tutorial). When building a LINQ to SQL data layer you'll usually want to encapsulate common LINQ queries (or SPROC invocations) into helper methods that you add to your DataContext class.  We can do this by adding a partial class to our project.  For example, we could add a helper method called "GetCustomer()" that enables us to lookup and retrieve a Customer object from the database based on their CustomerID value: VB: C#:   Step 2: Using our Data Access Layer (still without SPROCs) We now have a data access layer that encapsulates our data model, integrates business validation rules, and enables us to query, update, insert, and delete the data. Let's look at a simple scenario using it where we retrieve an existing customer object, update the customer's ContactName and Phone Number, and then create a new Order object and associate it with them.  We can write the below code to do all of this within a single transaction.  LINQ to SQL will ensure that our business logic validation rules are clean before saving anything in the database: VB: C#: LINQ to SQL monitors the modifications we make to the objects we retrieve from the DataContext, and keeps track of all of the objects we add into it.  When we call DataContext.SubmitChanges() at the end, LINQ to SQL will check that our business logic rules are valid, and if so automatically generate the appropriate dynamic SQL to update our Customer record above, and insert a new record into the Orders table.  Hang on a second - I thought this post was about using SPROCs???  If you are still reading this, you might be feeling confused about where SPROCs fit into this post.  Why did I show you above how to write code that works with our data model objects, and then causes dynamic SQL to run?  Why haven't I showed you how to call a SPROC for doing inserts/updates/deletes instead yet?  The reason is that the programming model in LINQ to SQL for working with data model objects backed by SPROCs is the same as those updated via dynamic SQL.  The way you add data model validation logic is exactly the same (so all the validation rules on our data model classes above still apply when we use SPROCs).  The code snippet above where we use our data access layer to retrieve a customer, update it, and then add a new order associated with it is also exactly the same regardless of whether we are using dynamic SQL for updates, or whether we have configured our data model classes to use SPROCs instead. This programming model symmetry is powerful both in that you don't have to learn two ways of doing things, and also because it means that you don't have to decide up front at the beginning of your project whether you are going to use SPROCs or not.  You can start off using the dynamic SQL support provided by the LINQ to SQL ORM for all queries, inserts, updates and deletes.  You can then add your business and validation rules to your model.  And then later you can optionally update your data mapping model to use SPROCs - or not if you decide you don't want to.   The code and tests you write against your data model classes can stay the same regardless of whether you use dynamic SQL or SPROCs. We'll now spend the rest of this blog post demonstrating how we can update the data model we've built to use SPROCs for updates/inserts/deletes - while still using the same validation rules, and working with the same code snippets above. How to Use SPROCs for Insert/Update/Delete Scenarios We can modify the data access layer we've been building to use SPROCs to handle updates, instead of dynamic SQL, in one of two ways: 1) By using the LINQ to SQL designer to graphically configure SPROCs to execute in response to Insert/Update/Delete operations on our data model classes. or: 2) By adding a NorthwindDataContext partial class in our project, and then by implementing the appropriate Insert/Update/Delete partial methods provided on it (for example: InsertOrder, UpdateOrder, DeleteOrder) that will be called when we insert/update/delete data model objects.  These partial methods will be passed the data model instances we want to update, and we can then execute whatever SPROC or SQL code we want to save it within the database.  When we use approach #1 (the LINQ to SQL designer) to graphically configure SPROCs to call, it is under the covers generating the same code (in a partial class it creates) that you'd write when using approach #2.  In general I'd recommend using the LINQ to SQL designer to configure the SPROCs for the 90% case - and then in more advanced scenarios go in and custom tweak the SPROC invocation code it generates if you need to.  Step 3: Doing Order Inserts with a SPROC We'll begin switching our data model to use SPROCs by starting with the Order object.  We'll first go to the Visual Studio "Server Explorer" window, expand into the "Stored Procedures" node of our database, and then right-click and choose "Add New Stored Procedure": We'll then create a new SPROC that we'll call "InsertOrder" that inserts a new order record into our Orders table: Notice above how the SPROC defines the "OrderID" parameter as an output param.  This is because the OrderID column in the database is an identity column that is set to auto-increment each time a new record is added.  The caller of the SPROC will pass in NULL as a value when calling it - and the SPROC then passes back the newly created OrderID value as the output value (by calling the SCOPE_IDENTITY() function at the end of the SPROC). After creating the SPROC we'll then open up the LINQ to SQL ORM designer for our data access layer.  Like I discussed in my last blog post in this series (Part 6: Retrieving Data Using Stored Procedures), we can drag/drop SPROCs from the server-explorer onto the method pane of our DataContext designer.  We'll want to-do this with our newly created InsertOrder SPROC: Our last step will be to tell our data access layer to use the InsertOrder SPROC when inserting new Order objects into the database.  We can do that by selecting the "Order" class in the LINQ to SQL ORM designer, and then by going to the property grid and clicking the "..." button to override how Insert operations happen for it: Clicking the "..." button will bring up a dialog that allows us to customize how insert operations happen: Notice above how the default mode ("Use Runtime") is to have LINQ to SQL calculate and execute dynamic SQL to handle the insert operations.  We can change that by selecting the "Customize" radio button and then pick our InsertOrder SPROC from the list of available SPROCs: The LINQ to SQL designer will populate a parameter list for the SPROC we picked, and enable us to map properties on our Order class to parameters of our InsertOrder SPROC.  By default it is smart and tries to "best match" them based on name.  You can go in and override them if you want. Once we click "ok" on the dialog, we are done.  Now whenever a new Order is added to our DataContext and the SubmitChanges() method is invoked, our InsertOrder SPROC will be used instead of executing dynamic SQL.  Important: Even though we are now using a SPROC for persistence, the custom Order "OnValidate()" partial method we created earlier (in step 1 of this blog post) to encapsulate Order validation rules still executes before any changes are saved or the SPROC is invoked.  This means we have a clean way to encapsulate business and validation rules in our data models, and can re-use them regardless of whether dynamic SQL or SPROCs are used. Step 4: Doing Customer Updates with a SPROC Now let's modify our Customer object to handle updates using a SPROC. We'll start by creating a new "UpdateCustomer" SPROC like below:   Notice above how in addition to passing in the @CustomerID parameter, we are also passing in a @Original_CustomerID parameter.  The CustomerID column in the Customers table is not an auto-increment identity column, and it can be modified as part of an update of the Customer object.  Consequently we need to be able to provide the SPROC with both the original CustomerID and the new CustomerID in order to update the record.  We'll look at how we map this using the LINQ to SQL designer shortly. You'll notice above how I'm also passing in a @Version parameter (which is a timestamp) to the SPROC.  This is a new column I've added to the Northwind Customers table to help handle optimistic concurrency.  I will cover optimistic concurrency in much more depth in a later blog post in this LINQ to SQL series - but the short summary is that LINQ to SQL fully supports optimistic concurrency, and enables you to use either a version timestamp or to supply both original/new values to your SPROCs to detect if changes have been made by another user since you last refreshed your data objects.  For this sample I'm using a timestamp since it makes the code much cleaner. Once we've created our SPROC, we can drag/drop it onto the LINQ to SQL designer to add it as a method on our DataContext.  We can then select the Customer class in the ORM designer and click the "..." button to override the Customer object's Update behavior in the property grid: We'll select the "Customize" radio button and pick our UpdateCustomer SPROC to use: When mapping our Customer object's properties to the SPROC parameters, you'll notice that we'll want to be deliberate about whether we supply the "current" property value on the data object, or the original value that was in the database when the object was first retrieved.  For example, we'll want to make sure we map the "current" value of the Customer.CustomerID property for the @CustomerID SPROC parameter, and that we map the original value for the @original_customerID SPROC parameter. Once we click "ok" on the dialog, we are done.  Now whenever a new Customer is updated and the SubmitChanges() method is invoked, our UpdateCustomer SPROC will be used instead of executing dynamic SQL.  Important: Even though we are now using a SPROC for persistence, the Customer "OnPhoneChanging()" partial method we created earlier (in step 1 of this blog post) to encapsulate Phone Number validation rules still executes before any changes are saved or the SPROC is invoked.  We have a clean way to encapsulate business and validation rules in our data models, and can re-use them regardless of whether dynamic SQL or SPROCs are used. Step 5: Using our Data Access Layer Again (this time with SPROCs) Once we've updated our data layer to use SPROCs instead of dynamic SQL for persistence, we can re-run the exact same code we wrote in Step 2 earlier against our data model classes: Now the updates for the Customer object, and the insert for the Order object, are executing via SPROCs instead of dynamic SQL.  The validation logic we defined still executes just like before, though, and the data access code we write to use the data model classes is exactly the same. Some Advanced Notes When Using SPROCs A few quick notes that you might find useful for more advanced SPROC scenarios with LINQ to SQL: Usage of SPROC Output Params: In the Insert scenario (Step 3) above I showed how we could return back the new OrderID value (which is an auto-increment identity column in the Orders table) using an output parameter of the SPROC.  You aren't limited to only returning back identity column values when using SPROCs with LINQ to SQL - in fact you can update and return back output values for any parameter of your SPROC.  You can use this approach both for Insert and Update scenarios.  LINQ to SQL will then take the return value and use it to update the property value of your data model object without you having to-do any second queries against the database to refresh/populate them.  What Happens if the SPROC Throws an Error? If a SPROC raises an error when doing an Insert/Update/Delete operation, LINQ to SQL will automatically cancel and rollback the transaction of all changes associated with the current SubmitChanges() call on the DataContext.  This ensures that your data is always kept in a clean, consistent state.  Can you write code instead of using the ORM designer to call SPROCs? As I mentioned earlier in this post, you can use either the LINQ to SQL ORM designer to map your insert/update/delete operations to SPROCs, or you can add partial methods on your DataContext class and programmatically invoke them yourself.  Here is an example of the explicit code you could write in a partial class for the NorthwindDataContext to override the UpdateCustomer behavior to call a SPROC: The code above was actually what was generated by the LINQ to SQL ORM designer when we used the designer to map the SPROC and then associate the update behavior of the Customer object with it.  You can use it as a starting point and then add any additional logic you want to it to make it more advanced (for example: use the return value of the SPROC to raise custom exceptions for error conditions, optimistic concurrency, etc). Summary LINQ to SQL is a very flexible ORM.  It enables you to write clean object-oriented code to retrieve, update and insert data. Best of all - it enables you to cleanly design data model classes independent of how they are persisted and loaded from a database.  You can use the built-in ORM engine to efficiently retrieve and update data in the database using dynamic SQL.  Or alternatively you can configure your data layer to use SPROCs.  The nice thing is that your code consuming the data layer, and all of the business logic validation rules you annotate it with, can be the same regardless of which persistence approach you use. In future blog posts in this series I'll cover some remaining LINQ to SQL concepts including: Single Table Inheritance, Deferred/Eager Loading, Optimistic Concurrency, and handling Multi-Tier scenarios.  I'm on vacation next week, and so will hopefully have some free time to get a few of them written then.  Hope this helps, Scott Posted Thursday, August 23, 2007 4:27 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, LINQ

• The asp:ListView control (Part 1 - Building a Product Listing Page with Clean CSS UI)

  One of the new controls in ASP.NET 3.5 that I think will be very popular is the <asp:ListView> control.  The ListView control supports the data editing, insertion, deleting, paging and sorting semantics of higher-level controls like the GridView.  But - unlike the GridView - it provides you with complete control over the html markup generated.  The ListView control, when combined with the new Web Designer and rich CSS support in VS 2008, enables you to build much cleaner HTML UI.  Over the next few weeks I'll be putting together several blog posts that show off how you can use it. Building a Products Catalog Page For today's blog post we are going to start simple and just build a basic product catalog listing page like below: This products.aspx page will take a Catalog index in the URL, and use LINQ to SQL to retrieve and display product information.  We will also enable paging navigation at the bottom of the product listing (and do all of the paging operations in the database - so that only 6 products at a time are retrieved from the database).  The HTML markup output from the server will be 100% CSS based (no tables or inline styles).  Step 1: Defining out Site Layout with Nested Master Pages Before we start working on our product page, we'll first want to define the overall UI and layout structure for our site. If you are "design challenged" like me, one approach you might want to take with a new site is to get started by using one of the free HTML site templates that you can download from these two sites: http://www.opensourcetemplates.org/ or http://www.oswd.org/.  The templates on these sites are pure HTML (meaning you can use them with any server-side programming technology), and are built using clean CSS and XHTML markup.  For this blog post I decided to go with the "TerraFirma" template here. After I downloaded the template, the first thing I did was to create a root "Site.Master" Master Page that defined the overall layout structure for the site.  I then created a few nested master pages to define different column style layouts ("SingleColumn.master" and "TwoColumn.master").  VS 2008 now has great support for creating and using nested master pages that makes doing this easy.  You can read about how to define and use them in my earlier VS 2008 Nested Master Page Support blog post. Once we have created our master pages layouts, we can then create a new product catalog page for the site that is based on the single-column nested master page (click below for a full screen picture): Notice above how we can edit the page using the new VS 2008 Split View mode feature.  Above I'm using the "Vertical Split View" option so that I can see both the source and design view on a wide-screen monitor.  You can learn how to enable this in my earlier Enabling Vertical Split View in VS 2008 blog post. Step 2: Defining our CSS Rules using Mock HTML UI When it comes to defining our product UI for the page, there are a couple of different approaches we can use.  One would be to start by writing code to generate some dynamic data UI, and then work to make it pretty.  Another approach we could take would be to start by mocking up the HTML UI first, and then once we are happy with it write the code to make it dynamic.  For this blog post I'm going to take this second approach. To start let's just add a standard <ul><li> list of product content into the page: As you can see above, this <ul> list looks pretty unattractive, and obviously not like what we want our product listing page to look like.  To make it more attractive, we'll use some of the new CSS editing features I covered in my earlier VS 2008 Web Designer and CSS Support blog post. Specifically we'll want to open the new "Manage Styles" tool window in VS 2008 (you can open this by selecting the Format->CSS Styles->Managed Styles menu item): The manage styles window provides an easy way for us to see all CSS rules currently in our CSS stylesheet.  It also enables us to quickly lookup CSS selector values, refactor css rules across stylesheets, and create new rules.  We can create a new CSS rule for our product listing by selecting the "New Style..." link in the "Manage Styles" window.  This will bring up a dialog that enables us to choose where we want to define the CSS rule, and configure what settings we want for it.  For this sample we'll name the CSS selector ".productslist li" and select the "Define in existing style sheet" option to add it to the external stylesheet we already have for our application: We can then hit "ok", and return back to source mode to assign the CSS rule on our <ul> list (note how VS 2008 now provides CSS intellisense in source view): Currently our CSS rule doesn't have settings assigned to it, so our <ul> list will still look the same as it did before.  We can change that by assigning some CSS settings. There are a couple of ways we could set these CSS settings: 1) open up the CSS stylesheet and set them in source mode, 2) use the manage styles dialog we saw before to set them, or 3) use the new CSS Properties Window to edit the CSS rules in real-time within the designer.  We can bring up the CSS Properties Windows via the View->CSS Properties menu: When you select an element either in source-view or design-view, the CSS Properties Windows will list all of the CSS rules that apply to it.  The "Applied Rules" list at the top of the CSS Properties window indicates the precedence order of cascading rules.  The CSS properties list below it then shows all of the setting values assigned to that element.  The "target rule" drop down in the style application toolbar (circled in red above) indicates which CSS selector a change in the CSS Properties window will be assigned to.  In the example above our .productlist li rule is the CSS selector we currently have selected - which means as we set values in the CSS Properties window they will be persisted under that rule name in our external CSS stylesheet.  No style settings will be persisted inline the HTML page.  Let's now make some changes to our "productlist li" CSS rule.  First we'll change the layout display mode to be "inline": We'll then want to float each <li> to the left: And lastly we'll set the bottom and left margin of each <li> element to be 15px to space out the products nicely: Notice how when we are done no inline styles have been saved in our HTML page: Instead they've all been cleanly saved under our "productlist li" CSS selector in the external CSS stylesheet: Now all that remains is for us to replace the static HTML with some dynamic data coming from a database. Step 3: Defining our LINQ to SQL Data Model We'll use LINQ to SQL to retrieve our product data from the database.  LINQ to SQL is a great new ORM (object relational mapper) implementation built into .NET 3.5.  You can learn more about it from my on-going LINQ to SQL blog series (more posts in it coming soon): Part 1: Introduction to LINQ to SQL Part 2: Defining our Data Model Classes Part 3: Querying our Database Part 4: Updating our Database Part 5: Binding UI using the ASP:LinqDataSource Control We'll use the Northwind sample database for our product data, and define our data model classes in the LINQ to SQL ORM designer like so: Once we have our LINQ to SQL data model defined, we are ready to use the <asp:listview> control and bind the data to it. Step 4: Convert our HTML Mock UI to use the <asp:ListView> control The <asp:listview> control is a template-driven control.  The control itself outputs no "built-in" UI, nor any actual HTML markup.  Instead, you can define whatever markup *you* want displayed using the below templates: LayoutTemplate ItemTemplate AlternatingItemTemplate SelectedItemTemplate EditItemTemplate InsertItemTemplate EmptyItemTemplate EmptyDataTemplate ItemSeparatorTemplate GroupTemplate GroupSeparatorTemplate The first two templates in the list above - LayoutTemplate and ItemTemplate - are the most common ones you'll end up using.  The <LayoutTemplate> template allows you to define the outer container/wrapper of your data UI.  The <ItemTemplate> template then allows you to define what each item in the list should look like.  Within the <LayoutTemplate> you then define an "ItemContainer" control that indicates where you want the <asp:ListView> control to dynamically add the <ItemTemplate> items into the output markup. To see how this works in action, we could take our mock products HTML UI: And replace it with a <asp:listview> that can dynamically generate the exact same markup output like so: Notice above how I am using a <asp:placeholder> control in the <LayoutTemplate> to indicate where I want to add in my items in the list.  I could use other controls instead as the itemContainer if I wanted to - but by using an <asp:placeholder> control as the itemContainer I will prevent any id values or extra markup being generated. Notice above how I've also defined an <EmptyDataTemplate>.  This will display instead of the <LayoutTemplate> if I assign an empty sequence of data to the ListView.  This will avoid us inadvertently displaying an empty <ul></ul> element in the event that there are no products in the catalog specified.    Once we've defined our template above, we can write some code in our code-behind file to retrieve our product data using LINQ to SQL, and bind our ListView with it: VB: C#: And now when we run the page and supply a valid categoryid as a querystring argument we'll see our products dynamically pulled from the database: If we try a category that doesn't contain products, we'll get our empty template message: If you do a "view source" on the products page in the browser, you'll see that the markup generated from our ListView control is the same as what our static HTML was: There are no ID elements or inline styles generated.  We had complete control over every HTML element and attribute generated. Step 5: Using a <asp:LinqDataSource> control instead of writing code In our previous step above we wrote procedural LINQ to SQL code to databind our <asp:ListView>.  This obviously works, and provides a great deal of control over the logic executed. Another option you can alternatively use is a declarative ASP.NET datasource control.  All of the ASP.NET 2.0 datasource controls (SqlDataSource, ObjectDataSource, AccessDataSource, XmlDataSource, SiteMapDataSource, etc) work with the ListView.  You can also use the new <asp:LinqDataSource> control with it.  For more information on the LinqDataSource, check out my previous LINQ to SQL Part 5: Binding UI using the ASP:LinqDataSource Control blog post. To use the <asp:LinqDataSource> in the above sample, we'll first delete the previous code we wrote in the code-behind file, and then click on the <asp:ListView> control in the designer and select the "Choose Data Source->New DataSource" option.  We'll pick the "LINQ DataSource" option in the data source dialog, and then bind to the Northwind data model we created earlier.  We can then choose to bind the ListView against the "Products" entity collection in our Northwind data model: We can then hit the "Where" button to configure a LINQ filter based on the "category" value in the querystring (we could alternatively bind the value from a form value, cookie, session value, another control, etc): When we press the "ok" button the ListView's DataSourceID will be set to a new <asp:LinqDataSource> in the page: And now without us having to have any code in the application we have a product listing with custom HTML UI databinding against our LINQ to SQL data model. Step 6: Enabling Server Side Paging using the <asp:DataPager> control Our last step with this sample will be to enable paging support over the products data.  Specifically, we only want to display 6 products at a time on the page, and provide a nice page number UI to allow users to navigate forward and backwards over our product sequence. One of the other new controls in ASP.NET 3.5 is the <asp:DataPager> control - which makes data paging scenarios with the ListView control pretty easy.  You can drop it anywhere you want on a page, and set its "PagedControlID" property to point at the ListView, and its "PageSize" property to indicate how many items in the ListView you want displayed at a time: The <asp:DataPager> will then output navigation UI for your ListView: And then if you click the "2" link in the paging UI above it will show you the remaining 5 products in the category: The <asp:LinqDataSource> automatically uses LINQ to SQL's built-in support for server-side data paging to ensure that only 6 products (because the PageSize is 6) are ever retrieved from the database at a time.  This means that your application and database will scale even if you have thousands of products in a specific category. Disclaimer: The <asp:DataPager> in Beta2, though, does have some limitations in that it can't by default be driven off of a querystring value - which makes it not very SEO friendly.  I'll cover how to fix this and support a querystring index in a future blog post. Summary Hopefully the above walkthrough provides a good first look overview of how to use the new <asp:ListView> control.  You will find that this control provides you with complete control over the markup output sent down to a client - while still providing rich data paging, editing, deleting and insertion semantics.  I'll cover many more of these scenarios (along with the ListView's cool grouping functionality) in future blog posts. Click here to download a copy of the above sample in order to try it out on your own machine. Hope this helps - and have a great weekend! Scott Posted Friday, August 10, 2007 4:16 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, LINQ

• Great New ASP.NET 2.0 Data Tutorials Published

  Over the last year Scott Mitchell has written 75 awesome data access tutorials covering ASP.NET 2.0 and VS 2005.  You can read all of them for free on the www.asp.net site (VB and C# versions are provided for each tutorial). Earlier this week we published the last 9 articles in the series: Scott Mitchell has more details on them on this blog post (make sure to thank him for writing all of these!). Hope this helps, Scott P.S. All of the tutorials are available in both HTML as well as in a PDF format that you can download and print/take offline. Posted Wednesday, August 08, 2007 9:58 PM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, Community News

• Using LINQ to XML (and how to build a custom RSS Feed Reader with it)

  One of the big programming model improvements being made in .NET 3.5 is the work being done to make querying data a first class programming concept.  We call this overall querying programming model "LINQ", which stands for .NET Language Integrated Query. LINQ supports a rich extensibility model that facilitates the creation of efficient domain-specific providers for data sources.  .NET 3.5 ships with built-in libraries that enable LINQ support against Objects, XML, and Databases. What is LINQ to XML? LINQ to XML is a built-in LINQ data provider that is implemented within the "System.Xml.Linq" namespace in .NET 3.5. LINQ to XML provides a clean programming model that enables you to read, construct and write XML data.  You can use LINQ to XML to perform LINQ queries over XML that you retrieve from the file-system, from a remote HTTP URL or web-service, or from any in-memory XML content.  LINQ to XML provides much richer (and easier) querying and data shaping support than the low-level XmlReader/XmlWriter API in .NET today.  It also ends up being much more efficient (and uses much less memory) than the DOM API that XmlDocument provides.  Using LINQ to XML to query a local XML File To get a sense of how LINQ to XML works, we can create a simple XML file on our local file-system like below that uses a custom schema we've defined to store RSS feeds: I could then use the new XDocument class within the System.Xml.Linq namespace to open and query the XML document above.  Specifically, I want to filter the <Feed> elements in the XML file and return a sequence of the non-disabled RSS feeds (where a disabled feed is a <Feed> element with a "status" attribute whose value is "disabled").  I could accomplish this by writing the code below: VB: C#: Notice in the code-snippets above how I'm loading the XML file using the XDocument.Load(path) static method - which returns back an XDocument object.  Because I'm running this code within ASP.NET, I'm using the Server.MapPath(path) helper method to resolve the correct path for my XML file relative to the page I'm running the code on. Once I have an XDocument object for my XML file I can then write a LINQ query expression to retrieve the XML data I'm looking for.  In the code above I'm querying over each of the <Feed> elements within the XML file.  This is driven by this opening clause in the LINQ query expression: from feed in feedXML.Decedents("Feed") I'm then applying a filter that only returns back those "Feed" elements that either don't have a "status" attribute, or whose "status" attribute value is not set to "disabled": Where (feed.Attribute("status") Is Nothing) OrElse (feed.Attribute("status").Value <> "disabled") I am then using the select clause in our LINQ expression to indicate what data I want returned.  If I simply wrote "select feed", LINQ to XML would return back a sequence of XElement objects that represents each of the XML element nodes that match my filter.  In the code samples above, though, I am using the shaping/projection features of LINQ to instead define a new anonymous type on the fly, and I am defining two properties on it - Name and Feed - that I want populated using the <Name> and <Url> sub-elements under each <Feed> element: Select Name = feed.Element("Name").Value, Url = feed.Element("Url").Value As you can see above (and below), I can then work against this returned sequence of data just like I would any collection or array in .NET.  VS 2008 provides full intellisense and compilation checking support over this anonymous type sequence: I can also data-bind the results against any UI control in ASP.NET, Windows Forms, or WPF.  For example, assuming I had a dropdownlist control defined in my page like so: I could use the below LINQ to XML code to databind the results to it: This will then produce a nice drop-downlist in our HTML page like so: Hmm - What is this "anonymous type" thing? In my code above I've taken advantage of a new language feature in VB and C# called "anonymous types".  Anonymous types enable developers to concisely define inline CLR types within code, without having to explictly define a formal class declaration of the type.  You can learn more about them in my previous New "Orcas" Language Feature: Anonymous Types blog post. While anonymous types can be super useful when you want to locally iterate and work with data, we'll often want/need to define a standard class when passing the results of our LINQ query between multiple classes, across class library assemblies, and over web-services.  To enable this, I could define a non-anonymous class called "FeedDefinition" to represent our Feed data like so: Note above how I'm using the new "Automatic Properties" feature of C# to define the properties (and avoid having to define a field for them). I could then write the below method to return back a generics based List<FeedDefinition> collection containing FeedDefinition objects: Note above how the only change I've made to the LINQ to XML query we were using before is to change the "select" clause from "select new" (with no type-name) to "select new FeedDefinition".  With this change I'm now returning a sequence of FeedDefinition objects that I can pass from class to class, assembly to assembly, and across web-services. Using LINQ to XML to Retrieve a Remote RSS XML Feed The XDocument.Load(path) static method supports the ability open both XML files from the file-system, as well as remote XML feeds returned from an HTTP URL.  This enables you to use it to access remote RSS feeds, REST APIs, as well as any other XML feed published on the web. For an example of this in action, let's take a look at the XML of my blog's RSS feed (http://weblogs.asp.net/scottgu/rss.aspx): I could write the LINQ to XML code below to retrieve the above blog post data from my RSS feed, and work with the individual feed items as .NET objects: Note above how I am converting the "Published" field in the RSS field - which is a string in the XML - to a .NET DateTime object.  Notice also how LINQ to XML includes a built-in XNamespace type that provides a type-safe way to declare and work with XML Namespaces (which I need to-do to retrieve the <slash:comments> element). I could then take advantage of the composition features of LINQ to perform a further sub-query on the result, so that I filter over only those RSS posts that were published within the last 7 days using the code below: As you can see above, you can feed the results of one LINQ query expression to be the input of another LINQ expression.   This enables you to write very clean, highly composable, code. Using LINQ Sub-Queries within a LINQ to XML Query Expression If you look at the raw XML of my RSS feed, you'll notice that the "tag" comments for each post are stored as repeated <category> elements directly below each <item> element: When designing the object model for a "BlogEntry" class, I might want to represent these <category> values as a sub-collection of strings.  For example, using a "Tags" property that is a generic list of type string: You might be wondering - how do we take a flat collection of <category> elements under <item> and transform them into a nested sub-collection of strings?  The nice thing about LINQ is that it makes this type of scenario easy by allowing us to use nested LINQ query expressions like so: This "shaping" power of LINQ, and its ability to take flat data structures and make them hierarchical (and take hierarchical data structures and make them flat) is super powerful.  You can use this feature with any type of data source - regardless of whether it is XML, SQL, or plain old objects/arrays/collections. Putting it all Together with a Simple RSS Feed Reader The code snippets I've walked through above demonstrate how you can easily write LINQ to XML code to retrieve a list of RSS feeds from a local XML file, and how to remotely query an RSS feed to retrieve an individual feed's details and individual item post contents.  I could obviously then take the resulting feed contents and data-bind it to a ASP.NET GridView or ListView control to provide a nice view of the blog feed: I've built a simple sample application that puts all of these snippets together to deliver a simple RSS Reader with LINQ to XML and the new <asp:ListView> control.  You can download it here.  Included in the download is both a VB and C# version of the application. Summary LINQ to XML provides a really powerful way to efficiently query, filter, and shape/transform XML data.  You can use it both against local XML content, as well as remote XML feeds.  You can use it to easily transform XML data into .NET objects and collections that you can further manipulate and transfer across your application. LINQ to XML uses the same core LINQ query syntax and concepts that LINQ to SQL, LINQ to Objects, LINQ to SharePoint, LINQ to Amazon, LINQ to NHibernate, etc. use when querying data. You can learn more about the LINQ query syntax and the supporting language features being added to VB and C# to support it from these previous blog posts of mine: Automatic Properties, Object Initializer and Collection Initializers Extension Methods Lambda Expressions Query Syntax Anonymous Types You might also find these blog posts of mine useful to learn more about LINQ to SQL: Part 1: Introduction to LINQ to SQL Part 2: Defining our Data Model Classes Part 3: Querying our Database Part 4: Updating our Database Part 5: Binding UI using the ASP:LinqDataSource Control In a future blog post I'll return to LINQ to XML and demonstrate how it can be used not just to query XML, but also to really cleanly generate XML output from a .NET data structure.  Hope this helps, Scott Posted Tuesday, August 07, 2007 4:22 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, LINQ

• LINQ to SQL Debug Visualizer

  Probably the biggest programming model improvement being made in .NET 3.5 is the work being done to make querying data a first class programming concept.  We call this overall querying programming model "LINQ", which stands for .NET Language Integrated Query.  Developers can use LINQ with any data source, and built-in libraries are included with .NET 3.5 that enable LINQ support against Objects, XML, and Databases. Earlier this summer I started writing a multi-part blog series that discusses the built-in LINQ to SQL provider in .NET 3.5.  LINQ to SQL is an ORM (object relational mapping) implementation that allows you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as update/insert/delete data from it.  LINQ to SQL fully supports transactions, views, and stored procedures.  It also provides an easy way to integrate data validation and business logic rules into your data model. You can learn more about LINQ to SQL by reading my posts below (more will be coming soon): Part 1: Introduction to LINQ to SQL Part 2: Defining our Data Model Classes Part 3: Querying our Database Part 4: Updating our Database Part 5: Binding UI using the ASP:LinqDataSource Control Using the LINQ to SQL Debug Visualizer One of the nice development features that LINQ to SQL supports is the ability to use a "debug visualizer" to hover over a LINQ expression while in the VS 2008 debugger and inspect the raw SQL that the ORM will ultimately execute at runtime when evaluating the LINQ query expression. For example, assume we write the below LINQ query expression code against a set of data model classes: We could then use the VS 2008 debugger to hover over the "products" variable after the query expression has been assigned: And if we click the small magnifying glass in the expression above, we can launch the LINQ to SQL debug visualizer to inspect the raw SQL that the ORM will execute based on that LINQ query: If you click the "Execute" button, you can even test out the SQL query and see the raw returned results that will be returned from the database: This obviously makes it super easy to see precisely what SQL query logic LINQ to SQL ORM is doing for you.  You can learn even more about how all this works by reading the Part 3: Querying our Database segment in my LINQ to SQL series above. How to Install the LINQ to SQL Debug Visualizer The LINQ to SQL Debug Visualizer isn't built-in to VS 2008 - instead it is an add-in that you need to download to use.  You can download a copy of it here. The download contains both a binary .dll assembly version of the visualizer (within the \bin\debug directory below), as well as all of the source code for the visualizer: To install the LINQ to SQL debug visualizer, follow the below steps: 1) Shutdown all running versions of Visual Studio 2008 2) Copy the SqlServerQueryVisualizer.dll assembly from the \bin\debug\ directory in the .zip download above into your local \Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\ directory: 3) Start up Visual Studio 2008 again.  Now when you use the debugger with LINQ to SQL you should be able to hover over LINQ query expressions and inspect their raw SQL (no extra registration is required). Hope this helps, Scott Posted Wednesday, August 01, 2007 1:23 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, LINQ, SQL Server

• LINQ to SQL (Part 5 - Binding UI using the ASP:LinqDataSource Control)

  Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data. Below are the first four parts of my LINQ to SQL series: Part 1: Introduction to LINQ to SQL Part 2: Defining our Data Model Classes Part 3: Querying our Database Part 4: Updating our Database In these previous LINQ to SQL blog posts I focused on how you can programmatically use LINQ to SQL to easily query and update data within a database. In today's blog post I'll cover the new <asp:LinqDataSource> control that is shipping as part of ASP.NET in the upcoming .NET 3.5 release.  This control is a new datasource control for ASP.NET (like the ObjectDataSource and SQLDataSource controls that shipped with ASP.NET 2.0) which makes declaratively binding ASP.NET UI controls to LINQ to SQL data models super easy. Sample Application We'll be Building The simple data editing web application I'll walkthrough building in this tutorial is a basic data entry/manipulation front-end for products within a database: The application will support the following end-user features: Allow users to filter the products by category Allow users to sort the product listing by clicking on a column header (Name, Price, Units In Stock, etc) Allow users to skip/page over multiple product listings (10 products per page) Allow users to edit and update any of the product details in-line on the page Allow users to delete products from the list The web application will be implemented with a clean object-oriented data model built using the LINQ to SQL ORM. All of the business rules and business validation logic will be implemented in our data model tier - and not within the UI tier or in any of the UI pages.  This will ensure that: 1) a consistent set of business rules are used everywhere within the application, 2) we write less code and don't repeat ourselves, and 3) we can easily modify/adapt our business rules at a later date and not have to update them in dozens of different places across our application. We will also take advantage of the built-in paging/sorting support within LINQ to SQL to ensure that features like the product listing paging/sorting are performed not in the middle-tier, but rather in the database (meaning only 10 products are retrieved from the database at any given time - we are not retrieving thousands of rows and doing the sorting/paging within the web-server).  What is the <asp:LinqDataSource> control and how does it help? The <asp:LinqDataSource> control is an ASP.NET control that implements the DataSourceControl pattern introduced with ASP.NET 2.0.  It is similar to the ObjectDataSource and SqlDataSource controls in that it can be used to declaratively bind other ASP.NET controls on a page to a datasource.  Where it differs is that instead of binding directly to a database (like the SqlDataSource) or to a generic class (like the ObjectDataSource), the <asp:linqdatasource> is designed to bind against a LINQ enabled data model. One of the benefits of using the <asp:linqdatasource> control is that it leverages the flexibility that LINQ based ORMs provide.  You don't need to define custom query/insert/update/delete methods for the datasource to call - instead you can point the <asp:linqdatasource> control at your data model, identify what entity table you want it to work against, and then bind any ASP.NET UI control against the <asp:linqdatasource> and have them work with it. For example, to get a basic product listing UI on my page that works against Product entities within a LINQ to SQL data model, I could simply declare a <asp:linqdatasource> on my page that points to my LINQ to SQL datacontext class, and identify the entities (for example: Products) in the LINQ to SQL data model I want to bind against.  I could then point a GridView at it (by settings its DataSourceID property) to get a grid-like view of the Product content: Without having to-do anything else, I can run the page and have a listing of my Product data with built-in support for paging and sorting over the data.  I can add a edit/delete button on the Grid and automatically have update support as well.  I don't need to add any methods, map any parameters, or write any code for the <asp:LinqDataSource> to handle both these querying and updating scenarios - it can work against the LINQ to SQL data model we point it against and do these operations automatically.  When updates are made, the LINQ to SQL ORM will automatically ensure that all business rules and validation logic we've added (as partial methods) to the LINQ to SQL data model pass before persisting anything to the database. Important: The beauty of LINQ and LINQ to SQL is that it obviously isn't tied to being used only in UI scenarios - or with particular UI binding controls like the LinqDataSource.  As you've seen in my previous posts in this series, writing code using the LINQ to SQL ORM is extremely clean.  You can always write custom UI code to directly work against your LINQ to SQL data model if you prefer, or when you find a UI scenario that isn't particularly suited to using the <asp:linqdatasource>.  The below sections walkthrough using LINQ to SQL and the <asp:LinqDataSource> control to build the web application scenario I defined above. Step 1: Define our Data Model We'll begin working on the application by first defining the data model we'll use to represent our database.  I discussed how to create a LINQ to SQL data model using VS 2008's LINQ to SQL designer in Part 2 of this series.  Below is a screenshot of the data model classes I can quickly create using the LINQ to SQL designer to model the "Northwind" sample database: We'll revisit our data model in Step 5 of this tutorial below when we add some business validation rules to it.  But to begin with we'll just use the above data model as-is to build our UI. Step 2: Creating a Basic Product Listing We'll start our UI by creating an ASP.NET page with a <asp:gridview> control on it and use some CSS to style it: We could write code to programmatically bind our data model to the GridView (like I did in Part 3 of this series), or alternatively I could use the new <asp:linqdatasource> control to bind the GridView to our data model.  VS 2008 includes build-in designer support to make it easy to connect up our GridView (or any other ASP.NET server control) to LINQ data.  To bind our grid above to the data model we created earlier, we can switch into design-view, select the GridView, and then select the "New Data Source..." option within the "Choose Data Source:" drop-down: This will bring up a dialog box that lists the available datasource options to create.  Select the new "LINQ" option in the dialog box and name the resulting <asp:linqdatasource> control you want to create: The <asp:linqdatasource> designer will then display the available LINQ to SQL DataContext classes that your application can use (including those in class libraries that you are referencing): We'll want to select the data model we created with the LINQ to SQL designer earlier.  We'll then want to select the table within our data model that we want to be the primary entity for the <asp:linqdatasource> to bind against.  For this scenario we'll want to select the "Products" entity class we built.  We'll also want to select the "Advanced" button and enable updates and deletes for the datasource: When we click the "Finish" button above, VS 2008 will declare a <asp:linqdatasource> within our .aspx page, and update the <asp:gridview> to point to it (via its DataSourceID property).  It will also automatically provide column declarations in the Grid based on the schema of the Product entity we choose to bind against: We can then pull up the "smart task" context UI of the GridView and indicate that we want to enable paging, sorting, editing and deleting on it: We can then press F5 to run our application, and have a product listing page with full paging and sorting support (note the paging indexes at the bottom of the grid below): We can also select the "edit" or "delete" button on each row to update the data: If we flip into source view on the page, we'll see that the markup of the page contains the content below.  The <asp:linqdatasource> control points at the LINQ to SQL DataContext we created earlier, as well as the entity table we want to bind against.  The GridView then points at the <asp:linqdatasource> control (via its DataSourceID) and indicates which columns should be included in the grid, what their header text should be, as well as what sort expression to use when the column header is selected.  Now that we have the basics of our web UI working against our LINQ to SQL data-model, we can go ahead and further customize the UI and behavior. Step 3: Cleaning up our Columns Our GridView above has a lot of columns defined within it, and two of the column values (the SupplierID and the CategoryID) are currently foreign-key numbers -- which certainly isn't the ideal way to represent them to an end-user.  Removing Unnecessary Columns  We can start cleaning up our UI by deleting a few of the columns we don't need.  I can do this in source mode (simply nuke the <asp:boundfield> declarations) or in designer mode (just click on the column in the designer and choose the "Remove" task).  For example, we could remove the "QuantityPerUnit" column below and re-run our application to get this slightly cleaner UI: If you have used the <asp:ObjectDataSource> control before and explicitly passed update parameters to update methods (the default when using DataSet based TableAdapters) one of the things you know can be painful is that you have to change the method signatures of your TableAdapter's update methods when the parameters based by your UI are modified.  For example: if we deleted a column in our grid (like above), we'd end up having to modify our TableAdapter to support update methods without that parameter. One of the really nice things about the <asp:LinqDataSource> control is that you do not need to-do these types of changes.  Simply delete (or add) a column from your UI and re-run the application - no other changes are required.  This makes changing web UI built using the <asp:LinqDataSource> much easier, and enables much faster scenarios iterations within an application. Cleaning up the SupplierID and CategoryID Columns Currently we are displaying the foreign-key integer values in our GridView for the Supplier and Category of each Product:  While accurate from a data model perspective, it isn't very end-user friendly.  What I really want to-do is to display the CategoryName and SupplierName instead, and provide a drop-downlist while in Edit mode to enable end-users to easily associate the SupplierID and CategoryID values. I can change the GridView to display the Supplier Name and Category Name instead of the ID's by replacing the default <asp:BoundField> in our GridView with an <asp:TemplateField>.  Within this TemplateField I can add any content I want to customize the look of the column.  In the source code below I'm going to take advantage of the fact that each Product class in the LINQ to SQL data model we created has a Supplier and Category property on it. What this means is that I can easily databind their Supplier.CompanyName and Category.CategoryName sub-properties within our Grid:   And now when I run the application I get the human readable Category and Supplier name values instead: To get drop-down list UI for the Supplier and Category columns while in Edit-Mode in the Grid, I will first add two additional <asp:LinqDataSource> controls to my page.  I will configure these to bind against the Categories and Suppliers within the LINQ to SQL data model we created earlier: I can then go back to the <asp:TemplateField> columns we added to our GridView earlier and customize their edit appearance (by specifying an EditItemTemplate).  We'll customize each column to have a dropdownlist control when in edit mode, where the available values in the dropdownlists are pulled from the categories and suppliers datasource controls above, and where we two-way databind the selected value to the Product's SupplierID and CategoryID foreign keys: And now when end-users click edit in the GridView, they are presented a drop-down list of all valid Supplier's to associate the product with: And when they hit save the Product is updated appropriately (the GridView will use the DropDownList's currently selected value to bind the SupplierID). Step 4: Filtering our Product Listing Rather than show all products within the database, we can update our UI to include a dropdownlist that allows the user to filter the products by a particular category.  Because we already added a <asp:LinqDataSource> control to the page earlier that references our Categories within our LINQ to SQL data model, all I need to-do to create a drop-downlist control at the top of the page that binds against this.  For example: When I run the page I'll now get a filter dropdownlist of all categories at the top of the page: My last step is to configure the GridView to only show those Products in the category the end-user selects from the dropdownlist.  The easiest way to-do this is by selecting the "Configure DataSource" option in the GridView smart task: This will bring me back to the <asp:LinqDataSource> control's design-time UI that we used at the very beginning of this tutorial.  I can select the "Where" button within this to add a binding filter to the datasource control.  I can add any number of filter expressions, and declaratively pull the values to filter by from a variety of places (for example: from the querystring, from form-values, from other controls on the page, etc): Above I'm going to choose to filter by the Products by their CategoryID value, and then retrieve this CategoryID from the DropDownList control we just created on our page: When we hit finish, the <asp:linqdatasource> control in our page will have been updated to reflect this filter clause like so: And when we now run the page the end-user will now be able to select from the available Categories in the filter drop-downlist and page, sort, edit and delete just the products in that category: The <asp:LinqDataSource> control will automatically apply the appropriate LINQ filter expression when working against our LINQ to SQL data model classes to ensure that only the required data is retrieved from the database (for example: in the Grid above only the 3 rows of Product data from the second page of Confection products will be retrieved from the database). You can optionally handle the Selecting event on the <asp:LinqDataSource> if you want to write a custom LINQ expression in code to completely customize the query instead. Step 5: Adding Business Validation Rules As I discussed in Part 4 of this LINQ to SQL series, when we define LINQ to SQL data models we will automatically have a default set of schema based validation constraints added to our data model classes.  This means that if I try and enter a null value for a required column, try and assign a string to an integer, or assign a foreign-key value to a row that doesn't exist, our LINQ to SQL data model will raise an error and ensure that our database integrity is maintained. Basic schema validation is only a first step, though, and is rarely enough for most real-world applications.  Typically we'll want/need to add additional business rules and application-level validation to our data model classes.  Thankfully LINQ to SQL makes adding these types of business validation rules easy (for details on the various validation approaches available, please read Part 4 of my LINQ to SQL series). Example Business Validation Rule Scenario For example, let's consider a basic business logic rule we might want to enforce.  Specifically, we want to ensure that a user of our application can't discontinue a product while we still have units on backorder for it: If a user tries to save the above row, we'll want to prevent this change from being persisted and throw an appropriate error telling the user how to fix it. Adding a Data Model Validation Rule The wrong place to add this type of business validation rule is in the UI layer of our application.  Adding it in the UI layer of our application will mean that the rule will be specific to only that one place, and will not be automatically enforced when we add another page to our application that also updates Products.  Distributing business rules/logic in our UI layer will also make life extremely painful as our application grows in size - since changes/updates to our business will necessitate making code changes all over the place. The right place to specify this type of business logic validation is instead in our LINQ to SQL data model classes that we defined earlier.  As I discussed in Part 4 of this series, all classes generated by the LINQ to SQL designer are defined as "partial" classes - which means that we can easily add additional methods/events/properties to them.  The LINQ to SQL data model classes automatically call validation methods that we can implement to enforce custom validation logic within them. For example, I could add a partial Product class to my project that implements the OnValidate() partial method that LINQ to SQL calls prior to persisting a Product entity.  Within this OnValidate() method I could add the following business rule to enforce that products can't have a Reorder Level if the product is discontinued: Once I add the above class into my LINQ to SQL project, the above business rule will be enforced anytime anyone uses my data model to try and modify the database.  This is true for both updating existing Products, as well as adding new Products into the database. Because the <asp:LinqDataSource> that we defined in our pages above works against our LINQ to SQL data model classes, all of its update/insert/delete logic will now have to pass the above validation check prior to the change being persisted.  We do not need to-do anything to our UI tier in order for this validation to occur - it will automatically be applied anywhere and everywhere our LINQ to SQL data model is used. Adding Nice Error Handling in our UI Tier By default if a user now uses our GridView UI to enter a non-valid UnitsOnOrder/Discontinued combination, our LINQ to SQL data model classes will raise an exception.  The <asp:LinqDataSource> will in turn catch this error and provides an event that users can use to handle it.  If no one handles the event then the GridView (or other) control bound to the <asp:LinqDataSource> will catch the error and provide an event for users to handle it.  If no one handles the error there then it will be passed up to the Page to handle, and if not there to the global Application_Error() event handler in the Global.asax file.  Developers can choose any place along this path to insert appropriate error handling logic to provide the right end-user experience. For the application we defined above, probably the best place to handle any update errors is by handling the RowUpdated event on our GridView.  This event will get fired every time an update is attempted on our datasource, and we can access the exception error details if the update event fails.  We can add the below code to check if an error occurs, and if so display an appropriate error message to the end-user: Notice above how we have not added any validation specific logic into our UI.  Instead, I am retrieving the validation error message string we raised in our business logic and am using it to display an appropriate message to the end-user (I am then displaying a more generic error message in the event of other failures).  Notice how I'm also indicating above that I want the GridView to stay in Edit mode when an error occurs - that way the user can avoid losing their changes, and can modify the values they entered and click "update" again to try and save them.  We can then add a <asp:literal> control with the "ErrorMessage" ID anywhere we want on our page to control where where we want the error message to be displayed: And now when we try and update the Product with an invalid value combination we'll see an error message indicating how to fix it: The nice thing about using this approach is that I can now add or change my data model's business rules and not have to modify any of my UI tier's code to have them pick up and honor the changes.  The validation rules, and corresponding error messages, can be written and centralized in one place in my data model and automatically applied everywhere. Summary The <asp:LinqDataSource> control provides an easy way to bind any ASP.NET UI control to a LINQ to SQL data model.  It enables UI controls to both retrieve data from a LINQ to SQL data model, as well as cleanly apply updates/inserts/deletes to it.  In our application above we used the LINQ to SQL ORM designer to create a clean, object oriented data model.  We then added three ASP.NET UI controls to our page (a GridView, a DropDownList, and a ErrorMessage Literal), and added three <asp:LinqDataSource> controls to bind Product, Category, and Supplier data from it: We then wrote 5 lines of business validation logic in our data model, and 11 lines of UI error handling logic.  The end result is a simple web application with custom UI that allows users to dynamically filter product data by category, efficiently sort and page over the product results, inline-edit the product data to save updates (providing our business rules pass), and delete products from the system (also providing our business rules allow it). In future posts in this series I'll cover more LINQ to SQL scenarios including optimistic concurrency, lazy and eager loading, table mapping inheritance, and custom SQL/SPROC usage. Next week I also plan to start a new series of blog posts that cover the new <asp:ListView> control - which is a new control that will ship with the ASP.NET release in .NET 3.5.  It provides total control over the markup generated for data scenarios (no tables, no spans, no inline styles...), while also delivering built-in support for paging, sorting, editing, and insertion scenarios.  For example, we could optionally use it to replace the default Grid layout look of our application above with a completely custom look and feel.  Best of all, I could replace it within the above page in my application and not have to change my Data Model, the <asp:linqdatasource> declaration, or my code-behind UI error handling logic at all. Hope this helps, Scott Posted Tuesday, July 17, 2007 2:03 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, LINQ, SQL Server

• LINQ to SQL (Part 4 - Updating our Database)

  Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data. Below are the first three parts of my LINQ to SQL series: Part 1: Introduction to LINQ to SQL Part 2: Defining our Data Model Classes Part 3: Querying our Database In today's blog post I'll cover how we we can use the data model we created earlier, and use it to update, insert, and delete data.  I'll also show how we can cleanly integrate business rules and custom validation logic with our data model. Northwind Database Modeled using LINQ to SQL In Part 2 of this series I walked through how to create a LINQ to SQL class model using the LINQ to SQL designer that is built-into VS 2008.  Below is a class model created for the Northwind sample database and which I'll be using in this blog post:   When we designed our data model using the LINQ to SQL data designer above we defined five data model classes: Product, Category, Customer, Order and OrderDetail.  The properties of each class map to the columns of a corresponding table in the database.  Each instance of a class entity represents a row within the database table. When we defined our data model, the LINQ to SQL designer also created a custom DataContext class that provides the main conduit by which we'll query our database and apply updates/changes.  In the example data model we defined above this class was named "NorthwindDataContext".  The NorthwindDataContext class has properties that represent each Table we modeled within the database (specifically: Products, Categories, Customers, Orders, OrderDetails). As I covered in Part 3 of this blog series, we can easily use LINQ syntax expressions to query and retrieve data from our database using this NorthwindDataContext class.  LINQ to SQL will then automatically translate these LINQ query expressions to the appropriate SQL code to execute at runtime. For example, I could write the below LINQ expression to retrieve a single Product object by searching on the Product name: I could then write the LINQ query expression below to retrieve all products from the database that haven't yet had an order placed for them, and which also cost more than $100: Note above how I am using the "OrderDetails" association for each product as part of the query to only retrieve those products that have not had any orders placed for them. Change Tracking and DataContext.SubmitChanges() When we perform queries and retrieve objects like the product instances above, LINQ to SQL will by default keep track of any changes or updates we later make to these objects.  We can make any number of queries and changes we want using a LINQ to SQL DataContext, and these changes will all be tracked together.  Note: LINQ to SQL change tracking happens on the consuming caller side - and not in the database.  This means that you are not consuming any database resources when using it, nor do you need to change/install anything in the database to enable it. After making the changes we want to the objects we've retrieved from LINQ to SQL, we can then optionally call the "SubmitChanges()" method on our DataContext to apply the changes back to the database.  This will cause LINQ to SQL to dynamically calculate and execute the appropriate SQL code to update the database. For example, I could write the below code to update the price and # of units in stock of the "Chai" product in the database: When I call northwind.SubmitChanges() above, LINQ to SQL will dynamically construct and execute a SQL "UPDATE" statement that will update the two product property values we modified above. I could then write the below code to loop over unpopular, expensive products and set the "ReorderLevel" property of them to zero: When I call northwind.SubmitChanges() above, LINQ to SQL will calculate and execute an appropriate set of UPDATE statements to modify the products who had their ReorderLevel property changed. Note that if a Product's property values weren't changed by the property assignments above, then the object would not be considered changed and LINQ to SQL would therefore not execute an update for that product back to the database.  For example - if the "Chai" product's unitprice was already $2 and the number of units in stock was 4, then calling SubmitChanges() would not cause any database update statements to execute.  Likewise, only those products in the second example whose ReorderLevel was not already 0 would be updated when the SubmitChanges() method was called. Insert and Delete Examples In addition to updating existing rows in the database, LINQ to SQL obviously also enables you to insert and delete data.  You can accomplish this by adding/removing data objects from the DataContext's table collections, and by then calling the SubmitChanges() method.  LINQ to SQL will keep track of these additions/removals, and automatically execute the appropriate SQL INSERT or DELETE statements when SubmitChanges() is invoked. Inserting a New Product I can add a new product to my database by creating a new "Product" class instance, setting its properties, and then by adding it to my DataContext's "Products" collection: When we call "SubmitChanges()" above a new row will be created in our products table. Deleting Products Just as I can express that I want to add a new Product to the database by adding a Product object into the DataContext's Products collection, I can likewise express that I want to delete a product from a database by removing it from the DataContext's Products collection: Note above how I'm retrieving a sequence of discontinued products that no one has ever ordered using a LINQ query, and then passing it to the RemoveAll() method on my DataContext's "Products" collection.  When we call "SubmitChanges()" above all of these Product rows will be deleted from our products table. Updates across Relationships What makes O/R mappers like LINQ to SQL extremely flexible is that they enable us to easily model cross-table relationships across our data model.  For example, I can model each Product to be in a Category, each Order to contain OrderDetails for line-items, associate each OrderDetail line-item with a Product, and have each Customer contain an associated set of Orders.  I covered how to construct and model these relationships in Part 2 of this blog series. LINQ to SQL enables me to take advantage of these relationships for both querying and updating my data. For example, I could write the below code to create a new Product and associate it with an existing "Beverages" category in my database like so: Note above how I'm adding the Product object into the Category's Products collection.  This will indicate that there is a relationship between the two objects, and cause LINQ to SQL to automatically maintain the foreign-key/primary key relationship between the two when I call "SubmitChanges()". For another example of how LINQ to SQL can help manage cross-table relationships for us and help clean up our code, let's look at an example below where I'm creating a new Order for an existing customer.  After setting the required ship date and freight costs for the order, I then create two order line-item objects that point to the products the customer is ordering.  I then associate the order with the customer, and update the database with all of the changes. As you can see, the programming model for performing all of this work is extremely clean and object oriented.  Transactions A transaction is a service provided by a database (or other resource manager) to guarantee that a series of individual actions occur atomically - meaning either they all succeed or they all don't, and if they don't then they are all automatically undone before anything else is allowed to happen. When you call SubmitChanges() on your DataContext, the updates will always be wrapped in a Transaction.  This means that your database will never be in an inconsistent state if you perform multiple changes - either all of the changes you've made on your DataContext are saved, or none of them are. If no transaction is already in scope, the LINQ to SQL DataContext object will automatically start a database transaction to guard updates when you call SubmitChanges(). Alternatively, LINQ to SQL also enables you to explicitly define and use your own TransactionScope object (a feature introduced in .NET 2.0).  This makes it easier to integrate LINQ to SQL code with existing data access code you already have.  It also means that you can enlist non-database resources into the transaction - for example: you could send off a MSMQ message, update the file-system (using the new transactional file-system support), etc - and scope all of these work items in the same transaction that you use to update your database with LINQ to SQL. Validation and Business Logic One of the important things developers need to think about when working with data is how to incorporate validation and business rule logic.  Thankfully LINQ to SQL supports a variety of ways for developers to cleanly integrate this with their data models.  LINQ to SQL enables you to add this validation logic once - and then have it be honored regardless of where/how the data model you've created is used.  This avoids you having to repeat logic in multiple places, and leads to a much more maintainable and clean data model.  Schema Validation Support When you define your data model classes using the LINQ to SQL designer in VS 2008, they will by default be annotated with some validation rules inferred from the schema of the tables in the database. The datatypes of the properties in the data model classes will match the datatypes of the database schema.  This means you will get compile errors if you attempt to assign a boolean to a decimal value, or if you attempt to implicitly convert numeric types incorrectly. If a column in the database is marked as being nullable, then the corresponding property in the data model class created by the LINQ to SQL designer will be a nullable type.  Columns not marked as nullable will automatically raise exceptions if you attempt to persist an instance with a null value.  LINQ to SQL will likewise ensure that identity/unique column values in the database are correctly honored. You can obviously use the LINQ to SQL designer to override these default schema driven validation settings if you want - but by default you get them automatically and don't have to take any additional steps to enable them.  LINQ to SQL also automatically handles escaping SQL values for you - so you don't need to worry about SQL injection attacks when using it. Custom Property Validation Support Schema driven datatype validation is useful as a first step, but usually isn't enough for real-world scenarios.  Consider for example a scenario with our Northwind database where we have a "Phone" property on the "Customer" class which is defined in the database as an nvarchar.  Developers using LINQ to SQL could write code like below to update it using a valid telephone number:  The challenge that we will run into with our application, however, is that the below code is also legal from a pure SQL schema perspective (because it is still a string even though it is not a valid phone number): To prevent bogus phone numbers from being added into our database, we can add a custom property validation rule to our Customer data model class.  Adding a rule to validate phone numbers using this feature is really easy.  All we need to-do is to add a new partial class to our project that defines the method below: The code above takes advantage of two characteristics of LINQ to SQL: 1) All classes created by the LINQ to SQL designer are declared as "partial" classes - which means that developers can easily add additional methods, properties, and events to them (and have them live in separate files).  This makes it very easy to augment the data model classes and DataContext classes created by the LINQ to SQL designer with validation rules and additional custom helper methods that you define.  No configuration or code wire-up is required. 2) LINQ to SQL exposes a number of custom extensibility points in its data model and DataContext classes that you can use to add validation logic before and after things take place.  Many of these extensibility points utilize a new language feature called "partial methods" that is being introduced with VB and C# in VS 2008 Beta2.  Wes Dyer from the C# team has a good explanation of how partial methods works in this blog post here. In my validation example above, I'm using the OnPhoneChanging partial method that is executed anytime someone programmatically sets the "Phone" property on a Customer object.  I can use this method to validate the input however I want (in this case I'm using a regular expression).  If everything passes successfully, I just return from the method and LINQ to SQL will assume that the value is valid.  If there are any issues with the value, I can raise an exception within the validation method - which will prevent the assignment from taking place. Custom Entity Object Validation Support Property level validation as used in the scenario above is very useful for validating individual properties on a data model class.  Sometimes, though, you want/need to validate multiple property values on an objec