Author Advanced .NET Applications in Perl

n part 1 of this series you learned how PERL has been incorporated into the Visual Studio .NET family. This article explores its membership in more detail. PERL and its relation to ADO.NET and Web services are discussed in depth here.

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 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.

ASP.NET pages are completely compatible with standard HTML.

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 (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:

Opening connection to data source
Running commands against the data source
Closing the connection

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
ExecuteScalar
ExecuteNonQuery

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 exit the program.

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 time to process the data is considerably longer. When you’re using

DataReader, extended processing time will tie up the database connection.

A batch operation is desired. For example, you would like to query all the Shippers information, present it for users to change, and update all the changes in one shot.
You would like to take the result set of a query offline, and possibly work on it in-memory. For example, a presentation of a result set where the user can sort on different columns interactively.
Manipulation of data from multiple sources is desired.
You would like to pass the result set between architectural layers, say to a remote client as a managed object or to a Web Service as a serialized document.

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 (see Listing 4):

  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 some sense, 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:

C#
VB.NET
Jscript.NET

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:

.NET redistributables
PDK 4.0 or better
IIS Web server

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 localtime PERL function (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 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 the PERL language, 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:

Code-behind style
Using Web server controls
Data access and Data binding
Handling server control events

Figure 1 shows the Web Form in Internet Explorer (we added the last two shippers using the AddShipper program that we presented in a previous part of this article).

Figure 1: The shippers.aspx Web form in Internet Explorer.

Figure 2: The definition page for the Web service. SOAP information is also provided.

Let’s see the .aspx page code that is responsible for presentation layer (Listing 7).

The Page directive (lines 2-3) specifies the name of the code behind file (SRC attribute) and the name of the class from which our page inherits (Inherits attribute). We’ve enclosed most of the content with the

tags (lines 11 and 65). Since many server controls such as Button may appear only inside these tags. Lines 12-59 define a data-bound Repeater control and templates for it. The data binding occurs in the code-behind file. In the Web Form we extract the shippers’ data calling the DataBinder.Eval method and display it inside the table using a special data binding syntax (<%# ... %> tags). For example, lines 38-39 display the company name of the shipper.

For each row of shippers we’ll place a Button server control to remove the shipper from the database. Instead of the popular Click event we handle the Command event for these buttons. We bind the CommandArgument property of the Button control to the ShipperID (lines 48-50). This way we’ll know which button caused the Command event and what row to delete from the database. We assigned the RemoveShipper method to handle the event (line 52).

Line 64 adds another Button server control. We don’t handle any event on it. However, this control will cause post-back to the Web Form and this way we’ll refresh the data displayed.

Code-Behind File for Shippers Web Form
The corresponding code-behind file (Listings 8-10) defines the ShippersPage class that is the base class for our Web Form. This class defines the business logic for the application. It accesses the data layer and queries or updates the Shippers table from the Northwind database. Additionally, this class includes the data binding code. Another role that we assigned to the ShippersPage class is events handling. Our sample demonstrates working with the Command event.

We divided the code of the code-behind file into three listings to help you navigate through it.

Listing 8 presents the main declarations of the ShippersPage class. We define the interface of the class (lines 11-21) specifying that we inherit from the Page class (line 12). Additionally, we implement the overridden OnLoad method where we invoke the BindControls method (line 27) that binds our Repeater control to the data from the Shippers table. Listing 9 shows the data binding code.

The BindControls method sets the DataSource property of the Repeater control to the DataTable object that is returned by CreateDataSource (lines 5-6). In the next line we call the DataBind method of the Repeater to bring the data into the container (line 7).

The CreateDataSource method performs simple data access in disconnected mode, fills the DataSet, and returns the DataTable object with the shippers’ data.

Finally, Listing 10 shows the Command event handler?the RemoveShipper method.

To remove the shipper from the database we construct DELETE SQL statement bases on the CommandArgument property of the CommandEventArgs class that holds an id of a shipper to delete. We work in the connected mode and invoke the ExecuteNonQuery method (line 13). Some shippers may be referenced by the other tables (like Orders) and they cannot be deleted. We handle the exception with the eval block (lines 12-14). In case removing the shipper wasn’t successful, we catch an exception and display the corresponding message in the lblMsg label (lines 15-19).

Now that we’ve demonstrated the power of PerlASPX in creating ASP.NET Web Forms, we’ll show you how this wonderful product may help you in mastering ASP.NET Web services.

ASP.NET Web Services
Web services enable two different applications to integrate with each other using XML and SOAP (Simple Object Access Protocol). This integration becomes language and platform agnostic by nature. .NET enables you to write Web services just adding a WebMethod CLR Attribute that exposes that method as a Web service. In a quick example, we’ll see how to write a Web services using PerlNET.

The above program exposes the Spell assembly that we’ve created in the last article. As in any other .NET language, it becomes easy to write Web services in PerlNET just by adding the [System.web.Services.WebMethod] tag above the method that we choose to expose as a Web service.

When a browser hits the PerlSpell.asmx page (see Listing 11), ASP.NET generates an information page on the Service (see Figure 2). When a user gives an integer value and presses the Invoke Button, the application calls the Web service Method spellNumber and the result is returned in an XML format.

The XML formatted output for the number 2242, the PerlSpell.asmx returns,

     two thousand two hundred forty two

Web Service Client
Consuming a Web service uses the .NET wsdl.exe tool that, given the URL of the Web service, creates a proxy class in a specified language. The current version of WSDL generates the proxy classes only in VB, Jscript, or C#. In order to consume the Spell Web service we have to generate the proxy class, say in C#, then compile it to produce a library assembly. First, we’ll generate the proxy class as

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

Next we’ll compile the resulting SpellService.CS into a DLL and create SpellService.dll

  csc /target:library SpellService.cs

The following code shows a PerlNET Console program that consumes the Spell Web service.

Compiling the code in Listing 12 along with reference option value for SpellService.Dll produces the CspellClient.exe. The following code snippet shows a sample result.

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

Once we have the proxy class in a .NET assembly it becomes pretty easy to develop any type of program to consume the Web service. The code in Listing 13 shows a Web page consuming the Spell Web service.

Share the Post:

Author Advanced .NET Applications in Perl

Author Advanced .NET Applications in Perl

The Best Mechanical Keyboards For Programmers: Where To Find Them

The Digital Panopticon: Is Big Brother Always Watching Us Online?

Embracing Change: How AI Is Revolutionizing the Developer’s Role

The Benefits of Using XDR Solutions

How AI is Revolutionizing Fraud Detection

Companies Leading AI Innovation in 2023

Step-by-Step Guide to Properly Copyright Your Website

Fivetran Pricing Explained

Kubernetes Logging: What You Need to Know

Why Is Ransomware Such a Major Threat?

Tools You Need to Make a Data Dictionary

10 Software Development Tips to Get Early Funding for your Startup