|
Introduction
In the first part of this two-part
article, we created the SQL database
objects and .NET classes that will provide the database backend for an ASP.NET website.
In this part, we will tie that logic to an ASP.NET page with a GridView and DetailsView.
In contrast to the first part's emphasis on straight coding with no graphical content,
this part will involve drag-and-drop GUI development with almost no coding.
The Presentation Layer
Open default.aspx in Design mode, open the Toolbox, expand the Data section, and
drag ObjectDataSource onto the web page as shown in Illustration 1.
Illustration 1: Adding the ObjectDataSource
Ignore the Tasks list that appears for the ObjectDataSource. Instead,
sort the control's properties alphabetically and change the values as follows:
| Property | Value |
|---|
| DataObjectTypeName | Library1.PersonEntity |
| DeleteMethod | Delete |
| EnablePaging | True |
| InsertMethod | Update |
| MaximumRowsParameterName | maxRows |
| SelectCountMethod | GetRecordCount |
| SelectMethod | Select |
| SortParameterName | sortedBy |
| StartRowIndexParameterName | startRecIdx |
| TypeName | WebApp1.App_Data.PersonDAL |
| UpdateMethod | Update |
Open the Toolbox again, expand the Data section, and drag a GridView control onto the web page.
In the Tasks list that appears, click on the dropdown for Choose Data Source and select the
ObjectDataSource you added in the previous step -- it should be the only option besides
(None) and <New Data Source...>.
Once you have set the data source, the Tasks list will immediately expand.
Turn on the checkmarks for Enable Paging, Enable Sorting,
Enable Editing, and Enable Deleting as shown in Illustration 2.
Illustration 2: Configuring the GridView
One important but easily overlooked step is to set the GridView's DataKeyNames property.
This is a comma-separated list of fields which make up the table's primary key, and is
critical to the Delete method. Set this property to ID, as that is our only primary field.
(Because we are working at the code level, we use the field names defined in the PersonEntity class; differences
between class property names and table field names can be resolved in the DAL or the relevant stored procedure).
You are now able to try out your web page: press F5 or select
Debug | Start Debugging to start your page running. Click the page
numbers to view different pages of results, click on column headers to change how
the items are sorted, click Edit to modify values, and click Update
to save your changes. You can even click Delete and see a record immediately
disappear. Note that all of this works without anything being added to the code-behind page.
If you want to include the same data on other pages, all you need to do is include similar
ObjectDataSource and GridView objects, which can be done by copying and pasting the raw HTML.
However, you may not like how the columns are arranged, and you would probably like the ability
to add new items to the table. To straighten out the columns, click on the GridView Tasks expander
and select Edit Columns. Ensure the Auto-generate fields checkbox is empty, as shown in
Illustration 3.
Illustration 3: Editing the GridView columns
Because the ID field is an IDENTITY field, it cannot be updated and is usually of no interest to users.
Click on ID in the Selected fields list in the lower left; in the properties section, set
Visible to False and ReadOnly to True,
as shown in Illustration 4.
Illustration 4: Configuring GridView column properties
Working in the Selected fields section, select FirstName and click the up arrow control until it is
just below the CommandField item. Repeat this with the remaining fields until they are in a desirable
order, then click OK.
By default, the GridView shows 10 records at a time. If you would like to change this,
select the GridView object and set its PageSize property to a different positive integer.
Run the project again and note the difference. Your results should resemble Illustration 5
Illustration 5: a working GridView
We are able to edit and delete existing records at this point, however the GridView
control does not include support for inserting new rows. For that, drag a DetailsView
control from the Toolbox onto the page. In the Tasks list that appears, click on the dropdown for
Choose Data Source and select the ObjectDataSource object created earlier.
Once you have set the data source, the Tasks list will immediately expand. Turn on the checkmarks
for Enable Editing, and Enable Inserting. Click on
Edit Fields... to manage the DetailsView object's fields. If no bound fields
are listed, click the hyperlink for Refresh Schema. If prompted to refresh
fields for the GridView control, select No, as this will undo the
arranging and hiding of columns we just did. If prompted to refresh fields for the DetailsView
control, select Yes. Arrange the fields for the DetailsView just as you did for
the GridView, and set the ID field to be ReadOnly and to not be visible.
After closing the DetailsView task list, change the DetailsView object's DefaultMode
property to Insert. Run the project again and verify that you are able to
insert new records successfully, and you have a fully functional system.
If you wish to take things a step further, you can configure your page so that all editing and
deleting takes place in the DetailsView object, and the GridView is only used for viewing and
selecting records to modify. To do this, open the GridView's tasks and disable all checkboxes
except for Enable Paging and Enable Selection.
Open Default.aspx.cs and add the following method:
protected void selectEdit(object sender, EventArgs e)
{
Session["personIdx"] = GridView1.SelectedIndex + (GridView1.PageIndex * GridView1.PageSize);
DetailsView1.DataBind();
}
If you have used names other than the default GridView1 or DetailsView1, be sure to
use those names in the method. This method calculates the index of the record selected
by the user and stores that value in a session variable so it will be accessible elsewhere,
then forces the DetailsView to refresh its data.
Open Default.aspx and select the DetailsView object. Single-click the GridView object on
your page to select it. As shown in Illustration 6, click on the lightning bolt button
in the Properties window to show the object's events and set the SelectedIndexChanged
event to point to the selectEdit method you just created.
Illustration 6: a configured DetailsView
Select the DetailsView object on your page. In the Properties window, set the
DataKeyNames property to ID, and the
DefaultMode to ReadOnly.
One more item needs to be taken care of: when the DetailsView object tries to
populate itself with the selected record, it will call the DAL class' Select
method with a maxRows value of zero. This will cause a division by zero error,
but even if it did not the method still would not return the record the user
selected. We could create an alternate method and stored procedure for the
DetailsView object to use, but a small change to the existing method will
allow it to handle calls from either type of object:
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";
}
if(maxRows < 1) // call is from DetailsView
{
maxRows = 1;
try
{
startRecIdx = (int)HttpContext.Current.Session["personIdx"];
}
catch(Exception ex) { String fooexx = ex.Message; }
}
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;
}
Run the project again, and you should be able to select individual records to
edit or delete. To create a new record, click New in the DetailsView.
Conclusion
The Data Access Layer and ObjectDataSource are not the simplest way to bind
data controls to a SQL data source, however using them allows you to centralize
the relevant logic to take advantage of SQL-side paging and help make ongoing
development easier. Development shops that divide work between graphical
developers and programmers find the separation this provides between graphical
and non-graphical logic especially useful. In the next article in this series,
we will convert this to an AJAX web application with a ReorderList to support advanced sorting.
For a more in-depth look at the ObjectDataSource, I recommend Manuel Abadia's blog at
http://www.manuelabadia.com/blog/PermaLink,guid,32e83915-a503-403e-97c7-e20dcf2e0b7e.aspx.
|
