Author Advanced .NET Applications in Perl : Page 2

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.

Previous Page

Next Page