jeudi 6 août 2015

Issues with Mono.Data.Sqlite and System.Data.Linq

I'm working with SQLite database and my project relies on three DLLs, taken from Mono (for cross-platform development): Mono.Data.Sqlite, System.Data, System.Data.Linq.

I have a class that represents user:

[Table(Name = "Users")]
public class User
{
    [Column(Name = "Id", IsPrimaryKey = true)]
    public int Id { get; set; }

    [Column(Name = "Username")]
    public string UserName { get; set; }

    [Column(Name = "Password")]
    public string Password { get; set; }

    [Column(Name = "IP")]
    public string IP { get; set; }

    [Column(Name = "Role")]
    public UserGroup Role { get; set; } /* Just an enum */
}

And for example here is how i add new user to database:

 using (var conn = new SqliteConnection("Data Source=database.db;DbLinqProvider=sqlite;Password=somePass"))
        {
            conn.Open();

            using (var db = new DataContext(conn))
            {
                var users = db.GetTable<User>();

                users.InsertOnSubmit(new User()
                {
                    UserName = "John",
                    Password = "testPass11",
                    IP = "127.0.0.1",
                    Role = UserGroup.User
                });

                db.SubmitChanges();

                // For test
                var user = users.FirstOrDefault(x => x.UserName.Equals("John"));

                if (user == null) Console.WriteLine("Not found");
                else Console.WriteLine("{0}|{1}", user.UserName, user.Password);
            }

            conn.Close();
        }

Record is successfully inserted, but when i'm searching through table i get an exception (stack trace point to line 34):

Mono.Data.Sqlite.SqliteException (0x80004005): SQLite error near ".": syntax error
---
Line 34:
var user = users.FirstOrDefault(x => x.UserName.Equals("John"));

BTW, also i need to increment ID automatically when new record is inserted, so i modify an attribute in User class:

[Column(Name = "Id", IsPrimaryKey = true)]
public int Id { get; set; }

Is now changed to:

[Column(Name = "Id", IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true)]
public int Id { get; set; }

Now i get another exception:

Mono.Data.Sqlite.SqliteException (0x80004005): SQLite error near "SELECT": syntax error 
in Mono.Data.Sqlite.SQLite3.Prepare(SqliteConnection cnn, String strSql, SqliteStatement previous, UInt32 timeoutMS, String& strRemain)
in Mono.Data.Sqlite.SqliteCommand.BuildNextCommand()
in Mono.Data.Sqlite.SqliteCommand.GetStatement(Int32 index)
in Mono.Data.Sqlite.SqliteDataReader.NextResult()
in Mono.Data.Sqlite.SqliteDataReader..ctor(SqliteCommand cmd, CommandBehavior behave)
in Mono.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
in Mono.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
in System.Data.Common.DbCommand.ExecuteReader()
in System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
in System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
in System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
in System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
in System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
in System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
in System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

Aucun commentaire:

Enregistrer un commentaire