Condividi tramite

Local Data Overview

  • Articolo

When you use local data, you connect your application to a database file on the local computer, instead of to a database on a separate server. For example, you can connect an application that you’re developing in Visual Studio to the following local database files:

  • SQL Server Express LocalDB database files (.mdf)

  • SQL Server Express database files (.mdf)

  • Microsoft Access database files (.mdb)

The following table provides links to topics that describe how to connect your application to local data:

Topic

Description

Walkthrough: Creating a Local Database File in Visual Studio

Provides step-by-step instructions for creating a local database file that you can use to test data features and build applications.

Walkthrough: Connecting to Data in a Local Database File (Windows Forms)

Provides step-by-step instructions for connecting to a SQL Server Express LocalDB database while you create a simple Windows application.

Walkthrough: Connecting to Data in an Access Database (Windows Forms)

Provides step-by-step instructions for connecting to a Microsoft Access database.

How to: Connect to the Northwind Database

Provides instructions for connecting to the Northwind sample database in SQL Server, SQL Server Compact, SQL Server Express, and Access.

After you create a data source and configure it to access a local data file, you work with the data by using the same technologies and objects that you would use to work with data from any other source. For more information, see Creating Data Applications.

Integrating the Database Into Your Application

If you connect to local data, you can not only connect to a database file but also integrate it into your application. For example, you can open the Project menu, browse to an existing .sdf, .mdf, or .mdb file, and then add it to your project.

If you add local data files, you create a typed dataset and a dynamic connection string that points to the database file in your application. When you add a database file to your project, you use the Data Source Configuration Wizard to specify the objects to include.

Note

You can automatically configure your connection and start the Data Source Configuration Wizard by dragging an .sdf, .mdf, or .mdb file from File Explorer into Solution Explorer. You can then specify the objects to use in your application.

If you use the Data Source Configuration Wizard to create the data source for a local data file, you are prompted to include the file in your project. If you don’t include it, your application will contain only the connection string to which the hard-coded path points, not the actual data file. For more information, see How to: Manage Local Data Files in Your Project.

After you complete the wizard, the database file and dataset appear in Solution Explorer/Database Explorer, and the database objects that you specified appear in the Data Sources window. By dragging items from the Data Sources window onto your form, you can create controls that are bound to the underlying data. To open the Data Sources window, open the Data menu, and then choose Show Data Sources. For more information, see Binding Controls to Data in Visual Studio.

Using a Database File

Before you can use an existing database file (.mdf) in Visual Studio, you probably must convert the file to a SQL Server 2012 database file. When you connect to an existing database file, a message box asks whether you want to upgrade.

Important

If you upgrade the database file (.mdf), you can’t open it in an earlier version of SQL Server.

You don’t need to convert the database file (.mdf) if the SQL Server Instance Name is set to SQLEXPRESS and SQL Server 2008 Express is installed. SQL Server 2008 Express is installed if Visual Studio 2010 is installed. To change the instance name for this database file, openVisual Studio, open the Add Connection dialog box, specify .\SQLEXPRESS as the server name, and then specify the database or database file name.

SQL Server Express LocalDB and SQL Server Express

You can add a service-based database file (.mdf) to any project in Visual Studio. You can use designers in Visual Studio to design tables and other database objects, and you can run queries.

When you create a service-based database in Visual Studio, it uses the SQL Server Express LocalDB engine to access the database file (.mdf), where earlier versions of Visual Studio used the SQL Server Express engine.

SQL Server Express LocalDB is a lightweight version of SQL Server that you can program in many of the same ways as a SQL Server database. SQL Server Express LocalDB runs in user mode, and you can install it more quickly with fewer prerequisites and no configuration.

Note

For more information about SQL Server Express LocalDB, see Introducing LocalDB, an Improved SQL Express and LocalDB: Where is My Database? on the Microsoft website.

In Visual Studio, you can use SQL Server Express by default instead of SQL Server Express LocalDB. On menu bar, choose Tools, Options. Under the Database Tools node, choose Data Connections. In the SQL Server Instance Name text box, enter SQLEXPRESS. As an alternative, you can enter other values for the SQL Server instance name (for example, SQL2008).

The following table describes differences between the SQL Server Express LocalDB and SQL Server Express engines.

SQL Server Express LocalDB

SQL Server Express

Database type when you create a service-based database

In Visual Studio 2012 and Visual Studio 2013, SQL Server Express LocalDB

In Visual Studio 2010 and earlier, SQL Server Express

Name of SQL Server instance in Tools / Options

(LocalDB)\v11.0

SQLEXPRESS

Value of data source in connection string

(LocalDB)\v11.0

.\SQLEXPRESS

Value of AttachDbFilename in connection string

file path

file path

User instance is required ("User Instance=True" in connection string)

No

Yes

Extension of database file

.mdf

.mdf

Benefits of SQL Server Express LocalDB

  • SQL Server Express LocalDB is compatible with service-based editions of SQL Server for the features that SQL Server Express LocalDB enables. In SQL Server, you can move any database or Transact-SQL code from SQL Server Express LocalDB to SQL Server or SQL Azure without any upgrade steps. Therefore, you can use SQL Server Express LocalDB to develop applications that target all editions of SQL Server.

  • SQL Server Express LocalDB supports the same Query Optimizer and Query Processor as higher editions of SQL Server do.

Each Project Contains Two Copies of the Database

When you build a project, the database file might be copied from the root project folder into the output, bin, folder. This behavior depends on the Copy to Output Directory property of the file, and the default value of that property depends on the type of database file that you’re using.

To view the bin folder in Solution Explorer, choose the Show All Files button on the toolbar.

Note

The Copy to Output Directory property doesn’t apply to web or C++ projects.

The database file in your root project folder is changed only when you edit the database schema or data by using Server Explorer/Database Explorer or other Visual Database Tools.

As you change data during application development, you’re changing the database in the bin folder. For example, when you choose the F5 key to debug your application, you’re connected to the database in that folder.

Value of Copy to Output Directory property

Behavior

Copy if newer (default value for .sdf files)

The database file is copied from the project directory to the bin directory the first time that you build the project. The Date Modified property of the files is then compared every time that you build the project again. If the file in the project folder is newer, it’s copied to the bin folder, replacing the previous file. Otherwise, no files are copied.

Warning

We don’t recommend this value for .mdb or .mdf files. The database file can change even if the data doesn’t change. The file can be marked as newer if you simply open a connection (for example, expand the Tables node in Server Explorer).

Copy always (default value for .mdf and .mdb files)

The database file is copied from the project directory to the bin directory every time that you build your application. Any changes made to the data file in the output folder are overwritten the next time that you run the application.

Do not copy

The system never overwrites the file in the bin directory. Your application creates a dynamic connection string that points to the database file in the output directory. Therefore, you must manually copy the file to the output directory if you want the data in the output directory to match the data in the project directory.

Common Issues with Local Data

The following table explains common issues that you might encounter as you work with local data files.

Issue

Explanation

Every time I test my application and modify data, my changes are gone the next time I run my application.

The value of the Copy to Output Directory property is Copy if newer or Copy always. The database in your output folder (the database that’s being modified when you test your application) is overwritten every time that you build your project. For more information, see How to: Manage Local Data Files in Your Project.

A message appears, saying that the data file is locked.

Access (.mdb files): Verify that the file isn’t open in another program, such as Access.

SQL Server Express (.mdf files): SQL Express locks the data file if you try to copy, move, or rename it outside the Visual Studio IDE.

Access is denied when more than one user tries to access the same database at the same time.

Visual Studio takes advantage of user instances, which is a feature of SQL Server Express that creates a separate instance of SQL Server for each user. After one user accesses the file, any subsequent users can’t connect. This issue can occur if, for example, you try to run a web application in ASP.NET Development Server and Internet Information Services (IIS) at the same time, because IIS typically runs under a different account.

See Also

Tasks

Walkthrough: Connecting to Data in a Local Database File (Windows Forms)

Walkthrough: Connecting to Data in an Access Database (Windows Forms)