A Data Source

Introduction

 

Practical Learning: Introducing Visual Database Support

1. Start Microsoft Visual Basic and create a Windows Application named spr1
2. In the Solution Explorer, right-click Form1.vb and click Rename
3. Type RentalProperties.vb and press Enter
4. Double-click the middle of the form and implement the Load event as follows:
    

   Imports System.Data.SqlClient Public Class RentalProperties Private Sub RentalProperties_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Using cnnSolasPropertyRental As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Integrated Security='SSPI';") Dim strSolasPropertyRental As String = _ "CREATE DATABASE SolasPropertyRental1;" Dim cmdSolasPropertyRental As SqlCommand = _ New SqlCommand(strSolasPropertyRental, _ cnnSolasPropertyRental) cnnSolasPropertyRental.Open() cmdSolasPropertyRental.ExecuteNonQuery() MsgBox("The SolasPropertyRental database has been created") End Using Using cnnSolasPropertyRental As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='SolasPropertyRental1';" & _ "Integrated Security='SSPI';") Dim strSolasPropertyRental As String = _ "CREATE TABLE RentalProperties " & _ "( " & _ " RentalPropertyID int identity(1,1) NOT NULL, " & _ " PropertyCode char(7) NULL, " & _ " PropertyType varchar(32) NULL, " & _ " Bedrooms tinyint, " & _ " Bathrooms float, " & _ " MonthlyRent smallmoney, " & _ " OccupancyStatus varchar(30) " & _ ");" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('527-992', 'Apartment', 1, 1.00, 925.00, 'Available');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('726-454', 'Apartment', 2, 1.00, 1150.50, 'Available');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('476-473', 'Single Family', 5, 3.50, 2250.85, 'Occupied');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('625-936', 'Townhouse', 3, 2.50, 1750.00, 'Available');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('179-768', 'Townhouse', 4, 2.50, 1920.00, 'Available');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('727-738', 'Single Family', 4, 2.00, 2140.50, 'Needs Repair');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('371-801', 'Apartment', 3, 2.00, 1250.25, 'Available');" & _ "" & _ "INSERT INTO dbo.RentalProperties(PropertyCode, PropertyType," & _ " Bedrooms, Bathrooms, MonthlyRent, OccupancyStatus)" & _ "VALUES('241-536', 'Townhouse', 3, 1.50, 1650.50, 'Occupied');" Dim cmdSolasPropertyRental As SqlCommand = _ New SqlCommand(strSolasPropertyRental, _ cnnSolasPropertyRental) cnnSolasPropertyRental.Open() cmdSolasPropertyRental.ExecuteNonQuery() MsgBox("The RentalProperties table has been created") End Using End Sub End Class

5. Execute the application
6. Close the form and return to your programming environment
7. Delete the whole code in the Load event

Instead of using the DataSet object from the Toolbox, Microsoft Visual Studio provides a technique that allows you to automatically get a data set object by creating a connection to a database. The data set would be filled with the tables from the database. To use it, you can first display the Data Source window. To display the Data Source, on the main menu, you can click Data -> Show Data Sources.

To create a data source:

• On the main menu, you can click Data -> Add Data Source...
• In the Data Source window:
  • You can click the Add New Data Source button
  • You can right-click a blank area in the window and click Add New Data Source
  • If the Add New Data Link appears (that is, if the current application does not have a data source yet), you can click it

The first page of the Data Source Configuration Wizard allows you to specify the type of data source you want to create: Database, Web Service, and Object

If you click Database and click Next, the second page of the wizard allows you to select an existing connection or create a new one. To select an existing connection, you can click the arrow of the combo box and select from the list:

Select the connection and click Next. If you click the New Connection button, you would then have to select the server, the authentication, and the database.

In the third page of the wizard, you would specify a name for the connection string, and click Next.

In the fourth page of the wizard, you have the option of selecting one or more tables (and other types of objects) to include in your data set. To do this, you can click the check boxes in the list. If you do not specify the tables, you can click Finish and, later on, you can reconfigure the data source and select the tables (and/or other types of objects). After making your selection, you can click Finish.

1. On the main menu, click Data -> Add New Data Source...
2. On the first page of the wizard, make sure Database is selected and click Next
3. In the second page of the wizard, click New Connection...
   If the Choose Data Source comes up, click Microsoft SQL Server. In the Data Provider combo box, select .NET Framework Data Provider for SQL Server
    
   
    
   Click Continue
4. In the Server Name combo box, select the server or type (local)
5. In the Select or Enter a Database Name combo box, select SolasPropertyRental1
6. Click Test Connection
    
   
7. Click OK twice
8. On the Data Source Configuration Wizard, make sure the new connection is selected
   Click the + button of Connection String
    
   
9. Click Next
10. Change the connection string to strSolasPropertyRental and click Next
11. Expand the Tables node and click the check box of RentalProperties
12. Change the name of the data set to dsSolasPropertyRental
     
    
13. Click Finish

When you click the Finish button of the Data Source Configuration Wizard, Microsoft Visual Studio generates many classes (XML Schemas) and creates a data set object specially made and configured for your database. Practically, the studio would create a class named after the name you gave to the data set and this class would be derived from the DataSet class. To examine this created class, from the Solution Explorer, you can open the file that holds the name of the data set followed by .Designer.vb.

Among the objects created in the data set class is a class that represents the table (or each table) you selected in the last page of the wizard. This class for the table is derived from the DataTable class and implements the System.Collections.IEnumerable interface. In order to use this new table in your code, you must declare a variable for it. Once you have done that, you can access the characteristics (properties and methods) of the table or its parent.

Although the data set created from the Toolbox and the one generated from creating a data source have many differences, they still share the common definition of being data sets. As mentioned earlier, a data set created from adding a data source contains the table(s) (including its (their) column(s) and record(s), if any) you would have selected.  This allows you to access any of the characteristics we studied for a data set.

Microsoft SQL Server does not provide user-friendly objects that a user can use to perform the necessary operations of a regular application. That is why you use Microsoft Visual Studio to create an application made of good looking Windows controls. The controls of a Windows application are meant to serve all types of applications, not just databases. If you want a Windows control of your application to use the values of your database, you must create a type of link between the control and the column of a table. This process is referred to as binding. The object that serves this connection is referred to as a binding source.

To support binding sources, the .NET Framework provides the BindingSource class from the System.Windows.Forms namespace. To visually create a binding source, from the Data section of the Toolbox, you can click BindingSource and click the form or container of your application. Because it is a non-visual object, its label would be positioned under the form. You can then specify its characteristics in the Properties window.

To programmatically create a binding source, you can declare a variable of type BindingSource. The class is equipped with three constructors. The default constructor allows you to simply declare the variable. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim bsNames  as BindingSource = new BindingSource
End Sub

If you create a binding source, obviously you must give it a name. If you create it from the Toolbox, you can accept or change its name in the Properties window. Besides its name, the second most important detail of a binding source is the list that holds its values. This list is referred to as the data source. To make the binding source as flexible as possible, the data source can be almost any type of list, including an array. In reality, any class that implements the IList interface is a candidate to be a data source.

To support data sources, the BindingSource class is equipped with a property named DataSource, which is of type Object. The idea of using the vague Object type indicates that many types, but not all types, of objects or lists can be used as data sources.

To programmatically specify the data source of a binding source, first create your list, then assign it to the DataSource property of your BindingSource object. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim bsNames As BindingSource = New BindingSource
    Dim strNames As List(Of String) = New List(Of String)

    strNames.Add("Vicky Bisso")
    strNames.Add("Amy Warren")
    strNames.Add("Chrissie Childs")
    strNames.Add("Martial Otto")

    bsNames.DataSource = strNames
End Sub

To visually specify the data source of a binding source, access the Properties window of your BindingSource object. In the Properties window, click DataSource and click the arrow of its combo box:

• If you had not previously created a data source, you can click Add Project Data Source... This would launch the Data Source Configuration Wizard that allows you to create and configure a data set
• If you had previously created a data set object, you can expand the Other Data Sources node and expand the Project Data Sources node. You would see the created data set and you can select it. Here is an example:
   
  

  

1. In the Data section of the Toolbox, click BindingSource and click the form
2. In the Properties window, change its name to bsSolasPropertyRental
3. Click DataSource and click the arrow of its combo box
4. Expand the Other Data Sources node and the Project Data Sources node
5. Click dsSolasPropertyRental

After creating a data set and a data source, the database that holds the values is ready to make them available to your application. The next step is to indicate to each control where its data would come from. To assist you with this task, Microsoft Visual Studio includes a tool that allows you to create an object that can retrieve the values (data) from a table and make them available to the Windows controls. This object is called a table adapter.

There are various ways you can create a table adapter:

• If you had added a data source to your application and created a binding source, to create a table adapter, under the form, you can click the binding source object. In the Properties window, click the DisplayMember field and, from its combo, select the name of the table:
   
  
  
  After doing this, Microsoft Visual Studio would create a table adapter. You can then accept or change its name
• Probably the best way to create a table adapter consists of generating it from the Data Source window. To do this, after creating a data source, from the Data Source window, you can drag the table node and drop it on the form
   
  
   
  After dropping the table, Microsoft Visual Studio would create both a data set and a table adapter.

1. While the binding source is still selected under the form, in the Properties window, click DataMember and click the arrow its combo box to select RentalProperties
2. Under the form, click rentalPropertiesTableAdapter and, in the Properties window, change its Name to tadRentalProperties
3. Save the form

If you drag a table from the Data Source window and drop it on a form, Microsoft Visual Studio adds the necessary Windows controls to the form and binds them to the columns of the table. To move from one record to another, you would need a way to navigate among the records. You can manually take care of this if you want. Alternatively, the .NET Framework provides a class named BindingNavigator that contains all the necessary functionality for this task.

There are various ways you can create a binding navigator:

• You can declare a variable of type BindingNavigator and configure it
• From the Data section of the Toolbox, you can drag a BindingNavigator object and drop it on a form. You should then access the Properties window for the binding navigator. The BindingNavigator class has a property named BindingSource. You can access it from the Properties window of the binding navigator and select the binding source
• If you drag a table from the Data Source window and drop it on a form, Microsoft Visual Studio would create and configure a binding navigator for you

1. In the Data section of the Toolbox, click BindingNavigator and click the form
2. In the Properties window, change its Name to bnRentalProperties
3. Still in the Properties window, click BindingSource and select bsSolasPropertyRental
4. In the Data section of the Toolbox, click DataGridView and click the form
5. Still in the Properties window, click DataSource and select bsSolasPropertyRental
6. Under the Properties window, click Edit Columns and configure the columns as follows:
    

   Column	HeaderText	Width
   RentalPropertyID	Prop ID	50
   PropertyCode	Prop Code	70
   PropertyType	Property Type	90
   Bedrooms	Beds	50
   Bathrooms	Baths	50
   MonthlyRent	Monthly Rent	80
   OccupancyStatus	Status	90

7. In the Properties window, change the following characteristics:
   (Name): dgvRentalProperties
   ColumnHeadersHeightSizeMode: EnableResizing
   Anchor: Top, Bottom, Left, Right
    
   
8. Execute the application to see the result
   
   
9. Close the form and return to your programming environment

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 selection 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.

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:

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:

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

The Table window is divided in four sections:

• Diagram: The top section is referred to as the Diagram window or the Diagram section. 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 and/ 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:

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

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:

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:

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.

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 .

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

Data selection is actually performed using SQL code that contains one or more criteria. To prepare for data selection, you have various options:

• If you have not yet decided what table holds the data you want to analyze, in the Object Explorer (Microsoft SQL Server Management) or Server Explorer (Microsoft Visual Studio), you can right-click a table and click New Query. Then, in the query window, enter the necessary SQL statement
• If you know the table that holds the data you want to analyze, in the Object Explorer of the Microsoft SQL Server Management, right-click the table, position the mouse on Script Table As, followed by SELECT To, and click New Query Editor Window. A query window would be opened with sample SQL code

After entering the SQL statement, you can execute it to see the result. In Microsoft SQL Server Management Studio, this would display the Table window. The result would be displayed in the bottom section. There are two ways you can display the result. To have access to these options, you can first display the SQL Editor toolbar. In Microsoft SQL Server Management Studio, to display the SQL Editor toolbar:

• On the main menu, you can click View -> Toolbars -> SQL Editor
• You can right-click any toolbar and click SQL Editor

In Microsoft SQL Server Management Studio, to specify how you want to show the results of your SQL statement, you have two options:

• To show the result as text, on the SQL Editor toolbar, you can click the Result To Text button . Alternatively, you can right-click somewhere in the table, position the mouse on Results To, and click Results To Text.
  
  The results would appear in two columns of text. Here is an example:

• To show the result as a spreadsheet, on the SQL Editor toolbar, you can click the Result To Grid button . Alternatively, you can right-click somewhere in the table, position the mouse on Results To, and click Results To Grid.
  
  The results would appear as a spreadsheet of one or various columns. Here is an example:

In either the Table window or the query window, you are expected to write appropriate code that would constitute a SQL statement.