// 根据表空间名字获得表空间的物理文件名
// 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);
}
// 获得 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);
}
// 创建一个适合于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);
}
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 = "";
}
}
}
// 根据表空间名字获得表空间的物理文件名
// 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;
}
// 创建一个适合于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;
}
// 获得 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;
}
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 = "";
}
}
}