ErikEJ/SqlCeToolbox

ExportSqlCE40: Export SqlCe uniqueidentifier column data to SQLite as binary

alexey-leonovich opened this issue · 8 comments

alexey-leonovich commented

Hello.
If I use the following command to export my test SqlCe database to SQLite:

ExportSQLCE40.exe "Data Source=test.sdf;" "test.sql" sqlite

it creates insert statement where uniqueidentifier column data is inserted as text (e. g. '65c6b3fd-e487-464a-bf79-fb3cebe9c9bf').
Is it possible make it to create inserts as binary (e. g. X'a8828ddfef224d36935a1c66ae86ebb3')?
I've tried Data Source=entities.sdf;BinaryGUID=True; but looks like your tool doesn't understand that:

Error: System.ArgumentException: Keyword is not supported: "binaryguid".

Thank you.

ErikEJ commented

"BinaryGUID" is not a valid SQL Server Compact connection string keyword, correct.

There a many opinions about storage of Guids with SQLite - dotnet/efcore#15078

Is it possible make it to create inserts as binary

Anything is possible! But it would have to be an opt-in flag to avoid breaking changes - interested in doing a PR?

alexey-leonovich commented

interested in doing a PR?

I'm afraid I don't have enough skills to try to make these code changes(

ErikEJ commented

@alexey-leonovich Is your example above using the same guid value?

If not, could you provide an example that does?

alexey-leonovich commented

@ErikEJ sorry, missed your question somehow.
Binary form X'a8828ddfef224d36935a1c66ae86ebb3' equals to text form 'DF8D82A8-22EF-364D-935A-1C66AE86EBB3'

ErikEJ commented

How do you get from one to the other?

alexey-leonovich commented 
Guid guid = Guid.Parse("df8d82a8-22ef-364d-935a-1c66ae86ebb3");
var bytes = guid.ToByteArray();
var hex = new StringBuilder(bytes.Length * 2);
foreach (var b in bytes)
    hex.AppendFormat("{0:x2}", b);
var guidString = hex.ToString();

or simply via https://toolslick.com/conversion/data/guid

ErikEJ commented

Thanks, I will consider implementing an option to use this format