private DataTable GetPaymentID(string orderID)
 {
     DataTable datTable = new DataTable();
     string sqliteQuery = "SELECT payment_id FROM orders WHERE id = @id";
     SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
     sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
     sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@id", orderID);
     dataAccessObject = new DataAccessLayer();
     datTable = dataAccessObject.Select(sqliteQuery, sqliteDataAdapterSelect);
     return datTable;
 }
 private DataTable GetUser(string email)
 {
     DataTable datTable = new DataTable();
     StringBuilder sqliteQuerySelect = new StringBuilder();
     sqliteQuerySelect.Append("SELECT ");
     sqliteQuerySelect.Append("id, ");
     sqliteQuerySelect.Append("email, ");
     sqliteQuerySelect.Append("encrypted_password, ");
     sqliteQuerySelect.Append("sign_in_count ");
     sqliteQuerySelect.Append("FROM users ");
     sqliteQuerySelect.Append("WHERE email = @email");
     SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
     sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
     sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@email", email);
     dataAccessObject = new DataAccessLayer();
     datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect);
     return datTable;
 }
 private DataTable GetUser(string email)
 {
     DataTable datTable = null;
     StringBuilder selectQuery = new StringBuilder();
     selectQuery.Append("SELECT ");
     selectQuery.Append("id, ");
     selectQuery.Append("email, ");
     selectQuery.Append("encrypted_password, ");
     selectQuery.Append("sign_in_count ");
     selectQuery.Append("FROM users ");
     selectQuery.Append("WHERE email = @email;");
     using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery.ToString()))
     {
         commandSQLite.Parameters.AddWithValue("@email", email);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         datTable = dataAccessObject.Select(commandSQLite);
     }
     return datTable;
 }
 private DataTable GetOrders(int userID)
 {
     DataTable datTable = new DataTable();
     StringBuilder sqliteQuerySelect = new StringBuilder();
     sqliteQuerySelect.Append("SELECT ");
     sqliteQuerySelect.Append("DISTINCT id, ");
     sqliteQuerySelect.Append("user_id, ");
     sqliteQuerySelect.Append("payment_id, ");
     sqliteQuerySelect.Append("state, ");
     sqliteQuerySelect.Append("amount, ");
     sqliteQuerySelect.Append("description, ");
     sqliteQuerySelect.Append("created_at, ");
     sqliteQuerySelect.Append("updated_at ");
     sqliteQuerySelect.Append("FROM orders ");
     sqliteQuerySelect.Append("WHERE user_id = @user_id ORDER BY id DESC LIMIT 1");
     SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
     sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
     sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@user_id", userID);
     dataAccessObject = new DataAccessLayer();
     datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect);
     return datTable;
 }
        private bool CheckIsExistingUser()
        {
            bool isExistingUser = false;
            DataTable datTable = new DataTable();
            int rows = 0;
            var email = TextBoxEmail.Text.Trim();
            StringBuilder sqliteQuerySelect = new StringBuilder();
            sqliteQuerySelect.Append("SELECT ");
            sqliteQuerySelect.Append("count(*) AS NumberOfUsers ");
            sqliteQuerySelect.Append("FROM users ");
            sqliteQuerySelect.Append("WHERE email = @email");
            SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
            sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
            sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@email", email);
            dataAccessObject = new DataAccessLayer();
            datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect);

            if (datTable != null && datTable.Rows.Count > 0)
            {
                var distinctRows = from DataRow dRow in datTable.Rows
                                   select new { column1 = dRow["NumberOfUsers"] };
                if (distinctRows != null)
                {
                    foreach (var row in distinctRows)
                    {
                        rows = Convert.ToInt32(row.column1);
                        break;
                    }
                }
            }
            if (rows == 1)
            {
                isExistingUser = true;
            }
            return isExistingUser;
        }
 private bool Update(string email)
 {
     bool isSuccess = false;
     int rowsAffected = 0;
     int signInCount = 0;
     DataTable datTable = GetUser(email);
     if (datTable != null && datTable.Rows.Count > 0)
     {
         var distinctRows = (from DataRow dRow in datTable.Rows
                            where dRow.Field<string>("email") == email
                             select new { column1 = dRow["sign_in_count"] }).Distinct();
         if (distinctRows != null)
         {
             foreach (var row in distinctRows)
             {
                 signInCount = Convert.ToInt32(row.column1.ToString());
                 signInCount++;
                 break;
             }
         }
         StringBuilder updateQuery = new StringBuilder();
         updateQuery.Append("UPDATE Users ");
         updateQuery.Append("SET ");
         updateQuery.Append("sign_in_count = @sign_in_count ");
         updateQuery.Append("WHERE ");
         updateQuery.Append("email = @email;");
         using (SQLiteCommand commandSQLite = new SQLiteCommand(updateQuery.ToString()))
         {
             commandSQLite.Parameters.AddWithValue("@sign_in_count", signInCount);
             commandSQLite.Parameters.AddWithValue("@email", email);
             DataAccessLayer dataAccessObject = new DataAccessLayer();
             rowsAffected = dataAccessObject.Execute(commandSQLite);
         }
     }
     if (rowsAffected > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
 private bool Insert(int userID, string createdAt, string updatedAt)
 {
     bool isSuccess = false;
     int rowsAffacted = 0;
     StringBuilder sqliteQueryInsert = new StringBuilder();
     sqliteQueryInsert.Append("INSERT INTO orders");
     sqliteQueryInsert.Append("(");
     sqliteQueryInsert.Append("user_id, ");
     sqliteQueryInsert.Append("created_at, ");
     sqliteQueryInsert.Append("updated_at ");
     sqliteQueryInsert.Append(") ");
     sqliteQueryInsert.Append("VALUES ");
     sqliteQueryInsert.Append("(");
     sqliteQueryInsert.Append("@user_id, ");
     sqliteQueryInsert.Append("@created_at, ");
     sqliteQueryInsert.Append("@updated_at ");
     sqliteQueryInsert.Append(")");
     SQLiteDataAdapter sqliteDataAdapterInsert = new SQLiteDataAdapter();
     sqliteDataAdapterInsert.InsertCommand = new SQLiteCommand();
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@user_id", userID);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@created_at", createdAt);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@updated_at", updatedAt);
     dataAccessObject = new DataAccessLayer();
     rowsAffacted = dataAccessObject.Insert(sqliteQueryInsert.ToString(), sqliteDataAdapterInsert);
     if (rowsAffacted > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
示例#8
0
        private bool Update(string email)
        {
            bool isSuccess = false;

            int       rowsAffacted         = 0;
            var       newPassword          = TextBoxNewPassword.Text.Trim();
            var       confirmNewPassword   = TextBoxConfirmNewPassword.Text.Trim();
            var       encryptedNewPassword = Secure.Encrypt(newPassword);
            var       signInCount          = 0;
            var       dateTimeNow          = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
            var       currentSignInAt      = dateTimeNow;
            var       lastSignInAt         = string.Empty;
            var       signInIPAddress      = ConfigManager.Instance.GetProperty("IPAddress");
            var       currentSignInIP      = signInIPAddress;
            var       lastSignInIP         = string.Empty;
            var       createdAt            = string.Empty;
            var       updatedAt            = dateTimeNow;
            DataTable datTable             = GetUser(email);

            if (datTable != null && datTable.Rows.Count > 0)
            {
                var distinctRows = from DataRow dRow in datTable.Rows
                                   where dRow.Field <string>("email") == email
                                   select new { column1 = dRow["sign_in_count"], column2 = dRow["last_sign_in_at"], column3 = dRow["last_sign_in_ip"], column4 = dRow["created_at"] };
                if (distinctRows != null)
                {
                    foreach (var row in distinctRows)
                    {
                        signInCount = Convert.ToInt32(row.column1.ToString());
                        signInCount++;
                        DateTime lastSignInAtDateTime = Convert.ToDateTime(row.column2);
                        lastSignInAt = lastSignInAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
                        lastSignInIP = Convert.ToString(row.column3);
                        DateTime createdAtDateTime = Convert.ToDateTime(row.column4);
                        createdAt = createdAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
                        break;
                    }
                }
                CreditCard    credCard              = CreateCreditCard();
                var           creditCardID          = credCard.id;
                var           creditCardDescription = credCard.number;
                StringBuilder sqliteQueryUpdate     = new StringBuilder();
                sqliteQueryUpdate.Append("UPDATE Users ");
                sqliteQueryUpdate.Append("SET ");
                sqliteQueryUpdate.Append("encrypted_password = @encrypted_password, ");
                sqliteQueryUpdate.Append("sign_in_count = @sign_in_count, ");
                sqliteQueryUpdate.Append("current_sign_in_at = @current_sign_in_at, ");
                sqliteQueryUpdate.Append("last_sign_in_at = @last_sign_in_at, ");
                sqliteQueryUpdate.Append("current_sign_in_ip = @current_sign_in_ip, ");
                sqliteQueryUpdate.Append("last_sign_in_ip = @last_sign_in_ip, ");
                sqliteQueryUpdate.Append("created_at = @created_at, ");
                sqliteQueryUpdate.Append("credit_card_id = @credit_card_id, ");
                sqliteQueryUpdate.Append("credit_card_description = @credit_card_description ");
                sqliteQueryUpdate.Append("WHERE ");
                sqliteQueryUpdate.Append("email = @email");
                SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
                sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@email", email);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@encrypted_password", encryptedNewPassword);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@created_at", createdAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
                dataAccessObject = new DataAccessLayer();
                rowsAffacted     = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
            }
            if (rowsAffacted > 0)
            {
                isSuccess = true;
            }
            return(isSuccess);
        }
 private DataTable GetPaymentId(string orderId)
 {
     DataTable datTable = null;
     string selectQuery = "SELECT payment_id FROM orders WHERE id = @id;";
     using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery))
     {
         commandSQLite.Parameters.AddWithValue("@id", orderId);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         datTable = dataAccessObject.Select(commandSQLite);
     }
     return datTable;
 }
 private DataTable GetOrders(int userId)
 {
     DataTable datTable = null;
     StringBuilder selectQuery = new StringBuilder();
     selectQuery.Append("SELECT ");
     selectQuery.Append("id, ");
     selectQuery.Append("user_id, ");
     selectQuery.Append("payment_id, ");
     selectQuery.Append("state, ");
     selectQuery.Append("amount, ");
     selectQuery.Append("description, ");
     selectQuery.Append("created_at, ");
     selectQuery.Append("updated_at ");
     selectQuery.Append("FROM orders ");
     selectQuery.Append("WHERE user_id = @user_id ");
     selectQuery.Append("ORDER BY updated_at DESC, id DESC;");
     using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery.ToString()))
     {
         commandSQLite.Parameters.AddWithValue("@user_id", userId);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         datTable = dataAccessObject.Select(commandSQLite);
     }
     return datTable;
 }
 private bool Insert(int userId, string createdAt, string updatedAt)
 {
     bool isSuccess = false;
     int rowsAffected = 0;
     StringBuilder insertQuery = new StringBuilder();
     insertQuery.Append("INSERT INTO orders");
     insertQuery.Append("(");
     insertQuery.Append("user_id, ");
     insertQuery.Append("created_at, ");
     insertQuery.Append("updated_at ");
     insertQuery.Append(") ");
     insertQuery.Append("VALUES ");
     insertQuery.Append("(");
     insertQuery.Append("@user_id, ");
     insertQuery.Append("@created_at, ");
     insertQuery.Append("@updated_at ");
     insertQuery.Append(");");
     using (SQLiteCommand commandSQLite = new SQLiteCommand(insertQuery.ToString()))
     {
         commandSQLite.Parameters.AddWithValue("@user_id", userId);
         commandSQLite.Parameters.AddWithValue("@created_at", createdAt);
         commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         rowsAffected = dataAccessObject.Execute(commandSQLite);
     }
     if (rowsAffected > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
 private bool CheckIsExistingUser(string email)
 {
     bool isExistingUser = false;
     DataTable datTable = null;
     int rows = 0;
     StringBuilder selectQuery = new StringBuilder();
     selectQuery.Append("SELECT ");
     selectQuery.Append("count(*) AS NumberOfUsers ");
     selectQuery.Append("FROM users ");
     selectQuery.Append("WHERE email = @email;");
     using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery.ToString()))
     {
         commandSQLite.Parameters.AddWithValue("@email", email);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         datTable = dataAccessObject.Select(commandSQLite);
     }
     if (datTable != null && datTable.Rows.Count > 0)
     {
         var distinctRows = (from DataRow dRow in datTable.Rows
                             select new { column1 = dRow["NumberOfUsers"] }).Distinct();
         if (distinctRows != null)
         {
             foreach (var row in distinctRows)
             {
                 rows = Convert.ToInt32(row.column1);
                 break;
             }
         }
     }
     if (rows == 1)
     {
         isExistingUser = true;
     }
     return isExistingUser;
 }
 private bool Insert(string email, string password, string passwordConfirmation, string creditCardType, string creditCardNumber, string creditCardCVV2, string creditCardExpireMonth, string creditCardExpireYear)
 {
     bool isSuccess = false;
     int rowsAffected = 0;
     var encryptedPassword = Secure.Encrypt(password);
     var signInCount = 1;
     var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
     var currentSignInAt = dateTimeNow;
     var lastSignInAt = currentSignInAt;
     // Set first (current) signed in IP Address
     var signInIPAddress = string.Empty;
     var currentSignInIP = signInIPAddress;
     var lastSignInIP = signInIPAddress;
     var createdAt = dateTimeNow;
     var updatedAt = createdAt;
     CreditCard credCard = CreateCreditCard(creditCardType, creditCardNumber, creditCardCVV2, creditCardExpireMonth, creditCardExpireYear);
     var creditCardId = credCard.id;
     var creditCardDescription = credCard.number;
     StringBuilder insertQuery = new StringBuilder();
     insertQuery.Append("INSERT INTO users");
     insertQuery.Append("(");
     insertQuery.Append("email, ");
     insertQuery.Append("encrypted_password, ");
     insertQuery.Append("sign_in_count, ");
     insertQuery.Append("current_sign_in_at,");
     insertQuery.Append("last_sign_in_at, ");
     insertQuery.Append("current_sign_in_ip, ");
     insertQuery.Append("last_sign_in_ip, ");
     insertQuery.Append("created_at, ");
     insertQuery.Append("updated_at, ");
     insertQuery.Append("credit_card_id, ");
     insertQuery.Append("credit_card_description ");
     insertQuery.Append(") ");
     insertQuery.Append("VALUES ");
     insertQuery.Append("(");
     insertQuery.Append("@email, ");
     insertQuery.Append("@encrypted_password, ");
     insertQuery.Append("@sign_in_count, ");
     insertQuery.Append("@current_sign_in_at,");
     insertQuery.Append("@last_sign_in_at, ");
     insertQuery.Append("@current_sign_in_ip, ");
     insertQuery.Append("@last_sign_in_ip, ");
     insertQuery.Append("@created_at, ");
     insertQuery.Append("@updated_at, ");
     insertQuery.Append("@credit_card_id, ");
     insertQuery.Append("@credit_card_description ");
     insertQuery.Append(");");
     using (SQLiteCommand commandSQLite = new SQLiteCommand(insertQuery.ToString()))
     {
         commandSQLite.Parameters.AddWithValue("@email", email);
         commandSQLite.Parameters.AddWithValue("@encrypted_password", encryptedPassword);
         commandSQLite.Parameters.AddWithValue("@sign_in_count", signInCount);
         commandSQLite.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
         commandSQLite.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
         commandSQLite.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
         commandSQLite.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
         commandSQLite.Parameters.AddWithValue("@created_at", createdAt);
         commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
         commandSQLite.Parameters.AddWithValue("@credit_card_id", creditCardId);
         commandSQLite.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         rowsAffected = dataAccessObject.Execute(commandSQLite);
     }
     if (rowsAffected > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
        private bool Update(string email, string newPassword, string confirmNewPassword, string newCreditCardType, string newCreditCardNumber, string newCreditCardCVV2, string newCreditCardExpireMonth, string newCreditCardExpireYear)
        {
            bool isSuccess = false;

            int rowsAffected = 0;
            var encryptedNewPassword = Secure.Encrypt(newPassword);
            var signInCount = 0;
            var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
            var currentSignInAt = dateTimeNow;

            // Set last signed in IP Address from database
            var lastSignInAt = string.Empty;

            // Set first signed in IP Address from database
            var signInIPAddress = string.Empty;

            // Set current signed in IP Address
            var currentSignInIP = string.Empty;

            // Set last signed in IP Address from database
            var lastSignInIP = string.Empty;

            var createdAt = string.Empty;
            var updatedAt = dateTimeNow;
            DataTable datTable = GetUser(email);
            if (datTable != null && datTable.Rows.Count > 0)
            {
                var distinctRows = (from DataRow dRow in datTable.Rows
                                   where dRow.Field<string>("email") == email
                                   select new { column1 = dRow["sign_in_count"],
                                   column2 = dRow["last_sign_in_at"],
                                   column3 = dRow["last_sign_in_ip"],
                                   column4 = dRow["created_at"]
                                   }).Distinct();
                if (distinctRows != null)
                {
                    foreach (var row in distinctRows)
                    {
                        signInCount = Convert.ToInt32(row.column1.ToString());
                        signInCount++;
                        DateTime lastSignInAtDateTime = Convert.ToDateTime(row.column2);
                        lastSignInAt = lastSignInAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
                        lastSignInIP = Convert.ToString(row.column3);
                        DateTime createdAtDateTime = Convert.ToDateTime(row.column4);
                        createdAt = createdAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
                        break;
                    }
                }
                CreditCard credCard = CreateCreditCard(newCreditCardType, newCreditCardNumber, newCreditCardCVV2, newCreditCardExpireMonth, newCreditCardExpireYear);
                var creditCardId = credCard.id;
                var creditCardDescription = credCard.number;
                StringBuilder updateQuery = new StringBuilder();
                updateQuery.Append("UPDATE Users ");
                updateQuery.Append("SET ");
                updateQuery.Append("encrypted_password = @encrypted_password, ");
                updateQuery.Append("sign_in_count = @sign_in_count, ");
                updateQuery.Append("current_sign_in_at = @current_sign_in_at, ");
                updateQuery.Append("last_sign_in_at = @last_sign_in_at, ");
                updateQuery.Append("current_sign_in_ip = @current_sign_in_ip, ");
                updateQuery.Append("last_sign_in_ip = @last_sign_in_ip, ");
                updateQuery.Append("created_at = @created_at, ");
                updateQuery.Append("credit_card_id = @credit_card_id, ");
                updateQuery.Append("credit_card_description = @credit_card_description ");
                updateQuery.Append("WHERE ");
                updateQuery.Append("email = @email;");
                using (SQLiteCommand commandSQLite = new SQLiteCommand(updateQuery.ToString()))
                {
                    commandSQLite.Parameters.AddWithValue("@email", email);
                    commandSQLite.Parameters.AddWithValue("@encrypted_password", encryptedNewPassword);
                    commandSQLite.Parameters.AddWithValue("@sign_in_count", signInCount);
                    commandSQLite.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
                    commandSQLite.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
                    commandSQLite.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
                    commandSQLite.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
                    commandSQLite.Parameters.AddWithValue("@created_at", createdAt);
                    commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
                    commandSQLite.Parameters.AddWithValue("@credit_card_id", creditCardId);
                    commandSQLite.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
                    DataAccessLayer dataAccessObject = new DataAccessLayer();
                    rowsAffected = dataAccessObject.Execute(commandSQLite);
                }
            }
            if (rowsAffected > 0)
            {
                isSuccess = true;
            }
            return isSuccess;
        }
 private bool Insert()
 {
     bool isSuccess = false;
     int rowsAffacted = 0;
     var email = TextBoxEmail.Text.Trim();
     var password = TextBoxPassword.Text.Trim();
     var passwordConfirmation = TextBoxConfirmPassword.Text.Trim();
     var encryptedPassword = Secure.Encrypt(password);
     var signInCount = 1;
     var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
     var currentSignInAt = dateTimeNow;
     var lastSignInAt = dateTimeNow;
     var signInIPAddress = "";
     var currentSignInIP = signInIPAddress;
     var lastSignInIP = signInIPAddress;
     var createdAt = dateTimeNow;
     var updatedAt = dateTimeNow;
     CreditCard credCard = CreateCreditCard();
     var creditCardID = credCard.id;
     var creditCardDescription = credCard.number;
     StringBuilder sqliteQueryInsert = new StringBuilder();
     sqliteQueryInsert.Append("INSERT INTO users");
     sqliteQueryInsert.Append("(");
     sqliteQueryInsert.Append("email, ");
     sqliteQueryInsert.Append("encrypted_password, ");
     sqliteQueryInsert.Append("sign_in_count, ");
     sqliteQueryInsert.Append("current_sign_in_at,");
     sqliteQueryInsert.Append("last_sign_in_at, ");
     sqliteQueryInsert.Append("current_sign_in_ip, ");
     sqliteQueryInsert.Append("last_sign_in_ip, ");
     sqliteQueryInsert.Append("created_at, ");
     sqliteQueryInsert.Append("updated_at, ");
     sqliteQueryInsert.Append("credit_card_id, ");
     sqliteQueryInsert.Append("credit_card_description ");
     sqliteQueryInsert.Append(") ");
     sqliteQueryInsert.Append("VALUES ");
     sqliteQueryInsert.Append("(");
     sqliteQueryInsert.Append("@email, ");
     sqliteQueryInsert.Append("@encrypted_password, ");
     sqliteQueryInsert.Append("@sign_in_count, ");
     sqliteQueryInsert.Append("@current_sign_in_at,");
     sqliteQueryInsert.Append("@last_sign_in_at, ");
     sqliteQueryInsert.Append("@current_sign_in_ip, ");
     sqliteQueryInsert.Append("@last_sign_in_ip, ");
     sqliteQueryInsert.Append("@created_at, ");
     sqliteQueryInsert.Append("@updated_at, ");
     sqliteQueryInsert.Append("@credit_card_id, ");
     sqliteQueryInsert.Append("@credit_card_description ");
     sqliteQueryInsert.Append(")");
     SQLiteDataAdapter sqliteDataAdapterInsert = new SQLiteDataAdapter();
     sqliteDataAdapterInsert.InsertCommand = new SQLiteCommand();
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@email", email);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@encrypted_password", encryptedPassword);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@created_at", createdAt);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@updated_at", updatedAt);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
     sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
     dataAccessObject = new DataAccessLayer();
     rowsAffacted = dataAccessObject.Insert(sqliteQueryInsert.ToString(), sqliteDataAdapterInsert);
     if (rowsAffacted > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
        private bool Update(string email)
        {
            bool isSuccess = false;

            int rowsAffacted = 0;
            var newPassword = TextBoxNewPassword.Text.Trim();
            var confirmNewPassword = TextBoxConfirmNewPassword.Text.Trim();
            var encryptedNewPassword = Secure.Encrypt(newPassword);
            var signInCount = 0;
            var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
            var currentSignInAt = dateTimeNow;
            var lastSignInAt = string.Empty;
            var signInIPAddress = ConfigManager.Instance.GetProperty("IPAddress");
            var currentSignInIP = signInIPAddress;
            var lastSignInIP = string.Empty;
            var createdAt = string.Empty;
            var updatedAt = dateTimeNow;
            DataTable datTable = GetUser(email);
            if (datTable != null && datTable.Rows.Count > 0)
            {
                var distinctRows = from DataRow dRow in datTable.Rows
                                   where dRow.Field<string>("email") == email
                                   select new { column1 = dRow["sign_in_count"], column2 = dRow["last_sign_in_at"], column3 = dRow["last_sign_in_ip"], column4 = dRow["created_at"] };
                if (distinctRows != null)
                {
                    foreach (var row in distinctRows)
                    {
                        signInCount = Convert.ToInt32(row.column1.ToString());
                        signInCount++;
                        DateTime lastSignInAtDateTime = Convert.ToDateTime(row.column2);
                        lastSignInAt = lastSignInAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
                        lastSignInIP = Convert.ToString(row.column3);
                        DateTime createdAtDateTime = Convert.ToDateTime(row.column4);
                        createdAt = createdAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
                        break;
                    }
                }
                CreditCard credCard = CreateCreditCard();
                var creditCardID = credCard.id;
                var creditCardDescription = credCard.number;
                StringBuilder sqliteQueryUpdate = new StringBuilder();
                sqliteQueryUpdate.Append("UPDATE Users ");
                sqliteQueryUpdate.Append("SET ");
                sqliteQueryUpdate.Append("encrypted_password = @encrypted_password, ");
                sqliteQueryUpdate.Append("sign_in_count = @sign_in_count, ");
                sqliteQueryUpdate.Append("current_sign_in_at = @current_sign_in_at, ");
                sqliteQueryUpdate.Append("last_sign_in_at = @last_sign_in_at, ");
                sqliteQueryUpdate.Append("current_sign_in_ip = @current_sign_in_ip, ");
                sqliteQueryUpdate.Append("last_sign_in_ip = @last_sign_in_ip, ");
                sqliteQueryUpdate.Append("created_at = @created_at, ");
                sqliteQueryUpdate.Append("credit_card_id = @credit_card_id, ");
                sqliteQueryUpdate.Append("credit_card_description = @credit_card_description ");
                sqliteQueryUpdate.Append("WHERE ");
                sqliteQueryUpdate.Append("email = @email");
                SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
                sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@email", email);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@encrypted_password", encryptedNewPassword);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@created_at", createdAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
                sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
                dataAccessObject = new DataAccessLayer();
                rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
            }
            if (rowsAffacted > 0)
            {
                isSuccess = true;
            }
            return isSuccess;
        }
 private bool Update(int orderId, string state, string updatedAt)
 {
     bool isSuccess = false;
     int rowsAffected = 0;
     StringBuilder updateQuery = new StringBuilder();
     updateQuery.Append("UPDATE orders ");
     updateQuery.Append("SET ");
     updateQuery.Append("state = @state, ");
     updateQuery.Append("updated_at = @updated_at ");
     updateQuery.Append("WHERE ");
     updateQuery.Append("id = @id;");
     using (SQLiteCommand commandSQLite = new SQLiteCommand(updateQuery.ToString()))
     {
         commandSQLite.Parameters.AddWithValue("@state", state);
         commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
         commandSQLite.Parameters.AddWithValue("@id", orderId);
         DataAccessLayer dataAccessObject = new DataAccessLayer();
         rowsAffected = dataAccessObject.Execute(commandSQLite);
     }
     if (rowsAffected > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
 private bool Update(int orderID, string state, string updatedAt)
 {
     bool isSuccess = false;
     int rowsAffacted = 0;
     StringBuilder sqliteQueryUpdate = new StringBuilder();
     sqliteQueryUpdate.Append("UPDATE orders ");
     sqliteQueryUpdate.Append("SET ");
     sqliteQueryUpdate.Append("state = @state, ");
     sqliteQueryUpdate.Append("updated_at = @updated_at ");
     sqliteQueryUpdate.Append("WHERE ");
     sqliteQueryUpdate.Append("id = @id");
     SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
     sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
     sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@state", state);
     sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt);
     sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@id", orderID);
     dataAccessObject = new DataAccessLayer();
     rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
     if (rowsAffacted > 0)
     {
         isSuccess = true;
     }
     return isSuccess;
 }
示例#19
0
        private bool Insert()
        {
            bool          isSuccess             = false;
            int           rowsAffacted          = 0;
            var           email                 = TextBoxEmail.Text.Trim();
            var           password              = TextBoxPassword.Text.Trim();
            var           passwordConfirmation  = TextBoxConfirmPassword.Text.Trim();
            var           encryptedPassword     = Secure.Encrypt(password);
            var           signInCount           = 1;
            var           dateTimeNow           = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
            var           currentSignInAt       = dateTimeNow;
            var           lastSignInAt          = dateTimeNow;
            var           signInIPAddress       = ConfigManager.Instance.GetProperty("IPAddress");
            var           currentSignInIP       = signInIPAddress;
            var           lastSignInIP          = signInIPAddress;
            var           createdAt             = dateTimeNow;
            var           updatedAt             = dateTimeNow;
            CreditCard    credCard              = CreateCreditCard();
            var           creditCardID          = credCard.id;
            var           creditCardDescription = credCard.number;
            StringBuilder sqliteQueryInsert     = new StringBuilder();

            sqliteQueryInsert.Append("INSERT INTO users");
            sqliteQueryInsert.Append("(");
            sqliteQueryInsert.Append("email, ");
            sqliteQueryInsert.Append("encrypted_password, ");
            sqliteQueryInsert.Append("sign_in_count, ");
            sqliteQueryInsert.Append("current_sign_in_at,");
            sqliteQueryInsert.Append("last_sign_in_at, ");
            sqliteQueryInsert.Append("current_sign_in_ip, ");
            sqliteQueryInsert.Append("last_sign_in_ip, ");
            sqliteQueryInsert.Append("created_at, ");
            sqliteQueryInsert.Append("updated_at, ");
            sqliteQueryInsert.Append("credit_card_id, ");
            sqliteQueryInsert.Append("credit_card_description ");
            sqliteQueryInsert.Append(") ");
            sqliteQueryInsert.Append("VALUES ");
            sqliteQueryInsert.Append("(");
            sqliteQueryInsert.Append("@email, ");
            sqliteQueryInsert.Append("@encrypted_password, ");
            sqliteQueryInsert.Append("@sign_in_count, ");
            sqliteQueryInsert.Append("@current_sign_in_at,");
            sqliteQueryInsert.Append("@last_sign_in_at, ");
            sqliteQueryInsert.Append("@current_sign_in_ip, ");
            sqliteQueryInsert.Append("@last_sign_in_ip, ");
            sqliteQueryInsert.Append("@created_at, ");
            sqliteQueryInsert.Append("@updated_at, ");
            sqliteQueryInsert.Append("@credit_card_id, ");
            sqliteQueryInsert.Append("@credit_card_description ");
            sqliteQueryInsert.Append(")");
            SQLiteDataAdapter sqliteDataAdapterInsert = new SQLiteDataAdapter();

            sqliteDataAdapterInsert.InsertCommand = new SQLiteCommand();
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@email", email);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@encrypted_password", encryptedPassword);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@created_at", createdAt);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@updated_at", updatedAt);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
            sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
            dataAccessObject = new DataAccessLayer();
            rowsAffacted     = dataAccessObject.Insert(sqliteQueryInsert.ToString(), sqliteDataAdapterInsert);
            if (rowsAffacted > 0)
            {
                isSuccess = true;
            }
            return(isSuccess);
        }