public void SqlQueryEntityDynamicCache()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var policy = new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(5)
};
var users = session
.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.Query()
.ToList();
users.Should().NotBeNull();
users.Should().NotBeEmpty();
var cachedUsers = session
.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.Query()
.ToList();
cachedUsers.Should().NotBeNull();
cachedUsers.Should().NotBeEmpty();
}
public void SqlQuerySingleEntityFactoryCache()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var policy = new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(5)
};
var user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.QuerySingle <User>();
user.Should().NotBeNull();
user.EmailAddress.Should().Be(email);
var cachedUser = session.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.QuerySingle <User>();
cachedUser.Should().NotBeNull();
cachedUser.EmailAddress.Should().Be(email);
}
public void SqlQuerySingleEntity()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QuerySingle(r => new User
{
Id = r.GetInt32("Id"),
EmailAddress = r.GetString("EmailAddress"),
FirstName = r.GetString("FirstName"),
LastName = r.GetString("LastName"),
Avatar = (Byte[])r.GetValue("Avatar"),
CreatedDate = r.GetDateTime("CreatedDate"),
ModifiedDate = r.GetDateTime("ModifiedDate"),
RowVersion = (Byte[])r.GetValue("RowVersion"),
PasswordHash = r.GetString("PasswordHash"),
PasswordSalt = r.GetString("PasswordSalt"),
Comment = r.GetString("Comment"),
IsApproved = r.GetBoolean("IsApproved"),
LastLoginDate = r.GetDateTime("LastLoginDate"),
LastActivityDate = r.GetDateTime("LastActivityDate"),
LastPasswordChangeDate = r.GetDateTime("LastPasswordChangeDate"),
AvatarType = r.GetString("AvatarType"),
});
user.Should().NotBeNull();
user.EmailAddress.Should().Be(email);
}
public void SqlQuerySingleEntityFactoryCache()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var policy = new CacheItemPolicy { SlidingExpiration = TimeSpan.FromMinutes(5) };
var user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.QuerySingle<User>();
user.Should().NotBeNull();
user.EmailAddress.Should().Be(email);
var cachedUser = session.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.QuerySingle<User>();
cachedUser.Should().NotBeNull();
cachedUser.EmailAddress.Should().Be(email);
}
public void SqlReader()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var users = new List <dynamic>();
session.Sql(sql)
.Parameter("@EmailAddress", email)
.Read(reader =>
{
while (reader.Read())
{
var user = DataFactory.DynamicFactory(reader);
users.Add(user);
}
});
users.Should().NotBeNull();
users.Should().NotBeEmpty();
}
public void SqlQueryMultiple()
{
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress; " +
"select * from [Role]; " +
"select * from [Priority]; ";
User user = null;
List <Role> roles = null;
List <Priority> priorities = null;
using (var session = new DataSession("Tracker").Log(Console.WriteLine))
{
session.Should().NotBeNull();
session.Sql(sql)
.Parameter("@EmailAddress", email)
.QueryMultiple(q =>
{
user = q.QuerySingle <User>();
roles = q.Query <Role>().ToList();
priorities = q.Query <Priority>().ToList();
});
}
user.Should().NotBeNull();
user.EmailAddress.Should().NotBeEmpty();
roles.Should().NotBeNull();
roles.Should().NotBeEmpty();
priorities.Should().NotBeNull();
priorities.Should().NotBeEmpty();
}
public void SqlQuerySingleEntity()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QuerySingle(r => new User
{
Id = r.GetInt32("Id"),
EmailAddress = r.GetString("EmailAddress"),
FirstName = r.GetString("FirstName"),
LastName = r.GetString("LastName"),
Avatar = (Byte[])r.GetValue("Avatar"),
CreatedDate = r.GetDateTime("CreatedDate"),
ModifiedDate = r.GetDateTime("ModifiedDate"),
RowVersion = (Byte[])r.GetValue("RowVersion"),
PasswordHash = r.GetString("PasswordHash"),
PasswordSalt = r.GetString("PasswordSalt"),
Comment = r.GetString("Comment"),
IsApproved = r.GetBoolean("IsApproved"),
LastLoginDate = r.GetDateTime("LastLoginDate"),
LastActivityDate = r.GetDateTime("LastActivityDate"),
LastPasswordChangeDate = r.GetDateTime("LastPasswordChangeDate"),
AvatarType = r.GetString("AvatarType"),
});
user.Should().NotBeNull();
user.EmailAddress.Should().Be(email);
}
public void WriteServer()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var users = session.Sql(sql)
.Parameter("@EmailAddress", email)
.Query(r => new User
{
Id = r.GetInt32("Id"),
EmailAddress = r.GetString("EmailAddress"),
FirstName = r.GetString("FirstName"),
LastName = r.GetString("LastName"),
Avatar = (Byte[])r.GetValue("Avatar"),
CreatedDate = r.GetDateTime("CreatedDate"),
ModifiedDate = r.GetDateTime("ModifiedDate"),
RowVersion = (Byte[])r.GetValue("RowVersion"),
PasswordHash = r.GetStringNull("PasswordHash"),
PasswordSalt = r.GetStringNull("PasswordSalt"),
Comment = r.GetStringNull("Comment"),
IsApproved = r.GetBoolean("IsApproved"),
LastLoginDate = r.GetDateTimeNull("LastLoginDate"),
LastActivityDate = r.GetDateTime("LastActivityDate"),
LastPasswordChangeDate = r.GetDateTimeNull("LastPasswordChangeDate"),
AvatarType = r.GetStringNull("AvatarType"),
})
.ToList();
users.Should().NotBeNull();
users.Should().NotBeEmpty();
long ticks = DateTime.Now.Ticks;
foreach (var u in users)
{
u.EmailAddress = u.EmailAddress.Replace("@battlestar", "@u" + ticks);
}
session.BulkCopy("[User]")
.Mapping("EmailAddress", "EmailAddress")
.Mapping("FirstName", "FirstName")
.Mapping("LastName", "LastName")
.Mapping("CreatedDate", "CreatedDate")
.Mapping("ModifiedDate", "ModifiedDate")
.Mapping("PasswordHash", "PasswordHash")
.Mapping("PasswordSalt", "PasswordSalt")
.Mapping("Comment", "Comment")
.Mapping("IsApproved", "IsApproved")
.Mapping("LastLoginDate", "LastLoginDate")
.Mapping("LastActivityDate", "LastActivityDate")
.Mapping("LastPasswordChangeDate", "LastPasswordChangeDate")
.Mapping("AvatarType", "AvatarType")
.WriteToServer(users);
}
public void WriteServer()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var users = session.Sql(sql)
.Parameter("@EmailAddress", email)
.Query(r => new User
{
Id = r.GetInt32("Id"),
EmailAddress = r.GetString("EmailAddress"),
FirstName = r.GetString("FirstName"),
LastName = r.GetString("LastName"),
Avatar = (Byte[])r.GetValue("Avatar"),
CreatedDate = r.GetDateTime("CreatedDate"),
ModifiedDate = r.GetDateTime("ModifiedDate"),
RowVersion = (Byte[])r.GetValue("RowVersion"),
PasswordHash = r.GetStringNull("PasswordHash"),
PasswordSalt = r.GetStringNull("PasswordSalt"),
Comment = r.GetStringNull("Comment"),
IsApproved = r.GetBoolean("IsApproved"),
LastLoginDate = r.GetDateTimeNull("LastLoginDate"),
LastActivityDate = r.GetDateTime("LastActivityDate"),
LastPasswordChangeDate = r.GetDateTimeNull("LastPasswordChangeDate"),
AvatarType = r.GetStringNull("AvatarType"),
})
.ToList();
users.Should().NotBeNull();
users.Should().NotBeEmpty();
long ticks = DateTime.Now.Ticks;
foreach (var u in users)
u.EmailAddress = u.EmailAddress.Replace("@battlestar", "@u" + ticks);
session.BulkCopy("[User]")
.Mapping("EmailAddress", "EmailAddress")
.Mapping("FirstName", "FirstName")
.Mapping("LastName", "LastName")
.Mapping("CreatedDate", "CreatedDate")
.Mapping("ModifiedDate", "ModifiedDate")
.Mapping("PasswordHash", "PasswordHash")
.Mapping("PasswordSalt", "PasswordSalt")
.Mapping("Comment", "Comment")
.Mapping("IsApproved", "IsApproved")
.Mapping("LastLoginDate", "LastLoginDate")
.Mapping("LastActivityDate", "LastActivityDate")
.Mapping("LastPasswordChangeDate", "LastPasswordChangeDate")
.Mapping("AvatarType", "AvatarType")
.WriteToServer(users);
}
public void SqlQueryValue()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select Count(*) from [User] where EmailAddress like @EmailAddress";
var count = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QueryValue <int>();
count.Should().BeGreaterThan(0);
}
public void SqlQueryTable()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var users = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QueryTable();
users.Should().NotBeNull();
}
public void SqlQuerySingleEntityFactory()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QuerySingle<User>();
user.Should().NotBeNull();
user.EmailAddress.Should().Be(email);
}
public void SqlQuerySingleEntityDynamic()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
dynamic user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QuerySingle();
Assert.NotNull(user);
Assert.Equal <string>(user.EmailAddress, email);
}
public void SqlQuerySingleEntityFactory()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QuerySingle <User>();
user.Should().NotBeNull();
user.EmailAddress.Should().Be(email);
}
public void SqlQueryEntityDynamicCache()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var policy = new CacheItemPolicy { SlidingExpiration = TimeSpan.FromMinutes(5) };
var users = session
.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.Query()
.ToList();
users.Should().NotBeNull();
users.Should().NotBeEmpty();
var cachedUsers = session
.Sql(sql)
.Parameter("@EmailAddress", email)
.UseCache(policy)
.Query()
.ToList();
cachedUsers.Should().NotBeNull();
cachedUsers.Should().NotBeEmpty();
}
public void SqlReader()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var users = new List<dynamic>();
session.Sql(sql)
.Parameter("@EmailAddress", email)
.Read(reader =>
{
while (reader.Read())
{
var user = DataFactory.DynamicFactory(reader);
users.Add(user);
}
});
users.Should().NotBeNull();
users.Should().NotBeEmpty();
}
public void SqlQueryMultiple()
{
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress; " +
"select * from [Role]; " +
"select * from [Priority]; ";
User user = null;
List<Role> roles = null;
List<Priority> priorities = null;
using (var session = new DataSession("Tracker").Log(Console.WriteLine))
{
session.Should().NotBeNull();
session.Sql(sql)
.Parameter("@EmailAddress", email)
.QueryMultiple(q =>
{
user = q.QuerySingle<User>();
roles = q.Query<Role>().ToList();
priorities = q.Query<Priority>().ToList();
});
}
user.Should().NotBeNull();
user.EmailAddress.Should().NotBeEmpty();
roles.Should().NotBeNull();
roles.Should().NotBeEmpty();
priorities.Should().NotBeNull();
priorities.Should().NotBeEmpty();
}
public void SqlQuerySingleEntityDynamic()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
dynamic user = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QuerySingle();
Assert.NotNull(user);
Assert.Equal<string>(user.EmailAddress, email);
}
public void SqlQueryTable()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select * from [User] where EmailAddress like @EmailAddress";
var users = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QueryTable();
users.Should().NotBeNull();
}
public void SqlQueryValue()
{
var session = new DataSession("Tracker").Log(Console.WriteLine);
session.Should().NotBeNull();
string email = "*****@*****.**";
string sql = "select Count(*) from [User] where EmailAddress like @EmailAddress";
var count = session.Sql(sql)
.Parameter("@EmailAddress", email)
.QueryValue<int>();
count.Should().BeGreaterThan(0);
}