Problem specifying custom queries against a Sqlite database (on iOS) with Guid ID columns.

DaveBoggustDaveBoggust USMember, University ✭✭

I can retrieve data ok, including the ID column values. The problem is how I create a custom query that includes a Guid ID value to implement a join that I can use to re-query the database. Do I use a db command? How do I get the System. Guid value into the query? A SqliteParameterCollection? Any help would be much appreciated.

Best Answer

Answers

  • rene_ruppertrene_ruppert DEXamarin Team, University, XamUProfessors Xamurai

    There is no special "GUID" datatype in Sqlite. So I presume it's just a string. You should be able to query just like any other column with string content and also use it in a join.

    Can you give more information about your database schema and also some example queries?

  • DaveBoggustDaveBoggust USMember, University ✭✭

    Ok, so the this is my situation. I'm creating a small Forms app to access a Sqlite file generated by a much bigger Xamarin (non-Forms) app. If I view the schema of the file in sqlitebrowser, the column of interest in the Customers table is defined as follows:

    CustomerID   UNIQUEIDENTIFIER   NOT NULL   PRIMARY KEY   DEFAULT   (newid)
    

    The values in this column are valid Guid's (e.g. e1344cc6-9350-4bcc-8431-d86d3a02e01c) and I can view them in this format by executing the following:

    select hex(CustomerID) from ShipTo
    

    and I can query those out of the database without a problem using the following code:

    var filename = "Database_2_3.sdf";
    var libraryPath = NSFileManager.DefaultManager.GetUrls (NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomain.User) [0].Path;
    var path = Path.Combine (libraryPath, filename);
    var db = new Resco.Data.SQLite.SQLiteDatabase ();
    db.ConnectionString = Database.MakeConnectionString (path, null);
    var exists = db.DatabaseExists (path);
    var queryText = "select * from ShipTo where ERPShipToID = '1382-0'"; --- this returns 1 row with the ID in column 5
    var reader = db.ExecuteReader (queryText, 0x0, "");
    System.Guid employeeID;
    using (reader) {
        var output = reader.FieldCount;
        employeeID = (Guid)reader.GetValue(4);
    }
    

    What I want to be able to do is then re-query the database and pass the employeeID value into a query as a parameter, for example (note: I know this isn't correct as it stands, for one thing the IEnumerable cast isn't valid! The commented out lines are my other futile efforts):

    List<Guid> params = new List<Guid>{employeeID}; 
    IEnumerable<object> enums = (IEnumerable<object>)params;
    var cmd = db.GetCommand("select CompanyName from Customers where CustomerID = @param1", enums);
    

    // SqliteParameterCollection p = new SqliteParameterCollection();
    // cmd.Parameters.Add(p);
    // ("@param1", DbType.Guid).Value = employeeID;
    var reader = cmd.ExecuteReader ();
    using (reader) {
    var output = reader.FieldCount;
    var c = (string)reader.GetValue(0);
    }

    I'm not even sure if this is the correct approach by using the db.command in this way. Is there a better way? I know that under the covers the ID values will be strings but if I pass in a string the join doesn't seem to work hence my attempt to pass in a Guid.

  • DaveBoggustDaveBoggust USMember, University ✭✭

    Thanks for the suggestion Rene, but I finally managed to get it working. I didn't realize but I actually had a conflict between two SQLite components in my project and once I had resolved that things went much more smoothly. I used the following approach successfully:

    using (var command = new SqliteCommand ()) {
    command.Connection = (Mono.Data.Sqlite.SqliteConnection)db.Connection;
    command.CommandText = "SELECT CompanyName FROM [Customers] WHERE CustomerID = @CustID";
    command.Parameters.Add ("@CustID", DbType.Guid).Value = pID;
    var reader = command.ExecuteReader ();

Sign In or Register to comment.