private void textBox_loginName_TextChanged(object sender, EventArgs e)
{
// loginName 是否已经创建？
if (this._sqlServerInfo != null &&
string.IsNullOrEmpty(this.textBox_loginName.Text) == false)
{
DbaUser user = this._sqlServerInfo.FindUser(this.textBox_loginName.Text);
if (user != null)
{
this.textBox_tableSpaceName.Text = user.TableSpace;
string strTableSpaceFileName = "";
string strError = "";
int nRet = GetTableSpaceFileName(
this._sqlServerInfo,
user.TableSpace,
out strTableSpaceFileName,
out strError);
if (nRet == -1)
{
// MessageBox.Show(this, strError);
// 最好是浮动显示出来
}
else
{
this.textBox_tableSpaceFile.Text = strTableSpaceFileName;
}
}
}
}
// 创建一个适合于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);
}
// 创建一个适合于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);
}
// 获得 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;
}