Stock Management System Case Study
To illustrate the concepts we presented in this chapter, we modified the Stock Management System application from the previous chapter. The case study resides in the StockMgrForm folder. The code for the user interface remained intact. We made changes to the Stock component, which implements now the IStock interface using ADO.NET classes. Here we present the code for the Stock constructor, where we fill the dataset with the items from the Stock table. Additionally, we initialize the commands for the data adapter used to propagate the changes back to the data base. You should change the server name in the connection string to your local server name.
sub Stock
{
my $this = shift;
# Change the server name accordingly
my $connstr =
"server=Lotus;uid=sa;pwd=;database=PerlNET";
my $conn = SqlConnection->new($connstr);
my $stockAdapter = SqlDataAdapter->new();
my $SelCmd = SqlCommand->new("select * from Stock",
$conn);
$stockAdapter->{SelectCommand} = $SelCmd;
$this->{f_dsItems} = DataSet->new();
$stockAdapter->Fill($this->{f_dsItems}, "Stock");
# Initialize Insert command
my $InsCmd = SqlCommand->new(q(insert into
Stock(ID, Name, Price, Quantity) values
(@ID, @Name, @Price, @Qty)), $conn);
#Initialize Parameters for Insert Command
my $prmID = SqlParameter->new(q(@ID),
enum("SqlDbType.NChar"), 5, "ID");
my $prmName = SqlParameter->new(q(@Name),
enum("SqlDbType.Char"), 20, "Name");
my $prmPrice = SqlParameter->new(q(@Price),
enum("SqlDbType.Decimal"), 8, "Price");
my $prmQty = SqlParameter->new(q(@Qty),
enum("SqlDbType.NChar"), 5, "Quantity");
my @params = ($prmID, $prmName, $prmPrice, $prmQty);
$InsCmd->{Parameters}->Add($_) for @params;
$stockAdapter->{InsertCommand} = $InsCmd;
# Initialize Delete command
my $DelCmd = SqlCommand->new(q(delete from Stock where
ID = @rmvID), $conn);
#Initialize Parameters for Delete Command
my $prmRmvID = SqlParameter->new(q(@rmvID),
enum("SqlDbType.NChar"), 5, "ID");
$prmRmvID->{SourceVersion} =
enum("DataRowVersion.Original");
$DelCmd->{Parameters}->Add($prmRmvID);
$stockAdapter->{DeleteCommand} = $DelCmd;
# Initialize Update command
my $UpdCmd = SqlCommand->new(q(update Stock
set Name=@newName, Price=@newPrice,
Quantity=@newQty
where ID=@oldID), $conn);
#Initialize Parameters for Update Command
my $prmOldID = SqlParameter->new(q(@oldID),
enum("SqlDbType.NChar"), 5, "ID");
my $prmNewName = SqlParameter->new(q(@newName),
enum("SqlDbType.Char"), 20, "Name");
my $prmNewPrice = SqlParameter->new(q(@newPrice),
enum("SqlDbType.Decimal"), 8, "Price");
my $prmNewQty = SqlParameter->new(q(@newQty),
enum("SqlDbType.NChar"), 5, "Quantity");
@params = ($prmOldID, $prmNewName, $prmNewPrice,
$prmNewQty);
$UpdCmd->{Parameters}->Add($_) for @params;
$stockAdapter->{UpdateCommand} = $UpdCmd;
# Initialize SqlCommands for Generating ID from StockID
# Table
$this->{f_cmdGetID} = SqlCommand->new("Select LastID
from StockID",$conn);
$this->{f_cmdIncID} = SqlCommand->new("Update StockID
set LastID=LastID+1",$conn);
$this->{f_adapter} = $stockAdapter;
}
As we use the same data adapter for Insert, Delete, and Update commands, we have to give different names to all the commands parameters, which means that we cannot use @id for both Insert and Update commands.
We update the data source once the user makes a change. Here is the code for the RemoveItem method, where we first mark the item for deleting in the dataset and then call the Update method:
sub RemoveItem
{
my($this, $id) = @_;
my $ds = $this->{f_dsItems};
my $t = $ds->{Tables}->get_Item("Stock");
foreach my $row(in $t->{Rows})
{
if ($row->get_Item("ID") == $id)
{
$row->Delete();
$this->{f_adapter}->Update($ds, "Stock");
return;
}
}
}
Some of the operations inside the Stock component we perform through the execute methods of the SqlCommand object. For example, to generate a new ID for an item, we first call the ExecuteScalar method of the f_cmdGetID command object field we initialized in the constructor and then we invoke the ExecuteNonQuery method of the f_cmdIncID command object to increment the ID in the StockID database table.
sub GenerateID
{
my $this = shift;
# Obtain ID
$this->{f_cmdGetID}->{Connection}->Open();
my $id = int($this->{f_cmdGetID}->ExecuteScalar());
$this->{f_cmdGetID}->{Connection}->Close();
# Increment LastID in the table
$this->{f_cmdIncID}->{Connection}->Open();
$this->{f_cmdIncID}->ExecuteNonQuery();
$this->{f_cmdIncID}->{Connection}->Close();
return $id;
}
Figure 15-3 shows the main window of the Stock Management System application with the data from the Stock table.
Figure 15-3. Main window of the Stock Management System.
