How to connect to a Microsoft SQL Server database in Visual Studio 2010

Published:
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is more about using the Visual Studio environment to view databases on a SQL Server installation if you don’t have any database tools such as SQL Server Management Studio.  

The most useful reason you may want to do this is that once you do establish your data connection in Visual Studio, you can use the drag-and-drop data functionality in Visual Studio for SQL Server.

A handy tip:  many of these dialogs and menus are the same or very similar in Visual Studio 2008 and 2005.

1)  Open Visual Studio

2)  Click the Server Explorer tab from the left-hand pane (where you see the Toolbox--at the bottom of that pane you will see "Server Explorer")  

3)  Right-click Data Connections and choose Add Connection… from the context menu:  

4)  In the Choose Data Source window, click Microsoft SQL Server.  

You can double-check the dropdown to be sure it says .NET Framework Data Provider for SQL Server if you’d like, but that is the default.
 
Note:  there’s also an “Always use this selection” option in the lower-left-hand corner of this window.  If you think you’ll ever be using OLE DB connections, you might want to leave this unchecked…otherwise, checking it can save you some time in the future…

Click Continue.  The 'Test Connection' pane will open.
5)  The 'Test Connection' pane is is the most useful part:

a)  Server Name lets you type the name of the machine hosting SQL Server, or click the dropdown and you will see a list of all the visible instances of SQL Server on your network.

b)  Log on to the server lets you specify which method is used to access the database.  Typically this is Windows Authentication, but your DBA will be able to help you if you’re unsure.

c)  Select or enter a database name will let you type in the name, or you can click the dropdown and select it from among the databases that were found on the SQL Server.

d)  Test Connection is the best part – use it to confirm that your connection will work.  When you click it, you should either get "Test Connection Succeeded" or an error message indicating the problem.

e)  Click OK and you will be dropped back into Visual Studio
6)  You should now be able to browse the database to your heart’s content!
 
Just use the plus signs next to each element to open/close the item and view objects on the server.

NOTE:  The attached PDF includes screenshots...if this text isn't helpful, consider opening the attachment for a visual walkthrough.
SQLServerConnectionInVisualStudi.pdf

Hope this Article helps you with your database projects.
1
4,954 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.