/// <summary>
/// Creates a table in the database specified for this instance. Data is loaded from the column definitions encapsulated by this instance.
/// </summary>
/// <returns>True if table is created.</returns>
/// <remarks>Table create will fail if table already exists and you specify false for the DropOldTable option. Specify true for dropOldTable if you
/// want to create the table even if an older version of the table already exists.</remarks>
public bool CreateTableFromTableColumns()
{
bool tableCreated = false;
DataTable dt = null;
try
{
if (_db.DbPlatform == DatabasePlatform.ODBC || _db.DbPlatform == DatabasePlatform.OLEDB || _db.DbPlatform == DatabasePlatform.MSAccess)
{
dt = GetDataTableFromTableColumns(true);
}
else
{
dt = GetDataTableFromTableColumns();
}
if (_dropOldTable)
{
if (this.SchemaName.Trim().Length > 0)
{
if (_db.TableExists(this.SchemaName, this.TableName))
{
_db.DropTable(this.SchemaName, this.TableName);
}
}
else
{
if (_db.TableExists(this.TableName))
{
_db.DropTable(this.TableName);
}
}
}
tableCreated = _db.CreateTable(dt);
}
catch (System.Exception ex)
{
tableCreated = false;
_msg.Length = 0;
_msg.Append("Error while trying to create table from DataTable object ");
_msg.Append(this.TableName);
_msg.Append(": ");
_msg.Append(PFTextProcessor.FormatErrorMessage(ex));
throw new DataException(_msg.ToString());
}
finally
{
;
}
return(tableCreated);
}
/// <summary>
/// Runs the table create statements contained in the provided tableDefs object.
/// </summary>
/// <param name="destinationDatabase">Database object pointing to database where tables will be created.</param>
/// <param name="tableDefs">Object containing list of table definitions.</param>
/// <param name="dropBeforeCreate">If true and table already exists, table will be dropped and then recreated using the table definition in the supplied PFTableDefs list. If false, table create step is bypassed if table already exists.</param>
/// <returns>Number of tables created.</returns>
public int CreateTablesFromTableDefs(IDatabaseProvider destinationDatabase, PFList <PFTableDef> tableDefs, bool dropBeforeCreate)
{
int numTablesCreated = 0;
PFTableDef td = null;
string sqlStatement = string.Empty;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
if (destinationDatabase.TableExists(td) && dropBeforeCreate)
{
destinationDatabase.DropTable(td);
}
if (destinationDatabase.TableExists(td) == false)
{
sqlStatement = td.TableCreateStatement;
destinationDatabase.RunNonQuery(sqlStatement, CommandType.Text);
numTablesCreated++;
}
}
return(numTablesCreated);
}
public static void CreateTableTest(MainForm frm)
{
PFOdbc odbc = new PFOdbc();
IDatabaseProvider db = odbc;
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
{
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.ODBC);
List <DataTypeMapping> dataTypeMappings = tabBuilder.GetGenericDataTypeMappingsEx(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);
//create the table
_msg.Length = 0;
_msg.Append("Creating tables");
Program._messageLog.WriteLine(_msg.ToString());
bool result = false;
if (frm.chkUseOdbcBuilder.Checked)
{
dt = unitTestDt01.GetDataTableFromTableColumns(true);
result = odbc.CreateTableUsingOdbcSyntax(dt, out createScript);
dt2 = unitTestDt02.GetDataTableFromTableColumns(true);
result = odbc.CreateTableUsingOdbcSyntax(dt2, out createScript);
}
else if (frm.chkUseCustomSQL.Checked)
{
result = odbc.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.chkUseOdbcBuilder.Checked)
{
odbc.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.chkUseOdbcBuilder.Checked)
{
odbc.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());
}
/*
* if (schemaRoot.TableExists(catalogName, schemaName, tabName))
* {
* bool dropped = schemaRoot.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());
* }
* }
*
*
*
* 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);
*
*
* //DatabasePlatform dbplat = DatabasePlatform.Unknown;
*
* //if (connectionString.Contains("Driver={Microsoft Access Driver"))
* //{
* // dbplat = DatabasePlatform.MSAccess;
* //}
* //else if (connectionString.Contains("Driver={Oracle"))
* //{
* // dbplat = DatabasePlatform.OracleNative;
* //}
* //else if (connectionString.Contains("Driver={Microsoft ODBC for Oracle"))
* //{
* // dbplat = DatabasePlatform.MSOracle;
* //}
* //else if (connectionString.Contains("Driver={SQL Server")
* // || connectionString.Contains("SQL Server"))
* //{
* // dbplat = DatabasePlatform.MSSQLServer;
* //}
* //else if (connectionString.Contains("Driver={IBM DB2")
* // || connectionString.Contains("IBM DB2"))
* //{
* // dbplat = DatabasePlatform.DB2;
* //}
* //else if (connectionString.Contains("Driver={MySQL"))
* //{
* // dbplat = DatabasePlatform.MySQL;
* //}
* //else
* //{
* // dbplat = DatabasePlatform.ODBC;
* //}
*
*
*
* //PFTableBuilder tb = null;
* //if (frm.chkUseOdbcBuilder.Checked)
* //{
* // tb = new PFTableBuilder(DatabasePlatform.ODBC);
* // createScript = tb.GenericTableCreateStatement(dt, connectionString);
* //}
* //else if (dbplat != DatabasePlatform.MSAccess)
* //{
* // tb = new PFTableBuilder(dbplat);
* // createScript = tb.BuildTableCreateStatement(dt, connectionString);
* //}
* //else
* //{
* // createScript = string.Empty;
* //}
*
*
* bool result = false;
* if (frm.chkUseOdbcBuilder.Checked)
* {
* result = odbc.CreateTableUsingOdbcSyntax(dt, out createScript);
* }
* else if (frm.chkUseCustomSQL.Checked)
* {
* result = odbc.CreateTableUsingCustomScript(frm.txtSqlQuery.Text);
* }
* else
* {
* result = odbc.CreateTable(dt, out createScript);
* }
*
*
*
*/
}
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());
}
}
/// <summary>
/// Runs the table create statements contained in the provided tableDefs object.
/// </summary>
/// <param name="sourceDatabase">Database containing source tables..</param>
/// <param name="tableIncludePatterns">Wildard pattern to use when selecting which tables to include. Specify * or null or empty string for pattern to include all tables.</param>
/// <param name="tableExcludePatterns">Wildard pattern to use when selecting which tables to exclude. Specify * for pattern to exclude all tables. Specify null or empty string to exclude no tables.</param>
/// <param name="destinationDatabase">Database were data will be copied to..</param>
/// <param name="newSchemaName">Schema to use for identifying the destination tables.</param>
/// <param name="dropBeforeCreate">If true and table already exists, table will be dropped and then recreated using the table definition in the supplied PFTableDefs list. If false, table create step is bypassed if table already exists.</param>
/// <returns>Number of tables created.</returns>
/// <remarks>Include and exclude patterns are the same as Windows File wildcards. * specifies zero or more characters. ? specifies one character.</remarks>
public PFList <TableCopyDetails> CopyTableDataFromTableDefs(IDatabaseProvider sourceDatabase, string[] tableIncludePatterns, string[] tableExcludePatterns,
IDatabaseProvider destinationDatabase, string newSchemaName, bool dropBeforeCreate)
{
PFList <TableCopyDetails> tableCopyLog = new PFList <TableCopyDetails>();
PFTableDef td = null;
string sqlStatement = string.Empty;
string selectStatement = "select * from <TableName>";
string newTableName = string.Empty;
string tdToXml = string.Empty;
PFTableDef newTd = null;
PFList <PFTableDef> tableDefs = sourceDatabase.GetTableList(tableIncludePatterns, tableExcludePatterns);
DataTable sourceData = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
newTableName = td.TableName;
tdToXml = td.ToXmlString();
newTd = PFTableDef.LoadFromXmlString(tdToXml);
if (newSchemaName.Trim().Length > 0)
{
newTd.TableObject.TableName = newSchemaName + "." + newTableName;
}
else
{
newTd.TableObject.TableName = newTableName;
}
newTd.TableFullName = newTd.TableObject.TableName;
newTd.TableOwner = newSchemaName;
newTd.TableName = newTableName;
if (destinationDatabase.TableExists(newSchemaName, newTableName) && dropBeforeCreate)
{
destinationDatabase.DropTable(newSchemaName, newTableName);
}
if (destinationDatabase.TableExists(newSchemaName, newTableName) == false)
{
sqlStatement = destinationDatabase.BuildTableCreateStatement(newTd.TableObject);
destinationDatabase.RunNonQuery(sqlStatement, CommandType.Text);
}
TableCopyDetails tcdetails = new TableCopyDetails();
tcdetails.sourceTableName = td.TableFullName;
tcdetails.destinationTableName = newTd.TableFullName;
tcdetails.numSourceRows = -1;
tcdetails.numRowsCopied = -1;
try
{
sqlStatement = selectStatement.Replace("<TableName>", td.TableObject.TableName);
sourceData = sourceDatabase.RunQueryDataTable(sqlStatement, CommandType.Text);
sourceData.TableName = newTd.TableObject.TableName;
tcdetails.numSourceRows = sourceData.Rows.Count;
if (sourceData.Rows.Count > 0)
{
destinationDatabase.ImportDataFromDataTable(sourceData);
tcdetails.numRowsCopied = sourceData.Rows.Count;
tcdetails.result = TableCopyResult.Success;
tcdetails.messages = string.Empty;
}
else
{
tcdetails.numRowsCopied = 0;
tcdetails.result = TableCopyResult.Alert;
tcdetails.messages = "The were no rows in the source table.";
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to copy ");
_msg.Append(td.TableFullName);
_msg.Append(" to ");
_msg.Append(newTd.TableFullName);
_msg.Append(" failed. Error message: ");
_msg.Append(PFTextObjects.PFTextProcessor.FormatErrorMessage(ex));
tcdetails.messages = _msg.ToString();
tcdetails.numRowsCopied = -1;
tcdetails.result = TableCopyResult.Failure;
}
tableCopyLog.Add(tcdetails);
newTd = null;
sourceData = null;
}
return(tableCopyLog);
}
