|
Introduction
In my previous article,
Paging in SQL Server 2005, I demonstrated how to support paging the results of a SQL query inside
of a stored procedure. In this two-part article, I will tie that logic to an ASP.NET page with
a GridView and DetailsView to demonstrate an efficient way to incorporate paging and updating
through an ObjectDataSource. Along the way we will use the Class Diagram tool and the Web
Application Project template (added to Visual Studio 2005 by
Service Pack 1),
although you are free to use methods you are more comfortable with.
Background
Visual Studio 2005 introduced a number of tools that allow web developers to create dynamic
web pages more easily than ever. The GridView encapsulates most of the functionality needed
for displaying and editing tabular data efficiently, and can bind to several types of data
sources. The DetailsView is a sibling to the GridView, supporting most of the same
functionality but displaying a single record at a time. While these controls can bind
to databases through simple DataSets and SqlDataSources, the ObjectDataSource control
provides a more clearly defined, more sophisticated -- but potentially more complicated
-- connection. For developers looking to model their SQL data within the application
through custom classes, the ObjectDataSource allows them to bind their strongly-typed
classes to user controls and add a discrete Business Logic Layer to ensure user interactions
follow defined rules of behavior.
The Database Layer
The first order of business is to create a database to work with; the examples below were
created in a database called DALdemo, but you can change the connection string to whatever
name you choose. Once you have done this, the following SQL statement will create a simple
table to work with:
CREATE TABLE [dbo].[Person]
(
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Height] [decimal](5, 2) NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
To populate the table with some preliminary test data, execute the following SQL statements:
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Frank', 'Edward', 'Holiday', 62.38)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Nancy', 'Estelle', 'Walkafeller', 52.81)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Stewart', 'R.', 'Garvey', 61)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Jeff', 'A.', 'Walker', 66.43)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Walter', 'Albert', 'Johnson', 65.66)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Barbara', 'Connie', 'Jones', 65.38)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('William', '', 'Smith', 59.68)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Gillian', 'Kay', 'Krissinger', 62.43)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Sam', '', 'Stuart', 65.67)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Richard', 'G.', 'Harding', 63.98)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Laura', 'Edward', 'Kinsley', 70.56)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Frank', 'Quincy', 'George', 67.31)
INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Frank', 'P.', 'Williams', 62.99)
We will use the following stored procedure to query this table, which incorporates SQL paging logic and
uses a ReturnValue parameter to pass back the total number of records.
CREATE PROCEDURE [dbo].[utilPAGE]
@datasrc nvarchar(200)
,@orderBy nvarchar(200)
,@startPage int = 1
,@pageSize int = NULL
,@fieldlist nvarchar(200) = '*'
,@filter nvarchar(200) = ''
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
,@recct int -- total # of records
SET @recct = -1
IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = ' SELECT ' + @fieldlist +
' FROM ' + @datasrc +
' WHERE ' + @filter +
' ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = ' SELECT @recct = COUNT(*)
FROM ' + @datasrc +
' WHERE ' + @filter
EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
DECLARE
@lbound int,
@ubound int
IF @startPage < 1 SET @startPage = 1
IF @pageSize < 0 SET @pageSize = @pageSize * -1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@startPage - 1) * @pageSize)
SET @ubound = @lbound + @pageSize + 1
IF @lbound >= @recct BEGIN
SET @ubound = @recct + 1
SET @lbound = @ubound - (@pageSize + 1) -- return last page of records
END
SET @STMT = ' SELECT ' + @fieldlist +
' FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
FROM ' + @datasrc +
' WHERE ' + @filter +
' ) AS tbl
WHERE row > ' + CONVERT(varchar(9), @lbound) +
' AND row < ' + CONVERT(varchar(9), @ubound) + ' '
EXEC (@STMT) -- return requested records
END
RETURN @recct
We will use the PersonSET stored procedure to handle updates to the Person table. PersonSET handles both inserts and updates, with items being inserted if the @ID parameter is less than one:
CREATE PROCEDURE [dbo].[PersonSET]
(
@ID int = -1
,@FirstName nvarchar(50)
,@MiddleName nvarchar(50)
,@LastName nvarchar(50)
,@Height decimal(5, 2)
)
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
IF @ID > 0 BEGIN
UPDATE Person
SET FirstName = @FirstName,
MiddleName = @MiddleName,
LastName = @LastName,
Height = @Height
WHERE PersonID = @ID;
END ELSE BEGIN
INSERT INTO Person (FirstName, MiddleName, LastName, Height)
VALUES (@FirstName, @MiddleName, @LastName, @Height);
SET @ID = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
RETURN @ID
END TRY
BEGIN CATCH
DECLARE
@ERNUM int
SELECT @ERNUM = ERROR_NUMBER()
ROLLBACK TRANSACTION
IF @ERNUM > 0 SET @ERNUM = -@ERNUM
RETURN @ERNUM
END CATCH
PersonDELETE removes one record at a time from Person, based on the ID field:
CREATE PROCEDURE PersonDELETE
(
@ID int = 5
)
AS
SET NOCOUNT ON
DELETE FROM Person
WHERE PersonID = @ID
The Data Object Layer
The next step is to create a new C# ASP.NET Web Application called WebApp1
in Visual Studio. As shown in Illustration 1, enable the option
Create directory for solution and set the Solution Name to 15Seconds.
This will allow us to work with multiple projects simultaneously.
Illustration 1: Creating the Web Project and Solution
To create the second project, select File | New | Project and create a new C# Class
library called Library1. Be sure to change the default setting from
Create new Solution to Add to Solution, as shown in Illustration 2.
Illustration 2: Creating the class project within the solution
Feel free to close and delete the Class1.cs file created automatically by Visual Studio, as we will
not be using it. To create the class we do need, right-click on the Library1 project in the Solution
Explorer, select Add | Class, and create a class named PersonEntity.cs.
The quickest way to fill in the class is with the Class Editor; in the Solution Explorer, right-click on
PersonEntity.cs and select View Class Diagram.
The class needs internal data members for holding values, and we will follow the common
practice of prefixing an underscore to their names to avoid confusion later. To create
these, click on the <add field> text, type _ID,
and press tab. Set Type to be int, and set modifier to private.
Add the next three fields, _FirstName, _MiddleName, and
_LastName, all with Type set to string. Lastly, add field
_Height with Type set to double.
The class needs accessor (set/get) methods so other classes can modify these internal values.
To create these, click on the <add property> text, type ID, and press tab.
Set Type to be int, and set modifier to public.
Add the next three properties, called FirstName, MiddleName,
and LastName, all with Type set to string. Lastly, add property
Height with Type set to double.
At this point, your class diagram should resemble Illustration 3.
Illustration 3: Creating the PersonEntity skeleton with the Class Diagram tool
Right-click on ClassDiagram1.cd and select Rename; change the name to
diaPersonEntity.cd. Click on the PersonEntity.cs* tab to see what has
been happening in the background. The fields we added will show up as private data members:
private int _ID;
private string _FirstName;
private string _MiddleName;
private string _LastName;
private double _Height;
The properties we added show up as public get/set methods. The ID property is:
public int ID
{
get
{
throw new System.NotImplementedException();
}
set
{
}
}
We need to wire the fields and properties together, so that when someone refers to a class property, the relevant field is used. Change the ID property to:
public int ID
{
get
{
return _ID;
}
set
{
_ID = value;
}
}
Making private data members with public get/set methods probably seems like a lot of extra work, but it is an important part of good design that prevents subtle bugs and supports additional functionality. If a class' internal data members are exposed, other classes may inadvertently modify the internal values without trying. Also, set/get methods allow us to ensure the values used meet any requirements we might have. If, for example, we want to ensure that _ID can only be set to values greater than zero, we can add code in the set method to ensure this:
public int ID
{
get
{
return _ID;
}
set
{
if(value > 0)
{
_ID = value;
}
else
{
throw new Exception("ID must be greater than zero");
}
}
}
Alternatively, if we want the internal values to return an empty string when they are set to null,
we can change each of the get methods for string properties to use C#'s ?? operator. This is
similar to SQL Server's ISNULL() function: if the first value specified is NULL, the second
value specified is used instead. This can be used as shown below:
public string FirstName
{
get
{
return _FirstName ?? "";
}
set
{
_FirstName = value;
}
}
Note that the above code isn't strictly necessary for this project; it is simply an
example of things you can do if you want strict validation or other behavior within
your custom class (which is the primary benefit of making custom classes).
If you still aren't sold on the idea, see
http://msdn2.microsoft.com/en-us/library/65zdfbdt(VS.71).aspx
for a more complete explanation of the benefits.
Since we want to access this class from another project, be sure to change the class declaration to:
namespace Library1
{
public class PersonEntity
{
At its simplest, the PersonEntity.cs class should resemble:
using System;
namespace Library1
{
public class PersonEntity
{
private int _ID;
private string _FirstName;
private string _MiddleName;
private string _LastName;
public double _Height;
public int ID
{
get
{
return _ID;
}
set
{
_ID = value;
}
}
public string FirstName
{
get
{
return _FirstName;
}
set
{
_FirstName = value;
}
}
public string MiddleName
{
get
{
return _MiddleName ?? "";
}
set
{
_MiddleName = value;
}
}
public string LastName
{
get
{
return _LastName;
}
set
{
_LastName = value;
}
}
public double Height
{
get
{
return _Height;
}
set
{
_Height = value;
}
}
}
}
The Data Access Layer
Continuing to build from the back-end data store towards the user interface, we next need to create the Data Access Layer. The Data Access Layer is a class where you define the connection between an ObjectDataSource, a custom class (in our case, PersonEntity) and the data store for instances of this class (in our case, the Person table in SQL). After you define the relevant methods in the DAL class, you will configure the ObjectDataSource object to use the correct method for each necessary function, and can then set the ASP.NET elements that will use them.
Again, this can seem like a lot of overhead, but the extra work helps to make future maintenance easier. Instead of having specific SQL information embedded in each web page, you will define it once and be able to use it from any page that needs it. When you inevitably need to change your SQL table, you can update the class and DAL instead of editing multiple copies of the same queries throughout your web site.
To prepare the web application, right-click on WebApp1 in Solution Explorer and select
Set As StartUp Project. Next, right-click on References within the WebApp1
project and select Add Reference. Click on the Projects tab and
double-click Library1 as a new reference.
Rather than hard-coding the database connection string into our DAL class, we will store it in the web.config, where it can be modified as needed. Note that in a production environment, you should encrypt the database connection string for better security. To add your connection string, double-click the web.config file in the WebApp1 project. Modify it to have the following lines, replacing the user id and password with valid credentials and setting the data source and initial catalog with the host and database name you are using, respectively:
<connectionStrings>
<add name="15seconds"
connectionString="user id=demo; pwd=demopass; data source=localhost; initial catalog=15SecondsDAL;"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
To create the Data Access Layer class, right-click in the WebApp1 class and select
Add | Add ASP.NET Folder | App_Data (this is the standard location for DAL classes).
Right-click on the App_Data folder and select Add | Class and set the
name to PersonDAL.cs.
The DAL class must support four standard SQL functions: Select, Insert, Update, and Delete. However, the PersonSET stored procedure actually handles inserts and updates automatically, so we can use the same DAL method for both of these functions. In addition to these functions, the DAL class must return the total number of instances available (in our case, the number of records in the Person table). We will store the record count value returned from the utilPAGE stored procedure, so that when a bound data control needs the total number of records for its paging interface, the DAL will return that value instead of querying the database again.
At run-time, the ObjectDataSource settings will determine which methods to call from your DAL class for select, insert, update, delete, and count, and what information is passed into those methods. We will need to ensure that the ObjectDataSource configuration matches the DAL methods. In fact, enabling and disabling features such as sorting and paging support within the ObjectDataSource will affect which methods are called, so you may need to create overloaded versions of your DAL methods depending on the combinations of features you intend to support. In this example, however, we will include support for sorting and paging and will create methods which meet these needs.
A straightforward implementation of the PersonDAL class is shown below. The System.ComponentModel library lets us add declarative attributes to the source code that will help the .NET compiler catch any mistakes we might make with the DAL methods, and will help when the ObjectDataSource is deciding which DAL method to use.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.Configuration;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Library1;
using WebApp1;
namespace WebApp1.App_Data
{
[DataObject]
public class PersonDAL
{
private int _count = 0;
[DataObjectMethod(DataObjectMethodType.Select)]
public List<PersonEntity> Select(int startRecIdx, int maxRows, String sortedBy)
{
List<PersonEntity> records = new List<PersonEntity>();
SqlConnection conn = null;
SqlDataReader dr = null;
SqlParameter ret = null;
try
{
conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["15seconds"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("utilPAGE", conn);
cmd.CommandType = CommandType.StoredProcedure;
if(String.IsNullOrEmpty(sortedBy))
{
sortedBy = "LastName";
}
int startpg = (startRecIdx / maxRows) + 1;
cmd.Parameters.AddWithValue("@datasrc", "Person");
cmd.Parameters.AddWithValue("@orderBy", sortedBy);
cmd.Parameters.AddWithValue("@startPage", startpg);
cmd.Parameters.AddWithValue("@pageSize", maxRows);
ret = new SqlParameter("ReturnValue", SqlDbType.Int);
ret.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(ret);
dr = cmd.ExecuteReader();
while(dr.Read())
{
PersonEntity item = new PersonEntity();
item.ID = (int)dr["PersonID"];
item.FirstName = dr["FirstName"].ToString();
item.MiddleName = dr["MiddleName"].ToString();
item.LastName = dr["LastName"].ToString();
//item.Height = (double)(dr["Height"] ?? 0.0);
item.Height = Convert.ToDouble(dr["Height"].ToString());
records.Add(item);
}
}
catch(Exception ex)
{
throw ex;
}
finally
{
if(dr != null)
{
dr.Close();
}
if(conn != null)
{
conn.Close();
}
}
_count = Convert.ToInt32(ret.Value);
return records;
}
[DataObjectMethod(DataObjectMethodType.Update)]
public static bool Update(PersonEntity item)
{
bool retval = false;
SqlConnection conn = null;
try
{
conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["15seconds"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("PersonSET", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", item.ID);
cmd.Parameters.AddWithValue("@FirstName", item.FirstName);
cmd.Parameters.AddWithValue("@MiddleName", item.MiddleName);
cmd.Parameters.AddWithValue("@LastName", item.LastName);
cmd.Parameters.AddWithValue("@Height", item.Height);
SqlParameter ret = new SqlParameter("ReturnValue", SqlDbType.Int);
ret.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(ret);
cmd.ExecuteNonQuery();
int result = Convert.ToInt32(ret.Value);
retval = result > 0;
}
catch(Exception ex)
{
throw ex;
}
finally
{
if(conn != null)
{
conn.Close();
}
}
return retval;
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public static bool Delete(PersonEntity item)
{
bool retval = false;
SqlConnection conn = null;
try
{
conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["15seconds"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("PersonDELETE", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", item.ID);
SqlParameter ret = new SqlParameter("ReturnValue", SqlDbType.Int);
ret.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(ret);
cmd.ExecuteNonQuery();
int result = Convert.ToInt32(ret.Value);
retval = result > 0;
}
catch(Exception ex)
{
throw ex;
}
finally
{
if(conn != null)
{
conn.Close();
}
}
return retval;
}
public int GetRecordCount(int startRecIdx, int maxRows, string sortedBy)
{
return _count;
}
public int GetRecordCount()
{
return _count;
}
}
}
The Select method begins by creating an empty List collection that can hold PersonEntity objects.
After declaring some ADO.NET objects, the code enters a try block. This is
important, as any SQL objects used in this method need to be closed properly whether the method
succeeds or fails. A connection is created based on the web.config connection string entry named
15seconds, and preparations are made for running the utilPAGE stored procedure. Because utilPAGE
needs to have a non-blank orderBy argument, the LastName field is used as a default if nothing else
is supplied. The parameters for table name, sort field list, starting page number, and page size
are added, as is a ReturnValue parameter that will receive anything which the stored procedure
explicitly sends with a return statement. The query is executed, the results
are converted into new PersonEntity objects, and the PersonEntity objects are added to the
List<PersonEntity> collection. The SQL objects are properly closed in the
finally clause, and (because the return value can only be accessed
after the SqlDataReader object is closed) the return value is stored in _count.
The Update method receives a PersonEntity object when it is called. Because the only difference between an Insert and an Update will be the value of the ID field in this object, we will treat both the same and let the stored procedure handle the necessary logic. The Update method will largely imitate the Select method, except that the stored procedure and arguments are different and no result set will be processed. The return value will be checked to see if the update succeeded, with numbers less than one indicating an error.
The Delete method receives a PersonEntity object when it is called, however only the ID field will be populated. (What fields are populated for this is controlled by the DataKeyNames property, which we will see later.) The Delete method is nearly identical to the Update method, except for the stored procedure and arguments. The return value will be checked to see if the delete succeeded, with numbers less than one indicating an error.
Conclusion
The work so far has focused on laying a foundation, and just as with building an actual foundation,
there has been a lot of critical activity but little in the way of visible results. In the second
part of this article, we will create an interactive web page with very little code that uses
everything created so far.
|
