|
download source code
Introduction
This is the second article in the .netTiers series. The
first article
included getting started with the template settings, generating the libraries,
a first look at the admin site, a page selecting data out of the database and a
page inserting data into the database. This article will include .netTiers
discovery of custom stored procedures, SourceTable configuration, and adding
custom code to the .netTiers library.
Required Software
Please refer to the first article in this series for required software to implement
this article's demonstrations as well as where to download the software.
Visual Studio pubsNetTiers Solution
The following examples assume that you have set .netTiers properties and generated
the solution as shown in the first article of this series. The following examples
pick up where the last article ended - all example files are places in the Admin
project so that the References and Web.Config are correct.
Create a Search Page Using a Custom Stored Procedure
First, create a stored procedure named pubs_Authors_FindByPartial. The T-SQL code is:
create PROCEDURE [dbo].[pubs_Authors_FindByPartial]
@SearchTerm nvarchar(255)
AS
select * from Authors
where LastName like '%' + @SearchTerm + '%'
or FirstName like '%' + @SearchTerm + '%'
or Phone like '%' + @SearchTerm + '%'
or Address like '%' + @SearchTerm + '%'
or City like '%' + @SearchTerm + '%'
or State like '%' + @SearchTerm + '%'
or Zip like '%' + @SearchTerm + '%'
The custom stored procedure should return all rows in order for .netTiers to treat it
as any of its own stored procedures.
Change the .netTiers template settings to find the custom stored procedure based on
the naming standards:
.07 CRUD - Advanced:
CustomProcedureStartsWith=pubs_{0}_
Include Customs=true
|
You have to turn on custom stored procs to be included and you also
have to set the naming convention used to determine which procs are
the stored procs.
|
By setting the naming standard to pubs_{0}_, any stored procedure that begins
with 'pubs' and has the second term as the table name followed by an underscore
will be included. This holds true as long as you don't have the rest of the
procedure name exactly like the .netTiers stored procedure names. For example,
.netTiers provides a stored procedure named pubsNetTiers_Authors_Find and you
have your stored procedure named pubs_Authors_Find, .netTiers will fail at one
of the following places: 1) template generation, 2) library build in visual
studio, 3) library execution. However, you don't need to stray too far from
the naming standards netTiers uses so the stored procedure of
pubs_Authors_FindByPartial will work and produce a method named
FindByPartial on the AuthorsService provider.
Now, regenerate the library from the .netTiers template.
In order to verify that the custom stored procedure was discovered and a method
built for it, open the pubsNetTiers.Component project. Open the
AuthorsServiceBase.generatedCore.cs. In the AuthorsServiceBase class, look for
the Custom Methods region. You should find the following code:
#region Custom Methods
#region pubs_Authors_FindByPartial
/// <summary>
/// This method wrap the 'pubs_Authors_FindByPartial' stored procedure.
/// </summary>
/// <param name="searchTerm"> A <c>System.String</c> instance.</param>
/// <remark>This method is generate from a stored procedure.</remark>
/// <returns>A <see cref="TList{Authors}"/> instance.</returns>
public virtual TList<Authors> FindByPartial(System.String searchTerm)
{
return FindByPartial( searchTerm, 0, defaultMaxRecords );
}
/// <summary>
/// This method wrap the 'pubs_Authors_FindByPartial' stored procedure.
/// </summary>
/// <param name="searchTerm"> A <c>System.String</c> instance.</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <remark>This method is generate from a stored procedure.</remark>
/// <returns>A <see cref="TList{Authors}"/> instance.</returns>
public virtual TList<Authors> FindByPartial( System.String searchTerm, int start, int pageLength)
{
// throws security exception if not authorized
SecurityContext.IsAuthorized("FindByPartial");
TList<Authors> result = null;
TransactionManager transactionManager = null;
try
{
bool isBorrowedTransaction = ConnectionScope.Current.HasTransaction;
//since this is a read operation, don't create a tran by default, only use tran if provided to us for custom isolation level
transactionManager = ConnectionScope.ValidateOrCreateTransaction(noTranByDefault);
NetTiersProvider dataProvider = ConnectionScope.Current.DataProvider;
//Call Custom Procedure from Repository
result = dataProvider.AuthorsProvider.FindByPartial(transactionManager, start, pageLength , searchTerm);
}
catch (Exception exc)
{
//if open, rollback
if (transactionManager != null && transactionManager.IsOpen)
transactionManager.Rollback();
//Handle exception based on policy
if (DomainUtil.HandleException(exc, layerExceptionPolicy))
throw;
}
return result;
}
#endregion
Now that you are sure the custom method is available, open the solution file and
rebuild everything. If you plan to use the Admin site, you will need to do all
the same previous prep work from the first article in this series to make the
admin site work. The generation won't delete the two files in the first article
for select and insert so you can add them back to the admin project.
Create a web page in the admin site called AuthorSearch.aspx. This page will
search across all Author's to find any matches to a search term. Add a label
for the search term, a textbox for the search, and a button to perform the search.
Add an ObjectDataSoure using the pubsNetTiers.Component.AuthorsService with a select
method of FindByPartial. Add a GridView tied to the ObjectDataSource. Remove all
columns in the GridView except the columns found in the database table. Add a
click event handler in the code-behind for the page to handle the search. Tie
the objectdatasource's selection to the textbox control for the search term.
The AuthorSearch.aspx should look like:
<asp:Label ID="Label1" runat="server" Text="Term to find"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Find" OnClick="Button1_Click" />
<p />
<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" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
<asp:CheckBoxField DataField="Contract" HeaderText="Contract" SortExpression="Contract" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
SelectMethod="FindByPartial" TypeName="pubsNetTiers.Component.AuthorsService">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="searchTerm" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
The code-behind file should look like:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace pubsNetTiers.Website
{
public partial class AuthorsFind : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataBind();
}
}
}
A search for '415' returns several rows:
SourceTables Configuration
If you are using other database-driven .Net features such as Asp.Net Membership,
SQL-based Sitemaps or have any tables using SQL-based caching for change notification,
you will need to consider how you use the Source tables setting.
All membership tables are prefixed with 'aspnet_' by default. If you want .netTiers
to generate stored procedures and .netTiers library methods for the Membership tables,
do not change the current setting of the .netTiers templates. If you do not
want .netTiers to generate any stored procedures and .net library methods, make sure to
exclude these tables from the table list in the
.01b section - filter by individual objects
property of Source tables.
Because SQL-based SiteMaps generally don't change often, they are cached. When
the sitemap does change, you need a change notification to update the cache.
Since the table named AspNet_SqlCacheTablesForChangeNotification created by
the aspnet_regsql.exe application is stored in the database
but controlled by SQL itself, you shouldn't need .NetTiers to access the
table so make sure to exclude the table.
These are just two examples of TableSource exclusions from .netTiers library
generation. You should review all tables in your database to see which should
be discovered by .netTiers.
Adding Code to the .netTiers-generated Library
The base class of Authors.cs found in the pubsNetTiers.Base Visual Studio project is
where we need to add our custom code. Once this file is generated by .netTiers, it
will not be overwritten with subsequent generations. The following example will
show two different types of custom code. The first is a simple Boolean method to
check if an author is retired. Since this is just an example method, it always
returns false. While this particular method isn't extremely useful, it will
demonstrate how and where to add custom code. You could use a similar method
to do just about anything with the object itself.
The second is a validation rule. .netTiers adds validation rules to the library
to determine if the data is following the constraints defined in the database.
An example of this in the current project is in the
pubsNetTiers.Base.AuthorsBase.generated.cs file in the validation region. An
example of the validation rules created by .netTiers for the Author tables is
the validation for the LastName column which can not be null and is defined
as varchar(40).
ValidationRules.AddRule(
Validation.CommonRules.NotNull,
new Validation.ValidationRuleArgs("LastName", "Last Name"));
ValidationRules.AddRule(
Validation.CommonRules.StringMaxLength,
new Validation.CommonRules.MaxLengthRuleArgs("LastName", "Last Name", 40));
Open the pubsNetTiers.Base.Authors.cs file and add the following code.
#region Custom Code
public bool IsRetired()
{
return false;
}
protected override void AddValidationRules()
{
base.AddValidationRules();
ValidationRules.AddRule(AddressRuleCheck,("AddessCheck"));
}
public bool AddressRuleCheck(object target, Validation.ValidationRuleArgs e)
{
if (this.Address.Length == 0)
{
e.Description = "The address must not be null.";
return false;
}
else
return true;
}
#endregion
The validation rule is added in the AddValidationRules method.
The Rule is called AddressCheck and is defined as a check to
make sure the Address has a length greater than zero. The database allows a
null field however this rule will override that database column definition.
Find the authors.cs file in the file browser and note the timestamp. Now
regenerate the libraries and verify that the authors.cs file has the
timestamp prior to the generation while the AuthorsBase.generated.cs
file has a more recent timestamp.
Fix the admin Visual Studio project as noted in the first article, rebuild
all the projects, and then add a file to the admin website called
AuthorsCustomCode.aspx. Add a GridView to view the
authors, as well as a textbox for the AuthorId, and two buttons: one
for the validation rule, and other for the IsRetired method.
The AuthorsCustomCode.aspx code to add is:
<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>
<p />
AuthorId: <asp:TextBox ID="textboxAuthorId" runat=server Text="1"></asp:TextBox><br />
<asp:Button ID="buttonValidationRule" runat="server" Text="ValidationRule" OnClick="buttonValidationRule_Click" /><br />
<asp:Button ID="buttonIsRetired" runat="server" Text="IsRetired" OnClick="buttonIsRetired_Click" />
<p />
<asp:Label ID="label1" runat=server></asp:Label>
The code-behind file contains a click-event for each button.The code gets the
AuthorId from the textbox, creates an Author object.
For the Validation, the ValidationRule click event calls the .Validate() method
which calls into the custom validation rule just added to the Authors.cs file.
Then the IsValid() method is called to see if the validation rule threw an error.
The error is posted to the label.
For the IsRetired() method, it is called after the Author object is successfully created.
protected void buttonValidationRule_Click(object sender, EventArgs e)
{
int authorId = Convert.ToInt32(this.textboxAuthorId.Text);
pubsNetTiers.Component.AuthorsService authorService = new pubsNetTiers.Component.AuthorsService();
pubsNetTiers.Base.Authors author = authorService.GetByAuthorId(authorId);
author.Validate();
if (author.IsValid)
label1.Text = author.FirstName + " " + author.LastName + " is a valid author object.";
else
label1.Text = author.FirstName + " " + author.LastName + " is NOT a valid author object. " + author.Error;
}
protected void buttonIsRetired_Click(object sender, EventArgs e)
{
int authorId = Convert.ToInt32(this.textboxAuthorId.Text);
pubsNetTiers.Component.AuthorsService authorService = new pubsNetTiers.Component.AuthorsService();
pubsNetTiers.Base.Authors author = authorService.GetByAuthorId(authorId);
author.IsRetired();
if (author.IsRetired())
label1.Text = author.FirstName + " " + author.LastName + " is retired.";
else
label1.Text = author.FirstName + " " + author.LastName + " is NOT retired.";
}
In the following image, an author with a NULL address has been selected via the
AuthorId in the textbox and the ValidationRule button has been clicked.
The resulting error can be seen the label below the buttons.
Summary
This article in the .netTiers article series showed how to discover a custom SQL
stored procedure, configure the SourceTables properties, and add custom code to
the base library. These are just a few of the way to use .netTiers to gain huge
time-savings in your data layer.
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/.
|
