Raj Kaimal

Altering Indexed Views - Gotcha

2009-07-10T04:41:39Z

According to BOL, “ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.”

We can see this behavior by creating an indexed view using the Northwind database. The script is shown below:

CREATE VIEW [dbo].[MyView] WITH SCHEMABINDING
AS
SELECT     ContactName, CompanyName
FROM         dbo.Customers

GO

CREATE UNIQUE CLUSTERED INDEX [inx_MyIndex] ON [dbo].[MyView] 
(
    [ContactName] ASC
)
GO

Note that the indexed view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.

Now the fun part. We alter the index view by adding a new column “ContactTitle”, like so:

ALTER VIEW [dbo].[MyView] WITH SCHEMABINDING
AS
SELECT     ContactName, CompanyName, ContactTitle
FROM         dbo.Customers

GO

-- Output from Management Studio
-- Command(s) completed successfully.

By looking at the view in management studio, after refreshing the tree, we see that the index is no longer there! I expected to see a warning message when this occurred :-(

The easiest way to work around this is to make a copy of the index creation script before you alter the indexed view and run it afterwards.

Using the Modal Popup Extender to build a popup search interface

2009-05-07T06:30:05Z

This post and sample code demonstrates how to use the Modal Popup Extender (MPE) to display a popup search box, select a record from the popup, hide the popup and display details for the selected record on the page using AJAX.

We will be using the Northwind database and displaying a “Find Customer” popup. Once a Customer is selected from the search result list, we hide the MPE and refresh UpdatePanels on the page with information related to the Customer that was picked. Sample source code is attached at the bottom of the page.

The events that occur as as follows:

When we click on the “Show Customer Picker” button, we have a popup appear with the help of an MPE. This MP has a UserControl with textboxes, a search button and a GridView inside an UpdatePanel. Performing a “Search” will cause only the contents of this UpdatePanel to get refreshed. To avoid the search GridView from binding when the popup is hidden (when an async postback occurs by some other button on the page), we keep track of the MP visibility (The technique for keeping track of the MPE visibility is described here). If the MP is hidden, we use the ObjectDataSourceSelectingEventArgs.Cancel method in the ObjectDataSource Selecting event to cancel the SQL call.

When we select an employee by clicking on the “Select” link, the Selecting event of the GridView is raised. We get the primary key of the selected customer by subscribing to this event. The MP is then hidden and a custom CustomerSelected event is raised.

The Page is subscribed to the CustomerSelected event and saves the selected customer primary key to Session (You could use other techniques instead of this). It then forces the UpdatePanels of other UserControls on the page to refresh themselves. These UpdatePanels have databound controls in them that get bound by making SQL calls using the customer PK stored in Session. The page ends up showing information about the selected customer (Customer Details, Last 10 orders, To 10 Orders) on the page without a full page refresh.

The sample website project shows you how to achieve this UI in three stages (three pages). The demo in Stage1 shows how the page is laid out without using AJAX – all postbacks result in a full page refresh. In Stage2, we add the UpdatePanels to perform partial page rendering. In Stage3, we add the MP that displays the “Find Customer” popup.

Don’t forget to add a reference to the Ajax Control ToolKit before building the project.

Source Code
Mirror

Check Modal Popup Extender visibility from code behind

2009-05-05T05:41:50Z

AFAIK, the Modal Popup Extender has no direct way to determine its visibility state from code behind. This post describes a workaround for that.

The idea here is to wire up handlers that the MPE fires just before it’s about to show the popup (showing event) and before it’s about to hide the popup (hiding event).

In the handlers, we set the property of a HiddenField webcontrol to either ‘1’ or ‘’. We then check the Value property of this HiddenField from code behind. If the MPE has a value of 1, we know the MPE is visible.

   1: Sys.Application.add_load(applicationLoadHandler);

2:

3:

   4: //Subscribe to the show and hide events of the modal popup.

   5: //Set a hidden field some value when visible and set to empty when hidden

   6: //This hidden field is used in code behind to determine the popup visibility.

   7: function applicationLoadHandler() {

   8:     var mpeEmployeeSearch = $find('mpeEmployeeSearch');

   9:     if (mpeEmployeeSearch) {

  10:         mpeEmployeeSearch.add_showing(employeeShowingHandler);

  11:         mpeEmployeeSearch.add_hiding(employeeHidingHandler);

  12:     }

  13: }

14:

  15: function employeeShowingHandler() {

  16:     $get('hfModalVisible').value = '1';

  17: }

18:

  19: function employeeHidingHandler() {

  20:     $get('hfModalVisible').value = '';

  21: }

The MPE in this case has been assigned a BehaviorID of mpeEmployeeSearch. The HiddenField WebControl has ID (rendered in HTML) of “hfModalVisible”.

Finally, we can check the MPE visibility from code behind like so:

   1: if (!string.IsNullOrEmpty(hfModalVisible.Value))

   2: {

   3:     //Do something

   4: }

Post a comment if you know of a better way.

Uploading an Excel file to SQL through an ASP.NET webform

2009-05-02T21:59:00Z

The method below describes how to upload a file to a webserver and then import the file into SQL using either LinqToSQL or SQL Bulk Copy. The sample code only shows how to import xls and xlsx files but it could easily be extended to support csv files too. Sample code is attached at the bottom.

We will be uploading data from an Excel file containing columns CompanyName and Phone and loading that into the Northwind Shippers table.

We’ll start by uploading the file to the webserver. This is done with the help of the FileUpload web control. The FileUpload control has a SaveAs method which saves the contents of the file into the location that we specify. The file will be stored in a temp folder under App_Data since App_Data is not browsable directly by users.

Once we have successfully uploaded the file to the webserver, we use an OleDbConnection and an OleDbDataReader to read each row from the Excel file. The OleDb connection string varies by file extension. The connection strings are shown below:

Extension	ConnectionString
xls	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1”
xlsx	Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1

HDR=Yes specifies that the first row of the data contains column names and not data
IMEX=1 specifies that the driver should always read the “intermixed” data columns as text.

The query we will be using with the connection is "SELECT CompanyName, Phone FROM [Sheet1$]". This assumes that we have an excel sheet called Sheet1 with header columns CompanyName and Phone.

Method 1: Using LINQ To SQL

Using the OleDBDataReader, we read each record and create a new Shipper object for each OleDbDataReader record as shown below. We add this object to the Shipper collection object that is associated with the Shipper table in the database using InsertOnSubmit and call SubmitChanges. This loads all the Excel records into the Shipper table.

Note: Since we are calling SubmitChanges without any Transaction defined, LINQ to SQL automatically starts a local transaction and uses it to execute the insert statements. When all insert statements successfully complete, LINQ to SQL commits the local transaction – nice:-) This occurs behind the scenes.

        1: //ref: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader(VS.71).aspx

       2: using (var context = new NorthwindDataContext())

       3: {

       4:     using (var myConnection = new OleDbConnection(base.SourceConnectionString))

       5:     using (var myCommand = new OleDbCommand(query, myConnection))

       6:     {

       7:         myConnection.Open();

       8:         var myReader = myCommand.ExecuteReader();

       9:         while (myReader.Read())

      10:         {

      11:             context.Shippers.InsertOnSubmit(new Shipper()

      12:             {

      13:                 CompanyName = myReader.GetString(0),

      14:                 Phone = myReader.GetString(1)

      15:             });

      16:         }

      17:     }

      18:  

      19:     context.SubmitChanges();

      20: }

Method 2: Using SQL BulkCopy

With the BulkCopy method, we first have to define the Column Mappings since we will not be inserting data into the autogenerated ShipperID Primary Key column. The first column in the Excel file (CompanyName) has to be mapped to the second column in the Shipper table and the second column (Phone) has to be mapped to the third column in the Shipper table as shown below.

We read each record from the OleDbDataReader and using the BulkCopy WriteToServer overload that takes in an IDataReader (which the OleDbDataReader implements). The BulkCopy, using this method bulk loads the Shippers destination table with the data from the OleDbDatareader.

       1: //ref: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader(VS.71).aspx

       2: //ref: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

       3: using (var myConnection = new OleDbConnection(base.SourceConnectionString))

       4: using (var destinationConnection = new SqlConnection(destinationConnectionString))

       5: using (var bulkCopy = new SqlBulkCopy(destinationConnection))

       6: {

       7:     //Map first column in source to second column in sql table (skipping the ID column).

       8:     //Excel schema[CompanyName,Phone] Table schema[ShipperID, CompanyName, Phone]

       9:     bulkCopy.ColumnMappings.Add(0, 1);

      10:  

      11:     bulkCopy.ColumnMappings.Add(1, 2);

      12:     bulkCopy.DestinationTableName = "dbo.Shippers";

      13:  

      14:     using (var myCommand = new OleDbCommand(query, myConnection))

      15:     {

      16:         myConnection.Open();

      17:         destinationConnection.Open();

      18:  

      19:         var myReader = myCommand.ExecuteReader();

      20:         while (myReader.Read())

      21:         {

      22:             bulkCopy.WriteToServer(myReader);

      23:         }

      24:     }

      25: }

The BulkCopy object is much faster than LINQ to SQL. I am copying Pablo Castro’s newsgroup response:

No per-row statement execution. When you do multiple inserts without bulk-copy, each insert is a statement in itself (regardless of whether it's batched together with other statements). With bulk-copy, we don't incur the cost of executing a statement for each row, the whole copy operation is a single thing.
No multiple network round-trips. Once the bulk-insert operation is setup, we send rows from the client to the server continously, without going back-and-forth over the wire.
Server storage engine also can greatly optimize how rows are inserted when performing a bulk-copy operation. How much can be optimized depends a lot on the recovery model the tarder database is set to; in "simple" and "bulk logged" the overhead of logging is greatly reduced during bulk-copy operations, helping a lot with performance.

Sample Code
Mirror

Implementing Deeplinking in Silverlight

2008-07-09T07:58:00Z

This article is now outdated. Silverlight 3 now has deeplinking built in. There might be other articles on this but this is the only one I could find on this topic: http://programwith.net/2009/03/23/Silverlight3NdashDeepLinking.aspx

Version: Silverlight 2 Beta 2 / .NET 3.5 SP1

See source code links at the bottom of this post.

The following post is an attempt at implementing deep linking in Silverlight. Deeplinking allows users to use the browser back and forward buttons to switch between different “states” of the application and also allows bookmarking a certain state of the app. It provides a great UX in scenarios like viewing a slide show, paging through a document etc. IMHO, I think deeplinking should be baked into Silverlight by default.

To implement this, we use the System.Web.Extensions assembly in .NET 3.5 SP1 Beta which adds native support for navigating forward and backward through the browser history stack and also allows bookmaking. We will add some custom code to our Silverlight application and host it inside an aspx page so that it can take advantage of the new features of the assembly or more specifically the JavaScript code in this assembly.

As a quick refresher, in the context of an ASP.NET AJAX application, we add new entries to the browser history stack by calling Sys.Application.addHistoryPoint(state, title) where state is a StringDictionary containing the state we wish to store and title is the page title.

The Sys.Application.navigate event is raised anytime the user clicks the browser back or forward button. To restore the state of the page, we subscribe to this event and call e.get_state() inside our event handler. You can read more on this topic here.

The idea here is to have our Silverlight application make a javascript call to addHistoryPoint anytime we wish to store the state of the application in the browser history. We then subscribe to the JavaScript Sys.Application.navigate event and change the state of our application when this event is raised.

We start by creating Silverlight application using Blend 2.5 June 2008 preview or VS 2008. In the App.cs class, we add a helper method for making the call to addHistoryPoint like so (FYI, HtmlPage.Window.Eval method performs the equivalent of making a JavaScript eval method call):

   1: /// <summary>   2: /// Call to Sys.Application.addHistoryPoint   3: /// </summary>   4: public void AddHistoryPoint(string stateKey, string stateValue, string title)   5: {   6:     string addHistoryScript = "Sys.Application.addHistoryPoint({{ {0}:'{1}' }}, '{2}');";   7:     HtmlPage.Window.Eval(string.Format(addHistoryScript, stateKey, stateValue, title));   8: }

The next step is to subscribe to the JavaScript Sys.Application.navigate event from our Silverlight managed code. To make our managed code accessible through javascript, we have to register our class as a scriptable object and then make our method accessible by marking it with the Scriptable attribute, Read more on this topic here.

As shown below, HandleNavigate is marked as a scriptable member which exposes it to JavaScript.

   1: /// <summary>   2: /// Events   3: /// </summary>   4: public event EventHandler<StateEventArgs> Navigate;   5:     6: /// <summary>   7: /// Handler for Sys.Application navigate event   8: /// </summary>   9: [ScriptableMember]  10: public void HandleNavigate(ScriptObject state)  11: {  12:     if (Navigate != null)  13:     {  14:         Navigate(this, new StateEventArgs() { State = state });  15:     }  16: }

In the code below, we register our class as a scriptable object (Line 13). We also dynamically load a Javascript script block which creates a helper JavaScript function that subscribes to the navigate event. The helper function then makes a call to our silverlight HandleNavigate method (Line 17). The last method in the script block is a call to __navigateHandler which is for restoring the state of the page when the page loads for the first time or through a bookmark.

   1: private void OnStartup(object sender, StartupEventArgs e)   2: {   3:     // Load the main control here   4:     this.RootVisual = new Page();   5:     InitScripts();   6: }   7:     8: /// <summary>   9: /// Register event hander through proxy method  10: /// </summary>  11: private void InitScripts()  12: {  13:     HtmlPage.RegisterScriptableObject("App", this);  14:     string initScript = @"  15:         var __navigateHandler = new Function('obj','args','document.getElementById(\'appId\').content.App.HandleNavigate(args.get_state())');  16:         Sys.Application.add_navigate(__navigateHandler);  17:         __navigateHandler(this, new Sys.HistoryEventArgs(Sys.Application._state));  18:      ";  19:     HtmlPage.Window.Eval(initScript);  20: }

With this in place, we can subscribe to the Navigate event from any class and use the state information to make changes to the state of our application.

   1: App app = (App)Application.Current;   2: app.Navigate += new EventHandler<StateEventArgs>(app_Navigate);

We call the AddHistoryPoint method to store the state of the app when needed as shown below.

   1: App app = (App)Application.Current;   2: string pageTitle = (activeSlideIndex == -1) ? "Home" : "Slide " + activeSlideIndex;   3: app.AddHistoryPoint("index", activeSlideIndex.ToString(), pageTitle);

The last step involved is to create an ASP.NET AJAX application, add a ScriptManager control, set the EnableSecureHistorState to false (so that our URL is human readable) and finally add a Silverlight control with the source set to the xap file.

   1: <asp:ScriptManager ID="ScriptManager1" runat="server" EnableHistory="true" EnableSecureHistoryState="False">   2: </asp:ScriptManager>   3: <div id="slcontent">   4:     <asp:Silverlight ID="appId" runat="server" Source="~/ClientBin/SilverlightHistory.xap"   5:         MinimumVersion="2.0.30523" Width="800" Height="600" BackColor="Black" />   6: </div>

You can download the sample project here or here (This is experimental beta code - use at your own risk).
Things you can try:

Clicking on the links in the right menu adds history points to the browser stack.
You can use the browser back and forward buttons to walk the stack.
You can bookmark a URL and return to it (example: localhost:9999/SilverlightHistory_Web/Default.aspx#index=2 will load the app with the third slide visible)

Another topic that has been making headlines recently is RIA SEO. Here are three articles on that:

Comments are always welcome.

Loading an XML file in your Silverlight project into memory

2008-07-08T06:36:00Z

Version : Silverlight 2 Beta 2

Reading an XML file in an XAP package can easily be done with the help of a helper class like so:

   1: public static class XmlHelper   2: {   3:     public static XElement LoadDocument(string fileName)   4:     {   5:         //No longer required in Silverlight 2 since   6:         //XmlXapResolver has been added as the default resolver for XmlReader   7:         //ref: http://msdn.microsoft.com/en-us/library/cc189007(vs.95).aspx   8:         //XmlReaderSettings settings = new XmlReaderSettings();   9:         //settings.XmlResolver = new XmlXapResolver();  10:         //XmlReader reader = XmlReader.Create(fileName, settings);  11:    12:         XmlReader reader = XmlReader.Create(fileName);  13:         XElement element = XElement.Load(reader);  14:         return element;  15:     }  16: }

Once you have the XElement object, you are free to manipulate it with LINQ to XML as shown below:

   1: public static List<Slide> LoadSlides()   2: {   3:     XElement element = XmlHelper.LoadDocument("Slides.xml");   4:     var slides = from slide in element.Descendants("Slide")   5:                  select new Slide {   6:                     Title = (string)slide.Element("Title"),   7:                     Text = (string)slide.Element("Text"),   8:                     Image = (string)slide.Element("Image"),   9:                  };  10:     return slides.ToList();  11: }

The method above returns a List<Slide> where the Slide class is defined like so:

   1: public class Slide   2: {   3:     public string Title { get; set; }   4:     public string Text { get; set; }   5:     public string Image { get; set; }   6: }

and the contents of the XML file we are reading is:

   1: <?xml version="1.0" encoding="utf-8"?>   2: <Slides>   3:   <Slide>   4:     <Title>Slide 1</Title>   5:     <Text>Slide 1 Description</Text>   6:     <Image>Slide1.jpg</Image>   7:   </Slide>   8:   <Slide>   9:     <Title>Slide 2</Title>  10:     <Text>Slide 2 Description</Text>  11:     <Image>Slide2.jpg</Image>  12:   </Slide>  13: </Slides>

Note that the XML file should have its BuildAction set to "Content".

A four stroke engine in Silverlight

2008-06-02T07:26:00Z

I decided to try out Expression Blend 2.5 beta by creating a four stroke engine animation. A screen capture is shown below:

You can see a working demo here (Silverlight 2 plugin required)

Here are a few observations while working on this project.

Writing XAML by hand is difficult :-) Expression Blend is your friend.
To export Adobe Illustrator files to XAML, use the free plugin from Mike Swanson. I chose to use AI to create the vector art because I am familiar with AI and it has a lot more features than Expression Design. When importing the XAML into Blend, you might see error messages in Blend about invalid markup as a result of the translation. In most cases these errors can easily be fixed by editing the XAML markup (Blend gives you the line number of the error).
For performing simple custom animations using a timer in your UI thread, use the DispatcherTimer class. The timer raises a Tick event at the end of the specified interval where you can update the UI. Note that the DispatcherTimer is single threaded and does not use the thread pool which could lead to a frozen UI if the Tick event handler takes too long to complete or if the thread is busy doing something else.
To apply multiple transformations to an object, for example a RotateTransform and a ScaleTransform at the same time, use the TransformGroup class.
Expression Blend, AFAIK, does not allow you to edit your C# files. You edit them in VS 2008. Be prepared for a lot of switching back and forth between both applications. In addition, VS 2008 sometimes goes crazy and refuses to show you the XAML preview when you have a complex XAML document. Restarting VS or recompiling the project usually makes the problem go away. Probably because I am using a beta product.
Animations can be created in expression blend using a timeline where you set keyframes that define the start and end points of the transform on an object. I chose, instead, to create the animations in code behind using C# using mathematical equations.

Overall, Expression Blend 2.5 beta was easy to work with. The UI could be improved a lot though. I plan to post feedback on Microsoft Connect soon.

You can download the VS 2008 project here or from the mirror here.

Connection strings in LINQ to SQL classes.

2008-05-21T03:12:00Z

Version : VS 2008 RTW

When you have a team working on a project that contains a LINQ to SQL class (dbml), you might see the following message when trying to add a Table entity or stored procedure in a dbml created by a fellow developer:

The objects you are adding to the designer use a different data connection than the designer is currently using. Do you want to replace the connection used by the designer?

The reason this happens is because the connection string in Server Explorer used to add the new stored procedure is different from what was originally used. In a team environment, this will occur if one developer checks in the dbml using one connection string and another developer checks out the dbml and tries to add a stored procedure or a table using a different connection string. Connection strings generally fall into one of the two shown below for SQL server:

Windows Authentication:

     Data Source=.\sqlexpress;Initial Catalog=northwind;Integrated Security=True
  

SQL Authentication:

    Data Source=.\sqlexpress;Initial Catalog=Northwind;Persist Security Info=True;User ID=northwind_web;Password=**
  

The connection string could easily vary by the type of authentication, the userid/password combination, instance name etc. This could become a source control nightmare with each developer checking in the dbml file with a connection string defined in their "Server Explorer". Therefore it is best, when possible, that all developers use the same connection string defined in "Server Explorer" when working with a dbml file for a given project.

This brings up the question of how to change connection string when the dbml class library is used in an ASP.net website. The solution is create a connection string in web.config with the same name as what is specified in app.config.

To understand why, we have to look at the class library dll. I have a sample project attached at the bottom of this post that contains a class library containing a dbml and the class library being referenced from an ASP.Net project. When the library gets built, a sealed class called Settings is generated internally. Here is the class with the help of reflector:

    [CompilerGenerated, GeneratedCode("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "9.0.0.0")]

    internal sealed class Settings : ApplicationSettingsBase

    {

        // Fields

        private static Settings defaultInstance = ((Settings) SettingsBase.Synchronized(new Settings()));

        // Properties

        public static Settings Default

        {

            get

            {

                return defaultInstance;

            }

        }

        [ApplicationScopedSetting, SpecialSetting(SpecialSetting.ConnectionString), DefaultSettingValue(@"Data Source=.\sqlexpress;Initial Catalog=northwind;Integrated Security=True"), DebuggerNonUserCode]

        public string northwindConnectionString

        {

            get

            {

                return (string) this["northwindConnectionString"];

            }

        }

    }

We see that the northwindConnectionString property has a DefaultSettingValue attribute containing the connection string. When Settings gets loaded at runtime, if it finds the same a connection string with name MyClassLibrary.Properties.Settings.northwindConnectionString in app.config or web.config, it will use that. If it does not find it, it reverts to the DefaultSettingValue. As long as our web.config has the same "name" as what the Settings class is looking for, that will get used in place of the default value.

What about the case where we want one connection string during development and another for production? In web.config, you can specify the configSource attribute in the connectionStrings section. This attribute specifies the fully qualified name and location of an external configuration file that contains the connectionStrings section. During development, you can have configSource point to the connectionString of your test server and when you are ready to deploy to staging or production, you can change the attribute to point to the appropriate external config file. You can also have this done automatically using Web Deployment Projects or MSBuild.

The sample project attached contains a class library with a connection string defined. This class library is used in an ASP.net website project containing a connection string in web.config with the same "name" as what was used in the class library thereby "overriding" the class library connection string value. The website project also has a configSource defined in web.config pointing to an external configuration file ConnectionStrings_Test.config. When deploying to production, the configSource will be set to point to ConnectionStrings_Prod.config which contains the production server connection string.

Comments are welcome. Thanks!

LinqDataSource exceptions

2008-02-01T02:05:00Z

Prerequisite: LinqDataSource & SqlDataSource Master/Details

When working with the LinqDataSource, you may get the exceptions listed below.

1. Operator '==' incompatible with operand types 'Int32' and 'Object'

The exception occurs because anytime a ControlParameter in the WhereParameters collection (IDictionary<string, object>) is null, it gets treated as type Object causing the LINQ dynamic expression parser comparison to fail. Consider the code snippet below:

     
    <asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="DataClassesDataContext"
    
        Select="new (OrderID, ProductID, UnitPrice, Quantity, Discount, Order)" TableName="Order_Details"
    
        Where="OrderID == @OrderID">
    
        <WhereParameters>
    
            <asp:ControlParameter ControlID="GridView1" Name="OrderID" PropertyName="SelectedValue"
    
                Type="Int32" />
    
        </WhereParameters>
    
    </asp:LinqDataSource>

This is part of the classic Master/Details scenario where the LinqDataSource fetches the Order Details based on the OrderID selected in the GridView1.

When the page loads the first time, the OrderID ControlParameter is equal to GridView1.SelectedValue. GridView1.SelectedValue is null since no record has been selected in the GridView1 yet. Unfortunately, the LinqDataSource still attempts to fetch the data. The Linq expression parser treats the null parameter as type Object and the comparison fails because it was expecting type Int32.

What we need here is a way to prevent the Select from occurring when any parameter is null. The LinqDataSource, unlike the SqlDataSource, for some reason, does not have a CancelSelectOnNullParameter property. This property when set to true, will cancel the select when any parameter in the SelectParameters collection is null.

We can implement this by handling the Selecting event of the LinqDataSource and call the Cancel method when any WhereParameter is null like so:

    
    protected void LinqDataSource2_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    
    {
    
        foreach (KeyValuePair<string, object> kvp in e.WhereParameters)
    
        {
    
            if (kvp.Value == null)
    
            {
    
                e.Cancel = true;
    
                return;
    
            }
    
        }
    
    }

2. Operator '==' incompatible with operand types 'Guid' and 'String'

This exception occurs in cases where a parameter in the WhereParameters collection is of type Guid.

    
    <asp:LinqDataSource ID="LinqDataSource2" runat="server" 
    
        ContextTypeName="DataClassesDataContext" 
    
        Select="new (DummyID, FirstName, LastName)" TableName="DummyTables" 
    
        Where="DummyID == @DummyID" onselecting="LinqDataSource2_Selecting">
    
        <WhereParameters>
    
            <asp:ControlParameter ControlID="GridView1" Name="DummyID" 
    
                PropertyName="SelectedValue" Type="Object" />
    
        </WhereParameters>
    
    </asp:LinqDataSource>

In the snippet above, you can see that the GridView1.SelectedValue is defined as a WhereParameter for the LinqDataSource. DummyID is the primary key of the data in GridView1 and is of type Guid. In the ControlParameter, its Type is set to Object because Guid is not available in the TypeCode enum. Unfortunately, this results in the linq expression parser treating the value as type String causing the comparison to fail.

According to the Dynamic Expression API, a flavor of which is used by the LinqDataSource internally, we can perform explicit conversions using the syntax type(expr) where type is a type name optionally followed by ? and expr is an expression. The expression language defines the following primitive types:

Object Boolean Char String SByte Byte
Int16 UInt16 Int32 UInt32 Int64 UInt64
Decimal Single Double DateTime TimeSpan Guid

The primitive types correspond to the similarly named types in the System namespace of the .NET Framework Base Class Library. You can also use the nullable form of a value type by writing a ? after the type name (ex: Where="Foo = Int32?(@Foo)").

Therefore, we can rewrite our where clause like so which gets rid of the exception.

    
    <asp:LinqDataSource ID="LinqDataSource2" runat="server" 
    
        ContextTypeName="DataClassesDataContext" 
    
        Select="new (DummyID, FirstName, LastName)" TableName="DummyTables" 
    
        Where="DummyID == Guid(@DummyID)" onselecting="LinqDataSource2_Selecting">
    
        <WhereParameters>
    
            <asp:ControlParameter ControlID="GridView1" Name="DummyID" 
    
                PropertyName="SelectedValue" Type="Object" />
    
        </WhereParameters>
    
    </asp:LinqDataSource>

IMO, both these exceptions and a couple of others could probably be avoided if the LinqDataSource had a CancelSelectOnNullParameter and if the TypeCode enum had included the primitive types listed above and nullable value types.

Comments are always welcome!

The .NET Framework 3.5 Commonly Used Types and Namespaces poster

2007-11-04T04:39:00Z

Paul Andrew has posted a link to the .NET framework 3.5 commonly Used Types and Namespaces poster - cool stuff!

Download it here.

Daniel Moth also has a good older post on the .NET 3.5 bits

Classic Menu UI in Office 2007

2007-10-10T04:28:11Z

So my 65 year old neighbor called me yesterday because he was frustrated with the Office 2007 Ribbon on his brand new machine...

After googling around, I found a great add-in for Office 2007 by Patrick Schmid called the RibbonCustomizer. It comes in two versions - the free Starter Edition and the Professional edition. Both versions, in addition to customizing the Ribbon, give you a "Classic UI" tab which emulate the Office 2003 menus and toolbars.

As you can see from the screen captures, it does not replace the Ribbon UI but adds onto it, which, IMHO, lets the user work with a familiar UI and allows them to slowly transition into the new UI.

For a core product like the Office Suite, Microsoft should have provided this ability by default.

My neighbor couldn't be happier...

.NET Framework Libraries Source Code - License - MS-RL

2007-10-03T18:17:00Z

When I first read Scott's post, I missed the Microsoft Reference License (Ms-RL) which the .net Framework libraries is released under.

The key thing to remember is this:

"Reference use" means use of the software within your company as a reference, in read only form, for the sole purposes of debugging your products, maintaining your products, or enhancing the interoperability of your products with the software, and specifically excludes the right to distribute the software outside of your company.

.NET Framework Libraries Source Code

2007-10-03T16:50:00Z

Scott just announced that his team will be releasing the souce code to the .NET base class libraries, ASP.net, Windows Forms and WPF!

The big advantage to this is that when you are stepping through the code, you no longer see the horrible "[External Code]' in your call stack. You are taken right to the source code! This really really helps in understanding how the internals of the classes work.

Wonder what I am going to do with reflector...hmm...

Read more at Scotts' blog

PS: The source code is being released under Ms-RL which means that I can view but I'm not allowed to copy and paste the source code.

SQL 2005 - Maximum number of characters displayed in each column

2007-09-27T17:52:00Z

This setting in Microsoft SQL server management studio, which has a default setting of 256 characters, caused me to waste almost half an hour yesterday. I was looking in the wrong places trying to troubleshoot my stored procedure and wondering why my data was being truncated. There is no indication in management studio that the text was being truncated either.

To increase the maximum value, open SQL Server management studio and go to

Tools » Options » Query Results » Results to Text » Maximum number of characters displayed in each column

Note also that there is a "Maximum Characters Retrieved" section under "Results to Grid" section too.

Building an RSS feed using LINQ to XML and LINQ to SQL

2007-09-26T06:02:00Z

Version : VS 2008 Beta 2

In this post, I am going to show you how to build a RSS feed of the employees in the NorthWind database using LINQ. Before you proceed, you may want to read Scott's introduction of LINQ to XML over here if you haven't done so already.

To start, lets look at how we can programmatically create the XML document below:

 1: <?xml version="1.0" encoding="utf-8" ?> 2: <Employees> 3: <Employee> 4: <Name>Tom</Name> 5: <ID>1</ID> 6: </Employee> 7: <Employee> 8: <Name>Jane</Name> 9: <ID>2</ID> 10: </Employee> 11: </Employees>

Without LINQ, we could use the XmlDocument class to create an in-memory tree representation of the document above and write it to the HttpResponse stream like so:

 1: XmlDocument doc = new XmlDocument(); 2:   3: XmlDeclaration xmldec = doc.CreateXmlDeclaration("1.0", "utf-8", null); 4: XmlElement root = doc.DocumentElement; 5: doc.InsertBefore(xmldec, root); 6:   7: XmlElement employees = doc.CreateElement("Employees"); 8: doc.AppendChild(employees); 9:   10: XmlElement employee1 = doc.CreateElement("Employee"); 11: employees.AppendChild(employee1); 12:   13: XmlElement empID1 = doc.CreateElement("ID"); 14: empID1.InnerText = "1"; 15: employee1.AppendChild(empID1); 16:   17: XmlElement empName1 = doc.CreateElement("Name"); 18: empName1.InnerText = "Tom"; 19: employee1.AppendChild(empName1); 20:   21: XmlElement employee2 = doc.CreateElement("Employee"); 22: employees.AppendChild(employee2); 23:   24: XmlElement empID2 = doc.CreateElement("ID"); 25: empID1.InnerText = "2"; 26: employee1.AppendChild(empID2); 27:   28: XmlElement empName2 = doc.CreateElement("Name"); 29: empName2.InnerText = "Jane"; 30: employee2.AppendChild(empName2); 31:   32: Response.Clear(); 33: doc.Save(Response.Output); 34: Response.End();

LINQ provides a easier and cleaner way to create XML documents known as functional construction. Using functional construction, you can create all or parts of your in-memory XML tree in a single statement. This is made possible by a constructor in the XElement and XDocument classes that take in a params object (FYI, XElement and XDocument both inherit from type XContainer).

 1: public XElement(XName name, params object[] content); 2: public XDocument(XDeclaration declaration, params object[] content);

The constructor calls an internal method called AddContentSkipNotify to validate the type of object passed into the params object. From the documentation, parameters can be any of the following:

A string, which is added as text content. This is the recommended pattern to add a string as the value of an element; the LINQ to XML implementation will create the internal XText node.
An XText, which can have either a string or CData value, added as child content. This is mainly useful for CData values; using a string is simpler for ordinary string values.
An XElement, which is added as a child element
An XAttribute, which is added as an attribute
An XProcessingInstruction or XComment, which is added as child content
An IEnumerable, which is enumerated, and these rules are applied recursively
Anything else, ToString() is called and the result is added as text content
null, which is ignored

Using functional construction, we can create our in memory XML tree and write it to the response stream like so:

   1: XDocument doc = new XDocument(   2:     new XDeclaration("2.0", "utf-8", "yes"),   3:         new XElement("Employees",   4:             new XElement("Employee",   5:                 new XElement("Name", "Tom"),   6:                 new XElement("ID", "1")),   7:             new XElement("Employee",   8:                 new XElement("Name", "Tom"),   9:                 new XElement("ID", "1"))));  10:    11: Response.Clear();  12: doc.Save(Response.Output);  13: Response.End();

This code is easier to write compared to the XmlDocument method. It is also easy see the underlying structure of the XML document by looking at the code.

From the documentation above, we know that if we pass in IEnumerable<T> to the XElement constructor, it will get enumerated and the rules will be applied recursively to each element in the collection.

With this knowledge, we could rewrite the code above with a LINQ TO XML query that returns an IEnumerable<XElement> from a collection of anonymous types like so:

   1:         var employees = new[] {   2:             new {Name = "Tom", ID = 1},   3:             new {Name = "Jane", ID = 2}   4:         };   5:     6:         XDocument doc = new XDocument(   7:             new XDeclaration("2.0", "utf-8", "yes"),   8:                 new XElement("Employees",   9:                         //LINQ query that returns IEnumerable<XElement>  10:                         from e in employees  11:                         select new XElement("Employee",  12:                             new XElement("Name", e.Name),  13:                             new XElement("ID", e.ID)  14:                         )  15:                 )  16:         );  17:    18:         Response.Clear();  19:         doc.Save(Response.Output);  20:         Response.End();

Note that "var employees" above is a collection of anonymous types. The output of this code will produce the same XML document like the previous two code samples did.

Instead of the hard coded employee collection, we could use a LINQ to SQL query that returns an IEnumerable<XElement> of employee names and their description from the database. That is the basic idea behind this post - Using LINQ to SQL to query the database, construct an in-memory XML tree using LINQ to XML by following the RSS specification and finally writing it out to the HttpResponse stream.

The XDocument we are going to construct will follow the RSS 2.0 specification (the minimum required) below:

   1: <?xml version="1.0" encoding="utf-8" standalone="yes"?>   2: <rss version="2.0">   3:   <channel>   4:     <title></title>   5:     <link></link>   6:     <description></description>   7:     <item>   8:       <title></title>   9:       <description></description>  10:       <link></link>  11:     </item>  12:     ...  13:     ...  14:     ...  15:     <item>  16:       <title></title>  17:       <description></description>  18:       <link></link>  19:     </item>  20:   </channel>  21: </rss>

Assuming we have created our Nortwind LINQ to SQL object model, we can write the final code in 25 lines(!) like this:

   1:     protected void Page_Load(object sender, EventArgs e)   2:     {   3:         NorthwindDataContext context = new NorthwindDataContext();   4:         Response.Clear();   5:         Response.ContentType = "text/xml";   6:     7:         XDocument document = new XDocument(   8:             new XDeclaration("1.0", "utf-8", null),   9:             new XElement("rss",  10:                 new XElement("channel",  11:                     new XElement("title", "Employees of Northwind Traders Inc."),  12:                     new XElement("link", "http://www.northwindtraders.com"),  13:                     new XElement("description", "Employees of Northwind Traders Inc. ordered by last name."),  14:                             from emp in context.Employees  15:                             orderby emp.LastName  16:                             select new XElement("item",  17:                                 new XElement("title", emp.LastName + ", " + emp.FirstName),  18:                                 new XElement("description", emp.Notes),  19:                                 new XElement("link", "http://www.northwindtraders.com/employees.aspx?id=" + emp.EmployeeID)  20:                             )  21:                     ),  22:                 new XAttribute("version", "2.0")));  23:         document.Save(Response.Output);  24:         Response.End();  25:     }

Note that the LINQ to SQL query fetches the employee name (LastName + FirstName columns) and the Notes column and creates an XElement called "title" and "description" respectively. We also create a dummy "link" node that points to www.northwindtraders.com since it is part of the requirement of the RSS specs. Once we have our XDocument created, we clear the Response stream, set the content type to text/xml and then "Save" the document to the HttpResponse stream.

This is not the optimal solution though since the DB is queried everytime the page is requested. We obviously have to cache the results on the server for a set amount of time. This can easily be done by calling the .ToList() extension method on the XDocument and storing the results in Cache. I show an example of how to cache the results of a LINQ query here

Note also that you should always HtmlEncode data before rendering it on the browser (which is not shown in the sample code).

I hope this post has shown you how super easy it is to create XML documents from content in the database using LINQ. To learn more, download VS 2008 Beta 2 and check out the LINQ samples.