This is the second article of a multipart series that will walk you through setting up a typical merchant storefront website. It is intended to give a very quick walkthrough using Visual Studio.Net and demonstrate how quickly a three tiered web application can be created. The second installment walks through the creation of the data tier and touches on the business tier.
With our last article, we created
four projects in our VS.NET solution. They
were a Common project for our shared datasets, a DataAccess
component class project for our DataAdapters, a BusinessRules class
project for our business logic, and a MomAndPop web forms application.
This time, we will add on to our DataAccess project and start on our
business logic.
Lets start by opening up ProductCommand in your DataAccess
project. Double click on the
filename in the [Solution Explorer]. This
will open up a visual display of the component class.
Right-click anywhere in the panel or on the file name and select [View
Code] to also open up the source code.
Weve created an OleDbDataAdapter last time which also created OleDbCommands for select, insert, delete, and update. We need a couple more commands for our web application however. Open up your [Toolbox] and navigate to the [Data] tag. Drag an [OleDbCommand] to the panel of the ProductCommand instantiating a new command object in class. Turn your attention to the [Properties] box with the newly created command highlighted. Rename our new command object to oleDbSelectByIdCommand. Under [Connection], find the existing OleDbConnection1 and select it. Now find the [CommandText] property and click the button to the right of the text area with the text on it. This button is usually used for calling wizards or in this case, a sql query designer. We could build our query using the designer but to save time, we are going to type in our query. Type the following in the SQL Panel (3rd panel down):
SELECT Products.* FROM Products WHERE (id = ?)
A dialog box will appearing asking Do you want to apply new parameter configuration? Click [Yes]. Go back to the [Properties] window, and click the wizard button under properties (next to the word (Collections)) to view the parameters collection. You will see that Visual Studio.NET automatically generated the id parameter for us. With the same process, create another command with the name oleDbSelectByColorCommand with the sql command text:
SELECT Products.* FROM Products WHERE (color = ?) ORDER BY title
Open the POTWCommand class in your DataAccess Project and create an OleDbCommand named oleDbSelectPOTWCommand. This sql query is a bit more complex than the others. It will join the POTW table with the Product table by id and return a Product dataset of the products that have their id number listed in the POTW table. Enter the following sql command text:
SELECT Products.* FROM POTW INNER JOIN Products ON POTW.id = Products.id ORDER BY Products.title
View ProductCommand.cs to see your handy work.
Expand the [Component Designer generated code] region to see your newly
generated code for the new OleDbCommands.
In order to save some time and extra code, we are going to add a couple reusable functions in our component class. We are going to add an update data source function which takes an updated dataset and applies the appropriate DataAdapter commands based on the updates on the dataset. This will save us time and (more importantly) efficiency while doing batch updates. Updates can just be applied to the dataset and done in one pooled transaction rather than many individual database connections and queries. The second function returns a dataset parameter filled from the oleDbSelectCommand when passed a DataSet and an int indicating the number of rows affected. Above the generated code inside the class declaration, cut and paste the following code:
{
this.ProductCmdUpdateDataSource(updatedDataSet);
return updatedDataSet;
}
private int ProductCmdUpdateDataSource (System.Data.DataSet updatedDataSet)
{
System.Data.DataSet DeletedRows;
System.Data.DataSet UpdatedRows;
System.Data.DataSet InsertedRows;
int RowsAffected = 0;
try
{
// Get all of the deleted rows and update the datastore
DeletedRows = updatedDataSet.GetChanges(System.Data.DataRowState.Deleted);
if (((DeletedRows) != (null)))
{
RowsAffected = this.oleDbDataAdapter1.Update(DeletedRows);
}
// Get all of the updated rows and update the datastore
UpdatedRows = updatedDataSet.GetChanges(System.Data.DataRowState.Modified);
if (((UpdatedRows) != (null)))
{
RowsAffected = ((RowsAffected) +
(this.oleDbDataAdapter1.Update(UpdatedRows)));
}
// Get all of the inserted rows and update the datastore
InsertedRows = updatedDataSet.GetChanges(System.Data.DataRowState.Added);
if (((InsertedRows) != (null)))
{
RowsAffected = ((RowsAffected) +
(this.oleDbDataAdapter1.Update(InsertedRows)));
}
return RowsAffected;
}
catch (System.Exception eUpdateException)
{
// TODO: Handle errors here
throw eUpdateException;
}
}
public int ProductFillDataset( System.Data.DataSet myDataset )
{
int rowsAffected;
this.oleDbConnection1.Open();
myDataset.EnforceConstraints = false;
rowsAffected = this.ProductCmdFillDataset( myDataset );
myDataset.EnforceConstraints = true;
this.oleDbConnection1.Close();
return rowsAffected;
}
private int ProductCmdFillDataset( System.Data.DataSet myDataset )
{
int RowsAffected;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
// Create an error handling block in case filldataset throws an exception
try
{
RowsAffected = this.oleDbDataAdapter1.Fill( myDataset,
"Products" );
}
catch( System.Exception eFillException)
{
throw eFillException;
}
return RowsAffected;
}
You will notice that there is a
private function that handles the exception handling and another public function
that your web application or business logic will call.
The Update Data Source functions can be used in our POTWCommand and
UserCommand classes. The
FillDataset functions can also be used for the other two classes but need to be
modified with the correct command names. I
will leave this as an exercise for the reader.
Save and build your DataAccess project. Your data tier is complete!
In our next article, we will start the business logic tier and start the
implementation of our user login system. Until
next time If you have any questions, comments or gripes, email [email protected].
Click Here for Part 1
