C# DBConnection.Execute方法代码示例

        public static void SetSession(Hashtable accountRow)
        {
            var account = Account.Load(accountRow);

            if (account == null)
                throw new Exception("Unable to log into null account.");

            account.SessionKey = System.Web.HttpContext.Current.Session.SessionID;
            System.Web.HttpContext.Current.Session["Account"] = account;

            var agent = System.Web.HttpContext.Current.Request.UserAgent;
            if (agent.Length > 250)
                agent = agent.Substring(0, 250);

            using (var db = new DBConnection())
            {
                db.Execute("update account set last_on = {0}, num_logins = num_logins + 1 where id = {1}", Utility.UnixTimestamp(DateTime.UtcNow), account.Id);
                //if (!account.IsAdmin)
                    db.Execute("insert ignore into account_login (account_id, datetime, ipaddress, browser, adminused) values ({0}, {1}, '{2}', '{3}', 'False')", account.Id, Utility.UnixTimestamp(DateTime.UtcNow), DBConnection.AddSlashes(System.Web.HttpContext.Current.Request.UserHostAddress), DBConnection.AddSlashes(agent));

                foreach (var message in db.EvaluateTable("select distinct(from_id), account.Name from message join account on account.Id = from_id where to_id = {0} and readflag = 'False'", account.Id))
                {
                    AddChatWindow((int)message["from_id"], (string)message["Name"]);
                }

                db.Execute("update message set readflag = 'True' where to_id = {0}", account.Id);
            }

            var existingAccount = GameServer.GetOnlineAccount(account.Id);
            if (existingAccount == null)
            {
                if (account != null)
                {
                    lock (GameServer.OnlineAccounts)
                    {
                        GameServer.OnlineAccounts.Add(account);
                    }
                }
                else
                    throw new Exception("Account record was null.  This should never happen.");
            }
            else
                existingAccount.SessionKey = System.Web.HttpContext.Current.Session.SessionID;
        }

        public static string SendMessage(DBConnection db, int destinationId, int sourceId, string sourceName, string text)
        {
            //if (sourceId > 1 && !CanSend(db, sourceId))
            //    return "Unable to send message due to spam guard.  Everyone is only allowed to message ten different places within a single hour.";

            db.Execute
            (
                "insert into message (to_id, from_id, time, text) values ({0}, {1}, {2}, '{3}')",
                destinationId,
                sourceId,
                Utility.UnixTimestamp(DateTime.UtcNow),
                DBConnection.AddSlashes(text)
            );

            if (destinationId > 0)
            {
                var account = GameServer.GetOnlineAccount(destinationId);
                if (account != null)
                {
                    GameHub.SendMessage(account.SessionKey, sourceId, sourceName, text);
                }
                else
                {
                    // send email?
                    var destAccount = db.EvaluateRow("select name, email, forward_emails from account where id = " + destinationId);
                    if ((string)destAccount["forward_emails"] == "All")
                        GameServer.SendEmail((string)destAccount["email"], (string)destAccount["name"], "Message from " + sourceName, sourceName + " wrote:\n" + text);
                }
            }

            return "Message Sent";
        }

        string ResetPassword(string email)
        {
            if (String.IsNullOrWhiteSpace(email))
                return "Invalid login name or email address.";

            using (var db = new DBConnection())
            {
                var accountRow = db.EvaluateRow("select * from account where name = '{0}' or email = '{0}'", DBConnection.AddSlashes(email));

                if (accountRow == null)
                    return "Account not found.";

                var newPassword = HtmlUtils.GeneratePassword(8);
                db.Execute("update account set password = '{0}' where id = {1}", DBConnection.AddSlashes(LT.HtmlUtils.CalculateHash(newPassword)), (int)accountRow["id"]);

                GameServer.SendEmail((string)accountRow["email"], GameServer.FromAddress, LT.HtmlUtils.SiteName + " Password Reset", "Login Name: " + (string)accountRow["name"] + "\nPassword: " + newPassword + "\n\nIf you have a hard time remembering it, try tattooing it to your leg for easy access. \n\nThis request was sent from " + Request.UserHostAddress);

                return "A new password was sent to your email.";
            }
        }

 string ModifyPassword(string oldPassword, string newPassword, string newPasswordVerify)
 {
     using (var db = new DBConnection())
     {
         var realOldPassword = db.Evaluate("select password from account where id = {0}", Account.Id) as String;
         if (LT.HtmlUtils.CalculateHash(oldPassword) != realOldPassword)
         {
             return "Unable to modify your password.<br>You did not enter the correct current password.";
         }
         if (newPassword != newPasswordVerify)
         {
             return "Unable to modify your password.<br>The new passwords you entered do not match.";
         }
         if (newPassword.Length < 5)
         {
             return "Unable to modify your password.<br>Password must be at least five letters.";
         }
         db.Execute("update account set password = '{0}' where id = {1}", DBConnection.AddSlashes(LT.HtmlUtils.CalculateHash(newPassword)), Account.Id);
     }
     return "Password modified successfully.";
 }

        string ModifyLoginName(string newLoginName)
        {
            if (!Account.Name.EndsWith('-' + Account.Id.ToString()))
                return "You cannot change your login name";

            using (var db = new DBConnection())
            {
                newLoginName = newLoginName.Trim(new char[] { ' ', '\t', '\n', '\r', '0' });

                if (newLoginName != System.Web.HttpUtility.HtmlEncode(newLoginName) || newLoginName != DBConnection.AddSlashes(newLoginName))
                    return "Invalid login name.";

                if (db.Evaluate("select name from account where name = '" + DBConnection.AddSlashes(newLoginName) + "'") != null)
                    return "Login name already taken";

                db.Execute("update account set name = '" + DBConnection.AddSlashes(newLoginName) + "' where id = " + Account.Id);
                Account.Name = newLoginName;

            //                SendEmail(Account.EmailAddress, Account.Name, "New Login Name", String.Format(
            //@"You've changed your login to {0}
            //
            //You can change your account name and password at http://{1}/Account/Settings
            //", Account.Name,Request.Url.Host));
            }
            return "Login name modified successfully.  It will not be updated in your current games.";
        }

        string ModifyForwardEmails(string newforward)
        {
            using (var db = new DBConnection())
            {
                if (!new string[] { "All", "GameStarts", "AllGame", "None" }.Contains(newforward))
                    return "Invalid Forward Email Setting";

                db.Execute("update account set forward_emails = '" + DBConnection.AddSlashes(newforward) + "' where id = " + Account.Id);
                Account.ForwardEmails = newforward;
            }
            return "Email notifications modified successfully.";
        }

        string ModifyEmail(string newEmail)
        {
            newEmail = newEmail.Trim();

            using (var db = new DBConnection())
            {
                if (!HtmlUtils.IsValidEmailAddress(newEmail))
                    return "Unable to modify email address.<br>You need to enter a valid email address.";

                if (db.Evaluate("select email from account where email = '{0}' and id <> {1}", DBConnection.AddSlashes(newEmail), Account.Id) != null)
                    return "There is already an account with that email address.";

                db.Execute("update account set email = '" + DBConnection.AddSlashes(newEmail) + "' where id = " + Account.Id);
                Account.EmailAddress = newEmail;
            }
            return "Email modified successfully.";
        }

        public ActionResult PlayerInfo(int id)
        {
            if (id <= 0)
                return HttpNotFound();

            var model = new PlayerInfoModel();

            using (var db = new DBConnection())
            {
                model.Account = Account.Load(db.EvaluateRow("select * from account where id = {0}", id));

                if (model.Account == null)
                    return HttpNotFound();

                if (LoggedIn && Account.Id == id) // use freshest account record
                    Account = model.Account;

                if (IsSet("ShowLoginHistory"))
                    model.IpAddresses = db.EvaluateTable("select * from account_login where account_id = " + id + " order by datetime desc limit 100");

                if (IsSet("KillAccount") && Account.IsAdmin)
                {
                    db.Execute("update account set disabled_by = " + Account.Id + " where id=" + id);
                    ViewBag.ErrorMessage = "Account Disabled";
                }

                if (id > 1)
                    model.Games = GameServer.GetPlayerGames(id, IsSet("AllGames"));
                else
                    model.Games = new List<Game>();
            }

            return View("PlayerInfo", model);
        }

        public ActionResult OptOut(int account)
        {
            if (!IsSet("Account") || !IsSet("Key"))
            {
                ViewBag.ErrorMessage = "Missing account or opt out key.";
                return View();
            }

            using (var db = new DBConnection())
            {
                var accountRow = db.EvaluateRow("select * from Account where Id = {0}", account);
                if (accountRow == null)
                {
                    ViewBag.ErrorMessage = "Invalid account.";
                    return View();
                }
                else
                {
                    if ((int)accountRow["OptOutKey"] != GetInt("Key"))
                    {
                        ViewBag.ErrorMessage = "Incorrect opt out key.";
                        return View();
                    }
                }

                db.Execute("update Account set OptOut = 1 where Id = {0}", account);
                ViewBag.ErrorMessage = "You will no longer recieve emails about new features.";
            }

            return View();
        }

        protected string CreateAccount(string loginName, string password, string passwordVerify, string email, out int accountId, bool isTempLoginName = false)
        {
            accountId = 0;
            loginName = loginName.Trim(new char[] { ' ', '\t', '\n', '\r', '0' });
            email = email.Trim();

            if (!LT.HtmlUtils.IsValidEmailAddress(email))
                return "You need to enter a valid email address.";

            if (loginName != System.Web.HttpUtility.HtmlEncode(loginName) || loginName != DBConnection.AddSlashes(loginName))
                return "Invalid login name.";

            using (var db = new DBConnection())
            {
                if (db.Evaluate("select name from account where name = '" + DBConnection.AddSlashes(loginName) + "'") != null)
                    return "Login name already taken";

                if (db.Evaluate("select email from account where email = '" + DBConnection.AddSlashes(email) + "'") != null)
                    return "There is already an account with that email address.";

                if (password != passwordVerify)
                    return "The passwords you entered do not match.";
                if (password.Length < 5)
                    return "Password must be at least five letters.";

                db.Execute
                (
                    "insert into account (name, password, signed_up, email, referred_by, OptOutKey) values('{0}', '{1}', '{2}', '{3}', '{4}', {5})",
                    DBConnection.AddSlashes(loginName),
                    DBConnection.AddSlashes(LT.HtmlUtils.CalculateHash(password)),
                    Utility.UnixTimestamp(DateTime.Now),
                    DBConnection.AddSlashes(email),
                    GetInt("ReferredBy"),
                    Utility.Random.Next(1000000)
                );

                accountId = Convert.ToInt32(db.LastInsertID);

                if (isTempLoginName)
                    db.Execute("update account set name = concat(name, '-', id) where id = {0}", accountId); // append -ID
            }

            return String.Empty;
        }