C# dataAccess.sqlGetSingleInteger方法代码示例

        public static string SaveAccount(string sMode, string sAccountID, string sAccountName, string sAccountNumber, string sProvider, 
			string sLoginID, string sLoginPassword, string sLoginPasswordConfirm, string sIsDefault, string sAutoManageSecurity)
        {
            // for logging
            string sOriginalName = "";

            dataAccess dc = new dataAccess();
            acUI.acUI ui = new acUI.acUI();
            string sSql = "";
            string sErr = "";

            //if we are editing get the original values
            if (sMode == "edit")
            {
            }

            try
            {
                dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);

                // update the user fields.
                if (sMode == "edit")
                {
                    sSql = "select account_name from cloud_account " +
                           "where account_id = '" + sAccountID + "'";
                    if (!dc.sqlGetSingleString(ref sOriginalName, sSql, ref sErr))
                        throw new Exception("Error getting original account name:" + sErr);

                    // only update the passwword if it has changed
                    string sNewPassword = "";
                    if (sLoginPassword != "($%#[email protected]!&")
                    {
                        sNewPassword = ", login_password = '" + dc.EnCrypt(sLoginPassword) + "'";
                    }

                    sSql = "update cloud_account set" +
                        " account_name = '" + sAccountName + "'," +
                        " account_number = '" + sAccountNumber + "'," +
                        " provider = '" + sProvider + "'," +
                        " is_default = '" + sIsDefault + "'," +
                        " auto_manage_security = '" + sAutoManageSecurity + "'," +
                        " login_id = '" + sLoginID + "'" +
                        sNewPassword +
                        " where account_id = '" + sAccountID + "'";

                    oTrans.Command.CommandText = sSql;
                    if (!oTrans.ExecUpdate(ref sErr))
                        throw new Exception("Error updating account: " + sErr);

                    ui.WriteObjectChangeLog(Globals.acObjectTypes.CloudAccount, sAccountID, sAccountName, sOriginalName, sAccountName);}
                else
                {
                    //now, for some reason we were having issues with the initial startup of apache
                    //not able to perform the very first database hit.
                    //this line serves as an inital db hit, but we aren't trapping it or showing the error
                    dc.TestDBConnection(ref sErr);

                    //if there are no rows yet, make this one the default even if the box isn't checked.
                    if (sIsDefault == "0")
                    {
                        int iExists = -1;

                        sSql = "select count(*) as cnt from cloud_account";
                        if (!dc.sqlGetSingleInteger(ref iExists, sSql, ref sErr))
                        {
                            System.Threading.Thread.Sleep(300);
                            if (!dc.sqlGetSingleInteger(ref iExists, sSql, ref sErr))
                            {
                                System.Threading.Thread.Sleep(300);
                                if (!dc.sqlGetSingleInteger(ref iExists, sSql, ref sErr))
                                    throw new Exception("Unable to count Cloud Accounts: " + sErr);
                            }
                        }

                        if (iExists == 0)
                            sIsDefault = "1";
                    }

                    sAccountID = ui.NewGUID();
                    sSql = "insert into cloud_account (account_id, account_name, account_number, provider, is_default, login_id, login_password, auto_manage_security)" +
                    " values ('" + sAccountID + "'," +
                    "'" + sAccountName + "'," +
                    "'" + sAccountNumber + "'," +
                    "'" + sProvider + "'," +
                    "'" + sIsDefault + "'," +
                    "'" + sLoginID + "'," +
                    "'" + dc.EnCrypt(sLoginPassword) + "'," +
                    "'" + sAutoManageSecurity + "')";

                    oTrans.Command.CommandText = sSql;
                    if (!oTrans.ExecUpdate(ref sErr))
                        throw new Exception("Error creating account: " + sErr);

                    ui.WriteObjectAddLog(Globals.acObjectTypes.CloudAccount, sAccountID, sAccountName, "Account Created");
                }

                //if "default" was selected, unset all the others
                if (dc.IsTrue(sIsDefault))
                {
                    oTrans.Command.CommandText = "update cloud_account set is_default = 0 where account_id <> '" + sAccountID + "'";
//.........这里部分代码省略.........

        public void wmToggleStepCommonSection(string sStepID, string sButton)
        {
            dataAccess dc = new dataAccess();

            acUI.acUI ui = new acUI.acUI();

            try
            {
                if (ui.IsGUID(sStepID))
                {
                    string sUserID = ui.GetSessionUserID();

                    sButton = (sButton == "" ? "null" : "'" + sButton + "'");

                    string sErr = "";

                    //is there a row?
                    int iRowCount = 0;
                    dc.sqlGetSingleInteger(ref iRowCount, "select count(*) from task_step_user_settings" +
                                " where user_id = '" + sUserID + "'" +
                                " and step_id = '" + sStepID + "'", ref sErr);

                    if (iRowCount == 0)
                    {
                        string sSQL = "insert into task_step_user_settings" +
                            " (user_id, step_id, visible, breakpoint, skip, button)" +
                            " values ('" + sUserID + "','" + sStepID + "', 1, 0, 0, " + sButton + ")";
                        if (!dc.sqlExecuteUpdate(sSQL, ref sErr))
                            throw new Exception("Unable to toggle step button (0) [" + sStepID + "]." + sErr);
                    }
                    else
                    {
                        string sSQL = " update task_step_user_settings set button = " + sButton +
                            " where step_id = '" + sStepID + "';";
                        if (!dc.sqlExecuteUpdate(sSQL, ref sErr))
                            throw new Exception("Unable to toggle step button (1) [" + sStepID + "]." + sErr);
                    }

                    return;
                }
                else
                {
                    throw new Exception("Unable to toggle step button. Missing or invalid step_id or button.");
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string CopyTask(int iMode, string sSourceTaskID, string sNewTaskName, string sNewTaskCode)
        {
            //iMode 0=new task, 1=new major version, 2=new minor version
            dataAccess dc = new dataAccess();
            acUI.acUI ui = new acUI.acUI();

            string sErr = "";
            string sSQL = "";

            string sNewTaskID = ui.NewGUID();

            int iIsDefault = 0;
            string sTaskName = "";
            double dVersion = 1.000;
            double dMaxVer = 0.000;
            string sOTID = "";

            //do it all in a transaction
            dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);

            //figure out the new name and selected version
            oTrans.Command.CommandText = "select task_name, version, original_task_id from task where task_id = '" + sSourceTaskID + "'";
            DataRow dr = null;
            if (!oTrans.ExecGetDataRow(ref dr, ref sErr))
                throw new Exception("Unable to find task for ID [" + sSourceTaskID + "]." + sErr);

            sTaskName = dr["task_name"].ToString();
            dVersion = Convert.ToDouble(dr["version"]);
            sOTID = dr["original_task_id"].ToString();

            //figure out the new version
            switch (iMode)
            {
                case 0:
                    sTaskName = sNewTaskName;
                    iIsDefault = 1;
                    dVersion = 1.000;
                    sOTID = sNewTaskID;

                    break;
                case 1:
                    //gotta get the highest version
                    sSQL = "select max(version) from task where task_id = '" + sOTID + "'";
                    dc.sqlGetSingleDouble(ref dMaxVer, sSQL, ref sErr);
                    if (sErr != "")
                    {
                        oTrans.RollBack();
                        throw new Exception(sErr);
                    }

                    dVersion = dMaxVer + 1;

                    break;
                case 2:
                    sSQL = "select max(version) from task where task_id = '" + sOTID + "'" +
                        " and cast(version as unsigned) = " + Convert.ToInt32(dVersion);
                    dc.sqlGetSingleDouble(ref dMaxVer, sSQL, ref sErr);
                    if (sErr != "")
                    {
                        oTrans.RollBack();
                        throw new Exception(sErr);
                    }

                    dVersion = dMaxVer + 0.001;

                    break;
                default: //a iMode is required
                    throw new Exception("A mode required for this copy operation." + sErr);
            }

            //if we are versioning, AND there are not yet any 'Approved' versions,
            //we set this new version to be the default
            //(that way it's the one that you get taken to when you pick it from a list)
            if (iMode > 0)
            {
                sSQL = "select case when count(*) = 0 then 1 else 0 end" +
                    " from task where original_task_id = '" + sOTID + "'" +
                    " and task_status = 'Approved'";
                dc.sqlGetSingleInteger(ref iIsDefault, sSQL, ref sErr);
                if (sErr != "")
                {
                    oTrans.RollBack();
                    throw new Exception(sErr);
                }
            }

            //start copying
            oTrans.Command.CommandText = "create temporary table _copy_task" +
                " select * from task where task_id = '" + sSourceTaskID + "'";
            if (!oTrans.ExecUpdate(ref sErr))
                throw new Exception(sErr);

            //update the task_id
            oTrans.Command.CommandText = "update _copy_task set" +
                " task_id = '" + sNewTaskID + "'," +
                " original_task_id = '" + sOTID + "'," +
                " version = '" + dVersion + "'," +
                " task_name = '" + sTaskName + "'," +
                " default_version = " + iIsDefault.ToString() + "," +
                " task_status = 'Development'," +
//.........这里部分代码省略.........

        public void wmToggleStep(string sStepID, string sVisible)
        {
            dataAccess dc = new dataAccess();
            acUI.acUI ui = new acUI.acUI();

            sVisible = (sVisible == "1" ? "1" : "0");

            try
            {
                if (ui.IsGUID(sStepID))
                {
                    string sErr = "";
                    dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);

                    string sUserID = ui.GetSessionUserID();

                    //is there a row?
                    int iRowCount = 0;
                    dc.sqlGetSingleInteger(ref iRowCount, "select count(*) from task_step_user_settings" +
                                " where user_id = '" + sUserID + "'" +
                                " and step_id = '" + sStepID + "'", ref sErr);

                    if (iRowCount == 0)
                    {
                        oTrans.Command.CommandText = "insert into task_step_user_settings" +
                            " (user_id, step_id, visible, breakpoint, skip)" +
                            " values ('" + sUserID + "','" + sStepID + "', " + sVisible + ", 0, 0)";

                        if (!oTrans.ExecUpdate(ref sErr))
                            throw new Exception("Unable to toggle step (0) [" + sStepID + "]." + sErr);
                    }
                    else
                    {
                        oTrans.Command.CommandText = " update task_step_user_settings set visible = '" + sVisible + "'" +
                            " where step_id = '" + sStepID + "'";
                        if (!oTrans.ExecUpdate(ref sErr))
                            throw new Exception("Unable to toggle step (1) [" + sStepID + "]." + sErr);
                    }

                    oTrans.Commit();

                    return;
                }
                else
                {
                    throw new Exception("Unable to toggle step. Missing or invalid step_id.");
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string wmApproveTask(string sTaskID, string sMakeDefault)
        {
            dataAccess dc = new dataAccess();

            acUI.acUI ui = new acUI.acUI();

            try
            {
                string sUserID = ui.GetSessionUserID();

                if (ui.IsGUID(sTaskID) && ui.IsGUID(sUserID))
                {
                    string sErr = "";
                    string sSQL = "";

                    //check to see if this is the first task to be approved.
                    //if it is, we will make it default.
                    sSQL = "select count(*) from task" +
                        " where original_task_id = " +
                        " (select original_task_id from task where task_id = '" + sTaskID + "')" +
                        " and task_status = 'Approved'";

                    int iCount = 0;
                    if (!dc.sqlGetSingleInteger(ref iCount, sSQL, ref sErr))
                    {
                        throw new Exception("Unable to count Tasks in this family.." + sErr);
                    }

                    if (iCount == 0)
                        sMakeDefault = "1";

                    dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);

                    //flag all the other tasks as not default if this one is meant to be
                    if (sMakeDefault == "1")
                    {
                        sSQL = "update task set" +
                            " default_version = 0" +
                            " where original_task_id =" +
                            " (select original_task_id from (select original_task_id from task where task_id = '" + sTaskID + "') as x)";
                        oTrans.Command.CommandText = sSQL;
                        if (!oTrans.ExecUpdate(ref sErr))
                        {
                            throw new Exception("Unable to update task [" + sTaskID + "]." + sErr);
                        }
                        sSQL = "update task set" +
                        " task_status = 'Approved'," +
                        " default_version = 1" +
                        " where task_id = '" + sTaskID + "';";
                    }
                    else
                    {
                        sSQL = "update task set" +
                            " task_status = 'Approved'" +
                            " where task_id = '" + sTaskID + "'";
                    }

                    oTrans.Command.CommandText = sSQL;
                    if (!oTrans.ExecUpdate(ref sErr))
                    {
                        throw new Exception("Unable to update task [" + sTaskID + "]." + sErr);
                    }

                    oTrans.Commit();

                    ui.WriteObjectChangeLog(Globals.acObjectTypes.Task, sTaskID, "Status", "Development", "Approved");
                    if (sMakeDefault == "1")
                        ui.WriteObjectChangeLog(Globals.acObjectTypes.Task, sTaskID, "Default", "Set as Default Version.");

                }
                else
                {
                    throw new Exception("Unable to update task. Missing or invalid task id. [" + sTaskID + "]");
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public string wmRenameCodeblock(string sTaskID, string sOldCodeblockName, string sNewCodeblockName)
        {
            dataAccess dc = new dataAccess();

            acUI.acUI ui = new acUI.acUI();
            FunctionTemplates.HTMLTemplates ft = new FunctionTemplates.HTMLTemplates();

            try
            {
                if (ui.IsGUID(sTaskID))
                {

                    // first make sure we are not trying to rename it something that already exists.
                    string sErr = "";
                    string sSQL = "select count(*) from task_codeblock where task_id = '" + sTaskID + "'" +
                        " and codeblock_name = '" + sNewCodeblockName + "'";
                    int iCount = 0;

                    if (!dc.sqlGetSingleInteger(ref iCount, sSQL, ref sErr))
                    {
                        throw new Exception("Unable to check codeblock names for task." + sErr);
                    }
                    if (iCount != 0)
                    {
                        return ("Codeblock Name already in use, choose another.");
                    }

                    // do it
                    dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);

                    //update the codeblock table
                    sSQL = "update task_codeblock set codeblock_name = '" + sNewCodeblockName +
                        "' where codeblock_name = '" + sOldCodeblockName +
                        "' and task_id = '" + sTaskID + "'";

                    oTrans.Command.CommandText = sSQL;
                    if (!oTrans.ExecUpdate(ref sErr))
                    {
                        throw new Exception(sErr);
                    }

                    //and any steps in that codeblock
                    sSQL = "update task_step set codeblock_name = '" + sNewCodeblockName +
                        "' where codeblock_name = '" + sOldCodeblockName +
                        "' and task_id = '" + sTaskID + "'";

                    oTrans.Command.CommandText = sSQL;
                    if (!oTrans.ExecUpdate(ref sErr))
                    {
                        throw new Exception(sErr);
                    }

                    //the fun part... rename it where it exists in any steps
                    //but this must be in a loop of only the steps where that codeblock reference exists.
                    sSQL = "select step_id from task_step" +
                        " where task_id = '" + sTaskID + "'" +
                        " and ExtractValue(function_xml, '//codeblock[1]') = '" + sOldCodeblockName + "'";
                    oTrans.Command.CommandText = sSQL;
                    DataTable dtSteps = new DataTable();
                    if (!oTrans.ExecGetDataTable(ref dtSteps, ref sErr))
                    {
                        throw new Exception("Unable to get steps referencing the Codeblock." + sErr);
                    }

                    foreach (DataRow dr in dtSteps.Rows)
                    {
                        ft.SetNodeValueinXMLColumn("task_step", "function_xml", "step_id = '" + dr["step_id"].ToString() + "'", "//codeblock[. = '" + sOldCodeblockName + "']", sNewCodeblockName);
                    }

                    //all done
                    oTrans.Commit();

                    return sErr;

                }
                else
                {
                    throw new Exception("Unable to get codeblocks for task. Missing or invalid task_id.");
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static string DeleteAssets(string sDeleteArray)
        {
            dataAccess dc = new dataAccess();
            acUI.acUI ui = new acUI.acUI();
            string sSql = null;
            string sErr = "";

            ArrayList arrList = new ArrayList();
            arrList.AddRange(sDeleteArray.Split(','));

            if (sDeleteArray.Length < 36)
                return "";

            StringBuilder sbAssetIDString = new StringBuilder();
            StringBuilder sbAssetsCantDelete = new StringBuilder();
            foreach (string sAssetID in arrList)
            {
                if (sAssetID.Length == 36)
                {
                    // what about the instance tables?????
                    // bugzilla 1290 Assets that have history (task_instance table) can not be deleted
                    // exclude them from the list and return a message noting the asset(s) that could not be deleted
                    // check if this asset has any history rows.
                    sSql = "select count(*) from tv_task_instance where asset_id = '" + sAssetID + "'";
                    int iHistory = 0;
                    if (!dc.sqlGetSingleInteger(ref iHistory, sSql, ref sErr))
                        throw new Exception(sErr);
                    // if there is no history add this to the delete list,
                    // otherwise add the task id to the non delete list
                    if (iHistory == 0)
                    {
                        sbAssetIDString.Append("'" + sAssetID + "',");
                    }
                    else
                    {
                        sbAssetsCantDelete.Append("'" + sAssetID + "',");
                    };

                }
            }
            // trim the trailing ,
            if (sbAssetsCantDelete.ToString().Length > 2) { sbAssetsCantDelete.Remove(sbAssetsCantDelete.Length - 1, 1); };

            if (sbAssetIDString.ToString().Length > 2)
            {
                // delete from these tables:
                //   asset, asset_credential (if the credential is local).

                // trim the trailing ,
                sbAssetIDString.Remove(sbAssetIDString.Length - 1, 1);
                try
                {
                    dataAccess.acTransaction oTrans = new dataAccess.acTransaction(ref sErr);

                    // delete asset_credential
                    sSql = "delete from asset_credential" +
                        " where shared_or_local = 1" +
                        " and credential_id in (select credential_id from asset where asset_id in (" + sbAssetIDString.ToString() + "))";
                    oTrans.Command.CommandText = sSql;
                    if (!oTrans.ExecUpdate(ref sErr))
                        throw new Exception(sErr);

                    // delete asset
                    sSql = "delete from asset where asset_id in (" + sbAssetIDString.ToString() + ")";
                    oTrans.Command.CommandText = sSql;
                    if (!oTrans.ExecUpdate(ref sErr))
                        throw new Exception(sErr);

                    oTrans.Commit();

                    // add security log
                    ui.WriteObjectDeleteLog(Globals.acObjectTypes.Asset, sbAssetIDString.ToString(), "Batch Asset Delete", "Deleted Assets in batch mode");
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            };

            // if some did not get deleted return a message.
            if (sbAssetsCantDelete.Length > 2)
            {
                string sTaskNames = "";
                sSql = "select asset_name from asset where asset_id in (" + sbAssetsCantDelete.ToString() + ")";

                if (!dc.csvGetList(ref sTaskNames, sSql, ref sErr, true))
                    throw new Exception(sErr);

                return "Asset deletion completed. Asset(s) (" + sTaskNames + ") could not be deleted because history rows exist.";

            }
            else
            {
                return sErr;
            }
        }

        public string wmDeleteEcotemplates(string sDeleteArray)
        {
            dataAccess dc = new dataAccess();
            acUI.acUI ui = new acUI.acUI();
            string sSQL = null;
            string sErr = "";

            if (sDeleteArray.Length == 0)
                return "";

            try
            {
                sDeleteArray = ui.QuoteUp(sDeleteArray);

                //can't delete templates that have references...
                int iResults = 0;
                sSQL = "select count(*) from ecosystem where ecotemplate_id in (" + sDeleteArray.ToString() + ")";
                if (!dc.sqlGetSingleInteger(ref iResults, sSQL, ref sErr))
                    throw new Exception(sErr);

                if (iResults == 0)
                {
                    sSQL = "delete from ecotemplate_action where ecotemplate_id in (" + sDeleteArray.ToString() + ")";
                    if (!dc.sqlExecuteUpdate(sSQL, ref sErr))
                        throw new Exception(sErr);

                    sSQL = "delete from ecotemplate where ecotemplate_id in (" + sDeleteArray.ToString() + ")";
                    if (!dc.sqlExecuteUpdate(sSQL, ref sErr))
                        throw new Exception(sErr);

                    // if we made it here, so save the logs
                    ui.WriteObjectDeleteLog(acObjectTypes.EcoTemplate, "", "", "Eco Templates(s) Deleted [" + sDeleteArray.ToString() + "]");
                }
                else
                    sErr = "Unable to delete Ecosystem Template - " + iResults.ToString() + " Ecosystems reference it.";
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return sErr;
        }