Friday, March 27, 2009

Top 10 Techniques for Microsoft .NET Framework Database Developers

 

By: Cary Jensen

Abstract: Whether you are new to ADO.NET development, or have been doing it for some time, here are 10 essential techniques that belong in every Microsoft .NET Framework database developer's repertoire.�

Top 10 Techniques for Microsoft .NET 
Framework Database Developers

by Cary Jensen
Jensen Data Systems, Inc.

ADO.NET, the data access layer of the Microsoft .NET framework, provides developers with a powerful and rich set of classes and types for building database applications. Unfortunately, for most developers, ADO.NET also requires a radically different approach to database development. For example, a Delphi developer comfortable with the TDataSet API will find few familiar classes and concepts, making the transition to .NET development that much more challenging.

This paper is designed to provide you with a collection of useful and interesting techniques designed to make you more productive with ADO.NET. If you are already an ADO.NET developer, you should find several examples here that will inspire you further, giving you additional ways to leverage this powerful environment. If you are new to .NET database development, this paper should provide you with a insight into this remarkable and extensible data access environment.

This paper assumes that you are already familiar with ADO.NET in general, including its standard classes, interfaces, and philosophy. If you are new to ADO.NET, at a minimum you should first read my paper Building Database Applications with ADO.NET, article number 2186, which is also located on this conference CD-ROM.

Before continuing it should be noted that many of the techniques described in this paper can be applied to either ASP.NET or traditional client applications. ASP.NET, however, because of its generally stateless nature, requires fewer "tricks of the trade," if you will. As a result, most of the examples given here are described from the standpoint of client applications, which usually require data caching, state maintenance, and programmatic navigation. 

Note: SqlConnection connection strings referred to in the projects named in this paper must be changed to refer to the server on which you are running SQL Server.

1. Adding Expression DataColumns to a DataTable

A DataTable is an in-memory structure that holds data obtained from an XML file, an IDbDataAdapter, or programmatic input. Normally, the structure of a DataTable is based on the data obtained from an XML file or database.

DataTable's consist of DataRow objects, which hold the individual records of the DataTable, and DataColumn objects, which constitute the DataTable's metadata. Each DataRow holds one value (or a null value) for each DataColumn.

The following code segment from the CreateDataTable project demonstrates a DataTable being created on-the-fly using DataColumns, after which two DataRows are added.

var   DataTable1: DataTable;   DataRow1: DataRow;   DataColumn1: DataColumn; begin   DataTable1 := DataTable.Create;   DataColumn1 := DataColumn.Create('CustNo',     System.Type.GetType('System.Int32'));    DataColumn1.AllowDBNull := False;   DataColumn1.Unique := True;   DataTable1.Columns.Add(DataColumn1);    DataTable1.Columns.Add('FirstName',     System.Type.GetType('System.String'));   DataTable1.Columns.Add('LastName',     System.Type.GetType('System.String'));   DataTable1.Columns['LastName'].DefaultValue := 'not assigned';    //Add two records to the DataTable   DataRow1 := DataTable1.NewRow();   //Referencing columns using column name   DataRow1['CustNo'] := 100;   DataRow1['FirstName'] := 'Frank';   DataRow1['LastName'] := 'Borland';   DataTable1.Rows.Add(DataRow1);    DataRow1 := DataTable1.NewRow();   //Referencing columns using ordinal position   DataRow1[0] := 101;   DataRow1[1] := 'Bill';   DataRow1[2] := 'Gates';   DataTable1.Rows.Add(DataRow1);

Though this is not obvious, it is a trivial matter to add additional DataColumn instances to a DataTable even after the DataTable has been loaded with data. The following code segment shows a DataColumn that contains a calculation being added to a DataTable that was loaded through a IDbConnection. This example is found in the DetailForm project.

 SqlConnection1 := SqlConnection.Create('Persist Security Info=False;'+     'Integrated Security=SSPI;database=northwind;' +     'server=training5;Connect Timeout=30');   SqlDataAdapter1 := SqlDataAdapter.Create('Select * from employees',     SqlConnection1);   SqlCommandBuilder1 := SqlCommandBuilder.Create(SqlDataAdapter1);   DataTable1 := DataTable.Create;   SqlDataAdapter1.Fill(DataTable1);   DataTable1.Columns.Add('FullName',     System.Type.GetType('System.String'),     'FirstName + '' '' + LastName');

2. Working with Intermediate DataColumn Expressions

The Expression property of a DataColumn can include aggregate operators such as Sum, Min, and StdDev. Unlike expressions that do not include summary operators, an aggregate function can only use a single field of the DataTable as its parameter.

At first this limitation may suggest that you cannot perform aggregation on calculations that include two or more fields, or one or more fields and literal values.

Fortunately, there is a way to get around this limitation. What you do is to create an intermediate expression, one that includes the values from one or more fields and literals, combined using arithmetic operators. You then add a new expression DataColumn that performs the aggregation on that new column. If you do not want the user to see the intermediate value, set that DataColumn's Visible property to False.

The following code from the CalculationsAndAggregates project demonstrates the creation of a DataColumn named OrderTotal, which calculates the sum of a intermediate DataColumn named ProductTotal

  //Calculated field example. No aggregation   //This calculated field calculates the value of   //each purchase for each detail record   DetailsTable.Columns.Add('ProductTotal',     System.Type.GetType('System.Double'), '(UnitPrice * Quantity) - ((UnitPrice * Quantity) * Discount)');    //This expression aggregates the preceding calculation   //over the detail records by each group (OrderID)   OrdersTable.Columns.Add('OrderTotal',     System.Type.GetType('System.Double'),     'Sum(Child.ProductTotal)' ); (**)

3. Programmatically Navigating DataSets and DataTables

Unlike TDataSets in Delphi, DataSets in .NET do not have the concept of a current record. Consequently, navigating across the records of a DataSet in .NET looks radically different than the same operation in TDataSet applications.

DataSet navigation in .NET is more similar to navigating a three-dimensional array than anything else. The following code segment from the ConsoleNavigation project demonstrates how to navigate a DataTable named 'customers' in a DataSet named DataSet1.

uses   SysUtils,   System.Data,   System.Data.SqlClient;  var   SqlConnection1: SqlConnection;   SqlDataAdapter1: SqlDataAdapter;   DataSet1: DataSet;   DataRow1: DataRow;   i: Integer;  begin   SqlConnection1 := SqlConnection.Create('Persist Security Info=False;' +     'Integrated Security=SSPI;database=northwind;' +     'server=training5;Connect Timeout=30');   SqlConnection1.Open();   SqlDataAdapter1 := SqlDataAdapter.Create('select * from customers',     SqlConnection1);   DataSet1 := DataSet.Create;   SqlDataAdapter1.Fill(DataSet1, 'Customers');   for i := 0 to Pred(DataSet1.Tables['Customers'].Rows.Count) do   begin     Writeln(DataSet1.Tables['Customers'].Rows[i][0].ToString,      #9,      DataSet1.Tables['Customers'].Rows[i]['CompanyName'].ToString);     //The following lines do the same as the preceding     //DataRow1 := DataSet1.Tables['Customers'].Rows[i];     //Writeln(DataRow1[0].ToString, #9,     //DataRow1['CompanyName'].ToString);   end;   SqlConnection1.Close;   ReadLn; end.

4. Creating Calculated Fields Programmatically

In a preceding example you learned how to create a DataColumn whose Expression property defined the calculation or aggregate operation that that field will display. There is another, albeit more complicated way to produce a calculated field: you can define the calculation programmatically.

There are three steps to creating a calculated field programmatically. These are:

  • Create a DataColumn to hold the calculation
  • Iterate through the DataTable, assigning the value of the calculation programmatically
  • If the user can change data in the table, and those changes can affect the calculation, write an ColumnChanged event handler, and update the calculated field from within that event handler.

The following code demonstrates how to create the DataColumn and set its initial value.

//create new DataColumn DataColumn1 := DataSet1.Tables[0].Columns.Add( 'Invoice overdue date',System.Type.GetType('System.DateTime')); //initialize data in new DataColumn for i := 0 to Pred(DataSet1.Tables[0].Rows.Count) do   DataSet1.Tables[0].Rows[i]['Invoice overdue date'] :=     System.DateTime(DataSet1.Tables[0].    Rows[i]['Invoice due date']).AddDays(30); //make new DataColumn readonly DataColumn1.ReadOnly := True;

The following code demonstrates the ColumnChanged event handler that is used to update the calculated field when the underlying data changes.

procedure TWinForm.Table_ColumnChanged(Sender: System.Object;    e: System.Data.DataColumnChangeEventArgs); begin if e.Column.ColumnName = 'Invoice Due Date' then   begin      //make DataColumn writeable        DataSet1.Tables[0].Columns[   Pred(DataSet1.Tables[0].Columns.Count)].ReadOnly := False;      e.Row['invoice overdue date'] :=         System.DateTime(e.Row['invoice due date']).AddDays(30);   //make DataColumn readonly   DataSet1.Tables[0].Columns[     Pred(DataSet1.Tables[0].Columns.Count)].ReadOnly := True;   end; end;

5. Understanding BindingManagers

All controls, that is, classes that descend from Control in the .NET FCL, are data aware, and can be bound to a data source. A BindingManagerBase instance is created anytime a control is bound to a new data source. 

Controls that are bound to the same data source and data member are bound to the same BindingManagerBase instance. BindingManagerBase is an abstract class that two concrete classes descend from. These concrete classes are CurrencyManager and PropertyManager. If the data source implements IList, IListSource, or IBindingList, its BindingManagerBase instance is a CurrencyManager, otherwise it is a PropertyManager instance.

With regards to DataSets and DataTables, the BindingManagerBase instance is always a CurrencyManager. When working with data-bound controls, you use their current manager to determine on which record the user is viewing, as well as to navigate the controls bound to the CurrencyManager to new records.

The following is the declaration of the CurrencyManager found in the DataView project:

  public     //...     CM: CurrencyManager;

The next lines show the creation of the DataView, the binding of several controls to the same binding context, and then the casting of the binding context as a CurrencyManager:

  Connection1 := SqlConnection.Create('Persist Security Info=False;' + 			  	'Integrated Security=SSPI;database=northwind;' + 			  	'server=training5;Connect Timeout=30');   Connection1.Open();   //Sql statements are executed by IDbCommand objects   Command1 := SqlCommand.Create('SELECT * FROM customers', Connection1);   //DataAdapters are used to populate DataTables and resolve data   DataAdapter1 := SqlDataAdapter.Create(Command1);   //CommandBuilders create IDbCommand objects for a DataAdapters's   //DeleteCommand, InsertCommand, and UpdateCommand properties based   //on the IDbDataAdapter.SelectCommand IDbCommand property   CommandBuilder1 := SqlCommandBuilder.Create(DataAdapter1);   //DataSets manage DataTables and DataRelations   DataSet1 := DataSet.Create;   //This statement creates a DataTable named customers in the dataset   DataAdapter1.Fill(DataSet1, 'customers');   //A view is a customizable lens for a DataTable   DataTable1 := DataSet1.Tables['customers'];   //Point a reference to the table's Default DataView   DataView1 := DataSet1.Tables[0].DefaultView;   //Bind the DataGrid   DataGrid1.SetDataBinding(DataView1, '');   //Bind the TextBoxes   TextBox1.DataBindings.Add('Text',DataView1, 'CustomerID');   TextBox2.DataBindings.Add('Text',DataView1, 'CompanyName');   //Bind the ListBox   ListBox1.DataSource := DataView1;   ListBox1.DisplayMember := 'CustomerID';   //Get the CurrencyManager for the data source   CM := CurrencyManager(DataGrid1.BindingContext[DataView1]);

The following are the Click event handlers for the navigation buttons labeled First, Next, Prior, and Last: 

procedure TWinForm.FirstButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = 0 then     MessageBox.Show(Self, 'Already on first record')   else     CM.Position := 0; end;  procedure TWinForm.NextButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = Pred(CM.Count) then     MessageBox.Show(Self, 'Cannot move beyond last record')   else     CM.Position := CM.Position + 1; end;  procedure TWinForm.PriorButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = 0 then     MessageBox.Show(Self, 'Cannot move prior to the first record')   else     CM.Position := CM.Position - 1; end;  procedure TWinForm.LastButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = Pred(CM.Count) then     MessageBox.Show(Self, 'Already on last record')   else     CM.Position := Pred(CM.Count); end;

CurrencyManagers and DataRelations are slightly more involved. When two DataTables in a DataSet are related using a DataRelation instance, you must specify the name of the DataRelation in the DataMember part of the BindingContext when you obtain your CurrencyManager for a child relationship.

The following figure of the DataRelationExample project shows a form that includes a DataRelation that defines the master-detail relationship that is shown in the two DataGrids on this form.

The following is the code that creates the two CurrencyManagers that are used with the Next Master Record and Next Detail Record buttons.

  Connection := SqlConnection.Create('Persist Security Info=False;' + 			  	'Integrated Security=SSPI;database=northwind;' + 			  	'server=training5;Connect Timeout=30');   Connection.Open();   DataAdapter1 := SqlDataAdapter.Create('SELECT * FROM orders', Connection);   DataSet1 := DataSet.Create;   CommandBuilder1 := SqlCommandBuilder.Create(DataAdapter1);   DataAdapter1.Fill(DataSet1, 'Orders');   DataAdapter2 := SqlDataAdapter.Create('SELECT * FROM [order details]', Connection);   CommandBuilder2 := SqlCommandBuilder.Create(DataAdapter2);   DataAdapter2.Fill(DataSet1, 'Details');    DataSet1.Relations.Add('DetailsByOrder',     DataSet1.Tables['Orders'].Columns['OrderID'],     DataSet1.Tables['Details'].Columns['OrderID']);   DataGrid1.SetDataBinding(DataSet1.Tables[0].DefaultView, '');   DataGrid2.SetDataBinding(DataSet1.Tables[0].DefaultView, 'DetailsByOrder');   //This works also, but binds to the DataTable instead of a DataView   //DataGrid1.SetDataBinding(DataSet1, 'Orders');   //DataGrid2.SetDataBinding(DataSet1, 'Orders.DetailsByOrder');   CM := CurrencyManager(DataGrid1.BindingContext[DataSet1.Tables[0].DefaultView]);   CM2 := CurrencyManager(DataGrid2.BindingContext[DataSet1.Tables[0].DefaultView,     'DetailsByOrder']);

6. How to Create a Data Module

A data module is a container for data access classes that you can manage and configure at design time. Data modules typically permit you to share database connections, commands, DataSets, CurrencyManagers, and the like, between the various forms of your application.

You usually do not need a data module in an ASP.NET application. ASP.NET application are stateless, and data modules are all about state. The example data module demonstrated in this section is not intended for ASP.NET

If you are familiar with data modules, you have already noticed that Delphi 9 does not include anything that resembles one. Neither does Visual Studio, for that matter.

The good news is that it is possible to create something that resembles a data module in .NET. The bad news, at least in this version of the .NET framework, is these pseudo data modules do not support all of the features that you might be accustom to in Win32 Delphi, but nonetheless, they are useful.

You create a data module using a form. The reason that you use a form is that a form can support a binding context, and it is through this binding context that you can create and use a CurrencyManager, which is essential when you need to have two or more forms synchronize on a common current record.

You create the .NET equivalent of a data module using the following steps:

  • Create a form that will serve as the data module (from this point on I'll call this form the data module). The data module may be one of the forms that you display to your users, or it may be one that you create in memory but never show.
  • Add the ADO.NET components that you want to share to the public section of the data module's class declaration.
  • Add code to the data module's Create event to create the instances of, and configure, the ADO.NET classes that you added to the form's public section. Alternatively, call a method that creates and configures your ADO.NET components from the form's Create method.
  • Add one Control descendant to the data module for each data source that you want to use in other forms. Bind each control to the data source you want to expose.
  • Declare a variable of the data module class type within scope of each form that must use a binding context (read that data source) from the data module. This variable may be declared in the units associated with each form that uses the data module, or it may be declared in the interface section of the data module unit itself.
  • For each form that uses the data module, assign its BindingContext property to the data module's BindingContext property.
  • For each Control descendant on each form, set the binding context (or DataSource and DataMember properties) to the appropriate data source on the data module.

These steps sound pretty complicated, but they really are not that involved. The general idea is that your data module should have data sources (and data members) that other forms can bind to and use.

You can find a data module example in the DetailForm project. The following figure shows two forms of this project sharing a common DataBinding and CurrencyManager.

 

Here is the public section of the data module's declaration:

  public     SqlConnection1: SqlConnection;     SqlDataAdapter1: SqlDataAdapter;     SqlCommandBuilder1: SqlCommandBuilder;     DataTable1: DataTable;     constructor Create;     procedure InitializeDataComponent;   end; 

Here is the variable used to reference the data module:

var   MainForm: TMain;

Here is the binding of a form to the data module's BindingContext:

constructor DetailForm.Create; begin   inherited Create;   InitializeComponent;   Self.BindingContext := MainForm.BindingContext;   TextBox1.DataBindings.Add('Text', MainForm.DataTable1.DefaultView, 'EmployeeID');   TextBox2.DataBindings.Add('Text', MainForm.DataTable1.DefaultView, 'LastName');   TextBox3.DataBindings.Add('Text', MainForm.DataTable1.DefaultView, 'FirstName');   TextBox4.DataBindings.Add('Text', MainForm.DataTable1.DefaultView, 'Title');   Self.DataBindings.Add('Text', MainForm.DataTable1.DefaultView, 'FullName'); end;

 7. Using DataViews

If you want to create a customized view of the data in a DataTable, you use a DataView. Among other things, a DataView permits you to define a sort order and a filter expression. In short, a DataView permits you to create multiple simultaneous unique references to a single DataTable.

DataViews are also essential if you are going to display data to a user for editing. In short, unless you are displaying readonly data, you should never show data to the user without a DataView.

DataViews, like DataSets and DataTables, can be bound to a data-aware control via the control's CurrencyManager. 

There are major four properties that you can set for a DataView. These are the DataTable whose records the DataView should refer to, a string containing the RowFilter expression that filters the view, a string holding the row or the comma separated rows to sort on, and the types of rows of the DataTable to show. This last parameter is of a type DataViewRowState. 

If you do not use the DataView constructor that passing all four of these parameters, you must at least set DataTable before binding a view to a data-aware control. If you subsequently change the Sort, RowFilter, or RowStateFilter properties of the DataView at runtime, these changes are immediately reflected in the DataView.

The use of a DataView is demonstrated in the DataView project. In addition to demonstrating a DataView, it includes code to apply updates to the underlying database after one or more records have been changed, using common binding contexts to synchronize multiple visual Control to the data source, as well as using a CurrencyManager to manipulate records in a DataTable. The running main form of this application is shown in the following figure.

Notice the filter TextBox and the Sort By ListBox controls at the top of this form. These were initialized with the RowFilter and Sort properties of the DataView. Furthermore, the event handlers attached to the Button Click and ListBox SelectedIndexChanged events permit you to set the RowFilter and Sort properties at runtime.

The radio buttons in the DataViewRowState group permit you to view the various states of the DataView. Once you have changed one or more records in a DataTable, the DataView DataViewRowState property permits you to customize which records, and in which state, appear in controls that are bound to the view.

The following are the event handlers associated with the navigation and editing buttons on the preceding form:

procedure TWinForm.FirstButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = 0 then     MessageBox.Show(Self, 'Already on first record')   else     CM.Position := 0; end;  procedure TWinForm.NextButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = Pred(CM.Count) then     MessageBox.Show(Self, 'Cannot move beyond last record')   else     CM.Position := CM.Position + 1; end;  procedure TWinForm.PriorButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = 0 then     MessageBox.Show(Self, 'Cannot move prior to the first record')   else     CM.Position := CM.Position - 1; end;  procedure TWinForm.LastButton_Click(sender: System.Object; e: System.EventArgs); begin   if CM.Position = Pred(CM.Count) then     MessageBox.Show(Self, 'Already on last record')   else     CM.Position := Pred(CM.Count); end;  procedure TWinForm.SortListBox_SelectedIndexChanged(sender: System.Object; e: System.EventArgs); begin   DataView1.Sort := SortListBox.Text; end;  procedure TWinForm.RefreshButton_Click(sender: System.Object; e: System.EventArgs); begin 	DataSet1.Clear(); 	DataAdapter1.Fill(DataSet1, 'customers');   DataGrid1.SetDataBinding(DataView1, ''); end;  procedure TWinForm.UpdateButton_Click(sender: System.Object; e: System.EventArgs); //var //  DataTable1: DataTable; begin   DataAdapter1.Update(DataSet1.Tables[0]);   //or   //DataAdapter1.Update(DataSet1.Tables['customer']);  end;  procedure TWinForm.CancelButton_Click(sender: System.Object; e: System.EventArgs); begin   DataSet1.Tables[0].RejectChanges;  end;  procedure TWinForm.AddButton_Click(sender: System.Object; e: System.EventArgs); begin 	DataView1.AddNew();   CM.Position := Pred(CM.Count); 	//The following is wrong   //DataRow1 := DataSet1.Tables[0].NewRow(); 	//DataSet1.Tables[0].Rows.Add(DataRow1); end;  procedure TWinForm.DeleteButton_Click(sender: System.Object; e: System.EventArgs); begin   //Mark the row for deletion. Sets row's RowState to DataRowState.Deleted   DataView1.Delete(CM.Position); 	//The following is wrong 	//DataSet1.Tables[0].Rows[DataGrid1.CurrentRowIndex].Delete();   //The following _does_ work, however, because the   //CurrencyManager.Position is the basis for the   //DataGrid.CurrentRowIndex, since the DataGrid's   //currency manager is the DataView (which is what   //CM is based upon. 	//DataView1.Delete(DataGrid1.CurrentRowIndex); end;

8. Using IDbParameters to Avoid the SQL Script Hack

A common technique for providing users with a flexible means of selecting data is called query by form. In query by form, a user is provided with a form into which they can enter data that is used to construct an underlying SQL statement. 

In the past, developers have had the option to use the data entered by the user by binding it to parameters of a parameterized query, or by simply concatenated the entered data to a string that constitutes the SQL statement. 

In recent years, most of the major SQL databases have introduced a feature referred to a SQL script. A SQL script is a series of two or more SQL statements separated by a semicolon. While SQL scripts permit you to send two or more queries to your database in a single statement, they introduce a potential security risk that you must be aware of. Specifically, if you construct a query at runtime from data input by a user by concatenating the users input into a SQL statement, and the user has entered a semicolon into their form, your database will interpret the semicolon as a SQL statement separator. 

Initially, this might not sound like a security threat. After all, wouldn't the presence of the semicolon surely lead to a syntax error, causing the database server to return an error? 

The answer is "It depends." A user aware of the use of semicolons to separate SQL statements, and knowledge of SQL, could exploit this feature to undermine your database. 

Here's an example. Imagine that your client application includes a query that is constructed at runtime based on the user's entry of a Customer number. The following is an example of how this query could be created from a user's input:

var    s: StringBuilder;  begin    s := StringBuilder.Create('SELECT * FROM CUSTOMERS ' +     'WHERE CustomerNo = ');    s.Append(TextBox1.Text);    SqlCommand1 := SqlCommand.Create(s.ToString, SqlConnection1); 

Here, the value entered into the TextBox named TextBox1 is concatenated to the query being assigned to the SqlCommand. Consider what would happen if the user enters the following data into the TextBox:

1001; DROP TABLE CUSTOMERS; DROP TABLE ORDERS

The resulting query would actually be a SQL script, and would look like the following: 

SELECT * FROM CUSTOMERS WHERE CustomerID =1001;  DROP TABLE CUSTOMERS; DROP TABLE ORDERS 

Obviously, if you were to execute this SQL script, it could seriously compromise your database. The easiest way to prevent the SQL script hack is to use parameterized queries where you bind the user's input to query parameters. Because of the way parameters are treated, the semicolons and additional SQL statements would be all considered part of the WHERE clause condition, and would not be executed as separate statements.

9. Synchronizing DataSets and XMLDataDocuments

You can create, manage, and save XML data in the .NET framework using an XMLDocument instance. An XMLDocument provides an in-memory store for XML data that can be navigate in a hierarchical manner consistent with XML data. 

A DataSet is also an in-memory store for data, but it provides a relational view of data instead of a hierarchical one. Imagine that you wanted to navigate a DataSet hierarchically, like an XMLDocument. Or maybe you would like to use the relational DataSet mode of access with your XML data. Fortunately, both are possible by simply synchronizing a DataSet with an XMLDataDocument.

You create an XMLDataDocument by passing a DataSet to its constructor. The XMLDataDocument can then be used to navigate, edit, and save the DataSet's data using the same techniques that you use to modify XML using an XMLDocument instance. Once the XMLDataDocument is created, both the XMLDataDocument and the associated DataSet point to a common in-memory store. Any changes made with either instance are immediately visible to the other. 

For example, you can write to the data using the DataSet and then read that data using the XMLDataDocument. A simple demonstration of using a DataSet and an XMLDataDocument together can be found in the XMLDataDocument project, shown in the following figure.

The DataSet is used to display the data in a DataGrid, while the lower TextBox is used to display text obtained from the XMLDataDocument. Any changes made to data in the DataGrid will be seen in the XML output, and any changes made to the XMLDataDocument nodes or attributes can be seen in the DataGrid. The following code creates the XMLDataDocument:

procedure TWinForm.InitializeDataComponent; begin   Connection1 := SqlConnection.Create('Persist Security Info=False;' +     'Integrated Security=SSPI;database=northwind;' +     'server=training5;Connect Timeout=30');   Connection1.Open();   DataAdapter1 := SqlDataAdapter.Create('select * from customers',     Connection1);   DataSet1 := DataSet.Create();   DataAdapter1.Fill(DataSet1, 'table1');   DataGrid1.DataSource := DataSet1.Tables[0];   DataSet1.EnforceConstraints := False;   Doc1 := XmlDataDocument.Create(DataSet1); end;

10. Computing Expressions on Filtered Subset of Records

The Compute method permits you to quickly and easily calculate an aggregate value for a subset of records in a DataTable. Values calculated with Compute differ from those calculated with aggregate DataColumns because Compute can perform its calculation on a subset of records. The subset is defined using a filter expression, which is passed as a parameter to the Calculate method.

The use of Compute is demonstrated in the Compute project. The following shows this project's main form after the count of customer records where the City field contains the value Madrid is performed.

The following is the event handler that performed the computation:

procedure TWinForm.Button1_Click(sender: System.Object; e: System.EventArgs); begin   if ExpressionTextBox.Text = '' then     raise Exception.Create('Expression cannot be blank');   if FilterTextBox.Text = '' then     raise Exception.Create('Expression cannot be blank');   ValueTextBox.Text := DataTable1.Compute(ExpressionTextBox.Text,     FilterTextBox.Text).ToString; end;

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a company that provides computer software training, consulting, mentoring, and development services. He is an award-winning, best-selling co-author of nineteen books, including Advantage Database Server: The Official Guide (2003, McGraw-Hill/Osborne Media Group), Building Kylix Applications (2001, Osborne/McGraw-Hill), Oracle JDeveloper (1998, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), Delphi In Depth (1996, Osborne/McGraw-Hill), and Programming Paradox 5 for Windows (1995, Sybex). Cary's online Web column, The Professional Developer, is currently a feature of the Borland Developers Network (bdn.borland.com), and he is a popular speaker at conferences, workshops, and training seminars throughout North America and Europe.

Cary is the author and trainer for the Delphi Developer Days 2001-2004 seminars and workshops series and the Advantage Developer Days 2004 seminars. He was also the author and principle speaker for the 1995-1999 Delphi World Tours and the 1999-2001 Delphi Development Seminars. Cary's company, Jensen Data Systems, Inc., won the 2002 and the 2003 Delphi Informant Reader's Choice Awards for Best Training (and was first running up in 2004). Cary has a Ph.D. in Human Factors Psychology from Rice University, specializing in human-computer interaction. The Jensen Data Systems, Inc. Web site is at www.JensenDataSystems.com. You can contact Cary at cjensen@jensendatasystems.com.

Copyright) 2004 Cary Jensen, Jensen Data Systems, Inc.
ALL RIGHTS RESERVED. NO PART OF THIS DOCUMENT CAN BE COPIED IN ANY FORM WITHOUT THE EXPRESS, WRITTEN CONSENT OF THE AUTHOR.


Published on: 10/11/2004 2:18:28 PM

Server Response from: SC1

Copyright© 1994 - 2009 Embarcadero Technologies, Inc. All rights reserved.



Dapatkan alamat Email baru Anda!
Dapatkan nama yang selalu Anda inginkan sebelum diambil orang lain!


Firefox 3: Lebih Cepat, Lebih Aman, Dapat Disesuaikan dan Gratis.

0 Comments: