How to use Server Explorer and Query Designer walkthrough (317752)

SUMMARY

The information in this article applies to Microsoft Visual Basic .NET or Microsoft Visual Basic 2005 Enterprise Edition and Microsoft Visual Basic .NET or Microsoft Visual Basic 2005 Architect Edition. The following steps describe how to work with some of the new key Microsoft Visual Database Tools in Visual Studio .NET or Visual Studio 2005. This article describes how to create a Database Project, generate a Create Script, and how to create and run a Stored Procedure script to add it to the database by using the Northwind database.

back to the top

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you will need.

• SQL Server 7.0 or later with the Northwind database.
• Microsoft Visual Studio .NET Enterprise or Architect Editions.

Applies To:

• Visual Studio.NET or Visual Studio 2005
• Visual Basic .NET or Visual Basic 2005
• C#
• SQL Server

Prior Knowledge required:

• None, but familiarity with SQL Enterprise Manager is helpful.

back to the top

Visual Studio .NET or Visual Studio 2005 Productivity-Visual Database Tools

The following steps describe how to work with some of the new key Visual Database Tools in Visual Studio .NET or in Visual Studio 2005. This article describes how to create a Database Project, generate a Create Script, and how to create and run a Stored Procedure script to add it to the database by using the Northwind database.

1. Click Start, point to Programs, point to Microsoft Visual Studio .NET, and then click Microsoft Visual Studio .NET.
   
   Note In Visual Studio 2005, click Start, point to Programs, point to Microsoft Visual Studio .NET, and then click Microsoft Visual Studio 2005.
2. Press CTRL+ALT+S to open Server Explorer.
3. In Server Explorer, right-click Data Connections, and then click Add Connection.
4. In the Data Link Properties dialog box, type localhost as the server name in the first drop-down list box.
5. In the User name field, type sa, click to select the Blank Password check box, click Northwind in the bottom drop-down list, and then click OK.
6. In Server Explorer, click Data Connections to view the connection to the Northwind database.
7. Click the Northwind connection to view the various database objects that you now have access to.
8. On the File menu, point to New, and then click the Project menu.
9. Click Other Projects, and then click Database Projects.
10. In Templates, click Data Project.
11. In the Name field, type DBHowTo, and then click OK.
12. In the Add Database Reference dialog box, select the Northwind connection that you just created, and then click OK.
13. In Solution Explorer, right-click the Create Scripts folder, and then click Generate Create Script.
14. In the SQL Server Login dialog box, click OK.
15. In the Generate Create Scripts dialog box, select the first object that is listed, click Add, and then click OK.
16. In the Browse for Folder dialog box, verify that the Create Scripts folder is selected under the DBHowTo folder, and then click OK.
17. Click OK when the Scripting message box appears.
    
    NOTE: A script to create the object that you selected appears under the Create Scripts folder in Solution Explorer.
18. In Solution Explorer, right-click the Queries folder, and then click Add New Item.
19. In Templates, click Stored Procedure, type spGetTerritoryRegions in the Name field, and then click Open.
20. Right-click in the SQL Block, and then click Design SQL Block.
21. In the Diagram pane, right-click the sysobjects table title bar, and then click Remove.
22. Right-click the Diagram pane, and then click Add Table.
23. Press and hold the CTRL key while you click both the Region and Territories tables to select both, click Add, and then click OK.
24. Click the gray area to the left of the asterisk (*), and then press the DELETE key. Note that if you do not do this, the individual fields that you select will appear in the Grid pane with an alias.
25. In the Territories table, click to select the TerritoryDescription check box.
26. In the Region Table, click to select the RegionDescription check box.
27. Click Run Query on the Query toolbar or right-click any of the panes, and then click Run to run the SQL statements.
    
    The Results pane fills with all of the employees and their associated territories and regions.
28. Press CRTL + S to save the query.
29. Above the Diagram pane, click the \Queries\spGetTerritoryRegions.sql (DBHowTo) tab.
30. In the PRINT statement, select Stored_Procedure_Name, and then press CTRL + H.
31. In the Replace with field, type spGetTerritoryRegions, click Replace All, click OK, and then click Close.
32. Press CTRL + S to save the query.
33. In Solution Explorer, right-click spGetTerritoryRegions, and then click Run.
    
    This adds the stored procedure to the database, but does not output any result set.

back to the top

Verification it Works

1. A confirmation that the script executed appears in the Database Output window.
2. In Server Explorer, click Stored Procedures. spGetTerritoryRegions is not listed until you click Refresh at the top of Server Explorer.

back to the top

Troubleshooting

This article assumes that your SQL Server is using mixed security and is configured with a blank SA password. If it is not or you want to use integrated security, modify step 5 in the "Visual Studio .NET Productivity-Visual Database Tools" section appropriately.

back to the top

REFERENCES

For more information about Visual Database Tools, visit the following the MSDN Help Documentation.

• Overview of Visual Database Tools:

  http://msdn.microsoft.com/library/en-us/vsintro7/html/vxoriManagingProjectsSolutions.asp

• Database Queries and Visual Database Tools:

  http://msdn.microsoft.com/library/en-us/vdbt7/html/dvconquerydesignvisualdatabasetools.asp

back to the top