public static void ImportDataTableTest(MainForm frm)
{
PFOracle oracle = new PFOracle();
string connectionString = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("ImportDataTableTest started ...");
Program._messageLog.WriteLine(_msg.ToString());
oracle.DataSource = frm.txtDataSource.Text;
oracle.UseIntegratedSecurity = frm.chkUseIntegratedSecurity.Checked;
oracle.Username = frm.txtUserId.Text;
oracle.Password = frm.txtPassword.Text;
connectionString = oracle.ConnectionString;
oracle.OpenConnection();
StringBuilder sql = new StringBuilder();
DataTable dt = frm.keyValsDataSet.Tables["HR.KeyValTable"];
string tableName = dt.TableName;
//first delete table if it already exists
Program._messageLog.WriteLine("\r\nDropping old table if it exists ...");
//sql.Length = 0;
//sql.Append("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'");
//sql.Append(dt.TableName);
//sql.Append("') AND type in (N'U'))");
//sql.Append("\r\n");
//sql.Append("DROP TABLE ");
//sql.Append(dt.TableName);
//sql.Append(";");
//Program._messageLog.WriteLine(sql.ToString());
//sql.Length = 0;
//sql.Append("declare");
//sql.Append(" c int;");
//sql.Append("begin");
//sql.Append(" select count(*) into c from user_tables where table_name = upper('");
//sql.Append(dt.TableName);
//sql.Append("');");
//sql.Append(" if c = 1 then");
//sql.Append(" execute immediate 'drop table ");
//sql.Append(dt.TableName);
//sql.Append("';");
//sql.Append(" end if;");
//sql.Append("end;");
//Program._messageLog.WriteLine(sql.ToString());
//int numRows = oracle.RunNonQuery(sql.ToString(), CommandType.Text);
//_msg.Length = 0;
//_msg.Append("Num rows affected on drop table: ");
//_msg.Append(numRows.ToString());
//Program._messageLog.WriteLine(_msg.ToString());
string schemaName = "SYS";
string tabName = "InvalidTableName";
string[] parsedTableName = tableName.Split('.');
if (parsedTableName.Length == 2)
{
schemaName = parsedTableName[0];
tabName = parsedTableName[1];
}
else if (parsedTableName.Length == 1)
{
tabName = parsedTableName[0];
}
else
{
;
}
if (oracle.TableExists(schemaName, tabName))
{
bool dropped = oracle.DropTable(schemaName, tabName);
if (dropped == false)
{
_msg.Length = 0;
_msg.Append("Unable to drop table ");
_msg.Append(schemaName);
_msg.Append(".");
_msg.Append(tabName);
throw new DataException(_msg.ToString());
}
}
Program._messageLog.WriteLine("\r\nCreating a table in the database ...");
//create the table
oracle.CreateTable(dt);
oracle.ImportDataFromDataTable(dt, 100);
oracle.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("... ImportDataTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
