public static void test() { // Get the relations for this table DataTable relations = MappingEngine.getRelationsForDataSource(dataSources["PatientMealChoices"].getID()); Program.DisplayDataTable(relations); }
static void testSystem() { // Create a new SQLSERVER wrapper WrapperSQLSERVER SuperViewTest = new WrapperSQLSERVER("PatientMealChoices", "Data Source=(local);Initial Catalog=PatientMealChoices;User id=sa;Password=Pa55w0rd;"); // Attempt a connection to the database if (SuperViewTest.connect()) { Console.WriteLine("Connection successful \n"); SuperViewTest.disconnect(); } // QUERY TEST Console.WriteLine("QUERY TEST"); string query = "SELECT * FROM Patients;"; Console.WriteLine("Query: " + query); // Query the database DataTable results = SuperViewTest.query(query); // Output the results of the query DisplayDataTable(results); // END QUERY TEST Console.WriteLine(""); // GET SCHEMA TEST Console.WriteLine("GET SCHEMA TEST"); IList <string> tables = SuperViewTest.getTables(); // Display the list DisplayIList(tables); // END GET SCHEMA TEST Console.WriteLine(""); // GET COLUMNS TEST Console.WriteLine("GET COLUMNS TEST"); Dictionary <string, Dictionary <string, string> > columns = SuperViewTest.getColumns("Test"); // Display the columns foreach (KeyValuePair <string, Dictionary <string, string> > column in columns) { Console.WriteLine("Column Name: " + column.Key.ToString() + " Data Type: " + column.Value["data_type"].ToString()); } //DisplayDataTable(columns); // END GET COLUMNS TEST Console.WriteLine(""); //MAPPING ENGINE TEST Console.WriteLine("MAPPING ENGINE TEST"); results = MappingEngine.queryWrapper(SuperViewTest, "SELECT * FROM Patients WHERE NHSNumber = '1111111111'"); DisplayDataTable(results); //END MAPPING ENGINE TEST }
// Gets the data source of a mapping public static Wrapper getMappingDataSource(string targetField) { //Find the mapping, get the ID of the first source data source DataTable mapping = MappingEngine.getMappingSource(targetField); int sourceDataSource = (int)mapping.Rows[0]["SourceDataSource"]; return(getDataSourceWrapper(sourceDataSource)); }
// Function to use SuperView once initialised public static void useSuperView() { Console.WriteLine(""); // Get all of the mappings we have produced DataTable mappings = MappingEngine.getMappings(); // Display the mappings foreach (DataRow mapping in mappings.Rows) { string dataSourceName = Utilities.getDataSourceName((int)mapping["sourceDataSource"]); string tableName = Utilities.getTableName((int)mapping["sourceTable"]); Console.WriteLine(mapping["targetName"].ToString() + " (Data Source: " + dataSourceName + "| Table: " + tableName + ")"); } Console.WriteLine(""); Console.Write("Enter query: "); Dictionary <Wrapper, DataTable> results = QueryEngine.query(Console.ReadLine().ToString()); DataTable output = QueryEngine.joinData(results); }
public static DataTable joinData(Dictionary <Wrapper, DataTable> results) { // Get all of the relations needed to join the data we have //DataTable relations = MappingEngine.getRelationsForTable( // Empty data table to hold our results DataTable resultsTable = new DataTable(); // Loop foreach (KeyValuePair <Wrapper, DataTable> data in results) { // Get the relations for this table DataTable relations = MappingEngine.getRelationsForDataSource(data.Key.getID()); // Variable to make our table easier to access DataTable table = data.Value; resultsTable = joinDataTables(resultsTable, table, "NHSNumber", "NHSNumber"); } Program.DisplayDataTable(resultsTable); return(new DataTable()); }
//Function to display all the mappings public static void showMappings() { //Loop through all of the wrappers and output their names Console.WriteLine("Wrappers..."); foreach (KeyValuePair <string, Wrapper> dataSource in dataSources) { Console.WriteLine(dataSource.Key.ToString()); } Console.WriteLine(""); Console.WriteLine("Mappings..."); // Get all of the mappings we have produced DataTable mappings = MappingEngine.getMappings(); // Display the mappings (along with their ID) foreach (DataRow mapping in mappings.Rows) { string dataSourceName = Utilities.getDataSourceName((int)mapping["sourceDataSource"]); string tableName = Utilities.getTableName((int)mapping["sourceTable"]); Console.WriteLine("ID: " + mapping["ID"].ToString() + " Name: " + mapping["targetName"].ToString() + " (Data Source: " + dataSourceName + "| Table: " + tableName + " Table ID: " + mapping["sourceTable"] + ")"); } }
//Query the virtual data source public static Dictionary <Wrapper, DataTable> query(string query) { // Basic implementation // Get the WHERE clause (this is all we are passing in for now) List <string> qWhere = query.Split(' ').ToList <string>(); string qWhereField = qWhere[0]; string qWhereOperator = qWhere[1]; string qWhereValue = qWhere[2]; // Find the mapping for the WHERE field DataTable mapping = MappingEngine.getMappingSource(qWhereField); // Store the table ID int whereSourceTable = (int)mapping.Rows[0]["SourceTable"]; // Store the datas source ID int whereDataSource = (int)mapping.Rows[0]["SourceDataSource"]; // Build a dictionary to hold our results Dictionary <Wrapper, DataTable> results = new Dictionary <Wrapper, DataTable>(); // We must now loop through all of our data sources and query them for their data foreach (KeyValuePair <string, Wrapper> dataSource in Program.dataSources) { // Store the wrapper so it's easier to reference Wrapper wrapper = dataSource.Value; Dictionary <string, Dictionary <string, string> > where = null; // Check if our mapping table contains this data source's ID bool contains = mapping.AsEnumerable().Any(row => dataSource.Value.getID() == row.Field <int>("SourceDataSource")); Console.WriteLine(contains); // Does our WHERE apply to this data source? if (dataSource.Value.getID() == whereDataSource) { // We need to build a WHERE clause for this data source // Get the name of the table the WHERE applies to string whereTableName = MappingEngine.getTableName(whereSourceTable); // Build the WHERE clause dictionary where = new Dictionary <string, Dictionary <string, string> >(); // Add the field name to the dictionary where.Add(qWhereField, new Dictionary <string, string>()); // Add the operator and value to the dictionary for that field where[qWhereField].Add("operator", qWhereOperator); where[qWhereField].Add("value", qWhereValue); } // Generate a list of columns that we want from this data source, for now we just get them all // TODO - Only get the columns we actually need (can we do this?) List <string> columns = new List <string> { "*" }; // Generate a list of tables that we want for this data source, for now we just get all the mapped tables // TODO - Only get tables we actually need (can we do this?) DataTable mappedTables = MappingEngine.getMappedTables(wrapper); List <string> tables = mappedTables.AsEnumerable().Select(row => row.Field <string>("Name")).ToList(); results[Program.dataSources[dataSource.Key]] = wrapper.queryWithWhere(columns, tables, where); } return(results); }
// Function to start the SuperView system including initialising and mapping all defined data sources static void startSuperView() { string response; Console.WriteLine("Loading data source configurations..."); // Load the data source definitions and create the required objects loadDataSourceDefinitions(); createDataSourceObjects(); Console.WriteLine(dataSources.Count().ToString() + " data source(s) loaded"); Console.WriteLine(""); // Loop through each of the data sources foreach (KeyValuePair <string, Wrapper> dataSource in dataSources) { Wrapper wrapper = dataSource.Value; Console.WriteLine("Data Source: " + wrapper.getName() + " (ID: " + wrapper.getID().ToString() + ")"); Console.Write("Would you like to configure mappings for this data source? (Y/N): "); string reponse = Console.ReadLine(); // Perform the mappings if (reponse.ToString().ToLower() == "y") { // Map the tables Console.WriteLine("Mapping tables..."); // Give the user the option to preserve table mappings if (MappingEngine.getMappedTables(wrapper).Rows.Count > 0) { Console.WriteLine("Table mappings already exist for '" + wrapper.getName() + "'"); } else { // Loop through the tables foreach (string table in wrapper.getTables()) { // Ask the user if they want to map the table Console.Write("Would you like to map the '" + table + "' table? (Y/N): "); response = Console.ReadLine().ToString(); // If they do then map the table if (response.ToLower() == "y") { MappingEngine.mapTable(wrapper, table); Console.WriteLine(table); } } // Display how many tables we have mapped in this data source Console.WriteLine(MappingEngine.getMappedTables(wrapper).Rows.Count.ToString() + " table(s) mapped"); } Console.WriteLine(""); // Map the fields Console.WriteLine("Mapping fields..."); // Loop through every table that we have mapped for this data source foreach (DataRow mapping in MappingEngine.getMappedTables(wrapper).Rows) { int tableID = (int)mapping["ID"]; string table = mapping["Name"].ToString(); // Check if there are any mappings for this table, if there are then we can't change them if (MappingEngine.getMappings(wrapper, tableID).Rows.Count > 0) { Console.WriteLine("Mappings already exist for the '" + table + "' table."); } else { // Loop through each column in the data source foreach (KeyValuePair <string, Dictionary <string, string> > column in wrapper.getColumns(table)) { Console.WriteLine("Column Name: " + column.Key.ToString() + " | Data Type: " + column.Value["data_type"].ToString()); Console.Write("Enter a mapping name for this column (leave blank to ignore): "); string mappingName = Console.ReadLine().ToString(); // If we entered a mapping name then add the mapping to the database if (mappingName != "") { MappingEngine.mapField(wrapper, table, column.Key.ToString(), column.Value.ToString(), mappingName); } Console.WriteLine(""); } Console.WriteLine(MappingEngine.getMappings(wrapper, tableID).Rows.Count.ToString() + " field(s) mapped"); } } } } Console.WriteLine(""); // Set up relations Console.WriteLine("Creating relations..."); Console.Write("Would you like to add a relation? (Y/N): "); response = Console.ReadLine(); // While the user wants to add more relations while (response.ToString().ToLower() == "y") { // Get all of the mappings we have produced DataTable mappings = MappingEngine.getMappings(); // Display the mappings (along with their ID) foreach (DataRow mapping in mappings.Rows) { string dataSourceName = Utilities.getDataSourceName((int)mapping["sourceDataSource"]); string tableName = Utilities.getTableName((int)mapping["sourceTable"]); Console.WriteLine("ID: " + mapping["ID"].ToString() + " Name: " + mapping["targetName"].ToString() + " (Data Source: " + dataSourceName + "| Table: " + tableName + " Table ID: " + mapping["sourceTable"] + ")"); } Console.Write("Enter table 1 ID: "); int table1 = Int32.Parse(Console.ReadLine().ToString()); Console.Write("Enter table 2 ID: "); int table2 = Int32.Parse(Console.ReadLine().ToString()); Console.Write("Enter relation for column 1 (E.g. {ID}): "); string column1 = Console.ReadLine().ToString(); Console.Write("Enter relation for column 2 (E.g. {ID}): "); string column2 = Console.ReadLine().ToString(); //Store the relation MappingEngine.storeRelation(table1, table2, column1, column2); // Check if the user wants to make another mapping Console.Write("Would you like to add a relation? (Y/N): "); response = Console.ReadLine(); } }