Thursday, December 22, 2005

How To : Create a Dynamic Time Table in ASP.NET

There will be many times where you will need to have a time table in your applications.

For ex. Think of a travel agency person who wants to mark that the buses/transport facilities would be available on specific times - on specific days. This availability information is prone to have frequent changes and therefore must be editable also.

Lets suppose that we maintain two tables - one for number of days the bus will run on like Sunday, Monday, etc. - we will call it AvailabilityDays, and one for the timings - such as 8:30, 9:00, 9:30, 11:30, 12:30 , etc. called AvailabilityTimes.

All we need is an editable grid with checkboxes, with columns as AvailabilityTimes and rows as the AvailabilityDays.

The first step is to get data through a Stored Procedure into a DataSet. The following code gets the code into a DataSet from an SP that has just 2 select statements from the

DataTable avDaysTable = new DataTable();
DataTable avTimeTable = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlCon = new SqlConnection("server=[servername];uid=[username];pwd=[password];database=[database]");
SqlCommand cmd = new SqlCommand("GetAvailabilityDaysAndTime",sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
avDaysTable = ds.Tables[0];
avTimeTable = ds.Tables[1];

Now that we have two datatables with Days and Times, we now need to construct a dynamic table containing these. For that we first declare an empty table in the aspx page:

<asp:table CssClass="sub-heading" id="MyTable" Width="100%" Height="50%" Runat="server" Font-Size="10" Font-Name="Arial" BorderStyle="Ridge" GridLines="Both"></asp:table>

Now to build the dynamic time table, we first take the count of the columns that need to come, and first construct the header row with the number of columns.

int columnCount = avTimeTable.Rows.Count;
TableItemStyle tableStyle = new TableItemStyle();
tableStyle.HorizontalAlign = HorizontalAlign.Center;
tableStyle.VerticalAlign = VerticalAlign.Middle;
tableStyle.Width = Unit.Pixel(100);
tableStyle.CssClass = "button-editable-heading";

TableRow headerrow;
TableCell headerCell;
headerCell = new TableCell();
headerCell.Text = "";
headerrow = new TableRow();
for(int i=0;i<avTimeTable.Rows.Count;i++)
headerCell = new TableCell();
headerCell.Text = avTimeTable.Rows[i][1].ToString();

Next, for each of the columns in the header, we now need to add as many number of days as in the AvailabilityDays table. This will constitute the rows of the table. To each of these rows, we also need to add a Checkbox so that the availability for a day and for a particular time can be set. Finally, add these rows to the main table to complete the time table grid.

CheckBox chk;
for(int i=0;i<avDaysTable.Rows.Count;i++)
//add rows to first column
headerrow = new TableRow();
headerrow.ID = i.ToString();
headerCell = new TableCell();
headerCell.Text = avDaysTable.Rows[i][1].ToString();
for(int j=0;j<avTimeTable.Rows.Count;j++)
headerCell = new TableCell();
chk = new CheckBox();
chk.ID = "chk" + i.ToString() + j.ToString();;

Thats it. We now have a time table grid ready to take the values for any day any time. In the next article, I'll explain how we can store and retrieve values in the time table.

1 comment:

M R said...

how to create stored procedures