示例#1
0
        /// <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);
        }
示例#2
0
        /// <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);
        }
示例#3
0
        // 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...");
        }
示例#4
0
        /// <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...");
        }
示例#5
0
        /// <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();
        }