Log in to like this post! Event Based WebSchedule Data Provider Tom Puglisi / Friday, August 15, 2008 UPDATED ON 1/10/2011 This post was written quite some time ago. Since then, a new, elegant component was added to the ASP.NET control set which does what this post tried to do. The new component is called the WebScheduleGenericDataProvider. Here are some help files on the new component. =================================================== The NetAdvantage for ASP.NET WebSchedule controls represent a series of Web Server Controls that can be used to create a very full featured resource scheduling application. Download the ASP.NET Sample Here. WebSchedule consists of controls that are used to display and navigate Appointments as well as all the supporting reminder, recurrence, and Appointment editing dialog forms. Data is provided to WebSchedule via means of one of several Data Providers. Two specific data providers are included with NetAdvantage for ASP.NET: A Microsoft SQL Server data provider and an OleDB data provider. You are also given the SQL Server script to generate the Database, stored procedures and other objects as well as a Microsoft Access database file. If building an application from scratch, either of these will help you get up and running very quickly. Many companies already have existing production systems that already contain this data and it is stored in many different kinds of back ends such as Microsoft CRM, Sybase or just about any other type of data store. Companies may also be using SQL Server, but their resource and appointment data exists in a completely different data model altogether. Can WebSchedule connect to and access this data? Absolutely! In order for WebSchedule to access data stores other than the included Microsoft SQL Server database or the Microsoft Access file that comes with NetAdvantage for ASP.NET, you will have to create a custom data provider. Here is a very well written KB article that clearly and completely explains how to build your own WebSchedule data provider written by Tony Lombardo. The topic walks you through the code used to create a provider that is used to access an XML file that contains appointments. Understanding this example will allow you to create your own provider. HOWTO:Connect WebSchedule to a custom data source (Part 1) http://devcenter.infragistics.com/Support/KnowledgeBaseArticle.aspx?ArticleID=8484 HOWTO:Connect WebSchedule to a custom data source (Part 2) http://devcenter.infragistics.com/Support/KnowledgeBaseArticle.Aspx?ArticleID=10013 Here is a simple example of an event handler that you would find on the WebForm hosting the custom event based data provider: C# void d_ActivityDeleting(object sender, ActivityDeletingEventArgs e) { string theCmdText = "DELETE FROM Activity WHERE ActivityID =?"; OleDbCommand c = this.GetCommand(theCmdText, _cn, true); c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "ActivityID", e.DataKey)); //Execute the command and if no records have been affected, then there is a problem. if (c.ExecuteNonQuery() == 0) Response.Write("Appointment has not been added"); this.CleanupCommand(c); } So as you can see, you simply take the information provided to you by the event args (in this case, the primary key of the appointment) and then you use it however you need to in order to delete the record or entity from your back end system. Here is the event handler for updates: C# void d_ActivityUpdating(object sender, ActivityUpdatingEventArgs e) { string theCmdText = "UPDATE [Activity] "; theCmdText += "SET"; theCmdText += " [StartDateTimeUtc]=?,"; theCmdText += " [ActivityDescription]=?,"; theCmdText += " [Subject]=?,"; theCmdText += " [AllDayEvent]=?,"; theCmdText += " [Location]=?,"; theCmdText += " [Duration]=?,"; theCmdText += " [EnableReminder]=?"; theCmdText += "WHERE ActivityID=?"; OleDbCommand c = this.GetCommand(theCmdText, _cn, true); //Create and add command params to persist the Appointment properties. //NOTE: //You should add support for loading and saving the other //Appointment properties. This example has been abbreviated //to keep it simple. c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "StartDateTimeUtc", e.Appointment.StartDateTime)); c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "ActivityDescription", e.Appointment.Description)); c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "Subject", e.Appointment.Subject)); c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "AllDayEvent", e.Appointment.AllDayEvent)); c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "Location", e.Appointment.Location)); if (!e.Appointment.AllDayEvent) { int d = ( int)e.Appointment.EndDateTime.Subtract( e.Appointment.StartDateTime).TotalSeconds; c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "Duration", d)); } c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "EnableReminder", e.Appointment.EnableReminder)); c.Parameters.Add( new System.Data.OleDb.OleDbParameter( "ActivityID", e.Appointment.Key)); //Execute the command and if no records have been affected, then there is a problem. if (c.ExecuteNonQuery() == 0) Response.Write("Appointment has not been added"); this.CleanupCommand(c); } In order for your Appointments to show in the WebSchedule controls, they must be periodically fetched from the back end. Doing this is simple with this data provider as you must simply handle the ActivitiesFetching event so that you can retrieve your objects from wherever data store they exist and then using a loop construct, you instantiate and populate Appointment objects and simply add them to the Appointments collection found within the event args of this event. Then, like magic, your appointments will show in the WebSchedule controls. ActivitiesFetching event being handled in the WebForm: C# void d_ActivitiesFetching(object sender, ActivitiesFetchingEventArgs e) { //This is how it works: // 1. Access your data store // 2. Use a loop to iterate through your data and create Appointment objects // 3. Add each of the new Appointment objects to the e.Appointments collection // 4. That's it! string theCmdText = @"SELECT * FROM ACTIVITY"; OleDbCommand c = this.GetCommand(theCmdText, _cn, true); OleDbDataReader d = c.ExecuteReader(); while (d.Read()) { Appointment a = new Appointment(this.WebScheduleInfo1); //NOTE: //You should add support for loading and saving the other //Appointment properties. This example has been abbreviated //to keep it simple. a.DataKey = d[ "ActivityID"].ToString(); a.StartDateTime = new SmartDate(DateTime.Parse(d["StartDateTimeUtc"].ToString())); a.Subject = d[ "Subject"].ToString(); a.Description = d[ "ActivityDescription"].ToString(); a.AllDayEvent = ( bool)d["AllDayEvent"]; if (!a.AllDayEvent) { a.EndDateTime = a.StartDateTime.AddSeconds(( int)d["Duration"]); } (( IList)e.Appointments).Add(a); } this.CleanupCommand(c); } Now that you have learned about several ways of creating a custom WebSchedule data provider, please feel free to modify these existing examples or create your own custom data provider. Either way, WebSchedule provides a great full featured set of controls to help you create the best resource scheduling application out there! Event based WebSchedule Data Provider I recently created a WebSchedule data provider that simply raises events for INSERT, SELECT, UPDATE and DELETE operations. These events expose objects that are internal to the data provider so that you, the developer can simply access the event args and easily create, read, update or delete Appointments.