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 » SQL Server   (RSS)

• .NET
• ASP.NET
• Data
• LINQ
• Tips and Tricks

• LINQ to SQL (Part 8 - Executing Custom SQL BLOCKED EXPRESSION

  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 seven 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 Part 7: Updating our Database using Stored Procedures In my last two posts (Part 6 and Part 7) I demonstrated how you can optionally use database stored procedures (SPROCs) to query, insert, update and delete data using a LINQ to SQL data model.  One of the questions a few people have asked me since doing these posts has been "what if I want total control over the SQL expressions used by LINQ to SQL - but I don't want to use SPROCs to-do it?"  Today's blog post will cover that - and discuss how you can use custom SQL expressions that you provide to populate your LINQ to SQL data model classes, as well as perform insert, updates, and deletes. Using LINQ Query Expressions with LINQ to SQL For the purposes of this blog post, let's assume we've used the LINQ to SQL ORM designer in VS 2008 to define a set of data model classes like below for the Northwind database (note: read Part 2 of this series to learn how to use the LINQ to SQL ORM designer to do this): In Part 3 of this blog series I covered how you can use the new LINQ language support in VB and C# to query the above data model classes and return back a set of objects that represent the rows/columns in the database.  For example, we could add a "GetProductsByCategory" helper method to the DataContext class of our data model that uses a LINQ query to return back Product objects from the database: VB: C#: Once we've defined our encapsulated LINQ helper method, we can then write code like below that uses it to retrieve the products, and iterate over the results: VB:   When the LINQ expression within our "GetProductsByCategory" method is evaluated, the LINQ to SQL ORM will automatically execute dynamic SQL to retrieve the Product data and populate the Product objects.  You can use the LINQ to SQL Debug Visualizer to see in the debugger how this LINQ expression is ultimately evaluated. Using Custom SQL Queries with LINQ to SQL In our sample above we didn't have to write any SQL code to query the database and retrieve back strongly-typed Product objects.  Instead, the LINQ to SQL ORM automatically translated the LINQ expression to SQL for us and evaluated it against the database.  But what if we wanted total control over the SQL that is run against our database, and don't want LINQ to SQL to-do it for us in this scenario?  One way to accomplish this would be to use a SPROC like I discussed in Part 6 and Part 7 of this series.  The other approach is to use the "ExecuteQuery" helper method on the DataContext base class and use a custom SQL expression that we provide. Using the ExecuteQuery Method The ExecuteQuery method takes a SQL query expression as an argument, along with a set of parameter values that we can use to optionally substitute values into the query.  Using it we can execute any raw SQL we want against the database (including custom JOINs across multiple tables). What makes the ExecuteQuery method really useful is that it allows you to specify how you want the return values of your SQL expression to be typed.  You can do this either by passing a type-object as a parameter to the method, or by using a generic-based version of the method.  For example, we could change the GetProductsByCategory() helper method we created earlier - using a LINQ expression - to instead use the ExecuteQuery method to execute our own raw SQL expression against the database and return "Product" objects as a result: VB: C#: We can then call the GetProductsByCategory() helper method using the exact same code as before: But unlike before it will be our custom SQL expression that will run against the database - and not dynamic SQL executed in response to using a LINQ query expression. Custom SQL Expressions and Object Tracking for Updates By default when you retrieve a data model object using LINQ to SQL, it will track all changes and updates you make to it.  If you call the "SubmitChanges()" method on the DataContext class, it will then transactionally persist all of the updates back to the database.  I cover this in more depth in Part 4 of this LINQ to SQL series. One of the cool features of the ExecuteQuery() method is that it can fully participate in this object tracking and update model.  For example, we could write the code below to retrieve all products from a specific category and discount their prices by 10%: Because we typed the return value of our ExecuteQuery call in the GetProductsByCategory method to be of type "Product", LINQ to SQL knows to track the Product objects we returned from it.  When we call "SubmitChanges()" on the context object they will be persisted back to the database. Custom SQL Expressions with Custom Classes The ExecuteQuery() method allows you to specify any class as the return type of a SQL query.  The class does not have to be created using the LINQ to SQL ORM designer, or implement any custom interface - you can pass in any plain old class to it. For example, I could define a new ProductSummary class that has a subset of Product properties like below (notice the use of the new C# Automatic Properties feature): We could then create a GetProductSummariesByCategory() helper method on our NorthwindDataContext that returns results based on it.  Notice how our SQL statement below requests just the subset of product values we need - the ExecuteQuery method then handles automatically setting these on the ProductSummay objects it returns: We can then invoke this helper method and iterate over its results using the code below: Custom SQL Expressions for Inserts/Updates/Deletes In addition to using custom SQL expressions for queries, we can also execute them to perform custom Insert/Update/Delete logic. We can accomplish this by creating the appropriate partial Insert/Update/Delete method for the entity we want to change in a partial class on our DataContext.  We can then use the ExecuteCommand method on the DataContext base class to write the SQL we want to execute.  For example, to override the Delete behavior for Product classes we could define this DeleteProduct partial method: And now if we write the below code to remove a specific Product instance from our database, LINQ to SQL will call the DeleteProduct method - which will cause our custom SQL to execute in place of the default dynamic SQL that LINQ to SQL would otherwise use: Summary The LINQ to SQL ORM automatically generates and executes dynamic SQL to perform queries, updates, inserts and deletes against a database. For advanced scenarios, or cases where you want total control over the SQL query/command executed, you also have the ability to customize the ORM to use either SPROCs, or your own custom SQL Expressions, instead.  This provides you with a great deal of flexibility when building and extending your data access layer. 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.  Hope this helps, Scott Posted Monday, August 27, 2007 4:04 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, LINQ, SQL Server

• 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 object against each other.  Consider for example a scenario with an Order object where you set both the "OrderDate" and the "RequiredDate" properties: The above code is legal from a pure SQL database perspective - even though it makes absolutely no sense for the required delivery date of the new order to be entered as yesterday.  The good news is that LINQ to SQL in Beta2 makes it easy for us to add custom entity level validation rules to guard against mistakes like this from happening.  We can add a partial class for our "Order" entity and implement the OnValidate() partial method that will be invoked prior to the entity's values being persisted into the database.  Within this validation method we can then access and validate all of the data model class properties: Within this validation method I can check any of the entity's property values (and even obtain read-only access to its associated objects), and raise an exception as needed if the values are incorrect.  Any exceptions raised from the OnValidate() method will abort any changes from being persisted in the database, and rollback all other changes in the transaction. Custom Entity Insert/Update/Delete Method Validation There are times when you want to add validation logic that is specific to insert, update or delete scenarios.  LINQ to SQL in Beta2 enables this by allowing you to add a partial class to extend your DataContext class and then implement partial methods to customize the Insert, Update and Delete logic for your data model entities.  These methods will be called automatically when you invoke SubmitChanges() on your DataContext. You can add appropriate validation logic within these methods - and if it passes then tell LINQ to SQL to continue with persisting the relevant changes to the database (by calling the DataContext's "ExecuteDynamicXYZ" method): What is nice about adding the above methods is that the appropriate ones are automatically invoked regardless of the scenario logic that caused the data objects to be created/updated/deleted.  For example, consider a simple scenario where we create a new Order and associate it with an existing Customer: When we call northwind.SubmitChanges() above, LINQ to SQL will determine that it needs to persist a new Order object, and our "InsertOrder" partial method will automatically be invoked.  Advanced: Looking at the Entire Change List for the Transaction There are times when adding validation logic can't be done purely by looking at individual insert/update/delete operations - and instead you want to be able to look at the entire change list of operations that are occurring for a transaction.  Starting with Beta2 of .NET 3.5, LINQ to SQL now enables you to get access to this change list by calling the public DataContext.GetChangeList() method.  This will return back a ChangeList object that exposes collections of each addition, removal and modification that has been made.  One approach you can optionally employ for advanced scenarios is to sub-class the DataContext class and override its SubmitChange() method.  You can then retrieve the ChangeList() for the update operation and perform any custom validation you want prior to executing it:   The above scenario is a somewhat advanced one - but it is nice to know that you always have the ability to drop-down and take advantage of it if needed. Handling Simultaneous Changes with Optimistic Concurrency One of the things that developers need to think about in multi-user database systems is how to handle simultaneous updates of the same data in the database.  For example, assume two users retrieve a product object within an application, and one of the users changes the ReorderLevel to 0 while the other changes it to 1.  If both users then attempt to save the product back to the database, the developer needs to decide how to handle the change conflicts.  One approach is to just "let the last writer win" - which means that the first user's submitted value will be lost without the end-users realizing it.  This is usually considered a poor (and incorrect) application experience.  Another approach which LINQ to SQL supports is to use an optimistic concurrency model - where LINQ to SQL will automatically detect if the original values in the database have been updated by someone else prior to the new values being persisted.  LINQ to SQL can then provide a conflict list of changed values to the developer and enable them to either reconcile the differences or provide the end-user of the application with UI to indicate what they want to-do.  I'll cover how to use optimistic concurrency with LINQ to SQL in a future blog post. Using SPROCs or Custom SQL Logic for Insert/Update/Delete Scenarios One of the questions that developers (and especially DBAs) who are used to writing SPROCs with custom SQL usually ask when seeing LINQ to SQL for the first time is - "but how can I have complete control of the underlying SQL that is executed?"  The good news is that LINQ to SQL has a pretty flexible model that enables developers to override the dynamic SQL that is automatically executed by LINQ to SQL, and instead call custom insert, update, delete SPROCs that they (or a DBA) define themselves.  What is really nice is that you can start off by defining your data model and have LINQ to SQL automatically handle the insert, update, delete SQL logic for you.  You can then at a later point customize the data model to use your own custom SPROCs or SQL for updates - without having to change any of the application logic that is using your data model, nor would you have to change any of the validation or business rules logic supporting it (all of this stays the same).  This provides a lot of flexibility in how you build your application. I'll cover how to customize your data models to use SPROCs or custom SQL in a future blog post. Summary Hopefully the above post provides a good summary of how you can easily use LINQ to SQL to update your database, and cleanly integrate validation and business logic with your data models.  I think you'll find that LINQ to SQL can dramatically improve your productivity when working with data, and enable you to write extremely clean object-oriented data access code. In upcoming blog posts in this series I'll cover the new <asp:linqdatasource> control coming in .NET 3.5, and talk about how you can easily build data UI in ASP.NET that takes advantage of LINQ to SQL data models. I'll also cover some more specific LINQ to SQL programming concepts including optimistic concurrency, lazy and eager loading, table mapping inheritance, custom SQL/SPROC usage, and more. Hope this helps, Scott Posted Wednesday, July 11, 2007 1:00 PM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Data, LINQ, SQL Server

• Recipe: Deploying a SQL Database to a Remote Hosting Environment (Part 1)

  Scenario: You finish building a great ASP.NET application, have everything tested and working right on your local system, are taking full advantage of the new ASP.NET 2.0 Membership, Role and Profile features, and are ready to publish it to a remote hosting environment and share it with the world.  Copying the .aspx files and compiled assemblies to the remote system is pretty easy (just FTP or copy them up).  The challenge that confronts a lot of developers, though, is how to setup and recreate the database contents - both schema and data - on the remote hosted site.  Unfortunately there hasn't historically been a super-easy way to accomplish this. The good news is that this week the SQL Server team published the release candidate of a new SQL Server Hosting Toolkit that will make it much, much easier to deploy your SQL solutions remotely to a hosted environment.  The toolkit allows you to work with SQL Express, SQL Server 2000, and SQL Server 2005 databases locally, and then easily transfer your schema and data and install them into a shared hosting remote SQL Server account. The below post describes how you can start using this today.  SQL Server Hosting Toolkit The SQL Server Hosting toolkit is available for free, and ships with a Database Publishing Wizard that supports two database hosting deployment scenarios: 1) The Database Publishing Wizard enables you to point at a database you are working with on your local system, and then automatically create a .SQL script file that contains the setup logic needed to re-create an exact replica of the database on any remote system.  This .SQL script includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc - full details here), as well as populate the new database with the same table row contents as your local database (this is analogous to the MySQL dump utility).  The benefit of having this setup logic encapsulated in a single .SQL file is that most hosters already support the ability for you to upload .SQL files to their hosted environments and run these scripts via their hosting admin control panels.  Assuming you have a web hoster that supports this today, you can immediately start using the Database Publishing Wizard to easily deploy your sites without requiring anything to be installed or configured by the hoster. 2) The Database Publishing Wizard also enables you to point at a database you are working with on your local system, and then use web-services to transfer and recreate the database in your remote hoster environment (without you having to create the .SQL file or use the hoster admin control panel to run it).  This publishing option does require that a SQL Publishing web-service be exposed in the hosting environment, and the SQL Server Hosting Toolkit includes a free implementation of this SQL Publishing web-service that we'll be working with hosters to aggressively deploy.  The Database Publishing Wizard enables you to use either SQL Express or SQL Server 2000/2005 locally, and then use either SQL 2000 or SQL 2005 in the remote hoster environment.  It does not require that the versions of SQL match - so you can use SQL Express 2005 locally and then upload to a SQL 2000 server in the hosting environment without having to change any of your code.   The Database Publishing Wizard also supports handling the built-in ASP.NET 2.0 Membership, Role Management, Profile and Health Monitoring schemas.  A lot of people have run into issues because the built-in .SQL scripts that ship by default with ASP.NET for setting up these schemas require DBO permissions at install-time for the SQL scripts -- which a lot of hosters don't support (note: the scripts do not require DBO permissions at runtime - only for install time, but this can sometimes still be a blocker in itself unless the hoster is willing to install them for you).  The Database Publishing Wizard on the other-hand does not require DBO permissions when installing the ASP.NET Membership, Roles and Profile schemas/data, and should enable you to deploy the ASPNETDB tables + sprocs just as easily as any other database using the Database Publishing Wizard.  First Tutorial: Deploying a SQL Express Database to a SQL Server Hosting Account (using .SQL files) I'll be doing a series of posts over the next few weeks showing how to use the various features within the SQL Server Hosting Toolkit.  This first tutorial in the series covers how to use it to easily generate a .SQL installation file of a local SQL Express database that you can then copy to a remote hosting account and use to re-create a SQL Server database for you to use with your hosted site. Step 0: Download and Install the Database Publishing Wizard The first step we'll need to-do is to make sure we have the Database Publishing Wizard from the SQL Hosting Toolkit installed.  Click here to download it and install it. The Database Publishing Wizard comes with support for both a GUI based wizard, as well as a command-line utility.  The GUI based wizard can be run either standalone or via context-menu support that it adds to the Server Explorer in both Visual Studio 2005 and Visual Web Developer Express.  For the purposes of this tutorial we'll be using this later Server Explorer integration - which makes publishing really easy. Step 1: Create an ASP.NET web-site that uses a local SQL Express or SQL Server database To help with this demo, we will use the built-in Personal Starter Kit template that ships with both VS 2005 and Visual Web Developer Express.  To create a new web project based on it, select File->New Web Site within VWD or VS and choose the "Personal Starter Kit" template in the New Web-Site dialog.  By default the personal starter kit application is configured to use SQL Express (which is free and can be downloaded here).  When run the sample looks like below: After creating the application, you can then run the web admin tool (choose the WebSite->ASP.NET Configuration menu item in VWD/VS) and create a new user and add them to the "admin" role for the site.  You can then login as this new admin user and try uploading new pictures and/or customizing the existing ones on the site (note that both the picture meta-data, as well as the raw image binaries are stored in a database when you do this):   Once you are all done with the above steps we'll have two SQL Express databases installed within the \app_data directory for our project.  One of the SQL Express databases is named personal.mdf and contains the tables and stored procedures specific to our web-site (photo and album tables, as well as basic content management support).  The other SQL Express database is named aspnetdb.mdf and contains the database storage for the default ASP.NET 2.0 Membership, Role and Profile providers (which the application above is using for login and admin purposes). Step 2: Creating .SQL Installation Scripts for our Database Now that we've created a new application + local database, and added custom data to it (new users + their role membership, as well as new photos and albums), we want to deploy the application to a remote hosting server.  The first step we'll take is to create .SQL script files that will enable us to automate re-creating the exact same database schema + database content on our remote hosting account.  To-do this we'll use the Database Publishing Wizard we installed as part of the SQL Hosting Toolkit.  To begin, click on the "Server Explorer" tab within Visual Studio or Visual Web Developer to see the databases that the application is using: As you can see in the above picture, we have two SQL Express databases that we are using: ASPNETDB.MDF and Personal.MDF.  To generate .SQL installation files for each one, simply select the database in the solution explorer, then right-click and select the new "Publish to Provider" context menu item (added by the Database Publishing Wizard) setup on it: This will launch the Database Publishing Wizard and allow us to walkthrough scripting the installation of our database.  As I mentioned in the intro of this blog post, the Database Publishing Wizard supports two deployment options: 1) To generate .SQL install script files that you can copy to your remote hoster and run using their existing web admin control panel tools, or 2) To upload the database directly using Database Publishing Web-Services on the hoster web-site.  For this first tutorial, we'll be using the .SQL script file approach - so keep the default radio button selected and provide a name for the .SQL install script file you want to generate:   When you click "next" you'll be given the option to customize some of preferences when creating the .SQL setup file.  Note that you can control whether to drop existing objects within the target database, whether you want to target SQL 2000 or SQL 2005 with the script, and whether you want to setup both the schema and data, or just the schema, or just the data:  For this tutorial just keep the defaults selected, and hit next and generate the .SQL script: You now have a Personal .SQL file that contains a script that you can run on any SQL server to re-create all the tables, sprocs, views, triggers, full-text catalogs, etc. for a database, as well as import and add all of the table row data that was in the database at the time the .SQL file was created.  The .SQL file itself is just plain text - so you can open it up with any text editor to see it and/or customize it with your own statements: Notice above how the .SQL file includes both the SQL DDL needed to create the Photos table (including all of its constraints and primary-key/foreign-key relationships), as well as the SQL to insert data within the table once it is created (in the case above it is even inserting binary data for the photos - since they are stored in the database).  Once you repeat these steps for the ASPNETDB SQL Express database as well you'll have two .SQL installation scripts that you can use to automatically re-create your SQL database on any SQL Server:   Note that the .SQL files we built can be used to create two separate databases on a server, or they can both be run against the same database to create a single database that has a unified set of tables, sprocs, and data for the application.  To accomplish this, simply run both scripts against the same database, and assuming no table or sproc names conflict, you'll have a single database containing everything.  This later option is very useful when you have a hosting account that only provides 1 database instance for you to use! Step 3: Using our .SQL files to create our remote databases Now that we have our .SQL files, we can go about using them to install our database at our hoster.  Exactly how we use the .SQL files to install the database will vary depending on how the hoster gives us access to our SQL account.  Some hosters provide an HTML based file-upload tool that allows you to provide a .SQL file - which they will then execute against the SQL database you own.  Other hosters provide an online query tool (like below) that allows you to copy/paste SQL statements to run against your database.  If you have a hoster which provides an online query tool like this, then you can open the .SQL file with a text-editor and copy/paste the contents into the query textbox and run it. The quality of the SQL tools that different hosters provide varies quite a bit.  In testing the Database Publishing Wizard we found that some custom-made SQL admin tools provided by hosters had issues where they incorrectly parsed valid SQL statements (in particular GOTO statements).  This page describes one issue you might see some hosters have with GOTO statements, along with a workaround you can use.  To help improve the overall quality of SQL hosting admin tools, the SQL Server team early next year is going to be shipping the source to a free SQL HTML admin tool that hosters will be able to integrate into their experiences.  Hopefully this will help improve the standard experience with all Windows hosters. If your hoster has no usable HTML web admin tool for allowing you to easily manage your SQL database, then you can also just write a simple ASP.NET page that you FTP (along with your .SQL file) to your web-site and then hit to read the .SQL file on the server in as text, and then pass it as a string to ADO.NET to execute.  This will give you the same result as the query analyzer above - and fully create your database for you. Step 4: Updating our connection-string within web.config Once we've got our data uploaded within a database at our hoster, we'll want to upload our .aspx files, assemblies and content to the remote site (typically this is done over FTP).  The last step we'll need to take is to open up our web.config file and update the <connectionStrings> section to point at our new database location at the remote hoster.  Note that you'll need to get the exact SQL server, database name, and username/password account to use from the hoster. Using our personal starter kit example above, we'd change the <connectionStrings> section within its web.config file from the default connection-string (which uses two SQL Express database in the local \app_data directory): <connectionStrings>    <add name="Personal" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" />       <remove name="LocalSqlServer"/>    <add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /> </connectionStrings> To instead use a single SQL Server 2000 database (the "scottguDB" database on the "Server123" box).  <connectionStrings>    <add name="Personal" connectionString="Data Source=Server123;Initial Catalog=scottguDB;Integrated Security=True" providerName="System.Data.SqlClient" />    <remove name="LocalSqlServer"/>    <add name="LocalSqlServer" connectionString="Data Source=Server123;Initial Catalog=scottguDB;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> We were able to use a single database (instead of the two above) because we we ran both .SQL files against the single database - which merged all schema and data into a single database instance. Step 5: We are done Now we can run the application remotely in a hosted environment, and it should just work. Summary The Database Publishing Wizard that ships as part of the SQL Hosting Toolkit should make creating .SQL files for any database (SQL Express or full SQL Server) really easy.  You can use this to easily dump your local database and then use it to re-create the exact same database on a remote system. In future tutorials I'll also show how you can actually re-create your database remotely without even having to generate a .SQL file (instead you can publish the database directly from VS to your hoster over a web-service).  Stay tuned for details on how to-do this soon. Hope this helps, Scott P.S. We are planning on adding the Database Publishing Wizard into Visual Studio "Orcas" (which is the next release of VS), which means you won't need to download it separately in that timeframe.  But we wanted to make sure you didn't have to wait until then, which is why we are making it available today for Visual Studio 2005 and Visual Web Developer Express 2005. P.P.S. Please visit this page to find other ASP.NET Tips, Tricks and Recipes I've written on other topics in the past. Posted Friday, December 22, 2006 3:58 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, Tips and Tricks, Data, SQL Server

• Visual Studio for Database Professionals and other Cool Data Management Tools for .NET

  Earlier today CTP6 of the Visual Studio Team Edition for Database Professionals was made available for free download.  VS for Database Professionals has been getting rave reviews, and includes support for database refactorings, schema and data comparisons, database unit testing, and automated data generation.  You can learn more about it on its MSDN dev-center and Community page.  You can also watch a nice Channel9 video with the team here. Two other data management tools that I've seen posted on http://weblogs.asp.net recently that look very cool and are worth spending time to check out include: MassDataHandler - A free CodePlex project that provides a utility library to help automate data generation for unit testing (it can be used within any unit test framework).  Data Dictionary Creator -- A free tool that helps you document SQL Server databases, and helps you keep your documentation in sync with schema changes. Hope this helps, Scott Share this post: Email it! | bookmark it! | digg it! | reddit! Posted Wednesday, October 18, 2006 7:40 AM from ScottGu's Blog | 0 Comments Filed under: .NET, ASP.NET, Visual Studio, SQL Server

• Building and Using a "LINQ for SQL" Class Library with ASP.NET 2.0

  In my previous posts on doing data access using LINQ and ASP.NET, I used the built-in SQLMetal command-line utility to automatically generate the data model classes for my LINQ data classes.  Recently I've been using the LINQ for SQL (aka DLINQ) designer to define my data models instead, and have been really impressed with how easily it enables me to build a re-usable class library that nicely encapsulates my data and business logic. The below walkthrough demonstrates how you can get started with it yourself.  Simply install the LINQ May CTP download on top of VS 2005, and you can then follow along all of the steps below yourself.  For the sample below I am using C#, but everything works equally well in VB as well.   Step 1: Create a New LINQ Enabled Class Library Choose File->New Project within Visual Studio and navigate to the "LINQ Preview" node in the project-type tree-explorer.  Then select the "LINQ Library" project icon and create a new LINQ enabled class library: This will create a class library project whose MSBuild project file enables compilation support for LINQ.  A default "Class1.cs" file is added to the project -- I recommend just deleting it to start with an empty project.   Step 2: Add a LINQ for SQL Data Model to the Project Right-click on the project and choose the Add->New Item context menu item.  Scroll down in the dialog and select the "DLINQ Object" icon, and give it an appropriate name (in this sample I'm naming it "Northwind" since I'm going to be modeling the Northwind database): This will add a Northwind.dlinq file to your project, and bring up its designer: You can now use the designer to map your data models and entities graphically, as well as setup associations to map relationships between them.   Step 3: Quickly creating a Northwind data model One of the really easy ways to quickly define your data model classes is to open up the Server Explorer tab within VS 2005 and connect to your database.  You can then drill into the Tables and Views within your database, and drag/drop them from the server explorer onto the DLINQ designer surface to automatically create data models for them.  For example, if I dragged/dropped the Suppliers, Customers, Orders, Order Details, and Products tables onto the designer surface, I'd by default get a data model that looks like this: Note how the DLINQ designer automatically sets up default associations between the different entities based on the Primary Key/Foreign Key relationships in the database.  These will automatically cause properties to be created on the various data classes so that we can perform rich querying and/or easily traverse between them.  For example, if I had a "Customer" instance above I could simply access the "Customer.Orders" property to get a collection of all of that Customer's orders in the database. If you want, you can add, delete or alter these associations by simply clicking on the association links in the designer and then use the property grid to change its settings.  For example, if I wanted to modify the "Order Details" associations above to instead be named "OrderDetails", I would click on the association line in the designer and change it either via the property grid: Or because it supports inline editing for the name, just click on the name in the designer and rename it in place: Additionally, I could use the designer to easily rename the entities and/or rename/remove/add properties to them.  Once we are done defining your data models, we'll just save the file.  The designer will then automatically generate a .cs or .vb file containing the LINQ data class definitions.  This is saved in a nested file underneath the Northwind.dlinq item in the solution explorer: The LINQ data model classes created are defined as "partial" classes -- which means we will be able to go in later and add entity and property validation rules that enforce our business logic, as well as add any additional properties or helper methods we want to the classes.   Step 4: Creating a LINQ Enabled Web-Site that References our LINQ enabled Class Library We'll now want to go ahead and build a LINQ-enabled ASP.NET web-site that uses our LINQ class library.  Choose File->Add->Web Site within Visual Studio to add a new project to the solution.  Choose the LINQ web-site template to create an ASP.NET web-site that is enabled with the LINQ compilers: Your VS solution will then look like this: The last two steps you'll want to take are to: 1) Add a project reference from the web-site project to the LINQ class library.  To-do this, right-click on the web-site and select "Add Reference", select the "Projects" tab and add the reference as normal. 2) Add a connection-string to your web.config file in the web-site project for the LINQ class library.  This will enable you to easily configure and change the connection-string that is used at runtime for the application.  Simply copy/paste the connection-string value in the LINQ class library's app.config file to the web.config file's <connectionString> section to configure this. Once the above two steps are done, we are ready to build ASP.NET UI pages against our LINQ class library and associated data models.   Step 5: Build a simple Products Listing Page To give a simple taste of using LINQ, we'll build a simple Product Listing page that lists products like the image below: To implement this, I'll add a templated ASP.NET DataList control to the .aspx page using the markup below to define the UI for each product in our list: <asp:DataList ID="DataList1" RepeatColumns="2" runat="server">     <ItemTemplate>             <div class="productimage">             <img src="images/productimage.gif" />         </div>              <div class="productdetails">                      <div class="ProductListHead">                 <%#Eval("ProductName")%>             </div>                          <span class="ProductListItem">                 <strong>Price:</strong>                 <%# Eval("UnitPrice", "{0:c}") %>             </span>                      </div>                          </ItemTemplate> </asp:DataList> I can then use the below code-behind class to execute a LINQ query against the class library data model we defined in our class library previously to retrieve all of the products in the database supplier by the "Exotic Liquids" supplier and sorted by the product name: using System; using System.Web; using System.Query; using LINQClassLibrary; public partial class