ASP.NET, ADO.NET and calendaring

Sooner or later every community or organisation finds the need to publish a calendar of upcoming events. Given close to universal Internet access, a natural place to post such information is on a web site. This article shows how to use ASP.NET to create a simple but very useful community events calendar. In it we’ll explore the basics of creating an XML document and discover how to manipulate and display the data, using ADO.NET to bind the entries in the XML document to the Web Controls.

First, let’s consider how to persist the data. If we were designing a similar page using static HTML, the events data would just be embedded within the HTML source. We are basically dealing with a small amount of data compared with an Order Entry System or Payroll application, so although a static HTML page is not an optimal solution from the point of maintenance, there shouldn’t be a problem with regard to response time.

In the case of our ASP.NET program, we will read the data from a file and present the current schedule to the user whenever the page is visited. We could use a relational database such as Access, SQL Server or MySQL to persist the data. However, given that the number of records for this application will be relatively small, we’ll just use an XML document to store the data, which in turn will be associated with a DataSet component.

Creating the XML schema

To use ADO.NET with the XML document, we must first create an XML Schema. This is actually another XML document that will describe the structure of our event calendar data. To add an XML schema to the project, select Project|Add New Item from the menu. Click on the XML Schema icon, change the default name to something meaningful (EventCalendar for this example) – and click Open. We are now presented with the XML Schema Editor. Begin defining the Schema by drag-dropping an Element from the XML Schema toolbox or right-click and select Add|New Element from the popup. An instance of the XML Designer will be added to the XML Schema Designer – which appears as a grid structure. Change the default name from element1 to EventCalendar. Below this, the field names and data types are defined, see Figure 1).

Figure 1

There are several types of entries that can be added here – indicated by the drop-down at the far left, sporting a bold-face ‘E’ for ‘element.’ For our purposes we will leave this alone since the fields will be represented as elements. For the Event Calendar we will be using the following fields/data-type pairs:

• EventID int
• EventDateTime dateTime
• Location string
• Description string

The first field will serve as the primary key and is just a unique long integer. We define EventID as the primary key by right-clicking on the row where it was defined and selecting Add|New Key. A dialog box will appear prompting for the Index Name, which can be the same name as the field name being indexed if desired. Be sure to tick the Dataset primary key checkbox and leave the Nullable box empty, see Figure 2.

Figure 2

The EventID field is just an arbitrary number to give us a unique key. Since we are using an XML document and not a database, we will need to auto-increment this field in the code. A simple binary file will be used to hold the last used number, which will be read, incremented and replaced with the new value, thus insuring a unique number is used each time.

Even with the XML Schema complete, we will not be able to use it in our project until we have generated a dataset from the schema. Selecting Schema|Generate dataset from the menu will turn on your disk light and hourglass your mouse, conveniently adding a new dataset in the process. It is a really good idea to check and double-check your schema design before building and using the schema in your project. The IDE is not too forgiving about changes to the schema after it has been used in your project so you should try and avoid this. If you must make changes, it’s easier to add an entirely new XML Schema to your project. You can then copy and paste from the original XML Designer to avoid re-entering the field information.

With the schema created and the dataset generated, we are ready to design the forms. We will use two Web Forms in this application – one to add new items and another containing a grid that will allow us to display and edit the existing items.

The Add Events web form

Since we can’t display something unless it’s there, it would make sense to start with the add form, which we will name AddEvent.aspx. Set this as the start page by right-clicking this form in the Solution Explorer and selecting the appropriate entry. Let’s start by adding a header to the top of the form. Drag and drop a Table control onto the AddEvent form. The control should spread across the form, leaving enough room for two rows. Click on the Rows property and click the button to bring up the TableRow Collection Editor. Add a table row and then click the button next to Cells to bring up the TableCell Collection Editor. Click the Add button and set the properties of the new cell as follows:

0 TableCell:
ColumnSpan=2
Text=<H1>Community Events
	Calendar</H1>
HorizontalAlign=Center

Add another row to the table. The second row will have two cells, with the following properties:

0 TableCell:
	Text=<h3>Add Event</h3>
	HorizontalAlign=Center
1 TableCell:
	Text=<h3><a 
		href=ListEvents.aspx>
		List Events</h3>
	HorizontalAlign=Center

Now we can add the remaining controls to the Web Form starting with the Data Access controls. Click the Data tab and drag the Dataset Icon onto the Web Form. The Add Dataset dialog will appear and should present the Dataset, previously generated from the XML Schema as the default. Click Ok to add the Dataset. Now, click the Web Forms tab of the Toolbox and add the following Web Controls, setting the properties as indicated:

DataSet:
	Name=dsEventCalendar
Calendar:
	ID=caEvents
DropDownList:
ID=ddlHours
Label:
	Text=:
	Font.Bold=True
	Font.Size=Larger
DropDownList:
	ID=ddlMinutes
RadioButtonList:
	ID=rbAMPM
	RepeatDirection=Horizontal
Label:
	Text=Description
	Font.Bold=True
	Font.Size=Larger
TextBox :
	ID=txtDescription
RequiredFieldValidator:
	ID=rfDescription
	Font.Bold=True
	ErrorMessage=Please Enter a Description
	ControlToValidate=txtDescription
Label:
	Text=Location
	Font.Bold=True
	Font.Size=Larger
TextBox:
ID=txtLocation
RequiredFieldValidator:
	ID=rfLocation
	Font.Bold=True
	ErrorMessage=Please Enter a Location
	ControlToValidate=txtLocation
Button:
	ID=btnAddEvent
	Font.Bold=True
	Forecolor=Blue
	Text=Add Event
Label:
	ID=lblResults
	Forecolor=Blue

After adding the controls, click on the RadioButtonList and press F4 to bring up the properties window. Click on the Items property and press the button to bring up the ListItem Collection Editor and add the following items:

0
	Selected=True
	Text=AM
	Value=AM
1
	Selected=False
	Text=PM
	Value=PM

The form is pretty much ready to go at this point, but let’s make it look a little prettier. Click on the Calendar control and click the Auto Format link at the bottom of the Properties window. You will be presented with the Calendar Auto Format dialog box, which will present you with a selection of preset styles. Select the style you prefer and click OK to close the dialog box. The completed AddEvents.aspx form is shown in Figure 3.

Figure 3

Adding Items to the XML Document

Now let’s take a look at getting what’s on the Web Form into the XML Document. We begin with the Page_Load event, which will read the XML document and associate it with the DataSource. Since initially there will be no XML document until we have added at least one item, we only want to do this if the file exists. Having located and read the XML document, we bind it to the Web Form using the DataBind method.

Private Sub Page_Load(ByVal sender _
		As System.Object, _
		ByVal e As System.EventArgs) _
		Handles MyBase.Load
	Dim strEventCalendar As String = _
		Server.MapPath( _
		“EventCalendar.xml”)
	Dim fs As New _
		System.IO.FileInfo( _
		strEventCalendar)
	If fs.Exists() Then
		Me.dsEventCalendar.ReadXml( _
			MapPath( _
			“EventCalendar.xml”))
		If Not Page.IsPostBack Then
			Me.DataBind()
			Me.caEvents.SelectedDate _
				= Now
			Dim i As Integer
			For i = 1 To 12
ddlHours.Items.Add(Format(i, “0#”))
			Next
			For i = 0 To 55 Step 5
ddlMinutes.Items.Add(Format(i, “0#”))
			Next
		End If
	End If
End Sub

Adding a record to the document is done by instantiating and populating a DataRow object, which is added to the dataset using the AddEventCalendarRow and AcceptChanges methods. Finally the dataset is bound and written back to disk:

Private Sub btnAddEvent_Click( _
		ByVal sender As _
		System.Object, _
		ByVal e As System.EventArgs) _
		Handles btnAddEvent.Click

	Dim dr As DataRow = _
		Me.dsEventCalendar.Tables( _
			0).NewRow
	dr.Item(“EventID”) = NextID()
	With caEvents
		dr.Item(“EventTime”) = _
			New DateTime( _
			.SelectedDate.Year, _
			.SelectedDate.Month, _
			.SelectedDate.Day, _
			CLng( _
		ddlHours.SelectedItem.Text), _
			CLng(_
	ddlMinutes.SelectedItem.Text), _
		0)
	End With
	dr.Item(“Location”) = _
		txtLocation.Text
	dr.Item(“Description”) = _
		txtDescription.Text
	dsEventCalendar.EventCalendar. _
		AddEventCalendarRow(dr)
	dsEventCalendar.EventCalendar._
		AcceptChanges()
	Me.dsEventCalendar.WriteXml( _
		MapPath( _
		“EventCalendar.xml”))
	Me.DataBind()
	Me.ViewState(“EventsAdded”) = _
		Me.ViewState(“EventsAdded”) + 1
	lblResults.Text = _
		Me.ViewState(“EventsAdded”) & _
		“ event(s) added.”
End Sub

Note the call to the NextID() Function. This function (not listed) simply returns a unique Integer, which is read from disk, incremented and written back. By default, an ASP.NET program will not have the necessary permissions to write to this file nor the XML document file. In order to allow ASP.NET to create and write to these files, it will be necessary to right-click the appropriate folder and select the Security tab. Press the Add button to add a user, and then select ASPNET from the list and grant the appropriate permissions to this user.

With the code in place and the permissions set, you should now be able to add items to the XML document. Press F5 to run the application, enter some values and press the Add Events button. Since ASP.NET will be nice enough to send you an error message if something has gone wrong, the whole thing should be pretty un-eventful except for the message indicating that an event has been added. Since we don’t have a form to display the data yet, the easiest way to see if anything has been entered is to look for the newly created XML file using Windows Explorer and double-click it. This file will be found in the same folder where the application is running – normally one of the folders in \inetpub\wwwroot. On my computer, the path is:

c:\inetpub\wwwroot\calendar\
	EventCalendar.xml

Double-click this file, and you should see the record displayed in XML format.

Displaying the Data

Now that we can add items to our calendar, let’s write some code to display them. We will add another web form to the project called ListEvents.aspx with the same background and similar header information. This form also gets a Data Set, which is added as described above. On this page, we will want to sort the items in date order, so a DataView control should be added by dragging and dropping this to the Web Form as well. Lastly, we will use a DataGrid to display the events. The controls for this form are listed below:

DataSet:
	Name=dsEventCalendar
DataView:
	Name=dvEventCalendarSorted
	Sort=EventDateTime
	Table=dsEventCalendar.EventCalendar
Label:
	ID=lblOutput
	ForeColor=#C00000
DataGrid:
	ID=dgEvents
	DataKeyField=EventID
	DataSource=dvEvents

The DataGrid also has an Auto Format link as described for the Calendar control, and should be set to the same setting as was chosen previously to provide a consistent look and feel. We will want to further customize the DataGrid control by clicking the Property Builder link and selecting the Columns tab. Un-click the ‘Create columns automatically at run time’ checkbox. Below this, open the DataFields node of the Avaliable Columns tree and move the EventID, EventDateTime, Description and Location fields to the Selected Columns Listbox. (If no fields appear, be sure that you have set the Datasource Property). The EventID is only useful for editing and deleting and will be invisible on the form so the Visible property should be left unchecked for this field. Conversely, the Read Only box should be checked as well since this field is not to be modified. The EventDateTime field also needs some attention. After clicking on EventDateTime from the Selected Columns list, enter the following Data formatting expression:

{0:MMM dd yyyy hh:mm tt}

Having added the fields, scroll down and open the Button Column node. We will want to add the Edit, Update, Cancel as well as the Delete buttons to our grid. Select Link Button as the Button Type for each. With the columns selected, let’s set the paging properties of the grid. Click on the Paging tab at the left, and click the Allow Paging checkbox. Finally, let’s increase the default column widths a bit. This is done by clicking on the Format tab, opening the Columns node, selecting the appropriate column and entering the desired value. A little experimentation is in order here – but be aware that the DataGrid must be wide enough to support the column widths or they will be conveniently ignored.

The code to bind the DataGrid to the form is exactly the same as was presented for the AddEvens form and is not repeated here. With the DataGrid bound to the Web Form, we need only two lines of code to allow paging, which appears in the DataGrid’s PageIndexChanged event:

Private Sub dgEvents_PageIndexChanged _
		(ByVal source As Object, _
		ByVal e As _
		System.Web.UI.WebControls. _
		DataGridPageChangedEventArgs) _
		Handles dgEvents.PageIndexChanged
	Me.dgEvents.CurrentPageIndex = e.NewPageIndex
	DataBind()
End Sub

Delete, Edit and Update

We can add and display items at this point, but the Edit and Delete buttons will not function without some further code. Examining the DataGrid events, one will find CancelCommand, DeleteCommand, EditCommand and UpdateCommand events. These routines are called when the Cancel, Delete, Edit and Update buttons are pressed. We will start off by providing the delete function. A row is deleted, or ‘removed’ as the method is named from a DataSet by first setting a DataRow object to the row to be removed, and then passing this DataRow to the remove method. The method will have a name that matches the naming of the TableName of the DataSet, as we shall see shortly. We retrieve the row to be deleted using the Select method of the DataSet’s Tables collection. This method returns an array of DataRow objects based on the selection criteria. In our case, we only want one row but this method requires an array as a return value so we will just remove the item at position zero in the array. We can determine the EventID of the row to be deleted by accessing the DataGrid’s DataKeys collection. This collection is automatically populated based on the value indicated for the DataKey property, which we set during design time. The second parameter of the DeleteCommand event represents the row in the DataGrid, and so the row in question can be found by accessing the Item.ItemIndex property. Since we are persisting our data to an XML document, we will need to write the document back to storage after the operation has completed:

Public Sub dgEvents_DeleteCommand(ByVal source As Object, _
		ByVal e As System.Web.UI.WebControls. _
		DataGridCommandEventArgs) _
		Handles dgEvents.DeleteCommand
	If Not Me.ViewState(“Mode”) = MODE_EDIT Then
		Dim dr As DataRow() = _
			Me.dsEventCalendar.Tables(0).Select( _
			“EventID =” & CType(dgEvents.DataKeys( _
			e.Item.ItemIndex), Integer))
		Me.dsEventCalendar.EventCalendar. _
			RemoveEventCalendarRow(dr(0))
		Me.dsEventCalendar.WriteXml( _
			Server.MapPath(“EventCalendar.xml”))
		DataBind()
	End If
End Sub

Editing involves the use of the other three buttons. Pressing the Edit Button will allow editing of the cells in the DataGrid. The Update Button will cause the changes to be written back to the DataSet and the Cancel Button will provide a way to cancel the edit. Placing the DataGrid into edit mode is done by setting the EditItemIndex to the current row, and re-binding to the Web Form:

Public Sub dgEvents_EditCommand _
		(ByVal source As Object, _
		ByVal e As _
		System.Web.UI.WebControls. _
		DataGridCommandEventArgs) _
		Handles dgEvents.EditCommand
	Me.ViewState(“Mode”) = MODE_EDIT
	Me.dgEvents.EditItemIndex = _
		e.Item.ItemIndex
	DataBind()
End Sub

The code to cancel edit mode is very similar. Instead of setting the EditItemIndex to a row to be edited, we simply set it to –1:

Private Sub dgEvents_CancelCommand _
		(ByVal source As Object, _
		ByVal e As _
		System.Web.UI.WebControls. _
		DataGridCommandEventArgs) _
		Handles dgEvents.CancelCommand
	dgEvents.EditItemIndex = -1
	DataBind()
	Me.ViewState(“Mode”) = MODE_DISPLAY
End Sub

To update the DataSet with the changes, we first retrieve the DataRow, as we did with the Delete operation. Then we move the fields from the DataGrid to the fields in the DataRow and accept the changes. Accessing the edited data involves a somewhat confusing .Net syntax. To read the value of a particular cell, we need to access a TextBox which is located within a Cell which is located within the DataGrid’s row – or item zero of the Cell’s Controls collection. We then use the CType function to convert this to a TextBox and then finally access the value using the Text property of the Textbox, which renders this somewhat terse syntax, which would retrieve the edited value of the column at position 1:

CType(e.Item.Cells(1).Controls(0), _
	TextBox).Text

The update routine determines the EventID of the row to be updated and retrieves this DataRow from the DataSet. The EventDateTime (the only field in this application that could cause an invalid data exception) is checked and if valid, the edited fields are moved to the DataRow. The AcceptChange method of the DataSet makes the update complete:

Private Sub dgEvents_UpdateCommand( _
		ByVal source As Object, _
		ByVal e As _
		System.Web.UI.WebControls. _
		DataGridCommandEventArgs) _
		Handles dgEvents.UpdateCommand
	Const COL_EVENT_ID = 0
	Const COL_EVENT_DATE = 1
	Const COL_DESCRIPTION = 2
	Const COL_LOCATION = 3
	Dim intID As Integer _
		= CType(dgEvents.DataKeys( _
		e.Item.ItemIndex), Integer)
	Dim dr() As DataRow _
		= Me.dsEventCalendar. _
		EventCalendar.Select( _
		“EventID = “ & intID)
	Dim strEventDate As String = _
		CType(e.Item.Cells( _
		COL_EVENT_DATE). _
		Controls(0), TextBox).Text
	If IsDate(strEventDate) Then
		With dr(0)
			.Item(“EventDateTime”) _
				= CType( _
				strEventDate, Date)
			.Item(“Description”) = _
				CType(e.Item.Cells( _
				COL_DESCRIPTION). _
				Controls(0), _
				TextBox).Text
			.Item(“Location”) = _
				CType(e.Item.Cells( _
				COL_LOCATION). _
				Controls(0), _
				TextBox).Text
		End With
		dsEventCalendar.EventCalendar._
			AcceptChanges()
		Me.dsEventCalendar.WriteXml( _
			MapPath( _
			“EventCalendar.xml”))
		dgEvents.EditItemIndex = -1
					‘ Release edit mode
		Me.DataBind()
		Me.ViewState(“Mode”) = _
			MODE_DISPLAY
	Else
		Me.lblOutput.Visible = True
		lblOutput.Text = _
			“Invalid date/time - “ & _
			“please correct or cancel.”
	End If
End Sub

Whistles and Bells

At this point we have a fully functional web application. We can add items, change items and delete items. It’s not bad the way it is but there are still a couple of things that we could do to improve it. Probably the most obvious flaw in the existing application is the failure of the Delete button to prompt the user before deleting a row. We can associate a simple Java function to run when the button is pressed by taking advantage of the ItemDataBound event of the DataGrid. This event will fire as the rows of the DataSet are bound to the DataGrid, thus allowing us to set the onclick event of the button currently being bound at that time. We only want to do this if the row is a detail row or in Dot Net terms a ListItem or AlternatingItem. The Java function will prompt the user and only allow execution to proceed if the user has clicked the Ok button:

Public Sub dgEvents_ItemDataBound _
		ByVal sender As Object, _
		ByVal e As _
		System.Web.UI.WebControls _
		.DataGridItemEventArgs) _
		Handles dgEvents.ItemDataBound
	If e.Item.ItemType = _
		ListItemType.Item _
		Or e.Item.ItemType = _
		ListItemType.AlternatingItem _
		Then
		Dim btnDelete As Button = _
			CType( _
			e.Item.Cells(5).Controls( _
			0), Button)
		btnDelete.Attributes( _
			“onclick”) = _
			“return confirm( _
			‘Are you sure you want _
			to delete this event?’);”
	End If
End Sub

The last two niceties take us back to the AddEvents form. It is always a good idea to minimize keystrokes and mouse clicks and there are two perfect opportunities for us to do so here. Currently, when the page displays, a mouse click is required to move the cursor into the first TextBox. It is also necessary to click the Add Event button explicitly, as pressing the Enter key will not fire the code to add an item.

To automatically set focus to a control on the form, Javascript once again comes to the rescue. Double-click the AddEvent.aspx icon in the Solution Explorer. Click the HTML tab at the bottom of the Form Designer Window to reveal the HTML code behind the form. Add the following Javascript just above the </HEAD> tag:

<script language=”Javascript”>
	function setFocus()
	{ Form1.txtDescription.focus(); }
</script>

The Form1 in the code above refers to the ID found in the <Form> tag. In order for this routine to run, we need to associate it with the page’s onload event. This is done by adding the following declaration inside the <BODY> tag:

onload=”setFocus();”

We have but one line of code left to complete the project. We can cause the Add Event button to fire when the Enter key is pressed by taking advantage of the RegisterHiddenField method of the Page object. Strangely, this method does not appear in the dropdown list of methods for this class, effectively rendering the hidden field method hidden! This final line of code is added to the Page_Load event of AddEvents.aspx:

Page.RegisterHiddenField( _
	“__EVENTTARGET”, _
	“btnAddEvent”)

---

The complete source code for this project can be downloaded from www.skycoder.com/downloads.

Jon Vote is an Independent Consultant based on the west coast of the USA. He is a Microsoft Certified Solution Developer (MCSD) with a Bachelor’s Degree in Computer Science from Southern Oregon University. He can be reached at [email protected].