Monday, February 21, 2011

Using SQL Server Compact 4.0 with Desktop Private Deployment and a Setup project (MSI) (part 2)

I my previous post in this 2 part series, I demonstrated how to use SQL Server Compact 4 with Entity Framework 4 in a desktop application, despite the not excellent tools support.
This time I will show how to implement private deployment, and also how to solve other challenges related to installing via a Windows Installer file (MSI), by adding a Visual Studio Setup project to the solution.
First I will configure the project for Private Deployment, and then add a Setup project.For more information on requirements for Private Deployment, see my blog post SQL Server Compact “Private Deployment” on desktop–an overview.

Enable Private Deployment in a project

This includes copying the required SQL Server Compact 4.0 runtime files, and including these as content in the project, and modifying app.config to refer to the Private managed ADO.NET provider.
Locate the files to be copied in C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private.
image
Copy all files and folders here to the project folder (on my system C:\projects\Chinook\Chinook.WPF).
Select “Show all files” in Solution Explorer in Visual Studio. Your project should now look similar to this:
image
Include the amd64 and x86 folder in project (right click), including all content and subfolder as Content, Copy Always. Also include the two managed DLL files in the project root (System.Data.SqlServerCe.dll and System.Data.SqlServerCe.Entity.dll). Make sure to specify “Copy Always”, or the files will not be included in the project output. Verify that all files are included by looking in the debug folder after building the project.
You should now have a project structure like this (same set of files in the x86 folder, of course):
image
Now modify your app.config to refer to the Private ADO.NET provider, which has assembly version 4.0.0.1, not 4.0.0.0 as the on in the GAC. Using this special assembly version provider will prevent assembly probing from picking up a newer version of the provider in the GAC.
<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0"/>
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>



Notice the version on the type entry is 4.0.0.1.
IMPORTANT: If you reference System.Data.SqlServerCe.dll, make sure to reference the 4.0.0.1 version in your project folder!


Verify that the application still runs, and displays data.

Add and configure a setup project to produce a MSI (Windows Installer) file




(This is a little involved, as I will demonstrate solutions to several deployment issues here).



Start by adding a Visual Studio Installer project to the solution, and call it Chinook.WPF.Setup



image



Create a setup for a Windows application, and add the Content Files and Primary Output from Chinook.WPF:



image



For this project, we want to include the Chinook40.sdf file and deploy it with our installer. Other options include creating the sdf file at the first application startup. So we add the Chinook40.sdf file as additional file:



image



Set the Permanent property on the Chinook40.sdf file to True, to prevent it from being removed during uninstall.



image



In the file system browser in the Setup project, add the User’s application data folder, this is where we want the sdf file placed,as this is a writable location (Program Files folder is not writable):



image



Now we can set the folder location of the sdf file to this folder:



image



So the database file will now be installed in the C:\\Users\\<Username>\\AppData\\Roaming\\ folder. Now we need to modify the connection string. We can do this by manipulating the DataDirectory location, as this is part of the connection string. Add a Startup event handler to App.xaml:


<Application x:Class="Chinook.WPF.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             StartupUri="MainWindow.xaml" Startup="Application_Startup">
    <Application.Resources>
         
    </Application.Resources>
</Application>



In this handler, add the following code, notice the comments (!):


private void Application_Startup(object sender, StartupEventArgs e)
        {
            // This is our connection string: Data Source=|DataDirectory|\Chinook40.sdf
            // Set the data directory to the users %AppData% folder
            // So the Chinook40.sdf file must be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\
            AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
        }






Finally, add a shortcut to the application in the User’s Programs Menu file system folder:



image



Move the shortcut to the User’s Programs Menu folder, and rename to Chinook.



image



Now build the Setup project (right click and select Build), and test the installer on a system without SQL Server Compact 4.0 installed. .NET 4.0 Client Profile must be installed, however. The setup will not install if this is not the case.



You can test on your development system, by uninstalling the desktop runtime:



image



Happy deployment!



You can download the completed solution from here:

https://1drv.ms/u/s!AitHcOtLnuVHgwewiWcudEGRkpEt

102 comments:

Unknown said...

Thanks :) It works like a charm even with ClickOnce

Unknown said...

is it possible to run this setup on machine without vc++ 2010 runtime or any VC++ runtime ?

ErikEJ said...

Rahul: It is indeed! (The VC++ 2008 SP1 runtime is included a a private file)

Mike said...

This worked like a charm. However (you knew it was coming, right?)...

I just had to make minor schema change to my database. How do I make that change to the SDF file that may already be installed on the user's PC when they install the next version? I'm using EF 4 and don't see a way to do this.

Is there a simple way to run raw SQL on the SDF file during the installer's commit phase, or is there a best practice that I'm missing?

Thanks,
Mike

ErikEJ said...

Mike: I would run a schema version check during start of the updated application, it is easier to control than logic in the installer. Use a query against INFORMATION_SCHEMA.COLUMNS to find out if you need to issue any ALTER TABLE statements. You could try my Toolbox in order to get an idea about the required ALTER TABLE statements. Hope this helps!

Dnana said...

Your sample is not working in my system. Throwing "Failed to open underlying connection" exception.
What I'm missing?

ErikEJ said...

Dnana: The database file is not present in the expected location.

Any Mouse said...

Hey Erik!

This is working perfectly on my development machine, when I try this on a Windows XP test machine it works fine except the connection process to the sdf takes about 30 seconds.

Any insight would be greatly appreciated!


NOTE: using my own application not your included example application.

.... and Thanks for the awesome resource that is this blog!

ErikEJ said...

The database engine is rebuilding the indexes: See this: http://erikej.blogspot.com/2009/08/running-sql-compact-from-cd-rom-read.html

Any Mouse said...

from that page it says "the runtime needs to re-create indexes if a SDF file has been moved from Vista/Server 2008 to XP/Server 2003"

The database file is created in code when the application is first run. After creation, every Open() takes about 30 seconds on my XP test machine.

any ideas?

ErikEJ said...

Any: That's weird - could you share your connection string? Are you on XP SP3 (previous SPs are not supported) ?

Any Mouse said...

XP SP3 YES
---

return String.Format(...

DataSource=\"{0}\";
Password='{1}';
Persist Security Info=True;
Encrypt Database=True;
Mode = Exclusive"
...

Again, all is fine on Vista/7
XP SP3 runs fine too but long 30 second connection time.

ErikEJ said...

Any: I would test without encryption and exclusive mode. (Why do you need Exclusive??)

driv said...

We are using NuGet to import references for SQL CE 4, the result is the same private way of deploying the assemblies. We have a problem that our application keeps loading the GAC version 4.0.0.0 instead of the private one 4.0.8482.1. Even though we tried to specify a specific version in app.config:

system.data
DbProviderFactories
remove invariant="System.Data.SqlServerCe.3.5"
add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
remove invariant="System.Data.SqlServerCe.4.0"
add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
DbProviderFactories
system.data

I have stripped the XML off tags, I hope it is readable.

Any ideas?

ErikEJ said...

driv: I think you are mixing up file version and asembly version numbers.
I think NuGet always includes the 4.0.0.0 assembly due to web requirements (Medium trust). For private desktop deployment, I you must use 4.0.0.1, you cannot use the NuGet package.

Any Mouse said...

Erik, I have looked into this tested it every way I can then the most simple thing hit me, I installed as administrator and am running it under a non admin account.

Admin Account: Normal Perfect

Non Admin: 20-40 Seconds to Connect to Database. I would think this would fail or work not just be slow?

Database are created programmatically and stored in Application Data /AppName/ per users


Any Ideas?

ErikEJ said...

Any: And you have tested without Excluseive and Encryption? I would test with a tool like Process Monitor to see what it is waiting for...

Any Mouse said...

I am an old school C++ developer learning .NET , this issue may be elementary or even unrelated to SQL CE

The program runs perfectly fine on Windows Vista and 7 and XP.

In XP only Administrator has no issues.
Here is the problem for NON Administrative users on XP.

This happens when it attempts to open a connection.

-> Connect to SQL Server CE 4.0 sdf

24:43.9 MyProgram.exe 3704 CreateFile C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys\517efac85db7042e2b9ae54b76f4e58d_21b014c3-ea35-455d-9071-6330ae1b0a11 ACCESS DENIED Desired Access: Generic Read, Disposition: Open, Options: Sequential Access, Synchronous IO Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, AllocationSize: n/a

-> 90-ish more Attempts of the above line, application hangs for 10-20 seconds

-> Connection Success and Program runs just fine. This only happens during the connection process.


My Connection String:

return String.Format("DataSource=\"{0}\"; Password='{1}';Persist Security Info=True; Encrypt Database=True; Mode = Exclusive;", _dataBaseLocation, _password);

I have attempted this without Encryption and have the same results on this XP test machine.

Any idea what i is going wrong here?

I am also wondering why (below) it was accessing framework v2.0.50727 when configured for .NET 4 - that normal? (Note that access is denied here)


27:51.5 MyProgram.exe 3704 CreateFile C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\security.config.cch.new ACCESS DENIED Desired Access: Generic Write, Read Attributes, Disposition: OverwriteIf, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: None, AllocationSize: 0

27:51.7 MyProgram.exe 3704 CreateFile C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\enterprisesec.config.cch.new ACCESS DENIED Desired Access: Generic Write, Read Attributes, Disposition: OverwriteIf, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: None, AllocationSize: 0

ErikEJ said...

Which SP level is your XP test machine on - it must be SP3. Not sure why .NET 2.0 configs are loaded, no knowledege of C++.

Anonymous said...

Hi Erik,
The only thing I was unclear about in your instructions is modifying the App.config. I did not have a system.data node, so I placed that code snippet right above my connectionStrings node. Is that the right place for it? It runs when I execute the project in VS 2010, but doesn't when I trying to install onto an XP that doesn't have SQL Compact installed.

Also I am using install shield to create a setup. I wouldn't think that would matter though. The dll's are being place in their correct places.

ErikEJ said...

Andrew: Look in the code sample! What errors do yu get on XP?

Anonymous said...

I checked the code sample where you start modifying the app.config and I don't see any difference.

The error I get is not much of use. After the program installs and I try to open it. I get a screen saying that the program encountered an error, would you like to send this to Microsoft?

There are a couple of links in the error so, I try and drill down to find the details of the error and it gives me a bunch of numbers that don't mean anything. It also has some version number and a couple other things. It was very long so I didn't try and attach it, but I can if need be.

When I download SQL Compact to that computer, the program starts up fine.

Now I did stop following your instructions above when you got to the setup project. Is this a mistake? The program is working just fine with my install shield and the db is even in the program files folder and writing/reading to it.

Anonymous said...

I tried this same thing with another project and I am getting an error of: The specified store provider cannot be found in the configuration, or is not valid.

Again, when I install SQL compact it works fine.

ErikEJ said...

Andrew: I would need to see your install output to be sure, but it sounds like the Content files from your project is missing in the setup project.

Anonymous said...

K, I will try some things with that. I have a multi project solution, an InstallShield, Data, Services, and a winforms project. Not sure if that would affect at all. Currently, I am only attaching the content files of the winforms project.

I do see the SQL compact dll's and folders in the installation folder after I do an install.

Does the above code snippet for the app.config file need to be in a certain place in the app.config? I placed it right above my connection string in the winforms app.config. Also is it possible it need to be in another projects app.config?

Thanks for your time!

ErikEJ said...

Andrew: You would need to share your app.config file and/or repro project in order for me to help. This comments area is not the best place for this kind of support questions, please contact me via email (contact inf n my Blog profile)

Lionel J said...

Erik:
I am using EF 4.1 and SQLCE in a private deployment. In order to get the desktop application to work correctly, I am using your hint to change the app.config file so that EF will use version 4.0.0.1 of SQLCE and this works fine.

However, my VS installer script uses the same EF/SQLCE capability during the install process in one of my own assemblies. In this case - of course - the app.config of the application has no effect on msiexec and EF gives a cast exception between version 4.0.0.0 and 4.0.0.1 entities.
Is there any way around this or do I have to fall back to using non-EF code during the install process?

Thanks for all the information that have provided on this blog - it has saved me a lot of time.

ErikEJ said...

Lionel: Glad you found my blog post useful. I cannot see a way around it - but why not run the initial code on first startup rather than during install? That will solve your issue...

Lionel J said...

Erik - Thanks for the quick reply.

As you point out, I may have to postpone that database access until the first run of the app. Unfortunately, some of the actions invoked by what is found in the preexisting database requires administrative rights and I would have preferred to have kept all those actions within the installation phase. On the other hand, the database interrogation currently done during installation is only for one row from a single table, so I may just go the ADO.Net route there.

ShivaBhakta said...

I have followed all your steps. but I get the following error in my ASP.NET MVC 4.0 web app using SQLServer CE
[FileLoadException: Could not load file or assembly 'System.Data.SqlServerCe' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)]

[FileLoadException: Could not load file or assembly 'System.Data.SqlServerCe, Version=4.0.8482.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)]
System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +0
System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +39
System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks) +132
System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +144
System.Reflection.Assembly.Load(String assemblyString) +28
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +46

[ConfigurationErrorsException: Could not load file or assembly 'System.Data.SqlServerCe, Version=4.0.8482.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)]
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +618
System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +209
System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +130
System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +178
System.Web.Compilation.BuildManager.GetPreStartInitMethodsFromReferencedAssemblies() +94
System.Web.Compilation.BuildManager.CallPreStartInitMethods() +332
System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException) +677

[HttpException (0x80004005): Could not load file or assembly 'System.Data.SqlServerCe, Version=4.0.8482.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9079228
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +97
System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +256

ErikEJ said...

Jay; see this answer on SO: http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450

jd said...

I can confirm Any Mouse's probem with XP, ClickOnce, 2 separate users, SQL CE with Encryption and slowness.

The slowness is indeed caused by using Password (ie encryption) in the connection string. Slowness is manifested regardles of the installation type of SQL CE (I have tried both private and as a ClickOnce prerequisite).

Any ideas how this could be mitigated? Dropping encryption is not an option for my requirements.

ErikEJ said...

jd: And it is XP *SP3* (required)? I so, suggest you post a bug at connect.microsoft.com

jd said...

Indeed it is SP3. I will contemplate on submitting the bug.

jd said...

For future reference to anybody who happens to have the same problem with XP and encrypted database:

To get rid of the slowdown on non-admin users you need to grant them read access to this folder:
C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys

ErikEJ said...

Thanks, JD, for the tip - sadly this does not solve the issue for ClickOnce scenarios.

Tom W Hall said...

I'm interested to hear from those who have implemented this with ClickOnce as to whether they encountered the problem we did with the Microsoft.VC90.CRT.manifest files on Windows XP.
I found that everything except these files (which live in the Microsoft.VC90.CRT subfolders) installed fine on XP, but because ClickOnce marks these manifest files as dependencies rather than simply as file elements in its own manifest, it causes a spurious "unable to write to hard disk" error when installing. What I had to do was yet another nasty Regex operation on the ClickOnce manifest to convert the dependency element into a file element for these 2 files. Should this have been necessary? Thanks.

Tom W Hall said...

I think I was somewhat on the wrong track there. From what I can tell and what I have read, ClickOnce was trying to install the unmanaged DLLs twice - once when resolving the managed SQL CE DLL, which may have resulted in the unmanaged DLLs being dragged down in advance, and then again when it encountered the file elements for these unmanaged DLLs.
I read a recommendation to fix this by removing (as part of an MSBuild step in my case) the file elements for all unmanaged DLLs from the ClickOnce manifest. However this had the flowon error that ClickOnce couldn't find a couple of other VC++ DLLs such as msvcP90.dll and msvcM90.dll. I didn't want to start messing around with these strange DLLs so I followed another suggestion and packaged up the whole private deployment as a zip file which is unzipped on the app's first run.
But should I have to do all this to get this to work on XP? The XP machine in question has SP3 installed.

Bazza Formez said...

With respect to the section starting :
"Add a Startup event handler to App.xaml:"

My deployment is a simple Winforms type - not WPF. Do I need to do something similar to what you do to the App.xaml ??
Thanks,
Bazza

ErikEJ said...

Bazza: Yes, if you want to locate your database in a writeable location, and want to use DataDirectory (use Main in Program.cs)

Unknown said...

Hi Erik,
Related to bazzas comments, would the appropriate place to place the DataDirectory modifying code be in the winform apps entry point method (typically at Program.cs.Main()). Hope this makes sense! :-)

ErikEJ said...

Unknown: Yes, before you do any data access...

喔喔 said...

thanks for your hard work~

zafi said...

hi Erick,

I found you blog from link provided by you in MSDN forum. I am facing a problem related to DataDirectory.

I am declaring this in my program to store in the database:
Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\DataFiles\cscan4.mdb;")

When I create an installer and run in XP it works fine. But when I execute in Windows 7 its not working (When I try to press button to save in database). I reckon its because the usage of application.startup i guess...

Its not compatible in Windows 7 because of UAC issues (probably). I have set the installer to run in x86 platform but still not working.

I want to use System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)

Like you suggested but I am not sure how.

I hope you can advise me pertaining this please.

Thank you so much.

ErikEJ said...

Zafi: Why not use System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) in your connection string.

zafi said...

Hi Erick,

Can you teach me how to use it please? i know the function when I get some info from the forum but I dont know how to use it.
At least you can give me some advise on elements that I should Understand/consider before I can use it. I am willing to learn.

ErikEJ said...

Zafi: Suggest you ask in the MSDN forum, so more people can benefit from the answer

Vic said...

Erik, what is your advice on where to install the SDF file when it needs to be shared and writable by all users? The ProgramData folder in Windows7 isn't writable by default and there doesn't seem to be a clear consensus when I google/bing on this question.

Thanks..

ErikEJ said...

Vic: Your installer must create a folder in programdata with the required permissions - see http://stackoverflow.com/questions/1089644/users-public-not-writeable-in-windows-7-where-do-i-put-user-data

Anonymous said...

Hi, I am getting this error when i run the solution just after including those files..

The type initializer for 'System.Windows.Application' threw an exception.

ErikEJ said...

Myblog: Please add error handling to your app..

Anonymous said...

that issue solved when i reorder the tag.. but now the thing is, after i did the installation the application got crashed in the very first instance of opening a sql connection..

ErikEJ said...

Myblog: Suggest you provide more info, like sample code and error messages, and prefer you use the MSDN forum for support issues

Unknown said...

Hi Erik,

Thank you for the post.
I am using VS12 with InstallShield Limited Edition. I tried replicating what you had done, but, I am getting "Could not load file or assembly 'System.Data.SqlServerCe.Version=4.0.0.0" when trying to run the application on a different computer. The wanted the sdf file to be read-only [users will not be writing to it], so I left it in the programs area (instead of creating a folder for it in the user data space). Any idea why I am getting 'System.Data ...4.0.0.0 not found error?

ErikEJ said...

Sal: You are referencing the wrong DLL version, it must ber 4.0.0.1

Unknown said...

Hi Erik,

I just wanted to let you know I found another post by you adding a modification to the app.config file, adding section to force running against private dll files.

I am past that error now! :-) Thank you!

Now, I get the message that says a db file cannot be created, which means, I will have to create a user space directory for the sdf file as you have initially indicated.

Thanks!!! again

Unknown said...

I checked all the properties again and the manifest file was not declared as content.
Now I get other errors: Could not find file 'msvcm90.dll', 'msvcp90.dll' and 'msvcr90.dll'. However, the last one is in the folder and properties are as ou said. I tried moving the 2 first files into the folder with the same properties, but to no avail...

ErikEJ said...

Martin: Never heard of that issue, can you mail me a repro project?

L.Hipppias said...

Hi,
Nice Article.
I can compile sqlce project in my c# project with "any cpu" option on 64 bit machine.

But with .net 4.5 in visual studio 2012 there is a new option for "any cpu" which is "Prefer 32 bit". And if you check this option[ and by default it is checked Sqlce try to load x64 [ not x86] dlls wrongly so it crashes.Can not able to create sqlce driver.

This is repatable error. just write simple console aplication which insert simple field in sqlce database in c# with any cpu mode .It will compile but when try to run with "prefer 32" it crashes although if you uncheck it it will work

L.Hipppias said...

Hi,
Nice Article.
I can compile sqlce project in my c# project with "any cpu" option on 64 bit machine.

But with .net 4.5 in visual studio 2012 there is a new option for "any cpu" which is "Prefer 32 bit". And if you check this option[ and by default it is checked Sqlce try to load x64 [ not x86] dlls wrongly so it crashes.Can not able to create sqlce driver.

This is repatable error. just write simple console aplication which insert simple field in sqlce database in c# with any cpu mode .It will compile but when try to run with "prefer 32" it crashes although if you uncheck it it will work

ErikEJ said...

L. Thanks for that info.

admin said...

Do this way of deployment works with vb .net as my application is build on vb .net and not in c#.
If yes then what changes should i make.
Thanks

ErikEJ said...

Ashif - No vb.net changes required afaik

Unknown said...

Hi Erik. Your article does not mention adding an assembly redirect to the main project config file. I had to add this to get it to run. At the same time, I haven't found any posts that complain about it. Am I missing something else, or is this required?

ErikEJ said...

Brian: No, this is not required. Are you using the correct System.Data.SqlServerCe.dll (from the Private folder, assembly version 4.0.0.1) ?

Unknown said...

Erik,

Deployment is working fine now but it seems the program does not connect to the sdf files in the users' appdata folder. When I put the sdf files in the main program folder it works fine. On the development platform... (yes after uninstalling sql compact).
On a random PC though, it never finds the sdf files regardless of where tey are. I must have done something wrong in the coding. This is what I have:
string commandline = "SELECT TRIM from TRIM" + cbFlap.Text + " WHERE TOGW = " + cbtogw.Text + " and MAC = " + cbMac.Text;

SqlCeConnection myConnection = new SqlCeConnection(@"DataSource=|DataDirectory|\DC871TRIM.sdf");
SqlCeCommand cmd = new SqlCeCommand(commandline, myConnection);
Object trimSetting;

Why would this connection string only work on my development platform?

Thank you

ErikEJ said...

Martijn: Without the ability to see your code, I cannot assist. Please email me a repro project.

Unknown said...

Ok. What's a repro project... sry :)

ErikEJ said...

Martin: It is a VS project that demonstrates the issue

ErikEJ said...

Hippias: See my recent blog post on how to handle "Prefer 32 bit"

ErikEJ said...

Hippias: See my recent blog post on how to handle "Prefer 32 bit"

Vojtech Dohnal said...

This is a great site for SQL CE, thanks!

I am using Entity Framework 6.0 and SQL CE 4.0 and NuGet packages. Every mentiooned file is automatically copied to the release folder. However on the target machine when there is no SQL Compact 4.0 installed in GAC, I am still getting exception:

System.IO.FileLoadException
Trace:
System.RuntimeTypeHandle.GetTypeByName(System.String, Boolean, Boolean, Boolean, System.Runtime.CompilerServices.StackCrawlMarkHandle, IntPtr, Boolean, System.Runtime.CompilerServices.ObjectHandleOnStack)
na System.RuntimeTypeHandle.GetTypeByName(System.String, Boolean, Boolean, Boolean,
.....

It seems cannot find the assembly file, but the file is in the app folder.
I have aso tried the trick with assemblyBinding, but no avail...

ErikEJ said...

Vojtech: This article deals with EF4, not EF6. And the SQL Server Compact Entity Framework provider has changed to EntityFramework.SqlServerCompact dll

Rishi Jasapara said...

I get the following Warnings:
Warning 1 The value of the 'PublicKey' attribute in 'Microsoft .NET Framework 4.5 (x86 and x64)' does not match that of file 'C:\Program Files\Microsoft SDKs\Windows\v8.0A\Bootstrapper\Packages\DotNetFX45\dotNetFx45_Full_x86_x64.exe'

Can you help me out what am I missing?

ErikEJ said...

Rishi: Nope, I have no idea

Unknown said...

How I can add sql server for sql authentication as prerequisites in InstallShield LE for VS2012 in my windows C# project???

ErikEJ said...

Ruthvik: Suggest you ask Installshield support

ErikEJ said...

Ruthvik: Suggest you ask Installshield support

Unknown said...

Thanks..
For VS2012 & .Net Framework I have to use Installshield 2013 premier version? Because in limited version I can not include Sql server as prerequisites..

userD said...

Hi ErikEJ
Thank you for sharing all this info.

I've used it with a simple Winforms app, and I had to use
















to make it all work.

I haven't made an MSI, I just put the Release-folder on a partition on the machine w/o dev tools and then I'm running the .exe.

Although, the access to the db is taking much longer than when I'm running it on a machine with the developer tools installed.
Like 2-3 seconds per access, which would usually go in a splitsecond.

Any idea?

ErikEJ said...

UserD: Google swalloved your xml content! For slowness issues, see the post here: http://erikej.blogspot.dk/2013/08/faq-why-is-opening-my-sql-server.html

userD said...

Excellent. It was the Certificate Revocation List check.

Thank you!

Unknown said...

when i put the all data from 'C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private' this folder and after i build my application that time one error is occurred. and the error is
"Error 373 "amd64\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest;x86\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest" is an invalid value for the "InputManifest" parameter of the "GenerateApplicationManifest" task. Multiple items cannot be passed into a parameter of type "Microsoft.Build.Framework.ITaskItem".
"

ErikEJ said...

Pratham - suggest you ask in the MSDN forum, and provide more info or even better a console app that reproduces the issue

Paul Smietan said...

Nice blog - saved me a ton of grief!

marsifox said...

But were is your sample? I see only "Server error in..." :(

ErikEJ said...

Sample download: https://1drv.ms/u/s!AitHcOtLnuVHgwewiWcudEGRkpEt

Unknown said...

Thanks Erik, that helped me out of a hole!

Manish Rai said...

Hi Krik, I have added ErikEJ.SqlCeBulkCopy through nuget in my project but getting below error. - Found conflicts between different versions of the same dependent assembly that could not be resolved

After analysis I found that while installing ErikEJ.SqlCeBulkCopy it add below entries in app.config file








Here, Its saying newVersion="4.0.0.1" but actual version of System.Data.SqlServerCe(Microsoft.SqlServer.Compact.4.0.8876.1) assembly is showing as 4.0.0.0 in reference. After doing below manual changes in app.config, warning is gone. Is It Ok to manually edit it ?

ErikEJ said...

@Manish: Pls post on Github, I cannot see xml in Blogger commnents, and blogger comments are not the ideal place for support anyway: https://github.com/ErikEJ/SqlCeToolbox/issues/new

CSMDakota said...

I'm getting the same error as David Partham above, was there ever a follow-up to that?

"amd64\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest;x86\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest" is an invalid value for the "InputManifest" parameter of the "GenerateApplicationManifest" task. Multiple items cannot be passed into a parameter of type "Microsoft.Build.Framework.ITaskItem". NumericalDatabase


ErikEJ said...

CMSDakota: Suggest you post an issue on Github, this blog post is very old, and I have posts and advice/tools for better acheiving this goal.

Unknown said...

Hi ErikEJ,

Question, I'm on my second phase of my WPF application and my goal is to have it ready for marketing next year around summer. Its a stand alone personal software and I've decided to use SqlCe.

What API should I use: SqlCeEngine or DbContext along with EntityFramework?

Thanks for your feedback!...

ErikEJ said...

It depends - for performance or developer productivity. I would actually suggest either LINQ to SQL (or ADO.NET SqlCeConnection if performance is paramount - see my performance blog posts here)

Unknown said...

For a commercial product even though L2S is basically abandon for L2E do you recommend it? Because what I understand their will be no new features added and all attention is going forward to L2E.

So, I'm assuming L2S is recommended for SqlCe as for as my project is concern? And, do to the feature limitation in 4.0 I decided to use 3.5. Will I have a problem using version?

ErikEJ said...

Could we have this discussion on Github instead?

Unknown said...

Sure, here: ErikEJ/EntityFramework.SqlServerCompact or ?

ErikEJ said...

Yes

Mike said...

Hi.
Where download the completed solution?
link in post dont work

ErikEJ said...

@Mike: Link updated

UmarFarooq said...

I build my wpf application with access database now I want to deploy it.
Please help me to deploy this application.

ErikEJ said...

Ali: What have you tried - and what is not working? Suggest you ask on Stackoverflow or similar...