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