Click here to Skip to main content
15,867,834 members
Articles / Desktop Programming / WPF
Tip/Trick

Streamlined [Data Model-less] Record Editing with a SQLite Data Object Window Base Class

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
11 Jul 2015CPOL6 min read 6.8K   39   4  
This SQLite Data Object Window base class automates the data updates and the data bindings between the XAML presentation and the SQLite database allowing the minimum of code needed to present and edit SQLite database records.

Introduction

Having a number of database records that can be edited by the end-user, I wanted to streamline my efforts to present the various forms and to update the database with user approved changes. The crux of the code savings is the supplanting of any coding of the Data Model or DependencyProperties by directly using the DataView object returned from a SQL query as the DataContext of the form. Though this code works within a SQLite framework, because the interchange object is a DataView, the lightweight approach used here could easily be applied to the other database engines.

Background

This code depends upon the SQLite infrastructure submitted in a previous article: http://www.codeproject.com/Articles/1002537/Full-Service-SQLite-for-WPF-including-file-handlin

Also referenced was http://www.codeproject.com/Questions/111977/How-to-extend-WPF-Page-class-in-XAML-and-code-behi .

Launching the Form

Somewhere in your application, you will have a Click event mapped to a function like this to allow your user to launch a form to edit a specified record:

C#
private void EditReleaseClick(object sender, RoutedEventArgs e)
{
  ReleaseForm rf = new ReleaseForm();
  rf.SQLite3ODP = (SQLite3DataProvider)Resources["ARDVARC"];
  rf.RecordID = (Int64)(((DataRowView)_Releases.SelectedItem).Row["releaseID"]);
  rf.Query = "SELECT * FROM Releases WHERE id=@RecordID";

  if (rf.ShowDialog() ?? false)
    this._ARDVARC_Upload_Required = true;
}

From the user's Click action:

  • Create the form
  • Set the DataProvider
  • Set the desired record id for the record to edit
  • Set the SQL statement to query for the record, with the specified record id substitution variable (must be "@RecordID")
  • Then show the form
  • If the form is not canceled, flag a dirty bool or perform other post record-edit processing

Creating the Form

After you use the Visual Studio code generator to add a Window to your WPF project, you will need to make the following changes to your generated code behind:

C#
public partial class ReleaseForm : SQLite3DOWindow
{
  public ReleaseForm() 
  {
    this.Owner = Application.Current.MainWindow;
    InitializeComponent();
  }
}
  • Change the base class from Window to the SQLite3DOWindow.
  • [optional] Setting the Owner window allows the form to be used like a modal dialog.

Next, you will need to make the following changes to the generated XAML for the form:

XML
<local:SQLite3DOWindow x:Class="YourAppNamespace.ReleaseForm"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  xmlns:local="clr-namespace:YourAppNamespace"
  Title="Release Attributes" Width="300" Height="300" >
  • Replaced the initial element in the XAML from Window to local:SQLite3DOWindow. Without this change, your project will not compile as this element name has to point the XAML processor to the base class of the form.
  • Note the XML namespace qualifier, here local:, and the added line to map this xmlns qualifier to back to your code.

Next, layout your form and bind your controls to the fields of your record. The base class will set the DataContext to a DataView object which is returned from the submitted SQL query. This greatly streamlines the data binding without having to program Data Model classes or populate DependencyProperties.

XML
<Label Grid.Row="0" Grid.Column="0" >Release ID:</Label>
<DockPanel Grid.Row="0" Grid.Column="1" >
  <Button DockPanel.Dock="Right" 
  Click="UpdateAndCloseClick">Update</Button>
  <Button DockPanel.Dock="Right" 
  Click="CancelClick">Cancel</Button>
  <TextBlock DockPanel.Dock="Left" Text="{Binding id, Mode=OneTime}" />
</DockPanel>

For example, the first row of the sample form shows the record id along with an Update button and a Cancel button. Because the DataContext has been set to the DataView of the SQL query, I can bind by simply using the column name return by the SELECT statement. I set the binding to OneTime so that the column in the DataView would never be updated with a change, but, using a TextBlock control, I do not allow the record id to be edited anyway.

As a convenience, the base class offers the following three Click handlers:

  • A Cancel Click handler, which closes the window
  • An Update Click handler, which saves the changes to the record but does not close the window (e.g. and "Apply" handler), and
  • An UpdateAndClose Click handler, which saves the changes to the record and closes the window.
XML
<Label Grid.Row="1" Grid.Column="0" >Release State:</Label>
<ComboBox Grid.Row="1" Grid.Column="1" 
SelectedValue="{Binding releaseState, Mode=TwoWay}" >
  <ComboBox.ItemsSource>
    <MultiBinding Converter="{StaticResource SQLite3QueryConverter}" Mode="OneTime">
      <MultiBinding.ConverterParameter>SELECT id, 
       name FROM ReleaseStates ORDER BY id ASC</MultiBinding.ConverterParameter>
      <Binding Source="{StaticResource ARDVARC}" />
    </MultiBinding>
  </ComboBox.ItemsSource>
  <ComboBox.DisplayMemberPath>name</ComboBox.DisplayMemberPath>
  <ComboBox.SelectedValuePath>id</ComboBox.SelectedValuePath>
</ComboBox> 

For the second row of the form, I populate a ComboBox with the names and ids of the various normalized states that the record can be set to. The tables are normalized which means that the id of the ReleaseState is stored in the Release table, not the name. The form arbitrates between the two tables by the use of the SelectedValue DependencyProperty of the ComboBox, which TwoWay binds the id value of the displayed names with the releaseState id value of the record that is being edited.

(See my previous article for an explanation of how the ComboBox is populated: http://www.codeproject.com/Articles/1002537/Full-Service-SQLite-for-WPF-including-file-handlin )

XML
<Label Grid.Row="2" Grid.Column="0" 
>Release Name:</Label>
<TextBox Grid.Row="2" Grid.Column="1" 
Text="{Binding name, Mode=TwoWay}" />

<Label Grid.Row="3" Grid.Column="0" 
>Release Date:</Label>
<TextBlock Grid.Row="3" Grid.Column="1" 
Text="{Binding date, Mode=OneTime}" />

The rest of the form is straightforward with simple databinding to the column named fields of the DataView record. Here is a picture of the presentation of the sample form:

Inside the Data Object Window Base Class

The SQlite3DOWindow base class provides three DependencyProperties:

SQLite3ODP
This is the object data provider for SQLite. It is in active use throughout the WPF application and is set here for the base class' usage.

(See http://www.codeproject.com/Articles/1002537/Full-Service-SQLite-for-WPF-including-file-handlin for more detail)

RecordID
Though its usage is optional, any display or edit of a single record can use this DependencyProperty as a convenience. It is primarily used as a substitution parameter to the Query DependencyProperty below and is expected to be used in the WHERE clause of the Query.

As a note: a Previous Record/Next Record navigation could be accommodated on a form by changing this RecordID DependencyProperty and then resetting the Query DependencyProperty to itself (e.g. Query = Query), which would return a new dataset in a new DataView and reassign the form's DataContext.

Query
This is the workhorse property of the base class. Whenever the Query property is set or reset, even if reset with the same value, the DataContext of the form is refreshed with a new DateView returned from an issue or re-issue of the Query SQL to the SQL Object Data Provider.

The remainder of the base class' methods support the end of form functionality.

public int Update()
This method will construct and issue a SQL UPDATE statement from the current fields and field values in the DateView.

The method returns the number of records affected. e.g. 1 = success, 0 = failure.

This method makes the assumption that the first column of the DataView is the record id column. Regardless, the first column and value are used in the WHERE clause of the UPDATE statement.

protected void CancelClick(...)
This function closes the form without saving any of the changes to the DataView to the database.

DialogResult is set to false to signal to the calling process the cancel event.

protected void UpdateClick(...)
This function does not close the form, but does save any changes to the DataView to the database. This facilitates an "Apply" button on the form.

DialogResult is set to false if no records were updated, and true if at least one record was updated.

protected void UpdateAndCloseClick(...)
This function closes the form after saving any changes to the DataView to the database.

DialogResult is set to false if no records were updated, and true if at least one record was updated.

Conclusion

Instead of a heavy MVVM approach, this trick of making the base class of the WPF forms provide a DataView as their DataContext, allows a streamlined data binding to database records without a hand coded or heavily generated Data Model or DependencyProperties. This lightweight approach (call it MVVM), reduces the code footprint of the project and the extra complexity of a full Data Model architecture. This, of course, side steps any business logic, that a Data Model layer would provide, or at the very least relegates such business logic to the Presentation classes and may not be appropriate for a large multi-programmer project. But, for the single programmer project or for rapid prototyping or applications where the business logic is heavily encoded by database triggers, views, and stored procedures, this approach provides a much quicker implementation approach.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --