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 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 CreateConnectionName()
{
var session = new DataSession("Tracker");
session.Should().NotBeNull();
session.Connection.Should().NotBeNull();
session.Connection.State.Should().Be(ConnectionState.Closed);
}
public void ProcedureExecuteOutDuplicate()
{
Guid?userId = null;
int errorCode = -1;
var username = "******" + DateTime.Now.Ticks;
var email = username + "@email.com";
int result;
using (var session = new DataSession("AspNet").Log(Console.WriteLine))
{
session.Should().NotBeNull();
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", "paul.welter")
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter <string>("@PasswordQuestion", null)
.Parameter <string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.Parameter <Guid?>(parameter => parameter
.Name("@UserId")
.Type(DbType.Guid)
.Output(p => userId = p)
.Direction(ParameterDirection.Output)
)
.Return <int>(p => errorCode = p)
.Execute();
// Duplicate
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", "paul.welter")
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter <string>("@PasswordQuestion", null)
.Parameter <string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.Parameter <Guid?>(parameter => parameter
.Name("@UserId")
.Type(DbType.Guid)
.Output(p => userId = p)
.Direction(ParameterDirection.Output)
)
.Return <int>(p => errorCode = p)
.Execute();
}
result.Should().Be(-1);
errorCode.Should().BeGreaterThan(0);
}
public void ProcedureExecuteOut()
{
Guid userId = Guid.Empty;
int errorCode = -1;
var username = "******" + DateTime.Now.Ticks;
var email = username + "@email.com";
int result;
using (var session = new DataSession("AspNet").Log(Console.WriteLine))
{
session.Should().NotBeNull();
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", username)
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter <string>("@PasswordQuestion", null)
.Parameter <string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.ParameterOut <Guid>("@UserId", p => userId = p)
.Return <int>(p => errorCode = p)
.Execute();
}
result.Should().BeGreaterOrEqualTo(1);
userId.Should().NotBe(Guid.Empty);
errorCode.Should().Be(0);
}
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 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 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 CreateConnection()
{
var sqlConnection = new SqlConnection("Data Source=(local);Initial Catalog=Tracker;Integrated Security=True;");
var session = new DataSession(sqlConnection);
session.Should().NotBeNull();
session.Connection.Should().NotBeNull();
session.Connection.State.Should().Be(ConnectionState.Closed);
}
public void CreateConnectionName()
{
var session = new DataSession("Tracker");
session.Should().NotBeNull();
session.Connection.Should().NotBeNull();
session.Connection.State.Should().Be(ConnectionState.Closed);
}
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 CreateConnection()
{
var sqlConnection = new SqlConnection("Data Source=(local);Initial Catalog=Tracker;Integrated Security=True;");
var session = new DataSession(sqlConnection);
session.Should().NotBeNull();
session.Connection.Should().NotBeNull();
session.Connection.State.Should().Be(ConnectionState.Closed);
}
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 EnsureConnectionByName()
{
var session = new DataSession("Tracker");
session.Should().NotBeNull();
session.Connection.Should().NotBeNull();
session.Connection.State.Should().Be(ConnectionState.Closed);
session.EnsureConnection();
session.Connection.State.Should().Be(ConnectionState.Open);
session.ReleaseConnection();
session.Connection.State.Should().Be(ConnectionState.Closed);
}
public void EnsureConnectionByName()
{
var session = new DataSession("Tracker");
session.Should().NotBeNull();
session.Connection.Should().NotBeNull();
session.Connection.State.Should().Be(ConnectionState.Closed);
session.EnsureConnection();
session.Connection.State.Should().Be(ConnectionState.Open);
session.ReleaseConnection();
session.Connection.State.Should().Be(ConnectionState.Closed);
}
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 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 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 ProcedureQueryDynamicOut()
{
int totalRecords = -1;
int result = 0;
Guid userId = Guid.Empty;
int errorCode = -1;
var username = "******" + DateTime.Now.Ticks;
var email = username + "@email.com";
List <dynamic> results;
using (var session = new DataSession("AspNet").Log(Console.WriteLine))
{
session.Should().NotBeNull();
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", username)
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter <string>("@PasswordQuestion", null)
.Parameter <string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.ParameterOut <Guid>("@UserId", p => userId = p)
.Return <int>(p => errorCode = p)
.Execute();
results = session.StoredProcedure("[dbo].[aspnet_Membership_FindUsersByEmail]")
.Parameter("@ApplicationName", "/")
.Parameter("@EmailToMatch", "*****@*****.**")
.Parameter("@PageIndex", 0)
.Parameter("@PageSize", 10)
.Return <int>(p => totalRecords = p)
.Query()
.ToList();
}
results.Should().NotBeNull();
results.Count.Should().BeGreaterThan(0);
totalRecords.Should().BeGreaterThan(0);
}
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 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 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 ProcedureExecuteOutDuplicate()
{
Guid? userId = null;
int errorCode = -1;
var username = "******" + DateTime.Now.Ticks;
var email = username + "@email.com";
int result;
using (var session = new DataSession("AspNet").Log(Console.WriteLine))
{
session.Should().NotBeNull();
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", "paul.welter")
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter<string>("@PasswordQuestion", null)
.Parameter<string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.Parameter<Guid?>(parameter => parameter
.Name("@UserId")
.Type(DbType.Guid)
.Output(p => userId = p)
.Direction(ParameterDirection.Output)
)
.Return<int>(p => errorCode = p)
.Execute();
// Duplicate
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", "paul.welter")
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter<string>("@PasswordQuestion", null)
.Parameter<string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.Parameter<Guid?>(parameter => parameter
.Name("@UserId")
.Type(DbType.Guid)
.Output(p => userId = p)
.Direction(ParameterDirection.Output)
)
.Return<int>(p => errorCode = p)
.Execute();
}
result.Should().Be(-1);
errorCode.Should().BeGreaterThan(0);
}
public void ProcedureQueryDynamicOut()
{
int totalRecords = -1;
int result = 0;
Guid userId = Guid.Empty;
int errorCode = -1;
var username = "******" + DateTime.Now.Ticks;
var email = username + "@email.com";
List<dynamic> results;
using (var session = new DataSession("AspNet").Log(Console.WriteLine))
{
session.Should().NotBeNull();
result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", username)
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter<string>("@PasswordQuestion", null)
.Parameter<string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.ParameterOut<Guid>("@UserId", p => userId = p)
.Return<int>(p => errorCode = p)
.Execute();
results = session.StoredProcedure("[dbo].[aspnet_Membership_FindUsersByEmail]")
.Parameter("@ApplicationName", "/")
.Parameter("@EmailToMatch", "*****@*****.**")
.Parameter("@PageIndex", 0)
.Parameter("@PageSize", 10)
.Return<int>(p => totalRecords = p)
.Query()
.ToList();
}
results.Should().NotBeNull();
results.Count.Should().BeGreaterThan(0);
totalRecords.Should().BeGreaterThan(0);
}
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 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 ProcedureExecuteTransaction()
{
var session = new DataSession("AspNet").Log(Console.WriteLine);
session.Should().NotBeNull();
var transaction = session.BeginTransaction(IsolationLevel.Unspecified);
transaction.Should().NotBeNull();
Guid userId = Guid.Empty;
int errorCode = -1;
var username = "******" + DateTime.Now.Ticks;
var email = username + "@email.com";
var result = session.StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
.Parameter("@ApplicationName", "/")
.Parameter("@UserName", username)
.Parameter("@Password", "T@est" + DateTime.Now.Ticks)
.Parameter("@Email", email)
.Parameter("@PasswordSalt", "test salt")
.Parameter<string>("@PasswordQuestion", null)
.Parameter<string>("@PasswordAnswer", null)
.Parameter("@IsApproved", true)
.Parameter("@CurrentTimeUtc", DateTime.UtcNow)
.Parameter("@UniqueEmail", 1)
.Parameter("@PasswordFormat", 1)
.ParameterOut<Guid>("@UserId", p => userId = p)
.Return<int>(p => errorCode = p)
.Execute();
result.Should().BeGreaterOrEqualTo(1);
userId.Should().NotBe(Guid.Empty);
errorCode.Should().Be(0);
transaction.Commit();
}
