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 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 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 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 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 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(); }