tag:blogger.com,1999:blog-5238131531249720750.post7293475368813534423..comments2024-03-19T13:28:26.291+01:00Comments on Everything SQL Server Compact: Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDBErikEJhttp://www.blogger.com/profile/12641272161018114573noreply@blogger.comBlogger74125tag:blogger.com,1999:blog-5238131531249720750.post-83923653938355693702018-02-27T07:25:08.226+01:002018-02-27T07:25:08.226+01:00@Unsure : I think it would still prefer SQL Expres...@Unsure : I think it would still prefer SQL Express, but keep in mind it has a max database size of 10 GBErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-59434116347941710842018-02-22T20:17:47.222+01:002018-02-22T20:17:47.222+01:00hello, I'm not a developer by any means, but I...hello, I'm not a developer by any means, but I can tinker till something finally works! I'm actually a day trader who likes to write my own custom scripts (in C#, .Net 4.5 based) for my trading platform. Unfortunately, one of the things the platform is not good at is the way it loads historical tick by tick data (admittedly, this does ramp up the amount of data noticeably). I am considering attempting to put together something to save the data locally myself using a local/client side database, to hopefully speed things up a bit. In a situation like this one, where the database itself can become ever expanding, while there is no need for an actual server, which version of SQL would you recommend?Unsurehttps://www.blogger.com/profile/15879265576715120168noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-85718211547857295522017-12-05T20:47:17.984+01:002017-12-05T20:47:17.984+01:00Rahmat: I would not recommend using SQL lite on a ...Rahmat: I would not recommend using SQL lite on a web site, use SQL Server Express instead.ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-71326443028479170402017-12-05T18:30:47.731+01:002017-12-05T18:30:47.731+01:00Sir!
These is a problem for me. I want to attach ...Sir!<br /> These is a problem for me. I want to attach my ASP.NET MVC 5 application with SQLITE. But I can't find any solution for me. Now, I am using old one SQL Compact which is going in a good way and also supporting code first and all other entity framework features. But I want to attach SQLite because Microsoft is not going to support Compact more. Can you have any solution for ASP.NET MVC 5. Also, I have not find any example on internet to solve my problem (some examples are there which are for Core and some did not provide Entity Framework support).<br /><br />Also i want to know that, Is SQL Compact is faster or SQL SERVER Express is faster.<br />Thanks in advance<br /><br />Best Regards,Anonymoushttps://www.blogger.com/profile/03804629301196799627noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-1189471426135134912017-10-03T20:17:12.364+02:002017-10-03T20:17:12.364+02:00Furbo: Yes, but SQL CE is very limited compared to...Furbo: Yes, but SQL CE is very limited compared to SQL Server in many ways.ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-45395286502361007442017-10-03T20:14:30.956+02:002017-10-03T20:14:30.956+02:00Is it the network thing that makes that necessary?...Is it the network thing that makes that necessary? Would I be able to use CE if it were local?Furbohttps://www.blogger.com/profile/06846080431320404683noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-42136024497003489012017-10-03T20:11:58.964+02:002017-10-03T20:11:58.964+02:00Furbo: I would recommend SQL Server Express, is 10...Furbo: I would recommend SQL Server Express, is 100% compatible with larger SQL Server editions, free and works over the network, and is a small 275 MB downloadErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-31101947291099818422017-10-03T19:10:50.108+02:002017-10-03T19:10:50.108+02:00I am working with a proprietary product that has S...I am working with a proprietary product that has SQL Server output capability for reporting. The product is not very configurable on the connection, and will work with anything above SQL 2005. Basically it connects and writes data to database that encompasses about 20 tables. <br /><br />I am trying to come up with a free solution for the customers that don't have a SQL Server, that will have relatively low resource requirements that would masquerade as the SQL server the product is expecting to connect to. We would be reading from this database for updating our cloud using a C# utility we've written. We can obviously install Express, but it feels bloated for what we need, and if possible, I'd like something more compact. Because it has to look like MSSQL to the originating application, I would think that SQL Lite won't work. Preferably, the originating app could connect to this via IP (assuming that it might be on another machine on the local network). Can you advise?Furbohttps://www.blogger.com/profile/06846080431320404683noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-80704372631360911532017-09-25T14:09:50.898+02:002017-09-25T14:09:50.898+02:00Nasser: DataSets and SQL CE is not recommended and...Nasser: DataSets and SQL CE is not recommended and not supported in VS 2013+ - I suggest you switch your data acess layer to LINQ to SQLErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-40786129477982460402017-09-25T09:53:23.102+02:002017-09-25T09:53:23.102+02:00Hi,
Here is my situation
I did develop a WPF with ...Hi,<br />Here is my situation<br />I did develop a WPF with SQL CE 3.5 Visual Studio 2010 for almost 7 years ago for one of mine customer.<br />We planning to upgrade the solution to VS 2015 or 2017. Here started my problem.<br /><br />Open the solution in VS 2015, I get some error message regarding referace to sql CE 3.5 dll (this is OK since in my new development PC I had not installed this one but I have installed 4.0).<br />My issues ins that I can not open XSD file in Dataset designer. I get the following message:<br />the dataset designer in this version of visual studio is not compatible with SQL server compact connection.<br /><br />Do you have any suggestion how I can solve this issue?<br /><br />Is there any tools to open this files as I usd to in VS 2010?<br />By the way I use your solution (SQLite / SQL Server Compact Toolbox) in other program (greate tool) but I am not if it will help me with this issue.<br /><br />Best Regards<br />Nasser Anonymoushttps://www.blogger.com/profile/02436278530329523288noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-52951037484272314622017-04-23T12:03:10.954+02:002017-04-23T12:03:10.954+02:00Yes, I have a number of tools for table browsingYes, I have a number of tools for table browsingErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-28371750141527745092017-04-23T11:56:03.675+02:002017-04-23T11:56:03.675+02:00It says "runs within an app". Is there a...It says "runs within an app". Is there a way to connect to SQL Compact Edition to browse tables?NSKhttps://www.blogger.com/profile/15469572692880301138noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-86032139451276513882017-04-12T21:54:59.947+02:002017-04-12T21:54:59.947+02:00WARNING: Sqlite can't drop/alter columns/const...WARNING: Sqlite can't drop/alter columns/constraints!!Owenhttps://www.blogger.com/profile/10960651375243650575noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-30083826208194272092016-05-05T14:09:23.341+02:002016-05-05T14:09:23.341+02:00quentin: That should not be required, why do you n...quentin: That should not be required, why do you need it? Maybe we can continue the conversation here: https://github.com/ErikEJ/SqlCeToolbox/issues/new ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-66013413012216696542016-05-05T12:04:17.687+02:002016-05-05T12:04:17.687+02:00Hi Erik,
I'm currently using SQL Server Compa...Hi Erik,<br /><br />I'm currently using SQL Server Compact 3.5 for an application. However, I found that the .sdf file should be granted with Full access for Everyone. I would like to deny the deletion action in OS level (Windows 7). Do you have any idea how to do so or I should change to use another database?<br /><br />Thanks,<br />QuentinAnonymoushttps://www.blogger.com/profile/08076737925089456261noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-57874693614830114862016-04-14T22:50:04.049+02:002016-04-14T22:50:04.049+02:00Erik - Thanks for your continuing help. I'm m...Erik - Thanks for your continuing help. I'm making progress understanding the direction you've guided me to take (move from SQL CE with strongly-typed DataSets to SQLite and Linq for my standalone WinForms app), but I clearly still have some big knowledge gaps that have kept me at times from even understanding what some of the instructions/guidance meant.<br />In case it’s helpful to others to have a consolidated recap and to confirm my own understanding, here’s what I’ve done:<br />• Installed VS Community 2015 (instead of VS 2013 Express as I had been using).<br />• Installed the appropriate version of SQLite via the SQLite.org website (I gather I could have also installed it via VS | Tools | NuGet Package Manager). <br />• Installed via VS | Tools | Extensions Manager your SQL Server Compact/SQLite Toolbox.<br />• Installed via VS | Tools | NuGet Package Manager the sqlite-net codebase that’s needed for the Toolbox generated DataAccess code.<br />• Added to my VS project references to System.Data.SQLite, System.Data.SQLite.Linq, and System.Data.SQLite.EF6.<br />• Studied Linq, including the following explanation which, while a bit old from 2007, is very well written (https://msdn.microsoft.com/en-us/library/bb425822.aspx?f=255&MSPPError=-2147217396).<br /><br />This has enabled me to use your Toolbox to: (1) create SQLite databases; (2) create the database tables via the Toolbox’s generated scripts; and (3) as an alternative to #2, generate the DataAccess code that contains the classes corresponding to each table as well as the methods to programmatically create the tables via the sqlite-net codebase. This has been very helpful in getting me going.<br /><br />I started on all this when it came to light the VS DataSources-based designer I had been using to manage my SQL CE databases and their associated strongly-typed DataSets was no longer supported as of VS 2013. I have been thinking your Toolbox is designed to replace that functionality, and to some extent I can see that it does. But I’m uncertain whether it will (as you continue to enhance it)generate the Linq DataContext code (including fully attributed entity classes with relationships) based on the database/table designer input? This looks to me to be the Linq equivalent of a strongly-typed DataSet along with the various ADO.Net objects if I’m getting all this.<br /><br /> Also, is there a more recent version of your Toolbox Visual Guide of Features than the one from August, 2014?<br /><br />Thanks again for your help. SteveAnonymoushttps://www.blogger.com/profile/05119682377524082771noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-77782778136917211382016-04-09T11:45:45.576+02:002016-04-09T11:45:45.576+02:00Steve: Sorry, I missed that you meant "VS Exp...Steve: Sorry, I missed that you meant "VS Express" by VSE2013. Use VS Community, as VS Express does not support any DDEX extensibility or extensionsErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-58156399396407458522016-04-08T17:10:10.092+02:002016-04-08T17:10:10.092+02:00Erik: In trying to follow your suggestion to use S...Erik: In trying to follow your suggestion to use SQLite and Linq, I may be trying to do something that isn't possible with VS Express 2013, the IDE I’m using as noted in my first post. <br /><br />1. I installed SQLite (System.Data.SQLite (x86 / x64)) in a test WinForms app using NuGet Package Manager. <br />2. I did Add / New Item / Linq to Sql Classes in my VS project. That was the only Add option that seemed connected to your suggestion (as I recall from VS Express 2010, there was as Add Local DB option, but I guess that’s gone in 2013). <br />3. I did Add New Data Source / Database / DataSet from Data Sources which failed to show any SQLite. I thought that would be true and that I’d need your Toolbox.<br />4. I tried to install your SQL Server Compact/SQLite Toolbox 4.5.0.2 from MSDN, but hit the restriction that the Toolbox requires VS Pro. <br /> <br />Is there any way to get SQLite database designer support in VS Express 2013? If not, is that also true for SQL CE?<br /><br />Thanks for any continued help.<br />Anonymoushttps://www.blogger.com/profile/05119682377524082771noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-56772112571892951632016-04-05T22:07:31.094+02:002016-04-05T22:07:31.094+02:00Erik - Thanks for the advice. It will take me aw...Erik - Thanks for the advice. It will take me awhile to absorb the implications of moving to LINQ, which I assume will require code changes everywhere I reference or update the DataSets and their tables. Not as simple as replacing the backend DBMS which the DataSets mask. <br /><br />Your suggestion means that I now have 2 different though related decisions: (1) LINQ vs DataSets; and (2) SQL CE vs SQLite. I'm guessing any of the 4 combinations will work. If that's not right, please let me know.<br /><br />Your blog is great. Very clearly written and thorough. I appreciate the effort you've put into it and in giving me your personal advice.Anonymoushttps://www.blogger.com/profile/05119682377524082771noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-28543836708779772372016-04-03T09:57:39.224+02:002016-04-03T09:57:39.224+02:00Steve: Forgot the link : http://erikej.blogspot.dk...Steve: Forgot the link : http://erikej.blogspot.dk/2013/10/sql-server-compact-4-desktop-app-with.htmlErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-86655018487974217182016-04-03T09:57:12.434+02:002016-04-03T09:57:12.434+02:00CAD bloke/Steve Rehling: If you are used to a stro...CAD bloke/Steve Rehling: If you are used to a strongly typed RDBMS, where you can only put numbers in number columns, and dates in datetime columns, you are in for a surprise with SQLite, which basically is "untyped" - this is what I mean by "poor". So a bucket versus a filing cabinet.<br /><br />Steve: I suggest you use the approach I descibe here instead of DataSets, which incur way to much overhead for an embedded database.ErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-63374501037561293162016-04-02T20:42:06.996+02:002016-04-02T20:42:06.996+02:00I have the same question as CAD bloke. I am using...I have the same question as CAD bloke. I am using SQL CE in a WinForms app that's developed with VS Express 2013 and deployed via ClickOnce. I want to maintain the low overhead deployment capability.<br /><br />My RDBMS needs are very modest. My databases are very small (2 with < 5 tables having at most 20 columns and 100 rows each and one with < 25 tables having a similar column/row profile). Also, I only do individual table "Fill" and "Update" operations via TableAdapters. No joins, referential integrity, etc. <br /><br />I discovered the future of SQL CE when I found the database designer doesn't work anymore in VSE2013. Helpful to know that MS has a 2021 support end date. I'd just as soon get on with moving to SQLite (if that's what makes most sense for me), but am open to installing your Toolbox to get SQL CE database designer support back for now so I can make changes and still get the generated strongly-typed dataset code.Anonymoushttps://www.blogger.com/profile/05119682377524082771noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-52403479340886694442015-10-12T01:05:05.599+02:002015-10-12T01:05:05.599+02:00"Microsoft suggests using SQLite instead, but..."Microsoft suggests using SQLite instead, but I find that a poor RDBMS when coming from SQL Compact"<br /><br />I would love to hear more of your thoughts on why you think SQL CE is better, or at least in what generic usages it would be better, or worse. I am trying to figure out what sort of a disadvantage using SQLite would be compared to SQL CE. I know SQL CE is supported for another 5-6 years but that's actually not a great deal of time, I find time accelerates as you get older. :/CAD blokehttps://www.blogger.com/profile/12773177817279221087noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-81961522250915551032015-08-04T08:13:37.078+02:002015-08-04T08:13:37.078+02:00Dave: SQL Compact is supported until 2021. Microso...Dave: SQL Compact is supported until 2021. Microsoft suggests using SQLite instead, but I find that a poor RDBMS when coming from SQL CompactErikEJhttps://www.blogger.com/profile/12641272161018114573noreply@blogger.comtag:blogger.com,1999:blog-5238131531249720750.post-37166139713368741142015-08-04T02:56:46.484+02:002015-08-04T02:56:46.484+02:00Hi Erik, I'm looking for a database that I can...Hi Erik, I'm looking for a database that I can use offline, build for the .NET 4.0+ framework. I see that SQL Compact Edition 4.0 does the job BUT I have read that Microsoft is dropping support for it. I was wondering if you can comment on this and provide some reasons why this is and what should be the solution moving forward.<br /><br />Thanks, Dave.Anonymoushttps://www.blogger.com/profile/14265852706540622627noreply@blogger.com