Transcript conferences.embarcadero.com

3116
Top 10 Techniques for
Microsoft .NET Framework
Database Developers
Cary Jensen
President
Jensen Data Systems, Inc.
Overview
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Expression DataColumns
Intermediate DataColumn Expressions
Navigating Programmatically
Creating Calculated Fields Programmatically
Understanding BindingManagers
Creating Data Modules
Using DataViews
Avoiding the SQL Script Hack
Synchronzing DataSets and XMLDocuments
Computing Expressions on Filtered DataTables
Field References in Expressions
 Use the ColumnName property to reference fields
 if ColumnName contains one of the following characters,
the field reference must be enclosed in brackets:
(space), \n (newline), \t (tab), \r (carriage return), ~, (, ), #,
\, /, =, >, <, +, -, *, %, &, |, ^, ‘, “, [,]
 For consistency, you can use brackets even when the
column name does not include one of the special
characters.
 If your column name includes the ] character, it must be
preceded by a \.
Field Reference Examples
Column Name
AccountNo
Account#
Last Name
Column[1]
Reference
AccountNo
[Account#]
[Last Name]
[Column[1\]]
Literals
 Strings enclosed in single quotes
ex: 'Mr ' + [Contact Name]
 Dates enclosed between # characters:
ex: [Date] - #1/1/2004#
 Numeric values, including decimal values
and scientific notation, are not delimited
ex: [Percent] * 100
String Literals and Delphi
Consider the following string assignment (in C#):
dataColumn1.Expression = "'Mr ' + [First Name] + ' ' + [Last Name] ";
String literals are more complicated for Delphi developers, since single
quotes are already used to delimit strings. For example:
DataColumn1.Expression := '''Mr ''' +
' + [First Name] + ' + ''' ''' + ' + [Last Name]';
You can also use the QuotedStr function in Borland.Vcl.SysUtils:
DataColumn1.Expression := QuotedStr('Mr ') +
' + [First Name] + ' + QuotedStr(' ') + ' + [Last Name]';
Alternatively, enclose string literals between #39, like this:
DataColumn2.Expression := #39 + 'Mr ' + #39 +
' + [Invoice No] + ' + #39 + ' ' + #39 + ' + [Customer ID]';
You can even omit the concatenation operator (+) between #39 and strings:
DataColumn2.Expression :=
#39'Mr '#39' + [Invoice No] + '#39' '#39' + [Customer ID]';
Comparison operators
(in order of precedence)
<
>
<=
>=
<>
=
IN
LIKE
Boolean Operators
( expression)
AND
OR
NOT
Table Navigation
DataSets, DataTables, and DataRows
var
Table: DataTable;
Row: DataRow;
data: String;
begin
Table := DataSet1.Tables[0];
Row := Table.Rows[2];
data := Row[1].ToString();
DataSet
Table1
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
r3c1
r3c2
r3c3
r3c4
r3c5
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
Table2
DataSets, DataTables, and DataRows
var
data: String;
begin
data := DataSet1.Tables[0].Rows[2][1].ToString();
//or
data := DataSet1.Tables[0].Rows[2][‘rowname’].ToString();
DataSet
Table1
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
r3c1
r3c2
r3c3
r3c4
r3c5
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
Table2
Programmatically Create Calculated Fields
 Create a DataColumn in your DataTable that will hold
the calculated field
 After loading data, iterate through the DataTable and
assign the calculation. Then make the calculated field
to readonly
 Add a ColumnChanged event handler to the
DataTable
 From the ColumnChanged event handler, test
whether the changed field is one of those involved in
the calculation. If so, make the calculated field
writeable, re-calculate the calculation, then make the
field readonly once again
Controls and CurrencyManagers
 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 are bound
to the same BindingManagerBase instance.
BindingManagerBase is an abstract class, and two
concrete classes descend from it. These concrete classes
are CurrencyManager and PropertyManager.
Creating a Data Module






Create a form that will serve as the data module (let’s call this form the data
module) Add the ADO.NET components that you want to share to the public
section of the data module's class declaration.
When the data module is created, create the instances of, and configure,
the ADO.NET classes that you added to the form's public section
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 data module data source
For each form that uses the data module, assign that form’s BindingContext
property to the data module's (form-level) 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.
Questions?
Thank You
3116
Top 10 Techniques for Microsoft .NET
Framework Database Developers
Please fill out the speaker evaluation
You can contact me further at …
[email protected]