If you don't have MS Outlook, you may want to give this a try for simple scheduling. You can use it to write one line activities at any hour throughout your lifetime. It also includes an alarm feature for notifying you of special events. Since it includes the source code, you can modify it to customize your scheduling needs.
The Scheduling program consists of three main classes. The Form (shown above), the DatabaseController used to read and write scheduled events into the database and RowData which corresponds to data extracted from a row in the database. The UML Design is shown below:
The Above Design was Reverse Engineered using WithClass 2000 UML Design Tool
There are many aspects of the C# .NET framework we could talk about in this code such as use of the timer, listview manipulation, key capturing, the calendar control and edit box positioning. In this article we will concentrate primarily on how to use ADO.NET to read and write the scheduling information. Below is a database snapshot of our calendar database:
The ADO.NET Library Framework provides a fairly simple means of reading and writing data to an access JET database. Reading from the database is performed in a few steps. First you set up what's called an ADODataSetCommand Object. This object allows you to assign SQL commands to it in order to manipulate the database. The code for assiging a command to this object is shown below:
private ADODataSetCommand AdoCmd = new ADODataSetCommand("SELECT * FROM Activities", "Provider=Microsoft.JET.OLEDB.4.0;data source=calendar.mdb" ); // create a new DataSet Command and connect to
// the Access database calendar.mdb
...........
// set up a SQL Select command that filters on the date
AdoCmd.SelectCommand.CommandText = "SELECT * FROM Activities WHERE Date = #" + strDate + "#";
Here we have a DataSet Command Object in which we change the command to filter only Activity rows that are performed on a specific date. These are the rows we will display in our listview located on the left side of the form. Once we have the DataSet Command Object setup, we can create a dataset and fill it:
DataSet dtSet =
new DataSet(); // create a new data set
AdoCmd.FillDataSet(dtSet, "Activities"); // Fill the DataSet using the DataSet Command Object
The DataSet can now be used to extract information from the table. The table reference is gotten from the collection of tables in the dataset. Our database only has one table "Activities", so we can just get the first table, indexed at 0. Once we have the reference to the DataTable, we can go through the Rows collection and extract each row of information from the table. A Column of information in a row is extracted by dereferencing using the column name of the table (e.g. dtRow["Activity"] ). You could also use the column index (e.g. dtRow[4]):
DataTable dTable = dtSet.Tables[0];
// extract the first (and only) table from the dataset
// go through each row of the dataset and populate the listview
foreach( DataRow dtRow in dTable.Rows)
{
nCount = CalcRow(dtRow); // determine listview row to populate using the date and time information
// populate the listview row from the "Activity" column in the database
listView1.ListItems[nCount].SetSubItem(0 , dtRow["Activity"].ToString());
// Determine the Image to use in the listview based on whether the "IsAlarm" Column is set or not
if (dtRow["IsAlarm"].ToString().ToBoolean() == true)
{
listView1.ListItems[nCount].ImageIndex = 1;
}
else
{
listView1.ListItems[nCount].ImageIndex = 0;
}
}
Writing in ADO.NET is a bit more, well, complicated. To write (insert) a new row, I used a simple method provided by the Rows Collection object called AddUpdate. Below is a snippet of code from the WriteOutActivities routine in the DatabaseController to show how this command is used:
// seperate dataset created for use in adding new rows
// This command is selected in order of ID, so we can easily get the maximum ID (Primary Key) to use for the next
// Available Row
AdoCmdAll.SelectCommand.CommandText = "SELECT * FROM Activities ORDER BY ID";
AdoCmdAll.FillDataSet(dtSetAll, "Activities");
..........
int nMax = 0;
DataTable dTableAll = dtSetAll.Tables[0];
if (dTableAll.Rows.Count > 0)
{
// compute the maximum Row ID, by getting the ID of the last ordered row and adding 1
nMax = dTableAll.Rows[dTableAll.Rows.Count - 1]["ID"].ToString().ToInt32() + 1;
}
if (nMax < 0)
{
nMax = 0;
}
// add to the Row Collection the new data row with maximum row id + 1 as the new primary key
dTableAll.Rows.AddUpdate(new object[]
{
nMax,
strDate,
Time2Integer(listView1.ListItems[index].Text),
IsRowAM(index),
listView1.ListItems[index].SubItems[0],
(listView1.ListItems[index].ImageIndex == 1)
});
........
// Must Call Update on the DataSet Command in order for the changes to take place
AdoCmd.Update(dtSetAll, "Activities");
First the Maximum ID is calculated to get a brand new unique primary key. The maximum row is determined by forcing the dataset to return the rows in order using ORDER BY ID in the SQL statement of the DataSet Command. Then the row of data including the ID (Primary Key), date, time, am/pm flag, activity and alarm flag is added using the AddUpdate command. Finally we need to call Update on the DataSet Command Object, AdoCmd to force the changes to take place from the dateset into the database.
Editing an existing row is done a bit differently. Below we show how to update a row in the database that turns the alarm flag off in an activity. This is done by calling BeginEdit on a row, changing the data in the row and then calling EndEdit. Afterwards, we call Update on the ADODataSetCommand, to make sure the changes are saved in the database:
public void DeleteAlarm(int nKey)
{
try
{
// find the row with the passed in primary key
AdoCmd.SelectCommand.CommandText = "SELECT * FROM Activities WHERE ID = " + nKey.ToString();
DataSet dtSet = new DataSet();
AdoCmd.FillDataSet(dtSet, "Activities"); // fill the data set using the ADO Command Set
DataTable dTable = dtSet.Tables[0];
// go through each row meeting the SELECT criteria (should only be one row, since a primary key is unique)
foreach( DataRow dtRow in dTable.Rows)
{
dtRow.BeginEdit(); // called before editing the column in the row
dtRow["IsAlarm"] = false; // edit the column for the alarm switch
dtRow.EndEdit(); // finished the editing on the row
}
// Must call update for editing to take effect
AdoCmd.Update(dtSet, "Activities");
}
catch(Exception exx)
{
System.Console.WriteLine(exx.Message.ToString());
}
}
That's really all there is to it. I think you'll find it is a little more intuitive than the recordset concept in Visual C++. As far as the Scheduling program is concerned, look forward to future enhancements such as printing your daily schedule. Happy Scheduling!