SQLite/SQL Server Compact Toolbox. This extension adds several features to help your embedded database development efforts: Scripting of tables and data, import from SQL Server and CSV files, export/migrate to SQL Server and much, much more.
The support by the author (Erik) is outstanding! Suggestions for new features or improvements can be critically discussed on Github and two out of two times already Erik implemented the changes that solved my issues very quickly.
This tool has been great for my needs. Admittedly, my needs are very simple: I just need to open SQL Compact dbs every once in a while to examine the schema & data, and occasionally run some SQL statements to update data or examine it. I've been using it that way for a number of years, and it has been very solid. It's one of the first tools I reinstall when I move to a new machine.
I've used Erik's standalone toolbox for SqlCE 3.5 previously and just started using this for working with some Sqlite dbs. I've only used a small number of the features so far but it's already made my life much easier. Thanks Erik.
By default, SQLite does not enforce foreign key constraints (sadly) - you can enable this when you add a connection to a SQLite db by selecting the "Foreign Keys" option - this will make your connection string look like this: data source=C:\Code\sq.db;Foreign Keys=True
Documentation here: https://www.sqlite.org/foreignkeys.html - and if you like the Tool, I would be very grateful for a rating or review here:
Hi Erik - Firstly, this is a great tool and is helpful in my work!
I have few questions on data diff feature and others.
1. I'm trying to convert server db(.mdf) to .sdf file.I had about 1100000 records. On choosing to do this for both schema and data, it generated multiple .sqlce (102)files with 18528KB size. Is there a way to restrict the script to a single file?
2. Once the files were generated, I added an extra record. Since the last run of the script, is there a way to find out the differential updates and generate only the delta script other than doing the .sdf file compare?
3. I wanted to compare the .sdf files and see the difference. I used this 'script database diff' option and saw the schema changes captured for the .sdf file, I am unable to capture the delta data changes between source and target. Is there an option for that?
Hello, I download the extension and watched the videos on https://sqlcetoolbox.codeplex.com/documentation and https://github.com/ErikEJ/SqlCeToolbox and erikej.blogspot.com but I can´t find any simple tutorial of how to start from zero.
I just need to solve the creation of a SQLite dabatabase AND the ClickOncePublish thing to start searching for more.
1) I create a New proyect - Visual Basic - Windows - WFA
2) With the Toolbook I create or connect to the SQLite Database
3) I install NuGet - Install-Package System.Data.SQLite
4) I can show the data base info on a DataGridView
All OK.. but when I publish the proyect and try to run I have the error "Unable to load DLL 'SQLite.Interop.dll'"
I read a lot of boards and I even have the DLL but something still don´t work.
This is something related to the Toolboox or to Visual Studio - SQlite integration?
"If you use my tools, I would be very grateful for a rating or review here:
No problem, I need to learn more and then I will post a feedback
When I activate the "Show results as grid" option in the settings then execute a "SELECT * FROM table" (for example), the grid displays but once I do anything that would scroll the grid or once I use the group by thing by clicking at the "+", Visual Studio stops working and restarts.
I'm using Visual Studio 2015 Community on Windows 10 x64.
I'm attempting to use the CE 3.5 Stand Alone Runtime to modify a single table in a legacy application that has fallen into my lap.
The Problem I am having is, when I open the database, and "Edit Top 200 Rows" The DataGrid shows up, but has Tablename-ReadOnly as its title, and I cannot edit any of the rows. I dont see any messages, just will not let me edit.
There is only a single line on the table, so I'm confused as to why It shows Read-Only.
Do you have any suggestions?
Thanks for this wonderful tool!
Hi Paul - most likely because the table that you are trying to edit does not have a primary key defined. You may be able to use a SQL UPDATE statement if you can find an usable WHERE clause.
If you use my tools, I would be very grateful for a rating or review here:
Thank You for this usefull tool. I'm writting because I have problem which I can't resolve. I have VS Community 2015. Let me describe step by step, how I install Your tool:
1. I didn't have EnityFramework and EntityFramework.SqlServerCompat so I installed it from Nuget.
2. I installed Your tool
3. I created new database. Connection is fine but then I have this red error: http://oi68.tinypic.com/2hd2l3b.jpg
4. When I want to add entity model to project I get error:
"Please add the EntityFramework.SqlServerCompact NuGet package to the project"
I have 2 question too.
1.Do I have to EnityFramework and EntityFramework.SqlServerCompat referenced in solution? Because I don't see any of these in References in project.
2. May I install 3.5 version of Your tool and have option "Add Windows Phone DataContext to current Project".
Erik - Love the Toolbox so far. So I know what I might expect in the future and at the risk of seeming pushy, do you plan for SQLite to:
1. Enhance the Build Table (beta) designer to start with a previously entered table so that I can add/delete/rename columns and then execute a Drop/Create instead of starting over when I need such changes or having to edit the previously saved script?
2. Add the equivalent of "Add SQLite-net DataAccess.net" to instead generate a Linq DataContext class together with the fully attributed entity classes corresponding to each table?
3. If so on #2, will you include relationship code, which means adding a foreign key attribute to Build Table?
For those that you intend to implement, do you have a guess as to timing?
Hi, this project is open source and free, and accepts any contributions from you and others. Regarding your suggestions:
1: I propose that you just use the "Script as DROP and CREATE" feature. Remember that SQLite does not support "ALTER TABLE"
2: LINQ to SQL is not supported by SQLite as far as I know, so no plans there.
3: See reply to 2
If you like the Tool, feel free to post a review here...
Erik - Thanks for the response.
1. I thought it would make sense to just edit the DROP and CREATE script. That's certainly easy enough.
2. Seems ignorance on my part re Linq working with SQLite is bliss. I just got it to work. In particular, I did several regular Linq queries, a DataContext.GetTable(), and several approaches to changing data, each followed by DataContext.SubmitChanges(). All the changes were made in the database.
The key is to instantiate the Linq DataContext by passing in a SQLiteConnection instead of the connection string. After that, I used the Toolbox’s generated DataAccess code as a starting point for the Linq entity classes. The key hurdle there was that I made NULL attribute database script changes after I started without corresponding changes in the entity classes. That produced a cryptic error message that took some time to eventually rundown with help from Stack Overflow.
3. I may respond to your suggestion to contribute to the Toolbox, though certainly not until I'm on a more solid footing understanding this stuff. I'm only making step-by-step progress, having just started to learn about both SQLite and Linq a couple weeks ago at your suggestion. And a great suggestion it has proven itself to be!
Thanks again. I'll post a review of the Toolbox in the next week or so.
Hi, Erik! Thanks for new version! But I got bad news.
When the project first launched, trying to activate Toolbox makes active "Properties" window, so that I have to activate Toolbox again. It was also in early versions.