Monday, August 15, 2011

[C#] - Container Controls 6 : TableLayoutPanel Container combined with SplitPanel

1. Introduction to TableLayoutPanel Container

Do you know how the Table works in html? The "TableLayoutPanel" works almost the same way that the table works in html. In this article we will see how the TableLayoutPanel can be used. How each cells of this panel behaves. Also we will combine some other layout panel. At the end you will see a small profession looking form that can be used by a hospital management applications.

OK. The TableLayoutPanel is collection something called cells and these cells can be arranged in rows and columns. You can imagine each cell as a special container. Why I told it as special container? Well. Each cell can hold only one control in it. What if I need to have multiple controls in one cell? Actually that is not required most of the time. But if you still need an answer, place a container you like in that cell and start placing the controls inside container that occupying the cell.

2. About the Sample

Look at the screen shot given below:

The Form shows a Hospital Bed allocation system. Top of the form shows three floors and beds are arranged in two rows on each floor. Each row in each floor has room for 15 beds. A scroll bar is provided to see all the beds as it initially displays 10 beds on each floor completely. When you click a Bed number the bed goes to the allocated mode and that is shown in yellow color with a check mark in it. You can also select the floor and inside the floor decide south row or North row and decide the bed number to make a reservation or lock it for allocation by clicking the reserve button. I can able to say you murmuring the easy way is go with mouse click it like right click or Control click. OK. But I had taken this to make some function exploration and to explain the nested containers. To have more nice appearance you can place a legend at the bottom of the panel that says Yellow: Allotted. Red: Reserved. Dark Blue: Available.

Well. Now I will walk you through the creation of this form. Hope you downloaded the sample as it has some video to avoid lot of screen shots. View all the videos in full screen and that will help.

3. Placing the TableLayoutPanel inside the Splitter

After creating the Windows form application, A Split container control is placed in the form. Then the container’s splitter is positioned horizontally. At this stage the Panel1 is at the top and Panel2 is at the bottom. Once the Splitter is ready the TableLayoutPanel is placed inside the Panel1 of the split container. Now we have the nested containers. The Form is the container, which gives room for the Splitter treating it as control. Splitter acts as a container and gives room to accommodate the TableLayoutContainer. And this Table Layout container is going to have multiple controls in it.

After doing this TableLayoutPanel is docked inside the Panel1 with the Fill Dock style. This allows the TableLayoutPanel to occupy the full area of the Panel1 of the Split Container control.

4. The usage of Document Outline view

Now it is time to know what is the usage of the "Document Outline Window". As we have nested containers it will be hard to select the container and to know the relationship between them. I mean the relationship of who is parent and who is child.

The document outline window not only shows the relation ship between the containers it also helps you to pick the specific container you want. Using this Document outline window the splitter of the Split container and TableLayoutPanel names are renamed.

Look at the above picture, which shows the document outline window. You can access it from the view menu. The outline clearly shows that Form is the topmost container and it holds the Split container. And, TableLayoutPanel is contained inside the Panel1 of the Split container.

5. Adding Rows for the TableLayout

In this step all six rows required for the TableLayout is added. First the TableLayoutPanel is selected using the Document outline view. Then using the Quick Menu item Edit Rows and Columns all six rows for the control are created. For all the six rows the size type is set as AutoSize. Autosize will resizes each cells based on the control it got. Other size mode is self-explanatory and you play around it to know how they work.

For convenience only one column is set for the control. By default actually two columns added to the TableLayout control and a column deleted and totally six rows kept.

Watch this in: 03_AddRowstoTableLayout.avi

6. Placing Control for the Panel 2

All the controls required for the Bed reservation and the Label control that tells the Bed Allocation or De-Allocation is placed in the bottom panel of the split container control. To place the control simply drags the control from the toolbox and drop it in the bottom panel of the splitcontainer control. This Quick step is shown in the below video.

Watch: 04_PlaceControlsInsidePanelBottom.avi

Note that later I added a label and the Button for making the reservation.

7. Set Auto Scroll

Set Auto Scroll property for the Panel1 of the Split container and also for the TableLayoutPanel, which is inside the Panel1. Auto Scroll for the Panel1 is not that much important for this sample. But, Autoscroll for the table is important is important as it is going to accommodate 15 check boxes in a row. So what is the need of the Auto Scroll for Panel1? Here? Nothing. But if you want to add more controls along with Table layout it will be useful. The video shows setting auto scroll for the Panel1, you should do the same thing for the TableLayout Panel too. This is shown in the video:

Watch: 05_SetAutoScrollForTopPanel.avi

8. Placing the Labels for Floors

We need three labels to display the floor number. Before placing the label set the "CellBorderStyle Property" of the TableLayoutPanel control to "Inset". Once you set, the top portion of the TableLayout is shown as some multiple lines. This is because we set the SizeType as autosize in Section 5 of this article. Once you done this Place the Label inside the open cell of the Table Layout. Then adjust the Row property. Repeat this step for other two labels also.

Watch placing and adjusting the Label: 06_Placing the Label.avi

9. Set RowSpan for the Labels

As we know the TableLayoutPanel consists of rows and columns. These rows and columns together form a cell. Each Cell in the Layout can hold only one control or container. Now we have six rows and one column. But we want to place a label for two rows as each these three labels are going to indicate a floor number and each floor is going to have two rows (North, South) of beds.

The way we can achieve this is through the Row Span in this case. If you want to combine more column for one control use Column Span. You can also use "RowSpan" and "ColumnSpan" together also. These spans are going to say how many row or column a control want to extend. In our case each label in the first column want to extend two rows. So Rowspan for each Label Control is set to 2.

The finished form is shown below. Note that some controls in the bottom panel is not shown as I added that later.

Watch: 07_UsingRowSpan.avi

Once everything is done, all the rows are set to use 15% of the Panel1 size. This will be helpful when the Panel is resized.  At present the panel is fixed using the "IsSplitterFixed Property".

Watch: 08_ChangeToPercentage.avi

Well. That’s all with the form design. Now we will go ahead and add the Beds (Checkboxes) using the code. Before we move some more interesting stuff about the "TableLayoutContainer". The "GrowStyle Property" that I does not talk still now is useful to say how the cells added to the Table layout. You can fix the number of cells by using the "RowCount" and "ColumnCount" property of the control. Once cells are fixed and Layout is full with the control, that means all the cells are occupied. The Grow style tells how should layout accommodate the new in coming control. Right. Fixed value for this property does not allow a new incoming control. Addrows and AddColumns value of the property creates new cells by adding a new row or column depending on the property value.

In our example we are going to add control by specifying the exact location. So the growstyle is set as fixed. OK. Let us go ahead and start coding. First we need to create all the check boxes on the form load and hook it to the event handler on the fly. Then implement the event handler properly.

10. Form Load Handler

In the form load event handler we are creating all the beds for each floor and tying that with the event handler. The event hooked to the handler is checkstatechanged.

1) First we are setting the growstyle property of the TableLayout to FixedSize. Then we are setting the columncount to 16. Note that we are going to place only 15 beds in a row and one column in to specify the Floor Name and that column already exists.

//TableLayout 001: First Set the Properties for the Table Layout
contTable.GrowStyle = TableLayoutPanelGrowStyle.FixedSize;
contTable.ColumnCount = 16;

2) Next a nested for loop is created. The outer one iterates through each floor and inner one iterates through each bed. The for loop is given below:

//TableLayout 002: Add check boxes to the Panel
for (int row = 0; row < 6; row++)
    for (int col = 1; col < 16; col++)

3) Inside the For loop we are creating the checkbox (Bed Slot) and setting the dock style to Fill. This will ensure that the checkbox is fully occupied inside the each cells of the TableLayout container.

//002.1 : Create Checkbox
CheckBox chkbx = new CheckBox();
chkbx.Text = col.ToString();
chkbx.Dock = DockStyle.Fill;
chkbx.AutoSize = true;

Then CheckStateChanged event of the each checkbox is hooked to the event handler AlotOrFreeBed function. Note that event handler function should always take predefined types of parameters. That’s all the check box is made ready and it is added to the TableLayoutPanel container using the Controls collection. Note that when we are adding we specify the cell location. In our case we specified the location based on the col and row local variables.

//002.2 : Hook the Checkbox event
chkbx.CheckStateChanged += new System.EventHandler(AlotOrFreeBed);

//002.2 : Add to the TableLayout
contTable.Controls.Add(chkbx, col, row);

11. AlorOrFreeBed Handler

This is handler for the CheckStateChanged event handler for the all the check boxes created in the form load.

1) First the sender is type cast to Control and stored inside the variable. Then the "GetPositionFromControl()" function of the TableLayoutPanel used to retrieve the control position in a "TableLayoutPanelCellPosition" instance.

//003.1 : Get the Row Position of the Checkbox first
Control C = (Control) sender;
int FloorNumber;
TableLayoutPanelCellPosition position = contTable.GetPositionFromControl(C);

2) The floor number is decided based on the row value taken from the position returned by the previous code snippet. The floor number is assigned to a local variable. Now we know the bed from which floor it is.

//003.2 : Get the Floor Number
if (position.Row > 1 && position.Row < 4 )
    FloorNumber = 2;
else if (position.Row < 2)
    FloorNumber = 3;
    FloorNumber = 1;

3) We are using the one more reference for same sender using CheckBox typecast here. This reference is useful for checking the Checked State of the check box. When the check box is in checked state we form a message that says bed is allocated otherwise it says bed is Freed. Also we set different background and foreground color to differentiate the allocated from the free bed. Below is the code for it:

//003.3 : Frame the information display string
CheckBox chk = (CheckBox)sender;
if (chk.Checked == true)
    chk.BackColor = Color.Yellow;
    chk.ForeColor = Color.Black;
    lblDisplay.Text = string.Format("Bed Number {0} allocated on Floor {1}", chk.Text, FloorNumber);
    chk.BackColor = this.BackColor;
    chk.ForeColor = this.ForeColor;
    lblDisplay.Text = string.Format("Bed Number {0} Freed on Floor {1}", chk.Text, FloorNumber);

12. Reserve Button Click Handler

The event routine is almost same to the previous. There is nothing much to specify her except that we are directly getting the control from the TableLayoutPanel by telling the cell position in terms of row and column. The function "GetControlFromPosition" is just a reverse of what we used in the previous event handler. Below is the code that disables the reserved bed and background is set to Red. Once bed is reserved it is in reserved state only because I have not written any code for it. You can do it.

//TableLayout 004: Reserve the Bed
private void btnReserve_Click(object sender, EventArgs e)
    //004.1 : Get the Cell Row and Cell Column from user selection. (5-x) is to get the High Floor for low index
    int Row = 5 - cboFloor.SelectedIndex ;
    int Col = cboBedNo.SelectedIndex + 1;

    //004.2 : Now get the Control from the Cell Position
    Control C = contTable.GetControlFromPosition(Col, Row);
    C.BackColor = Color.Red;
    C.ForeColor = Color.Black;
    C.Enabled = false;

Note: The sample is created using VS2005 IDE.

Source Code : Download

Saturday, August 06, 2011

SQL 2005 - Inserting Table Data Techniques - Bulk Insert

1. Introduction

We all know that to insert data into SQL server database we use the Insert statement. This article explains inserting data into the database using various techniques available. First, we will start with normal insert statements. Then we will go ahead with "Bulk Insertion" and "Temporary Table" usage. OK let us move on.

2. Insert for all the columns

Look at the below SQL statements. The first one Use Pubs; informs SQL server that the queries should be performed on the Pubs database. In the below query we are executing the insert statement on the Jobs table of the Pubs database. All we specified is the table name jobs and some list of values separated by a comma between the open and close parenthesis.

Use Pubs;

--Insert values for all column except the one taken care by SQL2005
insert into jobs(15,'Editor 2',10,100);
Select * from Jobs;

Fig 1. Jobs Table from Pubs database

In the above picture, the table Jobs actually has job_id column and we are not inserting the values in the above query for it. This is because that particular column is an auto populated key column. We will discuss that in some other article. So in the Insert statement if I am not specifying the column names, then I am inserting values for all the columns exist in that table.

Below is the result of executing the SQL Statement:

Fig 2. The inserted column

3. Inserting Data for Specific Columns

Now look at the below insert statement. In the below statement after the table name discounts, column names separated by a comma is specified within the parenthesis. Also in the values list, the values are provided in the same order in which column names are provided in the column list. If the value is a text string, a single quote is used to enclose the string.

Use the Pubs database.

--Insert data to a specific column
insert into discounts(discounttype, discount)
values('Seasonal Discount', 11.15);
Select * from Discounts;

Below is the result of executing the Query:

Fig 3. Data inserted selectively for two columns

4. "Insert into ... Select" technique

Before we go, first we will create a table for inserting the data using "Insert into...Select" technique. Below is the table create statement:

      discounttype varchar(40),
      discount decimal(4, 2) NOT NULL

We just created a Distype table with two columns. And we are going to insert data into this table from the existing Discounts table in the Pubs database.

Now look at the below insert statement:

Insert into DisType(Discounttype,discount)
Select DiscountType, Discount from Discounts;

Here, we specified the target table DisType as the insertion target and also specified the columns in that table for that insertion data being supplied. Instead of passing the values manually we specified a source table to pick data. As we are looking values for only two columns DiscountType, Discount in the target table DisType, those two columns in the source table Discounts is retrieved through select statement. Note that it does not require that column name should match. But the data type should be matched for smooth insertion of data from source to destination table.

5. Bulk insert by creating Destination table

Consider the below SQL statement. Even though the statement looks like a select statement actually it performs two tasks. 

  1. It first creates the destination table disTypeOneMore
  2. It queries the Source table Discounts and inserts the resultant data into the destination table just now created.

The column names and data type of the "Destination Table" is taken from the select statement of the "Source Table" Discounts.

Select discounttype , discount
into DisTypeOneMore
From Discounts;

Select * from DisTypeOneMore;

Note: The tables involved in the examples are part of the pubs database that Microsoft ships as sample db.
Like this site? Tell it to your Firend :)