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);
}
}
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();
}