DATABASE

.NET Compact Framework 3.5 : Examining ADO.NET

6/30/2012 3:08:05 PM
ADO.NET is a set of classes for accessing and managing in-memory data. These classes provide the primary means for bringing data from a database into a managed-code program. The ADO.NET classes provide in-memory data support in a variety of environments for a wide range of application types, including desktop Windows Forms applications, Web Forms applications, Web services, and mobile-device applications. In fact, a good way to access a database that does not yet have a .NET Compact Framework provider is to wrap access to that database in a Web service.

The ADO.NET classes were designed to work with data that is drawn from a variety of sources or, in the terminology of ADO.NET, from a variety of data providers. Whatever the source of the data, the ADO.NET classes allow data to be managed and presented in a standard fashion. As mentioned earlier, to make this happen one layer of the ADO.NET classes is provider-specific, meaning those classes are capable of working with only one data provider, such as the SqlCeConnection class, which can connect only to a SQL Server CE database, or the SqlCommand class, which can submit a SQL statement only to a SQL Server database.

The other layer contains provider-independent memory-resident database classes, such as the DataSet, DataTable, and DataView classes. The data contained within these classes has been converted to the ADO.NET data types, and the structure of the data supported by these classes is independent of the database engine that provides the data. Thus, an ADO.NET data set may contain data tables with data drawn from multiple sources. For example, a data set could contain some tables that have been read from a Microsoft SQL Server CE database and other tables read from a Microsoft SQL Server database.

The ADO.NET memory-resident database classes treat data as provider-agnostic, meaning there is no easy way—and, hopefully, no need—to determine which data came from what data source. You can even build and populate data sets and data tables from scratch without reference to a particular database. Additionally, data sets can read and write their data to XML files without the need for any provider-specific software.

1. A Layered Approach

Because of the capabilities of the ADO.NET classes, you can use either a two-layer approach or a three-layer approach to data management; that is, you can use a memory-resident database to hold your data, or you can bypass the memory-resident database and move the data directly from the data source to its final destination.

In a two-layer approach, you submit SQL queries to the database and retrieve the data into program variables and control properties. You then build SQL statements to make the user-requested changes to the data and submit them to the database (see Figure 1). This is often referred to as the connected approach because the application must be connected to the database while transferring data. Do not confuse this meaning of the term connected with being connected to a network. 

Figure 1. A Two-Layer (Connected) Approach


In a three-layer approach, you bring the data into a memory-resident data set, perhaps manipulate the data as it resides in the data set, and then bind some or all of the data to controls on your form. Changes that the user makes to the data in the form are automatically propagated back to the data set; class methods then propagate changes from the data set to the database (see Figure 2). This is often referred to as the disconnected approach, meaning that the application is not connected to the database while it is accessing the data in the data set.

Figure 2. A Three-Layer (Disconnected) Approach


2. The ADO.NET Classes

Table 1 presents an overview of the classes you can use to access SQL Server CE databases.  They fall into four general categories:

  1. File system classes

  2. Provider-specific ADO.NET classes

  3. Provider-independent ADO.NET classes

  4. Data-bindable control classes

Table 1. Classes for Accessing, Maintaining, and Displaying Data
CategoryClassPurpose and Comments
File system classes[a]DirectoryTo create, delete, move, and detect directories
 FileTo create, delete, move, copy, and detect files
Provider-specific classes for SQL Server CE[b] and SQL Server[c]SqlCeEngineTo create and compact a SQL Server CE database
SqlCeConnection, SqlConnectionTo open a connection to a database. Only one open connection per SQL Server CE database is allowed at a time
SqlCeCommand, SqlCommandTo submit a SQL statement to the database. Contains the connection object
SqlCeDataReader, SqlDataReaderTo retrieve the rows returned by a command. Can be created only by the command’s ExecuteReader method. Only one open data reader per SQL Server connection is allowed at a time
SqlCeDataAdapter, SqlDataAdapterTo move data between a database and a DataTable. Contains up to four (SELECT, INSERT, UPDATE, and DELETE) command objects
SqlCeCommandBuilder, SqlCommandBuilderTo reverse-engineer a simple SELECT statement to create the INSERT, UPDATE, and DELETE statements used by an adapter to move modified data from a DataTable back to the database
Provider-independent ADO.NET classes[d]DataSetTo provide a memory-resident database. Contains the provider-independent classes listed below
 DataTableTo provide a memory-resident table
 DataRowTo provide a row within a DataTable object. Contains the data. Has methods to access other rows in other tables based on DataRelation objects, such as the GetChildRows method
 DataViewTo sort and filter the rows of a DataTable object. Primarily for use with data binding
 DataRelationTo specify the parent/child relationship between two DataTable objects
 UniqueConstraintTo constrain a DataTable object
 ForeignKeyConstraintTo constrain two DataTable objects
Data-binding support classes[e]Multi-item controls: DataGrid, ListBox, ComboBox

Single-item controls: TextBox, Label, RadioButton, CheckBox, Button, etc.

DataBindings collection

BindingContext

CurrencyManager
To display one or all columns of a DataTable or DataView object (when data-bound). Cannot be updated

To display one column of one row of a DataTable or DataView object (when data-bound). Automatically propagates data changes back to the bound DataTable. A Currency-Manager object (see below) manages row positioning and data updating

To specify data binding between a DataTable or DataView object and the single-item control; is a property of a single-item control

To contain a collection of Currency-Manager objects; is a property of a form. Used by the form to manage all data binding of all contained controls. Created by the .NET runtime

To manage the automatic updating of bound DataTable objects. Can be used to override the automatic updating of bound DataTable objects by canceling the update or forcing the immediate execution of the update. Contains a read-write indexer property that identifies the “current” row of the bound DataTable or Data-View. Created by the .NET runtime. Can be obtained by calling the form’s BindingContext(DataTable/DataView name) indexer property

[a] In the System.IO namespace.

[b] In the System.Data.SqlServerCe namespace.

[c] In the System.Data.SqlClient namespace.

[d] In the System.Data namespace.

[e] In the System.Windows.Forms namespace.

Also note that .NET Compact Framework multi-item controls do not permit users to enter data. The DataGrid control is for display only, and the ComboBox control does not permit direct entry of a value, allowing the user to select only from the list.

2.1. Namespace References and Assembly References

The SQL Server CE–specific ADO.NET classes are located in the System.Data.SqlServerCe namespace. There is a similar set of classes in the System.Data.SqlClient namespace for use when accessing SQL Server. Other namespaces hold classes for accessing data via Oracle, OLE DB, ODBC, and other providers on the desktop and, if supported, on a device.

Because the classes are located in specific namespaces, you must set some references in your project before writing any code. You need to set a reference to the System.Data.SqlServerCe namespace because it holds the classes you must use to access a SQL Server CE database; and you need to set a reference to the System.Data.SqlClient namespace when accessing SQL Server. You also need to set a reference to the System.Data.Common namespace because it contains private classes used by the data adapter class. And, finally, you need a reference to the System.Data namespace because it contains the provider-independent classes listed in Table 1. Therefore, among the namespace references and the assembly references you may need are those listed in Table 2.

Table 2. Commonly Used Namespaces for Data Access
NamespaceLocated in Assembly
System.DataSystem.Data.dll
System.Data.CommonSystem.Data.Common.dll
System.Data.SqlServerCeSystem.Data.SqlServerCe.dll
System.Data.SqlClientSystem.Data.SqlClient.dll

2.2. Functionality: Supersets and Subsets

The minimum set of provider-specific classes, methods, properties, and events that must be supported by a provider has been specified by Microsoft as a set of interfaces. For instance, the PMEs that a DataReader must support are defined in the IDataReader interface. As you move between SQL Server CE and SQL Server, you use the same properties and methods to access the data regardless of provider. For example, we’re about to use the SqlCeDataReader class, which has a NextResult method. The SqlDataReader class and the OleDbDataReader class also have this same method, as does the AdoceDataReader class in the Pocket Access data provider from In The Hand. They must all have the NextResult method because the interface requires it.

The advantage of this standards-driven approach is that the same code can be used to access data from a variety of providers, yet the underlying classes can be optimized to take advantage of the capabilities of the individual data provider. The disadvantage of this approach, especially for SQL Server CE programmers, is that functionality that is inappropriate for SQL Server CE exists in our classes simply because it may be appropriate for SQL Server or Oracle or some other data provider and therefore has been included as part of the standard.

Continuing with our example, the SqlCeDataReader class’s NextResult method provides for the processing of commands that contain multiple SELECT statements. But the SQL Server CE database does not support multiple SELECT commands. For this reason, the NextResult method is not supported and so is completely inappropriate when accessing a SQL Server CE database.

Thus, learning the object model in the System.Data.SqlServerCe namespace requires more than just learning the properties and methods—it also involves learning which of those properties and methods are appropriate for SQL Server CE (or whatever ADO.NET data provider you choose to use). O

Just as the SQL Server CE syntax is a subset of that found in SQL Server, ADO.NET for Windows CE is also a subset of the desktop version. For instance, only three of the eight overloads for the DataSet.ReadXml method are implemented. In general, however, ADO.NET for Windows CE has most of the functionality that the desktop version has, and you should not feel constrained.

3. ADO.NET Error Handling

Before diving into SQL Server CE programming in general, we take a moment to examine error handling. In the .NET Compact Framework, ADO.NET error handling uses the standard try...catch...finally technique used in any .NET application. If an error occurs, a SqlCeException is thrown. There is only one SqlCeClient exception class, SqlCeException, not one derived class for each possible error or category of error. Because SQL Server CE is written in unmanaged (native) code, it is written for the world of error numbers and HResults, not exceptions. Thus, each error represented by a SqlCeException contains its own error information.

The SqlCeException class has an Errors collection property, consisting of one or more SqlCeErrors (each containing the Source, Message, NativeError, and HResult properties, which are also properties of the SqlCeException class) plus three string parameters and three numeric parameters. In the SqlCeException class properties, Source, NativeError, and HResult are equal to the properties of the same name of SqlCeException.Errors[0], while Message is equal to String.Empty. The SqlCeException class also has an InnerException property, which is often empty. So, your best plan when handling a SQL Server CE exception is to examine its Errors collection. For instance, if a SELECT statement contains a misspelled column name, you receive the following information via the SqlCeException.Errors[0] object:

Source: Microsoft SQL Server Windows CE Edition.

Message: The column name is not valid.

NativeError: 25503.

HResult: -214721900.

ErrorParameters[0]: Empty.

ErrorParameters[1]: [The name of the misspelled column.]

ErrorParameters[2]: Empty.

NumericErrorParameters[0]: 0.

NumericErrorParameters[1]: 0.

NumericErrorParameters[2]: 0.

You should always use error handling. If nothing else, error handling allows your application to present the most meaningful message possible to the user and to exit gracefully, as shown in the following code:

private string  strConn = "Data Source=" +
                           @"My Documents\ourProduceCo.sdf";
             :
             :
SqlCeEngine dbEngine = new SqlCeEngine();
dbEngine.LocalConnectionString = strConn;
             :
             :
try
{
   dbEngine.CreateDatabase();
}
catch( SqlCeException exSQL )
{
   MessageBox.Show("Unable to create database at " +
                   dbEngine.LocalConnectionString +
                   ". Reason:  " +
                   exSQL.Errors[0].Message );
}

We mention error handling because it is a necessary part of production code. Without it, your code is brittle to the touch of all but the most casual user. With it, your code becomes robust enough to withstand the most brutal attack.

Other  
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 4) - Connecting the Source and Destination Adapters with a Path
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 3) - Setting Up Column Information
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 2)
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 1) - Creating Packages Programmatically - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 2) - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 1) - Control Flow
  •  SQL Server 2005 : Extending Your Database System with Data Mining - Data Mining Applied (part 2)
  •  SQL Server 2005 : Extending Your Database System with Data Mining - Data Mining Applied (part 1)
  •  # Oracle Coherence 3.5 : Achieving Performance, Scalability, and Availability Objectives (part 2)
  •  # Oracle Coherence 3.5 : Achieving Performance, Scalability, and Availability Objectives (part 1)
  •  
    Video
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone