Working with SQL Server Compact

Working with Microsoft SQL Server Compact 4.0 or building an ASP.NET Web application that uses SQL Server Compact occurs in the following stages:

  • Setting up a SQL Server Compact environment

  • Working with SQL Server Compact databases and data

You can set up the SQL Server Compact environment to build either stand-alone desktop applications, or ASP.NET Web applications.

  • To build an application that is intended to run locally on a computer, you must set up a development environment. Setting up the development environment involves installing Visual Studio 2010 Service Pack 1 (SP1). (Note: Microsoft Visual Web Developer 2010 SP1 Express is also supported; however, Visual Studio 2010 SP1 is preferred because it contains new features, such as support for the Transact-SQL editor on SQL Server Compact databases.) For more information, see Installing a Development Environment. Installing SQL Server Compact provides the client environment.

You can modify and manage SQL Server Compact databases and data by using the following components:

  • Programming APIs

  • SQL language

  • Microsoft Webmatrix and Microsoft Visual Web Developer 2010 SP1 Express

  • Transact-SQL Editor in Visual Studio 2010 SP1. For more information, see Visual Studio Transact-SQL Editor.

SQL Server Compact also provides multiple-connection access to a database, multiple-column indexes and matching index limits, transaction support, referential integrity, 128-bit file-level encryption and password protection, and a query optimizer.

For more information about working with SQL Server Compact databases, see Working with Databases (SQL Server Compact).

SQL Server Compact and the .NET Compact Framework provide development infrastructure technologies that you can use to build ASP.NET web applications.

An application that uses SQL Server Compact 4.0 can include creating the local SQL Server Compact 4.0 database, updating the local data, and merging changes that were made website with a SQL Server database.

The following programming models are supported by SQL Server Compact 4.0:

  • ADO.NET, accessible by using C# or Microsoft Visual Basic

  • OLE DB, accessible by using Visual C++ option from within Visual Studio