none
How to connect to SQL server compact 4.0 from Visual Studio on the same machine running Windows 10

    Question

  • I do not see a service running for this software on my machine. My question is can I use it to write .Net C# applications with SQL query statements? How do I initially set it up?
    Friday, December 30, 2016 10:01 PM

Answers

All replies

  • Hello,

    SQL Server Compact Edition (CE) isn't a Server service, therefore you can not see any service, it's a simple in process running assembly. From a .NET application you can use System.Data.SqlServerCe Namespace to use all functionality; see for example Walkthrough: Working with SQL Server Compact in Visual Studio


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Zolfaghar Saturday, December 31, 2016 1:33 AM
    Saturday, December 31, 2016 12:49 AM
  • I installed the required software, but I do not see "SQL Server Compact 4.0 Local Database" under the New Items. I did find the assembly and added it to my project. I have looked in nuget, and done all sorts of update and package installs. Any help you can give me on why I don't see the above mentioned or how to add it?
    Sunday, January 01, 2017 1:06 AM
  • Given you seem to be starting to work with SSCE in a new project, I wouldn't recommend you to invest much time in it. While it's a still great engine for existent projects, SQL Sever Compact 4.0 is already some years old and no plans are known for any evolution of the product, so I would consider another more supported engine.

    SQLite would be my recommendation for a free, performant and supported lightweight database engine. Its main limitation when compared with SQL Server Compact 4.0 is that SQLite doesn't support multiple connections to the same database file.


    Alberto Silva / www.moving2u.pt - R&D Manager / Former Microsoft MVP (2003-2015)

    Sunday, January 01, 2017 3:49 PM
  • Given you seem to be starting to work with SSCE in a new project, I wouldn't recommend you to invest much time in it. While it's a still great engine for existent projects, SQL Sever Compact 4.0 is already some years old and no plans are known for any evolution of the product, so I would consider another more supported engine.

    SQLite would be my recommendation for a free, performant and supported lightweight database engine. Its main limitation when compared with SQL Server Compact 4.0 is that SQLite doesn't support multiple connections to the same database file.


    But I develop some freewares in C++ (where I just distribute the exe + the database file) and SQL Server CE works really very well (small DB file, fast, no bug), while I had started first with SQLite that I had to abandon because I had some freezes on complex requests, which worked perfectly too on SQL Server CE...
    Sunday, January 01, 2017 5:06 PM
  • Thanks. I ended up installed SQL Express also. The question I have now is should I just use SQL Express or SQLLite? While I am not a DBA or a Web Admin, I am quickly working towards these activities. If SQLLite is not going to support a somewhat popular Web Site, I rather learn and use SQLExpress. My question is which is the better strategy? At the moment, I am just trying to get the following lines working:

    var query = from c in db.Customers
            where c.City == "Nantes"
            select new { c.City, c.CompanyName };

    I added the assembly for SQLCompact to my project, and I am working on defining and declaring "C" and "db".

    But I am having trouble with SQLExpress also. I am following this link, and I do not see an option for the SQLExpress, in the pull down window of a New SQL Server connection. This is basically the third step of the procedures. I do see SQLExpress running as a service. I am not going a suggested document for SQLExpress at this link. The question in my mind about the later link is whether there is a more relevant documentation for someone like me who is on Windows 10 instead of Linux environment. The document is written for .Net Core, which I understand to be for the Linux environment. Please provide any feedback you have for me as far as which SQL engine I should use, and how to get my current project working. I know I am close.

    Sunday, January 01, 2017 5:52 PM
  • Here are all the relevant packages on my machine. I rather conserve space as much as I can also.

    C:\WINDOWS\system32>wmic product get name,version | findstr "SQL"
    Microsoft SQL Server System CLR Types                                                                           10.51.2500.0
    Microsoft SQL Server Compact 4.0 SP1 x64 ENU                                                                    4.0.8876.1
    Microsoft AS OLE DB Provider for SQL Server 2016                                                                13.0.2164.0
    SQL Server Integration Services 2014                                                                            12.1.4449.0
    Microsoft SQL Server 2012 Analysis Management Objects                                                           11.3.6020.0
    Microsoft SQL Server 2014 Transact-SQL ScriptDom                                                                12.0.2000.8
    Microsoft SQL Server 2012 Command Line Utilities                                                                11.0.2100.60
    SQL Server Data Tools Reporting Services                                                                        13.0.1701.8
    SQL Server 2008 R2 SP2 Database Engine Services                                                                 10.52.4000.0
    Active Directory Authentication Library for SQL Server                                                          13.0.1601.5
    Microsoft SQL Server Data Tools - enu (14.0.61021.0)                                                            14.0.61021.0
    Microsoft SQL Server Database Publishing Wizard 1.4                                                             10.1.2512.8
    SQL Server Integration Services 2012                                                                            11.3.6523.0
    Microsoft System CLR Types for SQL Server 2016                                                                  13.0.1601.5
    Microsoft SQL Server 2008 R2 RsFx Driver                                                                        10.52.4042.0
    Microsoft SQL Server 2008 R2 Management Objects                                                                 10.51.2500.0
    SQL Server 2008 R2 SP2 Database Engine Shared                                                                   10.52.4000.0
    Microsoft SQL Server 2016 T-SQL Language Service                                                                13.0.14500.10
    Active Directory Authentication Library for SQL Server (x86)                                                    13.0.1601.5
    Microsoft SQL Server 2014 Management Objects  (x64)                                                             12.0.2000.8
    Microsoft SQL Server 2016 LocalDB                                                                               13.0.2151.0
    Microsoft SQL Server Browser                                                                                    10.52.4000.0
    Microsoft SQL Server 2008 R2 Management Objects (x64)                                                           10.51.2500.0
    Microsoft SQL Server 2016 ADOMD.NET                                                                             13.0.1601.5
    SQL Server 2008 R2 SP2 Database Engine Services                                                                 10.52.4000.0
    Microsoft System CLR Types for SQL Server 2014                                                                  12.0.2402.29
    Microsoft SQL Server System CLR Types (x64)                                                                     10.51.2500.0
    Microsoft SQL Server 2014 T-SQL Language Service                                                                12.0.2000.8
    Microsoft SQL Server 2016 Management Objects  (x64)                                                             13.0.1601.5
    Microsoft System CLR Types for SQL Server 2014                                                                  12.0.2402.29
    Microsoft SQL Server 2014 Express LocalDB                                                                       12.0.2000.8
    Microsoft System CLR Types for SQL Server 2016                                                                  13.0.1601.5
    Microsoft AS OLE DB Provider for SQL Server 2016                                                                13.0.2164.0
    Microsoft SQL Server 2008 Setup Support Files                                                                   10.1.2731.0
    Microsoft SQL Server 2012 Native Client                                                                         11.3.6518.0
    Microsoft SQL Server 2016 Analysis Management Objects                                                           13.0.1601.5
    SQL Server Integration Services                                                                                 13.0.2165.0
    Microsoft SQL Server 2016 T-SQL ScriptDom                                                                       13.0.1601.5
    SQL Server 2008 R2 SP2 Database Engine Shared                                                                   10.52.4000.0
    SQL Server 2008 R2 SP2 Common Files                                                                             10.52.4000.0
    Microsoft SQL Server 2008 R2 Native Client                                                                      10.52.4042.0
    Microsoft SQL Server 2016 T-SQL Language Service                                                                13.0.14500.10
    Microsoft SQL Server 2014 Analysis Management Objects                                                           12.1.4449.0
    Microsoft SQL Server 2008 R2 Setup (English)                                                                    10.52.4042.0
    Microsoft SQL Server VSS Writer                                                                                 10.52.4000.0
    SQL Server 2008 R2 SP2 Common Files                                                                             10.52.4000.0
    Microsoft SQL Server 2016 Management Objects                                                                    13.0.1601.5
    Microsoft SQL Server 2014 Management Objects                                                                    12.0.2000.8
    SQL Server Data Tools Analysis Services                                                                         13.0.1701.8

    Sunday, January 01, 2017 6:04 PM
  • Thank you everyone. I was able to use ASP.NET Core MVC and Entity Framework Core using Visual Studio procedures to successfully create tables.
    Sunday, January 01, 2017 7:38 PM