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.
Great tool, only error I receive is when trying to create a report using the report wizard. When creating a data source you would get "error occurred while retrieving the information from the database" or won't even attempt to connect. Any help with that?
Absolutely incredible. A lot of fantastic options, works without any hick-ups (thus far) and just makes my work that much easier. My only complaint would be that it does not display data types for SQLite, but rather generic db types. I would much rather choose from types listed on https://www.sqlite.org/datatype3.html .
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.
Great job! Thanks for this tool. It is just amazing. Quick question. Is there an API (DLL) so that the Script generation of SQL Server Database Schema and Data to SQLite can be called inside a C# code?
Glad you like it, feel free to post a review here! You can use my API, yes: http://erikej.blogspot.dk/2013/03/sql-server-compact-code-snippet-of-week.html (Just replace DB4Repository with ServerRepository)
I have another issue when calling the assembly:
The error meaasage is the following:
The type or namespace name 'ErikEJ' could not be found (are you missing a using directive or an assembly reference?).
Any idea about what's missing? Thanks in advance for your reply knowing your busy schedule.