Ejemplo n.º 1
0
        public void GetSchemata() {
            if (SchemaCollection==null) {
                SchemaCollection = new Dictionary<string, DataTable>();

                DataTable dtTables = new DataTable();
                DataTable dtColumns = new DataTable();
                DataTable dtColumnsKeywordsSingleValue = new DataTable();
                DataTable dtColumnsContVariableKeywords = new DataTable();
                DataTable dtColumnsMain = new DataTable();
                DataTable dtColumnsContVariable = new DataTable();

                dtTables.Columns.Add(new DataColumn("TABLE_NAME"));
                dtTables.Columns.Add(new DataColumn("FILEPATH"));

                dtColumns.Columns.Add(new DataColumn("TABLE_NAME"));
                dtColumns.Columns.Add(new DataColumn("COLUMN_NAME"));
                dtColumns.Columns.Add(new DataColumn("DATA_TYPE"));

                dtColumnsKeywordsSingleValue.Columns.Add(new DataColumn("TABLE_NAME"));
                dtColumnsKeywordsSingleValue.Columns.Add(new DataColumn("COLUMN_NAME"));
                dtColumnsKeywordsSingleValue.Columns.Add(new DataColumn("DATA_TYPE"));

                dtColumnsContVariableKeywords.Columns.Add(new DataColumn("TABLE_NAME"));
                dtColumnsContVariableKeywords.Columns.Add(new DataColumn("COLUMN_NAME"));
                dtColumnsContVariableKeywords.Columns.Add(new DataColumn("DATA_TYPE"));

                dtColumnsMain.Columns.Add(new DataColumn("TABLE_NAME"));
                dtColumnsMain.Columns.Add(new DataColumn("COLUMN_NAME"));
                dtColumnsMain.Columns.Add(new DataColumn("DATA_TYPE"));

                dtColumnsContVariable.Columns.Add(new DataColumn("TABLE_NAME"));
                dtColumnsContVariable.Columns.Add(new DataColumn("COLUMN_NAME"));
                dtColumnsContVariable.Columns.Add(new DataColumn("DATA_TYPE"));

                string filePath = PCAxisHelper.GetFilePathFromConnectionString(connectionstring);
                if (filePath.Trim().ToLower().StartsWith("ftp://")) {
                    filePath = PCAxisHelper.SynchFTPFiles(filePath);
                }

                DirectoryInfo di = new DirectoryInfo(filePath);
                FileInfo[] files = di.GetFiles("*.px");
                foreach (FileInfo fi in files) {
                    string tablename = PcAxis.FormatTableName(fi.Name);//.Substring(0, fi.Name.LastIndexOf('.')));//.Replace(" ", "_").Replace("-", "_");
                    DataRow dr = dtTables.NewRow();
                    dr["TABLE_NAME"] = tablename;
                    dr["FILEPATH"] = fi.FullName;
                    dtTables.Rows.Add(dr);

                    PcAxis pcAxis = new PcAxis(fi.FullName);
                    List<string> columns = pcAxis.GetColumns(ColumnFilter.All);
                    foreach (string column in columns) {
                        DataRow drc = dtColumns.NewRow();
                        drc["TABLE_NAME"] = tablename;
                        drc["COLUMN_NAME"] = column;
                        drc["DATA_TYPE"] = "VARCHAR";
                        dtColumns.Rows.Add(drc);
                    }
                    columns = pcAxis.GetColumns(ColumnFilter.KeywordsSingleValue);
                    foreach (string column in columns) {
                        DataRow drc = dtColumnsKeywordsSingleValue.NewRow();
                        drc["TABLE_NAME"] = tablename;
                        drc["COLUMN_NAME"] = column;
                        drc["DATA_TYPE"] = "VARCHAR";
                        dtColumnsKeywordsSingleValue.Rows.Add(drc);
                    }
                    columns = pcAxis.GetColumns(ColumnFilter.ContVariableKeywords);
                    foreach (string column in columns) {
                        DataRow drc = dtColumnsContVariableKeywords.NewRow();
                        drc["TABLE_NAME"] = tablename;
                        drc["COLUMN_NAME"] = column;
                        drc["DATA_TYPE"] = "VARCHAR";
                        dtColumnsContVariableKeywords.Rows.Add(drc);
                    }
                    columns = pcAxis.GetColumns(ColumnFilter.Main);
                    foreach (string column in columns) {
                        DataRow drc = dtColumnsMain.NewRow();
                        drc["TABLE_NAME"] = tablename;
                        drc["COLUMN_NAME"] = column;
                        drc["DATA_TYPE"] = "VARCHAR";
                        dtColumnsMain.Rows.Add(drc);
                    }
                    columns = pcAxis.GetColumns(ColumnFilter.ContVariable);
                    foreach (string column in columns) {
                        DataRow drc = dtColumnsContVariable.NewRow();
                        drc["TABLE_NAME"] = tablename;
                        drc["COLUMN_NAME"] = column;
                        drc["DATA_TYPE"] = "VARCHAR";
                        dtColumnsContVariable.Rows.Add(drc);
                    }
                }

                SchemaCollection.Add("Tables", dtTables);
                SchemaCollection.Add("Columns", dtColumns);
                SchemaCollection.Add("ColumnsKeywordsSingleValue", dtColumnsKeywordsSingleValue);
                SchemaCollection.Add("ColumnsContVariableKeywords", dtColumnsContVariableKeywords);
                SchemaCollection.Add("ColumnsMain", dtColumnsMain);
                SchemaCollection.Add("ColumnsContVariable", dtColumnsContVariable);
            }
        }
Ejemplo n.º 2
0
        private void DoLoadData() {

            List<string> tablenames = ExtractTableNamesFromSQLQuery(this.command.CommandText);
            var transaction = this.Connection.BeginTransaction();
            foreach (string tablename in tablenames) {
                if (!PcAxisConnection.LoadedTables.Contains(tablename))
                {
                    
                    DataTable tables = PcAxisConnection.GetSchema("Tables");
                    DataRow[] tableRows = tables.Select("TABLE_NAME ='" + tablename + "'");
                    if (tableRows.Length > 0)
                    { // MAT-249 workaround
                        PcAxisConnection.LoadedTables.Add(tablename);
                        string filepath = tableRows[0]["FILEPATH"].ToString();
                        //string filepath = PcAxisConnection.GetSchema("Tables").Select("TABLE_NAME ='" + tablename + "'")[0]["FILEPATH"].ToString();
                        PcAxis pcaxis = new PcAxis(filepath);

                        //CREATE A TEMPLATE COMMAND FOR INSERT A ROW IN THE MAIN TABLE
                        DataTable columns = PcAxisConnection.GetSchema("ColumnsMain", new string[] { null, null, tablename });
                        SQLiteCommand com = new SQLiteCommand(this.Connection) { Transaction = transaction };
                        StringBuilder insertSQL = new StringBuilder();
                        insertSQL.Append("INSERT INTO \"");
                        insertSQL.Append(tablename + "_DATA");
                        insertSQL.Append("\" VALUES (");
                        int i = 0;
                        foreach (DataRow row in columns.Rows)
                        {
                            i++;
                            string paramname = string.Format("@param{0}", i);
                            insertSQL.Append(paramname);
                            if (i != columns.Rows.Count)
                            {
                                insertSQL.Append(",");
                            }
                            else
                            {
                                insertSQL.Append(")");
                            }
                            SQLiteParameter param = new SQLiteParameter();
                            //param.Value = val;
                            param.DbType = DbType.String;
                            param.ParameterName = paramname;
                            com.Parameters.Add(param);
                        }
                        com.CommandText = insertSQL.ToString();

                        //CREATE A TEMPLATE COMMAND FOR INSERT A ROW IN THE KEYWORDS SINGLE VALUE TABLE
                        columns = PcAxisConnection.GetSchema("ColumnsKeywordsSingleValue", new string[] { null, null, tablename });
                        SQLiteCommand com2 = new SQLiteCommand(Connection) { Transaction = transaction };
                        insertSQL = new StringBuilder();
                        insertSQL.Append("INSERT INTO \"");
                        insertSQL.Append(tablename + "_KEYWORDS_SINGLE_VALUE");
                        insertSQL.Append("\" VALUES (");
                        i = 0;
                        foreach (DataRow row in columns.Rows) {
                            i++;
                            string paramname = string.Format("@param{0}", i);
                            insertSQL.Append(paramname);
                            if (i != columns.Rows.Count) {
                                insertSQL.Append(",");
                            } else {
                                insertSQL.Append(")");
                            }
                            SQLiteParameter param = new SQLiteParameter();
                            //param.Value = val;
                            param.DbType = DbType.String;
                            param.ParameterName = paramname;
                            com2.Parameters.Add(param);
                        }
                        com2.CommandText = insertSQL.ToString();


                        //CREATE A TEMPLATE COMMAND FOR INSERT A ROW IN THE CONTVARIABLE KEYWORDS TABLE
                        columns = PcAxisConnection.GetSchema("ColumnsContVariableKeywords", new string[] { null, null, tablename });
                        SQLiteCommand com3 = new SQLiteCommand(Connection) { Transaction = transaction };
                        insertSQL = new StringBuilder();
                        insertSQL.Append("INSERT INTO \"");
                        insertSQL.Append(tablename + "_CONTVARIABLE_KEYWORDS");
                        insertSQL.Append("\" VALUES (@contvar,");
                        i = 0;

                        SQLiteParameter paramContVar = new SQLiteParameter();
                        //param.Value = val;
                        paramContVar.DbType = DbType.String;
                        paramContVar.ParameterName = "@contvar";
                        com3.Parameters.Add(paramContVar);


                        foreach (DataRow row in columns.Rows) {
                            i++;
                            string paramname = string.Format("@param{0}", i);
                            insertSQL.Append(paramname);
                            if (i != columns.Rows.Count) {
                                insertSQL.Append(",");
                            } else {
                                insertSQL.Append(")");
                            }
                            SQLiteParameter param = new SQLiteParameter();
                            //param.Value = val;
                            param.DbType = DbType.String;
                            param.ParameterName = paramname;
                            com3.Parameters.Add(param);
                        }
                        com3.CommandText = insertSQL.ToString();

                        pcaxis.DataPreviewRows = this.PcAxisConnection.DataPreviewRows;
                        //RUN THIS COMMAND FOR EACH RECORD
                        pcaxis.InsertDataInMemoryDB(com, com2, com3);
                    }
                }
            }

            transaction.Commit();

        }