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

Question

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?

Answer 1

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 :).

Answer 2

It looks like I will need to change IDs to strings instead of Guids.

I already seem to have to do this for bools, but doing this just to wrap IDs would be pointless:

public int LoginEnabledPropertyValue { get; set; }

[SubSonicIgnore]
public bool LoginEnabled
{
     get
     {
          return (LoginEnabledPropertyValue > 0 ? true : false);
     }
     set
     {
          LoginEnabledPropertyValue = (value ? 1 : 0);
     }
 }

Answer 3

If using GUIDs without BinaryGUID=False; flag you have to build queries using AddWithValue method, I am doing it like this:

string SQL = "select count(*) from Groups where ID = @id";

SQLiteCommand cmd = new SQLiteCommand(SQL);

cmd.Parameters.AddWithValue("@ID", groupId);

cmd.Connection = sql_con;

var ret = cmd.ExecuteScalar();