Home

Fundamentals of Data Selection

 

Introduction

After creating a table and populating it with records, you may want to see what you can do with data stored in it. One of the most commonly performed operations by the users of a database is to look for data or to isolate data that responds to a particular criterion. Looking for data that is conform to a criterion is referred to as querying. The result of retrieving data based on a criterion is called a query.

As a database developer, you perform queries by passing instructions to the database engine. This is done using some special reserved words. You can perform data analysis using the Microsoft SQL Server Management Studio, a query window in Microsoft SQL Server Management Studio, a query window in Microsoft Visual Studio, a Windows application.

 
   

Practical LearningPractical Learning: Introducing Data Selection

  1. Start Microsoft Visual C# and create a Windows Application named SolasPropertyRental6
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type RentalProperties.cs and press Enter.
    If you followed the previous lesson, move to the next step. If not, follow the instructions in the previous lesson to create the SolasPropertyRental1 database
  4. Design the form as follows:
     
    Solas Property Rental: Form Design
    Control Text Name Other Properties
    DataGridView   dgvProperties Anchor: Top, Bottom, Left, Right
    GroupBox Fields to Show grpFieldsToShow Anchor: Bottom, Left, Right
    RadioButton Show all Fields rdoShowAllFields  
    RadioButton Show Only rdoShowSomeFields  
    CheckedListBox   clbColumns CheckOnClick: True
    MultiColumn: True
    Anchor: Bottom, Left, Right
    Button Execute btnExecute Anchor: Bottom, Right
    Button Close btnClose Anchor: Bottom, Right
  5. Double-click the checked list box and implement its event as follows:
     
    private void clbColumns_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (clbColumns.CheckedItems.Count < 1)
    	rdoShowAllFields.Checked = true;
        else
    	rdoShowSomeFields.Checked = true;
    }
  6. Execute the application to preview the form
  7. Close the form and return to your programming environment

The Data in the Table Window

To visually analyze data in Microsoft Visual Studio, in the Server Explorer, you can right-click a table and click Show Table Data. By default, when you open a table, the Query Designer toolbar comes up also:

Query Designer Toolbar

Once the table is opened, on the main menu, you can click Query Designer. Alternatively, you can right-click anywhere on the table. In both cases

  1. On the menu that appears, position the mouse on Pane and click Diagram
  2. Once again, open the Pane menu and click Criteria
  3. Again, open the Pane menu and click SQL:

The Data in the Table Window

Alternatively, on the Query Designer toolbar, you can click the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, and the Show SQL Pane button Show SQL Pane.

The Table window is divided in four sections:

  • Diagram: The top section is referred to as the Diagram window. It displays the table(s) that contain(s) the columns you want to query. Each column displays a check box on its left and the name of the column on the right. The first item of the list has a name made of an asterisk and the (All Columns) expression.
    If the list of items is too long for the allocated rectangle to display, the table would be equipped with a vertical scroll bar.
  • Criteria: Under the table, the second section is called Criteria. It displays a list of columns used to visually build the SQL statement.
  • SQL: The third section from top, called SQL, displays the SQL statement that results from selections in the Diagram or the Criteria sections
  • Results: The bottom section, called Results, displays the result produced by the SQL statement when it is executed

If you do not want a particular section or you want to hide some sections, you can right-click anywhere in the table, position the mouse on Pane and click the name of the section. To hide any section, you can right-click anywhere in the window, position the mouse on Show Panes and click one of the selections:

Using Panes

When a section is displaying, its menu option is surrounded.

Column Selection

A SQL statement is primarily built by selecting one or more columns whose data you want to view. To select a column, in the Diagram section of the Table window, you can click the check box on the left side of the name:

Column Selection

After clicking the check box of a column, it becomes selected in the Criteria section also and its name appears in the SQL section. Another technique used to select a column consists of clicking a box under the Column header of the Criteria section. This would reveal that it is a combo box. You can then click the arrow of the combo box to display the list and select a column from that list:

Selecting a Column

In the Criteria section, if you click a combo box that already contains a column but select another, the previous one would be replaced by the new one. Also, after selecting a column in the Criteria section, its check box becomes selected in the Diagram section and its name gets added to the SQL expression. If you know the name of a column that you want to add, which you can see in the Diagram section, you can directly enter it in the SQL statement.

Any of the above three techniques allows you to select one or more columns to build the desired SQL statement.

SQL Statement Execution

After creating a SQL statement, you can view its result, which you can get by executing the statement. To do this, you can right-click anywhere in the Table window and click Execute SQL. Alternatively, on the Query Designer toolbar, you can click the Execute SQL button Execute.

After executing the statement, the bottom section gets filled with data from only the selected column(s) of the table. Here is an example:

Query Execution

Published on Friday 04 January 2008

 

Home Copyright © 2007 FunctionX, Inc. Next