Export (0) Print
Expand All

Local Data Overview 

The term local data refers to having a connection between your application and a database file on the local computer (as opposed to having a connection to a database on a remote server). Currently supported local database files are SQL Server Compact Edition database files (.sdf), SQL Server/SQL Server Express database files (.mdf) and Microsoft Access database files (.mdb).

You can create connections to database files using the Data Source Configuration Wizard or the Add New Item Dialog Box.

NoteTip

Drag an .mdf or .mdb file from Windows into Solution Explorer to automatically configure your connection and start the Data Source Configuration Wizard ready for you to select the objects to use in your application.

The following table provides links to pages describing how to connect your application to local data:

Page Description

Walkthrough: Creating a SQL Server Express Database File

Provides step-by-step instructions for creating a local database file that can be used for testing data features and application building.

How to: Connect to Data in a SQL Server Express Database

Provides general instructions for creating a connection between your application and a SQL Server Express database file (.mdf).

Walkthrough: Connecting to Data in a SQL Server Express Database

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

How to: Connect to Data in an Access Database

Provides instructions for creating a connection between your application and a Microsoft Access database file (.mdb).

Walkthrough: Connecting to Data in an Access Database

Provides a step-by-step example of connecting to a Microsoft Access database.

How to: Add a SQL Server Compact Edition Database to a Project

Provides instructions for incorporating a SQL Server Compact Edition database into a Windows-based application.

Walkthrough: Using SQL Server Compact Edition in an Application

Provides step-by-step instructions for incorporating a Microsoft SQL Server 2005 Compact Edition database into a Windows-based application.

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

The Database Is Part of Your Application

Using the local data approach, you can not only connect to a database file, but you can also integrate the database file into your application. For example, you can select Add New Item from the Project menu, browse to an existing .mdf or .mdb file, and add the file to your project.

NoteNote

If you use the Data Source Configuration Wizard to create the data source for a local data file, you are asked if you want to include the file in your project. If you do not, your application will only contain the connection string pointed to the hard-coded path and not the actual data file. For more information, see How to: Manage Local Data Files in Your Project.

Adding local data files creates 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 select the objects to be included in the dataset.

After you complete the wizard, the database file and dataset appear in Solution Explorer, and the selected database objects are available in the Data Sources window. You can drag items from the Data Sources window onto your form to create controls bound to the underlying data. (Choose Show Data Sources from the Data menu to open the Data Sources window.) For more information, see Displaying Data on Forms in Windows Applications.

There Are Two Copies of the Database in Each Project

By default, when you build a project, the database file is copied from the root project folder into the output (bin) folder (select Show All Files in Solution Explorer to view the bin folder). This behavior is due to the Copy to Output Directory property of the file. The default setting of the Copy to Output Directory property is Copy always. This means that the database in the bin folder will be copied every time you build, debug, or run your application.

NoteNote

The behavior of the Copy to Output Directory property does not apply to web or C++ projects.

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

The database file behavior is dependent on the Copy to Output Directory property setting, which is detailed in the table below.

During application development, any changes made to the data (during run time within your application) are being made to the database in the bin folder. For example, when you press F5 to debug your application, you are connected to the database in the bin folder.

Copy to Output Directory setting Behavior

Copy if newer

This option is not recommended. The database file is copied from the project directory to the bin directory the first time the project is built. Every subsequent time you build the project, the Date Modified property of the files is compared. If the file in the project folder is newer, it is copied to the bin folder, replacing the file currently there. If the file in the bin folder is newer, then no files are copied. This setting persists any changes made to the data during run time, meaning every time you run your application and save changes to the data, those changes are visible the next time you run your application.

Caution noteCaution

The database file can change even when no changes are made to the data. Simply opening a connection (for example, expanding the Tables node in Server Explorer) on a data file can mark it as newer. Because of this unpredictable behavior, we recommended that you do not use this option.

Copy always (default)

The database file is copied from the project directory to the bin directory every time you build your application. Every time you build your application and save changes to the data, those changes are overwritten when the original file is copied to the bin directory, replacing the copy that you just changed. You do not see the updated data the next time you run your application. Any changes made to the data file in the output folder will be overwritten the next time you run the application.

Do not copy

The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself. You must manually copy the file to the output directory after setting to Do not copy.

Common Local Data Issues

The following table provides explanations for common issues encountered while working 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 Copy to Output Directory is set to Copy if newer or Copy always. These settings will overwrite the database in your output folder (the database being modified when testing your application) every time you build your project. For more information, see How to: Manage Local Data Files in Your Project.

I receive a message saying the data file is locked

Access (.mdb files): Verify that the file is not opened 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 the data file outside the Visual Studio IDE.

Access denied when two users attempt to access the database at the same time

Visual Studio takes advantage of user instances, a feature of SQL Server Express where a separate instance of SQL Server is created for each user. Once one user accesses the file, any subsequent users will not be able to connect. This can happen if, for example, you try to run a Web application in ASP.NET Development Server and IIS at the same time, because IIS typically runs under a different account.

See Also

Community Additions

ADD
Show:
© 2015 Microsoft