Data Analysis

Data analysis consists of visiting the values stored in a database, for any reason judged necessary. It can be performed to check the accuracy of a series of values, to apply new changes to one or more records, to look for the existence or state of a record, or else. Microsoft SQL Server itself is equipped with all the tools, including visual tools, needed to perform data analysis. If you want to perform data analysis in a Windows Forms Application, you must know what the SQL offers so you can apply the SQL as a language to your programming language and use these in your Windows controls.

If you are working in the SQL Server Enterprise Manager, before performing data analysis, you should first open the table in a view that shows its records. To do this, after locating the desired table, you can right-click it, position the mouse on Open Table, and click Return All Rows. If you are working with the Server Explorer, after locating the table, you can double-click it or right-click the table and click Retrieve Data From Table. In both cases, after displaying the table, to access the view that allows data analysis, click the Show SQL Pane button . If all cases, including the SQL Query Analyzer or a Windows Forms Application, to perform data analysis, you must write a SQL statement.

After writing the statement, you must execute it. If you are working with a table from the SQL Server Enterprise Manager or one from the Server Explorer, to execute the statement, you can click its Run button .

If you are working in the SQL Query Analyzer, to execute a statement, you can click the Execute Query button or press F5.

If you are working in a Windows Forms Application, to perform data analysis, the .NET Framework provides the DataView class. The DataView class is defined in the System.Data namespace of the System.Data.dll library. To use a DataView object in your application, you can declare a pointer to DataView using one of its three constructors. The default constructor allows you to declare the variable without giving its details. As an alternative to declaring a DataView variable, Microsoft Visual Studio .NET provides the DataView button in the Data section of the Toolbox. You can click it and click your form. This is equivalent to declaring a DataView variable using the default constructor, except that this variable would be available at the class level.

• Start Microsoft Visual Studio .NET or Microsoft Visual Basic .NET and create a Windows Forms Application named ROSH2

In order to perform any type of data analysis, you must specify the table and the columns that would be involved. This is done using the same type of SELECT statement we introduced for the data adapter in the previous lesson. The fundamental formula of selecting columns of a table is:

SELECT What FROM TableName

Based on this formula, you use the TableName to specify the name of the table that holds the records you want to retrieve. The table must exist in the database you specified when establishing your connection. If you want to use the records of only one column, you can specify its name as the What placeholder of our formula. Here is an example:

If you want to use more than one column, separate them with commas. Here is an example:

If you want to use all columns of the table, replace the What placeholder of our formula with *. Here is an example:

If you are working in a Windows Forms Application and plan to perform data analysis, after declaring a DataView variable, the first action you should take is to specify the table that holds the records you want to examine. If you have declared the DataView variable using its default constructor, to specify the table, you can assign the name of the database table to the Table property of the DataView object. Alternatively, you can specify the table when declaring the DataView class. To do this, you can use the following constructor:

Public Sub New(ByVal table As DataTable)

As you can see, this constructor expects a pointer to a DataTable as argument. After declaring the DataView variable, the records stored in the table argument would accessible for operations performed using the DataView object. The name of the table can also be specified in the Table field of the Properties window for the DataView object.

1. Make sure the form is displaying.
   In the Server Explorer, expand the name of the computer, followed by the SQL Servers node, followed by the name of the server, followed by the ROSH nodes, followed by the Tables node.
2. In the Tables node of the ROSH database, drag the Students node and drop it on the form
3. To create the associated DataSet object, on the main menu, click Data . Generate Dataset...
4. In the Generate Dataset dialog box, accept the New radio button. Change the name to dsROSH and click OK
5. In the Data section of the Toolbox, click the DataView button and click the form
6. In the Properties, change the (Name) to dvwStudents and click the Table field
7. Click the arrow of the Table field. Click the + button of dsROSH1 and click Students
8. Save all