Mar
23
Written by:
Robert Houben, CTO
3/23/2010 12:00 AM
I'm going to assume that the reader has, or is familiar with, our ADO.NET provider for MultiValue systems and has both the provider and the Command Builder and access to a MultiValue system with the Direct DAS configured.
I'll also assume you've added the provider into the Global Assembly Cache (GAC) and installed it as a provider in the machine.config file.
Preparation
First off, you need to run the FusionWare Direct ADO Command Builder:
When you run it the first time, both text boxes will be blank. Click the “…” button beside the Connection String text box. Set up your data source and test the connection. Make sure you set the Diagnostic option as shown below:
Once you have this working, Click Save and save it with a name so you can retrieve it at other times, if needed. Click OK.
Copy the connection string to the clipboard and save it somewhere, as you will be using it later.
We recommend that you use the following command:
CUST 'SELECT CUST NE "BAD.3"'
The reason for this is that we have an intentionally bad record in there so we can demonstrate how our provider deals with bad data.
Save this command text somewhere where you can use it later.
Creating the Application
Open Visual Studio and create a new project. Under “Visual C#” we selected “WPF Application”. For our example we stuck with the default WpfApplication2.
Right click on the Properties folder and select open.
In the center pane click on the “Settings” tab on the left.
Create two Application scoped settings of type string, named DataProvider (with a value of “FusionWare.DirectADO”) and ConnectionString. The value for ConnectionString should be the one you saved from the Command Builder step. Note that the DataProvider name must match provider invariant name that you used for our provider in the machine.config file.
Data Access Layer
Next, in keeping with recommended best practices, we will create a data access layer (DAL) for processing commands against our data source. This provides a layer of abstraction between the application user interface and the data.
In the Solution Explorer, right click on the project and select Add, New, and select “Class”. Name it “mvCust” and click the “Add” button.
Make it look like this:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Configuration;
namespace WpfApplication2
{
[DataObjectAttribute]
public class mvCust
{
private static DbProviderFactory m_factory =
DbProviderFactories.GetFactory(Properties.Settings.Default.DataProvider);
public mvCust()
{
}
private static DbConnection getConnection()
{
DbConnection connection = m_factory.CreateConnection();
if (connection != null)
{
// Get this from the app.config/web.config file...
connection.ConnectionString = Properties.Settings.Default.ConnectionString;
}
return connection;
}
[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable getCustData()
{
DataSet ds = null;
List<mvCust> list = new List<mvCust>();
DbConnection connection = null;
DbCommand command = null;
DbDataAdapter adapter = null;
DataTable dt = null;
try
{
using (connection = getConnection())
{
connection.Open();
using (command = m_factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = "CUST 'SSELECT CUST NE \"BAD.3\"'";
using (adapter = m_factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
ds = new DataSet("CUST");
adapter.Fill(ds, "CUST");
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables["CUST"];
}
}
}
}
}
catch (Exception ex)
{
System.Console.WriteLine(ex.ToString());
}
return dt;
}
}
}
Note that we set command.CommentText to the command that we recommended earlier. We also use the name “CUST” for the data set and base table name that we are accessing. We also reference the Settings values that we have setup.
Creating the Data Source
Click on your XAML designer view and then, in the left pane, click on the Data Sources tab so that the empty Data Sources pane shows:
Click the “Add New Data Source” link in this pane.
Choose “Object” for Data Source Type, and click Next.
Expand your project objects so that you see mvCust and select it as shown:
Click Finish.
Wire Your Data Source to the Application
Drag the mvCust Data Source to your XAML design view canvas. Center it and adjust the layout to your wishes.
Right Click on your design surface and select “View Code”. Scroll down “MainWindow.xaml.cs” until you see this text:
private void Window_Loaded(object sender, RoutedEventArgs e)
{
System.Windows.Data.CollectionViewSource mvCustViewSource =
((System.Windows.Data.CollectionViewSource)(this.FindResource("mvCustViewSource")));
// Load data by setting the CollectionViewSource.Source property:
// mvCustViewSource.Source = [generic data source]
}
Change the commented line so you have the following:
private void Window_Loaded(object sender, RoutedEventArgs e)
{
System.Windows.Data.CollectionViewSource mvCustViewSource =
((System.Windows.Data.CollectionViewSource)(this.FindResource("mvCustViewSource")));
// Load data by setting the CollectionViewSource.Source property:
mvCustViewSource.Source = mvCust.getCustData();
}
Controlling Which Columns Are Visible (simple method)
There are two ways to approach how to control what is visible. First well take the approach of having the data grid automatically generate display information for all columns, and using an event handler to ignore some, and change the headings on others. Later we'll also show how you can explicitly control columns. While we recommend the latter, the former will get you a very quick prototype form, and demos very nicely.
Now we'll add the following code just below the Window_Loaded method
public event EventHandler<DataGridAutoGeneratingColumnEventArgs> AutoGeneratingColumn;
//Access and update columns during autogeneration
private void mvBaseCust_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
{
string headername = e.Column.Header.ToString();
//Cancel the column you don't want to generate
if (headername == "CUSTAccountTypes" ||
headername == "CUSTVisits" ||
headername == "AccountTypes" ||
headername == "Visits")
{
e.Cancel = true;
}
//update column details when generating
if (headername == "CustName")
{
e.Column.Header = "Customer Name";
}
else if (headername == "Birthtime")
{
e.Column.Header = "Time of Birth";
}
else if (headername == "CUSTID")
{
// The user can't change the customer id
e.Column.IsReadOnly = true;
e.Column.Header = "Customer Id";
}
}
This allows us to skip certain columns that are related to multivalues and also allows us to change the displayed column name so it’s more user friendly.
In order to finish hooking this up, we need to edit the XAML, so we need to go back to the XAML design view and look at the code window:
Add the AutoGenerateColumn and AutoGeneratingColumn attributes so that the DataGrid element looks like this when you are done:
<DataGrid
AutoGenerateColumns="True"
AutoGeneratingColumn="mvCust_AutoGeneratingColumn"
EnableRowVirtualization="True"
Height="200"
ItemsSource="{Binding Source={StaticResource mvCustViewSource}}"
Name="mvCustDataGrid"
RowDetailsVisibilityMode="VisibleWhenSelected"
Width="400" DataContext="{Binding}">
DataGrid>
You should now be able to build and run this application:
Controlling Which Columns are Visible (explicit method)
Instead of using the AutoGenerateColumns="True" setting and catching the autogenerating event handler, you can manually define the columns to display. To do this, change the XAML for the DataGrid to the following:
<DataGrid
AutoGenerateColumns="False"
EnableRowVirtualization="True"
Height="200"
ItemsSource="{Binding Source={StaticResource mvCustViewSource}}"
Name="mvCustDataGrid"
RowDetailsVisibilityMode="VisibleWhenSelected"
Width="400"
DataContext="{Binding}">
<DataGrid.Columns>
<DataGridTextColumn
Binding="{Binding CUSTID}"
IsReadOnly="True"
Header="Customer Id" />
<DataGridTextColumn
Binding="{Binding CustName}"
Header="Customer Name" />
<DataGridTextColumn
Binding="{Binding Age}"
Header="Age" />
<DataGridTextColumn
Binding="{Binding Birthdate, StringFormat=d}"
Header="Date of Birth" />
<DataGridTextColumn
Binding="{Binding Birthtime}"
Header="Time of Birth" />
<DataGridTextColumn
Binding="{Binding NetWorth}"
Header="Net Worth" />
DataGrid.Columns>
DataGrid>
Note that we have added a “DataGrid.Columns” element and one “DataGridTextColumn” element for each column to display. Note also that we can control the header, and in the case of the Birthdate column, we can tell it to display the date portion only. This explicit approach is generally recommended over the AutoGenerating columns approach.
Add Controls to XAML
In the XAML view, surround the DataGrid element with StackPanel start and end tags. Then insert a Text element and a Button control. When you are done, you should see this code in your XAML view:
<StackPanel>
<TextBox
Name="tbStartsWith"
Text=""
Width="200"
HorizontalAlignment="Left"
VerticalAlignment="Top" />
<Button
Name="btnStartsWith"
Tag="Filter Starting With"
Height="30"
Width="120"
HorizontalAlignment="Left"
VerticalAlignment="Top"
Content="Filter Starting With"
Click="btnStartsWith_Click" />
<DataGrid
AutoGenerateColumns="False"
…
DataGrid>
StackPanel>
Note that you can put this all in, omitting the Click= part, then double click on the button to have it add the Click= attribute and create the empty handler method.
Add the following handler code into MainWindow.xaml.cs:
private void btnStartsWith_Click(object sender, RoutedEventArgs e)
{
System.Windows.Data.CollectionViewSource mvCustViewSource =
((System.Windows.Data.CollectionViewSource)
(this.FindResource("mvCustViewSource"))
);
mvCustViewSource.Source = mvCust.getCustWithNameStarting(this.tbStartsWith.Text);
}
Testing Your App
Build and run your application. At first, it will show you all records that meet the original select. But enter "JA" into the text box and click the button and you will see it retrieve just Jane Doe's record.
Retrieving a Single Record
Our next step will be to provide you with an accessor method that retrieves just a single record.
Accessor Method for Retrieving by Item-Id
Add this accessor method into the mvCust class:
[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable getCustData()
{
DataSet ds = null;
List<mvCust> list = new List<mvCust>();
DbConnection connection = null;
DbCommand command = null;
DbDataAdapter adapter = null;
DataTable dt = null;
try
{
using (connection = getConnection())
{
connection.Open();
using (command = m_factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = "CUST 'SSELECT CUST NE \"BAD.3\"'";
using (adapter = m_factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
ds = new DataSet("CUST");
adapter.Fill(ds, "CUST");
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables["CUST"];
}
}
}
}
}
catch (Exception ex)
{
System.Console.WriteLine(ex.ToString());
}
return dt;
}
XAML Changes
Change the text box name and button name and content to reflect that you are selecting a customer by their id instead of by the starting characters of their name field, and change the handler method to this:
mvCustViewSource.Source = mvCust.getSingleCustomer(this.tbCustId.Text);
Accessing MultiValues and SubValues
By putting a breakpoint in our mvCust class and examining the DataSet returned by our call to the FusionWare Direct DAS, you can see that the following tables are exposed:
The CUSTAccountTypes and CUSTVisits tables represent multivalued sets, while CUSTAccountTypesAccounts and CUSTVisitsVisitNoteLines represent subvalued sets.
By changing the name of the referenced table in the line that has the breakpoint, you can change which table you return.
What you would probably do is create multiple accessor methods in addition to getCustData() and also create multiple AutoGeneratingColumn event handlers for each table you wished to return.
Updating Changes
By keeping the DataSet and DbDataAdapter objects around, you can use the Update method to save changes to the data. You would have to set the DbDataAdapter object's UpdateCommand, InsertCommand and DeleteCommand properties to command objects as per our WinForms tutorials to make this happen. See the FusionWare Direct ADO.NET WinForms tutorial for details of how to do this.
Summing it Up
FusionWare Direct ADO.NET allows you to build enterprise-quality .NET applications in Visual Studio 2010, using Microsoft's recommended best practices and fully supporting all bindable controls.
Part 2 - Controlling Which Records to Retreive
In part two of this multi-part blog, we'll customize the app so that we can control which records we wish to retrieve.
FusionWare Direct Product Family
The FusionWare Direct product family includes client providers that enable access to your MultiValue data from Java, .NET and Legacy COM/COM+ environments.
Client Platforms Supported
Client Platforms supported for .NET and COM clients include:
- Windows 2000, 2003 and 2008, 32 bit and 64 bit
- Windows XP, Vista and Windows 7, 32 bit and 64 bit
Client Platforms Supported for Java client include:
- Windows 2000, 2003 and 2008, 32 bit and 64 bit
- Windows XP, Vista and Windows 7, 32 bit and 64 bit
- IBM Platforms:
- IBM i (AS/400)
- System z (mainframe)
- System p (AIX)
- Certified "Ready for IBM Systems with Linux"
- Linux
- Unix
- and more...
MultiValue Platforms Supported
- Rocket (formerly IBM)
- U2 (Universe and Unidata) including Universe back to version 5
- PI Open
- Raining Data
- Northgate
- Temenos Group
- Ladybridge Systems (coming soon)
- Others (older or unsupported MultiValue Systems
- UltPlus
- Power95
- and more...
Copyright ©2010 FusionWare Integration Corporation