Esempio n. 1
0
        // 根据表空间名字获得表空间的物理文件名
        // return:
        //      -1  出错
        //      0   成功。此时 strTableSpaceFileName 依然可能为空
        static int GetTableSpaceFileName(
            OracleSqlServerInfo info,
            string strTableSpaceName,
            out string strTableSpaceFileName,
            out string strError)
        {
            strError = "";
            strTableSpaceFileName = "";

            string strConnection = @"Persist Security Info=False;"
                                   + "User ID=" + info.SqlUserName + ";" //帐户和密码
                                   + "Password="******";"
                                   + "Data Source=" + info.ServerName + ";"
                                   + "Connect Timeout=30";

            try
            {
                using (OracleConnection connection = new OracleConnection(strConnection))
                {
                    connection.Open();

                    /*
                     * SQL> select tablespace_name, file_name from dba_data_files where tablespace_name
                     * = 'TS_DP2KERNEL_ORACLE';
                     * */

                    string strCommand = "";

                    strCommand = "select file_name from dba_data_files where tablespace_name = '" + strTableSpaceName.ToUpper() + "'";
                    try
                    {
                        using (OracleCommand command = new OracleCommand(strCommand, connection))
                        {
                            OracleDataReader reader = command.ExecuteReader();
                            while (reader.Read() == true)
                            {
                                strTableSpaceFileName = reader.GetString(0);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        strError = "执行命令 " + strCommand + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                        return(-1);
                    }
                }
            }
            catch (OracleException sqlEx)
            {
                strError = "出错:" + sqlEx.Message + "。";
                int nError = sqlEx.ErrorCode;
                return(-1);
            }
            catch (Exception ex)
            {
                strError = "出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return(-1);
            }

            return(0);
        }
Esempio n. 2
0
        // 获得 SQL Server 信息
        // return:
        //      -1  出错
        //      0   放弃
        //      1   成功
        int GetSqlServerInfo(
            string strSqlServerName,
            out OracleSqlServerInfo info,
            out string strError)
        {
            strError = "";
            int nRet = 0;

            info = new OracleSqlServerInfo();

            SystemLoginDialog dlg = new SystemLoginDialog();

            GuiUtil.AutoSetDefaultFont(dlg);
            dlg.SqlServerName = strSqlServerName;
            dlg.StartPosition = FormStartPosition.CenterScreen;

REDO_INPUT:
            dlg.ShowDialog(this);

            if (dlg.DialogResult != DialogResult.OK)
            {
                return(0);
            }

            info.ServerName      = strSqlServerName;
            info.SqlUserName     = dlg.SqlUserName;
            info.SqlUserPassword = dlg.SqlPassword;

            string strConnection = @"Persist Security Info=False;"
                                   + "User ID=" + info.SqlUserName + ";" //帐户和密码
                                   + "Password="******";"
                                   + "Data Source=" + strSqlServerName + ";"
                                   + "Connect Timeout=30";

            OracleConnection connection = null;

            try
            {
                connection = new OracleConnection(strConnection);
            }
            catch (Exception ex)
            {
                strError = "建立连接时出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return(-1);
            }

            try
            {
                connection.Open();
            }
            catch (OracleException sqlEx)
            {
                // ex.Number == 12154
                // ORA-12154: TNS: 无法解析指定的连接标识符
                strError = "连接SQL服务器出错:" + sqlEx.Message + "。";
                int nError = sqlEx.ErrorCode;
                MessageBox.Show(this, strError);
                dlg.Comment = "登录错误: " + strError + "\r\n请重新登录";
                goto REDO_INPUT;
                return(-1);
            }
            catch (Exception ex)
            {
                strError = "连接SQL服务器出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return(-1);
            }

            try
            {
                string        strCommand = "";
                OracleCommand command    = null;

                strCommand = "select username,default_tablespace from dba_users";
                command    = new OracleCommand(strCommand,
                                               connection);
                try
                {
                    OracleDataReader reader = command.ExecuteReader();
                    while (reader.Read() == true)
                    {
                        DbaUser user = new DbaUser();
                        user.Name       = reader.GetString(0);
                        user.TableSpace = reader.GetString(1);
                        info.DbaUsers.Add(user);
                    }
                }
                catch (Exception ex)
                {
                    strError = "执行命令 " + strCommand + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                    return(-1);
                }
            }
            finally
            {
                connection.Close();
            }

            return(1);
        }
Esempio n. 3
0
        // 创建一个适合于dpKernel的 Oracle 数据库用户
        // return:
        //      -1  出错
        //      0   成功
        //      1   原来已经存在,且不允许删除
        static int CreateLogin(
            OracleSqlServerInfo info,
            string strLoginName,
            string strLoginPassword,
            string strTableSpaceFileName,
            out string strError)
        {
            strError = "";
            int nRet = 0;

            string strConnection = @"Persist Security Info=False;"
                                   + "User ID=" + info.SqlUserName + ";" //帐户和密码
                                   + "Password="******";"
                                   + "Data Source=" + info.ServerName + ";"
                                   + "Connect Timeout=30";

            try
            {
                using (OracleConnection connection = new OracleConnection(strConnection))
                {
                    connection.Open();


                    // 检查用户是否已经存在
                    {
                        string  strCommand = "";
                        DbaUser user       = null;

                        strCommand = "select username,default_tablespace from dba_users where username='******'";
                        try
                        {
                            using (OracleCommand command = new OracleCommand(strCommand, connection))
                            {
                                OracleDataReader reader = command.ExecuteReader();
                                while (reader.Read() == true)
                                {
                                    user            = new DbaUser();
                                    user.Name       = reader.GetString(0);
                                    user.TableSpace = reader.GetString(1);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            strError = "执行命令 " + strCommand + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                            return(-1);
                        }

                        if (user != null)
                        {
                            // 用户已经存在
                            // 需要验证密码是否正确?

                            // 检查表空间名,不能是 SYSTEM 或者 SYSAUX
                            if (user.TableSpace.ToUpper() == "SYSTEM" ||
                                user.TableSpace.ToUpper() == "SYSAUX")
                            {
                                strError = "用户 '" + user.Name + "' 的表空间为 " + user.TableSpace + ",不适合用作 dp2kernel 用户。请重新指定或创建一个用户";
                                return(-1);
                            }
                            return(1);
                        }
                    }

                    // 创建用户
                    {
                        // 先确保表空间物理文件不会和已有文件冲突
                        if (string.IsNullOrEmpty(strTableSpaceFileName) == true)
                        {
                            strError = "尚未指定表空间物理文件名";
                            return(-1);
                        }

                        if (File.Exists(strTableSpaceFileName) == true)
                        {
                            strError = "指定的表空间物理文件 '" + strTableSpaceFileName + "' 已经存在。创建用户失败。请重新指定一个表空间文件名";
                            return(-1);
                        }

                        // 确保表空间文件名中用到的路径已经创建好目录
                        PathUtil.TryCreateDir(Path.GetDirectoryName(strTableSpaceFileName));

                        string strCommand = "";

                        string strTableSpaceName = "ts_" + strLoginName;

                        strCommand = "create tablespace " + strTableSpaceName + " datafile '" + strTableSpaceFileName + "' size 100m autoextend on ; "
                                     + "create user " + strLoginName + " identified by " + strLoginPassword + " default tablespace " + strTableSpaceName + "; "
                                     + "grant dba, connect to " + strLoginName;
                        string[] lines = strCommand.Split(new char[] { ';' });
                        foreach (string line in lines)
                        {
                            string strLine = line.Trim();
                            if (string.IsNullOrEmpty(strLine) == true)
                            {
                                continue;
                            }

                            try
                            {
                                using (OracleCommand command = new OracleCommand(strLine, connection))
                                {
                                    nRet = command.ExecuteNonQuery();
                                }
                            }
                            catch (Exception ex)
                            {
                                strError = "执行命令 " + strLine + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                                return(-1);
                            }
                        }
                    }
                }
            }
            catch (OracleException sqlEx)
            {
                strError = "出错:" + sqlEx.Message + "。";
                int nError = sqlEx.ErrorCode;
                return(-1);
            }
            catch (Exception ex)
            {
                strError = "出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return(-1);
            }

            return(0);
        }
Esempio n. 4
0
        private void tabControl_main_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.tabControl_main.SelectedTab == this.tabPage_createLogin)
            {
                this._floatingMessage.Text = "正在访问 Oracle 数据库服务器 ...";
                try
                {
                    OracleSqlServerInfo info = null;

                    string strError = "";
                    // 获得 SQL Server 信息
                    // return:
                    //      -1  出错
                    //      0   放弃
                    //      1   成功
                    int nRet = GetSqlServerInfo(
                        this.textBox_sqlServerName.Text,
                        out info,
                        out strError);
                    if (nRet == -1)
                    {
                        MessageBox.Show(this, strError);
                        return;
                    }

                    if (nRet == 0)
                    {
                        return;
                    }

                    this._sqlServerInfo = info;

                    textBox_loginName_TextChanged(this, new EventArgs());

                    this.button_copySqlServerInfo.Enabled = true;

                    {
                        if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
                        {
                            this.textBox_loginName.Text = this.textBox_instanceName.Text;
                        }
                        if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
                        {
                            this.textBox_loginName.Text = "dp2kernel";  // 缺省的名字
                        }
                        // 设置缺省的表空间文件名
                        if (string.IsNullOrEmpty(this.textBox_tableSpaceFile.Text) == true)
                        {
                            string strFileName = this.textBox_instanceName.Text.Replace(" ", "");
                            if (string.IsNullOrEmpty(strFileName) == true)
                            {
                                strFileName = "dp2kernel";
                            }
                            this.textBox_tableSpaceFile.Text = "c:\\oracle_data\\" + strFileName + ".dbf";
                        }
                    }

                    this.DebugInfo = DateTime.Now.ToString() + "\r\n" + info.GetSummary() + "\r\n\r\n";
                }
                finally
                {
                    this._floatingMessage.Text = "";
                }
            }
        }
Esempio n. 5
0
        // 根据表空间名字获得表空间的物理文件名
        // return:
        //      -1  出错
        //      0   成功。此时 strTableSpaceFileName 依然可能为空
        static int GetTableSpaceFileName(
    OracleSqlServerInfo info,
    string strTableSpaceName,
    out string strTableSpaceFileName,
    out string strError)
        {
            strError = "";
            strTableSpaceFileName = "";

            string strConnection = @"Persist Security Info=False;"
                + "User ID=" + info.SqlUserName + ";"    //帐户和密码
                + "Password="******";"
                + "Data Source=" + info.ServerName + ";"
                + "Connect Timeout=30";

            try
            {
                using (OracleConnection connection = new OracleConnection(strConnection))
                {
                    connection.Open();

                    /*
 * SQL> select tablespace_name, file_name from dba_data_files where tablespace_name
= 'TS_DP2KERNEL_ORACLE';
                     * */

                    string strCommand = "";

                    strCommand = "select file_name from dba_data_files where tablespace_name = '" + strTableSpaceName.ToUpper() + "'";
                    try
                    {
                        using (OracleCommand command = new OracleCommand(strCommand, connection))
                        {
                            OracleDataReader reader = command.ExecuteReader();
                            while (reader.Read() == true)
                            {
                                strTableSpaceFileName = reader.GetString(0);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        strError = "执行命令 " + strCommand + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                        return -1;
                    }

                }
            }
            catch (OracleException sqlEx)
            {
                strError = "出错:" + sqlEx.Message + "。";
                int nError = sqlEx.ErrorCode;
                return -1;
            }
            catch (Exception ex)
            {
                strError = "出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return -1;
            }

            return 0;
        }
Esempio n. 6
0
        // 创建一个适合于dpKernel的 Oracle 数据库用户
        // return:
        //      -1  出错
        //      0   成功
        //      1   原来已经存在,且不允许删除
        static int CreateLogin(
            OracleSqlServerInfo info,
            string strLoginName,
            string strLoginPassword,
            string strTableSpaceFileName,
            out string strError)
        {
            strError = "";
            int nRet = 0;

            string strConnection = @"Persist Security Info=False;"
                + "User ID=" + info.SqlUserName + ";"    //帐户和密码
                + "Password="******";"
                + "Data Source=" + info.ServerName + ";"
                + "Connect Timeout=30";

            try
            {
                using (OracleConnection connection = new OracleConnection(strConnection))
                {
                    connection.Open();


                    // 检查用户是否已经存在
                    {
                        string strCommand = "";
                        DbaUser user = null;

                        strCommand = "select username,default_tablespace from dba_users where username='******'";
                        try
                        {
                            using (OracleCommand command = new OracleCommand(strCommand, connection))
                            {
                                OracleDataReader reader = command.ExecuteReader();
                                while (reader.Read() == true)
                                {
                                    user = new DbaUser();
                                    user.Name = reader.GetString(0);
                                    user.TableSpace = reader.GetString(1);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            strError = "执行命令 " + strCommand + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                            return -1;
                        }

                        if (user != null)
                        {
                            // 用户已经存在
                            // 需要验证密码是否正确?

                            // 检查表空间名,不能是 SYSTEM 或者 SYSAUX
                            if (user.TableSpace.ToUpper() == "SYSTEM"
                                || user.TableSpace.ToUpper() == "SYSAUX")
                            {
                                strError = "用户 '"+user.Name+"' 的表空间为 "+user.TableSpace+",不适合用作 dp2kernel 用户。请重新指定或创建一个用户";
                                return -1;
                            }
                            return 1;
                        }
                    }

                    // 创建用户
                    {
                        // 先确保表空间物理文件不会和已有文件冲突
                        if (string.IsNullOrEmpty(strTableSpaceFileName) == true)
                        {
                            strError = "尚未指定表空间物理文件名";
                            return -1;
                        }

                        if (File.Exists(strTableSpaceFileName) == true)
                        {
                            strError = "指定的表空间物理文件 '" + strTableSpaceFileName + "' 已经存在。创建用户失败。请重新指定一个表空间文件名";
                            return -1;
                        }

                        // 确保表空间文件名中用到的路径已经创建好目录
                        PathUtil.CreateDirIfNeed(Path.GetDirectoryName(strTableSpaceFileName));

                        string strCommand = "";

                        string strTableSpaceName = "ts_" + strLoginName;

                        strCommand = "create tablespace " + strTableSpaceName + " datafile '" + strTableSpaceFileName + "' size 100m autoextend on ; "
                            + "create user " + strLoginName + " identified by " + strLoginPassword + " default tablespace " + strTableSpaceName + "; "
                            + "grant dba, connect to " + strLoginName;
                        string[] lines = strCommand.Split(new char[] { ';' });
                        foreach (string line in lines)
                        {
                            string strLine = line.Trim();
                            if (string.IsNullOrEmpty(strLine) == true)
                                continue;

                            try
                            {
                                using (OracleCommand command = new OracleCommand(strLine, connection))
                                {
                                    nRet = command.ExecuteNonQuery();
                                }
                            }
                            catch (Exception ex)
                            {
                                strError = "执行命令 " + strLine + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                                return -1;
                            }
                        }
                    }
                }
            }
            catch (OracleException sqlEx)
            {
                strError = "出错:" + sqlEx.Message + "。";
                int nError = sqlEx.ErrorCode;
                return -1;
            }
            catch (Exception ex)
            {
                strError = "出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return -1;
            }

            return 0;
        }
Esempio n. 7
0
        // 获得 SQL Server 信息
        // return:
        //      -1  出错
        //      0   放弃
        //      1   成功
        int GetSqlServerInfo(
            string strSqlServerName,
            out OracleSqlServerInfo info,
            out string strError)
        {
            strError = "";
            int nRet = 0;

            info = new OracleSqlServerInfo();

            SystemLoginDialog dlg = new SystemLoginDialog();
            GuiUtil.AutoSetDefaultFont(dlg);
            dlg.SqlServerName = strSqlServerName;
            dlg.StartPosition = FormStartPosition.CenterScreen;

        REDO_INPUT:
            dlg.ShowDialog(this);

            if (dlg.DialogResult != DialogResult.OK)
                return 0;

            info.ServerName = strSqlServerName;
            info.SqlUserName = dlg.SqlUserName;
            info.SqlUserPassword = dlg.SqlPassword;

            string strConnection = @"Persist Security Info=False;"
                + "User ID=" + info.SqlUserName + ";"    //帐户和密码
                + "Password="******";"
                + "Data Source=" + strSqlServerName + ";"
                + "Connect Timeout=30";

            OracleConnection connection = null;
            try
            {
                connection = new OracleConnection(strConnection);
            }
            catch (Exception ex)
            {
                strError = "建立连接时出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return -1;
            }

            try
            {
                connection.Open();
            }
            catch (OracleException sqlEx)
            {
                // ex.Number == 12154
                // ORA-12154: TNS: 无法解析指定的连接标识符
                strError = "连接SQL服务器出错:" + sqlEx.Message + "。";
                int nError = sqlEx.ErrorCode;
                MessageBox.Show(this, strError);
                dlg.Comment = "登录错误: " + strError + "\r\n请重新登录";
                goto REDO_INPUT;
                return -1;
            }
            catch (Exception ex)
            {
                strError = "连接SQL服务器出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                return -1;
            }

            try
            {
                string strCommand = "";
                OracleCommand command = null;

                strCommand = "select username,default_tablespace from dba_users";
                command = new OracleCommand(strCommand,
                    connection);
                try
                {
                    OracleDataReader reader = command.ExecuteReader();
                    while (reader.Read() == true)
                    {
                        DbaUser user = new DbaUser();
                        user.Name = reader.GetString(0);
                        user.TableSpace = reader.GetString(1);
                        info.DbaUsers.Add(user);
                    }
                }
                catch (Exception ex)
                {
                    strError = "执行命令 " + strCommand + " 出错:" + ex.Message + " 类型:" + ex.GetType().ToString();
                    return -1;
                }
            }
            finally
            {
                connection.Close();
            }

            return 1;
        }
Esempio n. 8
0
        private void tabControl_main_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.tabControl_main.SelectedTab == this.tabPage_createLogin)
            {
                this._floatingMessage.Text = "正在访问 Oracle 数据库服务器 ...";
                try
                {
                    OracleSqlServerInfo info = null;

                    string strError = "";
                    // 获得 SQL Server 信息
                    // return:
                    //      -1  出错
                    //      0   放弃
                    //      1   成功
                    int nRet = GetSqlServerInfo(
                        this.textBox_sqlServerName.Text,
                        out info,
                        out strError);
                    if (nRet == -1)
                    {
                        MessageBox.Show(this, strError);
                        return;
                    }

                    if (nRet == 0)
                        return;

                    this._sqlServerInfo = info;

                    textBox_loginName_TextChanged(this, new EventArgs());

                    this.button_copySqlServerInfo.Enabled = true;

                    {
                        if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
                            this.textBox_loginName.Text = this.textBox_instanceName.Text;
                        if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
                            this.textBox_loginName.Text = "dp2kernel";  // 缺省的名字

                        // 设置缺省的表空间文件名
                        if (string.IsNullOrEmpty(this.textBox_tableSpaceFile.Text) == true)
                        {
                            string strFileName = this.textBox_instanceName.Text.Replace(" ", "");
                            if (string.IsNullOrEmpty(strFileName) == true)
                                strFileName = "dp2kernel";
                            this.textBox_tableSpaceFile.Text = "c:\\oracle_data\\" + strFileName + ".dbf";
                        }
                    }

                    this.DebugInfo = DateTime.Now.ToString() + "\r\n" + info.GetSummary() + "\r\n\r\n";
                }
                finally
                {
                    this._floatingMessage.Text = "";
                }
            }

        }