public async Task RawQueryAsync()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
await context.Persons.InsertAsync(person);
await context.Skills.InsertAsync(new SQLiteSkill
{
PersonId = person.Id,
Title = "HTML/CSS"
});
await context.Skills.InsertAsync(new SQLiteSkill
{
PersonId = person.Id,
Title = "JavaScript"
});
var personsSkills = await context.RawQueryAsync <SQLitePersonSkill>(@"SELECT [Persons].[Name] + ' ' + [Persons].[Family] AS FullName,
[Skills].[Title] AS Skill
FROM [Persons] JOIN [Skills]
ON [Persons].[Id] = [Skills].[PersonId];");
Assert.True(personsSkills.Count() > 0);
}
public async Task RawScalatQueryAsync()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
context.Persons.Insert(person);
context.Skills.Insert(new SQLiteSkill
{
PersonId = person.Id,
Title = "HTML/CSS"
});
context.Skills.Insert(new SQLiteSkill
{
PersonId = person.Id,
Title = "JavaScript"
});
var skillsCount = await context.RawScalarQueryAsync <long>("SELECT COUNT(*) FROM [Skills] WHERE [Skills].[PersonId] = @personId", new { personId = person.Id });
Assert.Equal(2, skillsCount);
}
public void Insert()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
context.Persons.Insert(person);
Assert.True(person.Id > 0);
}
public void Where()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
context.Persons.Insert(person);
var findPerson = context.Persons.Where("Id = @id", new { id = person.Id });
Assert.NotNull(findPerson);
}
public async Task Find()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
await context.Persons.InsertAsync(person);
var findPerson = await context.Persons.FindAsync(person.Id);
Assert.NotNull(findPerson);
}
public void RawNonQuery()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
context.Persons.Insert(person);
context.RawNonQuery("UPDATE [Persons] SET [Name] = @name WHERE Id = @id", new { name = "mh", id = person.Id });
var findPerson = context.Persons.Find(person.Id);
Assert.Equal("mh", findPerson.Name);
}
public void Update()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
context.Persons.Insert(person);
person.Age = 42;
context.Persons.Update(person);
var changedPerson = context.Persons.Find(person.Id);
Assert.Equal(42, changedPerson.Age);
}
public void Transaction_RollBack()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
using (var transaction = context.BeginTransaction())
{
context.Persons.Insert(person, transaction);
transaction.RollBack();
}
var persons = context.Persons.All();
Assert.Empty(persons);
}
public void Delete()
{
var person = new SQLitePerson
{
Name = "MohammadHasan",
Family = "Farzin",
Age = 32,
Gender = SQLiteGender.Male
};
using var context = ContextFactory.GetSqlLiteContext();
context.DataBaseTruncate();
context.Persons.Insert(person);
var findPerson = context.Persons.Find(person.Id);
Assert.NotNull(person);
context.Persons.Delete(person.Id);
var deletePerson = context.Persons.Find(person.Id);
Assert.Null(deletePerson);
}