Beth's Chinese blog
In the July/August issue of CoDe Magazine there's an article I wrote with Avner Aharoni (the Program Manager on XML literals) called Sharpening Your Axis with Visual Basic 9. (They just opened up online access to even non-subscribers today!) I highly recommend this article which shows you from the beginning how XML literals, embedded expressions and axis properties work in Visual Basic.
I start with the basic syntax and definitions and then walk through the XML to Schema tool (which has been added to VS2008 SP1) to enable XML IntelliSense, as well as show some practical examples of using these features with LINQ. Avner describes how IntelliSense works and discusses advanced scenarios with XML namespaces. We end with tips-and-tricks which get you thinking about using XML literals in other text generation/manipulation scenarios.
If you like this topic, so do I! Check out the XML topic feed from this blog for more resources.
Enjoy!
As you can probably tell from my last couple posts I've been working with WPF in different data scenarios. Yesterday I was playing with data validation in WPF and .NET 3.5 which is pretty slick. In this article I'll walk through how to hook up validation in your data objects using the IDataErrorInfo interface and then I'll go through a couple Validation ErrorTemplates you can use to display the validation error messages and cues to the user.
Performing Validation on Data Objects
If you're using custom business objects or LINQ to SQL classes you first need to implement the IDataErrorInfo interface in order to collect validation messages on your objects. If you are using DataSets on your WPF or Windows Forms, the DataRowView already implements this interface so you can just add validation to your DataTable partial classes and you're good to go. Just open the DataSet designer, right-click on the DataTable and select "View Code" and party on. For instance, if we have a customer DataTable we can write validation for the LastName field like so:
Partial Class CustomerDataSet Partial Class CustomerDataTable Private Sub CheckLastName(ByVal row As CustomerRow) If row.IsNull("LastName") OrElse row.LastName = "" Then row.SetColumnError(Me.LastNameColumn, "Please enter a last name") Else row.SetColumnError(Me.LastNameColumn, "") End If End Sub Private Sub CustomerDataTable_ColumnChanged(ByVal sender As Object, _ ByVal e As System.Data.DataColumnChangeEventArgs) _ Handles Me.ColumnChanged If e.Column Is Me.LastNameColumn Then Me.CheckLastName(CType(e.Row, CustomerRow)) End If End Sub Private Sub CustomerDataTable_TableNewRow(ByVal sender As Object, _ ByVal e As System.Data.DataTableNewRowEventArgs) _ Handles Me.TableNewRow Dim row As CustomerRow = CType(e.Row, CustomerRow) 'This will fire the ColumnChanged event which will give 'immediate feedback to user when a row is added. '(Stick other default values here too.) row.LastName = "" End Sub End Class End Class
If you're building our own custom business objects or are using LINQ to SQL classes then it's up to you to implement the IDataErrorInfo interface yourself. I showed how to do this with LINQ to SQL classes in this post where I set it up in a base business class. Here's a "short version" example implementation for a customer LINQ to SQL class that performs the same validation on the LastName field:
Partial Class Customer Implements System.ComponentModel.IDataErrorInfo 'This dictionary contains a list of our validation errors for each field Private validationErrors As New Dictionary(Of String, String) Protected Sub AddError(ByVal columnName As String, ByVal msg As String) If Not validationErrors.ContainsKey(columnName) Then validationErrors.Add(columnName, msg) End If End Sub Protected Sub RemoveError(ByVal columnName As String) If validationErrors.ContainsKey(columnName) Then validationErrors.Remove(columnName) End If End Sub Public Overridable ReadOnly Property HasErrors() As Boolean Get Return (validationErrors.Count > 0) End Get End Property Public ReadOnly Property [Error]() As String _ Implements System.ComponentModel.IDataErrorInfo.Error Get If validationErrors.Count > 0 Then Return String.Format("{0} data is invalid.", TypeName(Me)) Else Return Nothing End If End Get End Property Default Public ReadOnly Property Item(ByVal columnName As String) As String _ Implements System.ComponentModel.IDataErrorInfo.Item Get If validationErrors.ContainsKey(columnName) Then Return validationErrors(columnName).ToString Else Return Nothing End If End Get End Property Private Sub OnValidate(ByVal action As System.Data.Linq.ChangeAction) Me.CheckLastName(Me.LastName) If Me.HasErrors Then Throw New Exception(Me.Error) End If End Sub Private Sub OnLastNameChanging(ByVal value As String) Me.CheckLastName(value) End Sub Private Sub CheckLastName(ByVal value As String) If value = "" Then Me.AddError("LastName", "Please enter a last name") Else Me.RemoveError("LastName") End If End Sub End Class
Data Binding in WPF
Now that our data objects are validating themselves we can data bind them to a form. Setting up a simple WPF Window with some TextBoxes and binding them is easy in XAML once you get the knack for remembering the syntax ;-). The key is to make sure you specify the ValidatesOnDataErrors attribute on the Binding and set it to True. Take a look at the TextBoxes in the XAML below:
<Window x:Class="Window1" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="Customers" Height="253" Width="300" Name="Window1"> <Grid Margin="6"> <Grid.RowDefinitions> <RowDefinition Height="222*" /> <RowDefinition Height="40*" /> </Grid.RowDefinitions> <Grid.ColumnDefinitions> <ColumnDefinition Width="112*" /> <ColumnDefinition Width="166*" /> </Grid.ColumnDefinitions> <StackPanel Name="StackPanel1"> <Label Name="Label1" Width="Auto" HorizontalContentAlignment="Right" Margin="3"> Last Name:</Label> <Label Name="Label2" Width="Auto" HorizontalContentAlignment="Right" Margin="3"> First Name:</Label> <Label Name="Label3" Width="Auto" HorizontalContentAlignment="Right" Margin="3"> City:</Label> <Label Name="Label4" Width="Auto" HorizontalContentAlignment="Right" Margin="3"> State:</Label> <Label Name="Label5" Width="Auto" HorizontalContentAlignment="Right" Margin="3"> ZIP:</Label> </StackPanel> <StackPanel Grid.Column="1" Name="StackPanel2"> <TextBox Text="{Binding Path=LastName, ValidatesOnDataErrors=True}" Name="TextBox1" Height="28" Width="Auto" HorizontalContentAlignment="Left" Margin="3" /> <TextBox Text="{Binding Path=FirstName, ValidatesOnDataErrors=True}" Name="TextBox2" Height="28" Width="Auto" HorizontalContentAlignment="Left" Margin="3" /> <TextBox Text="{Binding Path=City, ValidatesOnDataErrors=True}" Name="TextBox3" Height="28" Width="Auto" HorizontalContentAlignment="Left" Margin="3"/> <TextBox Text="{Binding Path=State, ValidatesOnDataErrors=True}" Name="TextBox4" Height="28" Width="Auto" HorizontalContentAlignment="Left" Margin="3"/> <TextBox Text="{Binding Path=ZIP, ValidatesOnDataErrors=True}" Name="TextBox5" Height="28" Width="Auto" HorizontalContentAlignment="Left" Margin="3" /> </StackPanel> <Button Name="btnAdd" Grid.Column="1" Grid.Row="1" Margin="0,0,79,6" Height="24" Width="75" VerticalAlignment="Bottom" HorizontalAlignment="Right" >Add</Button> <Button Name="btnSave" Grid.Column="1" Grid.Row="1" HorizontalAlignment="Right" Margin="0,0,0,6" Width="75" Height="24" VerticalAlignment="Bottom">Save</Button> </Grid> </Window>
Now we can load our data and set it to the Window.DataContext in the Window_Loaded event handler. If you're using DataSets, then set up your TableAdapter query like normal and Fill the DataSet. Then set the Window's DataContext to the customer DataTable:
Me.CustomerTableAdapter.Fill(Me.MyCustomerData.Customer) Me.DataContext = Me.MyCustomerData.Customer
If you're using LINQ to SQL classes then just call upon the SQLDataContext to load your list of customers:
Dim db As New MyDatabaseDataContext Me.DataContext = From Customer In db.Customers _ Where Customer.LastName = "Massi"
WPF's Default Validation ErrorTemplate
So if we were to run this as-is WPF would give us a default visual cue when our validation fails. The control is drawn with a red border indicating there is a problem, however no message is displayed. Oh yea, that's helpful! Prepare for your tech support phone lines to light up if you release this baby.
Specifying a Custom Validation Style
We obviously want to let the user know what needs fixing here. Let's just do something simple and display the message in a ToolTip. For now, we can just create a Style in our Window.Resources section that applies to the Textboxes on this form. The Style sets up a Trigger that sets the ToolTip property to the validation message when the Validation.HasError changes to True:
<Window.Resources> <Style TargetType="TextBox"> <Style.Triggers> <Trigger Property="Validation.HasError" Value="true"> <Setter Property="ToolTip" Value="{Binding RelativeSource={x:Static RelativeSource.Self}, Path=(Validation.Errors)[0].ErrorContent}"/> </Trigger> </Style.Triggers> </Style> </Window.Resources>
Now when we run this again, you'll see that when you hover over the TextBox the validation message is displayed in a ToolTip. Better! But this solution only displays for TextBoxes. What about the rest of our controls like CheckBoxes, ComboBoxes, etc.? And we really want to declare all this in one place for our entire application. No problem, we can stick this Style into the Application.Resources instead. We can also specify that the TargetType="Control" and then we can declare additional styles for the rest of our controls and base them on this one. Open up your Application.xaml and add this XAML to your Resources section:
<Application.Resources> <Style TargetType="Control" x:Key="myErrorTemplate"> <Style.Triggers> <Trigger Property="Validation.HasError" Value="true"> <Setter Property="ToolTip" Value="{Binding RelativeSource={x:Static RelativeSource.Self}, Path=(Validation.Errors)[0].ErrorContent}"/> </Trigger> </Style.Triggers> </Style> <Style TargetType="TextBox" BasedOn="{StaticResource myErrorTemplate}" /> <Style TargetType="CheckBox" BasedOn="{StaticResource myErrorTemplate}" /> <Style TargetType="ComboBox" BasedOn="{StaticResource myErrorTemplate}" /> </Application.Resources>
We just need to specify a x:Key for the Template and then we can set the BasedOn attribute on the inherited Styles. Now all the controls in the entire application can pick up this Style.
Replacing the Entire ErrorTemplate
So far all we've done is specify a Style Trigger. The default WPF ErrorTemplate is still being utilized as we're still seeing the red border around the control. We can completely change the ErrorTemplate that is used by defining a new one here in the Application.Resources. Let's take a simple example by setting up our ErrorTemplate to display a generic message over the control. In the Style above the Trigger section (we'll leave the ToolTip mesage there) we set the Validation.ErrorTemplate property and its Value to our very own ControlTemplate.
<Style TargetType="Control" x:Key="myErrorTemplate"> <Setter Property="Validation.ErrorTemplate"> <Setter.Value> <ControlTemplate> <TextBlock Foreground="Red" Text="DOH! Thank you for trying."/> </ControlTemplate> </Setter.Value> </Setter> <Style.Triggers> <Trigger Property="Validation.HasError" Value="true"> <Setter Property="ToolTip" Value="{Binding RelativeSource={x:Static RelativeSource.Self}, Path=(Validation.Errors)[0].ErrorContent}"/> </Trigger> </Style.Triggers> </Style>
Now when we run this again, we still get our ToolTip when we hover over the control, but now we're also overlaying the control with TextBlock we defined in our ControlTemplate. Notice that there's no more red border:
Okay a pretty lame example, I admit. The problem (besides being a sarcastic message) is that the TextBlock is really covering the control and you have to hover over the edge to get the ToolTip to display. The other problem of course is that if we start typing into the field again the message won't disappear until we tab off if it so that's pretty annoying.
Instead you can stick a DockPanel into the ControlTemplate and Dock the TextBlock to the right in order to display the text after the control (and this time let's just display an asterisk). Say you want to still have that red border around the control. We can do this by specifying a special element called AdornedElementPlaceholder in our XAML for the ErrorTemplate Setter.Value:
<Setter Property="Validation.ErrorTemplate"> <Setter.Value> <ControlTemplate> <DockPanel LastChildFill="True"> <TextBlock DockPanel.Dock="Right" Foreground="Red" FontSize="11pt" FontWeight="Bold">* </TextBlock> <Border BorderBrush="Red" BorderThickness="1"> <AdornedElementPlaceholder Name="myControl"/> </Border> </DockPanel> </ControlTemplate> </Setter.Value> </Setter>
Much better! Of course you can use your imagination to create any kind of visual cue appropriate for your application. That's the cool thing about WPF.
Duplicating the Winforms ErrorProvider Look and Feel
For those Winforms developers out there, what if you want to duplicate the look and feel of the ErrorProvider which displays a blinking error icon? I always liked to place the red error icon inside the right-hand side of the control so I didn't have to worry about spacing issues between the controls when I was designing forms. And I actually liked how the icon would flash a few times and then stop. It's relatively easy to do this type of animation in WPF using Storyboards (and it's REALLY easy to create animations in Expression Blend so I highly recommend you have a look at that product if you're making the transition to WPF).
This time we'll create an Ellipse and set up an EventTrigger for the Loaded event to begin our animation which will simply toggle the Visibility property of the Ellipse a few times. We also want to place a TextBlock over the Ellipse whose Text is an exclamation point (the animation will run on this as well). And since I want to place these inside the right-hand side of the control by setting a negative left margin, I'm going to want to also set the ToolTips of the Ellipse and the TextBlock so that if the user hovers over the error glyph it will display the ToolTip as well.
Here's the complete XAML to enable this look and feel contained in the Application.Resources:
<Application.Resources> <Storyboard x:Key="FlashErrorIcon"> <ObjectAnimationUsingKeyFrames BeginTime="00:00:00" Storyboard.TargetProperty="(UIElement.Visibility)"> <DiscreteObjectKeyFrame KeyTime="00:00:00" Value="{x:Static Visibility.Hidden}"/> <DiscreteObjectKeyFrame KeyTime="00:00:00.2000000" Value="{x:Static Visibility.Visible}"/> <DiscreteObjectKeyFrame KeyTime="00:00:00.4000000" Value="{x:Static Visibility.Hidden}"/> <DiscreteObjectKeyFrame KeyTime="00:00:00.6000000" Value="{x:Static Visibility.Visible}"/> <DiscreteObjectKeyFrame KeyTime="00:00:00.8000000" Value="{x:Static Visibility.Hidden}"/> <DiscreteObjectKeyFrame KeyTime="00:00:01" Value="{x:Static Visibility.Visible}"/> </ObjectAnimationUsingKeyFrames> </Storyboard> <Style x:Key="myErrorTemplate" TargetType="Control"> <Setter Property="Validation.ErrorTemplate"> <Setter.Value> <ControlTemplate> <DockPanel LastChildFill="True"> <Ellipse DockPanel.Dock="Right" ToolTip="{Binding ElementName=myTextbox, Path=AdornedElement.(Validation.Errors)[0].ErrorContent}" Width="15" Height="15" Margin="-25,0,0,0" StrokeThickness="1" Fill="Red" > <Ellipse.Stroke> <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5"> <GradientStop Color="#FFFA0404" Offset="0"/> <GradientStop Color="#FFC9C7C7" Offset="1"/> </LinearGradientBrush> </Ellipse.Stroke> <Ellipse.Triggers> <EventTrigger RoutedEvent="FrameworkElement.Loaded"> <BeginStoryboard Storyboard="{StaticResource FlashErrorIcon}"/> </EventTrigger> </Ellipse.Triggers> </Ellipse> <TextBlock DockPanel.Dock="Right" ToolTip="{Binding ElementName=myControl, Path=AdornedElement.(Validation.Errors)[0].ErrorContent}" Foreground="White" FontSize="11pt" Margin="-15,5,0,0" FontWeight="Bold">! <TextBlock.Triggers> <EventTrigger RoutedEvent="FrameworkElement.Loaded"> <BeginStoryboard Storyboard="{StaticResource FlashErrorIcon}"/> </EventTrigger> </TextBlock.Triggers> </TextBlock> <Border BorderBrush="Red" BorderThickness="1"> <AdornedElementPlaceholder Name="myControl"/> </Border> </DockPanel> </ControlTemplate> </Setter.Value> </Setter> <Style.Triggers> <Trigger Property="Validation.HasError" Value="true"> <Setter Property="ToolTip" Value="{Binding RelativeSource={x:Static RelativeSource.Self}, Path=(Validation.Errors)[0].ErrorContent}"/> </Trigger> </Style.Triggers> </Style> <Style TargetType="TextBox" BasedOn="{StaticResource myErrorTemplate}" /> <Style TargetType="CheckBox" BasedOn="{StaticResource myErrorTemplate}" /> <Style TargetType="ComboBox" BasedOn="{StaticResource myErrorTemplate}" /> </Application.Resources>
Now when we run our application and trigger the validation error we see an error icon that flashes 3 times (it looks a lot smoother than this image ;-))
Validating your data objects in WPF with the .NET Framework 3.5 is the same as before with WinForms using the IDataErrorInfo interface. However, WPF styles and control templates make displaying visual cues to the user extremely flexible. If you can imagine it, you can probably do it with WPF.
In my last post on this subject I explored creating WPF UI's dynamically using XML literals. The one part that bugged me a bit was that even though the UI was dynamic, we were using a fixed object model of our customer (using LINQ to SQL classes). I wanted to augment this code a bit more so that we could not only dynamically generate the WPF UI but also dynamically edit any maintenance table in our database -- not just customer. What I really wanted to enable is if we modify the database schema of any of our maintenance tables then we don't have to update our object model and recompile our code.
To achieve this I decided to explore loading and editing a simple untyped DataTable at runtime. This worked well using XML literals again -- this time to generate the SELECT and UPDATE statements for the SqlDataAdapter. One thing to note, this technique relies on the database to enforce any validation rules. This is why I would only use this type of dynamic form to edit very simple tables (aka. maintenance tables).
For this example I decided to create a typed DataTable for my TableSchema instead as well, eliminating the need for LINQ to SQL classes in the project. If you recall this is used to hold the column metadata (ColumnName, DataType, etc.) for the table we want to edit. I just right-clicked on my project to add a new item and selected DataSet. I named it TableSchemaDataSet and then just simply dragged the GetTableSchema stored procedure (which we added to the Northwind database in the last post) onto the design surface. This automatically creates a typed DataTable for us with no fuss. I renamed the DataTable to TableSchema and saved it.
Now for the fun part. We need to load an untyped DataTable into our dynamically generated WPF form because we don't want to make any assumptions about the schema of the table we're editing (except that there is a primary key of some sort). DataTables and DataSets work well with WPF but there are a couple things we need to set up manually since we're loading this all at runtime. (Writing the code to load and save the DataTable makes me REALLY appreciate the DataSet designer and the code that it generates for you).
First I set up a Public property on the form to hold the name of the table we want to edit called TableName and set the default to "Shippers" this time. Then I created some private class level variables to reference the ADO.NET objects we'll need. (Take a look at the last post for the XAML markup of the Window, it's exactly the same for this example.)
Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"> Imports <xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> Imports System.Windows.Markup Imports System.Data.SqlClient Imports System.Data Partial Public Class Window2 'This is the metadata table we created in the DataSet Designer Private TableSchema As New TableSchemaDataSet.TableSchemaDataTable 'ADO.NET objects used to load and save the table we're editing Private TableDataAdapter As New SqlDataAdapter Private TableConnection As New SqlConnection(My.Settings.NorthwindConnectionString) Private Table As DataTable 'This is the key field used in searching for a row in this example Private PKField As TableSchemaDataSet.TableSchemaRow 'This property can be set before the Form.Show() to edit any table Private m_tableName As String = "Shippers" Public Property TableName() As String Get Return m_tableName End Get Set(ByVal value As String) m_tableName = value End Set End Property
In the Loaded event handler we can now load the metadata, create and load our XAML to display our UI just like before, and then set our UpdateCommand on the TableDataAdapter.
Private Sub Window1_Loaded() Handles MyBase.Loaded Try 'Get the schema of the database table we want to edit Dim taSchema As New TableSchemaDataSetTableAdapters.TableSchemaTableAdapter taSchema.Fill(Me.TableSchema, Me.TableName) 'Create the DataTable that will hold the record we're editing Me.Table = New DataTable(Me.TableName)
Me.Title = Me.TableName Me.LoadUI() Me.SetPrimaryKey() Me.SetUpdateCommand() Catch ex As Exception MsgBox(ex.ToString) Me.Close() End Try End Sub Private Sub LoadUI() Dim UI = <Grid xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" Name="Grid1"> <Grid.ColumnDefinitions> <ColumnDefinition Width="100*"/> <ColumnDefinition Width="200*"/> </Grid.ColumnDefinitions> <StackPanel Name="StackLabels" Margin="3"> <%= From column In Me.TableSchema _ Where column.IsPrimaryKey = 0 AndAlso column.DataType <> "timestamp" _ Select <Label Height="28" Name=<%= column.ColumnName & "Label" %> HorizontalContentAlignment="Right"> <%= column.ColumnName %>:</Label> %> </StackPanel> <StackPanel Grid.Column="1" Name="StackFields" Margin="3"> <%= From column In Me.TableSchema _ Where column.IsPrimaryKey = 0 AndAlso column.DataType <> "timestamp" _ Select GetUIElement(column) %> </StackPanel> </Grid> Me.DynamicContent.Content = XamlReader.Load(UI.CreateReader()) End Sub Private Function GetUIElement(ByVal columnInfo As TableSchemaDataSet.TableSchemaRow) As XElement Select Case columnInfo.DataType.ToLower Case "datetime", "int", "smallint", "money" Return <TextBox Height="28" Name=<%= "txt" & columnInfo.ColumnName %> Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/> Case "bit" Return <CheckBox HorizontalContentAlignment="Left" Name=<%= "chk" & columnInfo.ColumnName %> IsChecked=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>> <%= columnInfo.ColumnName %> </CheckBox> Case Else Return <TextBox Height="28" Name=<%= "txt" & columnInfo.ColumnName %> MaxLength=<%= columnInfo.MaxLength %> Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/> End Select End Function
Now that we've got the UI defined I'll set the primary key field (which is a TableSchemaDataRow object) so that we can use this in our UPDATE statement as well as in the SELECT query when the user clicks the Find button on the form. Generally primary keys are surrogate keys (like auto-incrementing integers) and mean nothing to the user, so instead you may want to create another Public property that captures the search field name. Since there is only one primary key field on a table, I use the FirstOrDefault() extension method which returns the first of the sequence.
Private Sub SetPrimaryKey() 'Grab the Primary Key column of the table we want to edit so we can use it in the search Me.PKField = (From column In Me.TableSchema Where column.IsPrimaryKey = 1).FirstOrDefault() End Sub
To create the SELECT statement, notice that I'm once again using XML literals but this time I'm not creating XML. Instead I'm creating a string by calling the XElement's .Value property. We then can create the SqlCommand and fill our untyped DataTable with the results. Setting the WPF form's DataContext sets up the data binding to the fields we specified when we generated the XAML above.
Private Sub btnFind_Click() Handles btnFind.Click If Me.txtSearch.Text <> "" Then Try 'Create the SELECT command Dim cmdText = <s> SELECT * FROM <%= Me.TableName %> WHERE <%= Me.PKField.ColumnName %> = <%= If(Me.PKField.DataType.Contains("char"), _ "'" & Me.txtSearch.Text & "'", _ Me.txtSearch.Text) %> </s>.Value Dim cmd As New SqlCommand(cmdText, Me.TableConnection) Me.Table.Clear() Me.TableDataAdapter.SelectCommand = cmd Me.TableDataAdapter.Fill(Me.Table) Me.DataContext = Me.Table Dim view = CollectionViewSource.GetDefaultView(Me.Table) view.MoveCurrentToFirst() Catch ex As Exception MsgBox(ex.ToString) Me.DataContext = Nothing End Try Else Me.DataContext = Nothing End If End Sub
We want to be able to edit and save this record so we need to also generate an UPDATE command. For this example I'm only setting up the UpdateCommand on the SqlDataAdapter because we're creating a form that just edits records, but it's easy enough to create Delete and Insert commands as well. Here I'm using XML Literals again (of course! ;-)).
Private Sub SetUpdateCommand() 'Set the UpdateCommand so that we can save edited records in the table Dim cmdText = <s> UPDATE <%= Me.TableName %> SET <%= From column In Me.TableSchema _ Where column.IsPrimaryKey = 0 AndAlso column.DataType <> "timestamp" _ Select <c> <%= column.ColumnName %> = @<%= column.ColumnName %> <%= If(Me.TableSchema.Rows.IndexOf(column) < _ Me.TableSchema.Rows.Count - 1, ", ", "") %> </c>.Value %> WHERE <%= Me.PKField.ColumnName %> = @<%= Me.PKField.ColumnName %> <%= From column In Me.TableSchema _ Where column.IsPrimaryKey = 0 AndAlso column.DataType = "timestamp" _ Select <c> AND <%= column.ColumnName %> = @<%= column.ColumnName %> </c>.Value %> </s>.Value Dim cmd As New SqlCommand(cmdText, Me.TableConnection) Dim p As SqlParameter For Each column In Me.TableSchema If column.IsPrimaryKey = 0 AndAlso column.DataType = "timestamp" Then 'Note: It's recommended to use a TimeStamp column in your tables for concurrency checking p = New SqlParameter("@" & column.ColumnName, SqlDbType.Timestamp) p.SourceVersion = DataRowVersion.Original p.SourceColumn = column.ColumnName cmd.Parameters.Add(p) Else p = New SqlParameter("@" & column.ColumnName, _ CType([Enum].Parse(GetType(SqlDbType), column.DataType, True), SqlDbType)) p.SourceColumn = column.ColumnName p.SourceVersion = DataRowVersion.Current cmd.Parameters.Add(p) End If Next Me.TableDataAdapter.UpdateCommand = cmd End Sub
One important note here is that when I create the UpdateCommand, I'm assuming that concurrency checking is being done with a TimeStamp field (which I've added to my copy of Northwind) but you can also create the "long version" where it checks original against current values as well.
So now we can simply set the TableName property of this form and it will dynamically generate the UI, load the data when we click the Find button, and allow us to save our changes back to the database. Try modifying your database table's schema and running it again without recompiling. Slick.
I've uploaded this project onto CodeGallery with both forms (this one and the one we did in the last post) so have a look. You'll need to download the Northwind database here first.
On Saturday evening I had the privilege of attending the ACM (Association for Computing Machinery) Awards presentation held at the Palace Hotel in San Francisco. I must say it was humbling and exciting to see such amazing computer scientists all in one ballroom. These people have changed and continue changing the computer science field. It was also encouraging to see that there were many brilliant women there.
A friend, Larry Constantine, invited us to attend because he was being awarded ACM Technical Fellow for his many achievements in the computer software field. I was in charge of the camera so I hope the pictures came out! ;-)
The Turing Award (this is like the Nobel prize for computer science) was given to Edmund M. Clarke, E. Allen Emerson, and Joseph Sifakis "for their role in developing Model-Checking into a highly effective verification technology, widely adopted in the hardware and software industries."
Check out the many other award recipients here.
Wow.
Just released onto the Visual Basic Developer Center and VSTO Portal, a new article Creating a Text-to-speech add-in for Microsoft Word 2007 with Visual Studio 2008 from a member of our Italian .NET community, Alessandro Del Sole.
The article shows how to use Visual Studio 2008 to create an add-in for Word 2007 that will read your documents out loud to you using the text-to-speech APIs in .NET Fx 3.0.
Lately I've been getting my hands deep into WPF with my line-of-business (LOB)/data-based application mind set. I'm taking a different approach to the technology resisting the urge to put on my amateur-designer hat and instead purely focus on data, data-binding, formatting controls, and some basic layout. (Yes before you ask, I have started producing the WPF forms over data videos!)
Today I wanted to play with dynamically creating XAML and loading it at runtime. This was really easy using XML literals and XML namespace imports. Let me show you what I mean. You can load and save XAML at runtime using the System.Windows.Markup.XamlReader and System.Windows.Markup.XamlWriter classes. Create a new WPF project and in the Window1 code-behind you can do this:
Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"> Imports <xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> Imports System.Windows.Markup Class Window1 Private Sub Window1_Loaded() Handles MyBase.Loaded Dim UI = <Label Name="Label1">This is COOL!</Label> Me.Content = XamlReader.Load(UI.CreateReader()) End Sub End Class
When we run it:
There are a lot of possibilities here. For instance, wouldn't it be nice to automatically generate all your maintenance screens in your business apps? When I say "maintenance" I mean all those simpler lookup data tables or contact tables. Even a customer contact form is usually pretty basic. Instead of handing those screens to the newbie or the junior on the team why not just generate them all at runtime based on your object model or database schema?
Admittedly this isn't something that is only unique to WPF. You could do this in Winforms as well but it was an exercise in coding the layout by hand. WPF makes this a breeze because we can define one piece of XAML and we can construct it from a single LINQ query. You can use reflection to look at your object model but if it's really simple and maps one-to-one to your database table anyway you can just create a table (or a stored proc) that contains the schema (or meta-data) of all of your maintenance tables. For instance to generate a simple UI we would probably want to obtain at minimum the following properties for each column in a table:
ColumnNameDataTypeMaxLengthIsPrimaryKey
You can either create a meta-data table in your database or a stored proc that returns the info from the information schema (check permissions for the stored proc if you go that route). I was playing with the stored proc idea because then if I make a change to my database, I don't need to update my code necessarily. So for this example I created a stored proc in the Northwind database called GetTableSchema:
CREATE PROCEDURE dbo.GetTableSchema ( @table varchar(50) ) AS SELECT c.table_name As TableName, c.column_name As ColumnName, c.data_type As DataType, c.character_maximum_length As MaxLength, COALESCE ( ( SELECT CASE cu.column_name WHEN null THEN 0 ELSE 1 END FROM information_schema.constraint_column_usage cu INNER join information_schema.table_constraints ct ON ct.constraint_name = cu.constraint_name WHERE ct.constraint_type = 'PRIMARY KEY' AND ct.table_name = c.table_name AND cu.column_name = c.column_name ),0) AS IsPrimaryKey FROM information_schema.columns c INNER JOIN information_schema.tables t ON c.table_name = t.table_name WHERE @table = t.table_name and (t.table_type = 'BASE TABLE' and not (t.table_name = 'dtproperties') and not (t.table_name = 'sysdiagrams')) ORDER BY c.table_name, c.ordinal_position
I then added a new "LINQ to SQL classes" item to my project and dragged Customer onto the design surface (for this example that's what we'll be editing) from the Northwind database I had connected to in my Server Explorer. I then expanded the "Stored Procedures" and dragged the above procedure onto the methods pane. Next I manually created an object on the design surface called TableSchema that contained the same properties as the fields I'm returning from the stored proc. Once I have that all set up I can now map the result type of the stored proc to the TableSchema class:
Okay now that we have that set up we can get back to the fun stuff. I created a simple lookup textbox and "Find" and "Save" buttons at a fixed area at the top of my WPF window. Under that I dragged a ContentControl onto the form and named it DynamicContent. We're going to generate the content here from the Customer schema and bind to the customer object that is returned from our LINQ query when we click find.
<Window x:Class="Window1" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="Window1" Name="Window1" SizeToContent="WidthAndHeight" > <Grid Name="MainGrid" > <Grid.RowDefinitions> <RowDefinition Height="10*" /> <RowDefinition Height="60*" /> </Grid.RowDefinitions> <StackPanel Name="StackPanel1" Orientation="Horizontal" Margin="3" VerticalAlignment="Top"> <Label Height="28" Name="Label1" Width="84" HorizontalContentAlignment="Right" FontWeight="Bold">ID</Label> <TextBox Height="25" Name="txtSearch" Width="120">ALFKI</TextBox> <Button Height="25" Name="btnFind" Width="75">Find</Button> <Button Height="25" Name="btnSave" Width="75">Save</Button> </StackPanel> <ContentControl Grid.Row="1" Name="DynamicContent" Margin="3" /> </Grid> </Window>
First let's generate the UI in the Load event of our form. The first thing is to add the appropriate Imports at the top of the file and then we can generate our UI. (I also have handlers here for the actual loading and saving of the customer.)
Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"> Imports <xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> Imports System.Windows.Markup Class Window1 Dim db As New NorthwindDataContext Dim CustomerData As Customer Private Sub Window1_Loaded() Handles MyBase.Loaded Dim customerSchema = db.GetTableSchema("Customers").ToList() Dim UI = <Grid xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"> <Grid.ColumnDefinitions> <ColumnDefinition Width="100*"/> <ColumnDefinition Width="200*"/> </Grid.ColumnDefinitions> <StackPanel Name="StackLabels" Margin="3"> <%= From column In customerSchema _ Where column.IsPrimaryKey = 0 _ Select <Label Height="28" Name=<%= column.ColumnName & "Label" %> HorizontalContentAlignment="Right"> <%= column.ColumnName %>:</Label> %> </StackPanel> <StackPanel Grid.Column="1" Name="StackFields" Margin="3"> <%= From column In customerSchema _ Where column.IsPrimaryKey = 0 _ Select GetUIElement(column) %> </StackPanel> </Grid> Me.DynamicContent.Content = XamlReader.Load(UI.CreateReader) End Sub Private Function GetUIElement(ByVal column As TableSchema) As XElement Select Case column.DataType Case "datetime", "int" Return <TextBox Height="28" Name=<%= "txt" & column.ColumnName %> Text=<%= "{Binding Path=" & column.ColumnName & ", ValidatesOnDataErrors=True}" %>/> Case "bit" Return <CheckBox HorizontalContentAlignment="Left" Name=<%= "chk" & column.ColumnName %> IsChecked=<%= "{Binding Path=" & column.ColumnName & ", ValidatesOnDataErrors=True}" %>> <%= column.ColumnName %> </CheckBox> Case Else Return <TextBox Height="28" Name=<%= "txt" & column.ColumnName %> MaxLength=<%= column.MaxLength %> Text=<%= "{Binding Path=" & column.ColumnName & ", ValidatesOnDataErrors=True}" %>/> End Select End Function Private Sub btnFind_Click() Handles btnFind.Click If Me.txtSearch.Text <> "" Then Me.CustomerData = (From cust In db.Customers _ Where cust.CustomerID = Me.txtSearch.Text).FirstOrDefault() Me.DataContext = Me.CustomerData Else Me.DataContext = Nothing End If End Sub Private Sub btnSave_Click() Handles btnSave.Click If Me.DataContext IsNot Nothing Then Try db.SubmitChanges() MsgBox("Saved") Catch ex As Exception MsgBox(ex.ToString) End Try End If End Sub End Class
The stored proc will work for any table contained in the database, so we could even abstract this further, and I obviously didn't get very fancy with the UI -- but I think you get the idea.
UPDATE: Read the latest post on dynamic data entry and download the code samples.
I feel like I need a T-shirt that says "I survived TechEd". It was my first time at TechEd and no one can really prepare you for how much work and play takes place at a huge conference like that. I had a BLAST. Our VB6 to .NET migration talks went well but I think by far the most fun was working the Visual Studio booths (so much so that I stayed longer there than I signed up for). I also did a .Net Rocks! show on VB XML Literals (I'll let you know when it goes live) and hung out at the MSDN booths for a bit playing with the new Social Bookmarks and Feeds. Oh yea, and lots and lots of parties (I'm pretty good at those ;-)) Lessons Learned: Next time bring more business cards. I ran out the second day!
Booth "Duty"
I say duty but manning the booth really didn't feel like a chore at all. The most fun I always have is talking to people about what they work on, then trying to understand their business challenges and hearing what technology decisions they've made. Most folks I spoke with were government employees (agriculture, energy, defense, and a couple people building applications for local police as well). Other folks were building or maintaining departmental applications in large corporations. Fun stuff.
It was also great to hear that most people I spoke with had downloaded and were starting to use Visual Studio 2008. There was lots of great feedback here especially related to IntelliSense and of course LINQ. Many people wanted me to show them how to do something in LINQ as it seemed that they were still trying to get their heads around it all. Some people I spoke with didn't realize that LINQ isn't just for accessing data in a database. LINQ is a way to query any collection, be that in-memory objects, XML, or even over in-memory DataSets.
I think my favorite moment was when I worked with a developer that was trying to search an XML document using the XML DOM and we moved the code to LINQ to XML and queried it using one LINQ statement -- thus eliminating more than a page full of code. I also showed XML IntelliSense in VB by including a schema in the project. I pointed people to the downloads section of the Visual Basic Developer Center to install the XML to Schema tool which automatically infers the schema of XML data. It'll be good to get this tool baked into SP1.
There were also a lot of good discussions at the booth on LINQ to relational data (SQL, Entities, DataSets) and which approach is best in what scenarios. I got a couple "not another data access technology" vibes but once I explained them as options on top of ADO.NET rather than replacements I think people were more at ease. I showed a few people how you can take advantage of LINQ without having to re-architect your data access layer at all. I also pointed people who know T-SQL but are trying to learn LINQ to a series of blog posts the VB Team is putting together on "Converting SQL to LINQ" that show translations of a variety of statements and scenarios.
VB6 to .NET Migration Talks
The VB6 to .NET Migration topic had a great turnout. What was a bit surprising is that even though the Interop Forms Toolkit has been out for more than a year, it was news to almost everyone. Folks planning a phased migration should really get the Interop Toolkit and the Power Packs into their arsenal.
This topic had two sessions that I did with Rob Windsor (VB MVP) and Paul Yuknewicz (colleague at Microsoft). We did one TLC in an "interactive theater" located on the trade show floors as well as one "birds-of-a-feather" (BOF) discussion. The TLC we presented in similar format to the Webcast I did with Rob that you can view here. The BOF was really more of an interactive discussion and the group had some great challenges and we addressed many architectural options for migration. The discussion also included a couple great folks from ArtinSoft who provide migration tools and consulting.
VB6 to .NET Migration resources: