public void TestBulkLoadPerformance() { using (NuoDbConnection cnn = new NuoDbConnection(connectionString)) { cnn.Open(); Utils.DropTable(cnn, "temp"); DbCommand createCommand = new NuoDbCommand("create table temp (col1 integer, col2 integer)", cnn); int result = createCommand.ExecuteNonQuery(); DbCommand cmm = cnn.CreateCommand(); cmm.CommandText = "insert into temp(col1, col2) values(?, ?)"; cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col1" }); cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col2" }); cmm.Prepare(); const int ROW_NUMBER = 40000; DateTime start = DateTime.Now; for (var i = 1; i <= ROW_NUMBER; i++) { cmm.Parameters["col1"].Value = i; cmm.Parameters["col2"].Value = 2 * i; cmm.ExecuteNonQuery(); } DateTime end = DateTime.Now; double insertTime = (end - start).TotalMilliseconds; Utils.DropTable(cnn, "temp2"); createCommand = new NuoDbCommand("create table temp2 (col1 integer, col2 integer)", cnn); createCommand.ExecuteNonQuery(); NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString); loader.DestinationTableName = "TEMP2"; DbCommand command = new NuoDbCommand("select * from temp", cnn); DbDataReader reader = command.ExecuteReader(); loader.BatchProcessed += new BatchProcessedEventHandler(loader_BatchProcessed); start = DateTime.Now; loader.WriteToServer(reader); end = DateTime.Now; double loadTime = (end - start).TotalMilliseconds; reader.Close(); Console.WriteLine("{0} insert = {1}\n{0} bulk load = {2}\n", ROW_NUMBER, insertTime, loadTime); Assert.IsTrue(loadTime < insertTime, "BulkLoad takes more time than manual insertion"); } }
public void TestAsynchronousScalar1() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand(); countCommand.CommandText = "select count(*) from hockey"; connection.Open(); IAsyncResult result = countCommand.BeginExecuteScalar(); int count = (int)countCommand.EndExecuteScalar(result); } }
// // MembershipProvider.ResetPassword // public override string ResetPassword(string username, string answer) { if (!EnablePasswordReset) { throw new NotSupportedException("Password reset is not enabled."); } if (answer == null && RequiresQuestionAndAnswer) { UpdateFailureCount(username, "passwordAnswer"); throw new ProviderException("Password answer required for password reset."); } string newPassword = System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters); ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true); OnValidatingPassword(args); if (args.Cancel) if (args.FailureInformation != null) throw args.FailureInformation; else throw new MembershipPasswordException("Reset password canceled due to password validation failure."); EnsureTableIsCreated(); NuoDbConnection conn = null; int rowsAffected = 0; string passwordAnswer = ""; DbDataReader reader = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT PasswordAnswer, IsLockedOut " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { if (reader.GetBoolean(1)) throw new MembershipPasswordException("The supplied user is locked out."); passwordAnswer = reader.GetString(0); } else { throw new MembershipPasswordException("The supplied user name is not found."); } if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer)) { UpdateFailureCount(username, "passwordAnswer"); throw new MembershipPasswordException("Incorrect password answer."); } using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET Password = ?, LastPasswordChangedDate = ? " + "WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False"; IDbDataParameter passwordParameter = updateCmd.CreateParameter(); passwordParameter.Value = EncodePassword(newPassword); passwordParameter.DbType = DbType.String; updateCmd.Parameters.Add(passwordParameter); IDbDataParameter lastPasswordChangedDateParameter = updateCmd.CreateParameter(); lastPasswordChangedDateParameter.Value = DateTime.Now; lastPasswordChangedDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastPasswordChangedDateParameter); updateCmd.Parameters.Add(usernameParameter); updateCmd.Parameters.Add(applicationNameParameter); rowsAffected = updateCmd.ExecuteNonQuery(); } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ResetPassword"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } if (rowsAffected > 0) { return newPassword; } else { throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset."); } }
public void TestUpdateWithGeneratedKeys() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { connection.Open(); DbTransaction transaction = connection.BeginTransaction(); DbCommand updateCommand = connection.CreateCommand(); updateCommand.CommandText = "update hockey set number = 99 where team = 'Bruins'"; DbDataReader reader = updateCommand.ExecuteReader(); Assert.IsNotNull(reader, "The command should return a generated keys recordset"); Assert.IsFalse(reader.Read(), "The generated keys recordset should be empty"); transaction.Rollback(); } }
public void TestScalability() { using (NuoDbConnection cnn = new NuoDbConnection(connectionString)) { cnn.Open(); Utils.DropTable(cnn, "temp"); DbCommand createCommand = new NuoDbCommand("create table temp (col1 integer, col2 integer)", cnn); int result = createCommand.ExecuteNonQuery(); DbCommand cmm = cnn.CreateCommand(); cmm.CommandText = "insert into temp(col1, col2) values(?, ?)"; cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col1" }); cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col2" }); cmm.Prepare(); int[] count = new int[] { 1000, 5000, 10000, 20000, 40000 }; double[] times = new double[count.Length]; for (var k = 0; k < count.Length; k++) { DateTime start = DateTime.Now; for (var i = 1; i <= count[k]; i++) { cmm.Parameters["col1"].Value = i; cmm.Parameters["col2"].Value = 2 * i; cmm.ExecuteNonQuery(); } DateTime end = DateTime.Now; times[k] = (end - start).TotalMilliseconds; if (k == 0) Console.WriteLine("{0} runs = {1} msec", count[k], times[k]); else { double countRatio = (count[k] / count[0]); double timeRatio = (times[k] / times[0]); Console.WriteLine("{0} runs = {1} msec => {2} {3}", count[k], times[k], countRatio, timeRatio); Assert.IsTrue(timeRatio < (countRatio * 1.50), "Scalability at {2} rows is not linear! (time for {0} rows = {1}; time for {2} rows = {3} => ratio = {4} is greater than {5}", new object[] { count[0], times[0], count[k], times[k], timeRatio, countRatio }); } } } }
public void TestAsynchronousUpdate2() { NuoDbConnection connection = new NuoDbConnection(connectionString); connection.Open(); Utils.DropTable(connection, "temp"); NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand(); countCommand.CommandText = "create table temp (col string)"; AsyncCallback callback = new AsyncCallback(HandleCallback3); IAsyncResult result = countCommand.BeginExecuteNonQuery(callback, countCommand); }
public void TestPrepareDDLNoParameter() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { DbCommand command = connection.CreateCommand(); connection.Open(); command.CommandText = "create table xyz (col int)"; command.Prepare(); try { int value = command.ExecuteNonQuery(); } catch (Exception e) { Assert.Fail("Executing a prepared DDL that doesn't use parameters reports an error: {0}", e.Message); } finally { Utils.DropTable(connection, "xyz"); } } }
public void TestNamedParameters3() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { DbCommand command = connection.CreateCommand(); connection.Open(); command.CommandText = "select name as \"'?\" from \"hockey\" where name='? ?.fake' or number = ?.number and team = ?.team"; NuoDbParameter p1 = new NuoDbParameter(); p1.ParameterName = "TEAM"; p1.Value = "Bruins"; command.Parameters.Add(p1); NuoDbParameter p2 = new NuoDbParameter(); p2.ParameterName = "NUMBER"; p2.Value = 1; command.Parameters.Add(p2); command.Prepare(); DbDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}", reader["'?"]); } reader.Close(); } }
// // MembershipProvider.ChangePasswordQuestionAndAnswer // public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPwdQuestion, string newPwdAnswer) { if (!ValidateUser(username, password)) return false; int rowsAffected = 0; EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET PasswordQuestion = ?, PasswordAnswer = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter questionParameter = cmd.CreateParameter(); questionParameter.Value = newPwdQuestion; questionParameter.DbType = DbType.String; cmd.Parameters.Add(questionParameter); IDbDataParameter passwordParameter = cmd.CreateParameter(); passwordParameter.Value = EncodePassword(newPwdAnswer); passwordParameter.DbType = DbType.String; cmd.Parameters.Add(passwordParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ChangePasswordQuestionAndAnswer"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) { return true; } return false; }
// // UpdateFailureCount // A helper method that performs the checks and updates associated with // password failure tracking. // private void UpdateFailureCount(string username, string failureType) { EnsureTableIsCreated(); NuoDbConnection conn = null; DbDataReader reader = null; DateTime windowStart = new DateTime(); int failureCount = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT FailedPasswordAttemptCount, " + " FailedPasswordAttemptWindowStart, " + " FailedPasswordAnswerAttemptCount, " + " FailedPasswordAnswerAttemptWindowStart " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.HasRows) { reader.Read(); if (failureType == "password") { failureCount = reader.GetInt32(0); windowStart = reader.GetDateTime(1); } if (failureType == "passwordAnswer") { failureCount = reader.GetInt32(2); windowStart = reader.GetDateTime(3); } } reader.Close(); DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow); if (failureCount == 0 || DateTime.Now > windowEnd) { // First password failure or outside of PasswordAttemptWindow. // Start a new password failure count from 1 and a new window starting now. using (DbCommand updateCmd = conn.CreateCommand()) { if (failureType == "password") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAttemptCount = ?, " + " FailedPasswordAttemptWindowStart = ? " + "WHERE Username = ? AND ApplicationName = ?"; if (failureType == "passwordAnswer") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAnswerAttemptCount = ?, " + " FailedPasswordAnswerAttemptWindowStart = ? " + "WHERE Username = ? AND ApplicationName = ?"; updateCmd.Parameters.Clear(); IDbDataParameter countParameter = updateCmd.CreateParameter(); countParameter.Value = 1; countParameter.DbType = DbType.Int32; updateCmd.Parameters.Add(countParameter); IDbDataParameter windowStartParameter = updateCmd.CreateParameter(); windowStartParameter.Value = DateTime.Now; windowStartParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(windowStartParameter); IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter(); updateUsernameParameter.Value = username; updateUsernameParameter.DbType = DbType.String; updateCmd.Parameters.Add(updateUsernameParameter); updateCmd.Parameters.Add(applicationNameParameter); if (cmd.ExecuteNonQuery() < 0) throw new ProviderException("Unable to update failure count and window start."); } } else { if (failureCount++ >= MaxInvalidPasswordAttempts) { // Password attempts have exceeded the failure threshold. Lock out // the user. using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET IsLockedOut = ?, LastLockedOutDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; updateCmd.Parameters.Clear(); IDbDataParameter isLockedOutParameter = updateCmd.CreateParameter(); isLockedOutParameter.Value = true; isLockedOutParameter.DbType = DbType.Boolean; updateCmd.Parameters.Add(isLockedOutParameter); IDbDataParameter lastLockedOutDateParameter = updateCmd.CreateParameter(); lastLockedOutDateParameter.Value = DateTime.Now; lastLockedOutDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastLockedOutDateParameter); IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter(); updateUsernameParameter.Value = username; updateUsernameParameter.DbType = DbType.String; updateCmd.Parameters.Add(updateUsernameParameter); updateCmd.Parameters.Add(applicationNameParameter); if (cmd.ExecuteNonQuery() < 0) throw new ProviderException("Unable to lock out user."); } } else { // Password attempts have not exceeded the failure threshold. Update // the failure counts. Leave the window the same. using (DbCommand updateCmd = conn.CreateCommand()) { if (failureType == "password") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAttemptCount = ? " + "WHERE Username = ? AND ApplicationName = ?"; if (failureType == "passwordAnswer") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAnswerAttemptCount = ? " + "WHERE Username = ? AND ApplicationName = ?"; updateCmd.Parameters.Clear(); IDbDataParameter countParameter = updateCmd.CreateParameter(); countParameter.Value = failureCount; countParameter.DbType = DbType.Int32; updateCmd.Parameters.Add(countParameter); IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter(); updateUsernameParameter.Value = username; updateUsernameParameter.DbType = DbType.String; updateCmd.Parameters.Add(updateUsernameParameter); updateCmd.Parameters.Add(applicationNameParameter); if (cmd.ExecuteNonQuery() < 0) throw new ProviderException("Unable to update failure count."); } } } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "UpdateFailureCount"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } }
// // EnsureTableIsCreated // A helper function that creates the table if it is not yet present // private void EnsureTableIsCreated() { NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE IF NOT EXISTS Users " + " (PKID String NOT NULL PRIMARY KEY, "+ " Username Text (255) NOT NULL, " + " ApplicationName Text (255) NOT NULL, " + " Email Text (128) NOT NULL, " + " Comment Text (255), " + " Password Text (128) NOT NULL, " + " PasswordQuestion Text (255), " + " PasswordAnswer Text (255), " + " IsApproved Boolean, " + " LastActivityDate DateTime, " + " LastLoginDate DateTime, " + " LastPasswordChangedDate DateTime, " + " CreationDate DateTime, " + " IsOnLine Boolean, " + " IsLockedOut Boolean, " + " LastLockedOutDate DateTime, " + " FailedPasswordAttemptCount Integer, " + " FailedPasswordAttemptWindowStart DateTime, " + " FailedPasswordAnswerAttemptCount Integer, " + " FailedPasswordAnswerAttemptWindowStart DateTime " + ")"; cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "EnsureTableIsCreated"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } }
// // MembershipProvider.ValidateUser // public override bool ValidateUser(string username, string password) { bool isValid = false; EnsureTableIsCreated(); NuoDbConnection conn = null; DbDataReader reader = null; bool isApproved = false; string pwd = ""; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Password, IsApproved " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { pwd = reader.GetString(0); isApproved = reader.GetBoolean(1); } else { return false; } reader.Close(); if (CheckPassword(password, pwd)) { if (isApproved) { isValid = true; using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET LastLoginDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter lastLoginDateParameter = updateCmd.CreateParameter(); lastLoginDateParameter.Value = DateTime.Now; lastLoginDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastLoginDateParameter); updateCmd.Parameters.Add(usernameParameter); updateCmd.Parameters.Add(applicationNameParameter); updateCmd.ExecuteNonQuery(); } } else { conn.Close(); UpdateFailureCount(username, "password"); } } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ValidateUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return isValid; }
// // MembershipProvider.UpdateUser // public override void UpdateUser(MembershipUser user) { EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET Email = ?, Comment = ?, IsApproved = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter emailParameter = cmd.CreateParameter(); emailParameter.Value = user.Email; emailParameter.DbType = DbType.String; cmd.Parameters.Add(emailParameter); IDbDataParameter commentParameter = cmd.CreateParameter(); commentParameter.Value = user.Comment; commentParameter.DbType = DbType.String; cmd.Parameters.Add(commentParameter); IDbDataParameter isApprovedParameter = cmd.CreateParameter(); isApprovedParameter.Value = user.IsApproved; isApprovedParameter.DbType = DbType.Boolean; cmd.Parameters.Add(isApprovedParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = user.UserName; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "UpdateUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } }
// // System.Web.Security.MembershipProvider methods. // // // MembershipProvider.ChangePassword // public override bool ChangePassword(string username, string oldPwd, string newPwd) { if (!ValidateUser(username, oldPwd)) return false; ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPwd, true); OnValidatingPassword(args); if (args.Cancel) if (args.FailureInformation != null) throw args.FailureInformation; else throw new MembershipPasswordException("Change password canceled due to new password validation failure."); int rowsAffected = 0; EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET Password = ?, LastPasswordChangedDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter passwordParameter = cmd.CreateParameter(); passwordParameter.Value = EncodePassword(newPwd); passwordParameter.DbType = DbType.String; cmd.Parameters.Add(passwordParameter); IDbDataParameter lastPasswordChangedDataParameter = cmd.CreateParameter(); lastPasswordChangedDataParameter.Value = DateTime.Now; lastPasswordChangedDataParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastPasswordChangedDataParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ChangePassword"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) { return true; } return false; }
// // MembershipProvider.UnlockUser // public override bool UnlockUser(string username) { EnsureTableIsCreated(); NuoDbConnection conn = null; int rowsAffected = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET IsLockedOut = False, LastLockedOutDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter lastLockedOutDateParameter = cmd.CreateParameter(); lastLockedOutDateParameter.Value = DateTime.Now; lastLockedOutDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastLockedOutDateParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "UnlockUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) return true; return false; }
public void TestInsertWithGeneratedKeys() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { connection.Open(); DbTransaction transaction = connection.BeginTransaction(); DbCommand maxIdCmd = connection.CreateCommand(); maxIdCmd.CommandText = "select max(id) from hockey"; int maxId = (int)maxIdCmd.ExecuteScalar(); DbCommand updateCommand = connection.CreateCommand(); updateCommand.CommandText = "insert into hockey (number, name) values (99, 'xxxx')"; DbDataReader reader = updateCommand.ExecuteReader(); Assert.IsNotNull(reader, "The command should return a generated keys recordset"); Assert.IsTrue(reader.Read(), "There must be at least one ID in the generated keys recordset"); int lastId = (int)reader.GetValue(0); Assert.IsTrue(lastId > maxId, "The generated ID must be greater than the existing ones"); transaction.Rollback(); } }
public void TestNamedParameters2() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { DbCommand command = connection.CreateCommand(); connection.Open(); command.CommandText = "select * from hockey where number = ?.number and team = ?.team"; NuoDbParameter p1 = new NuoDbParameter(); p1.ParameterName = "team"; p1.Value = "Bruins"; command.Parameters.Add(p1); NuoDbParameter p2 = new NuoDbParameter(); p2.ParameterName = "number"; p2.Value = 1; command.Parameters.Add(p2); command.Prepare(); DbDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["id"]); } reader.Close(); } }
// // MembershipProvider.CreateUser // public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status) { ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true); OnValidatingPassword(args); if (args.Cancel) { status = MembershipCreateStatus.InvalidPassword; return null; } if (RequiresUniqueEmail && GetUserNameByEmail(email) != "") { status = MembershipCreateStatus.DuplicateEmail; return null; } MembershipUser u = GetUser(username, false); if (u == null) { DateTime createDate = DateTime.Now; if (providerUserKey == null) { providerUserKey = Guid.NewGuid(); } else { if (!(providerUserKey is Guid)) { status = MembershipCreateStatus.InvalidProviderUserKey; return null; } } EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO Users " + " (PKID, Username, Password, Email, PasswordQuestion, " + " PasswordAnswer, IsApproved," + " Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," + " ApplicationName, IsLockedOut, LastLockedOutDate," + " FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " + " FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; IDbDataParameter pkidParameter = cmd.CreateParameter(); pkidParameter.Value = providerUserKey; pkidParameter.DbType = DbType.String; cmd.Parameters.Add(pkidParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter passwordParameter = cmd.CreateParameter(); passwordParameter.Value = EncodePassword(password); passwordParameter.DbType = DbType.String; cmd.Parameters.Add(passwordParameter); IDbDataParameter emailParameter = cmd.CreateParameter(); emailParameter.Value = email; emailParameter.DbType = DbType.String; cmd.Parameters.Add(emailParameter); IDbDataParameter passwordQuestionParameter = cmd.CreateParameter(); passwordQuestionParameter.Value = passwordQuestion; passwordQuestionParameter.DbType = DbType.String; cmd.Parameters.Add(passwordQuestionParameter); IDbDataParameter passwordAnswerParameter = cmd.CreateParameter(); passwordAnswerParameter.Value = EncodePassword(passwordAnswer); passwordAnswerParameter.DbType = DbType.String; cmd.Parameters.Add(passwordAnswerParameter); IDbDataParameter isApprovedParameter = cmd.CreateParameter(); isApprovedParameter.Value = isApproved; isApprovedParameter.DbType = DbType.Boolean; cmd.Parameters.Add(isApprovedParameter); IDbDataParameter commentParameter = cmd.CreateParameter(); commentParameter.Value = ""; commentParameter.DbType = DbType.String; cmd.Parameters.Add(commentParameter); IDbDataParameter creationDateParameter = cmd.CreateParameter(); creationDateParameter.Value = createDate; creationDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(creationDateParameter); IDbDataParameter lastPasswordChangedDateParameter = cmd.CreateParameter(); lastPasswordChangedDateParameter.Value = createDate; lastPasswordChangedDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastPasswordChangedDateParameter); IDbDataParameter lastActivityDateParameter = cmd.CreateParameter(); lastActivityDateParameter.Value = createDate; lastActivityDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastActivityDateParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); IDbDataParameter isLockedOutParameter = cmd.CreateParameter(); isLockedOutParameter.Value = false; isLockedOutParameter.DbType = DbType.Boolean; cmd.Parameters.Add(isLockedOutParameter); IDbDataParameter lastLockedOutDateParameter = cmd.CreateParameter(); lastLockedOutDateParameter.Value = createDate; lastLockedOutDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastLockedOutDateParameter); IDbDataParameter failedPasswordAttemptCountParameter = cmd.CreateParameter(); failedPasswordAttemptCountParameter.Value = 0; failedPasswordAttemptCountParameter.DbType = DbType.Int32; cmd.Parameters.Add(failedPasswordAttemptCountParameter); IDbDataParameter failedPasswordAttemptWindowStartParameter = cmd.CreateParameter(); failedPasswordAttemptWindowStartParameter.Value = createDate; failedPasswordAttemptWindowStartParameter.DbType = DbType.DateTime; cmd.Parameters.Add(failedPasswordAttemptWindowStartParameter); IDbDataParameter failedPasswordAnswerAttemptCountParameter = cmd.CreateParameter(); failedPasswordAnswerAttemptCountParameter.Value = 0; failedPasswordAnswerAttemptCountParameter.DbType = DbType.Int32; cmd.Parameters.Add(failedPasswordAnswerAttemptCountParameter); IDbDataParameter failedPasswordAnswerAttemptWindowStartParameter = cmd.CreateParameter(); failedPasswordAnswerAttemptWindowStartParameter.Value = createDate; failedPasswordAnswerAttemptWindowStartParameter.DbType = DbType.DateTime; cmd.Parameters.Add(failedPasswordAnswerAttemptWindowStartParameter); int recAdded = cmd.ExecuteNonQuery(); if (recAdded > 0) { status = MembershipCreateStatus.Success; } else { status = MembershipCreateStatus.UserRejected; } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "CreateUser"); } status = MembershipCreateStatus.ProviderError; } finally { conn.Close(); } return GetUser(username, false); } else { status = MembershipCreateStatus.DuplicateUserName; } return null; }
public void TestNamedParameters5() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { DbCommand command = connection.CreateCommand(); connection.Open(); command.CommandText = "select * from hockey where number = @number and team = @team"; command.Prepare(); command.Parameters["NumbER"].Value = 1; command.Parameters["TEam"].Value = "Bruins"; DbDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["ID"]); } reader.Close(); } }
// // MembershipProvider.DeleteUser // public override bool DeleteUser(string username, bool deleteAllRelatedData) { EnsureTableIsCreated(); NuoDbConnection conn = null; int rowsAffected = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "DELETE " + "FROM Users " + "WHERE Username = ? AND Applicationname = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); if (deleteAllRelatedData) { // Process commands to delete all data for the user in the database. } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "DeleteUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) return true; return false; }
public void TestPreparedInsertWithGeneratedKeys2() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { connection.Open(); DbTransaction transaction = connection.BeginTransaction(); DbCommand maxIdCmd = connection.CreateCommand(); maxIdCmd.CommandText = "select max(id) from hockey"; int maxId = (int)maxIdCmd.ExecuteScalar(); DbCommand updateCommand = connection.CreateCommand(); updateCommand.CommandText = "insert into hockey (number, name) values (?, ?)"; updateCommand.Prepare(); updateCommand.Parameters[0].Value = 99; updateCommand.Parameters[1].Value = "xxx"; DbDataReader reader = updateCommand.ExecuteReader(); Assert.IsNotNull(reader, "The command should return a generated keys recordset"); Assert.IsTrue(reader.Read(), "There must be at least one ID in the generated keys recordset"); int lastId = (int)reader.GetValue(0); Assert.IsTrue(lastId > maxId, "The generated ID must be greater than the existing ones"); DbCommand selectCommand = connection.CreateCommand(); selectCommand.CommandText = "select name from hockey where id = ?"; selectCommand.Parameters.Add(lastId); string value = (string)selectCommand.ExecuteScalar(); Assert.AreEqual("xxx", value); transaction.Rollback(); } }
// // MembershipProvider.GetAllUsers // public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords) { EnsureTableIsCreated(); NuoDbConnection conn = null; MembershipUserCollection users = new MembershipUserCollection(); DbDataReader reader = null; totalRecords = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Count(*) " + "FROM Users " + "WHERE ApplicationName = ?"; IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); totalRecords = (int)cmd.ExecuteScalar(); if (totalRecords <= 0) { return users; } cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + "FROM Users " + "WHERE ApplicationName = ? " + "ORDER BY Username Asc"; reader = cmd.ExecuteReader(); int counter = 0; int startIndex = pageSize * pageIndex; int endIndex = startIndex + pageSize - 1; while (reader.Read()) { if (counter >= startIndex) { MembershipUser u = GetUserFromReader(reader); users.Add(u); } if (counter >= endIndex) { break; } counter++; } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetAllUsers "); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return users; }
public void TestPrepareNoParameter() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { DbCommand command = connection.CreateCommand(); connection.Open(); command.CommandText = "select * from hockey where id = 2"; command.Prepare(); DbDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["id"]); } reader.Close(); } }
// // MembershipProvider.GetNumberOfUsersOnline // public override int GetNumberOfUsersOnline() { TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0); DateTime compareTime = DateTime.Now.Subtract(onlineSpan); EnsureTableIsCreated(); NuoDbConnection conn = null; int numOnline = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Count(*) " + "FROM Users " + "WHERE LastActivityDate > ? AND ApplicationName = ?"; IDbDataParameter compareDateParameter = cmd.CreateParameter(); compareDateParameter.Value = compareTime; compareDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(compareDateParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); numOnline = (int)cmd.ExecuteScalar(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetNumberOfUsersOnline"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } return numOnline; }
public void TestTransactions() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { connection.Open(); DbTransaction transaction = connection.BeginTransaction(); DbCommand countCommand = connection.CreateCommand(); countCommand.CommandText = "select count(*) from hockey"; DbCommand updateCommand = connection.CreateCommand(); updateCommand.CommandText = "insert into hockey (number, name) values (99, 'xxxx')"; int count1 = (int)countCommand.ExecuteScalar(); updateCommand.ExecuteNonQuery(); int count2 = (int)countCommand.ExecuteScalar(); Assert.AreEqual(count2, count1 + 1); transaction.Rollback(); int count3 = (int)countCommand.ExecuteScalar(); Assert.AreEqual(count3, count1); } }
// // MembershipProvider.GetPassword // public override string GetPassword(string username, string answer) { if (!EnablePasswordRetrieval) { throw new ProviderException("Password Retrieval Not Enabled."); } if (PasswordFormat == MembershipPasswordFormat.Hashed) { throw new ProviderException("Cannot retrieve Hashed passwords."); } EnsureTableIsCreated(); NuoDbConnection conn = null; string password = ""; string passwordAnswer = ""; DbDataReader reader = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Password, PasswordAnswer, IsLockedOut " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { if (reader.GetBoolean(2)) throw new MembershipPasswordException("The supplied user is locked out."); password = reader.GetString(0); passwordAnswer = reader.GetString(1); } else { throw new MembershipPasswordException("The supplied user name is not found."); } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetPassword"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer)) { UpdateFailureCount(username, "passwordAnswer"); throw new MembershipPasswordException("Incorrect password answer."); } if (PasswordFormat == MembershipPasswordFormat.Encrypted) { password = UnEncodePassword(password); } return password; }
public void TestCommand2() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { DbCommand command = connection.CreateCommand(); command.CommandText = "select * from hockey"; connection.Open(); DbDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (i > 0) Console.Out.Write(", "); Console.Out.Write(reader[i]); } Console.WriteLine(); } reader.Close(); } }
// // MembershipProvider.GetUser(object, bool) // public override MembershipUser GetUser(object providerUserKey, bool userIsOnline) { EnsureTableIsCreated(); NuoDbConnection conn = null; MembershipUser u = null; DbDataReader reader = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + "FROM Users " + "WHERE PKID = ?"; IDbDataParameter pkidParameter = cmd.CreateParameter(); pkidParameter.Value = providerUserKey; pkidParameter.DbType = DbType.String; cmd.Parameters.Add(pkidParameter); reader = cmd.ExecuteReader(); if (reader.Read()) { u = GetUserFromReader(reader); if (userIsOnline) { using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET LastActivityDate = ? " + "WHERE PKID = ?"; IDbDataParameter lastActivityDateParameter = updateCmd.CreateParameter(); lastActivityDateParameter.Value = DateTime.Now; lastActivityDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastActivityDateParameter); updateCmd.Parameters.Add(pkidParameter); updateCmd.ExecuteNonQuery(); } } } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetUser(Object, Boolean)"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return u; }
public void TestAsynchronousScalar3() { NuoDbConnection connection = new NuoDbConnection(connectionString); NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand(); countCommand.CommandText = "select count(*) from hockey"; connection.Open(); AsyncCallback callback = new AsyncCallback(HandleCallback2); for(int i=0;i<20;i++) countCommand.BeginExecuteScalar(callback, countCommand); }
// // MembershipProvider.GetUserNameByEmail // public override string GetUserNameByEmail(string email) { EnsureTableIsCreated(); NuoDbConnection conn = null; string username = ""; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Username " + "FROM Users " + "WHERE Email = ? AND ApplicationName = ?"; IDbDataParameter emailParameter = cmd.CreateParameter(); emailParameter.Value = email; emailParameter.DbType = DbType.String; cmd.Parameters.Add(emailParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); username = (string)cmd.ExecuteScalar(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetUserNameByEmail"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (username == null) username = ""; return username; }