//tests public static void RunConnectionTest(MainForm frm) { PFOleDb db = new PFOleDb(); Program._messageLog.WriteLine("RunConnectionTest started ..."); try { db.ConnectionString = frm.cboConnectionString.Text; db.OpenConnection(); _msg.Length = 0; _msg.Append("Connection string is "); _msg.Append(db.ConnectionString); _msg.Append("\r\n"); _msg.Append("Connection state is "); _msg.Append(db.Connection.State.ToString()); _msg.Append("\r\n"); _msg.Append("Provider is "); _msg.Append(db.Provider); _msg.Append("\r\n"); _msg.Append("DataSource is "); _msg.Append(db.DataSource); _msg.Append("\r\n"); _msg.Append("UDLFIleName is "); _msg.Append(db.UDLFileName); _msg.Append("\r\n"); _msg.Append("PersistSecurityInfo is "); _msg.Append(db.PersistSecurityInfo.ToString()); _msg.Append("\r\n"); _msg.Append("OleDbServices is "); _msg.Append(db.OleDbServices.ToString()); _msg.Append("\r\n"); Program._messageLog.WriteLine(_msg.ToString()); foreach (stKeyValuePair <string, string> kv in db.ConnectionStringKeyVals) { _msg.Length = 0; _msg.Append(kv.Key + "=" + kv.Value); Program._messageLog.WriteLine(_msg.ToString()); } Program._messageLog.WriteLine(Environment.NewLine); } 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.Connection.State == ConnectionState.Open) { db.CloseConnection(); } Program._messageLog.WriteLine("... RunConnectionTest finished."); } }
public static void GetQueryDataSchema(MainForm frm) { PFOleDb db = new PFOleDb(); Program._messageLog.WriteLine("GetQueryDataSchema started ..."); try { db.ConnectionString = frm.cboConnectionString.Text; db.OpenConnection(); _msg.Length = 0; _msg.Append("Connection string is "); _msg.Append(db.ConnectionString); _msg.Append("\r\n"); _msg.Append("Connection state is "); _msg.Append(db.Connection.State.ToString()); _msg.Append("\r\n"); _msg.Append("Query text is:\r\n"); _msg.Append(frm.txtSqlQuery.Text.ToString()); Program._messageLog.WriteLine(_msg.ToString()); db.SQLQuery = frm.txtSqlQuery.Text; db.CommandType = CommandType.Text; DataTable tab = db.GetQueryDataSchema(); foreach (DataColumn col in tab.Columns) { _msg.Length = 0; _msg.Append(col.ColumnName); _msg.Append(", "); _msg.Append(col.DataType.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.Connection.State == ConnectionState.Open) { db.CloseConnection(); } Program._messageLog.WriteLine("... GetQueryDataSchema finished."); } }
public static void ImportDataTableTest(MainForm frm) { PFOleDb db = new PFOleDb(); string connectionString = string.Empty; string originalTableName = string.Empty; DataTable dt = null; try { if (frm.cboConnectionString.Text.ToLower().Contains("ifxoledbc")) { _msg.Length = 0; _msg.Append("Import table does not work with Informix driver. Driver does not have capability of retrieving schema information."); throw new System.Exception(_msg.ToString()); } _msg.Length = 0; _msg.Append("ImportDataTableTest started ..."); Program._messageLog.WriteLine(_msg.ToString()); connectionString = frm.cboConnectionString.Text; db.ConnectionString = connectionString; db.OpenConnection(); StringBuilder sql = new StringBuilder(); dt = frm.keyValsDataSet.Tables["KeyValTable"]; originalTableName = dt.TableName; string tableName = frm.txtTableName.Text; //first delete table if it already exists Program._messageLog.WriteLine("\r\nDropping old table if it exists ..."); string catalogName = string.Empty; string schemaName = string.Empty; string tabName = string.Empty; catalogName = frm.txtCatalogName.Text; schemaName = frm.txtSchemaName.Text; tabName = "KeyValTable"; if (schemaName.Trim().Length > 0) { dt.TableName = schemaName + "." + tabName; } 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.Trim().Length > 0) { _msg.Append(catalogName); _msg.Append("."); } if (schemaName.Trim().Length > 0) { _msg.Append(schemaName); _msg.Append("."); } _msg.Append(tabName); throw new DataException(_msg.ToString()); } else { _msg.Length = 0; _msg.Append("Old table dropped: "); if (catalogName.Trim().Length > 0) { _msg.Append(catalogName); _msg.Append("."); } if (schemaName.Trim().Length > 0) { _msg.Append(schemaName); _msg.Append("."); } _msg.Append(tabName); Program._messageLog.WriteLine(_msg.ToString()); } } Program._messageLog.WriteLine("\r\nCreating a table in the database ..."); //create the table bool tableCreated = db.CreateTable(dt); if (tableCreated) { db.ImportDataFromDataTable(dt); } else { _msg.Length = 0; _msg.Append("CreateTable for "); _msg.Append(dt.TableName); _msg.Append(" failed."); Program._messageLog.WriteLine(_msg.ToString()); } db.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 { if (dt != null) { if (originalTableName.Length > 0) { dt.TableName = originalTableName; } } _msg.Length = 0; _msg.Append("... ImportDataTableTest finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
public static void DataReaderToDataTableTest(MainForm frm) { PFOleDb db = new PFOleDb(); string connectionString = string.Empty; Stopwatch sw = new Stopwatch(); try { connectionString = frm.cboConnectionString.Text; db.ConnectionString = connectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); if (frm.txtSqlQuery.Text.Length == 0) { throw new System.Exception("You must specify a SQL query to run."); } sw.Start(); db.OpenConnection(); db.SQLQuery = frm.txtSqlQuery.Text; db.CommandType = CommandType.Text; sw.Stop(); _msg.Length = 0; _msg.Append("Open connection time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); sw.Start(); OleDbDataReader rdr = (OleDbDataReader)db.RunQueryDataReader(); DataTable tab = db.ConvertDataReaderToDataTable(rdr); Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString()); rdr.Close(); for (int i = 0; i < tab.Rows.Count; i++) { DataRow r = tab.Rows[i]; _msg.Length = 0; int maxColInx = tab.Columns.Count - 1; for (int ci = 0; ci <= maxColInx; ci++) { _msg.Append(tab.Columns[ci].ColumnName); _msg.Append(": "); _msg.Append(r[ci].ToString()); if (ci < maxColInx) { _msg.Append(", "); } } Program._messageLog.WriteLine(_msg.ToString()); } sw.Stop(); _msg.Length = 0; _msg.Append("Table read time: "); _msg.Append(sw.FormattedElapsedTime); 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 { db.CloseConnection(); db = null; } }
public static void CreateTableTest(MainForm frm) { PFOleDb oledb = new PFOleDb(); IDatabaseProvider db = oledb; string connectionString = string.Empty; string createScript = string.Empty; string tableName = "TestTable01"; StringBuilder sql = new StringBuilder(); PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; DataTable dt = null; DataTable dt2 = null; try { if (frm.cboConnectionString.Text.ToLower().Contains("ifxoledbc")) { _msg.Length = 0; _msg.Append("Create table does not work with Informix driver. Driver does not have capability of retrieving schema information."); throw new System.Exception(_msg.ToString()); } db.ConnectionString = frm.cboConnectionString.Text; if (frm.txtTableName.Text.Length > 0) { tableName = frm.txtTableName.Text; } else { _msg.Length = 0; _msg.Append("You must specify a table name for the CreateTableTest."); throw new System.Exception(_msg.ToString()); } connectionString = db.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); db.OpenConnection(); //Get a list of the data mappings supported by the selected driver PFTableBuilder tabBuilder = new PFTableBuilder(DatabasePlatform.OLEDB); List <DataTypeMapping> dataTypeMappings = tabBuilder.GetGenericDataTypeMappingsEx(db.ConnectionString); //List<DataTypeMapping> dataTypeMappings = tabBuilder.GetGenericDataTypeMappingsAll(db.ConnectionString); Program._messageLog.WriteLine("\r\nData Type Mappings:\r\n"); if (dataTypeMappings != null) { for (int i = 0; i < dataTypeMappings.Count; i++) { _msg.Length = 0; _msg.Append(dataTypeMappings[i].DatabaseDataType); _msg.Append(" is mapped to "); _msg.Append(dataTypeMappings[i].DotNetDataType); _msg.Append(" ("); _msg.Append(dataTypeMappings[i].dataTypeCategory); _msg.Append(") MaxLen = "); _msg.Append(dataTypeMappings[i].MaxLength.ToString()); Program._messageLog.WriteLine(_msg.ToString()); } } Program._messageLog.WriteLine(string.Empty); //first delete table if it already exists Program._messageLog.WriteLine("\r\nDropping old table if it exists ..."); 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); //will not work for MsOracle driver //MsOracle driver will fail to create table for strings longer than 4000 bytes, more than one byte array or a char array. //create the table _msg.Length = 0; _msg.Append("Creating tables"); Program._messageLog.WriteLine(_msg.ToString()); bool result = false; if (frm.chkUseOleDbBuilder.Checked) { dt = unitTestDt01.GetDataTableFromTableColumns(true); result = oledb.CreateTableUsingOleDbSyntax(dt, out createScript); dt2 = unitTestDt02.GetDataTableFromTableColumns(true); result = oledb.CreateTableUsingOleDbSyntax(dt2, out createScript); } else if (frm.chkUseCustomSQL.Checked) { result = oledb.CreateTableUsingCustomScript(frm.txtSqlQuery.Text); createScript = frm.txtSqlQuery.Text; } else { 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()); if (frm.chkUseOleDbBuilder.Checked) { oledb.ImportDataFromDataTable(dt); } else if (frm.chkUseCustomSQL.Checked) { _msg.Length = 0; _msg.Append("No import done for test table 01 created by custom script"); Program._messageLog.WriteLine(_msg.ToString()); } else { unitTestDt01.ImportTableToDatabase(); } _msg.Length = 0; _msg.Append("Importing data to TestTable02"); Program._messageLog.WriteLine(_msg.ToString()); if (frm.chkUseOleDbBuilder.Checked) { oledb.ImportDataFromDataTable(dt2); } else if (frm.chkUseCustomSQL.Checked) { _msg.Length = 0; _msg.Append("No import done for test table 02 created by custom script"); Program._messageLog.WriteLine(_msg.ToString()); } else { 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()); } /* * Program._messageLog.WriteLine("\r\nCreating a table in the database ..."); * DataTable dt = new DataTable(tableName); * DataColumn k1 = new DataColumn("K1", Type.GetType("System.Int32")); * k1.AllowDBNull = false; * dt.Columns.Add(k1); * DataColumn f1 = new DataColumn("F1", Type.GetType("System.String")); * f1.MaxLength = 50; * dt.Columns.Add(f1); * DataColumn f1x = new DataColumn("F1X", Type.GetType("System.String")); * f1x.MaxLength = 500; * dt.Columns.Add(f1x); * DataColumn f1a = new DataColumn("F1A", Type.GetType("System.String")); * f1a.MaxLength = 50000; * dt.Columns.Add(f1a); * DataColumn f2 = new DataColumn("F2", Type.GetType("System.Int32")); * dt.Columns.Add(f2); * DataColumn f2a = new DataColumn("F2A", Type.GetType("System.UInt32")); * dt.Columns.Add(f2a); * DataColumn f3 = new DataColumn("F3", Type.GetType("System.Int64")); * dt.Columns.Add(f3); * DataColumn f3a = new DataColumn("F3A", Type.GetType("System.UInt64")); * dt.Columns.Add(f3a); * DataColumn f4 = new DataColumn("F4", Type.GetType("System.Int16")); * dt.Columns.Add(f4); * DataColumn f4a = new DataColumn("F4A", Type.GetType("System.UInt16")); * dt.Columns.Add(f4a); * DataColumn f5 = new DataColumn("F5", Type.GetType("System.Double")); * dt.Columns.Add(f5); * DataColumn f6 = new DataColumn("F6", Type.GetType("System.Single")); * dt.Columns.Add(f6); * DataColumn f7 = new DataColumn("F7", Type.GetType("System.Decimal")); * dt.Columns.Add(f7); * DataColumn f8 = new DataColumn("F8", Type.GetType("System.Char")); * dt.Columns.Add(f8); * DataColumn f9 = new DataColumn("F9", Type.GetType("System.Byte")); * dt.Columns.Add(f9); * DataColumn f9a = new DataColumn("F9A", Type.GetType("System.SByte")); * dt.Columns.Add(f9a); * DataColumn f10 = new DataColumn("F10", Type.GetType("System.Boolean")); * dt.Columns.Add(f10); * DataColumn f11 = new DataColumn("F11", Type.GetType("System.Object")); * dt.Columns.Add(f11); * DataColumn f12 = new DataColumn("F12", Type.GetType("System.DateTime")); * dt.Columns.Add(f12); * DataColumn f13 = new DataColumn("F13", Type.GetType("System.Guid")); * dt.Columns.Add(f13); * DataColumn f14 = new DataColumn("F14", Type.GetType("System.Char[]")); * dt.Columns.Add(f14); * DataColumn f15 = new DataColumn("F15", Type.GetType("System.Byte[]")); * dt.Columns.Add(f15); * DataColumn f16 = new DataColumn("F16", Type.GetType("System.Char[]")); * dt.Columns.Add(f16); * DataColumn f17 = new DataColumn("F17", Type.GetType("System.Byte[]")); * dt.Columns.Add(f17); * * * */ } 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("SQL Script: \r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); } }
public static void RunDataSetTest(MainForm frm) { PFOleDb db = new PFOleDb(); Program._messageLog.WriteLine("RunDataSetTest started ..."); try { db.ConnectionString = frm.cboConnectionString.Text; db.OpenConnection(); _msg.Length = 0; _msg.Append("Connection string is "); _msg.Append(db.ConnectionString); _msg.Append("\r\n"); _msg.Append("Connection state is "); _msg.Append(db.Connection.State.ToString()); _msg.Append("\r\n"); _msg.Append("Query text is:\r\n"); _msg.Append(frm.txtSqlQuery.Text.ToString()); Program._messageLog.WriteLine(_msg.ToString()); db.returnResult += new PFOleDb.ResultDelegate(OutputResults); db.SQLQuery = frm.txtSqlQuery.Text; db.CommandType = CommandType.Text; DataSet ds = db.RunQueryDataSet(); db.ProcessDataSet(ds); db.returnResult -= OutputResults; db.returnResultAsString += new PFOleDb.ResultAsStringDelegate(OutputResultsToFile); if (_textFile.FileIsOpen) { _textFile.CloseFile(); } _textFile.OpenFile(@"c:\temp\OleDbDsDelimited.txt", PFFileOpenOperation.OpenFileForWrite); ds = db.RunQueryDataSet(); db.ExtractDelimitedDataFromDataSet(ds, ",", "\r\n", true); if (_textFile.FileIsOpen) { _textFile.CloseFile(); } _textFile.OpenFile(@"c:\temp\OleDbDsFixedLength.txt", PFFileOpenOperation.OpenFileForWrite); ds = db.RunQueryDataSet(); db.ExtractFixedLengthDataFromDataSet(ds, true, true, false); if (_textFile.FileIsOpen) { _textFile.CloseFile(); } ds = db.RunQueryDataSet(); db.SaveDataSetToXmlFile(ds, @"c:\temp\OleDbTestDs.xml"); ds = db.RunQueryDataSet(); db.SaveDataSetWithSchemaToXmlFile(ds, @"c:\temp\OleDbTestDsPlus.xml"); ds = db.RunQueryDataSet(); db.SaveDataSetToXmlSchemaFile(ds, @"c:\temp\OleDbTestDs.xsd"); ds = db.RunQueryDataSet(); PFDataProcessor dataProcessor = new PFDataProcessor(); XmlDocument xmlDoc = dataProcessor.CopyDataSetToXmlDocument(ds); Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n"); } 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 (_textFile.FileIsOpen) { _textFile.CloseFile(); } if (db.Connection.State == ConnectionState.Open) { db.CloseConnection(); } Program._messageLog.WriteLine("... RunDataSetTest finished."); } }
/// <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); }