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). SQL Server Compact 3.5 database files (.sdf), SQL Server and SQL Server Express database files (.mdf), and Microsoft Access database files (.mdb) are the currently supported local database files.
Note
The preferred local database for client applications is SQL Server Compact 3.5. For more information, see SQL Server Compact 3.5 and Visual Studio.
Note
Drag an .sdf, .mdf, or .mdb file from Windows Explorer into Solution Explorer to automatically configure your connection and start the Data Source Configuration Wizard. You will then be able to select the objects to use in your application.
The following table provides links to topics describing how to connect your application to local data:
Topic |
Description |
|---|---|
Provides step-by-step instructions for creating a local database file that can be used to test data features and create applications. |
|
Provides step-by-step instructions for creating a local database file that can be used for testing data features and application building. |
|
Walkthrough: Connecting to Data in a SQL Server Express Database (Windows Forms) |
Provides step-by-step instructions for connecting to a SQL Server Express database while creating a simple Windows application. |
Walkthrough: Connecting to Data in an Access Database (Windows Forms) |
Provides a step-by-step example of connecting to a Microsoft Access database. |
Provides details for connecting to the SQL Server, SQL Server Compact 3.5, SQL Server Express, and Access versions of the Northwind sample database. |
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 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 click Add Existing Item on the Project menu, browse to an existing .sdf, .mdf, or .mdb file, and add the file to your project.
Note 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/Database 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 Binding Controls to Data in Visual Studio.
There Are Two Copies of the Database in Each Project
When you build a project, the database file might be copied from the root project folder into the output (bin) folder. (Click the Show All Files button in Solution Explorer to view the bin folder.) This behavior is dependent on the Copy to Output Directory property of the file. The default setting of the Copy to Output Directory property is dependent on the type of database file you are using.
Note
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 changed only when you edit the database schema or data by 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 following table.
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 (default for .sdf files) |
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. .gif "Caution note") Caution
This option is not recommended for .mdb or .mdf files. 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 do not recommended this option for .mdb or .mdf files.
|
Copy always (default for .mdf and .mdb files) |
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
Tasks
Walkthrough: Connecting to Data in a SQL Server Express Database (Windows Forms)
Walkthrough: Connecting to Data in an Access Database (Windows Forms)
How to: Add a SQL Server Compact 3.5 Database to a Project
How to: Deploy a SQL Server Compact 3.5 Database with an Application
Walkthrough: Creating a SQL Server Compact 3.5 Database
Walkthrough: Adding a SQL Server Compact 3.5 Database to an Application and Deploying it