public void TestQueryData() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); CreateTableTemplate createTableTemplate = new CreateTableTemplate(); createTableTemplate.TableName = "student"; createTableTemplate.Version = 1; createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER)"; PrivateObject obj = new PrivateObject(manager); obj.Invoke("CreateTableIfNotExists", createTableTemplate); var version = manager.GetTableVersion("student"); Assert.AreEqual(1, version); List <SqlTemplate> sqlTemplates = new List <SqlTemplate>(); for (int i = 0; i < 1000; i++) { SqlTemplate sqlTemplate = new SqlTemplate(); string sql = string.Format("INSERT INTO student values(?, ?)"); sqlTemplate.SqlExpression = sql; sqlTemplate.Params = new object[] { "student" + i, 20 }; sqlTemplates.Add(sqlTemplate); } int result = manager.ExecuteDML(sqlTemplates); Assert.AreEqual(1000, result); SqlTemplate querySqlTemplate = new SqlTemplate(); querySqlTemplate.SqlExpression = "SELECT * FROM student LIMIT 200"; var items = manager.QueryData(querySqlTemplate); Assert.AreEqual(200, items.Count()); } finally { DeleteDatabase(dbFilepath); } }
public void TestCreateTableIfNotExists() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); CreateTableTemplate createTableTemplate = new CreateTableTemplate(); createTableTemplate.TableName = "student"; createTableTemplate.Version = 1; createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER)"; PrivateObject obj = new PrivateObject(manager); obj.Invoke("CreateTableIfNotExists", createTableTemplate); var version = manager.GetTableVersion("student"); Assert.AreEqual(1, version); createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER," + "classroom varchar(100))"; createTableTemplate.Version = 2; obj.Invoke("CreateTableIfNotExists", createTableTemplate); version = manager.GetTableVersion("student"); Assert.AreEqual(2, version); } finally { DeleteDatabase(dbFilepath); } }
public int CreateTableIfNotExists(CreateTableTemplate createTableTemplate) { lock (_dbLocker) { string tableName = createTableTemplate.TableName; int version = createTableTemplate.Version; string createSql = createTableTemplate.CreateSql; int dbTableVersion = GetTableVersion(tableName); if (version > dbTableVersion) { int effectRows = 0; SqlTemplate createSqlTemplate = new SqlTemplate(); createSqlTemplate.SqlExpression = createTableTemplate.CreateSql; effectRows += DropTableIfExists(tableName); effectRows += ExecuteNonQuery(createSqlTemplate); effectRows += UpdateTableVersion(tableName, version); return(effectRows); } else { return(0); } } }
public void TestInsertOrReplaceDatas() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); CreateTableTemplate createTableTemplate = new CreateTableTemplate(); createTableTemplate.TableName = "student"; createTableTemplate.Version = 1; createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER)"; PrivateObject obj = new PrivateObject(manager); obj.Invoke("CreateTableIfNotExists", createTableTemplate); var version = manager.GetTableVersion("student"); Assert.AreEqual(1, version); string json = @"[ { ""name"": ""marray"", ""age"": 20 }, { ""name"": ""Jack"", ""age"": 21 } ]"; TableInfo tableInfo = new TableInfo(); tableInfo.TableName = "student"; tableInfo.ColumnNames = new string[] { "name", "age" }; IEnumerable <Dictionary <string, object> > datas = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(json); var effectRows = manager.InsertOrReplaceDatas(tableInfo, datas); Console.WriteLine("effectRows: " + effectRows); SqlTemplate querySqlTemplate = new SqlTemplate(); querySqlTemplate.SqlExpression = "SELECT * FROM student"; var items = manager.QueryData(querySqlTemplate); Assert.AreEqual(2, items.Count()); json = @"[ { ""name"": ""Jack"", ""age"": 30 } ]"; datas = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(json); effectRows = manager.InsertOrReplaceDatas(tableInfo, datas); Console.WriteLine("effectRows: " + effectRows); querySqlTemplate = new SqlTemplate(); querySqlTemplate.SqlExpression = "SELECT * FROM student"; items = manager.QueryData(querySqlTemplate); Assert.AreEqual(30.ToString(), items.Last()["age"].ToString()); } finally { DeleteDatabase(dbFilepath); } }