SQL Server Compact/SQLite Toolbox add-in for Visual Studio. This add-in adds several features to help your embedded database development efforts: Scripting of tables and data, import from SQL Server and CSV files and much, much more.
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.
Hi, sorry to her that, I have logged an issue here: https://github.com/ErikEJ/SqlCeToolbox/issues/113 - would be very helpful if you could provide more details (database engine and engine version in use, for example)
I just tried Export SQL Server to SQLite and get an error. -- Error Code = 1 Message = SQL Logic Error or missing Database near "'-'" = Syntax error Result = Error -- Looks like there was an error export Field Default Value.
Would it be possible to add a Close connection option to the pop-up menu of a database? I have to restart VS to be able to delete the DB I previously opened by SQL Server SQLite Toolbox. Otherwise it's a great tool!
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.
I have installed the Sqlite Toolbox, successfully connected to an existing sqlite database, and added DataAccess.cs to my project. However, the table and column attributes are not recognized:
What is the namespace for attributes in the DataAccess.cs?
What references are needed?
Hi Gary, I beleive there is a link to this blog post at the top of the DataAccess.cd file: See the blog post here for help on using the generated code: http://erikej.blogspot.dk/2014/10/database-first-with-sqlite-in-universal.html
Erik - I am having the same problem as Gary (though with a database I created using the Toolbox), plus these additional problems:
-- The generated DataAccess code had "using SQLite" instead of "using System.Data.SQLite", which made the SQLiteConnection class unrecognizable.
-- After fixing the using, SQLiteConnection was ok, but the CreateTable methods were not recognized. There are several CreateXxx methods in the SQLiteConection class, just not CreateTable.
I followed the blog you suggested, including installing the 2 additional Windows Phone and Windows RT extensions, all using the VS Extensions Manager. The problems are still there.
I can see how the Toolbox will be a big help once I'm past the startup challenges. Thanks for your help. Steve
Cincy Steve: As noted above, the generated code is for use with the sqlite-net class code, not with System.Data.Sqlite - please read the blogpost linked above. The same link should be at the top of the generated code.
I have a database that uses DateTimeFormat=Ticks in the connection string. When a DateTimes is written to the database, the value of the Ticks property is written. When I use your tool to query the database, I get an error from DateTime.ParseExact with a very long stack trace.
Can you fix your tool so it checks that property in the connection & interprets the value properly? There's also a property in the connection that tells you what value to use for the DateTimeKind property of the DateTime struct.
Hi Tony, I have made some fixes for that recently, can you describe the issue in more detal here, and tell me the exact extension version you use (from the about dialog in the add-in): https://github.com/ErikEJ/SqlCeToolbox/issues/new
after making the connection , I'll create the data source , and give an error that is not supported, it can be. It is to do a tutorial on how to connect and preecher data grid .
thank you for tool
The Data Source support in my DDEX provider is limited to saving connections on server explorer for use with the EF Tools. Not for creating DataSets, which I do not recommend for using with SQL CE anyway. If you want a quick data access layer, just use the LINQ to SQL code generation
Like using your tool, but I private install SQL CE with my app. Any thoughts on how to get your tool to work with private install? Also, I find this error occasionally https://support.microsoft.com/en-us/kb/974247 Any thoughs?
Can SQL Server Compact/SQLite Toolbox add-in help me use SQL statements to do operations on SQLite on UWP/Windows 10 environment (desktop, mobile etc.)?
I am not able to find that detail in the description of the package...
I have used your plug in in Visual Studio 2015 Community and it works fine. I have manged to script out LocalDB Schema and Data and created .sql file. Only DB tables were scripted out. How to script out Stored Procedures from the same LocalDB. All this will be uploaded to the SqlServer 2012 Developer.Thank you for your time.