Introduction to ADO.NET Visual Tools
A Data Source
So far, we have introduced and used the primary tools that Microsoft SQL Server provides to create a database. We hardly did anything visually. To make database development user friendly and graphically-driven, Microsoft Visual Studio provides its own set of tools. Some of these tools are available from the Toolbox. Some other tools are provided as classes you can use as long as you are aware of them.
In Lesson 15, we defined a data set as a system of values. The values are kept in one or more lists. We also saw that, to support this system, the .NET Framework provides a class named DataSet. This class is represented in the Data section of the Toolbox of Microsoft Visual Studio by the object of the same name.
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:
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.
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:
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 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:
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
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:
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:
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:
In Microsoft SQL Server Management Studio, to specify how you want to show the results of your SQL statement, you have two options:
In either the Table window or the query window, you are expected to write appropriate code that would constitute a SQL statement.
|Home||Copyright © 2008-2016, FunctionX, Inc.|