A view is a virtual table that represents data from one or more than one database table. You can select data from a single or multiple tables based on the sort and filter criteria (using the WHERE and GROUP BY clauses) and save data as a view. You can also set permissions on views. For example, a manager, an accountant, and a clerk of a company share the same database. The accountant can access partial data from multiple tables, and the clerk can access partial data from a single table. You can create three different views based on these user rights and let the user's access these views based in their rights.
Creating a View
Similar to stored procedures, you can create and manage views from the server explorer. To create a view, you can expand a database, right-click on the Views leaf, and select the New View option. This action launches the wizard, which lets you pick tables. The Add button adds tables to the view designer (see Figure 10-17).
Figure 10-17: Adding tables to the view designer
I added three tables to the designer: Customers, Order, and Employees. I selected only a few columns from each table (see Figure 10-18).
Figure 10-18: Creating a view after selecting columns from three tables
Now you can save a view by using the save buttons or menu or by right clicking on the view and selecting the save option (see Figure 10-19).
Figure 10-19: Saving a view
I called the view CustEmpView and clicked OK (see Figure10-20).
Figure 10-20: Click OK to save a view.
Now when you see the Server Explorer views, you'll see CustEmpView listed (see Figure 10-21).
Figure 10-21: Available views in the Server Explorer
Executing Views from VS .NET
Now you can execute a view by right-clicking on one in the Server Explorer and selecting the Retrieve Data from the View option. The output of the CustEmpView looks like figure 10-22.
Figure 10-22: Results of the CustEmpView after executing it from the Server Explorer
Retrieving data from a view programmatically
Similar to the stored procedures, the command object executes a view. You can retrieve data from a view programmatically by replacing the view name as the table name in a SQL statement. Listing 10-5 shows you how to use the CustEmpView in an application. As you can see from the code, I've used the CustEmpView view as table name in the SELECT statement:
SELECT * FROM CustEmpView
To test this code, create a Windows application in VS.NET, add a DataGrid control to the form, and write the code in Listing 10-5 on the Form_Load event. Also don't forget to add a reference to the System.Data.SqlClient namespace in the project.
Listing 10-5: Executing a view programmatically
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
' Create a connection object
Dim ConnectionString As String = "Integrated Security=SSPI;" & "Initial Catalog=Northwind;" & "Data Source=localhost;"
Dim conn As New SqlConnection(ConnectionString)
Dim adapter As New SqlDataAdapter("SELECT * FROM CustEmpView", conn)
Dim ds As New DataSet("CustEmpView")
dataGrid1.DataSource = ds.DefaultViewManager
The output of Listing 10-5 looks like figure 10-23.
Figure 10-23: The output the CustEmpView view from a program
Hope this article would have helped you in understanding working with Views. See other articles on the website also for further reference.