C# – Sqlite Subsonic C#: Guid is saving as Guid with SQL, but with strange characters when using code

csqlitesubsonic

I am using System.Data.SQLite with an sqlCommand String, this saves a Guid as expected.

Using code I strange Characters save instead of Guid as follows: ù“•»I={E±gÒ §[,

Code that seems to generate strange characters (as seen in SQLite Administrator):

...
// Constructor in Class
public ProfileUserAssignment()
{
    ID = Guid.NewGuid();
    _IsNew = true;

    SetDefaults();
}
...

...
// Save Method in same class
public ProfileUserAssignment Save()
{
    if (_IsNew)
    {
        Made4Print.SQLite.Repository.Add(this);
    }
    else
    {
        Made4Print.SQLite.Repository.Update(this);
    }

    return Get(this.ID);
}
...

Code that saves Guids as expected:

// Create Administrator User
using (System.Data.SQLite.SQLiteConnection connection = new System.Data.SQLite.SQLiteConnection(Made4Print.SQLite.GetProvider().ConnectionString))
{
    connection.Open();
    using (System.Data.SQLite.SQLiteCommand sqlCommand = new System.Data.SQLite.SQLiteCommand(connection))
    {
        StringBuilder sqlQuery = new StringBuilder();
        sqlQuery.Append("INSERT INTO [Users] ( ");
        sqlQuery.Append("[ID], ");
        sqlQuery.Append("[FirstName], ");
        sqlQuery.Append("[LastName], ");
        sqlQuery.Append("[Username], ");
        sqlQuery.Append("[Password], ");
        sqlQuery.Append("[Email], ");
        sqlQuery.Append("[Phone], ");
        sqlQuery.Append("[MobilePhone], ");
        sqlQuery.Append("[LoginEnabledPropertyValue], ");
        sqlQuery.Append("[SendEmailsPropertyValue], ");
        sqlQuery.Append("[SystemPropertyValue] ");
        sqlQuery.Append(" ) VALUES ( ");
        sqlQuery.Append("'2bdcac4d-019f-4213-b635-86ae8f7d757e', ");
        sqlQuery.Append("'Administrator', ");
        sqlQuery.Append("'User', ");
        sqlQuery.Append("'xxxxx', ");
        sqlQuery.Append("'" + Security.HashPassword("xxxxx") + "', ");
        sqlQuery.Append("'', ");
        sqlQuery.Append("'', ");
        sqlQuery.Append("'', ");
        sqlQuery.Append("1, ");
        sqlQuery.Append("1, ");
        sqlQuery.Append("1 ");
        sqlQuery.Append(" ) ");
        sqlCommand.CommandText = sqlQuery.ToString();
        sqlCommand.ExecuteNonQuery();
    }
    connection.Close();
}

Using SQLite Administrator, I see both the Guids as expected in one table or the characters in another table (havent tried doing both in on one table)

NEW INFO FOUND:

I just found this at: http://www.connectionstrings.com/sqlite

Store GUID as text
Normally, GUIDs are stored in a binary format. Use this connection string to store GUIDs as text.

Data Source=filename;Version=3;BinaryGUID=False;

Any comments on this?

Best Answer

SQLite doesn't have the notion of "types" from what I've read so my first guess is that it's translating the value to a string, and your encoding it making it crazy. So, first question: what is the default encoding on your DB and default encoding on your machine?

Next - is it not working? Sometimes it's a good idea to enjoy the sausage without asking to see the kitchen. In this case SQLite is doing something funky, but if it returns the values OK then one might suggest you shouldn't be looking at them :).

Related Topic