55

when I tried to create a new SQL Server Compact file from Visual Studio -> Add New Item, I don't see the 'Local Database' option any more which was actually the way to add SQL Server Compact Database. Now, if it is really discontinued from Microsoft, then, what is the recommended Embedded Database technology from Microsoft for Desktop Applications ? I tried SQLite but deployment is headache for SQLite because, the user may not have correct version of Visual C++ distributable installed in his/her machine.

Emran Hussain
  • 11,551
  • 5
  • 41
  • 48
  • Visual Studio intellisense uses SQL Server Compact Edition. Has Visual Studio 2014 switched to SQL Server Local? – Bruno Martinez Jun 26 '14 at 22:28
  • See @ErikEJ 's answer below. Presnetation on the topic with meat starting at slide 15-16: http://www.slideshare.net/tothc/hosting-simple-websites-on-azure – Csaba Toth Jun 29 '15 at 17:28
  • For long term I'd still brace myself and seek for something else, see @Aaron 's answer – Csaba Toth Jun 29 '15 at 17:29
  • Microsoft has deprecated MS SQL Server Compact from Visual Studio 2013. My own explanation for this is, that CE is a serverless DB system, that only runs on Windows machines today. Microsofts long term goal seems to be, to offer a real cross platform environment with newer Visual Studio versions. So a serverless DB, that doesn't run on Linux, Android and iOS makes no more sense. – Michael Sep 11 '15 at 17:30

7 Answers7

42

Yes, SQL Server Compact has been deprecated (see the comments on this Connect item). You should be using SQL Server Express or SQL LocalDB. Some posts:

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx

http://erikej.blogspot.com/2011/01/comparison-of-sql-server-compact-4-and.html

http://erikej.blogspot.com/2012/07/the-state-and-near-future-of-sql-server.html

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Well, maybe it's deprecated, but I just want to have some DB for a prototype Azure application, like described in here: http://www.dotnetcurry.com/showarticle.aspx?ID=883 – Csaba Toth Nov 27 '14 at 20:59
  • What is offered now is 20MB free Azure SQL, but that may not be enough for the prototype. So despite of all deprecation I will find a way to still use Compact edition. As I guess many others too. – Csaba Toth Nov 27 '14 at 21:01
  • @Csaba that doesn't make much sense. SQL Server Express can give you 10 GB for your free prototype. – Aaron Bertrand Jun 29 '15 at 15:22
  • 1
    Yes, but the question is where do you host that SQL Express? Whith Azure "Web Apps" you get a node with IIS under the hood, but not with SQL Express AFAIK. I worked around this with SQL CE: since it's an SQL server in a dll, it won't require installation in that node (you cannot install, since you are not on a full IaaS level). So the keyword here is Azure. How do I use SQL Express on Azure for free (and preferably with Web Apps). http://www.slideshare.net/tothc/hosting-simple-websites-on-azure – Csaba Toth Jun 29 '15 at 17:22
  • BTW, the slideshare I linked: after that presentation I spoke with more people who still use CE in the wild and they are using VS 2012. For those with CE scenarios who really seek a DB in a dll, would you recommend SQLite? – Csaba Toth Jun 29 '15 at 17:24
  • Can you really not create an Azure VM with IIS or the ability to install SQL Server Express there? I don't run support for that platform but I find it kind of hard to believe there isn't an option to allow that. I can't recommend for or against SQLite because I've never used it, sorry. – Aaron Bertrand Jun 29 '15 at 17:29
  • SQLite is an excellent alternative. It does all the relational stuff like foreign keys, indexes, as well as views, common table expressions, although not stored procedures or user defined functions. The problem with Azure is that unless you have an internet connection (exception, not the rule), its totally useless. E.g. You could never use an Azure database on (i.e. from) a spacecraft, but you could use SQLite (and the reliability is probably more suitable as well) – Reversed Engineer Oct 27 '15 at 14:25
19

Yes, it is missing from VS 2013, but you can do almost everything you need to do with SQLCE in VS 2013 by using my SQL Server Compact Toolbox addin. What are you missing?

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • 2
    Link to above mentioned vsix toolbox [here](http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1) – StuartLC Sep 29 '14 at 04:33
  • @ErikEJ Hi, Erik - I installed Your tool to my VS 2013, but when I try to add connection - it shows error message "Can not save this connection for use with EF6 tools, make sure the SQL Server Compact DbProvider is installed"... I have installed Microsoft SQL Server Compact. Could You point the direction - what might be the issue? – Prokurors Dec 05 '14 at 15:10
  • @Prokurors Suggest you post an issue at sqlcetoolbox.codeplex.com and provide a screnshot of the about dialog from the toolbox – ErikEJ Dec 05 '14 at 21:05
  • @ErikEJ this is strange, but the problem after the computer restart (visual studio restart didn't do the trick btw) is gone :) Thanks anyway! – Prokurors Dec 06 '14 at 11:40
  • Presentation on the topic, the meat starts at slide 15-16: http://www.slideshare.net/tothc/hosting-simple-websites-on-azure – Csaba Toth Jun 29 '15 at 17:26
  • Same thing happened to me. Quite frustrating. 'til I happened upon this thread. – Bluebaron Jul 14 '15 at 18:42
12

As others pointed out, SQL Server Compact has been deprecated. But instead you can use SQLite which doesn't require any installation on the client machine.

SQLite depends on the Visual C++ runtime, but you don't need to install it on the client machine. The system.data.sqlite download page contains several "static" packages that already contain the runtime.

All the "static" packages contain either native or mixed-mode assembly binaries linked statically to the appropriate version of the Visual C++ runtime. Typically, these packages are used in cases where customer machines may not have the necessary version of the Visual C++ runtime installed and it cannot be installed due to limited privileges.

For example, on my machine I am running Windows 8.1 x64 so I went under Precompiled Statically-Linked Binaries for 64-bit Windows (.NET Framework 4.0) and downloaded sqlite-netFx40-static-binary-x64-2010-1.0.90.0.zip.

This binary package contains all the binaries for the x64 version of the System.Data.SQLite 1.0.90.0 (3.8.2) package. The Visual C++ 2010 SP1 runtime for x64 is statically linked. The .NET Framework 4.0 is required.

I then unzipped the package and ran test.exe to make sure everything works. Zero installation required.

bouvierr
  • 3,563
  • 3
  • 27
  • 32
  • 2
    It's even easier with NuGet package. – infografnet Dec 09 '14 at 18:15
  • @infografnet: yes nuget is now the recommended way to get [sqlite](https://www.nuget.org/packages/System.Data.SQLite) for .NET. It manages automatically the different library dependencies for various versions of .NET. – bouvierr May 17 '15 at 14:49
8

For some reason the use of Server Explorer with SQL Compact was removed in VS 2013. There is a UserVoice issue in regards to have it re enabled.

http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/4714470-support-sql-server-compact-in-visual-studio-2013?utm_campaign=shorturls&utm_source=visualstudio.uservoice.com

Scott Wylie
  • 4,725
  • 2
  • 36
  • 48
  • But you can get VS 2013 Community for free and use the SQL Server Compact Toolbox with that – ErikEJ Apr 20 '15 at 06:30
3

SQLite is also available as a single C file (amalgamation), which you can directly compile into your application, if you use native C/C++. It then works in any situation where your app can open a file. No additional DLLs nor installation required.

Thinkeye
  • 655
  • 6
  • 6
1

For other newbies out there who are having a problem and land on this site, what I did was to create the SQL Server database file (which is created in LocalDB) and then in the project properties, publish tab, and prerequisites button, there is the opportunity to add the download and installation of LocalDB to the published package - and it works.

tbodt
  • 16,609
  • 6
  • 58
  • 83
Conceptual Wisdom
  • 11
  • 2
1

Unfortunately there is no built-in support of SQL Compact in Visual Studio 2013.

But you can install add-in SQL Server Compact/SQLite Toolbox

I also use this tool for SQL Compact in VS 2013 and it is very easy and clear

Andrew
  • 1,474
  • 4
  • 19
  • 27