How to: Connect to Data in a Database

You can use Visual Studio to connect your application to a database. After creating the data connection, Visual Studio generates a data model that your application uses to interact with the data in the database. The objects in the data model appear in the Data Sources Window. You can then create data-bound controls by dragging items from the Data Sources Window to a design surface. For more information, see Binding Controls to Data in Visual Studio.

This topic provides instructions for connecting to a database and creating the following types of data models:

• Dataset

• Entity Data Model (EDM)

Note

You can also use Visual Studio to create LINQ to SQL classes from a database. However, LINQ to SQL classes do not appear in the Data Sources window, and therefore cannot be dragged directly to a designer to create data-bound controls. For more information about creating LINQ to SQL classes from a database, see How to: Create LINQ to SQL Classes Mapped to Tables and Views (O/R Designer).

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Connecting to a Database and Creating a Dataset

When you create a dataset that is based on a database, Visual Studio creates a set of classes that represent a programmable view of the data. The main class is called a typed dataset. The typed dataset contains data table objects that represent tables in the database. For more information about typed datasets, see Working with Datasets in Visual Studio.

After you create a dataset, you can create data-bound WPF or Windows Forms controls by dragging dataset objects from the Data Sources window to the WPF or Windows Forms designer.

To connect your application to a database and create a dataset

1. Open an existing project in Visual Studio, or create a new project.

2. On the Data menu, click Add New Data Source.

   The Data Source Configuration Wizard opens.

3. On the Choose a Data Source Type page, select Database, and then click Next.

4. On the Choose a Database Model page, select Dataset, and then click Next.

5. On the Choose Your Data Connection page, select a data connection from the list of available connections and then click Next.

   If your desired data connection is not available, create a new connection by following the steps in Creating a New Database Connection.

6. On the Save the Connection String to the Application Configure File page, optionally clear the Yes, save the connection as check box if you want to save the connection string directly in the compiled application. By default, the connection is saved in the application configuration file. For more information, see How to: Save and Edit Connection Strings.

7. On the Choose Your Database Objects page, select the database objects that you will use in your application. You also have the option of replacing the default DataSet name.

8. Click Finish. The dataset you just created is now available in the Data Sources window.

   Note

   If the Data Sources window is not open, click Show Data Sources on the Data menu to open the window.

9. You can now drag items from the Data Sources window to the WPF designer, the Windows Forms designer, or the Component Designer to create data-bound controls. For more information, see Binding Controls to Data in Visual Studio.

Connecting to Database and Creating an Entity Data Model

When you create an Entity Data Model that is based on a database, Visual Studio creates a set of classes that represent a programmable view of the data. For more information about Entity Data Models and the ADO.NET Entity Framework, see Introducing the Entity Framework.

After you create an Entity Data Model, you can create data-bound WPF controls by dragging entity objects from the Data Sources window to the WPF designer.

To connect your application to a database and create an Entity Data Model

1. Open an existing project in Visual Studio, or create a new project.

2. Follow the steps in the Entity Data Model Wizard to connect to a database and specify the contents of the model. For more information, see How to: Create a New Entity Data Model.

3. After you complete the Entity Data Model Wizard, the Entity Data Model you created opens in the Entity Data Model Designer, and the data objects are now available in the Data Sources window.

   Note

   If the Data Sources window is not open, click Show Data Sources on the Data menu to open the window.

4. If the WPF designer is open, you can now drag items from the Data Sources window to the designer to create controls that are bound to the Entity Data Model. For more information, see How to: Bind WPF Controls to Data in Visual Studio.

   If the Windows Forms designer is open, you cannot drag items from the Data Sources to the designer. To create controls that are bound to the Entity Data Model, you must build the project, add a new object data source that is based on the Entity Data Model, and then drag those objects to the designer. For more information, see How to: Add an Object as a Project Data Source (Entity Framework).

Creating a New Database Connection

When you use the Data Source Configuration Wizard or the Entity Data Model Wizard, you must specify a connection to the database you want to use. If you do not already have a connection to the database, perform the following steps to create the connection.

These instructions assume you have already started the Data Source Configuration Wizard or the Entity Data Model Wizard as described in Connecting to Database and Creating a Dataset and Connecting to Database and Creating an Entity Data Model.

To create a new database connection

1. On the Choose Your Data Connection page of the Data Source Configuration Wizard or the Entity Data Model Wizard, click New Connection.

   One of the following actions occur:

   • If you have already created a data connection in Visual Studio, the Add Connection dialog box opens.

   • If this is the first data connection you have created in Visual Studio, the Choose Data Source dialog box displays. Select the type of database you want to connect to, and then click OK to display the Add Connection dialog box.

2. In the Add Connection dialog box, enter the requested information. The Add Connection dialog box is different for each type of data provider. For more information about the different options in this dialog box, see the following topics:

   • Add/Modify Connection (Microsoft SQL Server)

   • Add/Modify Connection (Microsoft SQL Server Database File)

   • Add/Modify Connection (Microsoft SQL Server Compact 3.5)

   • Add/Modify Connection (Microsoft Access)

   • Add/Modify Connection (Oracle)

   Note

   If the selected Data source in the Add Connection dialog box is not the data source you want to connect to, click Change to open the Change Data Source dialog box and then choose a different data source. For more information, see Choose/Change Data Source Dialog Box.

3. In the Add Connection dialog box, click OK.

   You return to the Choose Your Data Connection page of the Data Source Configuration Wizard or the Entity Data Model Wizard.

4. On the Choose Your Data Connection page, make sure that the new data connection is selected and then click Next.

5. Complete the remaining steps in the Data Source Configuration Wizard or the Entity Data Model Wizard.

Security

Storing sensitive information (such as a password) can affect the security of your application. Using Windows Authentication (also known as integrated security) is a more secure way to control access to a database. For more information, see Protecting Connection Information (ADO.NET).

See Also

Concepts

Data Sources Overview

Binding Windows Forms Controls to Data in Visual Studio

SQL Server Compact 3.5 and Visual Studio

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio

Connecting to a Data Source (ADO.NET)