ASP.NET supplies a data control structure called ObjectDataSource which supplies the possibility to use the 3 tier design model and has the advantage that less code has to be written. It supports all sql commands from the middle tier. You only have to supply the underlying object and map the methods from the Business Layer to the different sql commands.

The  C1Schedule can be bound to a data table using ObjectDataSource 3 tier architecture by combining the data access tier and middle tier in one tier. The positive side for using an ObjectDataSource is that the result of a query can be any possible collection (dataset, datatable), array or list of objects. Every record can be a custom object that makes his data accessible with public properties.

Defining AppointmentBizLayer Class


The first step in databinding is creating a class ‘AppointmentBizLayer’ which would define the business logic and data access logic. We need to define the data access operations (CRUD operations) for the scheduler.

public class AppointmentBizLayer

{

string connstr;

public AppointmentBizLayer()

{

connstr = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

}

[DataObjectMethod(DataObjectMethodType.Select, true)]

public DataSet GetAppointments()

{

OleDbConnection conn = new OleDbConnection(connstr);

OleDbCommand cmd = new OleDbCommand("select * from Appointments", conn);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

return ds;

}

[DataObjectMethod(DataObjectMethodType.Update, true)]

public void UpdateAppointments(Guid AppId, string subject, string location, string body, DateTime startdate, DateTime enddate, string properties)

{

OleDbConnection conn = new OleDbConnection(connstr);

conn.Open();

string id = "{" + AppId + "}";

string cmd = "Update Appointments SET Subject = '" + subject + "', Body ='" + body + "', Location = '" + location + "', Properties = '" + properties + "', StartDate = '" + startdate + "', EndDate ='" + enddate + "' WHERE AppID = '" + id + "'";

OleDbCommand dbCommand = new OleDbCommand(cmd, conn);

dbCommand.ExecuteNonQuery();

conn.Close();

}

[DataObjectMethod(DataObjectMethodType.Insert, true)]

public void InsertAppointments(Guid AppID, string subject, string location, string body, DateTime startdate, DateTime enddate, string properties)

{

OleDbConnection conn = new OleDbConnection(connstr);

conn.Open();

string cmd = "insert into Appointments(AppID, Subject, Body, Location, StartDate, EndDate, Properties) values ('" + AppID + "','" + subject + "','" + body + "','" + location + "','" + startdate + "','" + enddate + "','" + properties + "')";

OleDbCommand dbCommand = new OleDbCommand(cmd, conn);

int i = dbCommand.ExecuteNonQuery();

conn.Close();

}

[DataObjectMethod(DataObjectMethodType.Delete, true)]

public void DeleteAppointments(Guid AppId, string subject, string location, string body, DateTime startdate, DateTime enddate, string properties)

{

OleDbConnection conn = new OleDbConnection(connstr);

conn.Open();

string cmdText = "DELETE FROM Appointments WHERE AppId= \"" + AppId + "\"";

OleDbCommand cmd = new OleDbCommand(cmdText, conn);

int i = cmd.ExecuteNonQuery();

conn.Close();

}

}

ObjectDataSource


To add an ObjectDataSource, drop it from the Toolbox on the Web page. Click the Smart Tag > Configure Data Source and a wizard will help you to choose a business object “AppointmentBizLayer” which defines the data methods.



Click on Next and set the Select, Insert, Update and Delete methods of ObjectDataSource.



Click on Finish button to save the changes.

The TypeName property of ObjectDataSource defines the type of object which will be instantiated. The DataObjectTypeName property refers to the class that implements the sql methods. Every operation has to be linked with a method of the class defined in the TypeName property. Every time a method is called, an object of the class defined in the TypeName property is created by using the default constructor and destroyed afterwards.

By default, when the ObjectDataSource is bound to AppointmentBizLayer, the DataObjectTypeName gets set to ‘System.Guid’. Remove this and keep this field empty.

C1Schedule Mappings


The C1Schedule’s DataStorage component is responsible for data binding. AppointmentStorage object stores all the scheduled appointments. We need to bind the ObjectDataSource to this object. Set the DataStorage.AppointmentStorage.DataSourceID to ObjectDataSource.

After setting the datastorage, we need to map all the fields of the AppointmentStorage with the database. This can be done by setting the Mappings as follows :

<DataStorage>

<AppointmentStorage DataSourceID="ObjectDataSource1">

<Mappings>

<IdMapping MappingName="AppId" />

<LocationMapping MappingName="Location" />

<StartMapping MappingName="StartDate" />

<EndMapping MappingName="EndDate" />

<SubjectMapping MappingName="Subject" />

<BodyMapping MappingName="Body" />

<AppointmentProperties MappingName="Properties" />

</Mappings>

</AppointmentStorage>

</DataStorage>


Now our scheduler is ready to work with the ObjectDataSource. Similarly you can bind the various components of C1Schedule’s DataStorage to database.

Download Sample