private string getScriptObj(string objName, string objType)
{
try
{
string script = "";
string orcCommand = "select dbms_metadata.get_ddl('" + objType + "', '" + objName + "', '" + DBName + "') from dual";
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.CommandText = orcCommand;
OracleDataReader oracleDataReader = OracleProvider.GetOracleDataReader(oracleCommand, oracleConnection);
if (oracleDataReader != null && oracleDataReader.HasRows)
{
oracleDataReader.Read();
script = oracleDataReader.GetString(0);
if (objType == "TRIGGER")
{
int index = script.IndexOf("ALTER TRIGGER");
script = script.Substring(0, index);
}
return(script);
}
return(script);
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
return("");
}
}
public static List <string> getListObjKeyName(string tableName, string keyColumn, OracleConnection oracleConnection)
{
List <string> result = new List <string>();
try
{
string sqlCommand = "select distinct keyCol from tblName";
sqlCommand = sqlCommand.Replace("keyCol", keyColumn).Replace("tblName", tableName);
OracleCommand oracleCommand = new OracleCommand(sqlCommand);
OracleDataReader oracleDataReader = OracleProvider.GetOracleDataReader(oracleCommand, oracleConnection);
if (oracleDataReader != null && oracleDataReader.HasRows)
{
while (oracleDataReader.Read())
{
result.Add(oracleDataReader.GetOracleValue(0).ToString());
}
}
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
string msg_log = e.Message;
if (e.Message == "ORA-00942: table or view does not exist")
{
msg_log += " " + tableName;
}
Logger.Logging(msg_log, DateTime.Now.ToString());
}
return(result);
}
private void buttonTestConnect_Click(object sender, EventArgs ev)
{
try
{
OracleProvider oracleProvider = new OracleProvider();
string conString = oracleProvider.BuildConnectionString(comboBoxTNSname.SelectedValue.ToString(), textBoxUser.Text, textBoxPass.Text);
OracleConnection oracConnection = oracleProvider.GetConnection(conString);
if (oracConnection != null)
{
oracleConnection = oracConnection;
DBName = textBoxUser.Text;
labelTestConnect.Text = "SUCCESS!";
labelTestConnect.ForeColor = Color.Green;
char x = '"';
schemaName = x + textBoxUser.Text + x + ".";
}
else
{
labelTestConnect.Text = "FAIL!";
labelTestConnect.ForeColor = Color.Red;
}
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
labelTestConnect.Text = "FAIL!";
labelTestConnect.ForeColor = Color.Red;
}
}
private List <string> getListObject(string objName)
{
try
{
List <string> listObjName = new List <string>();
string orcCommand = "select OBJECT_NAME from dba_objects where owner = :mOwner and OBJECT_TYPE = '" + objName + "'";
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.CommandText = orcCommand;
oracleCommand.Parameters.Add(new OracleParameter("mOwner", DBName));
OracleDataReader oracleDataReader = OracleProvider.GetOracleDataReader(oracleCommand, oracleConnection);
//int x = oracleDataReader.FieldCount;
//string y = oracleDataReader.GetName(0);
//string z = oracleDataReader.GetDataTypeName(0);
if (oracleDataReader != null && oracleDataReader.HasRows)
{
while (oracleDataReader.Read())
{
listObjName.Add(oracleDataReader.GetString(0));
}
}
return(listObjName);
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
return(null);
}
}
public static void GenInsertCommand(string tableName, string keyColumn, OracleConnection oracleConnection, List <string> listPath, ProgressBar progressBar)
{
try
{
List <string> listObjKeyName = getListObjKeyName(tableName, keyColumn, oracleConnection);
foreach (string str in listObjKeyName)
{
string result = "DELETE FROM tblName WHERE kCol = 'okName'; \n";
result = result.Replace("tblName", tableName).Replace("kCol", keyColumn).Replace("okName", str);
string sqlCommand = "select * from tblName where keyCol = 'objKeyName'";
sqlCommand = sqlCommand.Replace("keyCol", keyColumn).Replace("tblName", tableName).Replace("objKeyName", str);
OracleCommand oracleCommand = new OracleCommand(sqlCommand);
OracleDataReader oracleDataReader = OracleProvider.GetOracleDataReader(oracleCommand, oracleConnection);
List <string> headerName = new List <string>();
for (int index = 0; index < oracleDataReader.FieldCount; index++)
{
headerName.Add(oracleDataReader.GetName(index));
}
if (oracleDataReader != null && oracleDataReader.HasRows)
{
while (oracleDataReader.Read())
{
List <string> values = new List <string>();
for (int index = 0; index < oracleDataReader.FieldCount; index++)
{
values.Add(oracleDataReader.GetOracleValue(index).ToString());
}
result += OracleProvider.GenScriptInsert(tableName, headerName, values);
}
}
result += "COMMIT;\n";
string path = getPath(listPath, tableName, str);
File.WriteAllText(path, result, Encoding.UTF8);
progressBar.PerformStep();
}
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
}
}
public static void GenInsertCommandForTransTable(OracleConnection oracleConnection, List <string> listPath, ProgressBar progressBar)
{
string path = "";
foreach (string str in listPath)
{
if (str.Contains("TRANS"))
{
path = str;
break;
}
}
try
{
List <string> listTblTran = new List <string>();
listTblTran.Add("tltx".ToLower());
listTblTran.Add("fldmaster".ToLower());
listTblTran.Add("fldval".ToLower());
listTblTran.Add("appchk".ToLower());
listTblTran.Add("appmap".ToLower());
List <string> listObjKeyName = getListObjKeyName("tltx", "tltxcd", oracleConnection);
foreach (string str in listObjKeyName)
{
string result = "";
foreach (string tblName in listTblTran)
{
result += "DELETE FROM tblName WHERE kCol = 'okName'; \n";
result = result.Replace("tblName", tblName).Replace("okName", str);
if (tblName == "tltx" || tblName == "appchk" || tblName == "appmap")
{
result = result.Replace("kCol", "tltxcd");
}
else
{
result = result.Replace("kCol", "objname");
}
string sqlCommand = "select * from tblName where keyCol = 'objKeyName'";
if (tblName == "tltx" || tblName == "appchk" || tblName == "appmap")
{
sqlCommand = sqlCommand.Replace("keyCol", "tltxcd");
}
else
{
sqlCommand = sqlCommand.Replace("keyCol", "objname");
}
sqlCommand = sqlCommand.Replace("tblName", tblName).Replace("objKeyName", str);
OracleCommand oracleCommand = new OracleCommand(sqlCommand);
OracleDataReader oracleDataReader = OracleProvider.GetOracleDataReader(oracleCommand, oracleConnection);
List <string> headerName = new List <string>();
for (int index = 0; index < oracleDataReader.FieldCount; index++)
{
headerName.Add(oracleDataReader.GetName(index));
}
if (oracleDataReader != null && oracleDataReader.HasRows)
{
while (oracleDataReader.Read())
{
List <string> values = new List <string>();
for (int index = 0; index < oracleDataReader.FieldCount; index++)
{
values.Add(oracleDataReader.GetOracleValue(index).ToString());
}
result += OracleProvider.GenScriptInsert(tblName, headerName, values);
}
}
result += "COMMIT;\n";
}
string pathTemp = path + "\\TRANS_" + str + ".sql";
File.WriteAllText(pathTemp, result, Encoding.UTF8);
progressBar.PerformStep();
}
}catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
}
}
private void buttonExpData_Click(object sender, EventArgs ev)
{
if (MessageBox.Show("The process take many times, Are you sure you want to continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
OracleProvider oracleProvider = new OracleProvider();
string conString = oracleProvider.BuildConnectionString(comboBoxTNSname.SelectedValue.ToString(), textBoxUser.Text, textBoxPass.Text);
OracleConnection oracConnection = oracleProvider.GetConnection(conString);
if (oracleConnection != null)
{
oracleConnection = oracConnection;
DBName = textBoxUser.Text;
}
progressBarExpDt.Maximum = 0;
if (checkBoxExpObj.Checked)
{
if (oracleConnection != null && oracleConnection.State == ConnectionState.Open)
{
// get list view
List <string> listViewName = getListObject("VIEW");
// get list trigger
List <string> listTriggerName = getListObject("TRIGGER");
// get list procedure
List <string> listProcedureName = getListObject("PROCEDURE");
// get list function
List <string> listFunctionName = getListObject("FUNCTION");
// get list package
List <string> listPackageName = getListObject("PACKAGE");
// get list type
List <string> listType = getListObject("TYPE");
progressBarExpDt.Maximum += listViewName.Count + listTriggerName.Count +
listProcedureName.Count + listFunctionName.Count +
+listPackageName.Count + listType.Count;
progressBarExpDt.Step = 1;
// gen view
genObject(listViewName, "VIEW", progressBarExpDt);
//gen trigger
genObject(listTriggerName, "TRIGGER", progressBarExpDt);
// gen procedure
genObject(listProcedureName, "PROCEDURE", progressBarExpDt);
// gen function
genObject(listFunctionName, "FUNCTION", progressBarExpDt);
// gen package
genObject(listPackageName, "PACKAGE", progressBarExpDt);
// gen type
genObject(listType, "TYPE", progressBarExpDt);
progressBarExpDt.Value = 0;
}
}
if (checkBoxExpTbl.Checked)
{
string[] lsTable = getListTableNameFromFile();
int progressSize = 0;
for (int index = 0; index < lsTable.Length; index++)
{
string[] tbl = lsTable[index].Split('_');
List <string> listObjKeyName = GenScript.getListObjKeyName(tbl[0], tbl[1], oracleConnection);
progressSize += listObjKeyName.Count;
}
progressSize += GenScript.getListObjKeyName("tltx", "tltxcd", oracleConnection).Count;
progressBarExpDt.Maximum = progressSize;
for (int index = 0; index < lsTable.Length; index++)
{
string[] tbl = lsTable[index].Split('_');
string tblName = tbl[0].ToLower();
if (tblName != "tltx" && tblName != "fldmaster" && tblName != "fldval" &&
tblName != "appchk" && tblName != "appmap")
{
GenScript.GenInsertCommand(tbl[0], tbl[1], oracConnection, pathMaster.ListPath, progressBarExpDt);
}
}
GenScript.GenInsertCommandForTransTable(oracConnection, pathMaster.ListPath, progressBarExpDt);
}
MessageBox.Show("Export completed!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception e)
{
Logger.ShowMsg(e.Message);
Logger.Logging(e.Message, DateTime.Now.ToString());
}
progressBarExpDt.Value = 0;
oracleConnection.Close();
oracleConnection.Dispose();
}
}