Working with Views within Client Applications

By accessing views from client applications instead of accessing base tables directly, you can hide the database design complexity of client applications. When you work with a view from within a client application, there is no difference between the view and a table. You can query the view by using a SQLCommand object and navigate through the result set with a SQLReader object. The following example demonstrates how to query a view using Visual Basic.NET. You can access this sample code from \Ch08\SampleCh0813.vb.

 ' Create an instance of a SQLConnection object Dim oConn as New SQLClient.SQLConnection ' Create an instance of a SQLCommand object Dim oCmd as New SQLClient.SQLCommand ' Create an SQLReader object Dim oReader as SQLClient.SQLReader ' Define the connection string oConn.ConnectionString= _     "server=localhost;database=AdventureWorks;Integrated Security=SSPI" ' Define command properties oCmd.Connection=oConn oCmd.CommandText="SELECT * FROM Sales.vSalesPerson" ' Open the connection oConn.Open() ' Execute the command oReader=oCmd.ExecuteReader While oReader.Read    ... <Process record by record> End While ' Close the connection oConn.Close() 

You can also create a data source within Visual Studio that receives data from a view.

Creating a Data Source with Visual Studio 2005

1. Start Visual Studio 2005. From the Start Menu, choose All Programs Microsoft Visual Studio 2005 Microsoft Visual Studio 2005.

2. From the File menu, choose New Project.

3. In the New Project dialog box, select the Windows Application template in the Visual Basic project types and click OK.

4. From the Data menu, choose Show Data Sources.

5. Click the Add New Data Source toolbar button in the Data Sources window. The Data Source Configuration Wizard appears.

6. Select Database and click Next.

7. In the Choose Your Data Connection step, click the New Connection button.

8. In the Add Connection dialog box, specify your SQL Server instance in the Data Source and Server Name boxes. Select the AdventureWorks database under the Select Or Enter A Database Name option of the Connect To A Database frame. Click OK.

9. In the Choose Your Data Connection step, click Next.

10. In the Save The Connection String To The Application Configuration File step, uncheck the Yes checkbox and click Next.

11. In the Choose Your Database Objects step, expand the Views node, select the vEmployee view, and then click Finish.

    

12. In the Data Sources window, select the vEmployee node, which will change to a combo box. Click the down arrow and select Details.

13. Drag and drop vEmployee from the Data Sources window to Form1.

14. Run the application. You can see data from the view and can navigate through the data, as shown below.