Performing Basic Tasks with ADS and ADO.NET

This section describes some of the more common tasks that you can perform with the Advantage .NET Data Provider. These include connecting to a data dictionary, executing a query, using a parameterized query, retrieving and editing data, and executing a stored procedure.

Connecting to Data

You connect to a data dictionary or a directory in which free tables are located using an AdsConnection object found in the Advantage.Data.Provider namespace. At a minimum, you must provide the AdsConnection object with sufficient information to locate your data and configure how the data should be accessed. This is done using the ConnectionString property. This property accepts name/value pairs using the parameters listed in Table 15-1. If you use more than one name/value pair, separate them with semicolons.

Table 15-1: Parameters for Connecting with the Advantage .NET Data Provider

Parameter	Description
CharType	Set to the character set type for DBF files. Valid values are ADS_ANSI and ADS_OEM. The default value is ADS_ANSI.
Compression	Set to ALWAYS, INTERNET, NEVER, or empty. If left empty (the default), the ADS.INI file will control the compression setting. This parameter is not used by ALS.
Connection Lifetime	The number of seconds after which a connection will be destroyed after being returned to the connection pool. The default is 0.
Data Source	The path to your free tables or data dictionary. If you are using a data dictionary, you must include the data dictionary filename in this path. It is recommended that this path be a UNC path. Data Source is a required parameter.
DbfsUseNulls	Set to TRUE to return empty fields from DBF files as NULL values. If set to FALSE, empty fields are returned as empty data values. The default is FALSE.
EncryptionPassword	Set to an optional password to use for accessing encrypted free tables. This parameter is ignored for data dictionary connections.
IncrementUsercount	Set to TRUE to increment the user count when the connection is made, or FALSE to make a connection without incrementing the user count. The default is FALSE.
Initial Catalog	Optional name of a data dictionary if the data dictionary is not specified in the Data Source parameter.
LockMode	Set to ADS_PROPRIETARY_LOCKING or ADS_COMPATIBLE _LOCKING to define the locking mechanism used for DBF tables. Use ADS_COMPATIBLE_LOCKING when your connection must share data with non-ADS applications. The default is ADS_PROPRIETARY_LOCKING.
Max Pool Size	The maximum number of connections to maintain in the connection pool. The default is 100.
Min Pool Size	The minimum number of connections to maintain in the connection pool. The default is 0.
Password	When connecting to a data dictionary that requires logins, set to the user's password.
Pooling	Set to TRUE to enable connection pooling, and FALSE to disable it. The default is TRUE.
ReadOnly	Set to TRUE to open tables readonly, or FALSE to open tables as editable (read-write). This setting applies to all CommandType values. The default is FALSE.
SecurityMode	Set to ADS_CHECKRIGHTS to observe the user's network access rights before opening files, or ADS_IGNORERIGHTS to access files regardless of the user's network rights. The default is ADS_CHECKRIGHTS. This property applies only to free table connections.
ServerType	Set to the type of ADS server you want to connect to. Use ADS_LOCAL_SERVER, ADS_REMOTE_SERVER, or ADS_INTERNET_SERVER. To attempt to connect to two or more types, separate the server types using a vertical bar (|). This is demonstrated in the ConnectionString shown later in this chapter.
Shared	Set to TRUE to open tables shared, or FALSE to open tables exclusively. This setting only applies to CommandType.TableDirect. The default is TRUE.
ShowDeleted	Set to TRUE to include deleted records in DBF files, or FALSE to suppress deleted records. The default is FALSE.
StoredProcedureConnection	Set to TRUE if connecting from within a stored procedure. When set to TRUE, the connection does not increment the user count. The default is FALSE.
TableType	Set to ADS_ADT, ADS_CDX, or ADS_NTX to define the default table type. The default is ADS_ADT. This parameter is ignored for data dictionary connections.
TrimTrailingSpaces	Set to TRUE to trim trailing spaces from character fields, or FALSE to preserve trailing spaces. The default is FALSE.
User ID	If connecting to a data dictionary that requires logins, set to the user's user name.

With the Advantage .NET Data Provider, the connection string property values can be enclosed in either double or single quotes, if necessary. For example, if the password contains a semicolon (the connection string parameter delimiter), it would be necessary to enclose it in single or double quotes.

For any of the optional connection string parameters that you fail to provide, the Advantage .NET Data Provider will automatically employ the default parameters.

Because the AdsConnection object that is used by this project must be used by a number of methods, the AdsConnection variable and several other variables that must be repeatedly referenced are declared private members of the Form class. The following is this declaration:

private AdsConnection connection; private IDbCommand command; private AdsCommand paramCommand; private IDataReader dataReader; 

The data source location of the data dictionary is also declared as a constant member of this class. This constant refers to a share named “share,” on a server named “server,” as shown in the following declaration:

private const String DataPath = "\\\\server\\share\\program files" +   "\\extended systems\\advantage\\adsbook\\DemoDictionary.add;";

This connection, named AdsConnection, is created, configured, and opened from the InitializeDataComponents method of the form, along with several other objects. InitializeDataComponents is called from the Form’s constructor. The relevant portion of this custom private method is shown in the following code:

private void InitalizeDataComponents() {   connection = new AdsConnection();   connection.ConnectionString = "Data Source=" + DataPath +    ";user ID=adsuser;password=password;"+   "ServerType=ADS_LOCAL_SERVER | ADS_REMOTE_SERVER;" +    "FilterOptions=RESPECT_WHEN_COUNTING;TrimTrailingSpaces=True";   connection.Open();   command = new AdsCommand();   command = connection.CreateCommand(); //additional statements follow

Note

If you have difficulty connecting, it might be because you have other client applications, such as the Advantage Data Architect, connected using a local connection. Ensure that all clients on the same machine use the same type of connection.

Executing a Query

You execute a query that returns a result set using an AdsCommand. There are numerous overloaded methods for doing this. The following code segment demonstrates one of these, where a query string and an open connection are passed as parameters to an AdsDataAdapter’s constructor. Within this constructor, the query string is assigned to an internally created AdsCommand object that is associated with the SelectCommand property of the AdsDataAdapter, which performs the query execution. The Fill method is then invoked on this AdsDataAdapter, which causes the result set to be loaded into a DataTable of a DataSet.

This DataTable is then used to display the resulting data in a DataGrid, as shown in Figure 15-2. The following code demonstrates the execution of a query entered by the user into the TextBox named selectText. This method is associated with the Execute SELECT button (shown in Figure 15-1):

private void executeSELECTBtn_Click(object sender,    System.EventArgs e) {   IDataAdapter adapter ;   adapter = new AdsDataAdapter(selectText.Text, connection);   DataSet ds = new DataSet();   adapter.Fill(ds);   DataTable dt = ds.Tables[0];   dataGrid1.DataSource = dt; }

Figure 15-2: The results of a SELECT query displayed in a DataGrid

Notice that the AdsDataAdapter that is created is assigned to a variable of type IDataAdapter. IDataAdapter is the interface that all data adapters implement. While we could have just as well assigned this object to a variable of type AdsDataAdapter, assigning it to an interface variable makes our code more portable, since any IDataAdapter implementing class can be assigned to this variable. This technique is used extensively in this chapter, wherever no Advantage .NET Data Provider functionality is specifically needed.

If you need to execute a query that does not return a record set, call the ExecuteNonQuery method of an AdsCommand object. The use of an AdsCommand object to execute a query that does not return a recordset is demonstrated later in this chapter.

Using a Parameterized Query

Parameterized queries are defined using an AdsCommand object. Before you can invoke a parameterized query, you must create one AdsParameter object for each of the query’s parameters. You can create an AdsParameter instance by calling the Add method of the AdsCommand object’s Parameters property.

The definition of a parameterized query, including the creation of a parameter, is shown in the following code segment, which is part of the private InitializeDataComponents method shown earlier:

paramCommand = new AdsCommand("SELECT * FROM INVOICE " +   "WHERE [Customer ID] = ?", connection); paramCommand.Parameters.Add(1,System.Data.DbType.Int32);

Before you can execute an AdsCommand that contains a parameterized query, you must bind data to each of its parameters. This is shown in the following method, which is called by the Show Invoices button (shown in Figure 15-1):

private void doParamQuery_Click(object sender,    System.EventArgs e) {   IDataAdapter dataAdapter;   DataSet ds = new DataSet();   DataTable dt;   if (paramText.Text.Equals("")) {   MessageBox.Show(this,   "You must supply a customer ID");   return;   }   paramCommand.Parameters[0].Value = Int32.Parse(paramText.Text);   dataAdapter = new AdsDataAdapter(paramCommand);   dataAdapter.Fill(ds);   dt = ds.Tables[0];   if (dt.Rows.Count == 0)   {   MessageBox.Show(this,   "No invoices for customer ID");   return;   }   dataGrid1.DataSource = dt; } 

As you can see from this code, after verifying that a value has been entered into the customer ID field, the entered data is assigned to the Value property of the AdsParameter. The AdsCommand object that holds the parameter is passed as an argument to an AdsDataAdapter, which then executes the query and assigns the result set to a DataTable. Note that it was not necessary to pass a connection object to the AdsDataAdapter constructor, since the AdsCommand object itself was constructed based on a connection.

This example is actually a classic example of how parameterized queries are used. Specifically, the query text is defined only once, but can be executed repeatedly. And by changing only the value of the parameter, a different result set can be returned upon each execution.

Reading and Writing Data

You read data from the fields of a table using either an AdsDataReader or a DataColumn, depending on how you are working with your data. Reading a field is demonstrated in this section using a DataColumn. Using an AdsDataReader is discussed later in this chapter.

You can use a DataColumn to read and write data from a field of a record. You obtain a DataColumn from the Items property of a DataRow. You obtain a DataRow through the Rows property of a DataTable.

Once you have a reference to a DataColumn, you can execute its ToString method to read its data. This technique is demonstrated in the following method associated with the Get Address button (shown in Figure 15-1):

private void getAddressBtn_Click(object sender,    System.EventArgs e) {   AdsCommand getCustCommand;   IDataAdapter dataAdapter;   String custNo;   custNo = custNoText.Text;   if (custNo.Equals(""))    {   MessageBox.Show(this, "You must supply a customer ID");   return;   }    getCustCommand = new AdsCommand(   "SELECT * FROM CUSTOMER WHERE [Customer ID] = ?",   connection);   getCustCommand.Parameters.Add(1,System.Data.DbType.Int32);   getCustCommand.Parameters[0].Value =    Int32.Parse(custNo);   dataAdapter = new AdsDataAdapter(getCustCommand);   DataSet ds = new DataSet();   dataAdapter.Fill(ds);   DataTable dt = ds.Tables[0];   DataRow dr = dt.Rows[0];   oldAddressText.Text = dr.["Address"].ToString(); }

You can also use a DataColumn to write data back to ADS. This process, however, is somewhat involved.

In ADO.NET, data is always written to the underlying provider using SQL queries. These queries are associated with the DeleteCommand, InsertCommand, and UpdateCommand AdsCommand properties of an AdsDataAdapter. In order for the applicable change to be submitted to ADS, the corresponding property of the AdsDataAdapter must have an AdsCommand object assigned to it. For example, in order to insert a new record into an ADS table, the AdsDataAdapter must have an appropriate INSERT query assigned to the AdsCommand referenced by the InsertCommand property.

The queries associated with the DeleteCommand and UpdateCommand AdsCommands are parameterized, including a WHERE clause that identifies the record that is being deleted or updated, respectively. The values for these parameters are automatically bound by the AdsDataAdapter prior to the execution of the associated query.

Although you can create the AdsCommand objects manually, doing so is not necessary if the query returns a live cursor. Instead, you merely need to call the constructor of an AdsCommandBuilder, passing the AdsDataAdapter as an argument. The AdsCommandBuilder will examine the query associated with the SelectCommand property of the AdsDataAdapter, and will then generate the appropriate AdsCommand objects and will assign them to the proper AdsCommand properties of the AdsDataAdapter.

If your query returns a static cursor, or if you want to have complete control over the query, you must build the delete, insert, and update Command objects manually. You must then assign these Command objects to the appropriate properties of your AdsDataAdapter before you can call Update.

After making changes to one or more columns of one or more records of a DataTable, you call the Update method of the AdsDataAdapter, passing the corresponding DataSet as an argument. The AdsDataAdapter responds by applying the changes from the DataSet. The following code, associated with the Set Address button (shown in Figure 15-1), demonstrates this operation:

private void setAddressBtn_Click(object sender,  System.EventArgs e) {   AdsCommand updateCustCommand;   IDataAdapter dataAdapter;   String custNo;   AdsCommandBuilder commandBuilder;   custNo = custNoText.Text;   if (custNo.Equals(""))    {   MessageBox.Show(this, "You must supply a customer ID");   return;   }   updateCustCommand = new AdsCommand(   "SELECT * FROM CUSTOMER WHERE [Customer ID] = ?",   connection);   updateCustCommand.Parameters.Add(1,   System.Data.DbType.Int32);   updateCustCommand.Parameters[0].Value =    Int32.Parse(custNo);   dataAdapter = new AdsDataAdapter(updateCustCommand);   commandBuilder =    new AdsCommandBuilder((AdsDataAdapter)dataAdapter));   DataSet ds = new DataSet();   dataAdapter.Fill(ds);   DataTable dt = ds.Tables[0];   DataRow dr = dt.Rows[0];   dr["Address"] = newAddressText.Text;   dataAdapter.Update(ds);  } 

Of course, you can write a SQL UPDATE query and execute it using an AdsCommand to achieve a similar result. While this approach is more efficient at runtime than using a CommandBuilder, since the CommandBuilder will require a number of round trips to the server to collect the metadata that it needs to construct the DELETE, INSERT, and UPDATE commands, each manually created SQL query introduces one more thing about your code that must be maintained—the queries themselves. You can decide yourself which approach is best for your applications.

Calling a Stored Procedure

Calling a stored procedure is no different than executing any other query. You can define a SQL EXECUTE PROCEDURE statement and assign it to the CommandText property of an AdsCommand object. Alternatively, you can assign the name of the stored procedure to the CommandText property of the AdsCommand object, and then set the CommandType property to CommandType.StoredProcedure.

You create an AdsCommand object explicitly or permit an AdsDataAdapter to create one for you. If the stored procedure returns a result set, you will either call the ExecuteReader method of the AdsCommand, or an appropriate method of an AdsDataAdapter. Otherwise, you can execute the stored procedure by calling the ExecuteNonQuery method of the AdsCommand.

Invoking a stored procedure that takes one input parameter is demonstrated by the following code associated with the Show 10% of Invoices button (shown in Figure 15-1). The stored procedure referenced in this code is the Delphi AEP created in Chapter 7. If you did not create this AEP, but created one of the other AEPs described in that chapter, substitute the name of the stored procedure object in your data dictionary in the first parameter in the AdsCommand constructor.

private void callStoredProc_Click_1(object sender,    System.EventArgs e) {   AdsCommand storedProcCommand;   IDataAdapter dataAdapter;   DataSet ds = new DataSet();   DataTable dt;   if (paramText.Text.Equals(""))    {   MessageBox.Show(this, "You must supply a customer ID");   return;   }   storedProcCommand = new AdsCommand("DelphiAEP", connection);   storedProcCommand.CommandType = CommandType.StoredProcedure;   storedProcCommand.Parameters.Add(1,System.Data.DbType.Int32);   storedProcCommand.Parameters[0].Value = Int32.Parse(paramText.Text);   dataAdapter = new AdsDataAdapter(storedProcCommand);   dataAdapter.Fill(ds);   dt = ds.Tables[0];   if (dt.Rows.Count == 0)    {   MessageBox.Show(this, "No invoices for customer ID");   return;   }   dataGrid1.DataSource = dt;  }