/// <summary> /// Writes data contained in DataTable to table stored in TableName property. /// </summary> /// <param name="dtList">List of temp file names containing data tables with grid rows to be output.</param> /// <returns>True if output operation is successful. False if write fails.</returns> public bool WriteDataToOutput(PFList <string> dtList) { bool success = true; PFDatabase db = null; bool createTable = false; DataTable dt = new DataTable(); string tableName = string.Empty; bool isOracleOdbc = false; bool isOracleOledb = false; try { if (dtList.Count == 0) { _msg.Length = 0; _msg.Append("WriteDataToOutput for list of temp files has failed."); _msg.Append(Environment.NewLine); _msg.Append("Temp file name list is empty."); throw new System.Exception(_msg.ToString()); } //Load first temp file in list to get data table schema dt.Rows.Clear(); dt.ReadXml(dtList[0]); db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName); db.ConnectionString = this.ConnectionString; tableName = this.TableName; //workaround for oracle odbc and oledb driver problems with inserts if (this.DbPlatform == DatabasePlatform.ODBC) { PFOdbc odbcDb = new PFOdbc(); odbcDb.ConnectionString = this.ConnectionString; DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform(); if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle) { isOracleOdbc = true; } odbcDb = null; } if (this.DbPlatform == DatabasePlatform.OLEDB) { PFOleDb oledbDb = new PFOleDb(); oledbDb.ConnectionString = this.ConnectionString; DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform(); if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle) { isOracleOledb = true; } oledbDb = null; } //set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider if (isOracleOdbc || isOracleOledb) { tableName = this.TableName.ToUpper(); for (int colInx = 0; colInx < dt.Columns.Count; colInx++) { dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper(); } } else { tableName = this.TableName; } //end workaround for oracle odbc and oledb dt.TableName = tableName; if (db.TableExists(tableName)) { if (this.ReplaceExistingTable) { db.OpenConnection(); db.DropTable(tableName); db.CloseConnection(); createTable = true; } else { createTable = false; //existing table will be imported into } } else { createTable = true; } if (createTable) { db.OpenConnection(); string createScript = string.Empty; string errorMessages = string.Empty; bool tabCreated = db.CreateTable(dt, out createScript, out errorMessages); if (tabCreated == false) { _msg.Length = 0; _msg.Append("Unable to create table: "); _msg.Append(tableName); _msg.Append(Environment.NewLine); _msg.Append("Error Messages: "); _msg.Append(errorMessages); _msg.Append(Environment.NewLine); _msg.Append("Create Script: "); _msg.Append(createScript); throw new System.Exception(_msg.ToString()); } db.CloseConnection(); } for (int dtInx = 0; dtInx < dtList.Count; dtInx++) { _msg.Length = 0; _msg.Append("List # "); _msg.Append(dtInx.ToString()); Console.WriteLine(_msg.ToString()); dt = new DataTable(); dt.TableName = this.TableName; //original table name should be in xml file definition dt.Rows.Clear(); dt.ReadXml(dtList[dtInx]); db.OpenConnection(); //set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider if (isOracleOdbc || isOracleOledb) { dt.TableName = this.TableName.ToUpper(); for (int colInx = 0; colInx < dt.Columns.Count; colInx++) { dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper(); } } db.ImportDataFromDataTable(dt, this.OutputBatchSize); db.CloseConnection(); dt = null; } } catch (System.Exception ex) { success = false; _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (db != null) { if (db.IsConnected) { db.CloseConnection(); } db = null; } } return(success); }
/// <summary> /// Writes data contained in DataTable to table stored in TableName property. /// </summary> /// <param name="dt">DataTable object containing data to be output.</param> /// <returns>True if output operation is successful. False if write fails.</returns> public bool WriteDataToOutput(DataTable dt) { bool success = true; PFDatabase db = null; bool createTable = false; DataTable saveDtSchema = null; string tableName = string.Empty; bool isOracleOdbc = false; bool isOracleOledb = false; try { //save column names that may be changed during export processing so that they can be restored in finally block below saveDtSchema = dt.Clone(); db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName); db.ConnectionString = this.ConnectionString; tableName = this.TableName; //workaround for oracle odbc and oledb driver problems with inserts if (this.DbPlatform == DatabasePlatform.ODBC) { PFOdbc odbcDb = new PFOdbc(); odbcDb.ConnectionString = this.ConnectionString; DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform(); if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle) { isOracleOdbc = true; } odbcDb = null; } if (this.DbPlatform == DatabasePlatform.OLEDB) { PFOleDb oledbDb = new PFOleDb(); oledbDb.ConnectionString = this.ConnectionString; DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform(); if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle) { isOracleOledb = true; } oledbDb = null; } //set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider if (isOracleOdbc || isOracleOledb) { tableName = this.TableName.ToUpper(); for (int colInx = 0; colInx < dt.Columns.Count; colInx++) { dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper(); } } //end workaround for oracle odbc and oledb dt.TableName = tableName; if (db.TableExists(tableName)) { if (this.ReplaceExistingTable) { db.OpenConnection(); db.DropTable(tableName); db.CloseConnection(); createTable = true; } else { createTable = false; //existing table will be imported into } } else { createTable = true; } if (createTable) { db.OpenConnection(); string createScript = string.Empty; string errorMessages = string.Empty; bool tabCreated = db.CreateTable(dt, out createScript, out errorMessages); if (tabCreated == false) { _msg.Length = 0; _msg.Append("Unable to create table: "); _msg.Append(tableName); _msg.Append(Environment.NewLine); _msg.Append("Error Messages: "); _msg.Append(errorMessages); _msg.Append(Environment.NewLine); _msg.Append("Create Script: "); _msg.Append(createScript); throw new System.Exception(_msg.ToString()); } db.CloseConnection(); } db.OpenConnection(); db.ImportDataFromDataTable(dt, this.OutputBatchSize); db.CloseConnection(); } catch (System.Exception ex) { success = false; _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (db != null) { if (db.IsConnected) { db.CloseConnection(); } db = null; } //resstore any column names that may have been changed during export processing for (int colInx = 0; colInx < dt.Columns.Count; colInx++) { dt.Columns[colInx].ColumnName = saveDtSchema.Columns[colInx].ColumnName; } } return(success); }
public static void CreateTableTest(MainForm frm) { string dbPlatformDesc = DatabasePlatform.Unknown.ToString(); DatabasePlatform dbPlatform = DatabasePlatform.Unknown; PFDatabase db = null; string connStr = string.Empty; string nmSpace = string.Empty; string clsName = string.Empty; string dllPath = string.Empty; Stopwatch sw = new Stopwatch(); string createScript = string.Empty; string tableName = string.Empty; StringBuilder sql = new StringBuilder(); PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { _msg.Length = 0; _msg.Append("CreateTableTest started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|'); dbPlatformDesc = parsedConnectionInfo[0]; connStr = parsedConnectionInfo[1]; string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty); string[] parsedConfig = configValue.Split('|'); nmSpace = parsedConfig[0]; clsName = parsedConfig[1]; dllPath = parsedConfig[2]; if (frm.txtTableName.Text.Length == 0) { throw new System.Exception("You must specify a table name."); } _msg.Length = 0; _msg.Append("Connecting to "); _msg.Append(dbPlatformDesc); Program._messageLog.WriteLine(_msg.ToString()); sw.Start(); db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName); dbPlatform = db.DbPlatform; db.ConnectionString = connStr; db.OpenConnection(); sw.Stop(); _msg.Length = 0; _msg.Append("Open connection time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); tableName = frm.txtTableName.Text; string catalogName = string.Empty; string schemaName = string.Empty; string tabName = string.Empty; string[] parsedTableName = tableName.Split('.'); if (parsedTableName.Length == 2) { schemaName = parsedTableName[0]; tabName = parsedTableName[1]; } else if (parsedTableName.Length == 1) { tabName = parsedTableName[0]; } else if (parsedTableName.Length == 3) { catalogName = parsedTableName[0]; schemaName = parsedTableName[1]; tabName = parsedTableName[2]; } else { tabName = string.Empty; } if (db.TableExists(catalogName, schemaName, tabName)) { bool dropped = db.DropTable(catalogName, schemaName, tabName); if (dropped == false) { _msg.Length = 0; _msg.Append("Unable to drop table "); if (catalogName != string.Empty) { _msg.Append(catalogName); _msg.Append("."); } if (schemaName != string.Empty) { _msg.Append(schemaName); _msg.Append("."); } _msg.Append(tabName); throw new DataException(_msg.ToString()); } } if (db.TableExists(catalogName, schemaName, tabName + "_02")) { bool dropped = db.DropTable(catalogName, schemaName, tabName + "_02"); if (dropped == false) { _msg.Length = 0; _msg.Append("Unable to drop table "); if (catalogName != string.Empty) { _msg.Append(catalogName); _msg.Append("."); } if (schemaName != string.Empty) { _msg.Append(schemaName); _msg.Append("."); } _msg.Append(tabName + "_02"); throw new DataException(_msg.ToString()); } } unitTestDt01 = new PFUnitTestDataTable(db, schemaName, tabName, true); unitTestDt02 = new PFUnitTestDataTable(db, schemaName, tabName + "_02", true); //--- _msg.Length = 0; _msg.Append("Initializing TableColumns"); Program._messageLog.WriteLine(_msg.ToString()); //select which data types to include List <KeyValuePair <string, string> > dataTypesToInclude = new List <KeyValuePair <string, string> >(); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int32", "1")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.String", "this is a string value")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int32", "1123456789")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt32", "3123456789")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int64", "23123456789")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt64", "8881234567889")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int16", "11123")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt16", "52432")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Double", "123456.7654")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Single", "321.234")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Decimal", "2123456789.22")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Char", "A")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Char[]", "ABCDEFGH")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Byte", "254")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.SByte", "125")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Byte[]", "UVWZYZ));")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Boolean", "true")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Object", "This is an object: be careful!")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.DateTime", "5/31/2013 13:54:25")); dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Guid", "58a4a08d-6101-4393-86dc-b2a8db46ec0f")); unitTestDt01.SetDataTypesToInclude(dataTypesToInclude); unitTestDt01.SetDataTypeOptions("System.String", false, true, 75); unitTestDt02.SetDataTypesToInclude(dataTypesToInclude); unitTestDt02.SetDataTypeOptions("System.String", false, true, 75000); //create the table _msg.Length = 0; _msg.Append("Creating tables"); Program._messageLog.WriteLine(_msg.ToString()); unitTestDt01.CreateTableFromTableColumns(); unitTestDt02.CreateTableFromTableColumns(); createScript = unitTestDt01.TableCreateScript; //import data to database _msg.Length = 0; _msg.Append("Importing data to TestTable01"); Program._messageLog.WriteLine(_msg.ToString()); unitTestDt01.ImportTableToDatabase(); _msg.Length = 0; _msg.Append("Importing data to TestTable02"); Program._messageLog.WriteLine(_msg.ToString()); unitTestDt02.ImportTableToDatabase(); //retrieve just created table and see what data types get assigned to data table columns Program._messageLog.WriteLine("\r\nRead row just created for " + tableName + "\r\n"); sql.Length = 0; sql.Append("select * from "); sql.Append(tableName); DataTable testTab = db.RunQueryDataTable(sql.ToString(), CommandType.Text); for (int c = 0; c < testTab.Columns.Count; c++) { _msg.Length = 0; _msg.Append(testTab.Columns[c].ColumnName); _msg.Append(", "); _msg.Append(testTab.Columns[c].DataType.FullName); _msg.Append(", "); _msg.Append(testTab.Columns[c].MaxLength.ToString()); Program._messageLog.WriteLine(_msg.ToString()); } } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (db != null) { if (db.IsConnected) { db.CloseConnection(); } db = null; } _msg.Length = 0; _msg.Append("\r\n... CreateTableTest finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
public static void ImportTableTest(MainForm frm) { string dbPlatformDesc = DatabasePlatform.Unknown.ToString(); PFDatabase db = null; string connStr = string.Empty; string nmSpace = string.Empty; string clsName = string.Empty; string dllPath = string.Empty; Stopwatch sw = new Stopwatch(); try { sw.Start(); _msg.Length = 0; _msg.Append("ImportTableTest started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|'); dbPlatformDesc = parsedConnectionInfo[0]; connStr = parsedConnectionInfo[1]; string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty); string[] parsedConfig = configValue.Split('|'); nmSpace = parsedConfig[0]; clsName = parsedConfig[1]; dllPath = parsedConfig[2]; db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName); db.ConnectionString = connStr; db.OpenConnection(); string tableName = frm.txtKeyValsTableName.Text; DataTable tab = frm.KeyValTable; tab.TableName = tableName; _msg.Length = 0; _msg.Append(tableName); if (db.TableExists(tableName)) { db.DropTable(tableName); if (db.TableExists(tableName) == false) { _msg.Append(" dropped."); } else { _msg.Append(" drop failed."); } } else { _msg.Append(" does not exist."); } Program._messageLog.WriteLine("\r\nCreating a table in the database ..."); //create the table db.CreateTable(tab); Program._messageLog.WriteLine("\r\nImporting data table to the database ..."); int batchSize = Convert.ToInt32(frm.txtUpdateBatchSize.Text); if (batchSize == 1) { db.ImportDataFromDataTable(tab); } else { db.ImportDataFromDataTable(tab, batchSize); } _msg.Length = 0; _msg.Append("Table imported: "); _msg.Append(tab.TableName); Program._messageLog.WriteLine(_msg.ToString()); } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (sw.StopwatchIsRunning) { sw.Stop(); sw.ShowMilliseconds = false; _msg.Length = 0; _msg.Append("Elapsed Time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); sw = null; } if (db != null) { if (db.IsConnected) { db.CloseConnection(); } db = null; } _msg.Length = 0; _msg.Append("\r\n... ImportTableTest finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
private void CopyTables(PFDatabase sourceDb, PFDatabase destinationDb, bool replaceExistingTables, int batchSizeForDataWrites, string outputTablesSchema) { string sourceQuery = string.Empty; string destinationTableName = string.Empty; int numTableCreateErrors = 0; int numImportErrors = 0; Stopwatch sw = new Stopwatch(); try { if (sourceDb.DbPlatform == DatabasePlatform.SQLServerCE35) { sourceQuery = _sourceQuery.Replace("dbo.", string.Empty); } else { sourceQuery = _sourceQuery; } _msg.Length = 0; _msg.Append("Output Database Platform: "); _msg.Append(destinationDb.DbPlatform.ToString()); _msg.Append(Environment.NewLine); Program._messageLog.WriteLine(_msg.ToString()); sw.Start(); foreach (stTableInfo tabInfo in _tableInfo) { //load data from source database into DataTable string qry = sourceQuery.Replace(@"<tablename>", tabInfo.tableName); string configValue = AppConfig.GetStringValueFromConfigFile(destinationDb.DbPlatform.ToString() + "_" + tabInfo.tableName, string.Empty); if (configValue.Length > 0) { qry = configValue; } sourceDb.SQLQuery = qry; sourceDb.CommandType = CommandType.Text; DataTable dt = sourceDb.RunQueryDataTable(); _msg.Length = 0; _msg.Append("Query text: "); _msg.Append(sourceDb.SQLQuery); _msg.Append(Environment.NewLine); _msg.Append("Expected number of rows: "); _msg.Append(tabInfo.numRows.ToString("#,##0")); _msg.Append(Environment.NewLine); _msg.Append("Number of rows returned: "); _msg.Append(dt.Rows.Count.ToString("#,##0")); Program._messageLog.WriteLine(_msg.ToString()); //import data from DataTable into destination database if (outputTablesSchema.Trim().Length > 0) { destinationTableName = outputTablesSchema + "." + tabInfo.tableName; } else { destinationTableName = tabInfo.tableName; } //workaround to fix issue with Oracle limit on identifier lengths if (destinationDb.DbPlatform == DatabasePlatform.OracleNative || destinationDb.DbPlatform == DatabasePlatform.MSOracle) { if (tabInfo.tableName == "FactAdditionalInternationalProductDescription") { if (outputTablesSchema.Trim().Length > 0) { destinationTableName = outputTablesSchema + "." + "FactProductDescriptionExt"; } else { destinationTableName = "FactProductDescriptionExt"; } } } dt.TableName = destinationTableName; _msg.Length = 0; _msg.Append(destinationTableName); if (replaceExistingTables) { if (destinationDb.TableExists(destinationTableName)) { destinationDb.DropTable(destinationTableName); if (destinationDb.TableExists(destinationTableName) == false) { _msg.Append(" dropped."); } else { _msg.Append(" drop failed."); } } else { _msg.Append(" does not exist."); } } else { _msg.Append(": No check was made to determine if table already existed."); } Program._messageLog.WriteLine(_msg.ToString()); Program._messageLog.WriteLine("Creating table in the database ..."); //create the table string createScript = string.Empty; string errorMessages = string.Empty; bool createSucceeded = true; createSucceeded = destinationDb.CreateTable(dt, out createScript, out errorMessages); _msg.Length = 0; _msg.Append("Create table result: "); _msg.Append(createSucceeded.ToString()); _msg.Append(Environment.NewLine); if (errorMessages.Trim().Length > 0) { _msg.Append("Error Messages: "); _msg.Append(Environment.NewLine); _msg.Append(errorMessages); _msg.Append(Environment.NewLine); } _msg.Append("Create table statement: "); _msg.Append(Environment.NewLine); _msg.Append(createScript); _msg.Append(Environment.NewLine); Program._messageLog.WriteLine(_msg.ToString()); if (createSucceeded == false) { numTableCreateErrors++; } else { Program._messageLog.WriteLine("Importing data table to the database ..."); _msg.Length = 0; _msg.Append("Data Write batch size: "); _msg.Append(batchSizeForDataWrites.ToString()); Program._messageLog.WriteLine(_msg.ToString()); try { if (batchSizeForDataWrites == 1) { destinationDb.ImportDataFromDataTable(dt); } else { destinationDb.ImportDataFromDataTable(dt, batchSizeForDataWrites); } _msg.Length = 0; _msg.Append("Table imported: "); _msg.Append(dt.TableName); _msg.Append(Environment.NewLine); Program._messageLog.WriteLine(_msg.ToString()); } catch (System.Exception ex) { numImportErrors++; _msg.Length = 0; _msg.Append("ERROR: Data import failed:"); _msg.Append(Environment.NewLine); _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); } finally { ; } } } } 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; if (numTableCreateErrors > 0) { _msg.Append("One or more table create errors were reported: "); _msg.Append(numTableCreateErrors.ToString()); _msg.Append(Environment.NewLine); } if (numImportErrors > 0) { _msg.Append("One or more data import errors were reported: "); _msg.Append(numImportErrors.ToString()); _msg.Append(Environment.NewLine); } if (_msg.Length > 0) { Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString()); } } }