|
download source code
Introduction
Data layers allow the abstraction of the SQL calls to a separate set of objects. The .netTiers template library
(using CodeSmith) will build a data layer for your .NET web application, including the data administration
website, the web service, base objects, etc. This article will start with a database with primary key and
foreign key relations and use .netTiers to create all the Create Read Update Delete (CRUD) stored
procedures and implement .netTiers as a data layer for a web site.
Required Software
This article will use Microsoft SQL Server 2005 as the database and Visual Studio 2005 as the
development environment.
The CodeSmith code generation tool is required to build the .netTiers library. While CodeSmith
ships the .netTiers templates with its product, you may want to grab the latest .netTiers
release here. .netTiers requires the Microsoft Enterprise
Library but you don't need to download that separately as .netTiers will include the version
used in its download.
CodeSmith and the .netTiers Templates
CodeSmith is a code generation tool. While you do need to have the application installed to
build the .netTiers library, you don't need to know CodeSmith template syntax. The .netTiers
template is easy to use and has a documentation site.
After installation of .netTiers, .netTiers will have a directory tree of templates to generate the code.
The Sample Database
This article uses a sample database that has a few tables with primary and foreign key relations
but no stored procedures. You can find the database.sql file as part of this
article's download. In order for .netTiers to create stored procedures and corresponding class
libraries, you should follow some naming standards provided by .netTiers. Specific documentation
for these rules can be found here. The sample
database in this article follows most of these rules. The two most important issues are that
each table should have a primary key and that the primary key naming should be something
other than "Id". Given a table for "Authors", and good primary key
column name is "AuthorId". The "Id" field is used extensively in
the .netTiers library and it's important not to conflict your column names with the
methods they use to access those column names. Prefixing the table name to "id"
is enough to allow .netTiers to find and use the column with a naming conflict.
Other columns whose names may be problematic are foreign keys and indexes. This
article's database has three tables with foreign key relationships.
Introduction to .netTiers
In order to use the .netTiers templates, double-click on the netTiers.cst file in the parent
directory of the .netTiers install. The template properties page will open.
.01 Getting Started Properties
The first property is to choose the datasource. The dot dot dot icon
 at the far right of the
field allows you to select a datasource. Select the PubsNetTiers database.
If the PubsNetTiers database isn't a choice, then the datasource manager
will allow you to create one for the PubsNetTiers database. The RootNameSpace
will default to the name selected for the datasource. Set the OutputDirectory to c:\nettiers\pubsnettiers.
The Output Directory
The output directory will be a top level directory holding several other directories.
While you are beginning to understand the .netTiers directories and files, it may be
best to keep the directory outside of source control and your website directory
structure. Once you are accustomed to .netTiers, you may choose to change the
OutputDirectory listed in the CodeSmith template (.cst) file to your website's
source tree. If you plan to use .netTiers as a standalone library, it may be
best to treat the output directory as a location outside of your current source
tree. This is similar to using the
Microsoft Enterpise Library
in that you have
the code and build the binaries but do not customize the code. However, if you
plan to customize the .netTiers layer, you may want to include the code directories
generated into your source tree.
At this point, set just a few properties and generate the library so you can see what
the library looks like. Set the following properties according to the table below.
If the property is not in the table (as most are not), don't change the value from
the .netTiers default. The default should create the web admin site and not
generate the web service.
.02 Framework Generation:
Execute SQL=false
IncludeComponentLayer=ServiceLayer
LaunchVisualStudio=true
ViewReport=true
|
You may want to check the SQL the first time you run the template. Most of the sample code in the docs for .netTiers assumes you have built the ComponentLayer.
|
.03 Namespaces:
BusinessLogicLayerNameSpace=Base
ComponentLayerNameSpace=Component
|
The namespaces for the specific .netTiers layers will help you understand their position in the layers.
|
.06b Website - Advanced:
UseWebAppProject=true
|
This will create a web app project file but you will still need to convert the admin files to get the designer.cs files.
|
.07 Crud - Advanced
ParseDBColDefaultVal=true
|
Make netTiers deal with identity columns.
|
.08 Stored Procedure - Advanced
ProcedurePrefix=netTiers_
|
This is the prefix for all CRUD stored procedures that .NetTiers creates - makes them easier to find.
|
Generate the library. If everything worked, the HTML report summary should display as well as
the Visual Studio solution. The following image is a view of the top level directory created
during the template generation.
If the directory and the report are not available, then an error occurred in the generation.
The most common errors are existing names for tables or columns that are conflicting with a
naming standard used by .netTiers.
The .netTiers Generated Solution
If Visual Studio isn't open with the solution yet, go ahead and open the pubsNetTiers.sln
file and build the solution. It should build all the projects in the proper order without any errors.
The first build is another place you may see some naming conflicts with your own database.
This would be table and column names, stored proc names, etc. While the error won't tell
you what you have to change, it should give you enough information to understand the conflict.
Prepwork to Use the Admin Website
You will need to perform the following actions every time you generate the Admin
site via the .netTiers template.
If you open the pubs.Website in Visual Studio, you will notice that there is only
one file (default.aspx). You will need to "show all files" for that project
in order to see the rest of the files. You will need to include the following
directories in the project: Admin, App_Themes, Bin, Common, Images, MasterPages.
You will also need to include the web.sitemap file.
Right click on the Admin directory in the solution explorer and
"Convert to web application". After the directory has been successfully converted
and each file has its designer.cs file, rebuild the solution now. Right-click to set the
pubs.Website project as the startup project. Find the default.aspx in the /Admin directory
and set it to the default start page. Make sure the pubs.Data.SqlClient is a reference for
the pubs.Website project. If it is not, right-click and choose Add Reference, then
select the Projects tab and find the pubs.Data.SqlClient library in the list.
Execute the SQL File
Before the admin site will work, the database must have the SQL stored procs that
.netTiers created and placed in the nettierspubs.SQL directory. Open the SQL Query
Analyzer (or whatever tool you use to execute T-SQL) and execute the procedures.sql
file in the pubsNetTiers.SQL directory.
A Look at the Admin Site
The Admin site's /Admin directory has a default.aspx that uses a sitemap based on
all the table names. The left column directory will allow you to choose any table and modify the data.
You can select any record to edit or insert a new record. If you select the Title's
table and add a new record, you will notice the insert includes a drop-down box where
you can select the publisher's name (via foreign key), or the author's name.
If you are starting your project with just the tables and their relationships,
then after generating your library with .netTiers, you now have all the stored
procedures to manage the tables as well as administrative web site to manage
the data from a web browser.
Admin Site Challenges
However, there are a few minor challenges of which you should be aware. One challenge
you should be aware of is that long textual data is not easily edited in the admin
pages as they are currently built. The field is a simple text box on one line.
Any data that extends beyond this textbox is difficult to write or edit in place.
Another challenge is malformed data on inserts or updates. For example, on the
Authors table, the Zip column has a check to make sure all 5 characters are numbers.
If you enter five characters such as 'asdfg' instead of a five-digit USA zipcode, the
insert will fail but the web admin page will not give any reason but will re-present
the insert page with all the fields blank. Since the Admin site doesn't redirect back
to a tables Select page, you won't immediately know that the insert failed.
Using the Admin Web Site for the Following Example Pages
The following select and insert pages are going to be created in the Admin web site.
This allows you to not have to create a new web site, add the pubsNetTiers libraries
as references, or modify the web.config.
Create a Page for Select
Add a web page to the pubsNetTiers.Website project titled AuthorsSelect.aspx.
Add a GridView and an ObjectDataSource. Configure the ObjectDataSource to use the
pubsNetTiers.Web.Data.AuthorsDataSource object found in the pubsNetTiers.Web library
and choose the Select method from the class as the Select method. Close the ObjectDataSource
configuration window. Configure the GridView columns to only show columns in the database
table and not the extra columns that the library added. These columns are for .netTiers to
keep tabs on various state issues of the objects. Remove all fields except those in the
database which are: AuthorId, FirstName, LastName, Phone, Address, City, State, Zip, Contract.
There is nothing to add to the code-behind file. Set this page as the Start page, build and run.
The following code shows the HTML view of the GridView and ObjectDataSource code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="AuthorId" DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="AuthorId" HeaderText="AuthorId" InsertVisible="False"
ReadOnly="True" SortExpression="AuthorId" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="Select"
TypeName="pubsNetTiers.Web.Data.AuthorsDataSource">
</asp:ObjectDataSource>
You should see a page such as
The pubsNetTiers.Web library has many objects and utility classes to help you use these objects in a web page.
Create a Page for Insert (Directly Accessing the Objects)
However, there may be times when you want to directly access the objects.
For this example, create a web page named AuthorsInsert.aspx.
Create textboxes for the three required fields of FirstName, LastName, and Phone Number.
Once the text is found and inserted in the Authors table, the page redirects
to the AuthorsSelect.aspx page.
The HTML is:
<asp:Label ID="Label1" runat="server" Text="FirstName"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="LastName"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
<asp:Label ID="Label3" runat="server" Text="Phone"></asp:Label>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" />
The code-behind for the click of the button is:
protected void Button1_Click(object sender, EventArgs e)
{
String lastName = TextBox2.Text;
String firstName = TextBox1.Text;
String phoneNumber = TextBox3.Text;
pubsNetTiers.Component.AuthorsService authorService = new pubsNetTiers.Component.AuthorsService();
pubsNetTiers.Base.Authors author = new pubsNetTiers.Base.Authors();
//author.AuthorId;
author.FirstName = firstName;
author.LastName = lastName;
author.Phone = phoneNumber;
if (authorService.Insert(author))
Response.Redirect("/AuthorsSelect.aspx");
}
The Component layer provides the Author Service. The Insert method on the AuthorService expects
the Base layer's Author object as the parameter. Since the Author table's id is an identity, we
don't need to pass the identity. If your primary key won't be created by default in the database,
you will need to set it in the Author object before calling the Insert method. If the database
will create the primary key on insert, you need to make sure that the .netTiers template setting
for determining default values is set. The Insert method returns a Boolean value indicating success.
Searching in Your Tables
.netTiers provides several ways to search your data. Each has its own positive and negative issues.
You may find yourself using more than one method.
| NetTiers.Component.ObjectService.Get methods |
Provided for all PK, FK, and Unique Indexes.
Pro: part of standard generation
Con: only these are provided, must be exact match
|
| NetTiers.Component.ObjectService.Find methods |
You provide where clause or SQL params for where clause
Pro: granular control
Con: looses abstraction of .netTiers layers
|
| NetTiers.Component.ObjectService.Custom Method |
Stored Proc is in database using naming conventions so .netTiers discovers it and provides method access to it.
Pro: granular control
Con: must abide by naming standards for discovery
|
| NetTiers.Base.T<List>.Find methods |
.netTiers object collections are either T<List> or V<List>. These generics have Find methods defined on them.
Pro: part of standard generation, can be loosely matched, allows you to continually refine collection
Con: performance issues over large collections
|
Summary
.netTiers templates are a great way to build your data layer. The templates will discover your
tables, create your stored procedures, find any custom stored procedures, and provide you with
a set of library objects that allow you to use your standard .NET controls to display your data.
Author Biography
Dina Fleet Berry is a .NET programmer and technical writer. She contributes to the Project
31A programming blog found at http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com/.
|
