Local Data Overview
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 Compact database files (.sdf)
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 |
|---|---|
Provides step-by-step instructions for creating a local database file that you can use to test data features and build applications. |
|
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 SQL Server Express LocalDB Database (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. |
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 in Visual Studio 2012
Before you can use an existing database file (.mdf) in Visual Studio 2012, 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, open Visual Studio 2012, 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 2012, 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 2012, 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, 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 |
Differences between SQL Server Express LocalDB and SQL Server Compact
Databases in SQL Server Express LocalDB (.mdf) and SQL Server Compact (.sdf) differ in the following ways:
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.
If you use SQL Server Express LocalDB, you can create stored procedures, user-defined functions and aggregates, spatial types, and other objects that SQL Server Compact doesn’t support, in addition to integrating your application with the .NET Framework.
SQL Server Express LocalDB is more robust and scalable than SQL Server Compact in that SQL Server Express LocalDB supports the same Query Optimizer and Query Processor as higher editions of SQL Server do.
SQL Server Compact databases can be deployed on desktop computers and smart devices. For more information, see SQL Server Compact 4.0 and Visual Studio.
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 SQL Server Express LocalDB Database (Windows Forms)
Walkthrough: Connecting to Data in an Access Database (Windows Forms)
How to: Add a SQL Server Compact Database to a Project
How to: Deploy a SQL Server Compact 4.0 Database with an Application
Walkthrough: Creating a SQL Server Compact Database
Walkthrough: Adding a SQL Server Compact Database to an Application and Deploying it