/// <summary>
/// Retrieve the list of table objects and log these. The names of the required tables are returned in a list
/// </summary>
/// <param name="connection">A reference to the database connection</param>
/// <param name="logFilename">The full file path of the log file.</param>
/// <returns>A list of database table names - user tables, not system</returns>
internal static List <string> GetTableNames(DatabaseConnector connection, string logFilename)
{
// Create the return variable to hold a list of tables
List <string> tableNames = new List <string>();
// Get the table information. This is stored as a list that is used in the construction of the
// column data and other schema objects. The tables collection allows for four restrictions;
// catalog, cchema, table Name and table type. We're not interested in the system tables, so set
// the final of these restrictions to the 'Tables' type.
string[] restrictionValues = new string[4];
restrictionValues[3] = "Table";
DataTable schemaData = connection.ExecuteGetSchema("Tables", restrictionValues);
if (schemaData != null)
{
// Update the log file
SchemaDocumentor.LogSchemaData(schemaData, logFilename, "Tables", 25); // Log file documentation
// Now extract the table names and populate the list
var selectedRows = from content in schemaData.AsEnumerable()
select new { TableName = content["table_name"] };
foreach (var row in selectedRows)
{
tableNames.Add(row.TableName.ToString());
}
}
Console.WriteLine("Processed database table data...");
return(tableNames);
}
/// <summary>
/// Retrieve the list of column objects by table and log these. For each table a list of columns is created and then
/// added to the database schema object which is returned
/// </summary>
/// <param name="connection">A reference to the database connection</param>
/// <param name="tableNames">The list of table names to process</param>
/// <param name="logFilename">The full file path of the log file</param>
/// <returns>A database schema object containing the table and column information for the Access database</returns>
internal static Dictionary <string, List <SchemaColumn> > GetColumnNames(DatabaseConnector connection, List <string> tableNames, string logFilename)
{
// Create the return variable to hold the schema
Dictionary <string, List <SchemaColumn> > schema = new Dictionary <string, List <SchemaColumn> >();
// Variables to hold the data table containing the column information
DataTable schemaData;
List <SchemaColumn> columnList;
string[] restrictionValues = new string[4];
// For each of the tables in the database get the column schema data relating to that table and process
foreach (string tableName in tableNames)
{
// Set the restriction (filter) to be the table name and retrieve the column data
restrictionValues[2] = tableName;
schemaData = connection.ExecuteGetSchema("Columns", restrictionValues);
if (schemaData != null)
{
// Update the log file
SchemaDocumentor.LogSchemaData(schemaData, logFilename, "Columns for Table: " + tableName, 25);
// Create an empty list to hold the column data
columnList = new List <SchemaColumn>();
// Extract the column data and populate the list
var selectedRows = from content in schemaData.AsEnumerable()
select new
{
ColumnName = content["COLUMN_NAME"],
ColumnDataType = content["DATA_TYPE"],
ColumnLength = content["CHARACTER_MAXIMUM_LENGTH"],
ColumnNullable = content["IS_NULLABLE"],
ColumnOrder = content["ORDINAL_POSITION"]
};
// Now loop through this row collection and populate a new schema column
SchemaColumn currentColumn;
foreach (var row in selectedRows.OrderBy(row => row.ColumnOrder))
{
currentColumn.Name = row.ColumnName.ToString();
currentColumn.Type = MapDataType((int)row.ColumnDataType);
if (currentColumn.Type == "VARCHAR")
{
currentColumn.Type += " (" + row.ColumnLength.ToString() + ")";
}
currentColumn.IsNullable = (bool)row.ColumnNullable;
currentColumn.IsPrimaryKey = false;
columnList.Add(currentColumn);
}
schema.Add(tableName, columnList);
}
}
Console.WriteLine("Processed database column data...");
return(schema);
}
// Read the database metadata and write this to the log. This is not used in the creation of the schema object
/// <summary>
/// Read the database metadata and write this to the log. This is not used in the creation of the schema object
/// </summary>
/// <param name="connection">A reference to the database connection</param>
/// <param name="logFilename">The full file path of the log file. Created next to the source database</param>
internal static void GetMetadata(DatabaseConnector connection, string logFilename)
{
// Get the database metadata and add this to the log file.
// This is provided for inforrmation only. This is not used in the creation of the schema
string[] restrictionValues = new string[4];
DataTable schemaData = connection.ExecuteGetSchema("", restrictionValues);
if (schemaData != null)
{
SchemaDocumentor.LogSchemaData(schemaData, logFilename, "Metadata", 25);
}
Console.WriteLine("Processed database metadata...");
}
/// <summary>
/// Read the views definitions from the database file and write this to the log.true This is not used in the schema but is helpful
/// </summary>
/// <param name="connection">A reference to the database connection</param>
/// <param name="logFilename">The full file path of the log file</param>
internal static void GetViewData(DatabaseConnector connection, string logFilename)
{
// Get the database views and add this to the log file. Three restrictions are required - none are used.
// This is provided for inforrmation only. This is not used in the creation of the schema
string[] restrictionValues = new string[3];
DataTable schemaData = connection.ExecuteGetSchema("Views", restrictionValues);
if (schemaData != null)
{
SchemaDocumentor.LogSchemaData(schemaData, logFilename, "Views", 32);
}
Console.WriteLine("Processed database views data...");
}
/// <summary>
/// Retrieve the list of primary keys by table and log these. For each table a duplicate the column ist and update values
/// if a primary key before adding to the database schema dictionary which is returned
/// </summary>
/// <param name="connection">A reference to the database connection</param>
/// <param name="schema">A reference to the schema that is to be duolicated and updated</param>
/// <param name="logFilename">The full file path of the log file</param>
/// <returns></returns>
internal static Dictionary <string, List <SchemaColumn> > GetIndexColumns(DatabaseConnector connection, Dictionary <string, List <SchemaColumn> > schema, string logFilename)
{
// Create an empty dictionary to use as a return value
Dictionary <string, List <SchemaColumn> > schemaReturn = new Dictionary <string, List <SchemaColumn> >();
// There are five restrictions possible; Catalog, Schema, Table Name, Constraint Name and Column Name.
// We use two: table name and the constriant name PrimaryKey
string[] restrictionValues = new string[5];
restrictionValues[2] = "PrimaryKey";
// Loops through each table, documenting the primary key data and extracting the primary key columns
foreach (KeyValuePair <string, List <SchemaColumn> > table in schema)
{
// Get the table name from the dictionary entry
string tableName = table.Key;
// Add the table name as a second restriction, set up varaibles for the primary key list and duplicate schema.
List <SchemaColumn> updatedColumns = new List <SchemaColumn>();
List <string> keyList = new List <string>();
restrictionValues[4] = tableName;
DataTable schemaData = connection.ExecuteGetSchema("Indexes", restrictionValues);
// Get the index information and doument this in the log file
if (schemaData != null)
{
// Update the logfile
SchemaDocumentor.LogSchemaData(schemaData, logFilename, "Index for Table:" + tableName, 25);
// Extract the list of primary key columns
var selectedRows = from content in schemaData.AsEnumerable()
select new
{
columnName = content["COLUMN_NAME"],
ColumnOrder = content["ORDINAL_POSITION"]
};
// Now loop through this row collection and populate a list of key fields
foreach (var row in selectedRows.OrderBy(row => row.ColumnOrder))
{
keyList.Add(row.columnName.ToString());
}
}
// For each column in the schema, check against the key list. Write the original and updated column data to the new list
foreach (SchemaColumn col in table.Value)
{
SchemaColumn updatedColumn;
updatedColumn.Name = col.Name;
updatedColumn.Type = col.Type;
updatedColumn.IsNullable = col.IsNullable;
updatedColumn.IsPrimaryKey = false;
// Check whether this column is a primary key
if (keyList.Contains(col.Name))
{
updatedColumn.IsPrimaryKey = true;
}
updatedColumns.Add(updatedColumn);
}
// Update the duplicate schema with the updated table definition
schemaReturn.Add(tableName, updatedColumns);
}
// Confirm completion and return the updated schema
Console.WriteLine("Processed database index data...");
return(schemaReturn);
}
/// <summary>
/// Main program entry point. The database to document is supplied as a command line argument
/// </summary>
/// <param name="args">The full path of the database file is provided here</param>
static void Main(string[] args)
{
// Check whether a parameter has been supplied and whether this parameter is a valid file
const string logFile = "Migration-Log.txt";
const bool includeData = true;
string databaseFile = "";
string databasePath = "";
#region Process the command line arguments and set up logging data
// Check whether a command line argument has been provided
if (args.Length == 0)
{
Console.WriteLine("No database path provided as a command line argument. Terminating... ");
Console.ReadLine();
System.Environment.Exit(1);
}
// Check that the specified file exists
else if (!File.Exists(args[0]))
{
Console.WriteLine("The specified file cannot be located. Please chack and re-run. Parameter entered:");
Console.WriteLine(args[0]);
Console.ReadLine();
System.Environment.Exit(1);
}
// Set the database file and path used for the log file
else
{
databaseFile = args[0];
databasePath = Path.GetDirectoryName(databaseFile);
}
// Set the reference to the log file
string logFilename = "";
if (databasePath.Length != 0)
{
logFilename += databasePath;
}
logFilename += "\\" + logFile;
if (File.Exists(logFilename))
{
File.Delete(logFilename);
}
#endregion
#region Process the database file and create the schema dictionary
// Initialise a class to connect to the database and variables to retrieve the schema data
DatabaseConnector connection = new DatabaseConnector(databaseFile);
// Process and log the metadata for the Microsoft Access database file
SchemaDocumentor.GetMetadata(connection, logFilename);
// Log the table data and retrieve a list of tables
List <string> tableNames = SchemaDocumentor.GetTableNames(connection, logFilename);
// Log the column data by table and create the schema dictionary
Dictionary <string, List <SchemaColumn> > schema = SchemaDocumentor.GetColumnNames(connection, tableNames, logFilename);
// Log the index data by table and update the schema dictionary
schema = SchemaDocumentor.GetIndexColumns(connection, schema, logFilename);
// Log the views data - this is not used in the schema creation
SchemaDocumentor.GetViewData(connection, logFilename);
#endregion
#region SQL script creation routine
// Now run the SQL script creation methods
string outputFilename = Path.GetFileNameWithoutExtension(databaseFile);
outputFilename = databasePath + "\\" + outputFilename + ".sql";
ScriptCreator.CreateSQL(connection, schema, outputFilename, includeData);
#endregion
// Message this this is complete and wait for user confirmation
Console.WriteLine("\nExecution complete. Press enter to exit...");
Console.ReadLine();
}