C# InfoCommand.ExecuteNonQuery方法代码示例

        public object SetUserMenus(object[] objParam)
        {
            String strUserID = objParam[0].ToString();
            String strItemType = objParam[2].ToString();
            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                //delete groups which will possibly be repeated later
                string strDel = "delete from USERMENUS where USERID = '" + strUserID + "' and MENUID in (select MENUID from MENUTABLE where ITEMTYPE='" + strItemType + "')";

                //为了区分不同的数据库 by Rei
                InfoCommand cmd = new InfoCommand(ClientInfo);

                cmd.Connection = nwindConn;
                cmd.CommandText = strDel;
                cmd.ExecuteNonQuery();

                //add new groups
                if (objParam[1] != null && objParam[1].ToString() != "")
                {
                    string[] MenuID = ((string)objParam[1]).Split(';');

                    for (int i = 0; i < MenuID.Length; i++)
                        if (MenuID[i].ToString() != "" && strUserID != "")
                        {
                            string strInsert = "insert into USERMENUS (USERID, MENUID) values ('"
                               + strUserID + "', '" + MenuID[i].ToString() + "')";

                            //为了区分不同的数据库 by Rei
                            InfoCommand InsertCmd = new InfoCommand(ClientInfo);

                            InsertCmd.Connection = nwindConn;
                            InsertCmd.CommandText = strInsert;
                            InsertCmd.ExecuteNonQuery();
                        }
                }

                return new object[] { 0, null };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object DeleteUserMenuControls(object[] objParam)
        {
            String strMenuID = objParam[0].ToString();
            String strUserID = objParam[1].ToString();

            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                string strSql = "DELETE FROM USERMENUCONTROL WHERE MENUID='" + strMenuID + "' AND USERID='" + strUserID + "'";

                //为了区分不同的数据库 by Rei
                InfoCommand cmd = new InfoCommand(ClientInfo);

                cmd.CommandText = strSql;
                cmd.Connection = nwindConn;
                cmd.ExecuteNonQuery();
                return new object[] { 0 };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object DelUser(object[] objParam)
        {
            string str = (String)objParam[0];
            string[] id = str.Split(';');
            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                string strSql = "delete from USERMENUCONTROL where USERID = '" + id[0] + "' and MENUID = '" + id[1] + "' and CONTROLNAME = '" + id[2] + "'";

                //为了区分不同的数据库 by Rei
                InfoCommand cmd = new InfoCommand(ClientInfo);

                cmd.CommandText = strSql;
                cmd.Connection = nwindConn;
                cmd.ExecuteNonQuery();
                return new object[] { 0, null };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object UpdateRoleAgent(object[] objParam)
        {
            String roleID = (String)objParam[0];
            String[] roles = objParam[1].ToString().Split(new String[] { "!" }, StringSplitOptions.RemoveEmptyEntries);

            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            InfoCommand cmd = new InfoCommand(ClientInfo);
            try
            {
                String strSql = "DELETE FROM SYS_ROLES_AGENT WHERE ROLE_ID='" + roleID + "'";
                cmd.Transaction = nwindConn.BeginTransaction();
                cmd.CommandText = strSql;
                cmd.Connection = nwindConn;
                cmd.ExecuteNonQuery();

                for (int i = 0; i < roles.Length; i++)
                {
                    if (roles[i].EndsWith(","))
                        roles[i] = roles[i].Remove(roles[i].LastIndexOf(","));
                    strSql = "INSERT INTO SYS_ROLES_AGENT (ROLE_ID, AGENT, FLOW_DESC, START_DATE, START_TIME, END_DATE, END_TIME, PAR_AGENT, REMARK) " +
                        "VALUES ('" + roleID + "', " + roles[i] + ")";

                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
                return new object[] { 0 };
            }
            catch (Exception ex)
            {
                cmd.Transaction.Rollback();
                return new object[] { 0, ex.Message };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object UpdateWorkFlow(object[] objParam)
        {
            if (IsWorkFlowTransactionEnabled && GetWorkFlowTransaction(GetClientInfo(ClientInfoType.LoginDB).ToString()) != null)
            {
                IDbConnection nwindConn = AllocateWorkFlowConnection(GetClientInfo(ClientInfoType.LoginDB).ToString());
                try
                {
                    string sql = (string)objParam[0];

                    InfoCommand cmd = new InfoCommand(ClientInfo);
                    cmd.Connection = nwindConn;
                    cmd.CommandText = sql;
                    //set work flow transaction
                    cmd.Transaction = GetWorkFlowTransaction(GetClientInfo(ClientInfoType.LoginDB).ToString());
                    //
                    try
                    {
                        cmd.ExecuteNonQuery();
                        return new object[] { 0, null };
                    }
                    catch (Exception e)
                    {
                        return new object[] { 1, e.Message };
                    }
                }
                finally
                {
                    ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
                }
            }
            else
            {
                string dbname = GetSplitSysDBSD(GetClientInfo(ClientInfoType.LoginDB).ToString());
                if (WorkFlowTransmitterPropagationToken != null)
                {
                    using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope(System.Transactions.TransactionInterop.GetTransactionFromTransmitterPropagationToken(WorkFlowTransmitterPropagationToken)))
                    {
                        using (IDbConnection nwindConn = DbConnectionSet.GetDbConn(dbname, DeveloperID).CreateConnection())
                        {
                            if (nwindConn.State != ConnectionState.Open)
                            {
                                nwindConn.Open();
                            }
                            string sql = (string)objParam[0];
                            InfoCommand cmd = new InfoCommand(ClientInfo);
                            cmd.Connection = nwindConn;
                            cmd.CommandText = sql;

                            try
                            {
                                var dbHelper = DbHelperFactory.CreateDbHelper();
                                if (dbHelper != null)
                                {
                                    dbHelper.ExecuteNonQuery(cmd);
                                }
                                else
                                {
                                    cmd.ExecuteNonQuery();
                                }
                                ts.Complete();
                                return new object[] { 0, null };
                            }
                            catch (Exception e)
                            {
                                return new object[] { 1, e.Message };
                            }
                        }
                    }
                }
                else
                {
                    using (IDbConnection nwindConn = DbConnectionSet.GetDbConn(dbname, DeveloperID).CreateConnection())
                    {
                        if (nwindConn.State != ConnectionState.Open)
                        {
                            nwindConn.Open();
                        }

                        string sql = (string)objParam[0];
                        InfoCommand cmd = new InfoCommand(ClientInfo);
                        cmd.Connection = nwindConn;
                        cmd.CommandText = sql;

                        try
                        {
                            var dbHelper = DbHelperFactory.CreateDbHelper();
                            if (dbHelper != null)
                            {
                                dbHelper.ExecuteNonQuery(cmd);
                            }
                            else
                            {
                                cmd.ExecuteNonQuery();
                            }
                            return new object[] { 0, null };
                        }
                        catch (Exception e)
                        {
                            return new object[] { 1, e.Message };
                        }
//.........这里部分代码省略.........

        public object UpdateADUsers(object[] objParam)
        {
            String[] strAdUsers = objParam[0].ToString().Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            object[] myRet = (object[])GetADUsers(null);
            ArrayList alADUsers = myRet[1] as ArrayList;

            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, false);
            try
            {
                foreach (String strADUser in strAdUsers)
                {
                    foreach (ADUser user in alADUsers)
                    {
                        if (strADUser == user.ID)
                        {
                            InfoCommand cmd = new InfoCommand(ClientInfo);
                            cmd.Connection = nwindConn;
                            if (ct == ClientType.ctMsSql || ct == ClientType.ctMySql)
                            {
                                cmd.CommandText = "DELETE FROM USERS WHERE [email protected]";
                                IDbDataParameter idpUSERID = cmd.CreateParameter();
                                idpUSERID.ParameterName = "@USERID";
                                idpUSERID.Value = user.ID;
                                cmd.Parameters.Add(idpUSERID);
                            }
                            else if (ct == ClientType.ctOleDB || ct == ClientType.ctSybase || ct == ClientType.ctODBC || ct == ClientType.ctInformix)
                            {
                                cmd.CommandText = "DELETE FROM USERS WHERE USERID=?";
                                IDbDataParameter idpUSERID = cmd.CreateParameter();
                                idpUSERID.ParameterName = "?";
                                idpUSERID.Value = user.ID;
                                cmd.Parameters.Add(idpUSERID);
                            }
                            else if (ct == ClientType.ctOracle)
                            {
                                cmd.CommandText = "DELETE FROM USERS WHERE USERID=:USERID";
                                IDbDataParameter idpUSERID = cmd.CreateParameter();
                                idpUSERID.ParameterName = ":USERID";
                                idpUSERID.Value = user.ID;
                                cmd.Parameters.Add(idpUSERID);
                            }
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            if (ct == ClientType.ctMsSql || ct == ClientType.ctMySql)
                            {
                                cmd.CommandText = "INSERT INTO USERS (USERID, USERNAME, DESCRIPTION, AUTOLOGIN, EMAIL, MSAD, CREATEDATE) " +
                                    "VALUES (@USERID, @USERNAME, @DESCRIPTION, 'S', @EMAIL, 'Y', @CREATEDATE)";
                                IDbDataParameter idpUSERID = cmd.CreateParameter();
                                idpUSERID.ParameterName = "@USERID";
                                idpUSERID.Value = user.ID;

                                IDbDataParameter idpUSERNAME = cmd.CreateParameter();
                                idpUSERNAME.ParameterName = "@USERNAME";
                                idpUSERNAME.Value = user.Name;

                                IDbDataParameter idpDESCRIPTION = cmd.CreateParameter();
                                idpDESCRIPTION.ParameterName = "@DESCRIPTION";
                                idpDESCRIPTION.Value = user.Description;

                                IDbDataParameter idpEMAIL = cmd.CreateParameter();
                                idpEMAIL.ParameterName = "@EMAIL";
                                idpEMAIL.Value = user.Email;

                                IDbDataParameter idpCREATEDATE = cmd.CreateParameter();
                                idpCREATEDATE.ParameterName = "@CREATEDATE";
                                idpCREATEDATE.Value = DateTime.Today.ToString("yyyyMMdd");

                                cmd.Parameters.Add(idpUSERID);
                                cmd.Parameters.Add(idpUSERNAME);
                                cmd.Parameters.Add(idpDESCRIPTION);
                                cmd.Parameters.Add(idpEMAIL);
                                cmd.Parameters.Add(idpCREATEDATE);
                            }
                            else if (ct == ClientType.ctOleDB || ct == ClientType.ctSybase || ct == ClientType.ctODBC || ct == ClientType.ctInformix)
                            {
                                cmd.CommandText = "INSERT INTO USERS (USERID, USERNAME, DESCRIPTION, AUTOLOGIN, EMAIL, MSAD, CREATEDATE) " +
                                    "VALUES (?, ?, ?, 'S', ?, 'Y', ?)";
                                IDbDataParameter idpUSERID = cmd.CreateParameter();
                                idpUSERID.ParameterName = "?";
                                idpUSERID.Value = user.ID;

                                IDbDataParameter idpUSERNAME = cmd.CreateParameter();
                                idpUSERNAME.ParameterName = "?";
                                idpUSERNAME.Value = user.Name;

                                IDbDataParameter idpDESCRIPTION = cmd.CreateParameter();
                                idpDESCRIPTION.ParameterName = "?";
                                idpDESCRIPTION.Value = user.Description;

                                IDbDataParameter idpEMAIL = cmd.CreateParameter();
                                idpEMAIL.ParameterName = "?";
                                idpEMAIL.Value = user.Email;

                                IDbDataParameter idpCREATEDATE = cmd.CreateParameter();
                                idpCREATEDATE.ParameterName = "?";
                                idpCREATEDATE.Value = DateTime.Today.ToString("yyyyMMdd");

                                cmd.Parameters.Add(idpUSERID);
//.........这里部分代码省略.........

        public object UpdateMenuTable(object[] objParam)
        {
            string[] itemType = ((String)objParam[0]).Split(';');
            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                string strSql = "update MENUTABLE set ITEMTYPE = '" + itemType[1] + "' where ITEMTYPE = '" + itemType[0] + "'";

                //为了区分不同的数据库 by Rei
                InfoCommand cmd = new InfoCommand(ClientInfo);

                cmd.CommandText = strSql;
                cmd.Connection = nwindConn;
                cmd.ExecuteNonQuery();
                return new object[] { 0 };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        //这个方法效率太低
        public object InsertToMenu(object[] objParam)
        {
            for (int i = 0; i < objParam.Length; i++)
            {
                string Menu = (String)objParam[i];
                if (Menu == ";;" || Menu == "" || Menu == null)
                    continue;
                string[] Insert = Menu.Split(';');
                ClientType ct = ClientType.ctMsSql;
                IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
                try
                {
                    string strSql = "insert into MENUTABLECONTROL (MENUID, CONTROLNAME , DESCRIPTION, TYPE) values ('" + Insert[0] + "', '" + Insert[1] + "', '" + Insert[2] + "', '" + Insert[3] + "')";

                    //为了区分不同的数据库 by Rei
                    InfoCommand cmd = new InfoCommand(ClientInfo);

                    cmd.CommandText = strSql;
                    cmd.Connection = nwindConn;
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
                }
            }
            return new object[] { 0 };
        }

//.........这里部分代码省略.........
                    paramErrStack = new OdbcParameter("@ErrStack", OdbcType.VarChar, 5000);
                    paramErrDescrip = new OdbcParameter("@ErrDescrip", OdbcType.VarChar, 300);
                    paramErrDate = new OdbcParameter("@ErrDate", OdbcType.Date);
                    paramErrScreen = new OdbcParameter("@ErrScreen", OdbcType.Binary);
                    paramStatus = new OdbcParameter("@Status", OdbcType.VarChar, 2);
                }
                else if (conn is OracleConnection)
                {
                    sQL = "insert into SYSERRLOG(USERID, MODULENAME, ERRMESSAGE, ERRSTACK, ERRDESCRIP, ERRDATE, ERRSCREEN, STATUS)" +
                            "values(:UserId, :ModuleName, :ErrMessage, :ErrStack, :ErrDescrip, :ErrDate, :ErrScreen, :Status)";
                    paramUserId = new OracleParameter(":UserId", OracleType.VarChar, 20);
                    paramMoudleName = new OracleParameter(":ModuleName", OracleType.VarChar, 30);
                    paramErrMessage = new OracleParameter(":ErrMessage", OracleType.VarChar, 300);
                    paramErrStack = new OracleParameter(":ErrStack", OracleType.VarChar, 5000);
                    paramErrDescrip = new OracleParameter(":ErrDescrip", OracleType.VarChar, 300);
                    paramErrDate = new OracleParameter(":ErrDate", OracleType.DateTime);
                    paramErrScreen = new OracleParameter(":ErrScreen", OracleType.Blob, (objParam[6] as byte[]).Length, ParameterDirection.Input, false,
                                                                        0, 0, null, DataRowVersion.Current, objParam[6]);
                    paramStatus = new OracleParameter(":Status", OracleType.VarChar, 2);
                }
                else if (conn is SqlConnection)
                {
                    sQL = "insert into SYSERRLOG(USERID, MODULENAME, ERRMESSAGE, ERRSTACK, ERRDESCRIP, ERRDATE, ERRSCREEN, STATUS)" +
                            "values(@UserId, @ModuleName, @ErrMessage, @ErrStack, @ErrDescrip, @ErrDate, @ErrScreen, @Status)";

                    paramUserId = new SqlParameter("@UserId", SqlDbType.VarChar, 20);
                    paramMoudleName = new SqlParameter("@ModuleName", SqlDbType.VarChar, 30);
                    paramErrMessage = new SqlParameter("@ErrMessage", SqlDbType.VarChar, 300);
                    paramErrStack = new SqlParameter("@ErrStack", SqlDbType.VarChar, 5000);
                    paramErrDescrip = new SqlParameter("@ErrDescrip", SqlDbType.VarChar, 300);
                    paramErrDate = new SqlParameter("@ErrDate", SqlDbType.DateTime);
                    paramErrScreen = new SqlParameter("@ErrScreen", SqlDbType.Binary);
                    paramStatus = new SqlParameter("@Status", SqlDbType.VarChar, 2);
                }
            #if MySql
                else if (conn is MySqlConnection)
                {
                    paramUserId = new MySqlParameter("@UserId", MySqlDbType.VarChar, 20);
                    paramMoudleName = new MySqlParameter("@ModuleName", MySqlDbType.VarChar, 30);
                    paramErrMessage = new MySqlParameter("@ErrMessage", MySqlDbType.VarChar, 300);
                    paramErrStack = new MySqlParameter("@ErrStack", MySqlDbType.VarChar, 5000);
                    paramErrDescrip = new MySqlParameter("@ErrDescrip", MySqlDbType.VarChar, 300);
                    paramErrDate = new MySqlParameter("@ErrDate", MySqlDbType.Datetime);
                    paramErrScreen = new MySqlParameter("@ErrScreen", MySqlDbType.Binary);
                    paramStatus = new MySqlParameter("@Status", MySqlDbType.VarChar, 2);
                }
            #endif
            #if Informix
            else if (conn is IBM.Data.Informix.IfxConnection)
            {
                sQL = "insert into SYSERRLOG(USERID, MODULENAME, ERRMESSAGE, ERRSTACK, ERRDESCRIP, ERRDATE, ERRSCREEN, STATUS)" +
                            "values(?, ?, ?, ?, ?, ?, ?, ?)";

                paramUserId = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.VarChar, 20);
                paramMoudleName = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.VarChar, 30);
                paramErrMessage = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.VarChar, 300);
                paramErrStack = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.Text, 5000);
                paramErrDescrip = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.VarChar, 300);
                paramErrDate = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.DateTime);
                paramErrScreen = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.Blob);
                paramStatus = new IBM.Data.Informix.IfxParameter("?", IBM.Data.Informix.IfxType.VarChar, 2);
            }
            #endif

                command.CommandText = sQL;
                paramUserId.Value = (objParam[0] == null ? "" : objParam[0]);
                paramMoudleName.Value = (objParam[1] == null ? "" : objParam[1]); ;
                paramErrMessage.Value = (objParam[2] == null ? "" : objParam[2]); ;
                paramErrStack.Value = (objParam[3] == null ? "" : objParam[3]); ;
                paramErrDescrip.Value = (objParam[4] == null ? "" : objParam[4]); ;
                paramErrDate.Value = (objParam[5] == null ? DateTime.Now : objParam[5]); ;
                paramErrScreen.Value = (objParam[6] == null ? "" : objParam[6]); ;
                paramStatus.Value = (objParam[7] == null ? "" : objParam[7]); ;

                command.Parameters.Add(paramUserId);
                command.Parameters.Add(paramMoudleName);
                command.Parameters.Add(paramErrMessage);
                command.Parameters.Add(paramErrStack);
                command.Parameters.Add(paramErrDescrip);
                command.Parameters.Add(paramErrDate);
                command.Parameters.Add(paramErrScreen);
                command.Parameters.Add(paramStatus);

                Int32 i = command.ExecuteNonQuery();
                if (i != 1)
                {
                    return new object[] { 1 };
                }
                else
                {
                    return new object[] { 0 };
                }
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), conn, true);
            }

            #endregion
        }

        public object[] AnyQuerySave(object[] param)
        {
            String userID = GetClientInfo(ClientInfoType.LoginUser).ToString();
            String queryID = param[0].ToString();
            String templateID = param[1].ToString();
            String xmlText = param[2].ToString();
            String tableName = param[3].ToString();

            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                String sql = "DELETE FROM SYS_ANYQUERY WHERE USERID='" + userID + "' AND QUERYID='" + queryID + "' AND TEMPLATEID='" + templateID + "'";
                InfoCommand myCommand = new InfoCommand(ClientInfo);

                myCommand.Connection = nwindConn;
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();

                String dt = DateTime.Now.ToShortDateString() + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second;
                if (ct == ClientType.ctMsSql)
                {
                    sql = "INSERT INTO SYS_ANYQUERY (QUERYID, USERID, TEMPLATEID, TABLENAME, LASTDATE, CONTENT) VALUES ('" + queryID + "', '" + userID + "', '" + templateID + "', '" + tableName + "', '" + dt + "', @CONTENT)";
                    SqlParameter paramContent = new SqlParameter();
                    paramContent.ParameterName = "@CONTENT";
                    paramContent.SqlDbType = SqlDbType.Text;
                    paramContent.Value = xmlText;
                    myCommand.CommandText = sql;
                    myCommand.Parameters.Add(paramContent);
                }
                else if (ct == ClientType.ctOracle)
                {
                    sql = "INSERT INTO SYS_ANYQUERY (QUERYID, USERID, TEMPLATEID, TABLENAME, LASTDATE, CONTENT) VALUES ('" + queryID + "', '" + userID + "', '" + templateID + "', '" + tableName + "', to_date('" + dt + "', 'yyyy/mm/dd hh24:mi:ss'), :CONTENT)";
                    OracleParameter paramContent = new OracleParameter();
                    paramContent.ParameterName = ":CONTENT";
                    paramContent.OracleType = OracleType.Blob;
                    byte[] byteArray = System.Text.Encoding.Default.GetBytes(xmlText);
                    paramContent.Value = byteArray;
                    myCommand.CommandText = sql;
                    myCommand.Parameters.Add(paramContent);
                }
                else if (ct == ClientType.ctODBC)
                {
                    sql = "INSERT INTO SYS_ANYQUERY (QUERYID, USERID, TEMPLATEID, TABLENAME, LASTDATE, CONTENT) VALUES ('" + queryID + "', '" + userID + "', '" + templateID + "', '" + tableName + "', '" + dt + "', ?)";
                    OdbcParameter paramContent = new OdbcParameter();
                    paramContent.ParameterName = "@CONTENT";
                    paramContent.OdbcType = OdbcType.NText;
                    paramContent.Value = xmlText;
                    myCommand.CommandText = sql;
                    myCommand.Parameters.Add(paramContent);
                }
                else if (ct == ClientType.ctOleDB)
                {
                    sql = "INSERT INTO SYS_ANYQUERY (QUERYID, USERID, TEMPLATEID, TABLENAME, LASTDATE, CONTENT) VALUES ('" + queryID + "', '" + userID + "', '" + templateID + "', '" + tableName + "', '" + dt + "', ?)";
                    OleDbParameter paramContent = new OleDbParameter();
                    paramContent.ParameterName = "@CONTENT";
                    paramContent.OleDbType = OleDbType.Binary;
                    paramContent.Value = xmlText;
                    myCommand.CommandText = sql;
                    myCommand.Parameters.Add(paramContent);
                }
            #if MySQL
                else if (ct == ClientType.ctMySql)
                {
                    sql = "INSERT INTO SYS_ANYQUERY (QUERYID, USERID, TEMPLATEID, TABLENAME, LASTDATE, CONTENT) VALUES ('" + queryID + "', '" + userID + "', '" + templateID + "', '" + tableName + "', '" + dt + "', @CONTENT)";
                    SqlParameter paramContent = new SqlParameter();
                    paramContent.ParameterName = "@CONTENT";
                    paramContent.SqlDbType = SqlDbType.Text;
                    paramContent.Value = xmlText;
                    myCommand.CommandText = sql;
                    myCommand.Parameters.Add(paramContent);
                }
            #endif
            #if Informix
                else if (ct == ClientType.ctInformix)
                {
                    sql = "INSERT INTO SYS_ANYQUERY (QUERYID, USERID, TEMPLATEID, TABLENAME, LASTDATE, CONTENT) VALUES ('" + queryID + "', '" + userID + "', '" + templateID + "', '" + tableName + "', '" + dt + "', ?)";
                    IBM.Data.Informix.IfxParameter paramContent = new IBM.Data.Informix.IfxParameter();
                    paramContent.ParameterName = "?";
                    paramContent.IfxType = IBM.Data.Informix.IfxType.Text;
                    paramContent.Value = xmlText;
                    myCommand.CommandText = sql;
                    myCommand.Parameters.Add(paramContent);
                }
            #endif
                myCommand.ExecuteNonQuery();

                return new object[] { 0 };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object InsertToGroup(object[] objParam)
        {
            string Group = (String)objParam[0];
            string[] insert = Group.Split(';');
            if (insert[0] == null || insert[0] == "")
                return new object[] { 0 };
            else
            {
                ClientType ct = ClientType.ctMsSql;
                IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
                try
                {
                    string strSql = "insert into GROUPMENUCONTROL (GROUPID, MENUID, CONTROLNAME, TYPE, ENABLED, VISIBLE, ALLOWADD, ALLOWUPDATE, ALLOWDELETE, ALLOWPRINT)" +
                                    " values ( '" + insert[0] + "',  '" + insert[1] + "',  '" + insert[2] + "', '" + insert[3] +
                                    "', '" + insert[4] + "', '" + insert[5] + "', '" + insert[6] + "', '" + insert[7] + "', '" + insert[8] + "', '" + insert[9] + "')";

                    //为了区分不同的数据库 by Rei
                    InfoCommand cmd = new InfoCommand(ClientInfo);

                    cmd.CommandText = strSql;
                    cmd.Connection = nwindConn;
                    cmd.ExecuteNonQuery();
                    return new object[] { 0 };
                }
                finally
                {
                    ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
                }
            }
        }

        public object[] GetMessage(object[] objParams)
        {
            string sNow = FormatDateTime(DateTime.Now);
            string sLoginUser = GetClientInfo(ClientInfoType.LoginUser).ToString();

            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {

                //为了区分不同的数据库 by Rei
                InfoCommand myCommand = new InfoCommand(ClientInfo);

                myCommand.Connection = nwindConn;
                string strSql = "";

                strSql = string.Format("select * from SYS_MESSENGER where STATUS = 'S' and USERID = '{0}'", sLoginUser);
                myCommand.CommandText = strSql;
                IDataAdapter adpater = DBUtils.CreateDbDataAdapter(myCommand);

                DataSet dsMessage = new DataSet();
                DataTable dt = new DataTable("Message");
                (adpater as DbDataAdapter).Fill(dt);
                dsMessage.Tables.Add(dt);

                strSql = string.Format("Update SYS_MESSENGER SET STATUS = 'R' where STATUS = 'S' and USERID = '{0}'", sLoginUser);
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();

                return new object[] { 0, dsMessage };

            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object GetFavorMenuID(object[] objParam)
        {
            String userID = objParam[0].ToString();
            String itemType = objParam[1].ToString();
            ArrayList menuID = objParam[2] as ArrayList;
            ArrayList caption = objParam[3] as ArrayList;
            String groupName = objParam[4].ToString();

            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                InfoCommand cmd = new InfoCommand(ClientInfo);
                cmd.Connection = nwindConn;

                String group = "";
                if (groupName == "")
                {
                    if (nwindConn is SqlConnection)
                    {
                        group = "GROUPNAME='" + group + "'";
                    }
                    else if (nwindConn is OracleConnection)
                    {
                        group = "GROUPNAME is null";
                    }
                    else if (nwindConn is OdbcConnection)
                    {
                        group = "GROUPNAME='" + group + "'";
                    }
                    else if (nwindConn is OleDbConnection)
                    {
                        group = "GROUPNAME='" + group + "'";
                    }
                    else if (nwindConn.GetType().Name == "MySqlConnection")
                    {
                        group = "GROUPNAME='" + group + "'";
                    }
                    else if (nwindConn.GetType().Name == "IfxConnection")
                    {
                        group = "GROUPNAME='" + group + "'";
                    }
                }
                else
                {
                    group = "GROUPNAME='" + groupName + "'";
                }
                String strSql = "delete MENUFAVOR where USERID='" + userID + "' and ITEMTYPE='" + itemType + "' AND " + group;
                cmd.CommandText = strSql;
                cmd.Connection = nwindConn;
                cmd.ExecuteNonQuery();

                for (int i = 0; i < menuID.Count; i++)
                {
                    strSql = "insert into MENUFAVOR (MENUID, CAPTION, USERID, ITEMTYPE, GROUPNAME) values ('" + menuID[i] + "', '" + caption[i] + "', '" + userID + "', '" + itemType + "', '" + groupName + "')";
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                }
                return new object[] { 0 };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object[] AnyQueryDeleteFile(object[] param)
        {
            ClientType ct = ClientType.ctMsSql;
            String queryID = param[0].ToString();
            String fileName = param[1].ToString();
            String userid = GetClientInfo(ClientInfoType.LoginUser).ToString();
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                String sql = "DELETE from SYS_ANYQUERY where USERID = '" + userid + "' AND QUERYID='" + queryID + "' AND TEMPLATEID='" + fileName + "'";
                InfoCommand myCommand = new InfoCommand(ClientInfo);

                myCommand.Connection = nwindConn;
                myCommand.CommandText = sql;

                myCommand.ExecuteNonQuery();
                return new object[] { 0 };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }

        public object SetUsers(object[] objParam)
        {
            string strMenuID = (String)objParam[0];
            ClientType ct = ClientType.ctMsSql;
            IDbConnection nwindConn = AllocateConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), ref ct, true);
            try
            {
                ArrayList GroupID = (ArrayList)objParam[1];

                //delete groups which will possibly be repeated later
                string strDel = "delete from USERMENUS where MENUID = '" + strMenuID + "'";

                //为了区分不同的数据库 by Rei
                InfoCommand cmd = new InfoCommand(ClientInfo);

                cmd.Connection = nwindConn;
                cmd.CommandText = strDel;
                cmd.ExecuteNonQuery();

                //modify by lily 2011/5/26 刪除聯動的MenuControl的table內容
                string lst = "";
                for (int i = 0; i < GroupID.Count; i++)
                {
                    lst = lst + "','" + GroupID[i].ToString();
                }
                if (lst.Length > 3)
                {
                    strDel = "delete from USERMENUCONTROL where MENUID='" + strMenuID + "' and USERID not in ('" + lst + "')";
                }
                cmd.CommandText = strDel;
                cmd.ExecuteNonQuery();

                //add new groups
                for (int i = 0; i < GroupID.Count; i++)
                {
                    string strInsert = "insert into USERMENUS (USERID, MENUID) values ('"
                        + GroupID[i].ToString() + "', '" + strMenuID + "')";

                    //为了区分不同的数据库 by Rei
                    InfoCommand InsertCmd = new InfoCommand(ClientInfo);

                    InsertCmd.Connection = nwindConn;
                    InsertCmd.CommandText = strInsert;
                    InsertCmd.ExecuteNonQuery();
                }

                return new object[] { 0, null };
            }
            finally
            {
                ReleaseConnection(GetClientInfo(ClientInfoType.LoginDB).ToString(), nwindConn, true);
            }
        }