ADO.NET Connection

In order to obtain access to information stored in a database, we have to connect to our data source. ADO.NET provides special Connection classes for different data providers. These classes encapsulate all the required functionality to establish connection to databases. You just have to make sure you choose the relevant class and supply it with correct connection parameters.

Whenever we need to connect to our database, the SqlConnection class will serve this purpose. We instantiate this class in our PerlNET programs as we do every other .NET class. We pass to its constructor the connection string. This string contains all the required information for connecting to a database server and/or authentication. The connection string we use is

"server=Lotus;uid=sa;pwd=;database=PerlNET"

As you can see, we provide the server name, user to connect with, user's password (in our case it is blank), and database name (we have created PerlNET database on the SQL Server). You should edit the string so that it reflects your local settings, like substituting your server's name instead of Lotus. Here is the code fragment of creating a connection object:

use namespace "System.Data";
use namespace "System.Data.SqlClient";
. . .
my $connstr = "server=Lotus;uid=sa;pwd=;database=PerlNET";
my $conn = SqlConnection->new($connstr);

After we create the SqlConnection instance, we have to instruct the .NET Framework to actually connect to the database or, in other words, to open a connection. We do it by calling the Open method on the SqlConnection object.

$conn->Open();

If there were no network or other problems, the above line establishes for us a connection to the database. We may now utilize it to do something useful and important (since all our programs are such), such as update data, insert new data, and run various queries, with the information inside the database.

The next sections describe these operations. After we finish all our operations on the data, we close the connection and free this valuable resource:

use PerlNET qw (enum);
. . .
if ($conn->{State} == enum("ConnectionState.Open"))
{
   $conn->Close();
}

Pay attention, that before closing our connection, we check that it is still opened by examining the State property.