ASP.NET Data Access FAQ
This topic answers frequently asked questions about how to access data in ASP.NET web applications. It contains the following sections:
Microsoft strongly recommends against using Access in web applications. The AccessDataSource control has been removed from the Visual Studio Toolbox for projects that target ASP.NET 4.5, and Microsoft customer support will not help resolve problems resulting from the use of Microsoft Access in web applications. Such use might be successful for a while if you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver, if you access an .mdb or .accdb file that is located within your web application's folder structure, and if web site traffic is very low. But a system failure with possible database corruption is likely to develop eventually, especially as traffic to the application increases. In addition, bugs in the database provider are no longer being fixed; the Jet provider has been superseded by the ACE provider, which specifically disallows use in web applications. Even if no errors are encountered, database security is especially important in a public web site, and Access databases have fewer security features than SQL Server databases.
Microsoft recommends SQL Server for web applications. For more information, see Choosing Data Access Options for ASP.NET Web Applications.
If you want to accept the risks and use Microsoft Access in a web site, see the Microsoft Support page on Using Microsoft Jet with IIS.
You can use SQL Server Express in a production web hosting environment if its limitations are acceptable for your web site. SQL Server Express is not designed to be highly scalable and may not be appropriate for a high-traffic web site. For example, a SQL Server Express instance is limited to 1 gig of memory and no more than 4 processors, and a SQL Server Express database is limited to 10 gigabytes. For more information about the differences between the full and Express versions of SQL Server, see Features Supported by the Editions of SQL Server 2012.
You can combine the membership tables and your application tables in a single database. The membership tables are clearly prefixed, so name collisions are unlikely. And if you keep all of the tables in the same database you can use referential integrity against your own tables that have the membership ID in them. For an example that shows one way to do this, see Deployment to a Hosting Provider: Migrating to SQL Server on the ASP.NET site.
If you want to enable multiple developers to work on the same Visual Studio web project, with some of them using Visual Studio 2010 and some of them using Visual Studio 2012, you can’t use the SQL Server editions that are installed by default. Visual Studio 2010 installs SQL Server Express 2008, and Visual Studio 2012 installs LocalDB, which is an edition of SQL Server 2012. You can’t open a LocalDB database in SQL Server Express 2008.
If you are deploying to SQL Server 2008 or an earlier edition in production, install SQL Server 2008 on the machines that have Visual Studio 2012. The following link uses the Web Platform Installer (WebPI) to do that: SQL Server Express 2008 R2.
If you are deploying to SQL Server 2012 in production, install LocalDB and SSDT on the machines that have Visual Studio 2010. Install LocalDB and any of its dependencies that you need by clicking this link: Microsoft SQL Express LocalDB Edition 11.0. For information about how to install SSDT, see Get Started with Microsoft SQL Server Data Tools and Installing and using SQL Server Data Tools (SSDT) on Visual Studio 2010 on Rick Anderson's blog.
For information about how to convert connection strings from LocalDB to SQL Server Express and vice versa, see How to Convert a LocalDB Connection String to SQL Server Express and How to Convert a SQL Server Express Connection String to LocalDB.