After more than 370.000 downloads, version 4.2 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the CodePlex issue tracker
The main features in this release have been improved integration with SQL Server & SQLite, and a number of improvements to the SQL editor.
This version is a recommended update for anyone using this tool.
Improved SQL Server integration
I have previously used this old tip from Jon Galloway for prompting users for a connection to a SQL Server/SQL LocalDB/SQL Azure database, but this caused a number of issues when connecting to LocalDB and SQL Azure, so for this release, you will use Server Explorer to create SQL Server connections, and then I will refer to these from the Toolbox.
So to interact with SQL Server from the Toolbox (for example for scripting, generating DGML files or Exporting a full database for SQL Server to SQLite, first connect to the SQL Server via Server Explorer:
Then launch the Toolbox (you can do that directly from Server Explorer), and select the relevant connection from Server Explorer from the dropdown list:
Export SQLite to SQL Server (and LocalDB/Express)
It is now possible to migrate a SQLite database to SQL Server without running creating any intermediate scripts. Simply right click your SQLite database in the Toolbox , and select the “Migrate to SQL Server” option:
Select your Server Explorer SQL Server connection (as described above), and you can follow the progress of the export via the Visual Studio status bar:
Many thanks to @thughesit for help with improving this feature.
As SQLite is much more loosely type than SQL Server, a new option have been added that will truncate string (causing data loss) if a string has been defined with a length like nvarchar(255) and the length of the data stored in SQLite actually exceeds this. This option is disabled by default. Any truncations will be logged to %temp%\SQLiteTruncates.log
You can now also migrate the other way, from SQL Server to SQLite, without any intermediate scripts. Right click the root “Data Connections” node of the Toolbox and select “Export SQL Server to SQLite (beta)”
Choose the Server from the dropdown list:
Choose the tables to Export:
And specify the name for the new SQLite database file, and you can follow the progress of the Export via the Visual Studio status bar.
SQL editor improvements
The query editor has received a number of highly requested improvements:
Improved document handling: There is now both a “Open” (1) “Save” and “Save As” (2) buttons, and each editor window is aware of its underlying document. If there is a document open, it is indicated in the window caption, and a * symbol indicates if the document has unsaved changes:
Keyboard shortcuts: Keyboard shortcuts have been added for "Execute" query: F5, Save: Ctrl+S
and Open: Ctrl+O – a long standing request finally implemented
Export results as CSV: A new button has been added, that will export the first results as a csv (Excel) file, saving you having to copy and paste the results to Excel.
SQLite PRAGMAs: It is now possible to execute PRAGMA commands with SQLite
Other Improvements
Migrating a SQL Compact or SQLite database to SQL Server no longer blocks the UI.
Exporting a database from SQL Server no longer blocks the UI.
No more Error Reporting dialog, I am now using Exceptionless for error reporting instead.
Improved formatting of DataAcccess.cs sqlite-net code
Improved initial directory when adding SQLite and SQL Compact connections
Improved error information when SQLite datetime parsing errors occur (SQLite allows you to put anything in a column defined as datetime, apparently!)
Improved error information when SQL Server script execution errors occur (would mainly happen when exporting from SQLite to SQL Server)
Closing the Edit Data grid now closes the connection to the database, meaning that the Toolbox will have no open connection to the database file when all Edit Grids are closed.
Update to version 1.0.96 of SQLite ADO.NET provider
Bug fixes
Migrate to SQL Server feature was broken
Merge Replication Subscription creation was broken
Edit column should not show "Primary Key" column, and not allow editing of column name
Opening the SQL Editor sometimes crashed VS
FormatException in Explorer window
24 comments:
Hello Erik,
is there any way how to rename "item" with DB file in Data Connection? All my databases (about 15 projects) have name Data.sdf and it's really tricky to see these names everythere. In standalone application there is added (1), (1)(2), (1)(2)(3)... but it is crazy as well. :-)
Best regards
Vladimír Klaus
Vladimir: No, this is currently not possible - but I (or you) could add that feature! https://sqlcetoolbox.codeplex.com/workitem/12059
Playing with the 4.2 release and like it so far.
Two requests, however, for the "Export SQL Server to SQL Compact 4.0" feature:
* I need the ability to add tables to an existing database file rather than overwriting the existing file.
* Some way to enable encryption when creating a new database file
Thanks!
Marty: Thanks for the kind Words, if you like it, feel free to post a review on VS Gallery! Your requests can already be fulfilled!
1: Use the script "SQL Server Data and Schema" feature to script individual tables and then run the script against your SQL CE database.
2: Export the database, then use the Maintenance menu/"Set password" feature to encrypt the new database file
Thanks for the quick reply, Erik.
I guess I neglected to mention that I'm importing about 1.8 million rows... Not really practical to script that as individual insert statements. ;-)
Thanks for the password info - I guess I didn't realize that encryption was implied by setting a password (and in fact, I'm not certain that setting a password really does encrypt the database - TechNet seems to suggest that encryption needs to be specified (via the "encryption mode" connection string setting) at database creation time. https://technet.microsoft.com/en-us/library/gg592949%28v=sql.110%29.aspx
Marty: Behind the scenes, the transfer takes place via scripts anyway! Maybe you can use my SqlCeBulkcopy library - fast and Works with a DataReader?
Re encryption: setting a password does indeed encrypt a database, and you cannot have an encrypted database without a password.
Hello Erik,
renaming works great!
Thank you very much
Vladimír
Vladimir: Thanks for the feedback, if you like the Toolbox I would be very grateful for a review here: https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1
Erik: I'd like to write a review but I need some rating (not sure what it is and how to get any). :-(
Vladimir: Sign in - click on the stars under the text: Your rating!
Erik: DONE :)
What is the default encryption mode used when setting a password? Engine Default or Platform Default? Would it be possible to expose that setting in future versions?
Fire2K: See https://technet.microsoft.com/en-us/library/gg592949(v=sql.110).aspx - default is "Platform Default" - but only applies when creating a database file, and can be set via a simple connection string option
Very new, and obviously missing something. Still using VB.
Dim conString As String = "Data source=c:\ProgramData\ees\Photography\eesData.db"
Try
Using conn As New SQLite.Net.SQLiteConnection(conString)
End Using
What am I doing wrong?
Thank you.
Robert: What is wrong?
I'm getting the following error:
Error BC30516 Overload resolution failed because no accessible 'New' accepts this number of arguments.
Ugh... I may have solved my own problem. The following code seems to work, but I'll have to do more testing:
Using conn As New SQLite.Net.SQLiteConnection("", conString)
Thank you for the quick reply. Terrific product.
I don't know if you received my last post, but it's now working with the following code:
Dim conString As String = "Data source=c:\ProgramData\ees\Photography\eesData.db"
Try
Using conn As New SQLite.Net.SQLiteConnection("", conString)
End Using
Catch ex As Exception
End Try
However, there is no open method for conn
Shouldn't there be conn.open*()
Thanks.
Robert: Intellisense should tell you!
No, there isn't an open() method, but in the e-book it shows that once the connection is established the open() method should be used.
I'm trying to establish a connection at runtime for my application.
Thanks.
Hello Erik,
I'm trying to upgrade project (.sdf DB) from VS 2010 Pro project to VS 2013 Pro project. In VS 2010 a used SQLCE 3.5 DB and created Dataset form it.
In VS 2013 it's not possible to open the DataSet in Dataset Designer - wrong version. So I decided to upgrade file to vesrion 4.0 and then create new DataSet from this upgraded file.
I installed Your Toolbox and I upgrade .sdf file from 3.5 to 4.0 version. No errors.
But when I'm trying to Add New Data Source (and create DataSet) via Data Source Configuration wizard, I'm getting message on screen Choose Your Databse Objects: "An error occured while retrieving the information from the database: The specified type is not supported by this selector."
Can You help me with this?
Thanks.
Pavel
Pavel: the DDEX provider is Simple as stated in the name, and only exists to support the EF Wizards. https://github.com/ErikEJ/SqlCeToolbox/issues/39 - but in addition, using DatSet with sql ce is a bad idea, as all data is buffered twice in memory. Use linq to sql or EF instead. The toolbox includes tools to quickly generate both linq to sql or ef code.
If you use my tools, I would be very grateful for a rating or review here:
https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/view/Reviews
how do i connect the generated DataAccess.cs file with my xaml files
Franics: Did you read this? http://erikej.blogspot.dk/2014/10/database-first-with-sqlite-in-universal.html
Post a Comment