In PerlNET: An Introduction (CoDe Magazine, Sept/Oct 2002) we introduced you to the new PerlNET technology that allows you to create .NET-compliant applications and components with PERL. In this article we'll show you how to author advanced .NET application in PERL. We'll use ADO.NET classes to incorporate database access into PerlNET programs, then we'll explain and demonstrate working in connected and disconnected modes. PerlASPX adds PERL to the family of ASP.NET languages and makes it possible to develop ASP.NET Web Applications in PERL. We'll explain how to create ASP.NET Web Forms and ASP.NET Web Services.

Database Access with ADO.NET

We live in the age of information technologies. Many modern applications handle large amounts of data. Database engines such as Microsoft SQL Server and Oracle are the ultimate solution for storing information and providing easy access to the data and different optimizations for better performance.

ADO.NET classes assist in retrieving data from numerous different data sources. ADO.NET classes form the bridge between your .NET programs and your database engine.

Data Providers and .NET Database Classes

Prior to creating a data-oriented application you should choose the data provider you'll use to access the database. Data providers are used to execute commands against data sources. The Microsoft .NET platform supports SQL and OLE DB data providers.

The SQL data provider is specially designed to work against Microsoft SQL Server and is highly optimized for such data access.

The SQL data provider is specially designed to work against Microsoft SQL Server and is highly optimized for such data access. The SQL classes reside in the System.Data.SqlClient namespace. OLE DB is not limited to a specific data engine and may be used to access any database, for which there exists an OLE DB driver. The corresponding namespace for an OLE DB data provider is System.Data.OleDb. Additionally, Microsoft is working with vendors to develop other data providers. An open database connectivity .NET provider (ODBC.NET) has been already released.

In this article we'll present code examples that use the SQL data provider. The functionality that these samples will provide applies to the OLE DB provider as well unless we state otherwise. In fact, there are many parallel classes in the System.Data.SqlClient and System.Data.OleDb namespaces. You can distinguish between different data providers' classes by the prefixes in their names. The SQL classes start with the Sql prefix and OLE DB classes with OleDb. Table 1 lists some of the parallel classes in both providers and gives short description of the functionality.

You work with ADO.NET classes in the same manner as you work with any other .NET classes.

ASP.NET pages are completely compatible with standard HTML.

You work with ADO.NET classes in the same manner as you work with any other .NET classes (constructing, invoking methods etc.). In the next sections, we'll show you how to use the classes listed above in different database access scenarios (connected and disconnected).

Connected Database Access

Working in connected mode consists of the three following stages:

To open a connection to a data source you'll define a connection string that specifies different connection parameters. In this case it will be:

"server=localhost;uid=sa;pwd=;"
                  . "database=Northwind;"

You'll connect to the localhost server with the sa user and blank password. We'll work against the Northwind database, which Microsoft supplies SQL Server. You'll pass this connection string as a parameter to the constructor of the SqlConnection class as shown in the following code fragment.

use namespace "System.Data";
use namespace "System.Data.SqlClient";
use PerlNET qw(enum);
# Initialize connection string
my $connstr = "server=localhost;uid=sa;pwd=;"
                   . "database=Northwind;";
# Initialize connection object
# and open connection to database
my $conn = SqlConnection->new($connstr);

The connection to the database will be established after you call the Open method on the SqlConnection class instance:

$conn->Open();

Now the connection is open and you are free to perform different database operations such as querying tables, updating rows, inserting new records, and so forth. We'll describe how to do it shortly.

After you're done it's a good idea to perform some clean up. Since you explicitly opened the connection, you should take care of closing it:

# Close connection
if (conn->{State} ==
    enum("ConnectionState.Open"))
{
   $conn->Close();
}

You'll check that the connection is actually open by examining its State property, and if it is, you close it.

Now it's time to do some real work against a database. First, you'll construct a SqlCommand object and pass the SqlCommand constructor command text and a reference to the SqlConnection object. Command text may be a SQL statement, DDL (Data Definition Language) command, or a stored procedure name. For example:

my $connstr =
  "server=localhost;uid=sa;pwd=;database=Northwind";
my $query = "select * from Customers";
my $conn = SqlConnection->new($connstr);
my $command = SqlCommand->new($query, $conn);
$conn->Open();

To run the command you should invoke one of the executing methods on the SqlCommand object:

ExecuteReader Method

This method returns the SqlDataReader class instance that allows iterating through the returned records in the forward-only manner. Hence, ExecuteReader is suitable for running SQL statements or stored procedures that perform SELECT queries. Listing 1 presents a PerlNET program that queries the Employees table and displays for each employee his/her ID, first name, last name, and country.

You compile the program with the following command-line referencing the System.Data.dll:

plc EmpQuery.pl –reference=System.Data.Dll

Lines 8-15 prepare the connection and command to execute. In line 17 you run the query and obtain the results storing them in the $reader variable. Lines 21-27 iterate through the records. You'll call the Read method of the SqlDataReader class to fetch first record. While there are records to fetch this method returns true. This check is similar to testing for EOF in the ADO recordsets. We'll then call the Read method again to fetch the next record. The SqlDataReader class exposes an indexer property that allows you to access column values using a column name string index. Since PerlNET supports square brackets syntax only for integer indexes, you'll have to call a getter method of an indexer (get_Item) explicitly (see lines 23-26). After you've iterated through all records you'll close the SqlDataReader object (lines 29-31). Then, you'll close the connection and exitprogram.

The output you get running the program is as follows (you may get different output depending on rows saved in the Employees table in your Northwind database):

             Employees
             ---------
1 Nancy Davolio from USA
2 Andrew Fuller from USA
3 Janet Leverling from USA
4 Margaret Peacock from USA
5 Steven Buchanan from UK
6 Michael Suyama from UK
7 Robert King from UK
8 Laura Callahan from USA
9 Anne Dodsworth from UK

Obviously, you can create more complex queries that will involve multiple tables. All you have to do is initialize the SqlCommand object with the appropriate valid SQL statement and then run the ExecuteReader to obtain the selected rows in the SqlDataReader class object.

ExecuteScalar Method

You should invoke the ExecuteScalar method when you know a-priori that your command will return a single value such as count of rows, sum of the column, etc. In case the query is multi-row or multi-column, the method will return the value from the first row and first column.

Suppose that you'd like to count the number of orders that are handled by employees from the UK. Here is the corresponding SQL statement that queries the Orders and Employees tables:

select count(OrderID) from Orders o,
                           Employees e
where o.EmployeeID = e.EmployeeID and
      e.Country = 'UK'

Obviously, the above query returns a single integer value. Hence, it makes sense to use the ExecuteScalar method in this case (see Listing 2 for the code).

The compilation command-line will be:

plc CountOrders.pl –reference=System.Data.Dll

In Line 19 you invoke the ExecuteScalar method, which returns an integer. In line 23 we print the result.

Here's the output from running the above program:

224 orders are handled by UK employees

ExecuteNonQuery Method

This method is suitable for situations when your SQL statement or stored procedure doesn't return any values. For example, you may use this method to execute INSERT, DELETE, or UPDATE SQL statements. To demonstrate ExecuteNonQuery in action here is a small program that provides text interface for adding new shipper to the Shippers table. Listing 3 presents the AddShipper program.

Here is the compilation command:

plc AddShipper.pl –reference=System.Data.Dll

The code asks the user to enter a company name and phone number in lines 8-11 and then constructs the corresponding INSERT statement (lines 12-14). The command execution occurs in line 19 after you've opened a connection. The Shippers table has the ShipperID field that represents the primary key and it generates automatically so that you don't have to supply a value for it.

Working in connected mode is simple and convenient. However, keeping the connection continuously open may affect the performance of the application and consume valuable system resources. Therefore, for complex operations on a database you may consider working in the disconnected mode, which is discussed next.

Disconnected Database Access

In previous examples you saw how to connect to the database, how to add, delete, modify, and query the data stored in. All the examples you've seen so far share a common pattern. The program opens a connection to the database, does what it needs to do with the data, and closes the connection. The connection is kept open all along the process. This may not be desirable in the following situations

The answer to this in ADO.Net is DataSets, which enable you get the result offline and in-memory, so the database connection need not kept opened all the time. Think of a DataSet as a container of collections. A commonly used collection is the DataTableCollection. The DataTableCollection is a collection of DataTable objects. You can access this collection via the Tables property of the DataSet object, which is the in-memory representation of a relational table. Apart from containing object collections for rows and columns, DataTable contains the DefaultView property, which is used to sort and filter the information in the corresponding DataTable.

To fill data into the DataTable in a DataSet, ADO.NET provides the DataAdapter object. The DataAdapter object is instantiated as

   my $myAdapter = SqlDataAdapter->new(
                      $sql, $conn);

Where $conn is the Connection string to the Data Source and $sql is a SQL query to fetch data. A given instance of DataSet, ($ds) is filled using the DataAdapter's Fill method.

   $myAdapter->Fill($ds, "TableName");

The following example shows how to iterate through the DataTable. Given a table name that is present in the database this program fetches all the data in that table and prints on the standard output.

The above program is compiled as follows

plc showdata.pl –reference=System.Data.Dll

Running ShowData.exe with Shippers as the parameter produces the following output.

======================
ShipperID CompanyName Phone
---------------------------------
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

Let's discuss the program step-by-step. Line 11 through 14 make sure that a command line argument is provided. Lines 16 and 17 specify the connection string to connect to the database. In this case, the program will connect to the NorthWind database in the localhost server. The Connection object is created in Line 20. Line 22 creates the SQLDataAdapter object by passing a SQL SELECT statement and the Connection object just created. Lines 25 and 27 create and populate an instance of the DataSet class. The Fill method accepts a second parameter, the table name, which is assigned to a DataTable object and assigned to the DataSet's Tables collection. The Fill method also opens the connection automatically if it was closed before filling the DataSet object. After filling the dataset it returns the connection's state to its original setting. By design, in somesense, DataAdapter and DataSet are disjointed. This allows you to fill a given DataSet with data from different data sources; the connection is assigned only to the DataAdapeter object and not the DataSet object directly, thus enabling disconnected databases.

Line 28 accesses the Table in the collection and successively gets all the column names by accessing the Columns collection of the DataTable objects in Lines 31 through 34. Line 36 gets the number of columns in the given table, by the Count property of the Columns collection. Lines 38 through 45 iterate through the Rows collections and accesses the value stored in each column for that row by referring the Column index to each Row object in the Rows collection.

Disconnected database access is crucial in the Web Applications world. In the rest of the article we'll see how to develop Web Applications and Web Services using PerlNET.

ASP.NET and PerlASPX

As part of the .NET environment Microsoft offers ASP.NET technology for developing Web Applications. ASP.NET represents a new fresh design compared to classic ASP. The programming model has significantly changed. It is possible to fully separate the presentation code from data processing code. ASP.NET comes with many built-in server controls that you can access programmatically on the server side.

Standard distribution of ASP.NET supports programming in three .NET languages:

The PerlASPX product by ActiveState adds the PERL language to the ASP.NET family.

The PerlASPX product by ActiveState adds the PERL language to the ASP.NET family.

Prior to installing PerlASPX, please, make sure you meet the following requirements:

For choosing the correct version of PerlASPX, please consult ActiveState (http://www.ActiveState.com)

ASP.NET Web Forms

Web Forms are stored on the Web server in the files with .aspx extensions. There you'll place HTML tags, server-side code blocks, ASP.NET server controls, etc.

Let's look at a simple Web Form that outputs the current time with the help of the localtimePERLfunction (Listing 5). Every time you reload the page in your browser, the time is updated.

Line 2 is the Page directive where you may specify different attributes of the page. In this example we've set the language that will be used for the page code—PERL. With the help of the <% ... %> tags we've injected server-side code to display current time in the HTML content (lines 11-15). As you can see, ASP.NET pages are completely compatible with standard HTML.

Now here's a more accurate way to design the WebTime page. We'll add a Label server control for displaying time and override the OnLoad method that handles the Load event of the Page class (see Listing 6). We'll enclose the server code by the <SCRIPT RUNAT="server"> . . . </SCRIPT> tags.

Inside the =for interface block we've declared the OnLoad method (lines 6-8) that our .aspx page inherits from the Page class and overrides it. We've provided the implementation for the OnLoad in lines 9-15. Note that the POD block must start from the first column of the line. Hence, we cannot indent =for interface and =cut. Line 22 of the code adds Label server control lblTime to the page. We'll access this control's Text property in the code of OnLoad and set it to the current time.

Managing Shippers Sample

To demonstrate various ASP.NET techniques combined with thePERLlanguage we've created a shippers management ASP.NET Web Form. We wrote it in the code-behind style, i.e., separating the presentation from the code. This Web application illustrates the following ASP.NET features:

<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://tempuri.org/">
two thousand two hundred forty two</string>

wsdl.exe /language:cs /out:SpellService.cs
http://localhost/codemag/webservices/
perlspell.asmx?wsdl

csc /target:library SpellService.cs

cspellclient.exe 234
==========
234 spelled as two hundred thirty four