C# DB.ViziAppsExecuteNonQuery方法代码示例

    protected void ActivateCustomer_Click(object sender, EventArgs e)
    {
        ClearMessages();
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        string customer_id = State["ServerAdminCustomerID"].ToString();
        if (customer_id == "0")
        {
            AdminMessage.Text = "Select a customer and try again.";
            return;
        }

        //check if admin
        string sql = "SELECT status FROM customers WHERE customer_id='" + customer_id + "'";
        DB db = new DB();
        string status = db.ViziAppsExecuteScalar(State, sql);
        if (status == "admin")
        {
            db.CloseViziAppsDatabase(State);
            AdminMessage.Text = "Status of Admin Customer can not be changed.";
        }
        else
        {
            sql = "UPDATE customers SET status='active' WHERE customer_id='" + customer_id + "'";
            db.ViziAppsExecuteNonQuery(State, sql);
            db.CloseViziAppsDatabase(State);
            CustomerStatus.Text = "active";
            AdminMessage.Text = "Customer has been activated.";
        }
    }

    protected void AgreeButton_Click(object sender, EventArgs e)
    {
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        Util util = new Util();
        if (util.CheckSessionTimeout(State, Response, "Default.aspx")) return;

        DB db = new DB();
        string sql = "SELECT agreed_to_eula FROM customers WHERE customer_id = '" +  State["CustomerID"].ToString() + "'";
        string agreed_to_eula = db.ViziAppsExecuteScalar((Hashtable)HttpRuntime.Cache[Session.SessionID], sql);
        if (agreed_to_eula.ToLower() == "false" || agreed_to_eula == "0")
        {
            sql = "UPDATE customers SET agreed_to_eula=true WHERE customer_id = '" +  State["CustomerID"].ToString() + "'";
            db.ViziAppsExecuteNonQuery((Hashtable)HttpRuntime.Cache[Session.SessionID], sql);
            SendEmailToSalesandCustomer(db);
        }
        db.CloseViziAppsDatabase(State);
         State["LoggedinFromEula"] = true;
        Response.Redirect("Default.aspx", false);
    }

    public void CancelPaidServicesDB(Hashtable State)
    {
        try
            {
                Util util = new Util();

                string NOW = DateTime.Now.ToUniversalTime().ToString("u").Replace("Z", "");
                string application_id = State["application_id"].ToString();

                DB db = new DB();

                StringBuilder b_sql = new StringBuilder("UPDATE paid_services SET ");
                b_sql.Append("cancellation_date_time='" + NOW + "',");
                b_sql.Append("status='cancelled'");
                b_sql.Append("WHERE application_id='" + application_id + "'");

                System.Diagnostics.Debug.WriteLine("b_sql =" + b_sql.ToString());
                string sql_string = b_sql.ToString();

                try
                {
                    db.ViziAppsExecuteNonQuery(State, sql_string);
                }
                catch (Exception ex)
                {
                    util.LogError(State, ex);

                    if (!ex.Message.ToLower().Contains("duplicate"))
                        throw new Exception(ex.Message);
                }

                db.CloseViziAppsDatabase(State);

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message.ToString() + ex.StackTrace.ToString());

            }
    }

 public void UpdateStagingAppXmlFromAdmin(Hashtable State)
 {
     try
     {
         string NOW = DateTime.Now.ToUniversalTime().ToString("u").Replace("Z", "");
         DB db = new DB();
         StringBuilder b_sql = new StringBuilder("UPDATE applications SET ");
         b_sql.Append("application_type='" + State["SelectedAdminAppType"].ToString() + "',");
         XmlDocument doc = (XmlDocument)State["AdminAppDesign"];
         Util util = new Util();
         b_sql.Append("staging_app_xml='" + util.MySqlFilter(doc.OuterXml) + "',");
         b_sql.Append("date_time_modified='" + NOW + "' ");
         b_sql.Append("WHERE application_name='" + State["SelectedAdminApp"].ToString() + "'");
         b_sql.Append(" AND customer_id='" + State["ServerAdminCustomerID"].ToString() + "'");
         db.ViziAppsExecuteNonQuery(State, b_sql.ToString());
         db.CloseViziAppsDatabase(State);
     }
     catch (Exception ex)
     {
         throw new Exception("Error in UpdateStagingAppXmlFromAdmin: " + ex.Message + ": " + ex.StackTrace);
     }
 }

 public void DeleteLargeIcon(Hashtable State, string application_id)
 {
     DB db = new DB();
     string sql = "DELETE FROM branding_images WHERE application_id='" + application_id + "' AND type='icon'";
     db.ViziAppsExecuteNonQuery(State, sql);
     db.CloseViziAppsDatabase(State);
 }

    public void DeleteAppPageImage(Hashtable State, string page_name)
    {
        DB db = new DB();
        string application_id = GetAppID(State);

        //delete image file
        string sql = "SELECT page_image_url FROM application_pages WHERE application_id='" + application_id +
            "' AND page_name='" + page_name + "'";
        string page_image_url = db.ViziAppsExecuteScalar(State, sql);
        if (page_image_url != null && page_image_url.Length > 0 && page_image_url != "../images/page_not_saved.jpg")
        {
            HtmlToImage util = new HtmlToImage();
            util.DeleteImageFromUrl(State, page_image_url);
        }

        //delete DB entry
        sql = "DELETE FROM application_pages WHERE application_id='" + application_id + "' AND page_name='" + page_name + "'";
        db.ViziAppsExecuteNonQuery(State, sql);

        db.CloseViziAppsDatabase(State);
    }

 public void SetFreeProductionExpiration(Hashtable State, DateTime expirationDateTime)
 {
     DB db = new DB();
     string expiration = expirationDateTime.ToString("s").Replace("T", " ");
     string sql = "UPDATE applications SET free_production_expiration_date_time='" + expiration + "' WHERE application_name ='" + State["SelectedApp"].ToString() + "' AND customer_id='" + State["CustomerID"].ToString() + "'";
     db.ViziAppsExecuteNonQuery(State, sql);
     db.CloseViziAppsDatabase(State);
 }

//.........这里部分代码省略.........
                    //rows = db.ViziAppsExecuteSql(State, sql);
                    //if (rows.Length == 0)
                    if (State["Password"] == null)
                    {
                        //db.CloseViziAppsDatabase(State);
                        SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: bad credentials");
                        throw new System.InvalidOperationException("Either the username or the password: " + password + " is incorrect.");
                    }

                    //check number of users -- unlimited use never needs a login
                    //bool use_1_user_credential = util.GetUse1UserCredential(State, application_id);
                    //if (use_1_user_credential)
                    if (State["Use1UserCredential"] != null && State["Use1UserCredential"].ToString() == "true")
                    {
                        Hashtable features = util.IsProductionAppPaid(State, application_id);
                        DataRow row = rows[0];
                        sql = "SELECT COUNT(*) FROM users_device_ids WHERE user_id='" + row["user_id"].ToString() + "'";
                        int device_count = Convert.ToInt32(db.ViziAppsExecuteScalar(State, sql));

                        sql = "SELECT COUNT(*) FROM users_device_ids WHERE device_id='" + device_id + "'";
                        string device_exists = db.ViziAppsExecuteScalar(State, sql);

                        if (device_exists == "0")
                        {
                            if (device_count >= (int)features["max_users"])
                            {
                                db.CloseViziAppsDatabase(State);
                                SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: reached limit of users");
                                throw new System.InvalidOperationException("Cannot download app: reached limit of users.");
                            }
                            else
                            {
                                sql = "INSERT INTO users_device_ids SET device_id='" + device_id + "',user_id='" + row["user_id"].ToString() + "'";
                                db.ViziAppsExecuteNonQuery(State, sql);
                            }
                        }
                        //else app is allowed
                    }
                }
            }
            else //staging
            {
                sql = "SELECT * FROM customers WHERE username='" + user.ToLower() + "'";
                rows = db.ViziAppsExecuteSql(State, sql);
                if (rows.Length == 0)
                {
                    db.CloseViziAppsDatabase(State);
                    SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: user not registered");
                    throw new Exception("The username " + user.ToLower() + " is not registered. Go to www.viziapps.com and create a free account.");
                }

                DataRow row = rows[0];
                if (row["password"].ToString() != password)
                {
                    db.CloseViziAppsDatabase(State);
                    SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: bad credentials");
                    throw new Exception("Either the username or the password: " + password + " is incorrect.");
                }
                if (row["status"].ToString() == "inactive")
                {
                    db.CloseViziAppsDatabase(State);
                    SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: account is inactive");
                    throw new Exception("Your account is inactive. Contact ViziApps to re-activate your account.");
                }
                customer_id = row["customer_id"].ToString();
                State["CustomerID"] = customer_id;

    private void DoRemoveCustomer(string username, string customer_id)
    {
        ClearMessages();
        DB db = new DB();
        string sql = "UPDATE customers SET status='inactive' WHERE customer_id='" + customer_id + "'";
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        db.ViziAppsExecuteNonQuery(State, sql);

        sql = "SELECT application_id FROM applications WHERE customer_id='" + customer_id + "'";
        DataRow[] rows3 = db.ViziAppsExecuteSql(State, sql);
        foreach (DataRow row3 in rows3)
        {
            string application_id = row3["application_id"].ToString();
            sql = "DELETE FROM application_pages WHERE application_id='" + application_id + "'";
            db.ViziAppsExecuteNonQuery(State, sql);
        }
        sql = "DELETE FROM applications WHERE customer_id='" + customer_id + "'";
        db.ViziAppsExecuteNonQuery(State, sql);

        sql = "DELETE FROM customers WHERE status='inactive' AND customer_id='" + customer_id + "'";
        db.ViziAppsExecuteNonQuery(State, sql);

        db.CloseViziAppsDatabase(State);
    }

 public void SetProductionAppName(Hashtable State, string production_app_name)
 {
     DB db = new DB();
     string sql = "UPDATE applications SET production_app_name='" + production_app_name + "' " +
     "WHERE application_name='" + State["SelectedApp"].ToString() + "'" +
     " AND customer_id='" + State["CustomerID"].ToString() + "'";
     db.ViziAppsExecuteNonQuery(State, sql);
     db.CloseViziAppsDatabase(State);
 }

    public void CopyAppToAccount(Hashtable State, string application_name)
    {
        DB db = new DB();
        StringBuilder b_sql = new StringBuilder("SELECT * FROM applications ");
        b_sql.Append("WHERE application_name='" + application_name + "'");
        b_sql.Append(" AND customer_id='" + State["CopyApplicationFromCustomerID"].ToString() + "'");
        DataRow[] rows = db.ViziAppsExecuteSql(State, b_sql.ToString());
        DataRow row = rows[0];

        string previous_application_id = row["application_id"].ToString();
        string application_id = Guid.NewGuid().ToString();

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(DecodeMySql(row["staging_app_xml"].ToString()));

        //delete any app with the same name
        db.ViziAppsExecuteNonQuery(State, "DELETE FROM applications WHERE application_name='" + application_name + "' AND customer_id='" + State["CopyApplicationToCustomerID"].ToString() + "'");

        string username = db.ViziAppsExecuteScalar(State, "SELECT username FROM customers WHERE customer_id='" + State["CopyApplicationToCustomerID"].ToString() + "'");

        b_sql = new StringBuilder("INSERT into applications SET ");
        b_sql.Append("application_id='" + application_id + "',");
        b_sql.Append("customer_id='" + State["CopyApplicationToCustomerID"].ToString() + "',");
        b_sql.Append("username='" + username + "',");

        XmlUtil x_util = new XmlUtil();
        string new_xml = x_util.RenameAppXmlWithID(State, row["staging_app_xml"].ToString(), application_name, application_id);

        b_sql.Append("staging_app_xml='" + MySqlFilter(new_xml) + "',");
        if (row["custom_header_html"] != null)
            b_sql.Append("custom_header_html='" + MySqlFilter(row["custom_header_html"].ToString()) + "',");
        b_sql.Append("application_name='" + application_name + "',");
        b_sql.Append("application_type='" + row["application_type"].ToString() + "',");

        if (row["default_button_image"] != null)
            b_sql.Append("default_button_image='" + row["default_button_image"].ToString() + "',");

        b_sql.Append("description='" + row["description"].ToString().Replace("'", "''").Replace(@"\", @"\\") + "',");
        string NOW = DateTime.Now.ToUniversalTime().ToString("u").Replace("Z", "");
        b_sql.Append("date_time_modified='" + NOW + "'");
        db.ViziAppsExecuteNonQuery(State, b_sql.ToString());

        //get all the pages
        string sql = "SELECT * FROM application_pages WHERE application_id='" + previous_application_id + "'";
        rows = db.ViziAppsExecuteSql(State, sql);

        //insert all the pages into the new app
        foreach (DataRow page_row in rows)
        {
            sql = "INSERT INTO application_pages (application_page_id,application_id,page_name,page_image_url,date_time_modified) VALUES (UUID(),'" +
            application_id + "','" +
            page_row["page_name"].ToString() + "','" +
            page_row["page_image_url"].ToString() + "','" + NOW + "')";
            db.ViziAppsExecuteNonQuery(State, sql);
        }
        db.CloseViziAppsDatabase(State);
        //reset
        State["AppXmlDoc"] = null;
    }

    public void UpdateUserCredentials(Hashtable State,
        string application_id, string[] credential)
    {
        DB db = new DB();
        string sql = "DELETE FROM users WHERE application_id='" + application_id + "'";
        db.ViziAppsExecuteNonQuery(State, sql);

        StringBuilder sb = new StringBuilder("INSERT INTO users (user_id,customer_id,application_id,username,password,status) VALUES ");
        string customer_id = State["CustomerID"].ToString();

        sb.Append("(UUID(),'" +
        customer_id +
        "','" + application_id +
        "','" + credential[0] +
        "','" + credential[1] +
        "','active')");

        db.ViziAppsExecuteNonQuery(State, sb.ToString());
        db.CloseViziAppsDatabase(State);
    }

    public void CancelPaidService(Hashtable State, string purchase_date, string sku)
    {
        DB db = new DB();
        string sql = "SELECT app_name FROM paid_services WHERE sku='" + sku + "' AND purchase_date='" + purchase_date + "'";
        DataRow[] rows = db.ViziAppsExecuteSql(State, sql);
        if (rows.Length > 0)
        {
            DataRow row = rows[0];
            string app_name = row["app_name"].ToString();
            if (app_name != null && app_name.Length != 0)
                RemoveAppFromProductionService(State, app_name, sku);
        }

        string NOW = DateTime.Now.ToUniversalTime().ToString("u").Replace("Z", "");
        int day_of_month = DateTime.Parse(purchase_date).Day;
        string expiration = DateTime.Now.ToUniversalTime()
            .AddDays(-Convert.ToDouble(DateTime.Now.ToUniversalTime().Day))
            .AddMonths(1)
            .AddDays(Convert.ToDouble(day_of_month))
            .ToString("u").Replace("Z", "");
        StringBuilder b_sql = new StringBuilder("UPDATE paid_services SET ");
        b_sql.Append("cancellation_date_time='" + NOW + "', ");
        b_sql.Append("expiration_date_time='" + expiration + "', ");
        b_sql.Append("app_name='NULL', ");
        b_sql.Append("application_id='NULL', ");
        b_sql.Append("status='cancelled' ");
        b_sql.Append("WHERE sku='" + sku + "' ");
        b_sql.Append("AND purchase_date='" + purchase_date + "'");
        db.ViziAppsExecuteNonQuery(State, b_sql.ToString());
        db.CloseViziAppsDatabase(State);

        ResetAppInDynamoDB(State);
    }

 public void UpdateStagingAppXml(Hashtable State, string application_name)
 {
     try
     {
         string NOW = DateTime.Now.ToUniversalTime().ToString("u").Replace("Z", "");
         XmlDocument doc = (XmlDocument)State["AppXmlDoc"];
         DB db = new DB();
         StringBuilder b_sql = new StringBuilder("UPDATE applications SET ");
         if (State["SelectedAppType"] == null)
             State["SelectedAppType"] = Constants.NATIVE_APP_TYPE;
         b_sql.Append("application_type='" + State["SelectedAppType"].ToString() + "',");
         b_sql.Append("staging_app_xml='" + MySqlFilter(doc.OuterXml) + "',");
         b_sql.Append("date_time_modified='" + NOW + "' ");
         b_sql.Append("WHERE application_name='" + application_name + "'");
         b_sql.Append(" AND customer_id='" + State["CustomerID"].ToString() + "'");
         db.ViziAppsExecuteNonQuery(State, b_sql.ToString());
         db.CloseViziAppsDatabase(State);
     }
     catch (Exception ex)
     {
         throw new Exception("Error in UpdateStagingAppXml: " + ex.Message + ": " + ex.StackTrace);
     }
 }

 /*public void SetApplicationLongDescription(Hashtable State, string long_description)
 {
     DB db = new DB();
     StringBuilder b_sql = new StringBuilder("UPDATE applications SET appstore_description='" + MySqlFilter(long_description) + "'");
     b_sql.Append(" WHERE application_name='" + State["SelectedApp"].ToString() + "'");
     b_sql.Append(" AND customer_id='" + State["CustomerID"].ToString() + "'");
     db.ViziAppsExecuteNonQuery(State, b_sql.ToString());
     db.CloseViziAppsDatabase(State);
 }
 public void SetApplicationKeywords(Hashtable State, string keywords)
 {
     DB db = new DB();
     StringBuilder b_sql = new StringBuilder("UPDATE applications SET keywords='" + MySqlFilter(keywords) + "'");
     b_sql.Append(" WHERE application_name='" + State["SelectedApp"].ToString() + "'");
     b_sql.Append(" AND customer_id='" + State["CustomerID"].ToString() + "'");
     db.ViziAppsExecuteNonQuery(State, b_sql.ToString());
     db.CloseViziAppsDatabase(State);
 }
 public void SetApplicationPricing(Hashtable State, string price)
 {
     DB db = new DB();
     StringBuilder b_sql = new StringBuilder("UPDATE applications SET price='" + price + "'");
     b_sql.Append(" WHERE application_name='" + State["SelectedApp"].ToString() + "'");
     b_sql.Append(" AND customer_id='" + State["CustomerID"].ToString() + "'");
     db.ViziAppsExecuteNonQuery(State, b_sql.ToString());
     db.CloseViziAppsDatabase(State);
 }*/
 public void SetUnlimitedUsers(Hashtable State)
 {
     DB db = new DB();
     String sql = "UPDATE applications SET has_unlimited_users=1,use_1_user_credential=0 WHERE application_name='" +
         State["SelectedApp"].ToString() + "' AND customer_id='" + State["CustomerID"].ToString() + "'";
     db.ViziAppsExecuteNonQuery(State, sql);
     db.CloseViziAppsDatabase(State);
     string application_id = GetAppID(State);
     DeleteUserCredentials(State, application_id);
 }