Browse DevX
Sign up for e-mail newsletters from DevX


Building ASP.NET Applications with Delphi and Advantage Database Server : Page 2

The Advantage Data Provider for .NET provides a feature-rich mechanism for Delphi developers using the Advantage Database Server (ADS) to build ASP.NET web applications and web services.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Advantage Data Provider Primary Classes

As an Advantage user, the primary classes that you will use in your ASP.NET applications include AdsConnection, AdsCommand, AdsDataReader, AdsExtendedReader, and AdsDataAdapter. There is also a good chance that you'll use AdsTransactions and AdsParameters. The following list describes each class briefly.

  • AdsConnection: You use an AdsConnection to make your connection to the Advantage Database Server. The AdsConnection.ConnectionString property defines where your data is located, user and password information, and other connection specifics of, including the size and duration of its connection pool.
  • AdsCommands: After establishing a connection, you use one or more AdsCommands to configure your database as well as access its data. AdsCommands can execute statements that return no result sets (such as a CREATE TABLE or DROP VIEW statement), that return a scalar value (a single value of type TObject), or a result set. You can navigate returned result sets using a DataReader, or use them to populate an in-memory DataTable.
  • AdsReader: An AdsDataReader is a read-only forward-only cursor that you can use to read records returned by a SELECT statement or a stored procedure. The Advantage .NET Data Provider also supports a super-charged data reader in the form of the AdsExtendedReader, which is unique in the .NET world in that it provides bi-directional navigation, as well as read/write access to data. In addition, it supports server-side cursors for high-speed navigation as well as optimized filtering and scopes (also known as ranges).
  • AdsDataAdapter: The AdsDataAdapter component uses an AdsCommand internally to load data from a result set into an in-memory DataTable. DataTables are useful for loading data into grid-like controls, and also provide a convenient mechanism for persisting result sets between web page renderings.
  • AdsTransaction: Use an AdsTransaction when your code needs to provide all-or-none modifications to two or more records, or to data in two or more database tables. You use the BeginTransaction method of your connected AdsConnection to create an AdsTransaction. After making the data changes, call the AdsTransaction's Commit or Rollback methods to perform the associated operation.
  • AdsParameters: When the CommandText property of your AdsCommand specifies a parameterized query or stored procedure call, you use AdsParameters instances to define the data types, names, and values of those parameters. You can create AdsParameters either by calling the AdsCommand's CreateParameter method or by calling the AdsParameter's constructor directly. After configuring the created parameters, add them to the AdsCommand's Parameters collection.

Basic Operations

You establish a connection to a data dictionary, or to a folder containing free tables, using an AdsConnection. The following code segment demonstrates a simple connection to the data dictionary of the sample database.

Author's Note: You can find a description of the sample database and data dictionary used in these examples in the book Advantage Database Server: A Developer's Guide (Sybase, Inc., 2007), by Cary Jensen and Loy Anderson. The complete text of this book is available in the Advantage 9.0 Help files. The following examples assume that you have followed the steps through Chapter 4 of the book to create a data dictionary from the tables supplied in the book's sample applications and code. See Appendix A for instructions on downloading the sample applications and source code.

... var Connection: AdsConnection; begin Connection := AdsConnection.Create; Connection.ConnectionString := 'Data Source='+ 'C:\AdsBook\DemoDictionary.add;user id=adssys;password=password;' + 'ServerType=ADS_REMOTE_SERVER | ADS_LOCAL_SERVER;'; Connection.Open; try //code to use the connection here finally Connection.Close; end; end;

This simple example demonstrates calling the AdsConnection, constructor and assigning its connection string, followed by a call to open the connection, use it, and close it. All the following examples assume that you have created an active connection referred to by the variable named Connection.

The preceding example represents the connection string literally in the code; however, it's also possible to store the connection string in the application's web.config file or in machine.config (a configuration file available to all applications on the web server in ASP.NET 2.0 and later).

In ASP.NET applications, it's important to ensure that each call to open a connection is matched by a corresponding call to close the connection. By default, the Advantage Data Provider gets its connections from a connection pool. If you fail to call close, the connection will not be returned to the connection pool until the ASP.NET garbage collector destroys the connection (and this might take a while). In contrast, by closing the connection explicitly, you ensure that the connection returns to the pool as soon as possible following its use, making it immediately available for use by another web page.

The connection provides methods to create AdsTransactions and AdsCommands. For example, the following code segment creates a table named #MyTable (the # character designates this table as a temporary table, one that exists only for the duration of the current connection).

var Command: AdsCommand; begin Command := Connection.CreateCommand; Command.CommandText := 'CREATE TABLE #MyTable (' + 'NumField Integer, ' + 'StringField cichar ( 100 ))'; Command.ExecuteNonQuery;

The CREATE TABLE command is a SQL data definition language (DDL) command, and it does not return a value or a result set. If your query returns a scalar value, you can use ExecuteScalar, which returns a TObject result. It is necessary to convert or cast this result to the appropriate data type, as demonstrated in the following code segment.

var Command: AdsCommand; IntValue: Integer; begin Command := Connection.CreateCommand; Command.CommandText := 'SELECT Count(*) FROM #MyTable'; IntValue := Convert.ToInt32( Command.ExecuteScalar);

Whenever you need to perform two or more operations, and have them succeed in an all-or-none fashion, you use an AdsTransaction. The following code demonstrates how to insert four records into the temporary table #MyTable within a transaction.

var Command: AdsCommand; Transaction: AdsTransaction; begin Transaction := Connection.BeginTransaction; try Command := Connection.CreateCommand; Command.CommandText := 'INSERT INTO #MyTable ' + 'VALUES (1, ''one'');'; Command.ExecuteNonQuery; Command.CommandText := 'INSERT INTO #MyTable ' + 'VALUES (2, ''two'');'; Command.ExecuteNonQuery; //The following command contains a SQL Script Command.CommandText := 'INSERT INTO #MyTable VALUES (3, ''three'');' + 'INSERT INTO #MyTable VALUES (4, ''four'');'; Command.ExecuteNonQuery; Transaction.Commit; except Transaction.Rollback; end;

When a query returns a result set, you can use a data reader to obtain a cursor to the result set or load the result set into an in-memory DataTable. The following example demonstrates a function that returns a result set using an AdsDataReader.

function ReusableDataLayer.GetAllEmployeeNames: AdsDataReader; var Command: AdsCommand; begin Command := Connection.CreateCommand; Command.CommandText := 'SELECT [First Name] + '' '' + '[Last Name] as Name, ' + '[Employee Number] FROM [EMPLOYEE]'; Result := Command.ExecuteReader; end;

Figure 6. Populated ListBox: Here's how the page might look after populating the ListBox with all the employee names.

The following code demonstrates how to use the preceding code, which is associated with a reusable data layer, to bind data to a ListBox (see Figure 6).

procedure TUseDataReader.Button1_Click( sender: System.Object; e: System.EventArgs); var ReusableDataLayer1: ReusableDataLayer; DataReader: AdsDataReader; begin ReusableDataLayer1 := ReusableDataLayer.Create(Context); try DataReader := ReusableDataLayer1.GetAllEmployeeNames; try ListBox1.DataSource := DataReader; ListBox1.DataTextField := 'Name'; ListBox1.DataValueField := 'Employee Number'; DataBind; finally DataReader.Close; end; finally ReusableDataLayer1.Dispose; end; end;

In the preceding code, note that the Dispose method of the reusable data layer calls the AdsConnection's Close method. It's also important to ensure that you close a DataReader when you are through with it.

Thanks for your registration, follow us on our social networks to keep up-to-date