public static void ConnectionTest(MainForm frm)
{
PFMsAccess db = new PFMsAccess(frm.cboDatabase.Text,
frm.txtDbUsername.Text,
frm.txtDbPassword.Text);
if (frm.cboAccessVersion.Text == "Access 2003")
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0;
}
else
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
}
try
{
db.OpenConnection();
_msg.Length = 0;
if (db.IsConnected)
{
_msg.Append("Connection established!");
}
else
{
_msg.Append("*** Connection failed! ***");
}
Program._messageLog.WriteLine(_msg.ToString());
db.CloseConnection();
_msg.Length = 0;
_msg.Append("Connection string = ");
_msg.Append(db.ConnectionString);
_msg.Append(Environment.NewLine);
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
{
;
}
}
/// <summary>
/// Appends data contained in ADO.NET DataTable object to path stored in OutputFileName 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 AppendDataToOutput(DataTable dt)
{
bool success = true;
PFMsAccess db = null;
try
{
PFDataAccessObjects.AccessVersion accver = this.AccessVersion == enAccessVersion.Access2007 ? PFDataAccessObjects.AccessVersion.Access2007 : PFDataAccessObjects.AccessVersion.Access2003;
if (File.Exists(_outputFileName) == false)
{
PFMsAccess.CreateDatabase(this.OutputFileName, accver, this._replaceExistingFile, this.DbUsername, this.DbPassword);
}
db = new PFMsAccess(this.OutputFileName, this.DbUsername, this.DbPassword);
db.OpenConnection();
dt.TableName = this.TableName;
db.ImportDataFromDataTable(dt);
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;
}
}
return(success);
}
private string GetConnectionString()
{
PFMsAccess db = new PFMsAccess();
string connectionString = string.Empty;
try
{
db.DatabasePath = this.cboDatabase.Text;
db.DatabaseUsername = this.txtDbUsername.Text;
db.DatabasePassword = this.txtDbPassword.Text;
if (this.cboAccessVersion.Text == "Access 2003")
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0;
}
else
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
}
connectionString = db.ConnectionString;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
}
finally
{
if (db != null)
{
db = null;
}
}
return(connectionString);
}
public static void RunCreateDatabaseTableTest(MainForm frm)
{
PFMsAccess access = null;
string dbPath = frm.txtDatabasePath.Text;
string createScript = string.Empty;
string tableName = "TestTabX1";
string tableName2 = "TestTabX2";
AccessVersion dbVersion = AccessVersion.Access2003;
PFUnitTestDataTable unitTestDt01 = null;
PFUnitTestDataTable unitTestDt02 = null;
try
{
_msg.Length = 0;
_msg.Append("RunAdoxAdorTest started ...");
Program._messageLog.WriteLine(_msg.ToString());
if (Path.GetExtension(dbPath) == ".accdb")
{
dbVersion = AccessVersion.Access2007;
}
else
{
dbVersion = AccessVersion.Access2003;
}
access = PFMsAccess.CreateDatabase(dbPath, dbVersion, frm.chkOverwriteExistingDb.Checked, "admin", string.Empty);
/*
* DataTable dt = new DataTable(tableName);
* DataColumn k1 = new DataColumn("K1",Type.GetType("System.Int32"));
* dt.Columns.Add(k1);
* DataColumn f1 = new DataColumn("F1", Type.GetType("System.String"));
* f1.MaxLength = 50;
* dt.Columns.Add(f1);
* 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);
*
* access.CreateTable(dt);
*
*/
if (access.IsConnected == false)
{
access.OpenConnection();
}
IDatabaseProvider db = (IDatabaseProvider)access;
unitTestDt01 = new PFUnitTestDataTable(db, "", tableName, true);
unitTestDt02 = new PFUnitTestDataTable(db, "", tableName2, true);
access.ReopenConnection();
_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;
_msg.Length = 0;
_msg.Append("CreateScript:\r\n");
_msg.Append(createScript);
Program._messageLog.WriteLine(_msg.ToString());
//import data to database
access.ReopenConnection();
_msg.Length = 0;
_msg.Append("Importing data to TestTable01");
Program._messageLog.WriteLine(_msg.ToString());
unitTestDt01.ImportTableToDatabase();
access.ReopenConnection();
_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
StringBuilder sql = new StringBuilder();
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.FormatErrorMessageWithStackTrace(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (access != null)
{
if (access.IsConnected)
{
access.CloseConnection();
}
}
access = null;
System.GC.Collect();
_msg.Length = 0;
_msg.Append("... RunCreateDatabaseTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void GetQueryDataSchema(MainForm frm)
{
PFMsAccess db = new PFMsAccess();
try
{
_msg.Length = 0;
_msg.Append("GetQueryDataSchema started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
db.DatabasePath = frm.cboDatabase.Text;
db.DatabaseUsername = frm.txtDbUsername.Text;
db.DatabasePassword = frm.txtDbPassword.Text;
if (frm.cboAccessVersion.Text == "Access 2003")
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0;
}
else
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
}
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.ConnectionState.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 PFMsAccess.ResultDelegate(OutputResults);
db.SQLQuery = frm.txtSqlQuery.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
{
_msg.Length = 0;
_msg.Append("\r\n... GetQueryDataSchema finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void DataSetTest(MainForm frm)
{
PFMsAccess db = new PFMsAccess();
Program._messageLog.WriteLine("DataSetTest started ...");
try
{
db.DatabasePath = frm.cboDatabase.Text;
db.DatabaseUsername = frm.txtDbUsername.Text;
db.DatabasePassword = frm.txtDbPassword.Text;
if (frm.cboAccessVersion.Text == "Access 2003")
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0;
}
else
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
}
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.ConnectionState.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 PFMsAccess.ResultDelegate(OutputResults);
db.SQLQuery = frm.txtSqlQuery.Text;
DataSet ds = db.RunQueryDataSet();
db.ProcessDataSet(ds);
db.returnResult -= OutputResults;
db.returnResultAsString += new PFMsAccess.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\MsAccessDsDelimited.txt", PFFileOpenOperation.OpenFileForWrite);
ds = db.RunQueryDataSet();
db.ExtractDelimitedDataFromDataSet(ds, ",", "\r\n", true);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\MsAccessDsFixedLength.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\MsAccessTestDs.xml");
ds = db.RunQueryDataSet();
db.SaveDataSetWithSchemaToXmlFile(ds, @"c:\temp\MsAccessTestDsPlus.xml");
ds = db.RunQueryDataSet();
db.SaveDataSetToXmlSchemaFile(ds, @"c:\temp\MsAccessTestDs.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.ConnectionState == ConnectionState.Open)
{
db.CloseConnection();
}
Program._messageLog.WriteLine("... DataSetTest finished.");
}
}
public static void DataReaderTest(MainForm frm)
{
PFMsAccess db = new PFMsAccess(frm.cboDatabase.Text,
frm.txtDbUsername.Text,
frm.txtDbPassword.Text);
if (frm.cboAccessVersion.Text == "Access 2003")
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0;
}
else
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
}
try
{
db.OpenConnection();
_msg.Length = 0;
if (db.IsConnected)
{
_msg.Append("Connection established!\r\n");
_msg.Append("Connection string: ");
_msg.Append(db.ConnectionString);
}
else
{
_msg.Append("*** Connection failed! ***");
}
Program._messageLog.WriteLine(_msg.ToString());
db.returnResult += new PFMsAccess.ResultDelegate(OutputResults);
db.SQLQuery = frm.txtSqlQuery.Text;
OleDbDataReader rdr = (OleDbDataReader)db.RunQueryDataReader();
db.ProcessDataReader(rdr);
rdr.Close();
db.returnResult -= OutputResults;
db.returnResultAsString += new PFMsAccess.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\MsAccessRdrDelimited.txt", PFFileOpenOperation.OpenFileForWrite);
rdr = (OleDbDataReader)db.RunQueryDataReader();
db.ExtractDelimitedDataFromDataReader(rdr, ",", "\r\n", true);
rdr.Close();
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\MsAccessRdrFixedLength.txt", PFFileOpenOperation.OpenFileForWrite);
rdr = (OleDbDataReader)db.RunQueryDataReader();
db.ExtractFixedLengthDataFromDataReader(rdr, true, true, false);
rdr.Close();
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
rdr = (OleDbDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\MsAccessTestrdr.xml");
rdr.Close();
rdr = (OleDbDataReader)db.RunQueryDataReader();
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\MsAccessTestrdrplus.xml");
rdr.Close();
rdr = (OleDbDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\MsAccessTestrdr.xsd");
rdr.Close();
rdr = (OleDbDataReader)db.RunQueryDataReader();
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
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 (db.IsConnected)
{
db.CloseConnection();
}
if (db != null)
{
db = null;
}
}
}
public static void RunDropTableTest(MainForm frm)
{
PFMsAccess db = new PFMsAccess(frm.txtDatabasePath.Text,
frm.txtDbUsername.Text,
frm.txtDbPassword.Text);
if (Path.GetExtension(frm.txtDatabasePath.Text) == ".mdb")
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0;
}
else
{
db.OleDbProvider = PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
}
string tableName = "TestTabX1";
bool tableDropped = false;
try
{
_msg.Length = 0;
_msg.Append("RunDropTableTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
db.OpenConnection();
_msg.Length = 0;
_msg.Append("Table ");
_msg.Append(tableName);
if (db.TableExists(tableName))
{
tableDropped = db.DropTable(tableName);
if (tableDropped)
{
_msg.Append(" was dropped.");
}
else
{
_msg.Append(" drop failed.");
}
}
else
{
_msg.Append(" does not exist.");
}
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... RunDropTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
private void ExportToAccessDatabase()
{
PFMsAccess db = null;
string fileName = string.Empty;
string fileExtension = string.Empty;
if (ShowSaveFileDialog() != DialogResult.OK)
{
return;
}
fileName = _saveFileDialog.FileName;
fileExtension = Path.GetExtension(fileName);
try
{
AccessVersion accver = AccessVersion.Access2003;
if (fileExtension.ToLower() == ".mdb")
{
accver = AccessVersion.Access2003;
}
else
{
accver = AccessVersion.Access2007;
}
if (File.Exists(fileName) == false)
{
PFMsAccess.CreateDatabase(fileName, accver, false, "Admin", string.Empty);
}
db = new PFMsAccess(fileName);
DataTable dt = this.keyValsDataSet.Tables["KeyValTable"];
db.CreateTable(dt); //separate adox and adodb connections are made by this call
db.OleDbProvider = fileExtension.ToLower() == ".mdb" ? PFAccessOleDbProvider.MicrosoftJetOLEDB_4_0 : PFAccessOleDbProvider.MicrosoftACEOLEDB_12_0;
db.OpenConnection();
db.ImportDataFromDataTable(dt);
db.CloseConnection();
db = null;
System.GC.Collect();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessageWithStackTrace(ex));
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToErrorLog);
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
System.GC.Collect();
}
}
}
/// <summary>
/// Writes data contained in ADO.NET DataTable object to path stored in OutputFileName 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;
PFMsAccess db = null;
try
{
if (File.Exists(_outputFileName))
{
if (_replaceExistingFile)
{
File.SetAttributes(_outputFileName, FileAttributes.Normal);
File.Delete(_outputFileName);
}
}
PFDataAccessObjects.AccessVersion accver = this.AccessVersion == enAccessVersion.Access2007 ? PFDataAccessObjects.AccessVersion.Access2007 : PFDataAccessObjects.AccessVersion.Access2003;
if (File.Exists(_outputFileName) == false)
{
PFMsAccess.CreateDatabase(this.OutputFileName, accver, this._replaceExistingFile, this.DbUsername, this.DbPassword);
}
db = new PFMsAccess(this.OutputFileName, this.DbUsername, this.DbPassword);
db.OpenConnection();
dt.TableName = this.TableName;
if (db.TableExists(this.TableName))
{
if (this.ReplaceExistingTable == false)
{
_msg.Length = 0;
_msg.Append("ERROR: Table ");
_msg.Append(this.TableName);
_msg.Append(" exists and ReplaceExistingTable is False.\r\n");
_msg.Append("Output to Access database will be cancelled without any data output.");
throw new System.Exception(_msg.ToString());
}
bool dropSucceeded = db.DropTable(this.TableName);
if (dropSucceeded == false)
{
_msg.Length = 0;
_msg.Append("ERROR: Attempt to drop existing table ");
_msg.Append(this.TableName);
_msg.Append(" failed.\r\n");
_msg.Append("Output to Access database will be cancelled without any data output.");
throw new System.Exception(_msg.ToString());
}
}
db.CreateTable(dt);
db.ReopenConnection(); //needed to make sure new table exists for subsequent statements
db.ImportDataFromDataTable(dt);
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;
}
}
return(success);
}
